面试官：MySQL 内存飙升，可能是什么原因？-益华科技

如果发现 MySQL 内存飙升，面试可能是内存什么原因呢？本文以 InnoDB 存储引擎来聊一聊 MySQL 可能消耗内存的地方。

为了改进性能，飙升MySQL 分配了多个缓存。什原

InnoDB buffer pool

InnoDB buffer pool 是面试 InnoDB 引擎最重要的一个缓存区，是内存一块用于缓存表、索引和其他辅助缓冲的飙升内存区域。它允许频繁使用的什原数据直接从内存中获取，从而加快了处理速度。面试在数据库的内存专用服务器上，高达 80 的飙升内存分配给了 buffer pool。

为了提高大容量读取操作的什原效率，buffer pool 被划分为可容纳多行的面试缓存页。为了提高缓存管理的内存效率，buffer pool 使用缓存页作为节点的飙升链表来实现，并且使用 LRU 算法（变体）对最近访问较少的数据进行淘汰。

InnoDB buffer pool 的缓存区结构如下图（来自官网）：

图片

缓存区分为 new(young) 和 old 两个区域，old 区域的头和 young 区域的云服务器尾相连。

young 区域是最近频繁被访问过的数据，占整个缓存区的 5/8；old 区域则是最近访问较少的数据，占整个缓存区的 3/8，当有新的数据需要缓存时，会从 old 区域中淘汰掉部分数据页。

当 InnoDB 读取一个新的数据页到缓存区时，会插入到 old 区域的头部。如果有用户访问 old 区域的页面（不包括系统预读线程），则该页面会立即被移动到 young 区域的头部。

InnoDB buffer pool 中 young 和 old 两个区域的页面如果长时间未被访问，则会随着新页面的插入慢慢移动到列表尾部而“老化”。最终，old 区域一个长期未被访问的页面到达 old 区域的末尾最终被淘汰。

默认情况下，只要是被读取到的数据页，云服务器提供商就会被移动到 young 区域。因此类似 mysqldump 操作和不带 where 条件的查询语句，可能会将大量数据页加入到 buffer pool 并且淘汰掉其他缓存页，即使这些新加入的缓存页以后不会再被使用。同样，后台预读线程加载的数据页也会有这个问题。当然也有一些优化措施。可以参考下面两个地址的方法进行优化：

复制https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-midpoint_insertion.html https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-read_ahead.html1.2.

InnoDB buffer pool 参数由 innodb_buffer_pool_size 配置，一般情况下，在不影响服务器上其他进程运行的情况下建议这个参数设置得尽可能大一些，推荐机器内存 60%~80%。

sort buffer

sort buffer 这个参数用于 SQL 中的排序语句，MySQL 会给每个会话分配一个 sort buffer。MySQL 会把需要查询的字段放入到 sort buffer，并且按照 order by 字段进行排序，最终把排序结果返回给客户端。

如果要排序的数据超过 sort buffer 大小，就需要利用磁盘临时文件辅助排序，性能下降。

join buffer

join buffer 是 MySQL 用来优化 JOIN 语句的一块缓存区，当查询无法使用索引时，就需要用到 join buffer。

join buffer 的服务器托管核心思想是用空间换时间，通过将一部分驱动表的数据临时存放到 buffer 中，来减少与被驱动表进行匹配时需要进行的磁盘 I/O 次数，从而加速查询。查询过程如下：

1. 将驱动表中需要查询的列和连接列读取到 join buffer；

2. 遍历被驱动表，拿每一行跟 join buffer 连接行进行匹配；

3. 如果匹配成功，形成结果集返回给客户端。

join buffer 的大小由参数 join_buffer_size 控制，如果 join buffer 放不下驱动表的数据，就需要分段查询，这会增加对被驱动的扫描。

临时表

在某些情况下，MySQL server 在执行 SQL 语句时会创建内部临时表，这种情况用户是无法控制的。由 tmp_table_size 或 max_heap_table_size 这两个参数确定，两个参数的最小值就是内存临时表的最大容量。如果同时有大量查询创建大临时表，会消耗大量内存。

在下面的情况下可能会创建内存临时表：

UNION 语句，除了一些特殊情况，比如 UNION ALL，或者 UNION 语句中没有全局 ORDER BY；一些视图，比如使用 TEMPTABLE 算法、UNION 或聚合的视图；衍生表，比如下面语句：复制SELECT ... FROM (subquery) [AS] tbl_name ...1. 为子查询或半连接创建的表；包括 ORDER BY 和 GROUP BY 子句并且使用的列不一样，或 ORDER BY 和 GROUP BY 语句使用的列不在 JOIN 中的第一个表；DISTINCT 和 ORDER BY 组合的语句；SQL_SMALL_RESULT，显示指定使用临时表；INSERT ... SELECT 语句；多表 update 语句；GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式。

其他

除了上面的配置外，还有 Read Buffer（主要用于顺序读取）、Read Rnd Buffer（用于排序后的行读取）等。

最后

本文讲述了导致 MySQL 内存升高的主要原因，除了 InnoDB buffer pool 外，其他配置都是会话级别的。业务量突增、SQL 编写不规范等，都可能造成 MySQL 内存升高。