mysql内存相关的配置

一、mysql为每个链接分配的和内存

以下四个参数是为每个链接（线程）分配的大小。（一百个链接，可能会分配一百次）

1、sort_buffer_size:

排序缓冲区的大小。不管需要多少，都是一次性分配该参数值大小的内存。

• max_length_for_sort_data: 用于指定排序数据的最大长度，根据需要排序的字段定义的大小总和与这个配置值进行比较。

2、join_buffer_size:

连接查询缓冲区的大小，默认值是256k。为每个连接查询分配该参数值大小的内存。

• 较大的 join_buffer_size 可以容纳更多行，减少对被驱动表的全表扫描次数。

3、read_buffer_size:

MyISAM 表进行全表扫描时分配的查询缓冲区大小。有查询需要时分配一次性参数值大小的内存；参数值大小必须是4k的倍数。

4、read_rnd_buffer_size:

索引缓冲区的大小（回表查询 ID 的缓存）。有查询需要时分配，只分配所需的大小而非整个参数值大小。

二、InnoDB 缓冲池（Innodb_buffer_pool）

1、Innodb_buffer_pool_size:

该参数控制InnoDB 缓冲池的的大小，在MySQL 5.7之前修改该值需要重启服务器。

• 对于单独的 MySQL 服务器，可以设置为：总内存 - （每个线程所需内存 * 链接数）- 系统保留内存。
• 根据手册建议，设置为总内存的75%。
• 当缓冲池很大时，重启服务器需要较长时间来刷新脏页。

2、Buffer Pool中的LRU链表：

• innodb_old_blocks_pc：该参数控制了InnoDB缓冲池中旧数据（已经不活跃）所占的比例。默认值为37，表示旧数据在缓冲池中的比例为37%。较低的值会导致更多的旧数据被保留在缓冲池中，可能增加缓冲池的使用率，但也可能影响新数据的读取性能。较高的值则允许更多的新数据进入缓冲池，但也可能导致频繁的缓冲池刷新和旧数据的淘汰。根据实际情况，可以适当调整这个值来平衡性能需求和缓冲池利用率。
• innodb_old_blocks_time：该参数定义了多长时间内被认为是“旧数据”。默认值为0，表示没有时间限制。当一个页面在缓冲池中处于未被访问状态的时间超过设定的阈值时，该页面将被视为旧数据并可能被替换出缓冲池。通过设置适当的阈值，可以确保缓冲池中保留的是最常访问的数据，避免过多的旧数据占用缓冲池资源。

3、change buffer:

是一种用于提高写入性能的内存数据结构。它用于延迟和合并磁盘上的物理页更改。当进行INSERT、UPDATE或DELETE操作时，Change Buffer会将修改记录到内存中，然后再批量地将这些修改应用到磁盘上的数据页。

• innodb_change_buffering： 这个参数控制Change Buffer的工作方式。它有三种可选值：

            all：表示所有的数据修改都会被缓冲，默认值。
             none：表示不使用Change Buffer。
             inserts：只缓冲INSERT操作。
  

• innodb_change_buffer_max_siz： 这个参数定义了Change Buffer的最大大小，以字节为单位。默认值为25%的缓冲池大小。根据系统的写入负载和可用内存，可以适当调整此参数以控制Change Buffer的大小。

4、innodb_max_dirty_pages_pct:

这个参数定义脏页在缓冲池中所允许的最大比例，以百分比表示。默认值为 75%。

• 当脏页所占的比例超过的设定值时，InnoDB 将开始触发后台线程将脏页刷新到磁盘上的数据文件中，以确保缓冲池中的脏页数量维持在可接受的范围内。
• 较低的 innodb_max_dirty_pages_pct 值可以使脏页更快地写入磁盘，从而减少系统崩溃或意外停机时数据丢失的风险，但可能会牺牲一些写入性能。
• 较高的 innodb_max_dirty_pages_pct 值可以提高性能，因为不必频繁写入磁盘，但可能会增加系统恢复时间并增加数据丢失的风险。

三、 MyISAM 缓冲池

1、key_buffer_size:

该参数是 MyISAM 缓冲池的大小，只缓存索引。即使所有表都使用 InnoDB 引擎，也要设置该值，因为 MySQL 系统表仍在使用 MyISAM 引擎。

2、可以通过执行以下查询获取 MyISAM 表索引所占空间的大小：

SELECT sum(index_length) from information_schema.tables where ENGINE='myisam';