MySQL故障排查与性能分析方法汇总

1,312 阅读25分钟

性能分析方法

方法解决问题总结
Slow Query分析出现出问题的sql通过执行进程状态分析
Explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句通过计划分析
Profile查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等通过过程分析

Slow Query

查询命令

通过执行命令SHOW PROCESSLISTSHOW FULL PROCESSLIST可以看到当前所有SQL执行进程情况,如下:
在这里插入图片描述

字段含义

字段含义
IdPROCESS的Id。
ProcessId任务的唯一标识,执行KILL PROCESS时需要使用ProcessId
User当前用户
Host显示发出这个语句的客户端的主机名,包含IP和端口号
DB显示该PROCESS目前连接的是哪个数据库
Command显示当前连接所执行的命令,即休眠(sleep)、查询(query)以及连接(connect)三种类型的命令
Time显示Command执行的时间,单位为秒
State显示当前连接下SQL语句的执行状态
Info显示SQL语句。

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

线程命令Command值

含义
Binlog Dump这是主服务器上的线程,用于将二进制日志内容发送到从服务器
Table Dump线程将表内容发送到从服务器
Change user线程正在执行改变用户操作
Close stmt线程正在关闭准备好的语句
Connect复制中,从服务器连接到其主服务器
Connect Out复制中,从服务器正在连接到其主服务器
Create DB线程正在执行create-database操作
Daemon此线程在服务器内部,而不是服务客户端连接的线程
Debug线程正在生成调试信息
Delayed insert线程是一个延迟插入处理程序
Drop DB线程正在执行drop-database操作
Execute线程正在执行一个准备好的语句(prepare statement类型就是预编译的语句,JDBC支持次类型执行SQL)
Fetch线程正在执行一个准备语句的结果
Field List线程正在检索表列的信息
Init DB线程正在选择默认数据库
Kill线程正在杀死另一个线程
Long Data该线程在执行一个准备语句的结果中检索长数据
Ping线程正在处理服务器ping请求
Prepare线程正在为语句生成执行计划
Processlist线程正在生成有关服务器线程的信息
Query该线程正在执行一个语句
Quit线程正在终止
Refresh线程是刷新表,日志或缓存,或重置状态变量或复制服务器信息
Register Slave线程正在注册从服务器
Reset stmt线程正在重置一个准备好的语句
Set option线程正在设置或重置客户端语句执行选项
Shutdown线程正在关闭服务器
Sleep线程正在等待客户端向其发送新的语句
Statistics线程正在生成服务器状态信息

线程状态State值

一般线程状态值

含义
After create当线程创建表(包括内部临时表)时,会在创建表的函数的末尾创建。即使由于某些错误而无法创建表,也会使用此状态
Analyzing线程正在计算MyISAM表密钥分布(例如:for ANALYZE TABLE)
checking permissions线程正在检查服务器是否具有执行语句所需的权限
Checking table线程正在执行表检查操作
cleaning up线程已经处理了一个命令,正在准备释放内存并重置某些状态变量
closing tables线程将更改的表数据刷新到磁盘并关闭已用表。这应该是一个快速的操作。如果没有,请验证您是否没有完整的磁盘,并且磁盘没有被非常大的使用
copy to tmp table线程正在处理ALTER TABLE语句。此状态发生在已创建新结构的表之后,但是将行复制到该表之前。对于此状态的线程,可以使用性能模式来获取有关复制操作的进度
Copying to group table如果语句具有不同ORDER BY和GROUP BY标准,各行按组排列和复制到一个临时表
Creating index线程正在处理ALTER TABLE … ENABLE KEYS一个MyISAM表
Creating sort index线程正在处理一个SELECT使用内部临时表解析的线程
creating table线程正在创建一个表,这包括创建临时表
committing alter table to storage engine服务器已经完成就位ALTER TABLE并提交结果
deleting from main table服务器正在执行多表删除的第一部分,它仅从第一个表中删除,并从其他(引用)表中保存要用于删除的列和偏移量
deleting from reference tables服务器正在执行多表删除的第二部分,并从其他表中删除匹配的行
discard_or_import_tablespace线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE声明
end这发生在结束,但的清理之前ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT,或UPDATE语句
executing该线程已经开始执行一个语句
Execution of init_command线程正在init_command系统变量的值中执行语句
freeing items线程已经执行了一个命令,在这种状态下完成的项目的一些释放涉及查询缓存,这个状态通常在后面cleaning up
FULLTEXT initialization服务器正在准备执行自然语言全文搜索
init此操作在初始化ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE之前发生,服务器在该状态中采取的操作包括刷新二进制日志、Innodb日志和一些查询缓存清理操作。对于最终状态, 可能会发生以下操作:更改表中的数据后删除查询缓存项、将事件写入二进制日志、释放内存缓冲区, 包括blob
Killed执行KILL语句,向线程发送了一个声明,下次检查kill标志时应该中断。在MySQL的每个主循环中检查该标志,但在某些情况下,线程可能需要很短时间才能死掉。如果线程被某个其他线程锁定,则一旦其他线程释放锁定,该kill就会生效
Locking system tables线程正在尝试锁定系统表(例如,时区或日志表)
login连接线程的初始状态,直到客户端成功认证为止
manage keys服务器启用或禁用表索引
NULL该状态用于SHOW PROCESSLIST状态
Opening system tables线程尝试打开系统表(例如,时区或日志表)
Opening tables线程正在尝试打开一个表,这应该是非常快的程序,除非有事情阻止打开。例如,一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表,直到语句完成。还可能需要关注table_open_cache参数的值是否足够大。对于系统表,使用Opening system tables状态
optimizing服务器正在执行查询的初始优化
preparing此状态发生在查询优化期间
Purging old relay logs线程正在删除不需要的中继日志文件
query end处理查询之后,freeing items状态之前会发生这种状态
Removing duplicates该查询的使用SELECT DISTINCT方式使得MySQL不能在早期阶段优化不同的操作。因此,MySQL需要一个额外的阶段来删除所有重复的行,然后将结果发送给客户端
removing tmp table处理语句后,该线程正在删除一个内部临时表SELECT 。如果没有创建临时表,则不使用该状态
rename线程正在重命名一个表
rename result table线程正在处理一个ALTER TABLE语句,已经创建了新表,并重新命名它来替换原始表
Reopen tables线程获得了表的锁,但在获得基础表结构更改的锁之后注意到。它释放了锁,关闭了table,并试图重新打开它
Repair by sorting修复代码正在使用排序来创建索引
preparing for alter table服务器正在准备就地执行ALTER TABLE
Repair done线程已经完成了一个MyISAM表的多线程修复
Repair with keycache修复代码通过密钥缓存逐个使用创建密钥,这比慢得多Repair by sorting
Rolling back线程正在回滚事务
Saving state对于MyISAM表操作(如修复或分析),线程将新的表状态保存到.MYI文件头。状态包括行数, AUTO_INCREMENT计数器和键分布等信息
Searching rows for update线程正在进行第一阶段,以便在更新之前查找所有匹配的行。如果UPDATE要更改用于查找涉及的行的索引,则必须执行此操作
setup线程正在开始一个ALTER TABLE操作
Sorting for group线程正在做一个满足一个GROUP BY
Sorting for order线程正在做一个满足一个ORDER BY
Sorting index线程是排序索引页,以便在MyISAM表优化操作期间更有效地访问
Sorting result对于一个SELECT语句,这类似于Creating sort index,但是对于非临时表
statistics服务器正在计算统计信息以开发查询执行计划。如果一个线程长时间处于这种状态,服务器可能是磁盘绑定的,执行其他工作
update线程正在准备开始更新表
Updating线程正在搜索要更新的行并正在更新它们
updating main table服务器正在执行多表更新的第一部分,它仅更新第一个表,并保存用于更新其他(引用)表的列和偏移量
updating reference tables服务器正在执行多表更新的第二部分,并从其他表更新匹配的行
User lock线程将要求或正在等待通过GET_LOCK()呼叫请求的咨询锁定 。因为 SHOW PROFILE,这个状态意味着线程正在请求锁定(不等待它)
User sleep线程调用了一个 SLEEP()调用

故障线程状态值

含义
logging slow query线程正在向慢查询日志写入语句
altering table服务器正在执行就地ALTER TABLE
Receiving from client服务器正在从客户端读取数据包
Copying to tmp table服务器正在复制磁盘到内存的临时表,是直接在磁盘创建的临时表而并非从内存转到磁盘的临时表。
Copying to tmp table on disk对于线程将临时表从内存中更改为基于磁盘的格式存储以节省内存后,又把临时表从磁盘复制到内存时的状态
Creating tmp table线程正在内存或磁盘上创建临时表。如果表在内存中创建,但后来转换为磁盘表,则该操作中的状态将为Copying to tmp table on disk
Sending data线程正在读取和处理SELECT语句的行,并将数据发送到客户端。由于在此状态期间发生的操作往往执行大量的磁盘访问(读取),所以在给定查询的整个生命周期内通常是最长的运行状态
Sending to client服务器正在向客户端写入数据包
Waiting for commit lockFLUSH TABLES WITH READ LOCK正在等待提交锁
Waiting for global read lockFLUSH TABLES WITH READ LOCK正在等待全局读锁定或read_only正在设置全局系统变量
Waiting for tables线程得到一个通知,表格的底层结构已经改变,需要重新打开表以获得新的结构。但是,要重新打开表格,必须等到所有其他线程都关闭该表。如果另一个线程已使用FLUSH TABLES或下面的语句之一:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或OPTIMIZE TABLE都会发生通知
Waiting for table flush线程正在执行FLUSH TABLES并正在等待所有线程关闭它们的表,或者线程得到一个通知,表中的底层结构已经改变,并且需要重新打开表以获得新的结构。但是,要重新打开表,必须等到所有其他线程都关闭该表。如果另一个线程已使用FLUSH TABLES或下面的语句之一:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或OPTIMIZE TABLE都会发出这个通知
Waiting for lock_type lock服务器正在等待THR_LOCK从元数据锁定子系统获取锁或锁,其中lock_type指示锁的类型。THR_LOCK状态表示:Waiting for table level lock;这些状态表示等待元数据锁定:Waiting for event metadata lock、Waiting for global read lock、Waiting for schema metadata lock、Waiting for stored function metadata lock、Waiting for stored procedure metadata lock、Waiting for table metadata lock、Waiting for trigger metadata lock
Writing to net服务器正在将数据包写入网络,如果一个线程长时间在执行并且一直处于Writing to net状态,那么一直在发送数据包到网络,可以试着调整max_allowed_packet大小。另外,这可能会导致其他线程大量阻塞
Waiting on cond线程等待条件成为true的一般状态,没有特定的状态信息可用
System lock线程已经调用mysql_lock_tables() ,且线程状态从未更新。这是一个非常普遍的状态,可能由于许多原因而发生。例如, 线程将请求或正在等待表的内部或外部系统锁。当InnoDB在执行锁表时等待表级锁时, 可能会发生这种情况。如果此状态是由于请求外部锁而导致的,并且不使用正在访问相同表的多个mysqld服务器MyISAM,则可以使用该–skip-external-locking选项禁用外部系统锁 。但是,默认情况下禁用外部锁定,因此这个选项很有可能不起作用。因为SHOW PROFILE,这个状态意味着线程正在请求锁定(不等待它)。对于系统表,使用Locking system tables状态

查询缓存状态值

含义
checking privileges on cached query服务器正在检查用户是否具有访问缓存查询结果的权限
checking query cache for query服务器正在检查当前查询是否存在于查询缓存中
invalidating query cache entries查询缓存条目被标记为无效,因为底层表已更改
sending cached result to client服务器正在从查询缓存中获取查询的结果,并将其发送给客户端
storing result in query cache服务器将查询结果存储在查询缓存中
Waiting for query cache lock当会话正在等待采取查询缓存锁定时,会发生此状态。这种情况可能需要执行一些查询缓存操作,如使查询缓存无效的INSERT或DELETE语句,以及RESET QUERY CACHE等等

事件调度器线程状态值

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

含义
Clearing调度程序线程或正在执行事件的线程正在终止,即将结束
Initialized调度程序线程或将执行事件的线程已初始化。
Waiting for next activation调度程序具有非空事件队列,但下一次激活是将来
Waiting for scheduler to stop线程发出SET GLOBAL event_scheduler=OFF并正在等待调度程序停止
Waiting on empty queue调度程序的事件队列是空的,它正在休眠

主库线程状态值

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

含义
Sending binlog event to slave二进制日志由各种事件组成,一个事件通常为一个更新加一些其它信息。线程已经从二进制日志读取了一个事件并且正将它发送到从服务器。
Finished reading one binlog; switching to next binlog线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件
Has sent all binlog to slave; waiting for binlog to be updated线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲,等待由主服务器上新的更新导致的出现在二进制日志中的新事件
Waiting to finalize termination线程停止时发生的一个很简单的状态

从库I/O线程状态值

含义
Connecting to master线程正试图连接主服务器
Checking master version建立同主服务器之间的连接后立即临时出现的状态
Registering slave on master建立同主服务器之间的连接后立即临时出现的状态
Requesting binlog dump建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送一条请求,索取从请求的二进制日志文件名和位置开始的二进制日志的内容
Waiting to reconnect after a failed binlog dump request如果二进制日志转储请求失败(由于没有连接),线程进入睡眠状态,然后定期尝试重新连接。可以使用–master-connect-retry选项指定重试之间的间隔
Reconnecting after a failed binlog dump request线程正尝试重新连接主服务器
Waiting for master to send event线程已经连接上主服务器,正等待二进制日志事件到达。如果主服务器正空闲,会持续较长的时间。如果等待持续slave_read_timeout秒,则发生超时。此时,线程认为连接被中断并企图重新连接
Queueing master event to the relay log线程已经读取一个事件,正将它复制到中继日志供SQL线程来处理
Waiting to reconnect after a failed master event read读取时(由于没有连接)出现错误,线程企图重新连接前将睡眠master-connect-retry秒
Reconnecting after a failed master event read线程正尝试重新连接主服务器,当连接重新建立后,状态变为Waiting for master to send event
Waiting for the slave SQL thread to free enough relay log space正使用一个非零relay_log_space_limit值,中继日志已经增长到其组合大小超过该值。I/O线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间
Waiting for slave mutex on exit线程停止时发生的一个很简单的状态

从库SQL线程状态值

含义
Reading event from the relay log线程已经从中继日志读取一个事件,可以对事件进行处理了
Has read all relay log; waiting for the slave I/O thread to update it线程已经处理了中继日志文件中的所有事件,现在正等待I/O线程将新事件写入中继日志
Waiting for slave mutex on exit线程停止时发生的一个很简单的状态

从库连接线程状态值

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

含义
Changing master线程正在处理CHANGE MASTER TO语句
Killing slave线程正在处理STOP SLAVE语句
Opening master dump table此状态发生在Creating table from master dump之后
Reading master dump table data此状态发生在Opening master dump table之后
Rebuilding the index on master dump table此状态发生在Reading master dump table data之后

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`)

查询SQL的执行计划。

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

    id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。
    在这里插入图片描述

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

含义
simple简单查询,查询不包含子查询和union
primary复杂查询,组合中最外层的select
subquery包含在select中的子查询(不在 from 子句中)
derived包含在 from 子句中的子查询

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

含义
NULLmysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
const, systemmysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings的结果)。用于primary keyunique key的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。 system是const的特例,表里只有一条元组匹配时为system mysql> explain extended select * from (select * from film where id = 1) tmp;mysql> show warnings;
eq_refprimary keyunique key索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种type。mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
ref相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。 ①简单 select 查询。name是普通索引(非唯一索引)mysql> explain select * from film where name = ‘film1’; ②关联表查询。idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分。mysql> explain select film_id from film left join film_actor on film.id =film_actor.film_id;range:范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。mysql> explain select * from actor where id > 1;
index扫描全表索引,这通常比ALL快一些。mysql> explain select * from film
all即全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了mysql> explain select * from actor
  • 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计算规则如下

类型len长度(字节)备注
char(n)n字符类型
varchar(n)2字节存储字符串长度,如果是utf-8,则长度 3n+ 2字符类型
tinyint1数值类型
smallint2数值类型
int4数值类型
bigint8数值类型
date3时间类型
timestamp4时间类型
datetime8时间类型
  • 如果字段允许为 NULL,需要1字节记录是否为NULL索引
  • 最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引
  • ref列
    显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)
  • rows列
    这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数
  • Extra列
    这一列展示的是额外信息。
含义
Using index使用覆盖索引 mysql> explain select film_id from film_actor where film_id = 1;
Using where使用 where 语句来处理结果,查询的列未被索引覆盖mysql> explain select * from actor where name = ‘a’;
Using index condition查询的列不完全被索引覆盖,where条件中是一个前导列的范围;mysql> explain select * from film_actor where film_id > 1;
Using temporarymysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的
Using filesort将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
Select tables optimized away使用某些聚合函数(比如 max、min)来访问存在索引的某个字段是mysql> explain select min(id) from film;

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%';

在这里插入图片描述

开启性能收集

执行如下命令进行打开:

mysql> SET profiling=1;

在这里插入图片描述

查看性能列表

mysql> show profiles;
  • Query_ID 是执行查询语句的ID
    在这里插入图片描述

查询性能详情

mysql> show profile all for query 145; 

在这里插入图片描述
All 是查看所有字段,可以指定具体字段进行查看,具体含义如下:

字段含义
ALL显示所有的开销信息
BLOCK IO显示块IO相关开销
CONTEXT SWITCHES上下文切换相关开销
CPU显示CPU相关开销信息
IPC显示发送和接收相关开销信息
MEMORY显示内存相关开销信息
PAGE FAULTS显示页面错误相关开销信息
SOURCE显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS显示交换次数相关开销的信息

总结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