对于互联网公司来说随着用户量和数据量的不断增加慢查询是无法避免的问题。一般情况下如果出现慢查询意味着接口响应慢、接口超时等问题如果是高并发的场景可能会出现数据库连接被占满的情况直接导致服务不可用。1. 前言慢查询的确会导致很多问题我们要如何优化慢查询呢主要解决办法有监控sql执行情况发邮件、短信报警便于快速识别慢查询sql打开数据库慢查询日志功能简化业务逻辑代码重构、优化异步处理sql优化索引优化其他的办法先不说后面有机会再单独介绍。今天我重点说说索引优化因为它是解决慢查询sql问题最有效的手段。如何查看某条sql的索引执行情况呢没错在sql前面加上explain关键字就能够看到它的执行计划通过执行计划我们可以清楚的看到表和索引执行的情况索引有没有执行、索引执行顺序和索引的类型等。索引优化的步骤是使用explain查看sql执行计划判断哪些索引使用不当优化sqlsql可能需要多次优化才能达到索引使用的最优值既然索引优化的第一步是使用explain我们先全面的了解一下它。2. explain介绍先看看mysql的官方文档是怎么描述explain的EXPLAIN可以使用于 SELECT DELETE INSERT REPLACE和 UPDATE语句。当EXPLAIN与可解释的语句一起使用时MySQL将显示来自优化器的有关语句执行计划的信息。也就是说MySQL解释了它将如何处理该语句包括有关如何连接表以及以何种顺序连接表的信息。当EXPLAIN与非可解释的语句一起使用时它将显示在命名连接中执行的语句的执行计划。对于SELECT语句 EXPLAIN可以显示的其他执行计划的警告信息。3. explain详解explain的语法{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { EXTENDED | PARTITIONS | FORMAT format_name } format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement }用一条简单的sql看看使用explain关键字的效果explain select * from test1;执行结果从上图中看到执行结果中会显示12列信息每列具体信息如下说白了我们要搞懂这些列的具体含义才能正常判断索引的使用情况。话不多说直接开始介绍吧。3.1 id 列该列的值是select查询中的序号比如1、2、3、4等它决定了表的执行顺序。某条sql的执行计划中一般会出现三种情况id相同id不同id相同和不同都有那么这三种情况表的执行顺序是怎么样的呢id相同执行sql如下explain select * from test1 t1 inner join test1 t2 on t1.idt2.id结果我们看到执行结果中的两条数据id都是1是相同的。这种情况表的执行顺序是怎么样的呢答案从上到下执行先执行表t1再执行表t2。执行的表要怎么看呢答案看table字段这个字段后面会详细解释。id不同执行sql如下explain select * from test1 t1 where t1.id (select id from test1 t2 where t2.id2);结果我们看到执行结果中两条数据的id不同第一条数据是1第二条数据是2。这种情况表的执行顺序是怎么样的呢答案序号大的先执行这里会从下到上执行先执行表t2再执行表t1。id相同和不同都有执行sql如下explain select t1.* from test1 t1 inner join (select max(id) mid from test1 group by id) t2 on t1.idt2.mid结果我们看到执行结果中三条数据前面两条数据的的id相同第三条数据的id跟前面的不同。这种情况表的执行顺序又是怎么样的呢答案先执行序号大的先从下而上执行。遇到序号相同时再从上而下执行。所以这个列子中表的顺序顺序是test1、t1、也许你会在这里心生疑问derived2是什么鬼它表示派生表别急后面会讲的。还有一个问题id列的值允许为空吗答案在后面揭晓。3.2 select_type列该列表示select的类型。具体包含了如下11种类型但是常用的其实就是下面几个类型含义SIMPLE简单SELECT查询不包含子查询和UNIONPRIMARY复杂查询中的最外层查询表示主要的查询SUBQUERYSELECT或WHERE列表中包含了子查询DERIVEDFROM列表中包含的子查询即衍生UNIONUNION关键字之后的查询UNION RESULT从UNION后的表获取结果集下面看看这些SELECT类型具体是怎么出现的SIMPLE执行sql如下explain select * from test1;结果它只在简单SELECT查询中出现不包含子查询和UNION这种类型比较直观就不多说了。PRIMARY 和 SUBQUERY执行sql如下explain select * from test1 t1 where t1.id (select id from test1 t2 where t2.id2);结果我们看到这条嵌套查询的sql中最外层的t1表是PRIMARY类型而最里面的子查询t2表是SUBQUERY类型。DERIVED执行sql如下explain select t1.* from test1 t1 inner join (select max(id) mid from test1 group by id) t2 on t1.idt2.mid结果最后一条记录就是衍生表它一般是FROM列表中包含的子查询这里是sql中的分组子查询。UNION 和 UNION RESULT执行sql如下explain select * from test1 union select* from test2结果test2表是UNION关键字之后的查询所以被标记为UNIONtest1是最主要的表被标记为PRIMARY。而union1,2表示id1和id2的表union其结果被标记为UNION RESULT。UNION 和 UNION RESULT一般会成对出现。此外回答上面的问题id列的值允许为空吗如果仔细看上面那张图会发现id列是可以允许为空的并且是在SELECT类型为UNION RESULT的时候。3.3 table列该列的值表示输出行所引用的表的名称比如前面的test1、test2等。但也可以是以下值之一unionM,N具有和id值的行的M并集N。derivedN用于与该行的派生表结果id的值N。派生表可能来自例如FROM子句中的子查询 。subqueryN子查询的结果其id值为N3.4 partitions列该列的值表示查询将从中匹配记录的分区3.5 type列该列的值表示连接类型是查看索引执行情况的一个重要指标。包含如下类型执行结果从最好到最坏的的顺序是从上到下。我们需要重点掌握的是下面几种类型system const eq_ref ref range index ALL在演示之前先说明一下test2表中只有一条数据并且code字段上面建了一个普通索引下面逐一看看常见的几个连接类型是怎么出现的system这种类型要求数据库表中只有一条数据是const类型的一个特例一般情况下是不会出现的。const通过一次索引就能找到数据一般用于主键或唯一索引作为条件的查询sql中执行sql如下explain select * from test2 where id1;结果eq_ref常用于主键或唯一索引扫描。执行sql如下explain select * from test2 t1 inner join test2 t2 on t1.idt2.id;结果此时有人可能感到不解const和eq_ref都是对主键或唯一索引的扫描有什么区别答const只索引一次而eq_ref主键和主键匹配由于表中有多条数据一般情况下要索引多次才能全部匹配上。ref常用于非主键和唯一索引扫描。执行sql如下explain select * from test2 where code 001;结果range常用于范围查询比如between ... and 或 In 等操作执行sql如下explain select * from test2 where id between 1 and 2;结果index全索引扫描。执行sql如下explain select code from test2;结果ALL全表扫描。执行sql如下explain select * from test2;结果3.6 possible_keys列该列表示可能的索引选择。请注意此列完全独立于表的顺序这就意味着possible_keys在实践中某些键可能无法与生成的表顺序一起使用。如果此列是NULL则没有相关的索引。在这种情况下您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列从而提高查询性能。3.7 key列该列表示实际用到的索引。可能会出现possible_keys列为NULL但是key不为NULL的情况。演示之前先看看test1表结构test1表中数据使用的索引code和name字段使用了联合索引。执行sql如下explain select code from test1;结果这条sql预计没有使用索引但是实际上使用了全索引扫描方式的索引。3.8 key_len列该列表示使用索引的长度。上面的key列可以看出有没有使用索引key_len列则可以更进一步看出索引使用是否充分。不出意外的话它是最重要的列。有个关键的问题浮出水面key_len是如何计算的决定key_len值的三个因素1.字符集2.长度3.是否为空常用的字符编码占用字节数量如下目前我的数据库字符编码格式用的UTF8占3个字节。mysql常用字段占用字节数字段类型占用字节数char(n)nvarchar(n)n 2tinyint1smallint2int4bigint8date3timestamp4datetime8此外如果字段类型允许为空则加1个字节。上图中的 184是怎么算的184 30 * 3 2 30 * 3 2再把test1表的code字段类型改成char并且改成允许为空执行sql如下explain select code from test1;结果怎么算的183 30 * 3 1 30 * 3 2还有一个问题为什么这列表示索引使用是否充分呢还有使用不充分的情况执行sql如下explain select code from test1 where code001;结果上图中使用了联合索引idx_code_name如果索引全匹配key_len应该是183但实际上却是92这就说明没有使用所有的索引索引使用不充分。3.9 ref列该列表示索引命中的列或者常量。执行sql如下explain select * from test1 t1 inner join test1 t2 on t1.idt2.id where t1.code001;结果我们看到表t1命中的索引是const(常量)而t2命中的索引是列sue库的t1表的id字段。3.10 rows列该列表示MySQL认为执行查询必须检查的行数。对于InnoDB表此数字是估计值可能并不总是准确的。3.11 filtered列该列表示按表条件过滤的表行的估计百分比。最大值为100这表示未过滤行。值从100减小表示过滤量增加。rows显示了检查的估计行数rows× filtered显示了与下表连接的行数。例如如果 rows为1000且 filtered为50.0050则与下表连接的行数为1000×50 500。3.12 Extra列该字段包含有关MySQL如何解析查询的其他信息这列还是挺重要的但是里面包含的值太多就不一一介绍了只列举几个常见的。Impossible WHERE表示WHERE后面的条件一直都是false执行sql如下explain select code from test1 where a b;结果Using filesort表示按文件排序一般是在指定的排序和索引排序不一致的情况才会出现。执行sql如下explain select code from test1 order by name desc;结果这里建立的是code和name的联合索引顺序是code在前name在后这里直接按name降序跟之前联合索引的顺序不一样。Using index表示是否用了覆盖索引说白了它表示是否所有获取的列都走了索引。上面那个例子中其实就用到了Using index因为只返回一列code它字段走了索引。Using temporary表示是否使用了临时表一般多见于order by 和 group by语句。执行sql如下explain select name from test1 group by name;结果Using where表示使用了where条件过滤。Using join buffer表示是否使用连接缓冲。来自较早联接的表被部分读取到联接缓冲区中然后从缓冲区中使用它们的行来与当前表执行联接。4. 索引优化的过程1.先用慢查询日志定位具体需要优化的sql2.使用explain执行计划查看索引使用情况3.重点关注key查看有没有使用索引key_len查看索引使用是否充分type查看索引类型Extra查看附加信息排序、临时表、where条件为false等一般情况下根据这4列就能找到索引问题。4.根据上1步找出的索引问题优化sql5.再回到第2步