MySQL索引优化讲解

前提

本文讲MySQL InnoDB存储引擎B树索引的sql优化，前提知识是对MySQL的B树索引的检索规则等方面有一定的理解，以及对EXPLAIN关键字查询计划的字段含义有一定的理解。

建表语句

CREATE TABLE `user` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `hobby` varchar(100) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


#创建联合索引(`name`,age,hobby)
CREATE INDEX idx_name_age_hobby ON `user`(`name`,age,hobby);

全值匹配

全值匹配是指查询的字段按照索引顺序都能匹配到，是比较好的一种方式。

where条件的and顺序不是按照联合索引的顺序来匹配也没关系，优化器会进行优化。

最佳左前缀法则

要想联合索引发挥作用，就不能断开，比如不能跳过 name 字段，直接where age=18 and hobby='打篮球’进行索引检索数据。这样会导致索引失效。
断开会导致后面的索引失效，一定要连续才行，比如 where name=‘yehaocong1’ and hobby='打篮球’这样会使得hobby字段索引失效，因为联合所以 name和hobby之间还有个age，这里断开来age，导致后面的hobby失效。

下面直接使得索引整体失效，使用全表扫描。

下面使得索引部分失效，只有name字段使用了索引，如果只是要name字段，key_len是403，name + age 是408，name+age+hobby是811，所以此处只使用到了name字段，索引部分失效。

过滤条件要使用索引必须按照索引建立时的顺序， 依次满足， 一旦跳过某个字段， 索引后面的字段都无法被使用。

原理是：
该联合索引是有序的，有序的数据结构才能使用二分法进行检索，而MySQL的Btree联合索引，是在前面的字段相同的情况下，后面那一个字段才能做到有序，否则后面字段是无序的，所以如果缺少了前面的字段，后面的字段就会变得无序，而无需的数据结构是无法使用二分来检索，就会使得后面的字段失效。

不要在索引列上做任何计算

不在索引列上做任何操作（计算、 函数、 (自动 or 手动)类型转换）， 会导致索引失效而转向全表扫描。

下面sql仅仅用到了索引的name列，原因是age字段使用了计算 age + 1，导致了索引该字段失效了，然后导致该列后面的索引列也会失效。

结论：等号左边无计算，等号的左边仅仅只能是索引列。

索引列上不能存在隐式转换

典型的情景是没有给varchar类型加单引号。

上面的情况是varchar类型的name属性，等于一个整形，会导致MySQL会将name字段进行一个隐式类型转换，这就违反了上面“不要在索引列上做任何计算“ 这个情况的类型转换，只是他是隐式的，但也是违反了，所以会导致索引失效。

索引列上不能有范围查询

在索引列上做范围查询（> < >= <= ）这几个，会导致该索引列后面的索引列失效。但是范围查询的索引列索引还是有效的，只是查找类型从ref变成了range。

下面sql，因为age列使用了范围查询，所以导致hobby失效，但是age列的索引还是有效的。

但是 类型 IN like 这些范围查询，却不会导致后面索引列的失效，但是查询type也是range。

原因是>、 < 、>=、 <=进行检索时，比如age>18，就会使用索引对age=18进行检索定位到叶子节点，然后因为mysql的btree索引的叶子节点有通向下一个叶子节点的指针，又因为定位到了18，然后18右边的所有叶子节点都是符合>18这个条件的，所以就直接通过指针扫描所有右边的叶子节点来进行hobby的过滤，所以就没有使用到hobby列。

而IN就相当于多次检索，每次都能使用三个索引列，比如上面的，就相当于
where (name=‘yehaocong1’ and age=18 and hobby=‘打篮球’) or (name=‘yehaocong1’ and age=20 and hobby=‘打篮球’) or (name=‘yehaocong1’ and age=21 and hobby=‘打篮球’) ，都能使用到hobby索引。

而like，varchar类型的索引值是字符从左到右一个个比对大小的，排序也是左右到右进行比对排序，比如 yehao%,就可以使用yehao这段确定的字符定位到相应的节点，这定位到由yehao开头的节点，比对玩yehao之后，剩下的字符就不管了，然后就继续使用后面的age和hobby进行继续二分索引检索。

总的来说，>、<、>=、<=是直接把数据定位到叶子节点，然后通过指针向左或者向右扫描剩余节点进行过滤，并没有使用到后面的索引列进行二分索引检索，而in like 是不直接到叶子节点的，找到了符合的节点，就继续使用下一索引列进行二分检索，所以like 和 in能使用到后面的索引列。

尽量使用覆盖索引

尽量少使用select * ，而是只检索必要的列，达到覆盖索引的效果，比如上面联合索引，如果是select *的话就没有使用到覆盖索引，因为联合索引会保存索引列的值和主键，如果你查询的列被这些索引列包含，就直接可以从索引中得出数据，如果不能全部包含，那么定位到主键，再由主键回表进行数据查询，多进行了一步，如果少了这一步，效率会得到一定的增强。

下面sql因为location不是联合索引的索引列，所以单单靠该索引文件不能达到检索到location字段的值，所以只能拿到检索到的主键id，然后进行回表再查一次。

相当于
SELECT id,NAME,age,hobby,location FROM user WHERE name = ‘yehaocong1’ AND age=18 AND hobby=‘打篮球’ 会得到(id,name,age,hobby)的值，但是没有location。

然后回表再次使用sql
select id,NAME,age,hobby,location from user where id = 上面查询到的id集合。原因是mysql InnoDB引擎是用主键id来组织数据的，相当于聚簇索引。其他都是二级索引。如果不是覆盖索引，就要进行两次sql查询。

不要使用反条件进行检索

比如 != 、not like 、not in 这些都是反条件，这些都是会使得索引失效的。

尽量不要使用is not null

假如一个索引列允许为null 值，那么 is null 会用到索引，但是 is not null 会使得索引失效。

如果索引列是不允许空值的话，那么is not null 也是会使得索引失效，并且is null 会得到impossible where情形。因为字段都设置为不允许空值了，你检索 is null的话，相当于 不可能的条件，相当于1！=1，总是返回false。

尽量不要使用like前模糊

like前模糊匹配会导致索引失效，比如name like ‘%aaaa’ 就会使得索引失效，原理与最左匹配原则类似，都是从左到右进行比较，%右边的字符不参与比较了，只有%左边的字符和参与比较，所以%aaaa这种情况根本就没有字符来进行二分比较，所以就会索引失效，而 aaaa%，有四个字符aaaa进行二分比较定位，相当于索引列部分失效，但是没有全部失效。

没失效，有三个字符aaa进行二分比较

失效

没失效，因为有两个aa字符可以进行二分比较，只要通配符%不是在最左边，留有字符来进行二分比较，就不会失效。

关联查询优化

关联查询尽量用小表驱动大表，然后在大表建索引。
建表：

CREATE TABLE `book` (
  `bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card` int(10) unsigned NOT NULL,
  PRIMARY KEY (`bookid`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4

CREATE TABLE `class` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4

左右连接

左右连接，左连接跟又连接都一样，这里就用左连接做例子。

LEFT JOIN 左边是class表，右边是book表，所以 book表是被驱动表，class表是驱动表。
这里刚开始是都没有建索引的，所以都没索引可用。

在驱动表左表book表的card列建立索引。然后再次查询。由下图可知，book表使用到了索引。

删除book表的card索引并在被驱动表class表的card列建立索引。然后再次查询。从下面结果得知，都没有用到索引。

在book表的建立card索引，此时，两个表的card列都有索引。从下面结果看也是只有驱动表才使用到了索引。

由上可得，进行左右连接查询时，只有在被驱动表上建立索引才会使得索引生效，在驱动表上的索引不生效。

内连接

由上可知在Inner join右边的表的索引会有用，只需往右边表建索引即可，但是，这是错误的结论。

因为我的boot表和class表的数据量都一样，都是20条。

在boot表删除10条记录。

再次查询：


由上面两个图可知，无论inner join 右边的是哪个表，都是class表的索引生效，此时class表的数据比boot表多。

结论：inner join 时， mysql 会自己帮你把小结果集的表选为驱动表。只有在被驱动表上建立索引才有效。

还有：
尽量不要把子查询得出来的表作为左右连接查询的被驱动表，因为子查询的出的表可能是虚表，没有办法建立索引，也就是没有优化的余地，left join时， 尽量让实体表作为被驱动表。

#这个就是使用实体表t_emp作为被驱动表（左连接的右表），可以通过添加索引进行优化。
EXPLAIN SELECT a.name  a_name,b.name b_name FROM
(SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) a
LEFT JOIN t_emp c on a.ceo= b.id;


#这个就是使用子查询的结果作为左连接查询的被驱动表，因为是虚表，所以没有优化的余地，尽量避免这个情况。
EXPLAIN SELECT e.name e_name,tmp.name tmp_name
FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp
ON e.deptId=tmp.did;

排序优化

如果查询语句中使用了order by 进行排序，那么应当尽量避免出现filesort这个文件排序的情况，这个性能是比较低下的，应该尽量使用索引排序。

还是用上面的user表进行演示。还是那个联合索引。

首先，要知道为什么可以使用索引排序呢，因为索引本来就是有序的。如果通过索引检索出来的数据，排序规则也是索引顺序排序，就能用到索引排序，因为根本就不用排序，查出来的数据就是有序的。

出现filesort的一下情况：

1. 如果没有使用到索引，就不会存在索引排序的情况。
   比如没有where条件：
   
   因为上面的sql没有where条件，根本就没有使用到索引，查出来的数据是根据主键来排序的，然而如果是order by id 的话，就能使用到索引排序，但是使用的并不是，所以要进行另外的排序。

2. 使用错误的，与索引列的顺序不一致时，就不能使用索引排序。
   
   因为hobby跟age反了，所以索引排序失效。

顺序是正确的，就可以避免文件排序。

2. 如果排序字段有包含不是索引列的字段，就会出现文件排序。
   
   因为location不是联合索引的列。

3. 方向反，就会使用文件排序，比如 order by age asc,hobby desc,一个升序，一个降序，也会出现文件排序，同时升序或者同时降序都不会，因为降序的话，只是把索引的顺序掉转而已。
   

4. 范围查询导致的索引排序失效，like、in都会。
   
   
   但是这样连着，使得没有断掉就可以继续使用索引排序。
   
   上面因为索引从列age开始断了，如果排序列中把段掉的列补上去，就会继续使用索引排序。

Group by

group by 使用索引的原则几乎跟 order by 一致 ， 唯一区别是 groupby 即使没有过滤条件用到索引， 也可以直接使用索引。

但是如果断了，顺序不对，也会出现文件排序，并且还会出现Using temporary，使用临时表，这个可能会出现比文件排序还严重的性能问题。

Exists 和 IN

有以下sql是等价的，也就是查出的数据是一样的

select *from a where a.id in (select id from b)  #1

select *from a where exists (select 1 from b where a.id=b.id) #2

但是在不同情况的性能不一样，当a表数据大于b表时，用 in性能较好，当b表数据大于a表时，用exists性能较好。前提是要建立索引。