MySQL 内存优化

一、内存优化原则

• 将尽量多的内存分配给 MySQL 做缓存，但要给操作系统和其他程序的运行预留足够的内存。
• myisam 的数据文件读取依赖于操作系统自身的 I/O 缓存，因此，如果有 myisam 表，要预留更多的内存给操作系统做 IO 缓存。
• 排序区，连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置过大，不但浪费内存资源，而且在并发连接较高时会导致物理内存耗尽。

二、myisam 内存优化

myisam 存储引擎使用 key buffer 缓存索引块，以加速 myisam 索引的读写速度，对于 myisam 表的数据块，MySQL 没有特别的患处机制，完全依赖操作系统的 IO 缓存。

1. key_buffer_size 设置

key_buffer_size 决定 myisam 索引块缓存区的大小，它直接影响 myisam 表的存取效率。可以在 MySQL 的参数文件中设置该值。
对弈一般数据库服务器，建议至少将 1/4 可用内存分配给 key_buffer_size。

可以通过检查 key_read_requests、key_write_requests 和 key_writes 等 MySQL 状态变量来评估索引缓存的效率。
一般来说，索引块物理读比率：
key_reads / key_read_requests 应小于0.01
索引块写比率也应尽可能小，但这与应用特点有关，对于更新和删除操作特别多的应用，key_writes / key_write_requests 可能会接近 1，而对于每次更新很多行记录的应用，key_writes / key_write_requests 就会比较小。

除通过索引块的物理读写比率衡量 key buffer 外，也可以通过评估 key buffer 的使用率来判断索引缓存设置是否合理。
key buffer 使用率的计算公式如下：
1 - ((key_blocks_unused * key_cache_block_size) / key_buffer_size)
一般来说，使用率在 80% 左右比较合适，大于 80% 可能因索引缓存不足而导致性能下降；小于 80% 会导致内存浪费。

2. 使用多个索引缓存

MySQL 通过各 session 共享的 key buffer 提高了 myisam 索引存取的性能，但它并不能消除 session 间对 key buffer 的竞争。比如，一个 session 如果对某个很大的索引进行扫描，就可能将其他的索引数据块挤出索引缓存区，而这些索引块可能是其他 session 要用的热数据。
为减少 session 间对 key buffer 的竞争，MySQL 从 5.1 版本开始引入了多缓存的机制，从而可以将不同表的索引缓存到不同的key buffer 中：

# hot_cache 是新建索引缓存的命名，global 关键字表示新建缓存对每一个新的连接都有效。
mysql> set global hot_cache.key_buffer_size = 128*1024;

# 删除刚刚创建的索引缓存
mysql> set global hot_cache.key_buffer_size = 0;

默认情况下，MySQL 将使用默认的key buffer 缓存 myisam 表的索引，可以用 cache index 命令指定表的索引缓存：

MySQL [sakila]> create table t2 (id int , name varchar(30)) engine myisam;
Query OK, 0 rows affected (0.03 sec)

MySQL [sakila]> create table t1 (id int , name varchar(30)) engine myisam;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> cache index t1,t2 in hot_cache;
+-----------+--------------------+----------+----------+
| Table     | Op                 | Msg_type | Msg_text |
+-----------+--------------------+----------+----------+
| sakila.t1 | assign_to_keycache | status   | OK       |
| sakila.t2 | assign_to_keycache | status   | OK       |
+-----------+--------------------+----------+----------+
2 rows in set (0.00 sec)

更常见的做法是通过配置文件在 MySQL 启动时自动创建并加在索引缓存：

key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 1G
init_file=/path/mysqld_init.sql

在 mysql_init.sql 文件中，可以通过 cache index 命令分配索引缓存，并用 load index into cache 命令来进行索引预加载：

cache index sales in hot_cache;
cache index sales2 in cold_cache;
load index into cache sales,sales2

3. 调整“中点插入策略”

中点插入策略
中点插入策略（midpoint insertion strategy）是对简单 lru 淘汰算法的改进，它将 lru 链分成两部分：hot 子表和 warm 子表，当一个索引块读入内存时，先放到 lru 链表的“中点”，即 warm 子表的尾部，当达到一定的命中次数后，该索引块会被晋升到 hot 子表的尾部；此后，该数据块在 hot 子表流转，如果其到达 hot 子表的头部并超过一定时间，它将由 hot 子表的头部降级到 warm 子表的头部；当需要淘汰索引块时，缓存管理程序会优先淘汰 warm 表头部的内存块。这种算法能够避免偶尔被访问的索引块将访问频繁的热块淘汰。

可以通过调节 key_cache_division_limit 来控制多大比例的缓存用做 warm 子表，其默认值是 100，意思是全部缓存块都放在 warm 子表，其实也就是不启用“中点插入策略”。如果我们希望将大致 30% 的缓存用来 cache 最热的索引块，可以对做如下设置

set global key_cache_division_limit = 70  
set global hot_cache key_cache_division_limit = 70

除了调节 warm 子表的比例外，还可以通过 key_cache_age_threshold 控制数据块由 hot 子表向 warm 子表降级的时间，值越小，数据块就越快被降级。对于有 N 个块的索引缓存来说，如果一个在 hot 子表头部的索引块，在最后 N * key_cache_age_threshold / 100 次缓存命中内未被访问过，就会被降级到 warm 子表。

4. 调整 read_buffer_size 和 read_rnd_buffer_size

如果需要经常顺序扫描 myisam 表，可以通过增大 read_buffer_size 的值来改善性能，但需要注意的是：read_buffer_size 是每个 session 独占的，如果默认值太大，就会造成内存浪费，甚至导致物理内存耗尽。
对于需要做排序的 myisam 表查询，如带有 order by 子句的 sql， 适当增大 read_rnd_buffer_size 的值，也可以改善此类 sql 的性能。read_rnd_buffer_size 也是按 session 分配的，默认值不能太大。

三、 innodb 内存优化

1. innodb 缓存机制

innodb 用一块内存区做 io 缓存池，该缓存池不仅用来缓存 innodb 的索引块，也用来缓存 innodb 的数据块，这一点与 myisam 不同。

在内部，innodb 缓存池逻辑上由 free list、flush list 和 lru list 组成：

• free list ： 空闲缓存块列表
• flush list ： 是需要刷新到此磁盘的缓存块列表
• lru list ： 是 innodb 正在使用的缓存块，它是 innodb buffer pool 的核心。

innodb 使用的 lru 算法与 myisam 的“中点插入策略”lru算法很类似，大致原理是：将 lru list 分为 young sublist 和 old sublist，数据从磁盘读入时，会将该缓存块插入到 lru list 的“中点”，即 old sublist 的头部；经过一定时间的访问（由 innodb_old_blocks_time 系统参数决定），该数据块将会由 old sublist 转移到 young sublist 的头部，也就是整个lru list 的头部；随着时间推移，young sublist 和 old sublist 中较少被访问的缓存块将从各自链表的头部逐渐向尾部移动；需要淘汰数据块时，优先从链表尾部淘汰。这种设计同样是为了防止偶尔被访问的索引块将访问频繁的热块淘汰。

2. innodb_buffer_pool_size 的设置

innodb_buffer_size 决定 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问 innodb 表需要的磁盘 io 就越少，性能也就越高。在一个专用的数据库服务器上，可以将 80% 的物理内存分配给 innodb buffer pool。

通过以下命令查看 buffer pool 的使用情况：

zj@bogon:/usr/local/mysql$ mysqladmin -u root -p -S /tmp/mysql.sock ext | grep -i innodb_buffer_pool
Enter password: 
| Innodb_buffer_pool_dump_status                | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status                | Buffer pool(s) load completed at 170918 15:07:09 |
| Innodb_buffer_pool_resize_status              |                                                  |
| Innodb_buffer_pool_pages_data                 | 456                                              |
| Innodb_buffer_pool_bytes_data                 | 7471104                                          |
| Innodb_buffer_pool_pages_dirty                | 0                                                |
| Innodb_buffer_pool_bytes_dirty                | 0                                                |
| Innodb_buffer_pool_pages_flushed              | 39                                               |
| Innodb_buffer_pool_pages_free                 | 32312                                            |
| Innodb_buffer_pool_pages_misc                 | 0                                                |
| Innodb_buffer_pool_pages_total                | 32768                                            |
| Innodb_buffer_pool_read_ahead_rnd             | 0                                                |
| Innodb_buffer_pool_read_ahead                 | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted         | 0                                                |
| Innodb_buffer_pool_read_requests              | 3329                                             |
| Innodb_buffer_pool_reads                      | 422                                              |
| Innodb_buffer_pool_wait_free                  | 0                                                |
| Innodb_buffer_pool_write_requests             | 515                                              |
zj@bogon:/usr/local/mysql$

可用以下公式计算 innodb 缓存池的命中率：

(1 - innodb_buffer_pool_read / innodb_buffer_pool_read_request) * 100

如果命中率太低，则应考虑扩充内存，增加 innodb_buffer_pool_size 的值。

3. 调整 old sublist 大小

调整 old_sublist 的比例由系统参数 innodb_old_blocks_pct 决定，其取值范围是 5 ~ 95, 默认值是 37。
通过以下命令可以查看其当前设置：

MySQL [(none)]> show global variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.00 sec)

4. 调整 innodb_old_blocks_time 的设置

innodb_old_blocks_time 参数决定了缓存数据块由 old sublist 转移到 young sublist 的快慢，当一个缓存数据块被插入到 midpoint（old sublist）后,至少要在 old sublist 停留超过 innodb_old_blocks_time(ms)后，才有可能被转移到 young list。

5. 调整缓存池数量，减少内部对缓存池数据源结构的争用

MySQL 内部不同线程对 innodb 缓存池的访问在某些阶段是互斥的，这种内部竞争也会产生性能问题, 尤其在高并发和 buffer pool 较大的情况下。为解决这个问题，innodb 的缓存系统引入了 innodb_buffer_poolinstances 配置参数，对于较大的缓存池，适当增大此参数的值，可以降低并发导致的内部缓存访问冲突，改善性能。innodb 缓存系统会将参数 innodb_buffer_pool_size 指定大小的缓存平分为 innodb_buffer_pool_instances 个 buffer pool。

6. 控制 innodb buffer 刷新，延长数据缓存事件，减缓磁盘 I/O

在 innodb 找不到干净可用缓存页或检查点被触发等情况下，innodb 的后台线程就会开始把“脏的缓存页”回写到磁盘文件中，这个过程叫缓存刷新。
·
通常都希望 buffer pool 中的数据在缓存中停留的时间尽可能长，以备重用，从而减少磁盘 IO 的次数。磁盘 IO 慢，是数据库系统最主要的性能瓶颈，可以通过延迟缓存刷新来减轻 IO 压力。

innodb buffer pool 的刷新快慢主要取决于两个参数。

innodb_max_dirty_pages_pct

它控制缓存池中脏页的最大比例，默认是 75% ，如果脏页的数量达到或超过该值，innodb 的后台线程将开始缓存刷新。

innodb_io_capacity

它代表磁盘系统的 IO 能力，其值在一定程度上代表磁盘每秒可完成 IO 的次数。innodb_io_capacity 默认值是 200，对于低转速的磁盘，如 7200RPM 的磁盘，可将该值降低到 100，而对于固态硬盘和由多个磁盘组成的盘阵，它的值可以适当增大。

innodb_io_capacity 决定一批刷新脏页的数量，当缓存池脏页的比例达到 innodb_max_dirty_pages_pct 时，innodb 大约将 innodb_io_capacity 个已改变的缓存页刷新到磁盘。在合并插入缓存时，innodb 每次合并的页数是 0.05 * innodb_io_capacity。
若 innodb_buffer_pool_wait_free 的值增长较快，则说明 innodb 经常在等待空闲缓存页，如果无法增大缓存池，那么应将 innodb_max_dirty_pages_pct 的值调小，或将innodb_io_capacity 的值提高，以加快脏页的刷新。

7. innodb doublewrite

当进行脏页刷新时，innodb 采用了双写（double write）策略，这么做的原因是：MySQL 的数据页大小（一般是 16KB）与操作系统的 IO 数据页大小（一般是 4KB）不一致，无法保证 innodb 缓存页被完整、一致的刷新到磁盘，而innodb 的 redo 日志只记录了数据页改变的部分，并未记录数据页的完整前像，当发生部分写或断裂写时（比如讲缓存页的第一个 4KB 写入磁盘后，服务器断电），就会出现页面无法恢复的问题，为解决这个问题，innodb 引入了 doublewrite 技术。

doublewrite 机制的实现原理：

用系统表空间的一块连续磁盘空间（100个连续数据页，大小为 2MB）作为 doublewrite buffer，当进行脏页刷新时，首先将脏页的副本写到系统表空间的 doublewrite buffer 中，然后调用 fsync() 刷新操作系统 IO 缓存，确保副本被真正写入磁盘，最后 innodb 后台 IO 线程将脏页刷新到磁盘数据文件。

在做恢复时，若㘝发现不一致的页，innodb 会用系统表空间 doublewrite buffer 区的相应副本来恢复数据页。

四、 调整用户服务线程排序缓冲区

如果通过 show global status 看到 sort_merge_passes 的值很大，可以考虑通过调整参数 sort_buffer_size 的值来增大排序缓存区，以改善带有 order by 子句或 group 子句 sql 的性能。

对于无法通过索引进行连接操作的查询，可以尝试通过增大 join_buffer_size 的值来改善性能。

不过需要注意的是，sort buffer 和 join buffer 都是面向客户线程分配的，如果设置过大可能造成内存浪费。

最好的策略是：设置较小的全局 join_buffer_size,而对需要做复杂连接操作的 session 单独设置较大的 join_buffer_size。