Mysql索引优化实战

简单介绍一下表结构：

• 表名：employees
• 列：id、name、age、position、hire_time
• 主键：id
• 索引：联合索引      idx_name_age_position （`name`, `age`, `position`）

联合索引第一个字段用范围可能不走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 
AND position ='manager';

正常来说，一个联合索引，如果使用了范围查询，那么应该使用到用作范围的那一个索引。但是这时候mysql底层会进行优化，发现因某些原因如回表过多造成不走索引可能实际效率会高一些，就直接全表扫描

in和or可能会导致全表扫描

在表数据量比较大且in的值较少的情况会走索引，在表记录不多的情况下会选择全表扫描，在in（）中有大量的值也会导致全表扫描

like的%写在左边会导致索引失效

在实际开发中，最好将%右边，因为左边相当于还要扫描所有索引。而写在右边例如k%或者k%k都能往下使用索引，这实际涉及到了索引下推

这里给出一条sql ，SELECT * FROM employees WHERE name like 'LiLei%'AND age = 22 AND position ='manager'，在mysql5.6之前和之后执行的流程是不一样的

在mysql5.6之前，会先找出所有name以LiLei开头的数据，然后根据主键到集聚索引中找出所有的数据，然后再比对age和poosition。

在mysql5.6之后，使用了索引下推，，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可 以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过 滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据

order by 索引失效

当联合索引未使用到前列索引，直接用后面的索引进行排序，那么索引是失效的。因为前列索引不确定的情况下，后面的索引实际上是无序的，只有当前列索引确定了，后面的索引才是有序的，这时候才可以根据索引进行排序

索引：联合索引      idx_name_age_position （`name`, `age`, `position`）

--------------------------------------------------------------------------------------------------------------------------

 这里可以看的，前面使用了name索引，于是也使用age索引进行排序，因为Extra并没有出现using filesort。

--------------------------------------------------------------------------------------------------------------------------

这里可以看的，前面使用了name索引，但是没有使用age索引，直接使用positon进行排序，在age没确定的情况下position是无序的，所以这里是通了using filesort。

--------------------------------------------------------------------------------------------------------------------------

 

 这里是先使用了name所以，然后先根据age进行排序，当age相同的时候再根据position进行排序，所以都使用到了所以。

--------------------------------------------------------------------------------------------------------------------------

这里先根据position进行排序，position相同再根据age排序，但是这里并没有先使用age索引，也就是说age不确定的情况，此时position是无序的，就不能够按照索引排序，所以还是使用了using filesort

 --------------------------------------------------------------------------------------------------------------------------

此时name和age已经确定，所以position是有序的，走索引，而后面再按照age排序，这个条件实际是可以忽略，因为age=18，已经是一个常量，这个排序无意义

  --------------------------------------------------------------------------------------------------------------------------

这里虽然都按照了索引的顺序进行排序，但是position的索引是按照升序的顺序进行排序的，而这里使用了降序，与索引树想反，索引还是不起作用了，于是有了using filesort

  --------------------------------------------------------------------------------------------------------------------------

  在order by 中，前面的索引使用了in 就相当于范围查找，因为前面不是确定的值，所以age并不是有序的，还是使用了using filesort

  --------------------------------------------------------------------------------------------------------------------------

 这里按正常逻辑来看，找到name>a的部分，都是有序的，按理来说应该是要走索引排序的，但是这里却还是选择了using filesort，因为！ mysql认为，这个数据量太大了，并且查询的是所有字段，最终还要回表查询，所以他干脆就全表扫描。可以通过覆盖索引来优化，当不用回表了就可与直接使用索引排序了

   --------------------------------------------------------------------------------------------------------------------------

总结：

1、MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL 扫描索引本身完成排序 。index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

   1) order by语句使用 索引最左前列 。

   2) 使用where子句与order by子句 条件列组合满足索引最左前 列。

3、尽量在 索引列 上完成排序，遵循 索引建立（索引创建的顺序） 时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先 排序后分组 ，遵照 索引创建顺序 的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序 。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

limit优化

limit是mysql在分页时使用的，虽然每次展示的数据都不多，但是性能是非常低下的。例如我们在这里执行这条语句 SELECT * FROM `employees`  LIMIT 1000000,10，他实际是是先找出1000010条数据，然后把前面1000000条抛弃掉，只展示后面10条。相当于数据量很大的查询。所以我们有必要做出优化

方式一：当主键id为递增并且连续的情况下，可以使用id做范围查找

SELECT * FROM employees  90000 LIMIT 90000, 5  的效果实际上等于  SELECT * FROM employees WHERE id > 90000 LIMIT 5 。这里走了主键索引，避免了回表，也筛除了大量数据。

但是使用条件较为苛刻，必须要求按照id排序并且id递增且中间不会删除数据，不然结果集不相同

方式二：使用子查询覆盖索引查出九万行到九万零五行的id

工作中大多数的需求都不会要求按照主键排序，例如这里

 由于回表次数太多，这里也走了全表扫描。可以这样优化

 join优化

join用于连接两个表，首先先了解一下mysql常用的两种表关联算法

Nested-Loop Join 算法 ，Block Nested-Loop Join 算法

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

原理：一次一次的从驱动表中读取行，根据关联字段在被驱动表中取出满足条件的行，最终取出结果合集。

情景：这种情况一般出现于被驱动表有索引，并且使用了索引作为连接条件。

从执行计划中可以看到这些信息：

驱动表是 t2（一百条数据)，被驱动表是 t1(一万条数据)。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。 当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表， 当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算 法是 NLJ。

执行流程：

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

整个过程会读取 t2 表的所有数据( 扫描100行 )，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行 )。因此整个过程扫描了 200 行 。

如果被驱动表的关联字段没索引， 使用NLJ算法性能会比较低(下面有详细解释) ，mysql会选择Block Nested-Loop Join算法。

2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法 

 原理：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，顺带提一下，join_buffer中的数据是无序的

情景：这种情况一般出现于被驱动表不以索引作为连接条件。

上面sql的大致流程如下：

• 1. 把 t2 的所有数据放入到 join_buffer 中
• 2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
• 3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100 。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次 。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单， 就是分段放 。

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然 后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再 次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是 磁盘扫描 。

很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高

对于关联sql的优化

关联字段加索引 ，让mysql做join操作时尽量选择NLJ算法

小表驱动大表 ，写多表连接sql时如果 明确知道 哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间

straight_join解释：straight_join 功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。 比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。 straight_join 只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用 straight_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

对于小表定义的明确

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤， 过滤完成之后 ，计算参与 join 的各个字段的总数据 量，数据量小的那个表，就是“小表” ，应该作为驱动表。

in和exsits优化

原则： 小表驱动大表 ，即小的数据集驱动大的数据集

in： 当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)2

#等价于：

for(select id from B){

select * from A where A.id = B.id

}

exists： 当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id)

#等价于:

for(select * from A){

select * from B where B.id = A.id

}

D字段应建立索引

• 1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
• 2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
• 3、not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

count（）的选择

• 这里要比较的是count（*）、counr（1）、count（字段）、count（主键）
• 在字段有索引的情况下效率是count（*）≈count（1）>count（字段）>count（主键）
• 在字段无索引的情况下效率是count（*）≈count（1）>count（字主键）>count（字段）

说明：

•         count（字段）和count（主键）的分析：在字段是索引的情况下，可以走索引 统计，而二级索引会比主键索引小很多，所以这种情况下count（字段）肯定会快一些。在字段不是索引的情况下，count（字段）就不能走索引了，而count（主键）还能走主键索引，索引count（主键)效率自然高一些。后面对count（主键）也做出了优化（大概是5.7版本之后），如果有二级索引存在，那么count（主键）实际上会扫描二级索引。
•         count（1）和count（字段）的你分析：两者的执行过程类似，区别在于count（字段）需要取出索引字段，然后再进行+1操作，而count（1）不需要读出字段，而是如果扫描到了这个索引，直接就+1。所以count（1）的效率会比count（字段）要高一些。
•         count（*）分析：count（*）并不会想显示看到的那样，找出所有字段，mysql内部对count（*）做出了专门的优化，是逐行+1，所以性能与count（1）差不多
• 在一般情况下，如果字段是索引，这四种的性能其实差不大多，硬是要分个优劣就根据上面的分析

以上的分析都是针对innodb存储引擎，而对于myisam存储引擎来说，直接使用count（*）效率是非常非常非常快的，因为他专门用一个变量存储总行数，所以实际就相当与查找一个变量的值，而innodb因为存在事务这个概念，并且需要维护mvcc，所以维护总行数的代价太大，就没有设置变量存储总行数。不过我们仍然有方式快速获取总行数

1、如果想知道总行数的预估值，可以通过这种方式：

show table status LIKE 'tableName'

 2、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难 保证表操作和redis操作的事务一致性

3、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，就是建立一个表，专门用来记录各个表的总行数，一行数据就代表一张表的总行数，让他们在同一个事务里操作（当有增加和删除的时候就对应的+1和-1）

索引的建立原则

1、代码先完善后再建立索引

对于索引的建立并不是表建立了之后就建立索引。我们应该要等到业务代码都开发的差不多了，然后把所有与这张表相关的sql都罗列出来综合分析后再建立索引

2、联合索引尽量覆盖条件，尽量不要建立单值索引

我们可以设计一个或两三个联合索引，让每一个索引都尽量去包含sql语句里的where，order by，group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。除了唯一索引，尽量不要建立其他单值索引，一个是占用空间较多，最主要的是，多个查询条件只能同时匹配一个索引，筛选力度不够。

3、不要在小基数字段上面建索引

例如一个表里有一百万条数据，其中有个性别字段，不是男就是女。假设男女各占一般，二分查找也只筛选了50万条记录。工作量还是很大。建立索引一般使用基数较大的字段，这样才能充分发挥b+tree的查找优势

4、mysqk5.6以后可以采用前缀索引优化长字符串索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。

当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。

对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY

index(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name 字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来 完整的name字段值进行比对。

但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排 序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

5、where与order by冲突时优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到 底是让where去用上索引，还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。

因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可 能会小很多。

索引规约

1、业务上具有唯一特性的索引，即使是组合字段，也要设置成唯一索引

2、超过三个表禁止使用jion。使用join时连接条件必须要整类型相同，被关联的字段必须有索引

3、在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分决定索引长度

4、like禁止使用左模糊或者全模糊

5、如果有 order by 的场景，请注意利用索引的有序性  

6、 利用覆盖索引来进行查询操作，避免回表。

7、 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts

最好

8、防止因字段类型不同造成的隐式转换，导致索引失效。