MySQL性能优化的天花板：10条你必须掌握的顶级SQL分析技巧

悟空码字
0 阅读6分钟

大家好，我是小悟。

概述

介绍10种MySQL高级性能分析和优化的SQL语句，帮助DBA和开发人员定位性能瓶颈、优化查询效率。

1. 找出执行最慢的查询

需求

定位慢查询日志中最耗时的SQL，分析是否需要优化索引或重构查询。

步骤

  1. 开启慢查询日志
  2. 设置慢查询阈值
  3. 查询慢查询日志或slow_log表

代码

-- 1. 开启慢查询记录
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

-- 2. 从performance_schema获取慢查询（MySQL 5.6+）
SELECT 
    DIGEST_TEXT AS query_sample,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT / 1000000000 AS total_secs,
    AVG_TIMER_WAIT / 1000000000 AS avg_secs,
    MAX_TIMER_WAIT / 1000000000 AS max_secs
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

2. 查找未使用索引的查询

需求

找出全表扫描或未合理使用索引的SQL。

步骤

  1. 开启log_queries_not_using_indexes
  2. 查询慢日志或使用EXPLAIN分析

代码

-- 开启记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 通过performance_schema查找未使用索引的查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED,
    SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY COUNT_STAR DESC;

3. 分析锁等待情况

需求

定位当前阻塞的事务和锁等待链。

步骤

  1. 查询information_schema中的锁相关表
  2. 分析事务等待关系

代码

-- 查看当前锁等待（MySQL 8.0）
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

-- MySQL 8.0 推荐使用 performance_schema
SELECT 
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_query
FROM sys.innodb_lock_waits;

4. 监控临时表使用情况

需求

发现创建了大量磁盘临时表的查询（性能杀手）。

代码

-- 查看临时表使用统计
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_CREATED_TMP_TABLES,
    SUM_CREATED_TMP_DISK_TABLES,
    ROUND(SUM_CREATED_TMP_DISK_TABLES / SUM_CREATED_TMP_TABLES * 100, 2) AS disk_tmp_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

5. 分析索引使用效率

需求

找出冗余索引、未使用索引和重复索引。

代码

-- 查找未使用的索引（从sys库）
SELECT 
    table_schema,
    table_name,
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM sys.schema_unused_indexes;

-- 查找重复索引
SELECT 
    table_schema,
    table_name,
    redundant_index_name,
    dominant_index_name
FROM sys.schema_redundant_indexes;

6. 查看表和索引的碎片率

需求

识别高碎片率的表，决定是否需要优化表。

代码

-- 查看表碎片情况
SELECT 
    table_schema,
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragmentation_ratio
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
  AND data_free > 0
ORDER BY fragmentation_ratio DESC;

7. 监控InnoDB缓冲池命中率

需求

评估内存是否充足，是否需要增加innodb_buffer_pool_size。

代码

-- 查看Buffer Pool命中率
SELECT 
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS read_requests,
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS physical_reads,
    ROUND((1 - (SELECT VARIABLE_VALUE 
                 FROM performance_schema.global_status 
                 WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
          (SELECT VARIABLE_VALUE 
           FROM performance_schema.global_status 
           WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')) * 100, 2) AS hit_rate;

8. 查找排序操作过多的查询

需求

识别需要大量磁盘排序的查询（filesort）。

代码

-- 查找高排序量的查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    SUM_SORT_ROWS AS total_sorted_rows,
    AVG_SORT_ROWS AS avg_sorted_rows,
    SUM_SORT_MERGE_PASSES AS merge_passes
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_ROWS > 100000
ORDER BY SUM_SORT_ROWS DESC
LIMIT 10;

9. 监控连接数和线程运行情况

需求

检测连接池是否合理，是否有连接泄漏或高并发问题。

代码

-- 查看当前连接状态
SELECT 
    command,
    COUNT(*) AS count,
    ROUND(COUNT(*) / (SELECT COUNT(*) FROM information_schema.processlist) * 100, 2) AS percentage
FROM information_schema.processlist
GROUP BY command;

-- 查看历史连接统计
SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Max_used_connections';

-- 检查是否超过max_connections阈值
SELECT 
    @@max_connections AS max_conn,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Max_used_connections') AS max_used,
    ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Max_used_connections') / @@max_connections * 100, 2) AS usage_pct;

10. 分析表连接（JOIN）效率

需求

找出多表JOIN时驱动表选择不当或缺少关联索引的问题。

代码

-- 从历史统计中找高成本JOIN查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_sent,
    ROUND(SUM_ROWS_EXAMINED / SUM_ROWS_SENT, 2) AS rows_examined_per_row_sent,
    SUM_TIMER_WAIT / 1000000000 AS total_secs
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%JOIN%'
  AND SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0) > 1000  -- 扫描行数/返回行数比例过高
ORDER BY rows_examined_per_row_sent DESC
LIMIT 10;

详细总结

性能优化的核心思路

优化维度关键指标建议阈值
慢查询查询时间 > 1秒优化索引或改写SQL
索引使用全表扫描次数大表必须走索引
锁等待等待时间 > 5秒缩短事务，降低隔离级别
临时表磁盘临时表比例 > 10%增加tmp_table_size，优化GROUP BY/ORDER BY
碎片率碎片率 > 20%执行OPTIMIZE TABLE
Buffer Pool命中率< 95%增加内存或优化查询
磁盘排序每查询排序行 > 1000添加复合索引覆盖排序字段
连接数使用率 > 80%增加连接池或max_connections
JOIN效率扫描/返回比例 > 1000检查驱动表和关联索引

日常巡检建议

-- 创建每日性能检查的存储过程示例
DELIMITER $$
CREATE PROCEDURE daily_performance_check()
BEGIN
    -- 1. 检查top10慢查询
    SELECT 'Top 10 Slow Queries' AS check_item;
    CALL top_slow_queries();
    
    -- 2. 检查高碎片表
    SELECT 'High Fragmentation Tables' AS check_item;
    CALL check_fragmentation();
    
    -- 3. 检查未使用索引
    SELECT 'Unused Indexes' AS check_item;
    CALL check_unused_indexes();
    
    -- 4. 检查锁等待
    SELECT 'Lock Waits' AS check_item;
    CALL check_lock_waits();
END$$
DELIMITER ;

最佳实践

  1. 定期收集统计信息

    ANALYZE TABLE your_table;

  2. 定期清理慢查询日志，避免磁盘占满

  3. 使用监控工具（Prometheus + Grafana）采集performance_schema指标

  4. 设置告警阈值：

    • 慢查询数量 > 100/小时
    • Buffer Pool命中率 < 99%（生产环境）
    • 锁超时次数 > 10/小时

  5. 版本差异注意：

    • MySQL 5.6-5.7：performance_schema需要手动开启
    • MySQL 8.0：默认开启，sys库非常实用
    • 推荐升级到8.0+获得更好的性能洞察能力

通过这10种SQL分析方法，可以系统性地定位MySQL性能问题，从慢查询、索引、锁、内存、磁盘IO等多个维度进行优化，显著提升数据库响应速度和吞吐量。

MySQL性能优化的天花板：10条你必须掌握的顶级SQL分析技巧.png

谢谢你看我的文章，既然看到这里了，如果觉得不错，随手点个赞、转发、在看三连吧，感谢感谢。那我们，下次再见。

您的一键三连，是我更新的最大动力，谢谢

山水有相逢，来日皆可期，谢谢阅读，我们再会

我手中的金箍棒，上能通天，下能探海

avatar
文章
阅读
粉丝