索引优化

索引优化

不恰当的索引用法

无法使用索引的情形

如果对索引字段进行了任何的表达式运算,那么其都会使索引功能失效,这是因为索引始终是一个 B+树,使用其进行索引片过滤的时候是通过二分查找实现的,如果进行了计算,那么就无法使用二分查找功能,也就使得索引失效了。

-- 对索引字段使用MySQL函数(可以对等于号后的值使用,不能对字段使用)
mysql> select * from actor where lower(first_name)='rmqchuezjthp’; -- 错误
mysql> select * from actor where first_name='rMqChueZJThP'; -- 正确
-- 隐式字符串转换(这里license字段为一个varchar类型字段)
mysql> select * from actor where license=6535; -- 错误
mysql> select * from actor where license='6535'; -- 正确
-- 对索引字段使用数学表达式
mysql> select * from actor where hash_email + 2 = 4224712734; -- 错误
mysql> select * from actor where hash_email = 4224712732; -- 正确

困难谓词

不等式

mysql> select A, B, C, D from TABLE where A=a and B>b and C>c;

于上述查询,推荐使用索引 (A, B, C, D)(A, C, B, D),如果 B 和 C 字段的选择性,哪一个高就将其放在索引字段的前面。对于索引(A, B, C, D),在查询时首先会根据字段 A 的等值条件和 B 的不等值条件进行索引片的过滤,然后扫描索引中 B 字段大于 b 的数据,在扫描过程中会判断获取到的数据是否满足 C>c 的条件,并且将符合条件的数据的 D 字段的值取出来,最后得到的结果集就是最终的结果集。该查询中 A 和 B 字段是参与了索引片的过滤的,而 C 和 D 字段则参与了索引覆盖扫描。

OR 谓词

mysql> select A, B, C from TABLE where A>a or B>b;

推荐索引(A)和(B),对于 OR 条件查询,由于并不是满足其中一个条件即可,而是两个条件只要满足一个即可。这里推荐索引为建立两个单列索引(A)和(B),因为 MySQL 可以通过这两个索引进行“索引合并扫描”,也就是其首先会扫描索引 A,获取其符合 A>a 条件的数据 id,然后扫描索引 B,获取其符合 B>b 的数据 id,然后将两个扫描结果进行合并,最后通过合并的数据 id 在磁盘上读取数据。

对于 OR 谓词的索引合并扫描需要说明的是,如果需要合并的结果集非常大,或者是结果集中重复数据过多,那么进行结果集的合并将是一个非常耗时的操作,有时候效率还不及全表扫描。对于这个问题的另一个解决办法就是新建一个字段,取值 1 和 0,标识其是否符合 where 条件,这样就只需要对该字段进行查询即可,也可以建立相关的索引。

IN 谓词

mysql> select A, B, C from TABLE where A in (m, n, p) and B=b;

推荐索引:(A, B),这里 IN 谓词严格意义上讲不是一个困难谓词,放在这里是为了借用 OR 谓词的索引合并扫描进行说明。对于 IN 谓词后的列表,MySQL 会循环列表中的数据,然后分别于后续索引字段联合,比如对于上述查询,其可以拆分为(A=m and B=b) union (A=n and B=b) union (A=p and B=b)。拆分之后 MySQL 会首先根据 A=m and B=b 扫描联合索引(A, B),获取结果集的 id,然后根据 A=n and B=b 再次扫描该索引,依次循环,知道所有 IN 列表条件都扫描完成。由于 IN 列表条件是不重复的,因而最后扫描索引片也是不重复的,在进行结果集的合并的时候也就没有类似 OR 谓词的去重操作,因而查询效率非常的高。总结来说,IN 谓词及其后续字段是可以使用到索引的。