这是我参与11月更文挑战的第6天,活动详情查看:2021最后一次更文挑战
正确地创建和使用索引是实现高性能查询的基础,本文笔者介绍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就已经满足了前缀索引的要求。
前缀字符个数 | 区分度 |
---|---|
3 | 0.0546 |
4 | 0.3171 |
5 | 0.8190 |
6 | 0.9808 |
7 | 0.9977 |
8 | 0.9982 |
9 | 0.9996 |
10 | 0.9998 |
多列索引
MySQL支持“索引合并”策略,一定程度上支持用多个单列索引来查询行。当出现索引合并时表明表上的所有是有值得优化的地方,判断是否出现索引合并可以观察Extra列是否出现了如下信息
Using union(account_batch_batch_no_index,account_batch_source_system_index); Using where
如果是在AND
操作中,说明有必要建立多列联合索引,如果是OR
操作,会耗费大量CPU和内存资源在缓存、排序与合并上。