一、前语

前段时刻自己做项目，项目用到了MySQL技术栈，主要是用MySQL记录使命信息，后面在压测的时分，发现一个接口的QPS一直上不去，后来经过排查发现，该接口在运转的过程中运用了慢查询句子，经过排查慢查询句子，终究发现是没有对字段树立联合索引，在树立了联合索引之后，功能提升了将近两倍。此文记录自己踩过的一些坑，以及解决方案。

二、怎么找到慢SQL句子

MySQL供给了慢查询日志，能够帮助咱们找到慢查询句子。

2.1 什么是慢查询日志？

MySQL的慢询日志，能够记录在MySQL中，响应时刻超越指定阈值的句子，比方设定阈值为3秒，那么任何SQL履行超越3秒都会被记录下来。

咱们借助慢查询日志功用能够发现哪些那些履行时刻特别长的询，并且有针对性地进行优化，从而提高系统的全体功率。

2.2 怎么敞开慢查询日志

慢查询日志一般默许是封闭的，由于敞开慢查询日志会对功能形成必定的影响，咱们只需求在功能调优的时分敞开就能够了，所以慢查询日志需求咱们自己去敞开。

履行下面指令查看是否敞开慢SQL：

假如是off便是没敞开，是on便是敞开了。

怎么经过指令敞开慢查询日志？

 set global slow_query_log='ON';

2.3 怎么设置慢查询日志的阈值

set global long_query_time = N;

这里的单位是秒，设置为N便是N秒，一切履行时刻超越N秒的sql句子都会被记录在慢查询日志里。

2.4 怎么查看慢查询日志的内容

先找到慢查询日志的方位：

show variables like '%slow_query_log_file%';

查看日志内容能够运用cat指令来查看：

cat xxx.log

三、explain剖析工具的运用

explain是MySQL自带的履行剖析工具，把他加在sql句子前面，就能够展示出该sql句子的详细的履行计划。

履行计划是依照多个字段来展示的，咱们平常在调优的时分主要重视以下几个要害字段：type，possible keys，key，rows，filtered，extra。下面我会一一介绍这些字段的详细意义。

3.1 type字段

type字段表明当时句子所履行的类型，有如下几种类型：

• all：全表扫描，依据表中一切的数据，逐行扫描并过滤符合条件的数据。

• index：全索引扫描，和全表扫描类似，但这个是把索引树遍历一次，会比全表扫描要快。

• range：依据索引字段进行范围查询，如between、<、>、in....等操作时呈现的状况。

• index_subquery：和上面意义相同，区别：这个是依据非主键、仅有索引字段进行in操作。

• unique_subquery：履行依据主键索引字段，进行in操作的子查询句子会呈现的状况。

• index_merge：多条件查询时，组合运用多个索引来检索数据的状况。

• ref_or_null：依据次级(非主键)索引做条件查询时，该索引字段答应为null呈现的状况。

• fulltext：依据全文索引字段，进行查询时呈现的状况。

• ref：依据非主键或非仅有索引字段查找数据时，会呈现的状况。

• eq_ref：连表查询时，依据主键、仅有索引字段匹配数据的状况，会呈现多次索引查找。

• const：经过索引一趟查找后就能获取到数据，依据仅有、主键索引字段查询数据时的状况。

• system：表中只要一行数据，这是const的一种特例。

• null：表中没有数据，无需经过任何数据检索，直接回来成果。

字段许多，常用的有下面几个，功能由好到坏依次是：const->eq_ref->ref->fulltext->range->index->all

平常索引优化，最差也要优化到range，假如是index甚至是all，是不答应的，太慢了。

3.2 key字段

possible keys表明能够运用的索引，key是终究优化器挑选运用的索引。留意，并不是有索引优化器就会挑选运用索引，优化器会预算查询的cost，挑选最低的那个查询方法。假如索引的基数太大，也有或许不会选用，由于运用二级索引意味着要回表，回表也是一个很大的开支。

默许状况下，possible_keys有值时都会从中选取一个索引，但这个挑选的工作是由MySQL优化器自己决定的，假如你想让查询句子履行时走固定的索引，则能够经过force index、ignore index的方法强制指定。

3.3 rows字段

这一列代表履行时，预计会扫描的行数，这个数字是预算的，所以并不是十分准确，但也具有很大的参考价值，假如这个值很大，在履行查询句子时，其功率必定很低，所以该值越小越好。

3.4 filtered 字段

这个字段仅作参考。filter字段的意义是，存储引擎回来的数据在server层过滤后，剩余的满足查询条件的记录数量占存储引擎回来数据的百分比，因而这个值越大阐明过滤掉的越少，就“越好”。但明显，filtered假如很小，只能阐明被过滤的多了，并不必定阐明索引欠好或许引擎功率不高，所以这个值实质上参考意义不大。

3.5 extra字段

extra包括的是额外的信息，可是这个信息对sql优化是很重要的。

可是这个字段的值有许多许多，下面列举了一些：

• Using index：表明目前的查询句子，运用了索引掩盖机制拿到了数据。

• Using where：表明目前的查询句子无法从索引中获取数据，需求进一步做回表去拿表数据，也便是回表。一般呈现这种状况的话就需求增加合适的索引来做优化了。

• Using temporary：表明MySQL在履行查询时，会创立一张暂时表来处理数据，MySQL需求创立一个暂时表来保存成果。常常呈现在查询包括以不同方法列出列的GROUP BY和ORDER BY子句的状况。

• Using filesort：表明MySQL会对成果运用一个外部索引排序，而不是从表里按索引次序读到相关内容。或许在内存或许磁盘上进行排序。 MySQL中无法利用索引完结的排序操作称为“文件排序”

• Select tables optimized away：表明查询过程中，对于索引字段运用了聚合函数。

• Using where;Using index：表明要回来的数据在索引中包括，但并不是索引的前导列，需求做回表获取数据。

• NULL：表明查询的数据未被索引掩盖，但where条件顶用到了主键，能够直接读取表数据。

• Using index condition：和Using where类似，要回来的列未完全被索引掩盖，需求回表。会先条件过滤索引，过滤完索引后找到一切符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。

• Using join buffer (Block Nested Loop)：连接查询时驱动表不能有用的经过索引加速拜访速度时，会运用join-buffer来加速拜访速度，在内存中完结Loop匹配。

• Impossible WHERE：where后的条件永远不或许成立时提示的信息，如where 1!=1。

• Impossible WHERE noticed after reading const tables：依据仅有索引查询不存在的值时呈现的提示。

• const row not found：表中不存在数据时会回来的提示。

• distinct：去重查询时，找到某个值的第一个值时，会将查找该值的工作从去重操作中移除。

• Start temporary, End temporary：表明暂时表用于DuplicateWeedout半连接战略，也便是用来进行semi-join去重。

• Using MRR：表明履行查询时，运用了MRR机制读取数据。

• Using index for skip scan：表明履行查询句子时，运用了索引跳跃扫描机制读取数据。

• Using index for group-by：表明履行分组或去重工作时，能够依据某个索引处理。

• FirstMatch：表明对子查询句子进行Semi-join优化战略。

• No tables used：查询句子中不存在from子句时提示的信息，如desc table_name;

一般状况下当Extra中呈现了Using filesort、Using temporary或Using where，则阐明你写的SQL需求优化了。

四、项目中遇到的问题

本项目是一个异步使命处理框架，其中服务模块供给了一个从MySQL数据库占据使命的一个接口。占据使命的时分，是依据使命的状况和优先级先进行排序，再拉取指定数量的使命的。

使命的状况分为待履行和履行中，以及成功和失利，拉取使命的接口只能拉取待履行的使命，在本项目中，我用的是status要害字来记录使命的状况。在使命状况为待履行的使命里，咱们还要对优先级排序字段进行排序，优先级越大的，越有或许被拉去到，越有或许被最早履行。

一开始的时分，我对status字段和优先级字段分别树立了索引，可是忘掉了树立联合索引，这个时分hold_task接口去拉取使命的时分，就会比较慢，由于数据量还是比较大的，有数十万。

后来，压测的时分发现hold_task的QPS一直不是很高，所以就对该接口进行查看，经过慢查询日志，定位到了慢sql句子，便是select xx from table where status = “待履行” ORDERED BY order_time（伪代码） 。然后对这个sql句子运用explain要害字进行剖析，发现type字段是index_merge，extra字段的值是Using where;Using index，没有走联合索引，而是索引合并，并且需求回表。

所以再回去查看MySQL表之后发现，忘掉树立联合索引了，所以对status字段和优先级字段树立了联合索引，这样一来就避免了回表查询。树立联合索引之后再运用explain要害字进行剖析的时分就发现，type字段变成了ref，extra字段的值变成了Using index，运用了掩盖索引，再进行压测，QPS就增大了一倍多。