mysql索引优化总结

最近一周的工作都集中在慢sql的治理上，大部分都是基于索引进行优化，所以做了下述的总结。

1. explain 介绍

explain（执行计划），使用 explain 关键字可以模拟优化器执行sql查询语句，从而知道 MySQL 是如何处理sql语句。explain 主要用于分析查询语句或表结构的性能瓶颈。

通过 explain + sql 语句可以知道如下内容：

表的读取顺序。（对应id）
数据读取操作的操作类型。（对应select_type）
哪些索引可以使用。（对应possible_keys）
哪些索引被实际使用。（对应key）
表直接的引用。（对应ref）
每张表有多少行被优化器查询。（对应rows）

explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra 12个字段。每个字段对应的介绍如下。可以先建几张表举例。
下面建表各自举例子：

CREATE TABLE `blog` (
  `blog_id` int NOT NULL AUTO_INCREMENT COMMENT '唯一博文id--主键',
  `blog_title` varchar(255) NOT NULL COMMENT '博文标题',
  `blog_body` text NOT NULL COMMENT '博文内容',
  `blog_time` datetime NOT NULL COMMENT '博文发布时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `blog_state` int NOT NULL COMMENT '博文状态--0 删除 1正常',
  `user_id` int NOT NULL COMMENT '用户id',
  PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户唯一id--主键',
  `user_name` varchar(30) NOT NULL COMMENT '用户名--不能重复',
  `user_password` varchar(255) NOT NULL COMMENT '用户密码',
  PRIMARY KEY (`user_id`),
  KEY `name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `discuss` (
  `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论唯一id',
  `discuss_body` varchar(255) NOT NULL COMMENT '评论内容',
  `discuss_time` datetime NOT NULL COMMENT '评论时间',
  `user_id` int NOT NULL COMMENT '用户id',
  `blog_id` int NOT NULL COMMENT '博文id',
  PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. id

表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。针对下面sql例子：

explain select discuss_body 
from discuss 
where blog_id = (
    select blog_id from blog where user_id = (
        select user_id from user where user_name = 'admin'));

三个表依次嵌套，发现按照id从小到大排序的table值依次是：discuss -> blog -> user 。

2. select_type

表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

SIMPLE：表示最简单的 select 查询语句，在查询中不包含子查询或者交并差集等操作。
PRIMARY：查询中最外层的select（存在子查询的外层的表操作为PRIMARY）。
SUBQUERY：子查询中首个select。
DERIVED：被驱动的select子查询（子查询位于from子句）。
UNION：在select之后使用了UNION。

3. table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。当from子句中有子查询时，table列是 <derivenN>的格式，表示当前查询依赖 id为N的查询，会先执行 id为N的查询。

4. partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

5. type

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标。

system: 当表仅有一行记录时（系统表），数据量很少，往往不需要进行磁盘IO，速度非常快。比如，Mysql系统表proxies_priv在Mysql服务启动时候已经加载在内存中，对这个表进行查询不需要进行磁盘 IO。
```
explain select * from mysql.proxies_priv;
```
const: 单表操作的时候，查询使用了主键或者唯一索引。
```
explain select * from user where user_id=1;
```
eq_ref: 多表关联查询的时候，主键和唯一索引作为关联条件。如下图的sql，对于user表（外循环）的每一行，user_role表（内循环）只有一行满足join条件，只要查找到这行记录，就会跳出内循环，继续外循环的下一轮查询。
```
explain select u.user_name from user u,user_role ur where u.user_id= ur.user_id;
```
ref: 查找条件列使用了索引而且不为主键和唯一索引。虽然使用了索引，但该索引列的值并不唯一，这样即使使用索引查找到了第一条数据，仍然不能停止，要在目标值附近进行小范围扫描。但它的好处是不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内做扫描。
```
explain select user_id from user where user_name='admin';
```
ref_or_null: 类似 ref，会额外搜索包含NULL值的行。
index_merge: 使用了索引合并优化方法，查询使用了两个以上的索引。新建comment表，id为主键，value_id为非唯一索引，执行explain select content from comment where value_id = 1181000 and id > 1000;，执行结果显示查询同时使用了id和value_id索引，type列的值为index_merge。
range: 有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。像between、and、'>'、'<'、in和or都是范围索引扫描。
```
explain select * from user where user_id>0;
```
index: index包括select索引列，order by主键两种情况。
（1）order by主键。这种情况会按照索引顺序全表扫描数据，拿到的数据是按照主键排好序的，不需要额外进行排序。
```
explain select * from user order by user_id;
```
（2）select索引列。type为index，而且extra字段为using index，也称这种情况为索引覆盖。所需要取的数据都在索引列，无需回表查询。
```
explain select user_id from user_id;
```
all: 全表扫描，查询没有用到索引，性能最差。
```
explain select user_id from user;
```
6. possible_keys

此次查询中可能选用的索引。但这个索引并不定一会是最终查询数据时所被用到的索引。

7. key

此次查询中确切使用到的索引。

8. key_len
9. ref
10. rows

估算要找到所需的记录，需要读取的行数。评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。

11. filtered

存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

12. extra

表示额外的信息说明。下面建两张表来举例：

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `order_status` tinyint DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8

CREATE TABLE `t_orderdetail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int DEFAULT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `cnt` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8

using where: 查询的列未被索引覆盖，where筛选条件非索引的前导列。对存储引擎返回的结果进行过滤（Post-filter，后过滤），一般发生在MySQL服务器，而不是存储引擎层。
```
explain select order_id,order_status from t_order where order_id=1;
```
using index: 查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。
```
explain select user_id,order_id,create_date from t_order where user_id=1;
```
Using where&Using index: 查询的列被索引覆盖，但无法通过索引查找找到符合条件的数据，不过可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

包括两种情况：
（1）where筛选条件不符合最左前缀原则

explain select user_id,order_id,create_date from t_order where order_id=1;

（2）where筛选条件是索引列前导列的一个范围

explain select user_id,order_id,create_date from t_order where user_id>1;

null: 查询的列未被索引覆盖，并且where筛选条件是索引的前导列，也就是用到了索引，但是部分字段未被索引覆盖，必须回表查询这些字段，Extra中为NULL。
```
explain select user_id,order_id,order_status from t_order where user_id=1;
```
using index condition: 索引下推（index condition pushdown，ICP），先使用where条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 where 子句中的其他条件去过滤这些数据行。
using temporary: 使用了临时表保存中间结果，常见于 order by 和 group by 中。典型的，当group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。
filesort: 文件排序。表示无法利用索引完成排序操作，以下情况会导致filesort：
- order by 的字段不是索引字段
- select 查询字段不全是索引字段
- select 查询字段都是索引字段，但是 order by 字段和索引字段的顺序不一致
```
explain select * from t_order order by order_id;
```
using join buffer: Block Nested Loop，需要进行嵌套循环计算。两个关联表join，关联字段均未建立索引，就会出现这种情况。比如内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

2. 索引失效场景

同样提前建表用于演示：

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

#准备数据
select count(*) from student_info;#1000000
select count(*) from course;      #100

1. 优先使用更快的索引（联合索引）

如下一条sql语句是没有索引的情况：

select * from student_info where name='123' and course_id=1 and class_id=1;

我们通过建立索引来优化它的查询效率：

建立普通索引：

#建立普通索引
create index idx_name on student_info(name);
#平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

在普通索引的基础上，再增加联合索引：

#name，course_id组成的联合索引
create index idx_name_courseId on student_info(name,course_id);
#该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策
#平均耗时20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

通过执行计划结果可以看到，在多个索引都可以使用时，系统一般优先使用更长的联合索引，因为联合索引相比来说更快，这点应该也很好理解，前提是要遵守联合索引的最左匹配原则。
如果再创建一个name,course_id,class_id组成的联合索引，那么上述sql语句不出意外会使用这个key_len更长的联合索引(意外是优化器可能会选择其他更优的方案，如果它更快的话)。

联合索引速度不一定优于普通索引，比如第一个条件就过滤了所有记录，那么就没必要用后序的索引了。

2. 最左匹配原则

删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id

create index idx_name_cou_cls on student_info(name,course_id,class_id);

联合索引全部匹配的情况：
```
#关联字段的索引比较完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;
```
该sql语句符合最左前缀原则，每个字段条件中的字段恰好和联合索引吻合。这种情况是最优的，因为依靠一个联合索引就可以快速查找，不需要额外的查询。
联合索引最右边缺失的情况：
```
explain select * from student_info where name='11111' and course_id=10068;
```
该sql语句条件中，并不含有联合索引的全部条件，而是抹去了右半部分，该语句使用的索引依旧是该关联查询，只不过只用到了一部分，通过查看key_len可以知道少了5字节，这5字节对应的是class_id，证明class_id并未生效而已(where中没有，当然用不到啦)。
同理，抹掉where中的course_id字段，联合索引依旧会生效，只是key_len会减小。
联合索引中间缺失的情况：
```
#联合索引中间的字段未使用，而左边和右边的都存在
explain select * from student_info where name='11111' and class_id=10154;;
```
如上sql语句依旧使用的是联合索引，但是它的key_len变小了，只有name字段使用到了索引，而class_id字段虽然在联合索引中，但是因为不符合最左匹配原则而GG了。
整个sql语句的执行流程为：先在联合索引的B树中找到所有name为11111的记录，然后全文过滤掉这些记录中class_id不是10154的记录。多了一个全文搜索的步骤，相比于①和②情况性能会更差。
联合索引最左边缺失的情况：
```
explain select * from student_info where class_id=10154 and course_id=10068;
```
该情况是上一个情况的特例，联合索引中最左边的字段未找到，所以虽然有其他部分，但是统统都失效了，走的是全文查找。

结论：最左匹配原则指的是查询从索引的最左列开始，并且不能跳过索引中的列，如果跳过了某一列，索引将部分失效(后面的字段索引全部失效)。

注意：创建联合索引时，字段的顺序就定格了，最左匹配就是根据该顺序比较的；但是在查询语句中，where条件中字段的顺序是可变的，意味着不需要按照关联索引字段的顺序，只要where条件中有就行了。

3. 范围条件右边的列索引失效

承接上面的联合索引，使用如下sql查询

#key_len=> name:63,course_id:5,class_id:5
explain select * from student_info where name='11111' and course_id>1 and class_id=1;

执行计划中key_len只有68，代表关联索引中class_id未使用到，虽然符合最左匹配原则，但因为>符号让关联索引中该条件字段右边的索引失效了。

但如果使用>=号的话

#不是>、<,而是>=、<=
explain select * from student_info where name='11111' and course_id>=20 and course_id<=40 and class_id=1;

右边的索引并未失效，key_len为73，所有字段的索引都使用到了。

结论：为了充分利用索引，我们有时候可以将>、<等价转为>=、<=的形式，或者将可能会有<、>的条件的字段尽量放在关联索引靠后的位置。

4. 计算、函数导致索引失效

#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';
#类似的也不会使用索引
explain select * from student_info where name+''='lisi';

结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。 字段能不用就不用函数。

5. 类型转换导致索引失效

#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。

6. 不等于(!= 或者<>)通常索引失效

#创建索引
create index idx_name on student_info(name);
#索引失效
explain select * from student_info where name<>'zhangsan';
explain select * from student_info where name!='zhangsan';

不等于的情况通常是不会使用索引的，标题里面也提到了通常。不走索引的原因，是因为通常来说不等于的条件筛选的数据太多，还不如全表扫描的效率高。如果说不等于筛选的数据很少，则依然可以生效。

7. is (not) null 回表多，索引失效

is null / is not null 本身是支持走索引的，但在多数场景中的确没有走索引，为何大众误解认为is null、is not null、!=这些判断条件会导致索引失效而全表扫描呢？

导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值可以简单得记忆为20%左右。

也就是如果一条查询语句导致的回表范围超过全部记录的20%，则会出现索引失效的问题。而is null、is not null、!=这些判断条件经常会出现在这些回表范围很大的场景，然后被人误解为是这些判断条件导致的索引失效。

注意，这里的理由是“回表数据过多”。因此如果 is (not) null 查询出的数据很少，或无需回表，则依然会走索引。

8. in / not in

in 本身是可以走索引的，但当in 的范围太多，索引也无法带来提示，就会导致索引失效，走全表扫描。一般这个范围的极限约为30%，当in的数据超过30%的数据，通常会导致索引失效。
not in 和前文的 is (not) null 一样，当回表数据太多就不会走索引，如果数据量不大，并且能保证走覆盖索引，就依然可以走索引。

9. like以%开头，索引失效

#使用到了索引
explain select * from student_info where name like 'li%';
#索引失效
explain select * from student_info where name like '%li';

只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。

10. OR前后存在非索引的列，索引失效

#创建好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果or前后都是索引，则正常走索引：

#使用索引
explain select * from student_info where name like 'li%' or course_id=200;

如果其中一个没有索引，那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。

explain select * from student_info where name like 'li%' or class_id=1;

11. 字符集不统一

字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。

3. 高效使用索引

3.1. 避免回表

1. 什么是回表查询

一般情况下是：先到普通索引上定位主键值，再到聚集索引上定位行记录，它的性能较扫一遍索引树低。

具体解释：

普通索引： 我们自己建的索引不管是单列索引还是联合索引，都称为普通索引，每个普通索引就对应着一颗独立的索引B+树，索引 B+ 树的节点仅仅包含了索引里的几个字段的值以及主键值。
聚簇索引： 主键索引是聚簇索引，也就是索引的叶子节点存的是整个单条记录的所有字段值。

在什么情况会出现回表操作呢？举个例子：假设表tbl有a,b,c三个字段，其中 a是主键，b上建了索引。

当编写sql语句select * from tbl where a=1;这样不会产生回表，因为所有的数据在a的索引树中均能找到；
当编写sql语句select a,b from tbl where b=1;这样也不会产生回表，因为a、b数据在b的索引树中也都能找到；
但如果是select * from tbl where b=1;这样就会产生回表。因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，就出现了回表操作。

2. 什么是索引覆盖？

简单说就是，索引列+主键包含 select 到 from之间查询的列。就是索引覆盖。可以不用去进行回表操作。

3. 为什么设置了命中了索引但还是造成了全表扫描

就是虽然命中了索引，但在叶子节点查询到记录后还要大量的回表，优化器认为不如直接去扫描全表。

3.2. 怎么建联合索引

1. 联合索引的优势

相较于普通的单列索引而言，联合索引的优势如下：

如果正确使用，一个联合索引可以抵得上多个单列索引。建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引。因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！
覆盖索引。同样的有复合索引 (a,b,c)，如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据。
（1）如果只有单列索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；
（2）如果是联合索引，通过索引筛选出1000w 10% 10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

2. 联合索引的字段顺序

大家都知道联合索引的最左匹配原则，因此创建联合索引时，如何保障索引中字段的顺序就很关键。

个人总结经验：分析表结构的业务查询需求，找出查询优先级从高到低的字段，在索引中从左往右。

例如：我们在做toB的项目，需要对不同客户机构做数据隔离，就要求所有建表时都包含org_id字段，所有查询都要过滤。因此优先级最高的就是“机构ID”；然后这是张机构导航表，几乎所有查询都需要根据导航组过滤，因此第二优先级就是“导航组ID”；其次再是“导航编号”或“导航名称”等。所以建联合索引时必须要保证是 (“机构ID”,“导航组ID”,...) 开头，以保障能让绝大多数的查询能尽可能匹配该索引更多的字段。

3.3. 索引优化排序

通过索引优化来实现MySQL的order by语句优化：

1.无 where 排序

order by的索引优化。如果一个SQL语句形如：

select [column1],[column2],…. from [TABLE] order by [sort];

在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2. where 一个字段排序

order by的索引优化。如果一个SQL语句形如：

select [column1],[column2],…. from [TABLE] where [columnX] = [value] order by [sort];

建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化！

select [column1],[column2],…. from [TABLE] where [columnX] IN ([value1],[value2],…) order by[sort];

3. where 多个字段排序

select * from [table] where uid=1 ORDER x,y LIMIT 0,10;

建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

MySQL order by 不能使用索引来优化排序的情况：

对不同的索引键做 order by ：(key1,key2分别建立索引)
```
select * from t1 order by key1, key2;
```
在非连续的索引键部分上做 order by：(key_part1,key_part2建立联合索引;key2建立索引)
```
select * from t1 where key2=constant order by key_part2;
```
同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)。
```
select * from t1 order by key_part1 DESC, key_part2 ASC;
```
用于搜索记录的索引键和做 order by 的不是同一个：(key1,key2分别建立索引)。
```
select * from t1 where key2=constant order by key1;
```
如果在where和order by的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化。
```
select * from t1 order by YEAR(logindate) LIMIT 0,10;
```