MySQL之慢查询

慢查询？

如何定位？

• 根据慢查询日志
• explain工具分析sql执行计划

慢查询日志？

概念

Mysql慢查询，即慢查询日志，是Mysql提供的一种日志记录方式，用来记录在Mysql中响应时长超过阈值的sql。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响

开启

默认关闭

set global slow_query_log=on

设置阈值

默认10秒

set global long_query_time=1;

慢查询日志的文件名和路径

show global variables like 'slow_query_log_file';

查询带有quer的相关变量

只有在配置文件修改才能永久改变，否则重启数据库就还原了

show global variables like '%quer%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dbyvI8Zn-1650216376961)(E:\file\学习\课后总结\MySQL\assert\image-mcx.png)]

应用

当sql执行时间超过所设置的阈值，就会被记录在慢查询日志中

查看系统自带的慢查询日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hSpvVodQ-1650216376964)(E:\file\学习\课后总结\MySQL\assert\image-20220417132248772.png)]

参数

• Time: 慢查询发生的时间
• Query_time: 查询时间
• Lock_time: 等待锁表的时间
• Rows_sent: 语句返回的行数
• Rows_exanined: 语句执行期间从存储引擎读取的行数

注意

有的慢查询正在执行，结果已经导致数据库负载过高，而由于慢查询还没执行完，因此慢查询日志看不到任何语句，此时可以使用show processlist命令查看正在执行的慢查询。show processlist显示哪些线程正在运行，如果有PROCESS权限，则可以看到所有线程。否则，只能看到当前会话线程。

如何提高效率？

索引失效场景

见下；

优化数据库结构

• 拆解字段很多的表为多个表

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

• 增加中间表

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

分解关联查询

• 将一个大查询分解为多个小查询

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效

优化分页

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

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

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

对于下面的查询：

select id,title from collect limit 90000,10;

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

• 筛选字段上添加索引

• 先查询出主键id值
  select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

  原理：先查询出90000条数据对应的主键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。

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