MySQL索引中的前缀索引和多列索引

正确地创建和使用索引是实现高性能查询的基础，本文笔者介绍MySQL中的前缀索引和多列索引。

不要对索引列进行计算

如果我们对索引列进行了计算，那么索引会失效，例如

explain select * from account_batch where id + 1 = 19298
复制代码

就会进行全表扫描，因为MySQL无法解析id + 1 = 19298这个方程式进行等价转换，另外使用索引时还需注意字段类型的问题，如果字段类型不一致，同样需要进行索引列的计算，导致索引失效，例如

explain select * from account_batch where batch_no = '202111060006';
explain select * from account_batch where batch_no = 202111060006
复制代码

第一行正确使用了batch_no索引列，第二行进行了全表扫描

前缀索引

如果索引列的值过长，可以仅对前面N个字符建立索引，从而提高索引效率，但会降低索引的选择性。对于BLOB和TEXT类型，MySQL必须使用前缀索引，具体使用多少个字符建立前缀，需要对其索引选择性进行计算。

计算方式如下

select N, COUNT(DISTINCT LEFT(x_name, N))/COUNT(*)  FROM x_table
复制代码

其结果值越大，说明区分度越高，由下面的表格可以看出，当N大于6之后，区分度增长量显著降低，因此当N为6就已经满足了前缀索引的要求。

多列索引

MySQL支持“索引合并”策略，一定程度上支持用多个单列索引来查询行。当出现索引合并时表明表上的所有是有值得优化的地方，判断是否出现索引合并可以观察Extra列是否出现了如下信息

Using union(account_batch_batch_no_index,account_batch_source_system_index); Using where
复制代码

如果是在AND操作中，说明有必要建立多列联合索引，如果是OR操作，会耗费大量CPU和内存资源在缓存、排序与合并上。