MySQL慢查询分析

MySQL慢查询监测开启

慢查询日志
慢查询日志是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。 通过使用--slow_query_log[={0|1}]选项来启用慢查询日志。所有执行时间超过long_query_time秒的SQL语句都会被记录到慢查询日志。 缺省情况下hostname-slow.log为慢查询日志文件名，存放到数据目录，同时缺省情况下未开启慢查询日志。 缺省情况下数据库相关管理型SQL(比如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE)不会被记录到日志。 对于管理型SQL可以通过--log-slow-admin-statements开启记录管理型慢SQL。 mysqld在语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。获得初使表锁定的时间不算作执行时间。
mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具

• 慢查询日志功能开启：
  slow_query_log：是否开启慢查询日志功能（必填）
  long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）
  slow_query_log_file：慢查询日志文件位置，自动在 \data\ 创建一个 [hostname]-slow.log 文件

MySQL慢查询分析工具

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化

1. 直接分析慢查询文件:
   pt-query-digest slow.log > slow_report.log
2. 分析最近12小时内的查询：
   pt-query-digest --since=12h slow.log > slow_report2.log
3. 分析指定时间范围内的查询：
   pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log
4. 分析指含有select语句的慢查询
   pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log
5. 针对某个用户的慢查询
   pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log
6. 查询所有所有的全表扫描或full join的慢查询
   pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log
7. 把查询保存到query_review表
   pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log
8. 把查询保存到query_history表
   pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0001
   pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0002

MySQL执行计划explain

名称	说明
id	id值越大优先级越高，id相同从上到下执行，上面的优先级较高
select_type	SIMPLE:简单查询, PRIMARY:最外层查询, SUBQUERY:映射为子查询, DERIVED:子查询, UNION:联合, UNION RESULT:使用联合的结果
table	正在访问的表名
type	ALL:全数据表扫描； index:全索引表扫描，比如select count(*) RANGE:对索引列进行范围查找，它是索引上的范围查询，它会在索引上扫码特定范围内的值(建议至少达到此级别) REF:根据索引查找一个或多个值，非主键非唯一索引等值扫描 EQ_REF:搜索时使用primary key 或 unique类型 CONST:常量，表最多有一个匹配行,只能查到1数据，用于主键或者唯一索引。 SYSTEM:系统，系统表，少量数据，往往不需要进行磁盘IO。
possiable_keys	可能使用的索引
key	实际使用的索引，如果为 NULL ，则没有使用索引
key_len	MySQL中使用索引字节长度
rows	mysql 预估为了找到所需的行而要读取的行数
filtered	表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比，即条件过滤出的记录行数占type指示的方式搜索的记录数（rows）的百分比
extra	Using index 此值表示mysql将使用覆盖索引，以避免访问表。 Using where mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 Using temporary mysql 对查询结果排序时会使用临时表。 Using filesort mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。