MySQL查询状态

MySQL 查询状态详解：通过 show full processlist 解析线程行为

MySQL 中，每个连接线程在查询周期内会处于不同状态，这些状态反映了线程当前的工作内容（如等待请求、执行查询、排序结果等）。通过 show full processlist 命令可查看所有线程的状态，是诊断查询阻塞、性能瓶颈的重要工具。

查询状态的查看方式

使用以下命令查看线程状态：

-- 显示所有线程的简要信息（默认只显示前100条）
show processlist;

-- 显示所有线程的完整信息（包括完整SQL）
show full processlist;

输出结果包含以下关键列：

• Id：线程 ID（可用于 kill 命令终止线程）。
• User：执行该线程的用户。
• Host：客户端主机。
• db：当前操作的数据库。
• Command：线程执行的命令类型（如 Query、Sleep）。
• Time：线程处于当前状态的时间（秒）。
• State：线程的具体状态（核心关注字段）。
• Info：执行的 SQL 语句（show full processlist 显示完整内容）。

常见查询状态及含义

1. Sleep

• 含义：线程正在等待客户端发送新的请求（无活跃操作）。
• 示例：客户端连接后未执行任何 SQL，或执行完成后未断开连接。
• 关注点：若 Time 过大（如超过 300 秒），可能是连接池未正确回收空闲连接，需检查 wait_timeout 配置（建议设为 300 秒以内）。

2. Query

• 含义：线程正在执行查询，或正在将结果发送给客户端。
• 示例：执行 select * from user where id = 1 时，线程处于此状态。
• 关注点：若 Time 过大，表明查询执行缓慢（可能因全表扫描、锁等待等），需优化 SQL 或索引。

3. Locked

• 含义：线程正在等待表级锁（行级锁不会体现在此状态）。
• 示例：MyISAM 表执行 update 时会加表锁，其他线程访问该表时会处于 Locked 状态。
• 关注点：Time 增长表明存在表锁竞争，建议改用 InnoDB 引擎（支持行锁），或优化 SQL 减少锁持留时间。

4. Analyzing and statistics

• 含义：线程正在收集存储引擎的统计信息（如索引 cardinality），并生成查询执行计划。
• 示例：复杂查询（多表关联、聚合函数）在执行前的准备阶段。
• 关注点：若频繁出现且耗时过长，可能是统计信息过时，可执行 analyze table 更新统计信息。

5. Copying to tmp table [on disk]

• 含义：线程正在将查询结果复制到临时表（内存或磁盘），通常因GROUP BY、ORDER BY或UNION操作需要临时存储中间结果。
  • 无 [on disk]：使用内存临时表（tmp_table_size 控制大小）。
  • 有 [on disk]：内存临时表超限，转为磁盘临时表（性能大幅下降）。
• 示例：select count(*) from order group by user_id 可能触发临时表。
• 关注点：[on disk]状态需优化：
  • 增大 tmp_table_size 和 max_heap_table_size（内存临时表上限）。
  • 优化 GROUP BY/ORDER BY 语句，避免不必要的临时表（如添加合适索引）。

6. Sorting Result

• 含义：线程正在对结果集进行排序（未使用索引排序，即 Using filesort）。
• 示例：select * from user order by name 若 name 无索引，会触发此状态。
• 关注点：耗时过长表明排序数据量大，需添加排序字段的索引，或调整 sort_buffer_size 减少磁盘排序。

7. Sending data

• 含义：线程正在处理数据（如读取行、关联表、生成结果集），或向客户端发送数据。
• 示例：全表扫描时读取大量行，或多表关联查询生成结果集。
• 关注点：此状态耗时过长通常因扫描行数过多，需优化 SQL（如添加索引、限制返回行数）。

其他重要状态

8. Waiting for table metadata lock

• 含义：等待表的元数据锁（如另一个线程正在执行 ALTER TABLE，其他线程需等待其完成）。
• 处理：终止长时间持有元数据锁的线程（如 kill [线程ID]）。

9. Reading from net / Writing to net

• 含义：
  • Reading from net：线程正在从客户端读取 SQL 语句。
  • Writing to net：线程正在向客户端发送结果。
• 关注点：耗时过长可能是网络延迟，或客户端处理结果过慢（如未及时读取数据）。

10. Creating sort index

• 含义：为 GROUP BY 操作创建临时排序索引（替代临时表）。
• 说明：InnoDB 优化手段，通常比 Copying to tmp table 高效。

状态分析与问题诊断

1. 识别慢查询：筛选 Time 过大的 Query 状态线程，通过 Info 字段查看 SQL，使用 explain 分析执行计划。
2. 定位锁阻塞：Locked 或 Waiting for table metadata lock 状态表明存在锁竞争，需找到持有锁的线程（通常是 Query 状态且 Time 较大的线程）并终止。
3. 优化临时表与排序：Copying to tmp table [on disk] 或 Sorting Result 频繁出现时，优先通过索引优化避免临时表和文件排序