老生常谈 Mysql 索引

一、前述

程序员每天都会跟数据库打交道，每天都在面临着 “这个加载好慢，可以优化吗”，“是不是卡死了啊，点击半天没反应”诸如此类的声音，数据库优化可能是项目里需要不断在进行的，项目版本迭代，数据更新，数据量增加等等一系列，都会驱动代码优化，数据库优化，而数据库优化最直接可能也是程序员脱口而出的答案，加索引，然而，索引加上了，不是那么的好用的，因为它会失效，那么本文就简单来测试下有关索引的一些场景，有错误以及需要改正的地方希望指点

二、索引简述

在 Mysql 官网 是这样介绍索引的：索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中有相关​​列的索引，MySQL可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多

三、索引可能失效情况简述以及实战验证

首先这是一个将近七百万数据的表，使用 explain 来查看执行情况，如下：

EXPLAIN select * from f_mini_user

可以看到，type 为 ALL ，耗时 29 s,也就是该条语句执行是全表扫描，在 mysql 里这是最坏的执行情况

1、主键 id 为默认的聚族索引：

explain select * from f_mini_user where id = 6874957

当主键放入 where 子句时，mysql 把这个查询转为一个常量

2、当查询的数据出来超过了全表的数据的某个阈值以后，索引也会失效，查询会走全表扫描：

给 ctime 的字段建立索引：

2.1 查询 ctime

EXPLAIN SELECT * FROM `f_mini_user` where ctime < '2019-08-22 02:05:53';

2.2 更换查询条件的值查询

EXPLAIN SELECT * FROM `f_mini_user` where ctime < '2019-08-2 02:05:53';

如图所示，该条查询最后的结果超过全表一定百分比，导致了索引失效，通常来说是 20% ~ 30%

3、类型隐式转换导致索引失效

接下来要新加一个索引，由于数据量太大，新加索引会很卡很慢，所以这里先删除部分数据来测试
给 password 字段添加索引，password 为 varchar 类型：

3.1 查看查询的执行计划：

EXPLAIN SELECT * FROM `f_mini_user` where password = '123';

可以看到，进行了索引访问

3.2 再看如下查询：

EXPLAIN SELECT * FROM `f_mini_user` where password = 123;

可以看到，type 已经变为了 ALL，索引失效

失效的原因就是原本 varchar 类型的字段，传入的条件值 却不是 varchar ，于是 mysql 执行器将其转为了 varchar,导致索引失效

4、索引字段上使用 is null / is not null 判断时，会导致索引失效

5、对索引进行前置模糊匹配查询

可以看到，当前置模糊匹配 （%XXX）以及全包模糊匹配（%XXX%）的时候，索引失效，只有当后置模糊匹配的时候才使用到了索引

6、索引使用 or 导致失效

这里的 username 是索引，当使用 or 来查询的时候，索引失效，将 or 改为 and：
:

只有当 or 两边的字段都是索引的时候，才不会失效，如下，ctime 也是索引

7、对索引进行 <>、!= 操作的时候，索引会失效

用 = 条件查询的时候，索引生效

8、对索引列进行计算，索引失效

9、联合多列索引

创建如图所示联合索引：

9.1 查询 ctime段：

可以发现索引失效了，不满足最左前缀匹配

9.2 username or ctime：

索引失效

9.3 username and ctime

索引生效

9.4 ctime and password

索引失效

9.5 username and ctime and password

索引生效

9.6 username and ctime> XXX and password

前面两个生效，type 为 range

10 in/not in

10.1 in

10.2 not in