mysql性能索引调优易混点总结

一、 前言

近几年看了很多和mysql相关的书，文章或视频，但仍然有一些点，看过之后又忘记了，这里做一些笔记来总结一下。

二、explain相关

1. explain解析后，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
2. select_type列代表的是对应行是简单还是复杂查询。
   1）simple：简单查询。查询不包含子查询和union
   2）primary：复杂查询中最外层的 select
   3）subquery：包含在 select 中的子查询（不在 from 子句中）
   4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
3. 当type是index的时候，代表扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
4. 可以通过key_len来判断使用了是吗索引，char(n)和varchar(n)中n代表的是字符数，而不是字节数。
   varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串。

三、索引优化相关

联合索引

1. 联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。当然可以使用forceidex强制走索引，但是效率不一定高。可以用覆盖索引优化。
2. in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描。

索引下推

• **好处:**索引下推主要是为了减少回表次数，并且只能用于二级索引，innodb主键索引保存的是全行数据，索引下推不会减少回表次数。
• **原理:**比如对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则
  SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'
  这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
  在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
  MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

排序和分组相关优化

4. 在order by和group by中，也能使用到索引，但不会显示在key_len字段中，会在extra中有显示，比如当某个字段用在排序，那么额外字段里没有using filesort。
5. 对于排序来说，多个相等条件(in)，也是范围查询，也会出现using filesort。

分页优化

EXPLAIN select * from employees ORDER BY name limit 90000,5;

• 对于深度分页，比如limit 10000,10，mysql会查询出前面10010条数据，并舍弃掉10000条数据，只要后面的10条数据这样效率很低。
• 如果是连续的，可以用 id>10000 limit 5实现。但弊端很大，要保证数据连续，还要保证如果排序了，排序的时候是按照主键排序的。（扫描整个索引并查找到没索引的行，可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。）
• 如何优化？
  其实关键是让返回的数据尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下
  select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

这样避免了filesort文件排序，还走了索引。

表关联优化

• 执行计划中首先执行的是驱动表，后执行的是被驱动表，驱动表一般数量级少。 使用 inner join 时，排在前面的表并不一定就是驱动表。
• 当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

嵌套循环连接 Nested-Loop Join(NLJ) 算法

前提：t2表100行数据，t1表1万行数据。

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join算法。
如果上面使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

从表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集（应建立索引）
in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)
先执行B，B相当于小表，B表数据越少，执行越快