慢查询日志和profiling
MySQL调优三步:
慢查询 (分析出现出问题的sql)
Explain (显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句)
Profile(查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.)
慢查询日志:
MySQL 提供了慢查询日志。这个日志会记录所有执行时间超过 long_query_time(默认是 10s)的 SQL 及相关的信息。
mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set mysql> show variables like 'slow_query%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/log/mysql/log-slow-queries.log | +---------------------+--------------------------------------+ 2 rows in set
slow_query_log 指的是慢查询日志是否开启。
slow_query_log_file 指明了日志所在的位置。
开启方法:
修改配置文件my.cnf或my.ini中的相应参数。
或者用set在命令行设置相应的变量
在开启了 MySQL 慢查询日志一段时间之后,日志中就会把所有超过 long_query_time 的 SQL 记录下来。
另一个有用的相关 MySQL 命令是 mysqldumpslow:由于慢查询日志可能很大或者很难分析,使用它可以获得 MySQL 对慢查询日志的一个总结报告,直接获得我们想要的统计分析后的结果。
Show命令:
我们可以通过show命令查看MySQL状态及变量,找到系统的瓶颈:
Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)
Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行,包括执行状态、是否锁表等
profiling分析查询:
通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。
如果觉得explain的信息不够详细,可以通过profiling命令得到更准确的SQL执行消耗系统资源的信息。
首先,set profiling=1,开启profiling
show profiles; 可以得到被执行的SQL语句的时间和ID
show profile for query 1; 得到对应SQL语句执行的详细信息