概述
一般我们在对mysql数据库做优化,肯定需要对慢sql去做分析才能开始优化,那么有什么分析的方法呢?下面通过对sql执行时间和执行情况来做分析。
一、SQL 执行时间分析
通过找到执行时间长的 SQL 语句,可以直观的发现数据层的效率问题。
1.通过 show processlist 来查看系统的执行情况
mysql> show processlist;
![0045b39732d135b2ea0b473d64136f86.png](https://i-blog.csdnimg.cn/blog_migrate/efd51297057a1db7eda948483e326034.jpeg)
2.通过 profiling 来进行查看
这个命令是查看 SQL 的执行时间,能很直观的看出快慢。
2.1 查看 profiling 是否开启
0 代表还是关闭着分析功能
mysql> select @@profiling;
2.2 打开工具
mysql> set profiling=1; mysql> select @@profiling;
![67b01723ddbbf22a310c06c81ad3cc6c.png](https://i-blog.csdnimg.cn/blog_migrate/73f6ff190806ad6c74f953b4307d98bb.jpeg)
2.3 查看 SQL 的执行时间
mysql> show profiles;
![16ba2ddc8fea2ea826f0c2aedfd63d61.png](https://i-blog.csdnimg.cn/blog_migrate/8728f399576096434620bc9094e6e102.jpeg)
2.4 查看 SQL 执行耗时详细信息
语法:show profile for query Query_ID
mysql> show profile for query 1;
![049bc07177d97e34990319f7b60571e1.png](https://i-blog.csdnimg.cn/blog_migrate/430e01c62a9bdc1ed56247972b55f9c2.jpeg)
以上具体的信息都是从 INFORMATION_SCHEMA.PROFILING 这张表中取得的。这张表记录了所有的各个步骤的执行时间及相关信息。语法:
select * from INFORMATION_SCHEMA.PROFILING where query_id = Query_ID;
![a82d3563ca804012873a29a125050697.png](https://i-blog.csdnimg.cn/blog_migrate/bd07283c22fa87cb36581ca72f77e4ca.jpeg)
3.慢查询日志
MySQL 的慢查询日志,顾名思义就是把执行时间超过设定值(默认为10s)的 SQL 记录到日志中。这项功能需要手动开启,但是开启后会造成一定的性能损耗。
3.1 查看慢日志是否开启
默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log的值来开启。语法:set global slow_query_log=1
mysql> show variables like '%slow_query_log%';mysql> set global slow_query_log=1;
![f4de379174b1b957d87606eb1bd588f0.png](https://i-blog.csdnimg.cn/blog_migrate/8027b397ff5199e10fe3ec1b9908c6a2.jpeg)
3.2 设置超时时间
- 设置语法:set global long_query_time=4
- 查看语法:show variables like 'long_query_time'
注意:修改后,需要重新连接或新开一个会话才能看到修改值。
永久生效,修改 my.cnf
slow_query_log=1long_query_time=10slow_query_log_file=/path/mysql_slow.log
3.3 其他参数
3.3.1 log_output
参数是指定日志的存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
3.3.2 log-queries-not-using-indexes
未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话,建议开启这个选项。另外,开启了这个参数,其实使用full index scan的sql也会被记录到慢查询日志。
3.3.3 log_slow_admin_statements
表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
3.4 分析工具 mysqldumpslow
MySQL 提供了慢日志分析工具 mysqldumpslow。
- -s 表示按照何种方式排序;
- c: 访问计数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间
- -t 是top n的意思,即为返回前面多少条的数据;
- -g 后边可以写一个正则匹配模式,大小写不敏感的;
3.4.1 命令示例
- 得到返回记录集最多的 10 个 SQL:mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
- 得到访问次数最多的 10 个 SQL:mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
- 得到按照时间排序的前10条里面含有左连接的查询语句:mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
- 另外建议在使用这些命令时结合 | 和 more 使用 ,否则有可能出现刷屏的情况:mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
二、SQL 执行情况分析
使用 explain 分析 SQL 执行情况。
mysql> explain select * from mysql.user;
![aae3dc0aaa545096931a6e97cb92dfb1.png](https://i-blog.csdnimg.cn/blog_migrate/f2ac3218fea8e75d1067d924f78cf130.jpeg)
说明:
![b64b39669b0bdac87ea92749f20d046a.png](https://i-blog.csdnimg.cn/blog_migrate/7a4aa8b83db270a541cbedfa7c3b5586.jpeg)
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
![1a3dff18831dcc42a2544b4496ef74f7.gif](https://i-blog.csdnimg.cn/blog_migrate/048ede79978643efd6c86333b7c9b7b4.gif)