大纲
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或其他基准测试工具对磁盘吞吐量测试。
