第20章：MySQL索引失效案例

1.全值匹配我最爱

当SQL查询

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';

创建3个索引

idx_age,idx_age_classid,idx_age_classid_name

当前优化器会选择跟where条件匹配最高的idx_age_classid_name索引，直接查询出对应的主键值然后回表查询，此时的效率最高。所以部分索引失效，因为使用的部分索引，会查询多个主键值还需要回表继续判断，效率低。

2.最佳左前缀规则

 当SQL查询

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND NAME='abcd';

创建索引

idx_age_classid_name

此时只用到索引age，因为跳过了索引字段，后面部分classid_name没有使用。

结论:MySQL可以为多个字段创建联合索引，where条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段无法使用。如果where条件没有使用第1个字段，索引将无法使用。

3.主键插入顺序

如果当前页的数据已经满了

 此时插入主键值为9的记录

会造成页分裂，把当前的页分裂成两个页。把本页的一些记录移动到新页，会造成性能损耗。所以主键值要依次递增。主键具有auto_increment，顺序写入，减少页分裂。

4.计算、函数、类型转换导致索引失效。

①函数索引失效

创建索引:idx_name

使用索引：SELECT * FROM student where name like 'abc%'

不使用索引：SELECT * FROM student where LEFT(name,3) ='abc'

②计算索引失效

创建索引:idx_sno

使用索引：SELECT * FROM student where stuno = 123;

不使用索引：SELECT * FROM student where stuno+1 = 123;

③类型转换索引失效

创建索引:idx_name

使用索引：SELECT * FROM student where name = 'abc'

不使用索引：SELECT * FROM student where name = 123

5.范围条件（大于小于）右边的列索引失效

创建索引:idx_age_classId_name

SQL语句: select * from student where age = 30 and classId>20 and name='abc'

只使用age_classId部分索引，name索引失效。

优化：创建联合索引，把范围条件的字段放在索引的最后，idx_age_name_classId

6.where条件不等于，大于，小于，索引失效

7.is null 可以使用索引（等于），is not null 不可以使用索引（不等于）

优化：在设计数据表将字段设置not null约束。

8.模糊查询%开头索引失效

优化：页面搜索严禁左模糊或全模糊，需要的话使用搜索引擎

9.or前后存在非索引的列，索引失效

or前后字段都要存在索引，才能使用

10.数据库和表的字符集统一使用utf8mb4，不同的字符集比较需转换会索引失效

【练习】index(a,b,c)

 

【建议】

①单列索引，当前搜索过滤性更好的索引

②组合索引:过滤性好的索引靠前，包含where条件更多的字段，出现范围查询放到最后