接手一台 MySQL 服务器free -h一看内存快被吃光了swap 开始频繁交换业务接口响应变慢这恐怕是每个运维和后端开发都遇到过的场景。MySQL 内存占用过大是个老生常谈却又特别容易踩坑的问题很多情况下不是一条慢查询能解释的也不是简单调一个参数就能解决的。我这些年排查过不少内存异常升高的案例从连接风暴到缓冲池配置失误从表缓存堆积到查询缓存残留真正动手梳理下来发现里面的门道比想象中要多得多。这篇文章我想把排查 MySQL 内存占用过大的完整思路整理出来从内存到底消耗在哪里、怎么用系统命令和 MySQL 内置表定位根因再到关键参数怎么调、隐藏的内存大户怎么揪出来最后附上一份实战排查的记录和常见问题速查表。内容面向的是需要实际动手解决线上问题的 DBA、后端开发和对 MySQL 内存管理机制感兴趣的读者尽量做到拿到就能用。1. 内存都去哪了先看透 MySQL 的内存分配模型1.1 全局缓冲与线程缓冲两种完全不同的“吃法”很多人排查内存问题第一步就去看innodb_buffer_pool_size这是对的但只看这个远远不够。MySQL 的内存消耗总体上分两大类全局缓冲区和线程缓冲区。全局缓冲区是整个实例共享的启动时就分配好比如 InnoDB 的 Buffer Pool、日志缓冲区、键缓存Key Buffer线程缓冲区则是每个会话连接私有的各自分配互不影响比如排序缓冲区Sort Buffer、连接缓冲区Join Buffer、读缓冲区Read Buffer、结果集缓存等。如果把 MySQL 比作一家餐厅全局缓冲区像公共厨房的食材仓库一轮菜备好后所有包间共用线程缓冲区则像每位客人面前的小料碟来一个客人就要摆一套客人越多小料碟的总量就越可观。连接数一旦涨上来线程缓冲区的内存占用可能成百上千 MB 地增长这也是为什么并发量高的业务经常出现“连接数正常、内存却爆掉”的诡异情况。1.2 各类组件在内存里的“固定份额”和“弹性份额”从 MySQL 的官方文档和实际运行情况来看内存占用通常包含以下固定项innodb_buffer_pool_size负责缓存数据页和索引页是内存占用的大头innodb_log_buffer_size是重做日志的缓冲key_buffer_size主要服务于 MyISAM 表的索引缓存虽然 MyISAM 用得少了但如果有存量表还是会占。除此之外还有自适应哈希索引、数据字典、行数据字典、锁信息等这些都属于“隐藏耗内存”的角色。动态变化的部分主要是线程缓冲区和各类临时结构。sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size这四个参数是典型的“按连接分配”的内存如果每个连接都是默认值 256KB 或更大几百个连接同时跑复杂查询瞬间就能吃掉几个 GB。tmp_table_size和max_heap_table_size则控制着内存临时表的大小超过限制会转换成磁盘临时表如果这两个值设置得很高大量并发排序或去重操作也会让内存飙升。了解了这两种分配方式后面排查时心里就有了一张地图。2. 上手排查从系统指标到 SQL 语句一步步定位是谁在吃内存2.1 别被 free 命令骗了先分清缓存和真实占用排查内存问题的第一步不是看 MySQL而是先在操作系统层面搞清楚整体内存的分配情况。很多人看到free -g里 used 很高就慌了但其实 Linux 会把空闲内存用作 page cache这部分内存是可以随时释放的对业务没有实际影响。要特别注意available这个值它才是真正可供新程序申请使用的内存余量。如果available已经低到几百 MB甚至开始使用 swap那就确实需要重点排查了。确认内存真的吃紧之后用top或ps aux --sort-%mem看一眼 mysqld 进程本身的 RSS常驻内存集同时对比实例启动初期的基线内存值。如果 mysqld 进程暴涨了几个 GB而buffer_pool设置没有变化那么增长大概率来自连接相关的线程缓冲、临时内存表、表缓存或者某种内存泄漏。这里有个容易忽略的点SHOW GLOBAL STATUS里的Memory_used统计的只是部分分配内存并不完整MySQL 实际占用内存最好以操作系统侧 RSS 为准再结合内部统计来定位。2.2 用 performance_schema 把“内存大头”捞出来如果版本支持最快的方法是直接查performance_schema的内存统计表。开启相关采集后可以执行类似下面的 SQL把内存分配量按线程和账号汇总排序SELECT THREAD_ID, EVENT_NAME, SUM(NUMBER_OF_BYTES_ALLOC) AS total_bytes FROM performance_schema.memory_summary_by_thread_by_event_name GROUP BY THREAD_ID, EVENT_NAME ORDER BY total_bytes DESC LIMIT 20;执行完之后重点看memory/sql/String::value、memory/sql/THD::main_mem_root、memory/innodb/buf_buf_pool这些事件名称。THD相关内存占用巨大基本可以断定是某条连接或某个会话执行了耗费内存的操作innodb/buf_buf_pool类事件高则说明buffer_pool本身在正常缓存数据页这种情况未必是故障要看命中率和数据量。还可以从sys.memory_global_by_current_bytes这种系统库视图快速看到全局内存消耗排行两步走基本就能知道问题出在全局还是线程级。2.3 用排他法“做对照实验”缩小范围内部统计查不到明确结果时我会用排除法做对照实验。找一个业务低谷窗口把连接池里的请求全部停掉让 MySQL 进入几乎无连接的“闲置”状态然后观察 RSS 有没有明显回落。如果内存掉下来了说明是业务连接和查询在搞事情如果内存还是居高不下说明是全局缓冲或者缓存结构在占用例如表缓存、存储程序缓存、或者某个长连接没有释放资源。做了这个对照实验后还能顺便验证一个常见猜想性能监控客户端比如一些第三方的数据库管理平台是否会通过大量打开表、频繁查询状态变量来推高内存。实际排查中我遇到过一例因为监控账号频繁查询information_schema导致数据字典内存上涨的案例这提醒我们凡是会影响 MySQL 内部行为的外部工具也要纳入排查范围。3. 参数调优实战正确处理 buffer pool、连接数与临时表3.1 innodb_buffer_pool_size 的常见误区和计算公式innodb_buffer_pool_size是 MySQL 内存配置里最核心的参数绝大多数内存问题也都是从这引出来的。一个常见的误区是想把内存“物尽其用”把 64GB 的机器给 MySQL 设置 56GB 的 buffer pool结果系统自身、线程缓冲、临时表和其他进程一算下来直接 OOM。其实 buffer pool 大小应该以“热数据总量”为基准而不是以物理内存为基准。通常的估算思路是统计库中实际常用表的总数据量information_schema.tables中的DATA_LENGTH INDEX_LENGTH再乘以一个热数据比例一般取 60% 到 80%作为 buffer pool 的参考值。如果物理内存充足官方也建议把 buffer pool 调大到能完全容纳热数据同时预留 20% 到 30% 的内存给系统和其他缓冲。我处理过一个案例某个业务系统核心表加起来不到 40GB却被设置了 86GB 的 buffer pool内存当然紧张调回 48GB 后不仅没增加磁盘 IO反而因为避免了 swap 提升了整体稳定性。另外 MySQL 5.7 以后支持innodb_buffer_pool_instances将 buffer pool 拆成多份能降低大缓冲下的并发锁竞争但实例数量不宜无脑设大通常和innodb_buffer_pool_size每份不低于 1GB 的原则搭配。3.2 连接数、线程栈和排序缓冲之间的“乘法效应”连接数导致的线程缓冲膨胀是内存问题的第二号元凶。max_connections每增加 100实际占用的内存绝不是 100 个连接的基础栈这么简单。每个线程的基础栈大小由thread_stack控制默认 256KB再加上sort_buffer_size、join_buffer_size、read_buffer_size等默认缓冲实测一个空闲连接通常能占到 1MB 到 2MB如果业务代码里还有连接池中反复执行大查询的坏习惯单个连接占用 5MB 甚至更高都不奇怪。假设max_connections 500平均每个连接占 1.5MB光是连接相关的基础开销就是 750MB再叠加高并发场景下临时分配的排序和连接缓冲轻松超过 2GB。遇到这种情况一方面要把max_connections调整到合理范围不建议盲目放开另一方面可以通过减小sort_buffer_size、join_buffer_size等参数到 2MB 或 4MB 来限制单连接内存峰值。注意这些参数是“按需分配”的设置太大意味着最坏情况下内存可能是几何级膨胀调优时需要时刻想到“一个会话 一次查询 这类缓冲”的最坏情况乘法。3.3 临时表和排序没有慢查询也一样吃内存临时表是排查中的盲区。tmp_table_size和max_heap_table_size共同决定内部内存临时表的上限超过上限会转为磁盘临时表。实际使用中如果将这两个值设置得过高比如 512MB一旦某个连接执行了GROUP BY、DISTINCT、ORDER BY或子查询内存临时表就可能把内存吃到不可控。这里有个深刻教训某个报表查询明明数据量不大但因为有大量字符串列的GROUP BY内部临时表创建了多个大对象最终把 8GB 内存全部吃掉调整tmp_table_size为 64MB 后内存消耗明显下降查询也并没有因为落盘变慢很多。操作层面建议用SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables和Created_tmp_tables的比值来观察临时表落盘情况如果磁盘临时表比例很高可以先优化 SQL 而不是强行调大内存上限。同时要注意max_heap_table_size优先于tmp_table_size生效两者中较小的值决定实际上限只调一个参数没用。4. 那些不显眼却吃内存的“隐藏大户”4.1 表缓存table_open_cache和数据库实例的“收藏癖”table_open_cache代表 MySQL 能缓存的打开表的数量。实际运行过程中每次访问表都会把表对象放入缓存频繁访问几百张表的情况下缓存数量会逐步逼近上限。如果这个值被设置得远高于实际表的数量或者表数量极大几千张以上缓存对象本身就会消耗大量内存。每个被缓存的表对象涉及表结构、列定义、分区信息等内容一张普通表的缓存开销可能在数 KB 到几十 KB 之间几千张表累计就是几百 MB。排查时可以用SHOW GLOBAL STATUS LIKE Open_tables和Opened_tables观察打开表数量和累计打开次数。Open_tables接近table_open_cache上限而Opened_tables持续增长说明缓存一直在换入换出不仅内存浪费还会增加表打开开销。针对表数量特别多的实例不要只调大缓存数量而是要结合 object cache 的实际需求和连接并发情况确定合理值同时注意分区表会打开多个分区对象单个表的缓存成本比想象中还高。4.2 performance_schema、日志缓冲和监控体系自身的开销很多运维同学忽略了监控系统本身会造成内存开销。启用performance_schema后MySQL 会为各类事件、等待、语句统计分配内存默认配置下占用可能达到数百 MB。如果线上实例内存本身紧张而监控平台又要求开启全量events_statements_history_long和events_waits_history_long那 performance_schema 的内存占用会进一步走高。这时候需要评估监控项的必要性只保留关键事件采集把不常用的等待事件关掉。SHOW ENGINE PERFORMANCE_SCHEMA STATUS可以看到当前各组件占用的内存量这是一个常被忽略但非常有用的命令。除此之外binlog_cache_size在每个事务提交时分配如果业务里大型事务很多临时 binlog 缓冲也会产生峰值max_binlog_cache_size设置过大碰到大事务更会一次性占用巨量内存。日志相关缓冲最容易被统计遗漏因为那部分内存并没有体现在Memory_used状态里但操作系统 RSS 会如实反映。4.3 存储程序、事件调度器与预处理语句的“常驻内存”存储过程、函数、触发器和事件调度器在执行后会将编译对象缓存在内存中。每次调用存储过程MySQL 都要在内存中保存执行计划、局部变量和游标状态。如果业务大量使用存储过程且调用频繁这部分内存开销会累积。特别是事件调度器Event Scheduler如果开了且有很多每分钟执行的事件还需要为每个事件维持独立的线程与缓存不够重视时会发现内存被“细水长流”地吃掉。预处理语句PREPARE / EXECUTE也是同样的道理没有执行DEALLOCATE PREPARE的语句会一直挂在会话对象上。排查这一类问题时performance_schema的events_statements_summary_by_digest配合会话列表基本能定位到长期占用内存的会话。还有一个小技巧定期重启可以临时回收这类“隐藏内存”但根本解法是梳理存储过程和事件调度器的使用频率让缓存规模可控。5. 常见问题排查与避坑记录5.1 一张速查表覆盖八成线上内存问题排查了这么多案例之后我整理成了下面的速查表基本能对应到八九成的内存异常场景。它能帮你快速把问题归类下一步再针对性去调参数或改代码症状可能原因定位手段解决思路mysqld RSS 明显超过 buffer pool 预期线程缓冲 连接数过高SHOW PROCESSLIST、memory_summary_by_thread限制连接数、调整连接池配置内存逐步上涨业务低谷也不回落表缓存 / 数据字典 / 存储程序缓存Open_tables、performance_schema调整table_open_cache、定期清理无用存储程序大查询并发时内存瞬时暴涨排序缓冲、临时表慢查询日志、Created_tmp_disk_tables优化 SQL、降低tmp_table_size实例可用内存低但 buffer pool 命中率正常performance_schema、日志缓冲SHOW ENGINE PERFORMANCE_SCHEMA STATUS精简监控采集项长连接不活跃但内存一直被占用会话内存未释放、预处理语句堆积检查连接空闲状态、会话内存统计增加连接池空闲回收、定期重启应用连接启动后内存快速占满buffer_pool_size设置过大计算热数据总量的合理值调低buffer_pool_size并留足系统余量5.2 实战中踩过的坑调优不当引发的次生问题内存调优很容易掉进“头疼医头”的陷阱。我见过有人为了压内存一口气把sort_buffer_size和join_buffer_size降到 128KB结果大量排序改用磁盘临时文件慢查询量翻了倍。内存和性能是跷跷板调参会互相牵连不能只看一个指标。另一方面部分云数据库的控制台提供了自动调内存参数的功能但自动调优基于通用经验不一定适配业务特有的连接模式最终还是要人工结合业务低峰期的监控数据来决定。还有一个值得特别提醒的坑不要频繁重启 MySQL 来“释放内存”。重启后 buffer pool 需要重新预热短时间内的磁盘 IO 会明显增大如果业务流量不小反而会造成一次新的性能抖动。真正的做法是记录下 RSS 的增长曲线根据业务特征判断是持续增长还是周期性峰值再决定调参还是优化 SQL。5.3 建立“基线 趋势”的监控习惯排查内存问题最怕没有参照物。建议从接手一台 MySQL 的第一天起就记录它运行平稳时的空闲内存值、连接数、缓冲池命中率、临时表落盘比例等关键指标形成基线数据。以后遇到内存告警先对照基线看是短时突发还是持续偏离。我自己的习惯是把核心指标采集到一个本地文件或运维平台保留至少一个月的曲线这样每次排查都能快速判断是配置变化、业务增长还是代码质量问题。再一个就是要关注连接池层的“上下文切换”和“会话状态”比如有大量连接处于Sleep状态但内存占用很高多半是应用侧连接没有有效复用这种场景调 MySQL 参数很难根治反而要去查连接池的minimumIdle和maximumPoolSize配置是否合理。6. 一次完整的线上排查复盘从头到尾的实操记录6.1 从告警到定位按步骤把问题缠住之前某业务系统出现内存告警现象是实例 32GB 内存available一度降到 1.2GBswap 使用持续增长。接手后我先看系统层free -g确认 page cache 不算高mysqld 的 RSS 已经到 26GB明显异常。接着查SHOW GLOBAL STATUS里的Threads_connected一度达到 700 多而max_connections是 1000说明连接数已经跑到一个很高的水位。再看SHOW PROCESSLIST有大量来自应用侧的连接处于Query状态同时还在执行一些关联查询。初步判断是连接数高 排序缓冲叠加导致的内存占用。随后我查了performance_schema.memory_summary_by_thread_by_event_name发现一个特定业务账号的THD::main_mem_root占用接近 8GB该账号对应的正是报表查询连接。进一步看慢查询日志定位到几条大范围JOIN和GROUP BY的 SQL每条执行都会创建较大的内部临时表和排序缓冲。到这里根因已经浮出水面单条查询内存峰值高加上连接数大导致线程缓冲总量高两者叠加把内存推到了临界点。6.2 参数调整与效果验证改哪些、怎么改、如何确认方案分成两步走。第一步限制连接池协调应用团队把连接池最大连接数从 200 降到 80MySQL 端max_connections从 1000 调整到 500并优化了连接池的空闲超时让不用会话及时释放。第二步针对查询会话把sort_buffer_size从 32MB 调到 4MBjoin_buffer_size从 16MB 调到 2MBmax_heap_table_size和tmp_table_size从 256MB 调低到 64MB避免一条查询构造超大内存临时表。同时保留性能压测验证空间避免排序落盘导致性能恶化。调整后观察半小时mysqld RSS 从 26GB 降到 16GB 左右available回升到 8GBswap 使用也稳定下来。慢查询数量没有明显上升因为真正慢的业务 SQL 已经在前一步做了索引优化而不是靠内存硬扛。通过这次复盘我最大的体会是内存问题往往不是单点配置问题而是连接数、查询复杂度、缓冲参数设置相互作用的结果单独调任何一项都只能临时缓解系统性调整才是根治之道。7. 最后分享一点个人习惯我平时接手一个 MySQL 实例时一定会先建立“内存基线检查单”innodb_buffer_pool_size、max_connections、thread_stack、sort_buffer_size、join_buffer_size、tmp_table_size、max_heap_table_size、table_open_cache这些参数逐一记录同时把正常业务时段的 RSS 和available记下来。等再次出问题时对照这份检查单能省掉大量重复定位的时间。内存问题的排查确实有套路可循但最核心的永远是“先理解业务如何用数据库再谈怎么调参数”。希望这次整理的内容能帮你在下次遇到 MySQL 内存告警时少走一些弯路。