MySQL session相关的内存设置

• read_buffer_size = 16M
  • MyISAM 表顺序扫描的缓存大小，如果要经常顺序扫描 MyISAM 表，可以通过增大 read_buffer_size 的值来改善性能，但是这个变量是每会话独占，如果设置太大，会造成内存浪费，甚至物理内存耗尽。
    • 在以下场景中适用于所有的存储引擎
    • 此选项也在以下场景中用于所有存储引擎：

      * 控制 ORDER BY 行排序时的临时文件（不是临时表，而是排序

      的文件）中缓存索引的大小

      * 控制批量插入到分区中的数据大小

      * 控制缓存嵌套查询的结果集大小

    • memory 存储引擎中该参数的值决定 MEMORY 表的内存块大小，注意是块大小，而不是 memory 表可以使用的内存总大小，memory 引擎表的数据总大小限由max_heap_table_size 参数控制。
    • 全局，会话变量，默认为 128K，最大为 2G，整型值。注意：设置值必须为 4K 的倍数，否则会截取你设定值的最接近 4K 的倍数的值作为该参数的值。取值范围为：8200~2147479552 字节。
    • PS：为啥这个变量官方描述的是用于 MyISAM，而在某些场景也适用于所有存储引擎呢？因为在这些场景下，mysql 内部保存这些临时数据仍然使用的是 MyISAM 表，在 mysql 5.7.x 的版本中，这些内部临时表可以设置使用 InnoDB 存储引擎，如果临时表设置为 InnoDB 存储引擎，则这个参数的大小就无需多关注了（除非你使用 MyISAM 引擎和 memory 引擎）。
• read_rnd_buffer_size = 32M
  • MyISAM 表排序缓存的缓存大小（注意，是 key-sorting 索引排序），如果需要对 MyISAM 表做排序，可以增大 read_rnd_buffer_size 改善性能，这个变量同样的每会话独占，也不能设置太大。
    • 该变量控制的 buffer 在从任何存储引擎读取数据的时候，都会用于缓存读取的数据，包括 MRR 优化特性也会使用。
    • 全局，会话变量，动态变量，默认值为 256K，最大值为 2G，整型值。取值范围 1~2147483647 字节（2G）。
• sort_buffer_size = 32M
  • 用于存放排序数据的缓存大小，超过这个大小就会使用文件排序。如果通过 show global status 看到 Sort_merge_passes 的值很大，可以考虑通过适当调整 sort_buffer_size 的值来增大排序缓存区（另外增加max_sort_length 参数的值时可能也需要增加 sort_buffer_size 参数的值），改善带有 order by 和 group by 子句的 sql 性能。sort_buffer_size 是每线程独占分配（每个执行排序操作的会话才会分配），不要设置过大，最好是设置一个较小的全局值，如果碰到较大表做排序，就对这个 session 设置较大的值。
    • 该参数适通用于所有存储引擎，最小值必须要足够大，至少要在排序缓冲中能够存放 15 个排序元组。
    • 5.6.4 之前的版本总是在有排序操作时直接分配整个排序缓冲给会话，从 5.6.4 版本开始优化器尝试计算排序数据的大小来分配排序缓冲的大小给会话。
    • 在 linux 上，大于 256K 或者 2M 的排序缓冲大小可能显著降低内存分配的性能。
    • 全局变量，会话变量，动态变量，默认值在不同版本中频繁变化，大于 5.6.4 版本号的 64 位版本默认值为 256K，整型值。64位平台取值范围为：32768~18446744073709551615 字节。
• tmp_table_size = 64M
  • 内存临时表超过这个大小后，就会把内存临时表转换为磁盘临时表，线程级别的参数，不要设置过大，一般全局设置不大于 100M 就可以了，否则很容易发生内存溢出，但是，如果说在临时大数据查询的时候，可以打开一个会话临时设置大一点，避免产生临时磁盘表。或者，可以使用索引查询尽量减少返回的数据量。
    • 内部用于控制内存临时表大小使用，是适用于用户创建的内存表和临时表。
    • 实际限制从 tmp_table_size 和 max_heap_table_size 两个变量的的值中取较小值。 如果内存中临时表超过这个限制，MySQL会自动将其转换为磁盘上的 MyISAM 表。 如果您执行许多高级GROUP BY 查询并且用到大量内存，请增加 tmp_table_size 的值（如果必要，也请增加 max_heap_table_size 的值）。
    • 状态变量 Created_tmp_disk_tables 持续增加时，需要增加tmp_table_size 的值。
    • Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)*100% > 10%的话，就需要注意适当提高tmp_table_size 的大小，但是不能设置太大，因为它是每个session 都会分配的，可能会导致 OOM(out of memory）。
    • 全局变量，会话变量，动态变量，默认值为 16M，整型值。取值范围为：1024~18446744073709551615 字节。
• max_heap_table_size=64M
  • 默认是 16M，可以根据需要加大，在定义内存表时，可以使用max_rows 子句指定表的最大行数来约定内存表的数据量。该参数是用于控制用户创建的内存表的数据大小。
    • 设置此变量不会影响任何现有的 MEMORY 表，除非使用如CREATE TABLE 或使用 ALTER TABLE 或 TRUNCATE TABLE 语句重新创建表、修改表结构或清空表数据。 服务器重新启动也会将现有 MEMORY 表的数据最大大小限制设置为全局max_heap_table_size 值。
    • 此变量也与 tmp_table_size 结合使用以限制内部内存表的大小。如果设置与 tmp_table_size 大小不一样，则控制内部内存临时表以较小的为准。
    • 全局变量，会话变量，动态变量，64 位版本默认值为 16M，整型值。取值范围为：16384~1844674407370954752 字节。
• join_buffer_size = 32M
  • 用于存放 join 查询中间结果的缓存大小。对于无法通过索引进行联结操作的查询，可以通过适当增大 join_buffer_size 的值来改善联结查询性能（但最好是想办法让 join 使用到索引来提高性能）。join_buffer_size 都是每线程独占分配，不要设置过大（除非能够使用BKA 特性，当使用 BKA 时，join_buffer_size 的值的大小决定了向存储引擎的每个请求中包含的键值对的多少，缓冲区越大，联结操作的右表即被驱动表的顺序 I/O 访问就越多，BKA 的作用就是把随机 I/O 访问变为顺序 I/O 访问，这可以显着提高性能），最好是设置一个较小的全局值，如果碰到较大表做联结查询，或者是比较复杂的联结表查询，就对这个 session 设置较大的值。
    • 对于简单查询的索引扫描、索引范围扫描以及因为不能使用到索引做全表扫描的 join 查询时，无论返回的数据多大，都会分配该参数的最小值 128 字节那么多 join buffer 做查询。
    • 对于简单的两个表之间的查询分配一个 join buffer，但是对于复杂的多表 join 查询且不能使用索引的时候，可能会分配多个 join buffer。
    • 全局变量，会话变量，动态变量，默认值在不同版本中频繁变化，大于 5.6.6 版本号的 64 位版本默认值为 256K，最小值为128 字节，整型值。取值范围为：128~18446744073709547520 字节。
• thread_cache_size = 64
  • 为加快数据库连接的速度，mysql 会缓存一定数量的客户端服务线程以备重用，通过这个参数可以控制 mysql 缓存客户端服务线程的数量，可以通过计算线程 cache 的失效率：Threads_created/Connections 状态变量比值来衡量 thread_cache_size 参数的设置是否合理，该值越接近 1，说明线程 cache 的命中率越低，就应该考虑增加这个参数的值。
    • 当一个客户端访问完成断开连接时，如果线程缓存中的客户端连接线程没有达到 thread_cache_size 定义的值，则这个客户端线程会被 put 到缓存中，当另外一个客户端新建连接时，如果线程缓存不为空（即有客户端线程缓存在线程缓存中），就会从缓存中取出这个客户端连接进行重用。在大多数情况下，如果你的并发连接不高的时候，这个值对性能的影响可能就看不出来，但是当有大量并发连接时，通过增大这个值可以缓解高并发连接的压力。
    • 默认值为-1，表示自动计算（计算公式：8 + (max_connections/ 100)），全局变量，动态变量，整型值。固定值取值范围为：0~16384
    • PS：这个变量对嵌入式服务器（libmysqld）没有任何影响，而在 MySQL 5.7.2 在嵌入式服务器中移除了这个变量。