MySQL隐形变慢排查:buffer pool命中率与磁盘临时表

    发布时间:2026-09-28 04:31 更新时间:2026-09-28 04:31 阅读量:8

    很多站长遇到过这种怪事:慢查询日志开着,long_query_time 也调到了 1 秒,但业务就是间歇性卡顿,日志里却翻不出几条记录。原因之一是慢查询日志只统计执行时间超阈值的语句,而有些语句单次很快,架不住高频执行、每次都在读磁盘或写临时表,累积起来照样把数据库拖慢。这类"隐形杀手"里,最常见的两个是 InnoDB 缓冲池命中率不足 和 磁盘临时表落盘。前者说明热数据没缓存在内存里,每次查询都要读磁盘;后者说明排序或分组的数据量超过了内存临时表上限,被迫写到磁盘。两者的现象都是"变慢",但解法完全不同——一个是加内存调参数,一个是改 SQL 或加索引。

    先判断:是缓存不足,还是排序落盘

    判断的第一步是看 InnoDB 缓冲池的命中率。执行下面这条命令,把几个关键状态值取出来:

    mysql -uroot -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_pages_free',
    'Innodb_buffer_pool_pages_total',
    'Innodb_buffer_pool_wait_free');"

    其中 Innodb_buffer_pool_read_requests 是逻辑读次数,Innodb_buffer_pool_reads 是不得不从磁盘物理读的次数。命中率大致等于 1 - reads / read_requests。如果这个比值长期低于 0.99(也就是物理读占比偏高),说明缓冲池装不下热数据,属于缓存不足。再看 Innodb_buffer_pool_pages_free,如果它接近 0,而 Innodb_buffer_pool_wait_free 在增长,说明缓冲池已经不够用,查询在等空闲页。

    需要提醒的是,命中率要结合时间段看。刚重启的实例命中率天然偏低,因为缓冲池是空的;批量导入、备份、大报表任务也会拉低命中率。所以更稳妥的做法是间隔一段时间采样两次,用差值计算,或者直接用 performance_schema 看是哪些表在被频繁物理读。

    mysql -uroot -p -e "SELECT OBJECT_SCHEMA, OBJECT_NAME,
    COUNT_READ, COUNT_FETCH, SUM_TIMER_WAIT/1000000000000 AS wait_ms
    FROM performance_schema.table_io_waits_summary_by_table
    WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema','sys')
    ORDER BY COUNT_FETCH DESC LIMIT 10;"

    这条语句会列出物理读(COUNT_FETCH 对应从存储引擎取行的次数)最多的表。哪张表排在前列,基本就是它把缓冲池挤出去了。此时可以估算一下:data_length + index_length 如果远大于 innodb_buffer_pool_size,那热数据不可能全放下,要么加内存,要么做冷热分离。

    再看磁盘临时表:排序落盘的信号

    排序、分组、去重、联表这些操作,MySQL 会创建内部临时表。内存临时表用 MEMORY 引擎,超出 tmp_table_size 或 max_heap_table_size 后就会转成磁盘临时表,通常是 Aria 或 InnoDB 引擎,速度差一个量级。看这两个状态值:

    mysql -uroot -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Created_tmp_tables',
    'Created_tmp_disk_tables',
    'Created_tmp_files',
    'Select_full_join',
    'Sort_merge_passes');"

    Created_tmp_disk_tables / Created_tmp_tables 就是磁盘临时表占比。如果这个比例超过 10%~20%,并且业务确实在变慢,就要重点查。另外 Sort_merge_passes 是排序时归并次数,数值大说明 sort_buffer_size 不够,排序被拆成多轮;Select_full_join 大则表示有没走索引的联表。

    定位具体是哪些语句在落盘,可以打开 performance_schema 的语句统计(默认就开着):

    mysql -uroot -p -e "SELECT DIGEST_TEXT,
    SUM_CREATED_TMP_DISK_TABLES AS disk_tmp,
    SUM_CREATED_TMP_TABLES AS tmp,
    SUM_SORT_MERGE_PASSES AS merge_passes,
    COUNT_STAR AS execs
    FROM performance_schema.events_statements_summary_by_digest
    WHERE SUM_CREATED_TMP_DISK_TABLES > 0
    ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC LIMIT 10;"

    DIGEST_TEXT 是语句模板,把参数替换成了占位符,方便按类型聚合。看到哪条语句磁盘临时表数高,就把它拿出来 EXPLAIN 看执行计划。

    对症下药:缓存与临时表的不同解法

    如果是缓冲池命中率低,方向是让热数据留在内存。可选的调整包括:适当提高 innodb_buffer_pool_size(在专用数据库服务器上可以给到物理内存的 50%~70%,具体看实例用途);把不常查的历史表迁到归档库;避免全表扫描把冷数据刷进缓冲池,比如给高频查询补索引。改完 innodb_buffer_pool_size 记得确认是否支持在线调整,不同版本行为有差异,以官方文档为准。

    # my.cnf 片段,改完重启生效(或按官方说明在线调整)
    [mysqld]
    innodb_buffer_pool_size = 2G
    innodb_buffer_pool_instances = 4

    如果是磁盘临时表多,重点在 SQL 和索引。常见原因有:ORDER BY 的列没有合适索引,导致必须排序;GROUP BY 走了临时表;SELECT 取了 TEXT/BLOB 大字段参与排序;联表字段字符集不一致导致索引失效。可以先尝试把 tmp_table_size 和 max_heap_table_size 调大(两者要一起调,取较小值生效),但这只是缓解,治本还是靠索引和改写语句。

    举个例子,如果 EXPLAIN 里 Extra 出现 Using temporary; Using filesort,说明排序落盘了。给排序字段建联合索引,让排序走索引顺序,往往能直接消掉临时表。对于超大分页,可以用延迟关联或覆盖索引减少参与排序的行数,这在站内另一篇分页优化的文章里也有展开。

    排查顺序建议是:先看缓冲池命中率和物理读 Top 表,排除缓存问题;再看磁盘临时表占比和具体语句,定位排序落盘。两个指标都要用差值或时间段来观察,单次采样容易误判。调整参数前先记录基线,改一次观察一次,避免同时改多个变量说不清是谁起的作用。对于持续变慢的实例,还可以配合慢查询日志和 SHOW ENGINE INNODB STATUS 一起看,多维印证,比盯着一个数字下结论要靠谱得多。

    继续阅读

    📑 📅
    Linux僵尸进程排查:ps状态Z到父进程不回收定位 2026-09-28
    PostgreSQL连接池选型与pgbouncer事务池踩坑 2026-09-27
    Docker 容器 PID 1 与信号处理:docker stop 超时强制 kill 的成因与写法 2026-09-27
    Nginx proxy_pass 带 URI 与不带 URI:路径拼接错乱排查 2026-09-27
    PostgreSQL WAL 堆积与复制槽不释放排查实战 2026-09-26
    Docker 容器内 cron 与宿主 crontab 取舍:重复执行与时区错乱排查 2026-09-28
    Linux TCP backlog 溢出排查:ss -lnt 的 Send-Q 怎么看 2026-09-28
    Nginx 缓存代理写满磁盘:max_size 与缓存目录清理实战 2026-09-28
    MySQL 8 临时表爆满排查:内存临时表与磁盘落盘定位 2026-09-28
    Redis内存碎片率异常:used_memory与RSS差在哪 2026-09-28