28. MySQL Server的优化

1. 查看 MySQL Server 参数
2. MySQL 内存管理及优化

默认情况下，MySQL有7组线程（1个主线程、4组IO线程、1个锁线程、1个错误监控线程），5.5之后新增一个purge线程：

master thread：主要负责将脏页缓存刷新到数据文件，执行purge操作，触发检查点，合并插入缓冲区等
insert buffer thread：注意负责插入缓冲区的合并操作
read thread：负责数据库的读取操作，可配置多个读线程
write thread：负责数据库的写操作，可配置多个
log thread：用于将重做日志刷新到logfile中
purge thread：MySQL5.5 之后用单独的额purge thread执行purge操作
lock thread：负责锁控制和死锁检测等
错误监控线程：负责错误监控和错误处理

通过show engine innodb status查看这些线程的状态。

和大多数数据库一样，MySQL 也提供了很多参数来进行服务器的设置。

1. 查看 MySQL Server 参数⚓

用 SHOW VARIABLES 和 SHOW STATUS 命令查看 MySQL 的服务器静态参数值和动态运行状态信息。其中前者是在数据库启动后不会动态更改的值，比如缓冲区大小、字符集、数据文件名称等；后者是数据库运行期间的动态变化的信息，比如锁等待、当前连接数等。

也可以在操作系统下直接查看数据库参数或数据库状态信息:

# 展示全部信息
$ mysqladmin -uroot -p variables

# 展示统计信息
$ mysqladmin -uroot -p status
Uptime: 404366  Threads: 2  Questions: 44  Slow queries: 0  Opens: 9  Flush tables: 2  Open tables: 35  Queries per second avg: 0.000

如果需要了解某个参数的详细定义，可以使用以下命令（MariaDB没有该命令）：

mysqld --verbose --help | more

2. MySQL 内存管理及优化⚓

InnoDB启动选项和系统变量

2.1 内存优化原则⚓

将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序的运行预留足够的内存，否则如果产生SWAO页交换，将严重影响系统性能。
MyISAM的数据文件读取依赖于操作系统自身的IO缓存，因此对于MyISAM来说应预留更多的内存给操作系统做IO缓存。
排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接树合理分配，如果设置太大，不但浪费内存资源，而且在并发连接较高时会导致物理内存耗尽。

2.2 MyISAM 内存优化⚓

2.2.1 设置 key_buffer_size⚓

这个参数是用来设置索引块（Index Blocks）缓存的大小，它被所有线程共享。对于数据块，完全依赖于操作系统的IO缓存。对于一般的MyISAM数据库，建议至少key_buffer_size设置为1/4的内存。

可通过全局变量查询：SHOW GLOBAL variables LIKE '%KEY%';。

可以检查MySQL的Key_read_requests、Key_reads、Key_write_requests、Key_writes状态变量来评估索引缓存效率。一般来说，索引块物理读比率Key_reads/Key_read_requests应小于0.01；索引块物理读比率Key_writes/Key_write_requests也应尽可能的小，但这与应用特点有关，对于更新和删除操作特别多的应用，该值可能会接近1。

除了使用读写比率衡量效率之外，还可以通过评估 key buffer 的使用率：

1 - ((key_blocks_unused * key_cache_block_size) / key_buffer_size)

一般来说，使用率大概在80%比较合适。大于80%，可能因索引缓存不足而导致性能下降；小于80%，会导致内存浪费。

2.2.2 使用多个索引缓存⚓

各个session是共享key buffer的，提高了索引存取的性能，却导致了session对key buffer的竞争。MySQL提供了多索引缓存机制，可以将不同表的索引缓存到不同的buffer中。

动态新建一个索引缓存：

MariaDB [MYISAM_TEST]> SET GLOBAL cache_test.key_buffer_size=512*1024;

GLOBAL 表示对每一个新的连接，此参数都将生效。
cache_test 是新的 key_buffer 名称。
如果需要更改参数值，可以随时通过这种方式进行重建。值为0时，表示删除此buffer。

动态指定表的索引缓存：

MariaDB [MYISAM_TEST]> CACHE INDEX sub_par_test IN cache_test;

通过配置文件在MySQL启动时自动创建并加载索引缓存：

MariaDB [MYISAM_TEST]> 
[root@dataBase ~]# cat /etc/my.cnf
…
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql 
…

-- 通过CACHE INDEX分配索引缓存
-- 通过LOAD INDEX INTO CACHE进行索引预加载，预先加载表的索引缓存到缓存区（一般对表操作后才会建立缓存）。
[root@dataBase ~]# cat mysqld_init.sql
CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache;
CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache;
LOAD INDEX INTO CACHE a, b;

删除索引缓存:

MariaDB [MYISAM_TEST]> SET GLOBAL cache_test.key_buffer_size=0;

注意：不能删除默认 key_buffer。

2.2.3 调整“中点插入策略”⚓

MySQL默认使用LRU（least recently used）策略选择要淘汰的索引数据块。但是在某些情况下回导致真正的热块被淘汰。

可以使用中点插入策略（Midpoint Insertion Strategy）来优化。《深入浅出MySQL第二版》21.2.2MyISAM内存优化章节有简单说明。

可以通过调节key_cache_division_limit来控制多大比例的缓存用作warm子表，默认值是100，即全部的缓存块都放在warm子表，不启用中点插入策略。

举例：将30%的缓存用于cache最热的索引块，可以这样设置：

MariaDB [MYISAM_TEST]> SET GLOBAL key_cache_division_limit = 70;
MariaDB [MYISAM_TEST]> SET GLOBAL hot_cache.key_cache_division_limit = 70;

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

2.2.4 table_cache⚓

MySQL手册中已经没有这个变量了，同义变量：table_open_cache。

这个参数表示数据库用户打开表的缓存数量。每个连接进来，都会至少打开一个表缓存。因此，table_cache与 max_connections有关。此外，还需要为临时表和文件保留一些额外的文件描述符。

[zzx@localhost ~]$ mysqld --verbose --help|grep table_cache=
    --table_cache=# The number of open tables for all threads.

可以通过检查mysqld的状态变量 open_tables (当前打开的表缓存数)和 opened_tables 确定这个参数是否过小，如果执行 FLUSH TABLES 操作，则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小。

-- 清空缓存
MariaDB [MYISAM_TEST]> FLUSH TABLES;

-- 查看缓存
MariaDB [MYISAM_TEST]> SHOW GLOBAL STATUS LIKE 'Open%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
                ······
| Open_tables              | 0     |
| Opened_tables            | 13    |
                ······
+--------------------------+-------+

-- 打开一张表
MariaDB [MYISAM_TEST]> SELECT * FROM CHILD;

-- 查看缓存
MariaDB [MYISAM_TEST]> SHOW GLOBAL STATUS LIKE 'Open%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
                ······
| Open_tables              | 1     |
| Opened_tables            | 14    |
                ······
+--------------------------+-------+

再次执行刚才对表 t 的查询时，两个值不会发生变化，因为已经对这张表进行了缓存。状态值open_tables对于设置 table_cache值有着更有价值的参考。

2.3 InnoDB 内存优化⚓

2.3.1 缓存机制⚓

InnoDB用一块内存做IO缓存池，同时缓存索引块和数据块。

缓存池逻辑上由几个列表组成：

free list：空闲的缓存块列表。从LRU list淘汰的数据页会被放在这里。
flush list：需要刷新到磁盘的缓存块列表。
LRU list：正在使用的缓存块，是InnoDB buffer pool的核心。

2.3.1.1 Buffer Pool LRU Algorithm⚓

InnoDB的中点插入策略。《深入浅出MySQL第二版》21.2.3InnoDB内存优化章节有简单说明。

使用LRU算法的变体，使用的数据很少在缓存中老化。

当需要向缓存池中添加一个新页（page）的空间时，最少被使用的页被驱逐，新页添加到链表的中部。这个中点插入策略将链表划分为两个子链表：

头部是访问新页的子链表。

可以通过调整InnoDB buffer pool的大小、改变young sublist和old sublist的分配比例、控制脏缓存的刷新活动、使用多个InnoDB缓存池等方法优化InnoDB的性能。

2.3.2 innodb_buffer_pool_instances 与 innodb_buffer_pool_size⚓

innodb_buffer_pool_size定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小（以byte为单位，默认是128MB）。这个值设得越高，访问表中数据需要的磁盘 I/O 就越少。在专用数据库服务器上课分配80%的物理内存给该值，但要注意避免设置过大导致页交换。

innodb_buffer_pool_instances是InnoDB缓冲池分区的数量。对具有GB范围缓冲池的系统来说，将缓冲池划分为几个独立的实例可以提高效率，通过减少因为不同的线程读取和写入缓存的页面的争用。要和innodb_buffer_pool_size（至少是1GB）配合使用。

[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_buffer_pool_size" -A 2
--innodb_buffer_pool_size=# 
The size of the memory buffer InnoDB uses to cache data
and indexes of its tables.

MariaDB [MYISAM_TEST]> SHOW VARIABLES LIKE '%innodb_buffer%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
+---------------------------------------+-----------+

innodb_buffer_pool_size是只读变量，可以在/etc/my.conf中的[mysqld]下写入innodb_buffer_pool_size = 2GB然后重启数据库。

查看缓存池的命中率：

[root@dataBase ~]# mysqladmin -uroot -pabcd1234 -S /var/lib/mysql/mysql.sock ext | grep -i innodb_buffer_pool
| Innodb_buffer_pool_bytes_data            | 6946816     |
| Innodb_buffer_pool_bytes_dirty           | 0           |
                ······
| Innodb_buffer_pool_read_requests         | 7676        |
| Innodb_buffer_pool_reads                 | 425         |
| Innodb_buffer_pool_wait_free             | 0           |
| Innodb_buffer_pool_write_requests        | 0           |

计算公式：

1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests * 100%

命中率太低时可考虑增加该值。

2.3.3 调整 old sublist 大小⚓

在LRU list中，old sublist的比例由系统参数innodb_old_blocks_pct决定（5~37，默认是37）。查看当前设置：

MariaDB [MYISAM_TEST]> SHOW GLOBAL VARIABLES LIKE '%innodb_old_blocks%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_pct  | 37    |
| innodb_old_blocks_time | 0     |
+------------------------+-------+

innodb_old_blocks_time设置为非零值可防止缓冲池被仅在短时间内引用的数据填充，例如在全表扫描期间。增加此值可提供更多保护，防止全表扫描干扰缓冲池中缓存的数据。

可以根据监视器输出的信息调整innodb_old_blocks_pct的大小。

2.3.4 调整innodb_old_blocks_time的大小⚓

innodb_old_blocks_time决定了缓存数据块由old sublist转移到young sublist的快慢（可以动态设置，可以对大的表扫描做临时调整）。当一个缓存数据块被插入到midpoint（old sublist）后，至少在其中停留innodb_old_blocks_time毫秒的时长才可能会被转移。这可以有效的避免表扫描污染buffer pool的情况。

以下两个统计变量（可以通过监视器来查看）可以反应数据块被转移的情况：

名称	描述
youngs/s	The per second average of accesses to old pages in the buffer pool LRU list that have resulted in making pages young.
non-youngs/s	The per second average of accesses to old pages in the buffer pool LRU list that have resulted in not making pages young.

如果youngs/s很高，non-young/s很低，那么应该考虑加大innodb_old_blocks_time。

2.3.5 控制innodb_buffer刷新，延长数据缓冲时间，减缓磁盘I/O⚓

在InnoDB找不到干净的可用缓存页或检查点被触发等情况下，InnoDB的后台线程会开始把“脏的缓存页”回写到磁盘文件中，这个过程叫缓存刷新。

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

innodb_max_dirty_pages_pct：缓存池中脏页的最大比例。若脏页的数量大于等于该值，InnoDB的后台线程将开始刷新缓存。
innodb_io_capacity：磁盘系统的IO能力。对于转速较低的磁盘，如7200RPM的磁盘，可降低至100；若是固态或磁盘阵列，可以适当增大。

innodb_io_capacity决定一批刷新脏页的数量，当缓存页比例达到innodb_max_dirty_pages_pct时，InnoDB将大约innodb_io_capacity个已改变的缓存页刷新到磁盘。当脏页比例小于innodb_max_dirty_pages_pct时，若innodb_adaptive_flushing=true，InnoDB讲根据函数buf_flush_get_desired_flush_rate返回的重做日志产生速度来确定要刷新的脏页数。在合并插入缓存时，InnoDB每次合并的页数是0.05 * innodb_io_capacity。

2.3.6 调整用户服务线程排序缓存区⚓

如果看到全局状态变量Sort_merge_passes很大，可以考虑增大参数sort_buffer_size来增加排序缓存区，以改善ORDER BY和GROUP字句的性能。

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

两者都是面向客户服务线程分配的，若设置过大会导致内存浪费，甚至内存交换。最好的策略是设置较小的全局join_buffer_size，而对于需要复杂连接的session单独设置较大的join_buffer_size。

2.4 InnoDB 机制及优化⚓

2.4.1 InnoDB 重做日志⚓

redo log是InnoDB保证事务ACID属性的重要机制。

更新数据时，大致的操作流程如下：

将数据读入 InnoDB buffer pool，并对相关记录加独占锁。
将UNDO信息写入undo 表空间的回滚段中。
更改缓存页中的数据，并将更新记录写入 redo buffer 中。
提交时，根据innodb_flush_log_at_trx_commit的设置，用不同的方式将 redo buffer 中的更新记录刷新到 InnoDB redo log file 中，然后释放独占锁。
最后，后台IO线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

下面针对几个参数对InnoDB的性能进行优化。

2.4.2 innodb_flush_log_at_trx_commit⚓

这个参数是用来控制 redo buffer 中的更新记录写入到日志文件以及日志文件数据刷新到磁盘的操作时机。对这个参数的设置可以对数据库在性能与数据安全之间进行折中。

[zzx@localhost ~]$ mysqld  --verbose  --help|grep  "\-\-innodb_flush_log_at_trx_commit"  -A 3
--innodb_flush_log_at_trx_commit[=#] 
Set to 0 (write and flush once per second), 1 (write and
flush at each commit) or 2 (write at commit, flush once
per second).

0：每秒进行一次日志缓存的写操作和刷新日志文件到磁盘的操作，但在事务中不做操作。若数据库崩溃，则可能会导致数据丢失；最不安全，效率最高。
1：在每个事务提交时。默认值。最安全，效率最低。
2：每次事务提交时进行写操作；每秒进行一次刷新日志文件到磁盘的操作。操作系统崩溃时，数据才可能丢失。

2.4.3 innodb_log_buffer_size⚓

重做日志缓存池的大小。

[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_log_buffer_size" -A2
--innodb_log_buffer_size=# 
The size of the buffer which InnoDB uses to write log to
the log files on disk.

默认值是 8MB，在中等强度写入负载以及较短事务的情况下，一般都可以满足服务器的性能要求。如果它的值设置太高了，可能会浪费内存，因为它每秒都会刷新一次，因此无需设置超过 1 秒所需的内存空间。通常设置为 8～16MB 就足够了，越小的系统它的值越小。

对于可能产生大量数据更新的操作，可以适当增大来减少日志写磁盘的操作。

2.4.4 innodb_log_file_size⚓

该参数含义是一个日志组（log group）中每个日志文件的大小。默认值是 5MB。

当一个日志文件写满后，InnoDB会自动切换到另一个日志文件，但切换时会触发数据库检查点（Checkpoint），这将导致缓存脏页的小批量刷新，会明显降低InnoDB的性能。**

[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_log_file_size" -A1
--innodb_log_file_size=# 
Size of each log file in a log group.

但如果设置过大，恢复时将需要更长时间，同时页不便于管理。一般来说，平均每半个小时写满一个日志文件比较合适。

计算方式：

首先计算每分钟产生的日志量：

MariaDB [MYISAM_TEST]> pager grep -i "Log sequence number"
PAGER set to 'grep -i "Log sequence number"'

MariaDB [MYISAM_TEST]> SHOW ENGINE INNODB STATUS \G SELECT SLEEP(60); SHOW ENGINE INNODB STATUS \G
Log sequence number 2680452
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

-- 因为我这数据库没有数据操作及查询，所以不会改变
Log sequence number 2680452
1 row in set (0.00 sec)

MariaDB [MYISAM_TEST]> nopager
PAGER set to stdout

MariaDB [MYISAM_TEST]> SELECT ROUND((2680452 - 2680452)/1024/1024) AS MB;
+------+
| MB   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

那么半小时的就是乘以30的数值了，这样就可以得出innodb_log_file_size的最低大小。

2.5 MySQL 并发相关参数⚓

2.5.1 max_connections⚓

控制允许连接到MySQL的最大数量，默认214。

不同的操作系统也会影响最大连接数。

还需要注意open_file_limit是否够用。

2.5.2 back_log⚓

控制监听TCP端口时设置的积压请求栈大小。

若需要数据库在较短的时间内处理大量连接请求，可考虑适当增大。

2.5.3 table_open_cache⚓

每个sql执行线程都至少需要打开一个表缓存，该参数控制所有sql执行线程可打开的表缓存的数量。若每个连接执行关联查询中所涉及到的表的最大个数为N，那么该值应设置为：max_connections * N。

在未执行flush tables的命令下，若状态变量opened_tables较大，应适当增大table_open_cache。还需要注意open_file_limit是否够用。

2.5.4 thread_cache_size⚓

该参数控制缓存客户服务线程（可加快连接数据库的速度）的数量。

计算失效率threads_created/connections来衡量该值是否合适，越接近1，则说明命中率越低，此时可考虑增加该值。

2.5.5 innodb_additional_mem_pool_size⚓

这个参数是 InnoDB 存储引擎用来存储数据库结构和其他内部数据结构的内存池的大小，其默认值是 1MB。应用程序里的表越多，则需要在这里分配越多的内存。如果 InnoDB 用光了这个池内的内存，则 InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。

没有必要给这个缓冲池分配非常大的空间，在应用相对稳定的情况下，这个缓冲池的大小也相对稳定。

[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_additional_mem_pool_size" -A 
2
--innodb_additional_mem_pool_size=# 
Size of a memory pool InnoDB uses to store data
dictionary information and other internal data

2.5.6 innodb_lock_wait_timeout⚓

InnoDB事务放弃等待一个行锁的时间。

[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_lock_wait_timeout" -A 2
--innodb_lock_wait_timeout=# 
Timeout in seconds an InnoDB transaction may wait for a row 
lock before being rolled back.

默认值是 50 秒。

对于需要快速反馈的交互式OLTP应用，可将其调小，避免事务长时间挂起；对于后台批处理操作，可以增大，以避免发生大的回滚操作。

注意：在InnoDB内部不会发生MySQL表锁定，并且此超时不适用于等待表锁定。

2.5.7 innodb_support_xa⚓

通过该参数设置是否支持分布式事务，默认值是 ON 或者 1，表示支持分布式事务。

[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_support_xa"
--innodb_support_xa Enable InnoDB support for the XA two-phase commit

如果确认应用中不需要使用分布式事务，则可以关闭这个参数，减少磁盘刷新的次数并获得更好的 InnoDB 性能。