慢sql优化（转载）

卢克来了
107 阅读3分钟

发现一篇慢sql优化很好的文章，个人转载过来留存

前言 sql语句优化的方式：

  1. 尽量少 join
  2. 尽量少排序
  3. 尽量避免 select *
  4. 尽量少 or
  5. 尽量用 union all 代替 union

（优化的方式有很多，这里就不一一举例了）

当你避免这些问题的时候，为什么sql查询还是这么慢？

排查慢查询sql

ps：mysql版本为5.7

1.连接mysql

mysql -uroot -p

image.png

2.查看数据库服务慢查询日志是否开启

show variables like ‘slow_query_log’;

image.png

3.开启慢查询

set global slow_query_log=on;

image.png

4.查看慢查询阈值（超过这个时间sql就会被记录在慢查询日志中）

show variables like ‘long_query_time’;

image.png

5.设置慢查询阈值 慢查询阈值默认为10秒，为了方便测试，这里设置了0.3秒。

set long_query_time=0.3;

image.png

6.查看慢查询日志所在地址

show variables like ‘slow_query_log_file’;

image.png

7.修改慢查询日志所在地址（方便后面查找）

set global slow_query_log_file=’/usr/local/mysql/data/slow_query.log’;

image.png

8.准备测试数据为了直观展示查询区别，准备了130万多条数据（造数据有点曲折）

image.png

9.执行查询sql

image.png

这条sql查询用时0.45秒，超过设置0.3秒，查看慢查询日志中是否有记录

10.查看慢查询日志

image.png

根据日志可看出：sql语句，查询时间，查询耗时，查询行数等信息。

小结 mysql判断sql语句是不是慢查询，是根据语句的执行时间来衡量的，mysql会用语句的执行时间和long_query_time这个系统参数做比较，如果语句执行时间大于long_query_time，都会把这个语句记录到慢查询日志里面。long_query_time的默认值是10s，一般生产环境不会设置这么大的值，一般设置1秒。

慢查询优化

找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

1.查询sql语句执行计划 执行 EXPLAIN 命令

EXPLAIN select algorithm_id,alg_name,camera_id,status,create_time from algorithm where alg_name = ‘helmet’

image.png

为了直观展示查询结果，这里使用navicat执行sql。 主要看type那列，ALL标识全文检索，所以这条sql查询很慢。 解决：最简单有效的方法就是：加索引。

2.加索引

ALTER TABLE algorithm ADD INDEX index_alg_name ( alg_name );

    ALTER TABLE algorithm ADD INDEX index_alg_name ( alg_name );

加完索引后再执行一次执行计划

image.png

可以看出type是ref，已经不是全盘扫描了。

(explain详解见：www.cnblogs.com/tufujie/p/9…)

重新执行查询sql

image.png 执行时间0.00秒，加了索引还是有作用的。

3.加索引前后对比

执行第一次（秒）执行第二次（秒）执行第三次（秒）
未加索引0.450.440.41
加索引0.000.000.00
————————————————
版权声明：本文为CSDN博主「长得赛」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Zhs_2000/article/details/110044522
avatar
文章
阅读
粉丝