MySQL之前缀索引_桐花思雨_mysql前缀索引

大大的周 02-07 6772

目录什么是前缀索引什么是索引选择性创建前缀索引数据库脚本获取前缀长度 `prefix_length`创建前缀索引前缀索引测试前缀索引与覆盖索引小结

什么是前缀索引

所谓前缀索引：说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时指定），这样建立起来的索引更小，所以查询更快。这有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数

那么为什么不对整个字段建立索引呢？一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性

这里又涉及到一个概念，什么是索引选择性？

什么是索引选择性

关于索引的选择性，它是指不重复的索引值和数据表的记录总数的比值，取值范围在 [0,1] 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行

那是不是选择性越高的索引越好呢？当然不是！索引选择性最高为 1，如果索引选择性为 1，就是唯一索引了，搜索的时候就能直接通过搜索条件定位到具体一行记录！这个时候虽然性能最好，但是也是最费空间的，这不符合我们创建前缀索引的初衷

我们一开始之所以要创建前缀索引而不是唯一索引，就是希望能够在索引的性能和空间之间找到一个平衡，我们希望能够选择足够长的前缀以保证较高的选择性（这样在查询的过程中就不需要扫描很多行），但是又希望索引不要太过于占用存储空间

那么我们该如何选择一个合适的索引选择性呢？索引前缀应该足够长，以便前缀索引的选择性接近于索引的整个列，即前缀的基数应该接近于完整列的基数

首先可过如下 SQL 得到全列选择性 SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name; 然后再通过如下 SQL 得到某一长度 prefix_length SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

在上面这条 SQL 执行的时候，我们要注意选择合适的 prefix_length，直至计算结果最接近于全列选择性的时候，就是最佳结果了，然后使用这个 prefix_length 就可以创建前缀索引了

创建前缀索引数据库脚本

数据库脚本我们使用的是 mysql 官方提供的示例数据库，下载地址如下：http://downloads.mysql.com/docs/sakila-db.zip，下载解压如下所示

我们选择 city 表作为测试表（此处我将 city 表名改为了 city_demo），表结构及数据分别在上面的 sql 文件中，并选择 city 列来创建前缀索引

获取前缀长度 prefix_length 首先我们通过如下 SQL 来获取一下 city 全列的索引选择性，结果如下 SELECT COUNT(DISTINCT city) / COUNT(*) FROM city_demo;

可以看到，结果为 0.9983。全列选择性为 0.9983 说明这一列的值还是有重复的

接下来我们获取前缀长度 prefix_length，这里一共测试了 8 个不同的 prefix_length，来看看各自的选择性 SELECT COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS pref7, COUNT(DISTINCT LEFT(city,8))/COUNT(*) AS pref8, COUNT(DISTINCT LEFT(city,9))/COUNT(*) AS pref9, COUNT(DISTINCT LEFT(city,10))/COUNT(*) AS pref10, COUNT(DISTINCT LEFT(city,11))/COUNT(*) AS pref11, COUNT(DISTINCT LEFT(city,12))/COUNT(*) AS pref12, COUNT(DISTINCT LEFT(city,13))/COUNT(*) AS pref13, COUNT(DISTINCT LEFT(city,14))/COUNT(*) AS pref14 FROM city_demo;

很明显，这里的前缀长度 prefix_length 是 14，接下来创建前缀索引

创建前缀索引 alter table city_demo add index city_index(city(14)); 前缀索引测试

使用如下 SQL 查看其执行计划，结果如下

EXPLAIN select * from city_demo where city = 'Kamjanets-Podilskyi';

可以看到，这个前缀索引已经用上了，具体搜索流程是这样

从 city_index 索引中找到第一个值为 Kamjanets-Podi 的记录（city 的前 14 个字符）

由于 city_index 是普通索引，叶子结点保存的是主键值，所以此时拿到了主键值，如 ID1

到主键索引树上查到 ID1 这一行，判断 city 的值满不满足 where 后的条件，不满足这一行丢弃

继续回到 city_index 这个索引树上查下一条记录，发现如果还是 Kamjanets-Podi，取出 ID2，再回到主键索引树上进行判断，如果值正确，将结果返回结果集中

重复执行以上流程，直到从 city_index 索引树上取出的数据不是 Kamjanets-Podi，循环结束

如果我们建立了前缀索引并且前缀索引的选择性为 1，那么就不需要第 5 步了，如果前缀索引选择性小于 1，就需要第 5 步。既节省了空间，又提高了搜索效率

前缀索引与覆盖索引

使用了前缀索引后，我们来看一个查询 SQL

EXPLAIN select city from city_demo where city = 'Kamjanets-Podilskyi';

如上的 SQL 查询的列是 city，在前缀索引中 B+Tree 里保存的根本就不是完整的 city 字段的值，必须要回表才能拿到需要的数据。所以，用了前缀索引，就用不了覆盖索引了小结前缀索引是一种能使索引占用空间更小，查询速度更快的有效办法，但另一方面也有其缺点：mysql 无法使用其前缀索引做 ORDER BY 和 GROUP BY，用了前缀索引，就用不了覆盖索引了要明确使用前缀索引的目的与优势大大节约索引的占用空间，从而提高索引效率对于 BOLB 、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度真正的难点在于：要选择足够长的前缀以保证较高的选择性，同时又不能太长，前缀的长度应该使前缀索引的选择性接近索引整个列，即前缀的基数应该接近于完整列的基数

参考：https://blog.csdn.net/qq_43672652/article/details/106156353 参考：https://blog.csdn.net/dhrome/article/details/72853153