MySQL中的慢查询

在MySQL中，提供了对慢查询的语句的检测与记录能力。

MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件）。默认情况下，MySQL数据库是不开启慢查询日志的，long_query_time的默认值为10（即10秒，通常设置为1秒），即运行10秒以上的语句是慢查询语句。

# ON为开启慢查询日志，OFF则为关闭慢查询日志
slow_query_log 

# 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒
long_query_time

# 记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）
slow_query_log_file

# 这个参数设置为ON，可以捕获到所有未使用索引的SQL语句（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON）
log_queries_not_using_indexes

慢查询日志具体记录了：导致慢查询的语句（sql_text），该语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息

如何查看当前慢查询日志的开启情况

通过show variables like '%quer%'命令

image.png

如何查询当前慢查询的语句的个数

在MySQL中有一个变量专门记录当前慢查询语句的个数，可以通过命令show global status like '%slow%' 获取

如何解决慢查询问题

要解决慢查询，就是优化这些查询缓慢的语句，或是重新组织自己的数据。

重新组织数据的表现形式是分表。对于成熟的业务系统而言，分表的代价是极高的。所以如何组织一张表仍然是建表的重要决策。

因此，优化语句才是解决慢查询的基本方法。

优化语句

优化查询速度的第一种方法是索引。为合适的列添加合适的索引往往能有效解决问题。关于MySQL中的索引，可以参加另一篇文章。

关于索引有一个需要注意的地方，即，使用LIKE关键字时，如果匹配字符串的第一个字符为'%'，那么索引不会起作用。'%'必须不能再第一个位置。

可以通过explain命令获取语句的执行计划，以了解到MySQL是否使用了预期的索引来帮助查询。

explain用于显示mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。在select语句前加上explain就可以了。例如：
explain select surname,first_name form a,b where a.id=b.id

分解关联查询

将一个大的查询分解为多个小查询是很有必要的。以美团点评为例，几乎绝大多数的查询都是单表查询。多表的连接可以分解到应用中去做，或者是采用ES的方案。

Limit分页

分页是一个常见的情景。分页通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

但是，当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，MySQL需要查询10020条，但是只返回最后20条，这样的代价很高。

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

对于下面的查询：

select id,title from collect limit 90000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。如果我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。比如说，先查询出90000条数据对应的主键id的值，然后通过该id的值直接查询该id后面的数据。

一个例子

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利用不上。

One more Thing

由于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到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。

参考
https://blog.csdn.net/qq_35571554/article/details/82800463
https://blog.csdn.net/timchen525/article/details/75268151