10个你必须掌握的顶级MySQL分析技巧_Mysql

概述

介绍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 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等多个维度进行优化，显著提升数据库响应速度和吞吐量。

到此这篇关于10个你必须掌握的顶级mysql分析技巧的文章就介绍到这了,更多相关mysql分析技巧内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！


验证码：

10个你必须掌握的顶级MySQL分析技巧

2026年04月16日 • Mysql •我要评论

概述