MySQL OCP888题解054-MySQL线程的内存分配

1、原题

1.1、英文原题

Which three allocate memory per thread in MySQL?
A、query cache
B、thread cache
C、read buffer
D、internal temporary table
E、sort buffer
F、InnoDB buffer pool instance

1.2、中文翻译

在MySQL中，哪三个线程为每个线程分配内存？
A、 查询缓存
B、 线程缓存
C、 读取缓冲
D、 内部临时表
E、 排序缓冲
F、 InnoDB缓冲池实例

1.3、答案

C、D、E

2、题目解析

2.1、题干解析

本题考察MySQL线程会被单独分配哪些内存。

2.2、选项解析

1. query cache（查询缓存）是全局的，不会给每个线程单独分配，所以选项A错误。
2. InnoDB缓冲池实例也是全局的，虽然它可以配置多个，但也不会给给每个线程单独分配，所以选项F错误。
3. read buffer是线程在对MyISAM表做顺序扫描时建立的缓冲区，所以选项C正确。
4. sort buffer是线程对表做排序时分配的缓冲区，所以选项E正确。
5. 线程当遇到UNION、多表update时，会用到内存临时表，所以选项D正确。

3、知识点

3.1、知识点1：read buffer（读取缓冲）

read buffer（读取缓冲）专用于MyISAM引擎。当一个线程对对MyISAM表进行顺序扫描时，该线程中会为所扫描的表分配一个read_buffer_size大小（字节）的缓冲区。read_buffer_size的默认值是131072，这个变量的值应该是4KB的倍数。如果它被设置为不是4KB的倍数，它的值将被四舍五入为最接近4KB的倍数。如果需要做很多顺序扫描，可能需要增加这个值。

官方参考文档
官方参考文档

3.2、知识点2：MySQL内存使用估算公式

MySQL内存使用估算公式：innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB

其中 2MB表示的是每个线程都会用到一个栈。

官方参考文档

3.3、知识点3：sort buffer（排序缓冲）

MySQL会为必须执行排序的每个会话（也就是线程）都会分配sort_buffer_size大小的缓冲区。sort_buffer_size值至少必须足够大，以容纳排序缓冲区中的十五个元组。sort buffer不特定于任何存储引擎。
如果在SHOW GLOBAL STATUS输出中每秒看到许多Sort_merge_passes，可以考虑增加sort_buffer_size值，以加快ORDER BY或GROUP BY操作的速度。

官方参考文档

3.4、知识点4：内部临时表

3.4.1、tmp_table_size系统变量

tmp_table_size系统变量代表内部内存中临时表的最大尺寸。这个变量不适用于用户创建的MEMORY表。实际限制是tmp_table_size和max_heap_table_size中较小的一个。当内存中的临时表超过限制时，MySQL自动将其转换为磁盘上的临时表。internal_tmp_disk_storage_engine选项定义了用于磁盘上临时表的存储引擎。
如果你做许多高级GROUP BY查询，并且你有大量的内存，请增加tmp_table_size的值（如果有必要，也增加max_heap_table_size）。
你可以通过比较Created_tmp_disk_tables和Created_tmp_tables的值来比较创建的内部磁盘临时表的数量和创建的内部临时表总数。
’
官方参考文档

3.4.2、内部临时表的创建

服务器在如下情况会创建临时表，用户无法直接控制这种情况何时发生。

• UNION语句、某些视图(那些使用TEMPTABLE算法、UNION或者聚合的视图)、派生表。
• 为子查询或半连接物化而创建的表。
• 包含ORDER BY子句和不同GROUP BY子句的语句，或者ORDER BY或GROUP BY包含来自连接队列中第一个表以外的表的列。
• DISTINCT与ORDER BY的组合可能需要一个临时表。
• 为了评估从同一表中选择并插入到同一表中的INSERT … SELECT语句，MySQL创建一个内部临时表来保存来自SELECT的行，然后将这些行插入到目标表中。
• 多表UPDATE语句。
• GROUP_CONCAT()或COUNT(DISTINCT)表达式。

要确定一个语句是否需要一个临时表，可以使用EXPLAIN并检查Extra列，看它是否写着使用临时表。

如下情况不使用内存中的临时表，而使用磁盘上的临时表：

• 表中存在一个BLOB或TEXT列。
• 如果使用UNION或UNION ALL，在SELECT列表中存在任何最大长度大于512的字符串列（二进制字符串为字节，非二进制字符串为字符）。
• 如果一个内部临时表被创建为内存表，但变得太大，MySQL会自动将其转换为磁盘表。内存中临时表的最大尺寸由tmp_table_size或max_heap_table_size值定义，以较小者为准。这与用CREATE TABLE明确创建的MEMORY表不同。对于这样的表，只有max_heap_table_size变量决定了是否需要转换为磁盘格式。

当在内存或磁盘上创建一个内部临时表时，服务器会增加Created_tmp_tables的值。当内部临时表在磁盘上被创建时，服务器会增加Created_tmp_disk_tables的值。如果在磁盘上创建了太多的内部临时表，可以考虑增加tmp_table_size和max_heap_table_size设置。

4、总结

1. MySQL的线程中会分配read buffer（读取缓冲）、sort buffer（排序缓冲）和临时表的内存，另外key buffer（MyISAM专用）、InnoDB缓冲池都是全局级别的。
2. read buffer（读取缓冲）专用于MyISAM表的顺序读取。
3. sort buffer（排序缓冲）适用于任何引擎的排序。
4. 当在内存或磁盘上创建一个内部临时表时，服务器会增加Created_tmp_tables的值。当内部临时表在磁盘上被创建时，服务器会增加Created_tmp_disk_tables的值。
5. MySQL使用的总内存的计算公式：
   InnoDB缓冲池大小 + MyISAM的key buffer大小 + 连接数 * ( 读取缓冲大小 + 排序缓冲大小 + 二进制缓存大小 + 栈大小)
   一般一个线程栈的大小为2MB。