MySQL之慢查询sql排查及优化

前言

sql语句优化的方式：
1.尽量少 join
2.尽量少排序
3.尽量避免 select *
4.尽量少 or
5.尽量用 union all 代替 union
…
（优化的方式有很多，这里就不一一举例了）

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

排查慢查询sql

ps：mysql版本为5.7

1.连接mysql

mysql -uroot -p

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

show variables like ‘slow_query_log’;

3.开启慢查询

set global slow_query_log=on;

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

show variables like ‘long_query_time’;

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

set long_query_time=0.3;

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

show variables like ‘slow_query_log_file’;

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

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

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

9.执行查询sql

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

根据日志可看出：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’

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

2.加索引

ALTER TABLE algorithm ADD INDEX index_alg_name ( alg_name );

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

可以看出type是ref，已经不是全盘扫描了。
explain详解见：https://www.cnblogs.com/tufujie/p/9413852.html

重新执行查询sql

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

3.加索引前后对比

	执行第一次（秒）	执行第二次（秒）	执行第三次（秒）
未加索引	0.45	0.44	0.41
加索引	0.00	0.00	0.00

加索引前后查询速度区别明显。

结

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车
                                                                                                                                                           ——菜鸟教程