首页 > 数据库 >MySQL执行状态查看与分析

MySQL执行状态查看与分析

时间：2024-07-21 18:54:33浏览次数：18  
标签：语句 状态 正在 name 查看 MySQL 线程 TABLE table

 当mysql出现性能问题时，一般会查看mysql的执行状态，执行命令： show processlist

各列的含义

列名含义
id一个标识，你要kill一个语句的时候使用，例如 mysql> kill 207
user显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句
host显示这个语句是从哪个ip 的哪个端口上发出的，可用来追踪出问题语句的用户
db显示这个进程目前连接的是哪个数据库
command显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）
time此这个状态持续的时间，单位是秒
state显示使用当前连接的sql语句的状态，很重要的列，state只是语句执行中的某一个状态，例如查询，需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成
info显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据

state 状态值

参考官网：MySQL :: MySQL 5.7 Reference Manual :: 8.14.3 General Thread States

状态值说明
After create当线程在创建表的函数结束时创建表（包括内部临时表）时，会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态。
altering table服务器正在执行就地 ALTER TABLE。
Analyzing线程正在计算 MyISAM 表的键分布（例如，对于 ANALYZE TABLE ）。
checking permissions线程正在检查服务器是否具有执行语句所需的特权。
Checking table线程正在执行表检查操作。
cleaning up线程已处理了一个命令，并准备释放内存和重置某些状态变量。
committing alter table to storage engine服务器已完成就地 ALTER TABLE 并正在提交结果。
closing tables该线程正在将已更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请验证磁盘是否已满，并且磁盘使用率是否很高。
converting HEAP to ondisk线程正在将内部临时表从 MEMORY 表转换为磁盘上的表。
copy to tmp table

线程正在处理 ALTER TABLE 语句。此状态发生在创建具有新结构的表之后，但在将行复制到其中之前。

对于处于此状态的线程，性能架构可用于获取有关复制操作进度的信息。请参见第25.12.5节“性能架构阶段事件表”。

Copying to group table如果语句具有不同的 ORDER BY 和 GROUP BY 条件，则按组对行进行排序并将其复制到临时表中。
Copying to tmp table服务器正在复制到内存中的临时表。
Copying to tmp table on disk服务器正在复制到磁盘上的临时表。临时结果集变得太大（请参见第8.4.4节"MySQL中的内部临时表使用"）。因此，线程将临时表从内存中的格式更改为基于磁盘的格式以节省内存。
Creating index线程正在为 MyISAM 表处理 ALTER TABLE ... ENABLE KEYS 。
Creating sort index线程正在处理使用内部临时表解析的 SELECT 。
creating table线程正在创建一个表。这包括创建临时表。
Creating tmp table线程正在内存或磁盘上创建临时表。如果该表是在内存中创建的，但后来被转换为磁盘上的表，则该操作期间的状态为 Copying to tmp table on disk 。
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 语句之前。

对于 end 状态，可能会发生以下操作：

  • 表中的数据更改后删除查询缓存项

  • 将事件写入二进制日志

  • 释放内存缓冲区，包括Blob

executing线程已开始执行语句。
Execution of init_command线程正在执行 init_command 系统变量值中的语句。
freeing items线程已执行命令。在此状态下执行的某些项释放涉及查询缓存。这个状态通常跟在 cleaning up 后面。
FULLTEXT initialization服务器正准备执行自然语言全文搜索。
init这发生在初始化 ALTER TABLE 、 DELETE 、 INSERT 、 SELECT 或 UPDATE 语句之前。服务器在此状态下执行的操作包括刷新二进制日志、 InnoDB 日志和一些查询缓存清理操作。
Killed有人向线程发送了一个 KILL 语句，它应该在下次检查kill标志时中止。在MySQL中的每个主要循环中都检查该标志，但在某些情况下，线程可能仍然需要很短的时间才能死亡。如果线程被其他线程锁定，则kill在其他线程释放其锁后立即生效。
logging slow query线程正在向慢速查询日志写入语句。
login连接线程在客户端成功通过身份验证之前的初始状态。
manage keys服务器正在启用或禁用表索引。
Opening tables线程正在尝试打开一个表。这应该是非常快的程序，除非有什么阻止打开。例如， ALTER TABLE 或 LOCK TABLE 语句可以在语句完成之前阻止打开表。同样值得检查的是，你的 table_open_cache 值是否足够大。
optimizing服务器正在对查询执行初始优化。
preparing此状态发生在查询优化期间。
preparing for alter table服务器正在准备执行就地 ALTER TABLE 。
Purging old relay logs线程正在删除不需要的中继日志文件。
query end此状态发生在处理查询之后，但在 freeing items 状态之前。
Receiving from client服务器正在阅读来自客户端的数据包。这个状态在MySQL 5.7.8之前被称为 Reading from net 。
Removing duplicates该查询使用 SELECT DISTINCT 的方式使得MySQL无法在早期阶段优化不同的操作。因此，MySQL需要一个额外的阶段来删除所有重复的行，然后再将结果发送给客户端。
removing tmp table线程在处理了一个 SELECT 语句后删除了一个内部临时表。如果未创建临时表，则不使用此状态。
rename线程正在重命名表。
rename result table线程正在处理 ALTER TABLE 语句，已创建新表，并将其重命名以替换原始表。
Reopen tables线程获得了表的锁，但在获得锁后注意到底层表结构发生了变化。它已经释放了锁，关闭了表，并试图重新打开它。
Repair by sorting修复代码正在使用排序来创建索引。
Repair done线程已完成 MyISAM 表的多线程修复。
Repair with keycache修复代码使用通过密钥缓存逐个创建密钥。这比 Repair by sorting 慢得多。
Rolling back线程正在回滚事务。
Saving state对于 MyISAM 表操作（如修复或分析），线程将新表状态保存到 .MYI 文件头。状态包括诸如行数、 AUTO_INCREMENT 计数器和键分布等信息。
Searching rows for update线程执行第一阶段，在更新行之前查找所有匹配的行。如果 UPDATE 正在更改用于查找相关行的索引，则必须执行此操作。
Sending data线程正在阅读和处理 SELECT 语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量的磁盘访问（读取），因此它通常是给定查询的生命周期中运行时间最长的状态。
Sending to client服务器正在向客户端写入数据包。这个状态在MySQL 5.7.8之前被称为 Writing to net 。
setup线程正在开始 ALTER TABLE 操作。
Sorting for group线程正在进行排序以满足 GROUP BY 。
Sorting for order线程正在进行排序以满足 ORDER BY 。
Sorting index该线程正在对索引页进行排序，以便在 MyISAM 表优化操作期间进行更有效的访问。
Sorting result对于 SELECT 语句，这与 Creating sort index 类似，但适用于非临时表。
starting语句执行开始时的第一阶段。
statistics服务器正在计算统计信息以制定查询执行计划。如果线程长时间处于这种状态，则服务器可能正在磁盘绑定中执行其他工作。
System lock

该线程已调用 mysql_lock_tables() ，并且自那以后未更新线程状态。这是一种非常普遍的状态，可能由于多种原因而发生。

例如，线程将请求或等待表的内部或外部系统锁。当 InnoDB 在执行 LOCK TABLES 期间等待表级锁时，可能会发生这种情况。如果这种状态是由外部锁请求引起的，并且您没有使用多个访问相同 MyISAM 表的mysqld服务器，则可以使用 --skip-external-locking 选项禁用外部系统锁。但是，默认情况下外部锁定是禁用的，因此该选项可能没有任何作用。对于 SHOW PROFILE ，这个状态意味着线程正在请求锁（而不是等待锁）。

update线程准备开始更新表。
Updating线程正在搜索要更新的行并正在更新它们。
updating main table服务器正在执行多表更新的第一部分。它只更新第一个表，并保存用于更新其他（引用）表的列和偏移量。
updating reference tables服务器正在执行多表更新的第二部分，并更新其他表中匹配的行。
User lock线程将请求或正在等待通过 GET_LOCK() 调用请求的咨询锁。对于 SHOW PROFILE ，这个状态意味着线程正在请求锁（而不是等待锁）。
User sleep该线程已调用 SLEEP() 调用。
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

有关表锁定指示器的信息，请参见第8.11.1节“内部锁定方法”。有关元数据锁定的信息，请参见第8.11.4节“元数据锁定”。若要查看哪些锁正在阻塞锁请求，请使用第25.12.12节“性能架构锁表”中描述的性能架构锁表。

Waiting on cond一种泛型状态，在这种状态下，线程正在等待条件变为真。没有可用的特定状态信息。
Writing to net服务器正在向网络写入数据包。从MySQL 5.7.8开始，这个状态被称为 Sending to client 。

1、Sleep

连接资源未释放，如果是通过连接池连接，sleep状态应该保持在一定数据范围内。

2、Copy to tmp table

索引及现有结构无法涵盖查询条件时，会建立一个临时表来满足查询要求，产生巨大的i/o压力Copy to tmp table通常与连表查询有关，建议减少关联查询或者深入优化查询语句，如果出现此状态的语句执行时间过长，会严重影响其他操作，此时可以kill掉该操作。

3、Sending data

Sending data并不是发送数据，是从物理磁盘获取数据的进程，如果影响结果集较多，那么就需要从不同的磁盘碎片去抽取数据，如果sending data连接过多，通常是某查询的影响结果集过大，也就是查询的索引项不够优化

4、Storing result to query cache

如果频繁出现此状态，使用set profiling分析，如果存在资源开销在SQL整体开销的比例过大，则说明query cache碎片较多，使用flush query cache可即时清理，Query cache参数可适当酌情设置

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。

除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

利用上面知识寻找MySQL中Varchar(20)和varchar(200)区别是什么？

数据准备：

CREATE TABLE `user_info_varchar20` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(20)   NOT NULL COMMENT '用户名',
  `age` tinyint(4)  NOT NULL DEFAULT 0 COMMENT '年龄',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性别 0：男 1： 女',
  `create_time` datetime NOT NULL DEFAULT NOW() COMMENT '创建时间',
  `udpate_time` datetime NOT NULL DEFAULT NOW() COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) COMMENT 'name索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户账户';

CREATE TABLE `user_info_varchar200` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(200)   NOT NULL COMMENT '用户名',
  `age` tinyint(4)  NOT NULL DEFAULT 0 COMMENT '年龄',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性别 0：男 1： 女',
  `create_time` datetime NOT NULL DEFAULT NOW() COMMENT '创建时间',
  `udpate_time` datetime NOT NULL DEFAULT NOW() COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) COMMENT 'name索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户账户';

每张表插入100w数据：

DELIMITER $$
CREATE PROCEDURE insertTestData(IN total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
		DECLARE batch_size INT DEFAULT 500;
		DECLARE var_offset INT DEFAULT 1;
		DECLARE var_limit INT;
		DECLARE var_batch_values TEXT;
		
		WHILE i <= total DO
			SET var_limit=var_offset+batch_size;
			SET var_batch_values='';
		  WHILE i < var_limit DO
				IF i = (var_limit-1) THEN
						SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', 0, 0, NOW(), NOW())'));
				ELSE
				    SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', 0, 0, NOW(), NOW()),'));
				END IF;
				SET i=i+1;
			END WHILE;
			
			SET @sql = CONCAT('INSERT INTO user_info_varchar20(name, age, sex, create_time, udpate_time) VALUES ', var_batch_values);
			PREPARE stmt FROM @sql;
      EXECUTE stmt;
			
			SET @sql = CONCAT('INSERT INTO user_info_varchar200(name, age, sex, create_time, udpate_time) VALUES ', var_batch_values);
			PREPARE stmt FROM @sql;
      EXECUTE stmt;
			
			SET var_offset=var_limit;
		END WHILE;
END$$
DELIMITER;

CALL insertTestData(1000000);

存储空间对比：

SELECT
    table_schema AS "数据库",
    table_name AS "表名",
    table_rows AS "记录数",
    TRUNCATE ( data_length / 1024 / 1024, 4)  AS "数据容量（MB）",
    TRUNCATE ( index_length / 1024 / 1024, 4)  AS "索引容量（MB）" 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'custom_db' 
and (TABLE_NAME = 'user_info_varchar20' or TABLE_NAME = 'user_info_varchar200')
ORDER BY
    data_length DESC,
    index_length DESC;

从MySQL统计表里面看二者差不多（统计的是个大概值），或者“ANALYZE TABLE tableName”更新统计信息也可以通过对比IBD文件（里面包含索引数据以及表的行数据）。

方式1:ANALYZE TABLE tableName
ANALYZE TABLE user_info_varchar20;
ANALYZE TABLE user_info_varchar200;

SELECT
    table_schema AS "数据库",
    table_name AS "表名",
    table_rows AS "记录数",
    TRUNCATE ( data_length / 1024 / 1024, 4)  AS "数据容量（MB）",
    TRUNCATE ( index_length / 1024 / 1024, 4)  AS "索引容量（MB）" 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'custom_db' 
and (TABLE_NAME = 'user_info_varchar20' or TABLE_NAME = 'user_info_varchar200')
ORDER BY
    data_length DESC,
    index_length DESC;

方式2： 对比IBD文件

查询性能对比

 测试前开启MySQL的profiles

 show variables like '%profiling%';

 set profiling = 1;  开启

执行测试SQL

-- 条件为索引字段查询		
select * from user_info_varchar20 where name = '测试用户800000';
select * from user_info_varchar200 where name = '测试用户800000';

-- 排序字段为索引同时搜索字段为索引覆盖(深分页)
select name from user_info_varchar20 order by name LIMIT 999995, 5;
select name from user_info_varchar200 order by name LIMIT 999995, 5;


-- 排序字段为索引同时搜索字段为普通字段（深分页）
select * from user_info_varchar20 order by name LIMIT 999995, 5;
select * from user_info_varchar200 order by name LIMIT 999995, 5;

执行命令show profiles，查看查询执行效率

 通过对比发现varchar(20)和varcha(200)基本差不多，但是在“排序字段为索引同时搜索字段为普通字段（深分页）”这种情况二者时间差距很大。


-- 索引排序同时深分页
select * from user_info_varchar20 order by name LIMIT 999995, 5;
select * from user_info_varchar200 order by name LIMIT 999995, 5;

show profile 命令继续分析

 翻看最上面state字段值说明，可知，二者在处理select查询字段时情况不一样，但为什么差距这么大呢？

 二者不出意外的话应该都使用了filesort排序，查询执行计划

二者都是查询字段未完全包含在排序字段中，所以是Using filesort，这个好理解，但是执行时间为啥会差距这么大呢？

开启优化分析器，继续分析，命令如下：

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 执行语句 */
select name,age from user_info_varchar200 order by name LIMIT 999995, 5;
 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * from information_schema.OPTIMIZER_TRACE;
 
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
SELECT @b-@a;
 表<user_info_varchar20>执行结果
  
表<user_info_varchar200>执行结果

 经过对比，二者的排序缓冲区（sort_buffer_size）大小为1048560(1M),在进行归并排序时，varchar20表用了75个临时表，而varchar200用到了419个临时表，临时文件越多(IO次数就越多)，归并排序性能越差。所以原因找到了！！！（间接说明varchar(20)与varchar(200)在排序缓冲区占用内存大小不一样，换种说法就是搜索引擎为两种长度在缓冲区分配的内存不一样,再细节的原因需要了解MySQL底层fileSort，顺便提一句是fileSort的sortKey字节长度不一样引起的）。

标签：语句,状态,正在,name,查看,MySQL,线程,TABLE,table
From： https://blog.csdn.net/java_key_code/article/details/140459353

相关文章

  • Linux - 网络状态工具ss命令详解
    ss是SocketStatistics的缩写。ss命令可以用来获取socket统计信息，它显示的内容和netstat类似。但ss的优势在于它能够显示更多更详细的有关TCP和连接状态的信息，而且比netstat更快。当服务器的socket连接数量变得非常大时，无论是使用netstat命令还是直接cat/proc......
  • 掌控数据库：在 PHPStudy 中玩转 MySQL 命令行的终极指南
    文章目录引言1.PHPStudy简介理解数据库，表，字段的模型2.打开MySQL命令行2.1启动PHPStudy2.2启动MySQL服务2.3打开命令行2.4登录MySQL3.MySQL基本命令3.1查看（进去）数据库3.2创建数据库3.3使用数据库3.4创建数据表3.5插入数据3.6查询数据（查表）3.7更新数据3.8......
  • 关于mysql架构的思考
    MySQLMySQL的逻辑架构主要可以分成哪几个部分？每部分的主要职责是什么？MySQL的逻辑架构主要可以分成三个主要部分：连接层、服务层和引擎层。每个部分都有其特定的职责和功能，以确保MySQL数据库系统的高效运行。1.连接层主要职责：负责与客户端建立连接和通信。当客户端发起......
  • Python第九章（面向对象基础--属性，继承，dir查看，内存地址，权限等等和银行账户题目，圆的面积
    面向对象创造对象示例代码：类的名字用小驼峰命名法#编写Person类classPerson():passclassCat:#，小括号可以省略pass#对象名=类名per=Person()c=Cat()#小括号不能省略print(type(per))print(type(c))代码结果：<class'__main__.Person'><class'__mai......
  • 无法更新 Python MySQL 中的tinyint
    我无法使用以下代码更新我的Tinyint值（valid_state）：importmysql.connectormydb=mysql.connector.connect(host="localhost",user="mahdi",passwd="Abcd@1324",database="mycustomers")mycursor=mydb.cursor()sql="UPDATE`......
  • Linux开发讲课39---设置和查看Linux系统的环境和Shell这两种变量
            在Linux系统中，有环境变量和Shell变量这两种变量。        环境变量是在程序及其子程序中全局可用的，常常用来储存像默认的文本编辑器或者浏览器，以及可执行文件的路径等等这样的信息。而Shell变量仅在当前Shell中可用，可以用来存储当前用户的ID......
  • MySQL基础（1） 分页查询 函数 过程
    目录一、分页查询：1、分页查询语法：情况1.每页显示20条记录，此时显示第1页情况2.每页显示20条记录，此时显示第2页2、分页查询公式：(当前页数-1)*每页条数，每页条数3、倒序（1）desc：（2）orderby:情况1：根据学生序号sno倒序排列情况2：查询员工表中工资最高的员工信息二、MySQL......
  • vue中怎么改变状态值？
    在Vue中，状态值通常指的是组件的data函数返回的对象中的属性，或者是Vuex状态管理库中的状态。以下是在Vue中改变状态值的几种常见方法：1.直接在组件内部改变状态值在Vue组件中，你可以直接在methods中改变data函数返回的对象中的属性。javascript<template><div><button@clic......
  • MySQL数据库基本操作包括MySQL过程、MySQL声明
    MySQL数据库操纵数据库查看数据库showdatabases;创建数据库createdatabase<database_name>;删除数据库dropdatabase<database_name>;使用数据库usemysql操纵数据表查看数据表showtables;创建数据表CREATETABLETBL_USER(#创建user，tableU_IDINTPR......
  • mysql常用命令总结
    连接数据库格式mysql-h连接地址-u用户-p密码-P端口例如mysql-h127.0.0.1-uroot-p123456-P3310 常用用户管理操作https://dev.mysql.com/doc/refman/8.0/en/create-user.html创建用户CREATEUSER'用户名字'@'%'IDENTIFIEDBY'密码';例如CREATEUSER'wxh......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99