mysql基础: 慢查询
MySQL优化的第一步应该做的就是排查问题，找出瓶颈，而通常情况下的瓶颈和问题都需要通过观察MySQL的运行情况来进行分析，而对于大多数的程序员来说，最容易发现并解决的问题就是MySQL的慢查询或者没有利用索引的查询。
涉及几个问题：慢查询？如何开启，如何检查是否开启了？慢查询结果如何分析？
慢查询？
在web开发中，经常会写出一些SQL语句，一条糟糕的SQL语句可能让整个程序都非常慢，超过10秒一般用户就会选择关闭网页，如何优化SQL语句将那些运行时间 比较长的SQL语句找出呢？MySQL提供了一个很好的功能，那就是慢查询！所谓的慢查询就是通过设置来记录超过一定时间的SQL语句！binlog日志一般都会开启，慢查询用于开发测试的时候调试和观察SQL语句的执行速度。
什么样的查询需要被日志呢？ 根据需求，需要在哪类查询需要作为慢查询记录的，就相应的开启哪些功能。
在mysql5.6英文手册（）的“5.2.5 The Slow Query Log”中说慢查询记录的条件：
-
The query must either not be an administrative statement, or log_slow_admin_statements must be enabled.
-
The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.
-
The query must have examined at least min_examined_row_limit rows.
-
The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.
粗略地翻译下：
-
查询必询不是管理语句，或者开启了
log_slow_admin_statements。 -
查询的时间至少是long_query_time的秒数,或者查询没有使用索引并且开启了log_queries_not_using_indexes。
-
查询至少检索了min_examined_row_limit 的行数.
-
查询必要不会根据log_throttle_queries_not_using_indexes 的设置而被抑制。
1）超过指定时长的查询开关
mysql> show global variables like '%long_query%';
就一个参数指定超过多少秒的查询需要被记录。mysql5.21+后版本支持毫秒记录（100ms，设置为0.1）
2）没有使用index的查询记录开关
mysql> show global variables like '%indexes%';
第一个参数 表示是否开启记录没有使用index的查询。为ON的话，当执行一个sql语句的时候，如果一个表没有索引就会把这个信息记录在慢查询文件中
第二个参数用来做日志记录的流量控制，一分钟可以记录多少条，默认0是表示不限制。
3）超过指定行数的扫描查询开关
mysql> show global variables like '%min_examined_row_limit%';
默认是0，代表不现在扫描行数
4）管理命令
mysql> SHOW GLOBAL VARIABLES LIKE "%slow%"
log-slow-admin-statements 表示管理命令开关。打开表示管理命令超过指定时长，也会被记录在慢查询日志中。
慢查询日志是可记录在表里，也可以写在日志文件中。
将MySQL慢查询日志写入表的的设置为。set global log_output='TABLE'; 如果日志是写在文件中。用mysqldumpslow工具可以对慢查询日志文件进行浏览。
如何开启？
默认情况下，慢查询日志并不开启。根据实际需求来开启哪些是慢查询，是输出到表中（mysql.slow_log）还是日志文件中。
log_long_format？
要开启这个功能，一种是修改MySQL的配置文件，windows下修改my.ini,Linux下修改my.cnf文件,在[mysqld]最后增加如下命令：
slow_query_log
long_query_time=1
log_queries_not_using_indexes=1
log-slow-admin-statements
1）slow_query_log 开启记录慢查询功能，slow_query_log=0关闭；slow_query_log=1开启（这个1可以不写）
2）long_query_time = 1 是记录超过1s的SQL执行语句，默认是10s
3）log_queries_not_using_indexes 为1表示开启，与1一个意思
4）log-slow-admin-statements 表示开启管理命令，包括执行速度较慢的管理命令(比如OPTIMEZE TABLE)。
5）日志文件存放位置，默认是放在mysql的data目录，并且文件名为host_name-slow.log即主机名-slow.log
第二种：可以通过命令行设置变量来即时启动慢日志查询。登陆到mysql上执行一下sql脚本即可。
set global slow_query_log=ON; // 打开慢查询sql记录slow_log, 执行成功的慢查询语句
set global long_query_time=2; // 慢查询时间限制(秒)
set global log_queries_not_using_indexes=ON; //记录未使用索引的sql语句
查看日志是否打开，日志文件路径（该日志要有写权限）
slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加
mysql > show global variables like 'long_query%';
mysql> show global variables like '%indexes%';
首先检查上述设置是否变化。
用select sleep(1),select sleep(3)或具体SQL来执行是否生效。
mysql > SELECT SLEEP(15);
# Time: 170418 10:42:50
# User@Host: root[root] @ localhost [127.0.0.1] Id: 10
# Query_time: 14.999858 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1492483370;
select sleep(15);
注意设置的参数，global 与session的区别。在当前session修改long_query_time后，查询字段时间一直未修改。关闭当前session后，重新打开再看，就显示为修改后内容了。
set global log=ON; -- 打开所有命令执行记录功能general_log, 所有语句: 成功和未成功的。
select * from mysql.general_log order by 1; -- 所有语句： 成功和未成功的
select * from mysql.slow_log order by 1; -- 执行成功的：慢查询语句,和未使用索引的语句
慢查询如何分析？
可以通过打开log文件查看得知哪些SQL执行效率低下
[root@localhost mysql]# more slow-query.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;
日志指出：查询用了11秒，返回了1行，一共查了6552961行
性能优化很重要的一个步骤是识别导致问题的BAD SQL。一般的数据库调优，往往调优人员会采用调优TOP 10的策略，如果我们把最“昂贵”10个查询优化完（或者更高效地运行它们，或者添加一个索引），那么我们就会立即看到对整体MySQL的性能的提升。不同数据库TOP 10基于的标准可能不太一样，MySQL的慢查询日志比较粗略，主要基于三项基本的信息。
- Query_time: 查询耗时
- Rows_examined: 检查了多少条记录
- Rows_sent : 返回了多少行记录(结果集)
在linux环境下，使用linux命令分析文件
1、按Rows_examined 逆序，列出前100条
# grep Rows_examined slow-query.log | sort -g -k9 -r|uniq -u |head -100
2、指定Rows_examined一值，查找
# grep -A 1 -B 2 "Rows_examined: 24460" slow-query.log
3、看到每秒的慢查询统计
# awk '/^#Time:/{print $3,$4,c;c=0}/^# User /{c++}' slow-query.log >/tmp/aaa.log
mysqldumpslow mysql自带的
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。
主要功能是, 统计不同慢sql的出现次数(Count), 执行最长时间(Time), 累计总耗费时间(Time), 等待锁的时间(Lock), 发送给客户端的行总数(Rows), 扫描的行总数(Rows), 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).
进入log的存放目录，运行
[root@mysql_data]#mysqldumpslow slow-query.log
Reading mysql slow query log from slow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
例如：
#访问时间最长的10个sql语句
mysqldumpslow -t 10 /usr/local/mysql/log/3304/slowquery.log
#访问次数最多的10个sql语句
mysqldumpslow -s c -t 10 /usr/local/mysql/log/3304/slowquery.log
#访问记录集最多的10个sql
mysqldumpslow -s r -t 10 /usr/local/mysql/log/3304/slowquery.log
#得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /usr/local/mysql/log/3304/slowquery.log
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
一般我们关注的3类，参见
A) "Rows_examined" is more than 2000
B) "Rows_examined" is less than 2000 but that query is being run 20 times a second.
C) "Rows_examined" is three times bigger than "Rows_sent"
pt-query-digest
也可以使用Percona的工具pt-query-digest工具来分析慢查询日志。它可以生成一份比官方mysqldumpslow可读性好得多的报告。
注意：
慢查询日志里的慢查询不一定就是BAD SQL。可能是受其他的查询影响，受系统资源限制导致。
如下面的例子，会话被block了，实际上是一个行锁等待50秒超时,然后记录到了慢查询日志:
# Query_time: 50.665866 Lock_time: 0.000102 Rows_sent: 0 Rows_examined: 0
SET timestamp=1339728734;
update tbl_rankings set status=2 where ranking=1;
mysqlsla
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等。
Rows examined, 扫描的行数量.
如何进行调优
可以用explain工具确认慢查询的执行计划 ，进行调优。80%的问题往往是索引不佳，添加适当的索引即可。
常见的慢查询原因：
- 未使用索引
- 索引不优
- 服务器配置不佳
- 死锁
- 。。。。
explain 执行计划，慢查询分析神器
-
type
- const,system: 最多匹配一个行，使用主键或者unique进行索引
- eq_ref: 返回一行数据，通常在联接时出现，使用主键或者unique索引（内表索引连接类型）
- ref: 使用key的最左前缀，且key不是主键或unique键
- range: 索引范围扫描，对索引的扫面开始于某一点，返回匹配的行
- index:以索引的顺序进行全表扫描，优点是不用排序，缺点是还要全表扫描
- all: 全表扫描 no no no
-
extra
- using index : 索引覆盖，只用到索引，可以避免访问表
- using where: 在存储引擎检索行后再做过滤
- using temporary:使用临时表，通常在使用GROUP BY，ORDER BY 时出现（严禁）
- using filesort: 到非索引顺序的额外排序，当order by col未使到索引时发生（严禁）
- possible_keys: 显示查询可能使用的索引
- key:优化器决定采用哪个索引来优化对该表的访问
- rows:MySQL估算的为了找到所需行要检索的数，优化选择key的参考 （不是结果集的行数）
- key_len: 使用的索引左前缀的长度(字节数)，亦可理解为使用了索引中哪些字段
- 定长字段，int占4个字节、date占3个字节、timestamp占4个字节，char(n)占n个字节
- NULL的字段:需要加1个字节，因此建议尽亮设计为NOT NULL
- 变长字段varchar(n)，则需要 (n 编码字符所占字节数 + 2 、)个字节，如utf8编码的， 个字符
占 3个字节，则 度为 n 3 + 2
- 强制使用索引: USE INDEX （建议）或 FORCE_INDEX （强制）
SHOW 命令
- show status
- 查看select语句的执行数 show global status like ‘Com_select’;
- 查看慢查询的个数 show global status like ‘Slow_queries’;
- 表扫描情况 show global status like ‘Handler_read%’; Handler_read_rnd_next / com_select > 4000 需要考虑优化索引
- show variables
- 查看慢查询相关的配置 show variables like ‘long_query_time’;
- 将慢查询时间线设置为2s set global long_query_time=2;
- 查看InnoDB缓存 show variables like ‘innodb_buffer_pool_size’;
- 查看InnoDB缓存的使用状态 show status like ‘Innodb_bufferpool%’; 缓存命中率=(1-Innodb_buffer_pool_reads/ Innodb_buffer_pool_read_requests) 100%；缓存率=（Innodb_buffer_pool_pages_data/ Innodb_buffer_pool_pages_total) 100%
- SHOW PROFILES;该命令可以trace在整个执行过程中各资源消耗情况(会话级)
- SHOW PROCESSLIST; 查看当前有哪些线程正在运行，并且处在何种状态
- SHOW ENGINE INNODB STATUS; 可用于分析死锁，但需要super权限
参考资料
浙公网安备 33010602011771号