MySQL之SQL优化详解（一）
序言： 在我面试很多人的过程中，很多人谈到SQL优化都头头是道，建索引，explain 分析，like 全模糊会导致索引失效 云云，于是我问道：优化之前，需要找出数据库中比如超过2s的慢SQL，你是怎么找的？很多人答不上来，要是找都找不到，怎么去优化呢，一个中大型系统可能成千上万条SQL都不过分，难道要一个个去分析么。
所以今天和大家聊聊 慢SQL的挖掘机-慢查询日志
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
1. 慢查询日志开启
- 查看是否开启： show variables like '%slow_query_log%';
- 开启慢查询日志：set global slow_query_log=1; (重启会失效)
开启了慢查询日志后，什么样的SQL才会记录到查询日志里面？
这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为10秒
查看命令： show variables like 'long_query_time%';
注： 永久设置慢查询日志开启，以及设置慢查询日志时间临界点（不建议）
linux中，mysql配置文件一般默认在 /etc/my.cnf 更改对应参数即可
2. 慢查询日志设置与查看
设置阀值命令： set global long_query_time=3 （修改为阀值到3秒钟的就是慢sql）
为什么设置后看不出变化：
- 需要重新连接或新开一个会话才能看到修改值。 show variables like 'long_query_time%';
- 直接 show global variables like 'long_query_time';
查看慢查询日志：
cat -n /data/mysql/mysql-slow.log
从慢查询日志中，我们可以看到每一条查询时间高于3s 的sql语句，并可以看到执行的时间是多少。
比如上面，就表示 sql语句 select * from comic where comic_id < 1952000; 执行时间为3.902864秒，超出了我们设置的慢查询时间临界点3s，所以被记录下来了
查看有多少条慢查询记录： show global status like '%Slow_queries%';
3. 日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow
- s: 是表示按照何种方式排序
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间
- t:即为返回前面多少条的数据
- g:后边搭配一个正则匹配模式，大小写不敏感的
工作常用参考:
-
得到返回记录集最多的10个SQL： mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
-
得到访问次数最多的10个SQL： mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
-
得到按照时间排序的前10条里面含有左连接的SQL： mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log
建议：为方便 可以结合 | 和 more 使用，否则可能出现爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log | more
