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

大家好我是小悟。概述介绍10种MySQL高级性能分析和优化的SQL语句帮助DBA和开发人员定位性能瓶颈、优化查询效率。1. 找出执行最慢的查询需求定位慢查询日志中最耗时的SQL分析是否需要优化索引或重构查询。步骤开启慢查询日志设置慢查询阈值查询慢查询日志或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。步骤开启log_queries_not_using_indexes查询慢日志或使用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. 分析锁等待情况需求定位当前阻塞的事务和锁等待链。步骤查询information_schema中的锁相关表分析事务等待关系代码-- 查看当前锁等待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 TABLEBuffer Pool命中率 95%增加内存或优化查询磁盘排序每查询排序行 1000添加复合索引覆盖排序字段连接数使用率 80%增加连接池或max_connectionsJOIN效率扫描/返回比例 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 ;最佳实践定期收集统计信息ANALYZE TABLE your_table;定期清理慢查询日志避免磁盘占满使用监控工具Prometheus Grafana采集performance_schema指标设置告警阈值慢查询数量 100/小时Buffer Pool命中率 99%生产环境锁超时次数 10/小时版本差异注意MySQL 5.6-5.7performance_schema需要手动开启MySQL 8.0默认开启sys库非常实用推荐升级到8.0获得更好的性能洞察能力通过这10种SQL分析方法可以系统性地定位MySQL性能问题从慢查询、索引、锁、内存、磁盘IO等多个维度进行优化显著提升数据库响应速度和吞吐量。谢谢你看我的文章既然看到这里了如果觉得不错随手点个赞、转发、在看三连吧感谢感谢。那我们下次再见。您的一键三连是我更新的最大动力谢谢山水有相逢来日皆可期谢谢阅读我们再会我手中的金箍棒上能通天下能探海