MYSQL慢查询及其优化

无论是单体应用部署还是分布式微服务应用，应用优化层面做起来是相当简单的，最后所有的压力都还是会落实到数据库上，于是对数据库的SQL优化则成为了我们急需解决的问题。

开启MYSQL慢查询日志

大多数人所称的“开启MYSQL慢查询”实际上指的是开启慢查询日志，这样方便我们定位慢查询的问题。

那么，首先我们需要开启SQL慢查询，这里一共有两种方式可以做到：

一、通过修改配置文件

找到my.ini文件，并且增加几行文字：主要是慢查询的定义时间（一般超过2秒就是慢查询），以及慢查询log日志记录（slow_query_log）

[mysqlld]
//定义查过多少秒的查询算是慢查询，一般为2秒
long_query_time=2

#5.0、5.1等版本配置如下
log-slow-queries="mmysql_slow_query.log"
#5.5及以上版本配置如下
slow-query-log=on
slow_query_log_file="mysql_slow_query.log"

//记录下没有使用索引的query
log-query-not-using-indexes

二、直接通过mysql数据库的命令行开启慢查询

mysql>set global slow_query_log=ON;
mysql>set global long_query_time=3600;
mysql>set global long_querise_not_using_indexes=ON;

分析慢查询日志

一般我们直接通过利用explain关键字可以模拟优化器执行SQL查询语句，来分析SQL的慢查询语句。

例如：执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

可以得到如下结果： table | type | possible_keys | key |key_len | ref | rows | Extra

EXPLAIN列的解释：

Type详解

下面按性能从高到低的顺序介绍type类型：以下四种类型，说明 “性能很好，一般无需优化” ：

system：表里就一条数据

const：一般是针对主键/唯一键的等值查询，mysql可以把这类查询优化为一个常量表达式

eq_ref：一般出现在多表join时，针对主键/唯一键的等值查询，mysql知道只需要返回一条记录

ref：多表 join 时，针对索引字段的查询

以下几种类型，需要 “看具体情况，决定是否要优化” ：

fulltext：关联使用了全文索引

ref_or_null：查询走了索引，但是除此之外还要判断字段是不是null，如果出现这种类型，可以考虑这个字段是否有为空的必要

index_merge：使用了索引合并优化，如果高频出现，可以考虑是不是索引设计有问题。

unique_subquery：in 子句中的子查询，如果只访问主键/唯一键可能会出现这种 type，并不常见

index_subquery：同样是 in 里的子查询，访问了索引列，并不常见

range：对索引字段的范围扫描，一般出现在带有比较的查询语句中，一些in和or的查询也会导致这种类型的扫描

以下两种类型，需要 “优化 & 避免出现” ：

index：按索引进行全表扫描，如果查询不是覆盖索引的，可能会产生很大量的随机IO

all：全表扫描

常见慢查询优化

分为：索引没起效、优化数据库结构、分解关联查询、优化LIMIT分页等四个手段。

索引不生效

1. 使用LIKE关键字的查询语句

在使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为"%"，则索引不会起作用，只有在"%"不在第一个位置，索引才会起作用。

1. 使用多列索引的查询语句

MYSQL可以为多个字段创建索引，一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

其实这个底层逻辑就是要满足索引的最左匹配原则。

对于范围查询的话，数量级过大时，也会默认走全表扫描，索引不生效。例如：

explain select * from user where user_id > 10;

产生原因：MySQL 优化器判断走索引&回表带来的消耗，比走全表还要多。因此，会走全表扫描。

优化方法：预估返回数量级。如果数量级过大，可以分批拉取。反之，可以加 limit 或者 force index 强制走索引。

同样对索引列参加运算和使用函数的方式，都会走全表扫描而不会走索引。

注意：数据库结构中如果规定了数据字段的类型，则隐式类型转换的比较也会导致不走索引，走全表扫描，其逻辑类似于使用了函数。

如果使用or来做查询，则需保证两个字段都建立了索引。

索引列做比较时，索引也会失效。

非主键列，加上not，索引也会失效——in 会走索引，not in 不会走索引，exists 会走索引，not exists 不会走索引，is null 会走索引，is not null 不会走索引（别加not）

非主键列，order by也可能导致索引失效，具体看版本，底层逻辑还是要满足索引的最左匹配原则。

优化数据库结构

理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

1. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

分解关联查询

指的是将一个大的查询分解为多个小的查询。

这在很多高性能应用上都会加以实践，指的是对每一个表进行一次单表查询，然后将查询结果在应用程序中进行聚合关联，很多场景下这样实践会更高效。

 SELECT * FROM tag 
        JOIN tag_post ON tag_id = tag.id
        JOIN post ON tag_post.post_id = post.id
        WHERE tag.tag = 'mysql';
 
        分解为：
 
        SELECT * FROM tag WHERE tag = 'mysql';
        SELECT * FROM tag_post WHERE tag_id = 1234;
        SELECT * FROM post WHERE post.id in (123,456,567);

优化LIMIT分页

在系统中需要分页的地方通常使用：LIMIT加上偏移量的方法实现，同时加上合适的order by子句，如果有对应的索引的话，通常效率会比较高，否则MYSQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit M,N 这样的查询，这是mysql需要查询M条然后只返回最后N条，前面的M条（当M很大）记录都将被舍弃，这样的代价很高。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升

对于下面的查询：

select id,title from collect limit 100000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。

如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。

试想，如果我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，然后舍弃，再从M+1开始再找到100条满足条件的记录了。

方法一：考虑筛选字段上加索引

例如上述的查询中，在titlle字段加索引

方法二：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 100000,1) limit 10;

原理：先查询出100000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

方法三：关延迟联

如果这个表非常大，那么这个查询可以改写成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。

这个技术也可以用在优化关联查询中的limit。

方法四：建立复合索引acct_id和create_time

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

注意SQL查询慢的原因都是：引起filesort

分析具体的SQL语句

1、两个表选哪个为驱动表，表面上可以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。
例如：

select * from a where id in (select id from b );  

对于这条sql语句它的执行计划其实并不是先查询出b表的所有id，然后再与a表的id进行比较。

mysql会把in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：

select * from a where exists(select * from b where b.id=a.id );

而exists相关子查询的执行原理是： 循环取出a表的每一条记录与b表进行比较，比较的条件是a.id=b.id，看a表的每条记录的id是否在b表存在，如果存在就行返回a表的这条记录。

exists查询有什么弊端？

​ 由exists执行原理可知，a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。

如何优化？

​ 建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。

但是这样优化够了吗？还差一些。

由于exists查询它的执行计划只能拿着a表的数据到b表查（外表到里表中），虽然可以在b表的id字段建索引来提高查询效率。
但是并不能反过来拿着b表的数据到a表查，exists子查询的查询顺序是固定死的。

为什么要反过来？

因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问：在双方两个表的id字段上都建有索引时，到底是a表查b表的效率高，还是b表查a表的效率高？

该如何进一步优化？

把查询修改成inner join连接查询：select * from a inner join b on a.id=b.id; （但是仅此还不够，接着往下看）

为什么不用left join 和 right join？

这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。

为什么使用inner join就可以？

​ inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有任何关系的，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。

那我们又怎么能知道a和b什么样的执行顺序效率更高？

​ 你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。

​ 在inner join的连接中，mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。

利用explain字段查看执行时运用到的key（索引）

​ 而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。