MySQL底层概述—5.InnoDB参数优化
大纲
1.内存相关参数优化
(1)缓冲池内存大小配置
(2)配置多个Buffer Pool实例
(3)Chunk(块)大小配置
(4)InnoDB缓存性能评估
(5)Page管理相关参数
(6)Change Buffer相关参数优化
2.日志相关参数优化
(1)日志缓冲区相关参数配置
(2)日志文件参数优化
3.IO线程相关参数优化
(1)查询缓存相关的参数
(2)脏页刷盘相关的参数
(3)LRU链表相关的参数
(4)脏页刷盘相关的参数
1.内存相关参数优化(Buffer Pool参数优化)
(1)缓冲池内存大小配置
(2)配置多个Buffer Pool实例
(3)Chunk(块)大小配置
(4)InnoDB缓存性能评估
(5)Page管理相关参数
(6)Change Buffer相关参数优化
(1)缓冲池内存大小配置
一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘，因此如果有很多增删改操作，通过设置该参数可大量减少磁盘IO次数。
专用数据库服务器上，可将缓冲池大小设置为物理内存的60% - 80%。
一.查看缓冲池大小
Buffer Pool的默认大小是128M。
# 134217728 / 1024 / 1024 = 128
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+二.在线调整InnoDB缓冲池大小
innodb_buffer_pool_size可以动态设置，允许在不重启服务器情况下动态调整缓冲池大小。
mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 256M
Query OK, 0 rows affected (0.10 sec)
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+三.监控在线调整缓冲池的进度
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------------------------+
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
+----------------------------------+----------------------------------------------------------------------+(2)配置多个Buffer Pool实例
当Buffer Pool的大小是GB级别时：可以将一个Buffer Pool分割成几个独立的实例，这样能降低多个线程同时读写缓存页的竞争性而提高并发性。
通过innodb_buffer_pool_instances参数可以调整实例个数。如果有多个实例，则缓存的数据页会随机放置到任意的实例中，且每个实例都有独立的Buffer Pool所有的特性。
Buffer Pool可以存放多个Instance，每个Instance由多个Chunk组成。Instance的数量范围和Chunk的总数量范围分别为1-64，1-1000。
innodb_buffer_pool_instances的默认值是1，最大可调整成64，需要在初始化数据库时完成。
mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+-------+(3)Chunk(块)大小配置
增大或减小缓冲池大小时，将以Chunk为单位进行操作，Chunk的大小是由参数innodb_buffer_pool_chunk_size决定的。引入Chunk是为了方便在线修改缓冲池大小，修改时以Chunk为单位拷贝Buffer Pool。
mysql> show variables like 'innodb_buffer_pool_chunk_size';
+-------------------------------+-----------+
| Variable_name | Value |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
+-------------------------------+-----------+缓冲池大小innodb_buffer_pool_size：必须始终等于或者是chunk_size * instances的倍数，如果不等于则MySQL会自动调整。
假设innodb_buffer_pool_chunk_size=128M，而且innodb_buffer_pool_instances=16，那么可以计算出：innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances=2G。如果设置innodb_buffer_pool_size=9G，则会被自动调整为2G的倍数10G。
(4)InnoDB缓存性能评估
当前配置的innodb_buffer_pool_size是否合适，可通过分析InnoDB缓冲池的缓存命中率来验证，以下公式可以计算InnoDB Buffer Pool命中率：
命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads) * 100参数1：innodb_buffer_pool_reads
表示缓冲池无法满足的请求数，要从磁盘读取。
参数2：innodb_buffer_pool_read_requests
表示从缓冲池中读取页的请求数，如果命中率低于90%，则可考虑增加innodb_buffer_pool_size。
mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| 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 | 12701 |
| Innodb_buffer_pool_reads | 455 |
+---------------------------------------+-------+
-- 此值低于90%，则可以考虑增加innodb_buffer_pool_size；
mysql> select 12701 / (455 + 12701) * 100 ;
+-----------------------------+
| 12701 / (455 + 12701) * 100 |
+-----------------------------+
| 96.5415 |
+-----------------------------+(5)Page管理相关参数
innodb_page_size只能在初始化MySQL实例之前配置，不能在之后修改。如果没有指定值，则使用默认页面大小初始化实例。
查看Page页的大小(默认16K)如下：
mysql> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+查看Page页管理状态的相关参数如下：
mysql> show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data | 515 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 334 |
| Innodb_buffer_pool_pages_free | 15868 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 16383 |
+----------------------------------+-------+
# pages_data：缓冲池中包含数据的页数，包括脏页和干净页
# pages_dirty：内存中修改但未写入文件的缓冲池数据页数量
# pages_flushed：表示从InnoDB缓冲池中刷新脏页的请求数
# pages_free：显示InnoDB缓冲池中的空闲页面
# pages_misc：用于管理或哈希索引而不能用作普通页的数目
# pages_total：缓存池的页总数目，单位是Page一.优化建议
innodb_page_size的官方描述：
MySQL5.7增加了对32K和64K页面大小的支持，所以MySQL5.7支持最小4K最大64K的页面大小设置。
默认的16K或更大的页面大小适用于各种工作负载，特别是涉及表扫描的查询和涉及批量更新的DML操作。对于涉及许多小写操作的OLTP工作负载，较小的页面大小可能更有效。
二.Page大小对于行存储的影响
对于4K、8K、16K和32K的页大小，最大行大小略小于页大小的一半。当然，不包括存储在页外的任何可变长度的列。
三.Page大小对于索引的影响
如果在创建MySQL实例时通过指定innodb_page_size选项，将InnoDB页面大小减少到8K或4K，则索引键的最大长度将按比例降低。这是基于16K页面大小的3072字节限制。也就是说，当页面大小为8K时，最大索引键长度为1536字节。当页面大小为4K时，最大索引键长度为768字节。
(6)Change Buffer相关参数优化
Change Buffer是MySQL5.5加入的新特性，Change Buffer是Insert Buffer的加强。Insert Buffer只针对insert有效，Change Buffer对insert、delete、update、purge都有效。
一.配置Change Buffer使用模式
innodb_change_buffering配置参数说明：
mysql> show variables like '%innodb_change_buffering%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_change_buffering | all |
+-------------------------+-------+
| 选项 | 说明 |
| ------- | --------------------------- |
| inserts | 插入缓冲 |
| deletes | 删除标记缓冲 |
| changes | 更新缓冲，由两个缓冲区组成 |
| purges | 缓冲在后台发生的物理删除操作 |
| all | 表示启用上面所有配置(默认) |
| none | 表示不启用任何配置 |二.配置Change Buffer大小
Change Buffer占用Buffer Pool空间，默认占25%，最大允许占50%。可以根据读写业务量来调整innodb_change_buffer_max_size，在写多读少+更新后数据不会被立即查询的场景，更适合用Change Buffer。
mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
+-------------------------------+-------+
1 row in set (0.00 sec)三.查看Change Buffer的工作状态
-- 查看Change Buffer的工作状态
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
# size：表示已经合并到辅助索引页的数量
# free list len：表示空闲列表长度
# seg size：表示当前Change Buffer的大小
# merges：表示合并的次数
# merged operations：每个操作合并次数
# insert：表示插入操作
# delete mark：表示删除标记操作
# delete：表示物理删除操作2.日志相关参数优化
(1)日志缓冲区相关参数配置
(2)日志文件参数优化
(1)日志缓冲区相关参数配置
日志缓冲区的大小，一般默认值16M都够用了。但如果事务中含有BLOB/TEXT等大字段，这个缓冲区会被很快填满会引起额外的IO负载。可以根据情况配置更大的日志缓冲区，从而有效提高InnoDB的效率。
一.通过参数innodb_log_buffer_size查看日志缓冲区大小
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+二.通过参数innodb_log_files_in_group查看日志组文件个数
日志组根据需要来创建，日志组的成员则需要至少2个，以实现循环写入并作为冗余策略。
mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2 |
+---------------------------+-------+三.通过参数innodb_log_file_size查看redo日志文件大小
参数innodb_log_file_size用于设定InnoDB日志组中每个日志文件的大小。参数innodb_log_file_size默认48M，参数innodb_log_file_size是一个全局的静态参数，不能动态修改。每组的文件大小不能超过512G，所以每个日志文件的大小不能超过256G。
mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+(2)日志文件参数优化
首先看日志文件大小设置对性能的影响。
一.设置过小
影响一：参数innodb_log_file_size设置太小，会导致redo log日志文件频繁切换。频繁触发数据库的检查点checkpoint，导致刷新脏页到磁盘的次数增加，从而影响性能。
影响二：处理大事务时，将所有的日志文件写满了，事务内容还没有写完，这样就会导致日志不能切换。
二.设置过大
参数innodb_log_file_size如果设置太大，虽然可以提升IO性能。但是当数据库意外宕机时，二进制日志文件可能会很大，那么恢复的时间就必然很长，而且恢复时间不可控，受多方面因素影响。
三.优化建议
如何设置合适的日志文件大小，根据实际生产的优化经验，一般是计算一段时间内生成的redo log大小。InnoDB的日志文件的大小最少应该承载一个小时的业务日志量，需要估算出当前系统的一小时内产生的日志数量。
步骤1：获取一分钟内的redo log日志数据量
想要估计redo log的大小，就需要抓取一段时间内Log Sequence Number的数据来计算。自系统修改开始，就不断修改页面，不断生成redo日志。为了记录一共生成了多少日志，InnoDB设计了全局变量Log Sequence Number，简称LSN。LSN不是从0开始的，而是从8704字节开始。
-- pager分页工具, 只获取sequence的信息
mysql> pager grep sequence;
PAGER set to 'grep sequence'
-- 查询状态，并倒计时一分钟
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5399154
1 row in set (0.00 sec)
1 row in set (1 min 0.00 sec)
-- 一分时间内所生成的数据量 5406150
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5406150
-- 关闭pager
mysql> nopager;
PAGER set to stdout步骤2：根据一分钟的redo log日志数据量，推算一小时内的日志数据量
select (5406150 - 5399154) / 1024 as kb_per_min;
+------------+
| kb_per_min |
+------------+
| 6.8320 |
+------------+
select (5406150 - 5399154) / 1024 * 60 as kb_per_min;
+------------+
| kb_per_min |
+------------+
| 409.9219 |
+------------+3.IO线程相关参数优化
(1)查询缓存相关的参数
(2)脏页刷盘相关的参数
(3)LRU链表相关的参数
(4)脏页刷盘相关的参数
数据库属于IO密集型的应用程序，其主要职责就是数据的管理及存储工作。从内存中读取一个数据库数据的时间是微秒级别，从一块普通硬盘上读取一个IO的时间是毫秒级别。要优化数据库，IO操作是必须要优化的，尽可能将磁盘IO转化为内存IO。
(1)查询缓存相关的参数
查询缓存Query Cache会保存SQL查询返回的完整结果。当查询命中查询缓存Query Cache时，会跳过解析、优化和执行阶段，立刻返回结果。查询缓存Query Cache会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这些表相关的查询缓存都将失效。
一.查看查询缓存Query Cache是否开启
-- 查询是否支持查询缓存
show variables like 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
-- 查询是否开启查询缓存 默认关闭
show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+二.在my.ini中添加参数开启查询缓存Query Cache
query_cache_size=128M
query_cache_type=1
# query_cache_type=0，缓存禁用；
# query_cache_type=1，缓存所有的结果；
# query_cache_type=DENAND，只缓存在select语句中通过SQL_CACHE指定需要缓存的查询；三.测试能否缓存查询
mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
# Qcache_free_blocks：缓存中目前剩余的block数量
# Qcache_free_memory：空闲缓存的大小
# Qcache_hits：命中缓存次数
# Qcache_inserts：未命中然后进行正常查询
# Qcache_lowmem_prunes：查询因内存不足而被移除出查询缓存记录数
# Qcache_not_cached：没有被缓存的查询数量
# Qcache_queries_in_cache：当前缓存中缓存的查询数量
# Qcache_total_blocks：当前缓存的block数量四.优化建议
开启查询缓存适用于读多写少的场景，如果写比较多，那么更新查询缓存会损耗性能。
查询缓存的开启主要需要两个参数配合：也就是query_cache_size和query_cache_type。如果数据变化不多(写少)，一般缓存大小query_cache_size设置为256M，当然也可以通过计算Query Cache的命中率来进行调整：
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100(2)脏页刷盘相关的参数
一.参数innodb_max_dirty_pages_pct的作用
该参数是InnoDB用来设置Buffer Pool中脏页的百分比，默认是75。当脏页数量占比超过该参数设置的值时，InnoDB会启动刷脏页的操作。
-- innodb_max_dirty_pages_pct 参数可以动态调整，最小值为0，最大值为99.99，默认值为75
show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+二.优化建议
该参数比例值越大，从内存到磁盘的写入操作就会相对减少，所以该参数比例值越大越能一定程度减少写入操作的磁盘IO。但是如果这个比例值过大，当数据库Crash后重启的时间可能会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。最大不建议超90，一般重启恢复的数据如果超1G，启动速度就会变慢。
(3)LRU链表相关的参数
一.两个相关参数的作用
参数innodb_old_blocks_pct是用来确定LRU链表中冷数据区域所占比例，参数innodb_old_blocks_pct默认37，表示冷数据区域默认占用37%。
mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37 |
+-----------------------+-------+参数innodb_old_blocks_time是用来控制冷数据区域中Page的转移策略，参数innodb_old_blocks_time默认值是1秒。新的Page页在进入LRU链表时，会先插入到冷数据区域的头部。然后Page需要在冷数据区域中停留innodb_old_blocks_time后，下一次对该Page的访问才会使其移动到热数据区域的头部。
mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000 |
+------------------------+-------+二.优化建议
在没有大表扫描的情况下，并且数据多为频繁使用的数据时，可以增加innodb_old_blocks_pct的值，减小innodb_old_blocks_time的值，让数据页能够更快和更多的进入的热点数据区。
(4)与脏页刷盘相关的参数
一.两个相关参数的作用
InnoDB1.0.x版本开始提供innodb_io_capacity参数，它的作用在两个方面：
作用1：合并插入缓冲时，每秒合并插入缓冲数量为innodb_io_capacity值的5%，默认200*5%=10。
作用2：从缓冲区刷新脏页时(Checkpoint)，每秒刷新脏页数量为innodb_io_capacity的值，默认是200。若用户使用了SSD类的磁盘，或者将几块磁盘做了RAID，即当存储设备拥有更高的IO时，可将innodbio_capacity_max的值调高，更好利用磁盘IO的吞吐量。
mysql> show variables like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+二.优化建议
在有频繁写入的操作时，可以对该参数进行调整。该参数设置的大小取决于硬盘的IOPS，即每秒的输入输出量(或读写次数)，什么样的磁盘配置应该设置innodb_io_capacity参数的值是多少。下面仅供参考，建议通过sysbench或其他基准测试工具对磁盘吞吐量测试。
