项目实战-MySQL极佳优化方案---前缀索引

一、应用背景

由于公司项目数据量较大，开发小程序和APP过程中，SQL查询超过1.5s以上的等待时间，因此需要对SQL或者数据表结构设计进行优化。就不讲SQL语句优化啦，而是记录一下数据表结构设计优化的前缀索引实现。

二、MySQL索引

作用：

• 加速查询：索引能够显著减少数据库系统需要扫描的数据量，从而加快查询速度。
• 支持排序和分组：通过使用索引，数据库可以更快地执行排序和分组操作，因为索引已经对数据进行了排序。
• 保证数据的唯一性：通过创建唯一索引，可以确保表中每一行数据的某列或多列组合是唯一的。

类型：

• 主键索引（PRIMARY KEY）：数据表只能包含一个主键索引，且不允许有空值（NULL）。在InnoDB存储引擎中，主键索引也被称为聚簇索引，表中的数据行实际上是按照主键索引的顺序存储的。
• 唯一索引（UNIQUE）：与主键索引类似，唯一索引也要求索引列的值是唯一的，但允许有空值（NULL）。
• 普通索引（INDEX 或 KEY）：最基本的索引类型，没有任何限制。
• 全文索引（FULLTEXT）：用于全文搜索，只有MyISAM和InnoDB存储引擎支持全文索引。
• 空间索引（SPATIAL）：用于地理空间数据类型，只有MyISAM存储引擎支持空间索引。
• 前缀索引：只索引列值的前缀字符。
• 复合索引：在表的多个列上创建索引。

三、前缀索引

介绍

对文本的前几个字符建立索引，具体是几个字符在建立索引时去指定，比如：以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快。有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。

优点

当某个字段的数据量太大，而且查询又非常的频繁时，使用前缀索引能有效的减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高索引查询的速度。比如，客户店铺名称，有的名称很长，有的很短，如果完全按照全覆盖来建索引，索引的存储空间可能会非常的大，有的表如果索引创建的很多，甚至会出现索引存储的空间都比数据表的存储空间大很多，因此对于这种文本很长的字段，可以截取前几个字符来建索引，在一定程度上，既能满足数据的查询效率要求，又能节省索引存储空间。

缺点

MySQL 中无法使用前缀索引进行 ORDER BY 和 GROUP BY，也无法用来进行覆盖扫描，当字符串本身可能比较长，而且前几个字符完全相同，这个时候前缀索引的优势就不明显，也就没有创建前缀索引的必要。

应用

当某个字段内容的前几位区分度很高的时候，这个时候采用前缀索引，可以在查询性能和空间存储方面达到一个很高的性价比。

创建

建立前缀索引

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

其中prefix_length这个参数，就是前缀长度的意思，通常通过如下方式进行确认，步骤如下：
第一步，先计算某字段全列的区分度。

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

第二步，然后再计算前缀长度为多少时和全列的区分度最相似

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

之后不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是prefix_length想要的值。

实践

新建测试表，数据体量在 100 万以上，表结构如下：

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

数据内容：

测试正常的带name条件查询，效率如下：

select * from tb_test where name like '1805.59281427%'

SQL查询耗时：0.29秒

接下来，以name字段为例，创建前缀索引，找出最合适的prefix_length值。大致计算一下name字段全列的区分度。

select count(distinct name) / count(*) from tb_test;

结果为 0.9945，即全局不相同的数据率在99.45%这个比例。

接下来计算在不同的prefix_length值下，对应的数据不重复比例。

• 当prefix_length为5，区分度为 0.2237

select count(distinct left(name, 5)) / count(*) from tb_test;

• 当prefix_length为10，区分度为 0.9944

select count(distinct left(name, 10)) / count(*) from tb_test;

• 当prefix_length为11，区分度为 0.9945

select count(distinct left(name, 11)) / count(*) from tb_test;

通过对比发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key(name(11));

再试试上面那个语句查询， 并查看运行效率：

很明显，创建前缀索引之后，查询效率倍增，达到0.00032秒。

四、注意事项

不是所有的字段都适合用前缀索引。当某个索引的字符串列很大时，创建的索引也就变得很大，为减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。
对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。
如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好。