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

1. explain 介绍

explain（执行打算），应用 explain 关键字能够模仿优化器执行sql查问语句，从而晓得 MySQL 是如何解决sql语句。explain 次要用于剖析查问语句或表构造的性能瓶颈。

通过 explain + sql 语句能够晓得如下内容：

  1. 表的读取程序。（对应id）
  2. 数据读取操作的操作类型。（对应select_type）
  3. 哪些索引能够应用。（对应possible_keys）
  4. 哪些索引被理论应用。（对应key）
  5. 表间接的援用。（对应ref）
  6. 每张表有多少行被优化器查问。（对应rows）

explain 执行打算蕴含字段信息如下：别离是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra 12个字段。每个字段对应的介绍如下。能够先建几张表举例。
上面建表各自举例子：

xCREATE 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 AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE 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 AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE 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 AUTO_INCREMENT=61 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=utf8CREATE 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;#1000000select count(*) from course;      #100
1. 优先应用更快的索引（联结索引）

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

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

咱们通过建设索引来优化它的查问效率：

  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;

  2. 在一般索引的根底上，再减少联结索引：

    #name，course_id组成的联结索引create index idx_name_courseId on student_info(name,course_id);#该查问语句个别应用的是联结索引，而不是一般索引，具体看优化器决策#均匀耗时20msselect * 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);

  1. 联结索引全副匹配的状况：

    #关联字段的索引比拟残缺explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

    该sql语句合乎最左前缀准则，每个字段条件中的字段恰好和联结索引吻合。这种状况是最优的，因为依附一个联结索引就能够疾速查找，不须要额定的查问。

  2. 联结索引最左边缺失的状况：

    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会减小。

  3. 联结索引两头缺失的状况：

    #联结索引两头的字段未应用，而右边和左边的都存在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的记录。多了一个全文搜寻的步骤，相比于①和②状况性能会更差。

  4. 联结索引最右边缺失的状况：

    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:5explain 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、!=这些判断条件常常会呈现在这些回表范畴很大的场景，而后被人误会为是这些判断条件导致的索引生效。

8. like以%结尾，索引生效
#应用到了索引explain select * from student_info where name like 'li%';#索引生效explain select * from student_info where name like '%li';

只有以%结尾就无奈应用索引，因为如果以%结尾，在B树排序的数据中并不好找。

9. 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;
10. 字符集不对立

字符集如果不同，会存在隐式的转换，索引也会生效，所有应该应用雷同的字符集，避免这种状况产生。

4. 高效应用索引

3.1. 防止回表

1. 什么是回表查问

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

具体解释：

  • 一般索引： 咱们本人建的索引不论是单列索引还是联结索引，都称为一般索引，每个一般索引就对应着一颗独立的索引B+树，索引 B+ 树的节点仅仅蕴含了索引里的几个字段的值以及主键值。
  • 聚簇索引： 主键索引是聚簇索引，也就是索引的叶子节点存的是整个单条记录的所有字段值。

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

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

简略说就是， 索引列+主键 蕴含 select 到 from之间查问的列 。就是索引笼罩。能够不必去进行回表操作。

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

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

3.2. 怎么建联结索引

1. 联结索引的劣势

相较于一般的单列索引而言，联结索引的劣势如下：

  1. 如果正确应用，一个联结索引能够抵得上多个单列索引。建了一个(a,b,c)的复合索引，那么理论等于建了(a),(a,b),(a,b,c)三个索引。因为每多一个索引，都会减少写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！
  2. 笼罩索引。同样的有复合索引 (a,b,c)，如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL能够间接通过遍历索引获得数据，而无需回表，这缩小了很多的随机io操作。缩小io操作，特地的随机io其实是dba次要的优化策略。所以，在真正的理论利用中，笼罩索引是次要的晋升性能的优化伎俩之一。
  3. 索引列越多，通过索引筛选出的数据越少。有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;