MySQL故障排查与性能分析方法汇总_mysql如何排除性能异常

性能分析方法

Slow Query

查询命令

通过执行命令SHOW PROCESSLIST 或 SHOW FULL PROCESSLIST可以看到当前所有SQL执行进程情况，如下：

字段含义

说明 如果不使用FULL关键字，只能查看每个记录中Info字段的前100个字符。

线程命令Command值

线程状态State值

一般线程状态值

故障线程状态值

查询缓存状态值

事件调度器线程状态值

这些状态适用于事件调度程序线程，创建用于执行调度事件的线程或终止调度程序的线程。

主库线程状态值

以下列表显示了主从复制中主服务器的Binlog Dump线程的State列中可能看到的最常见状态。如果Binlog Dump线程在主服务器上看不到，这意味着复制没有运行，也就是说，目前没有连接任何Slave主机。

从库I/O线程状态值

从库SQL线程状态值

从库连接线程状态值

这些线程状态发生在复制从库上，但与连接线程相关联，而不与I/O或SQL线程相关联。

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL

注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

explain

示例表

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (`id` INT(11) NOT NULL,5`name` VARCHAR(4) DEFAULT NULL,`update_time` DATETIME DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22 15:27:18'),(2,'b','2017‐12‐22 15:27:18'),(3,'c','2017‐12‐22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (`id` INT(11) NOT NULL AUTO_INCREMENT,`name` VARCHAR(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`)) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (`id` INT(11) NOT NULL,`film_id` INT(11) NOT NULL,`actor_id` INT(11) NOT NULL,`remark` VARCHAR(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`)
1
2
3
4
5
6
7
8
9
10

查询SQL的执行计划。

• id列
  id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。

  id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
  

• select_type列
  表示对应行是简单还是复杂的查询。

MySQL会将子查询结果存放在一个临时表中，也称为派生表（derived的英文含义）

• table列
  表示explain的一行正在访问哪个表
  • 当from子句中有子查询时，table列是derivenN格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询
  • 当有union时，UNION RESULT的table 列的值为<union1,2>，1和2表示参与union 的select行id。
• type列
  表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围

  依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all
  一般来说，得保证查询达到range级别，最好达到ref。

• possible_keys列
  显示查询可能使用哪些索引来查找。explain 时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。
• key列 显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index

  强制索引：EXPLAIN SELECT *FROM account_info FORCE INDEX(I_SEQ);
  忽略索引：EXPLAIN SELECT *FROM account_info IGNORE INDEX(I_SEQ);

• key_len列
  显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

  举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。mysql> explain select * from film_actor where film_id = 2

key_len计算规则如下

• 如果字段允许为 NULL，需要1字节记录是否为NULL索引
• 最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

• ref列
  显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
• rows列
  这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数
• Extra列
  这一列展示的是额外信息。

explain extended

会在 explain 的基础上额外提供一些查询优化的信息。
EXPLAIN EXTENDED SELECT * FROM account_info LIMIT 1 OFFSET 5;

紧随其后通过show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。

explain partitions

多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区

Query Profiler

Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。

查看功能状态

默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。可以通过如下方法查看当前mysql服务器是否开启了该功能。

• profiling参数值为OFF，说明没有打开该功能。
• profiling_history_size参数值为15表示，记录最近15次的查询历史

mysql> show variables like '%profiling%';
1

开启性能收集

执行如下命令进行打开：

mysql> SET profiling=1;
1

查看性能列表

mysql> show profiles;
1

• Query_ID 是执行查询语句的ID
  

查询性能详情

mysql> show profile all for query 145; 
1

All 是查看所有字段，可以指定具体字段进行查看，具体含义如下：

总结Query Profiler使用流程：

• set profiling=1; //打开profile分析
• 执行sql;
• show profiles; //查看sql1,sql2的语句分析
• SHOW profile CPU,BLOCK IO io FOR query 1; //查看CPU、IO消耗
• set profiling=0; //关闭profile分析

参考

Mysql分析-profile详解
Mysql调优之profile详解
MySQL SHOW PROCESSLIST协助故障诊断全过程
深入理解MySQL主从复制线程状态转变
Explain详解与索引最佳实践.pdf