21. 优化MySQL Server

1. 优化MySQL Server

之前提到过，MySQL的优化，从基础层面有SQL语句的优化，表字段数据库对象的优化。另外MySQL还有本节将要介绍的Server层面上的优化。

为什么要优化？

通常一个应用的吞吐量瓶颈往往出现在数据库中的处理速度上，随着应用的使用，数据库数据逐渐增多，数据库处理压力逐渐增大，关系型数据库的数据是存放在磁盘上的，IO速度远远小于ROM。所以说通过优化，减少系统瓶颈和资源的占用，提高系统反应速度。

例如：

通过优化文件系统，来提高磁盘I/O的独写速度；
通过优化操作系统的调度策略，提高MySQL在高负荷情况下的负载能力；
通过优化表结构、索引、查询语句等使得查询响应更快。

1.1MySQL体系结构

MySQL实例由一组后台线程、一些内存块和若干服务线程组成。

后台线程包括：

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

可通过SHOW ENGINE INNODB STATUS查看线程的状态。

1.2 查看MySQL Server参数

show variables:：查看MySQL服务器静态参数值。数据库启动会不会动态更改的值，比如buffer_size、character_set、数据文件名称等。
show status：查看MySQL服务器动态运行状态信息。如锁等待、当前连接数等。

内存优化原则：

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

1.3 MyISAM内存优化

MyISAM存储引擎使用索引缓存（key buffer）缓存索引块，对于数据块没有特别的缓存机制，完全依赖于操作系统的IO缓存。

1.3.1 索引缓存

key_buffer_size设置

key_buffer_size决定MyISAM索引缓存区的大小，它直接影响MyISAM表的存取效率。建议至少分配1/4可用物理内存。

通过检查系统状态变量可评估MyISAM缓存的效率：

读比率：key_reads/key_read_requests，一般应小于0.01。
写比率：key_writes/key_write_requests，对于更新和删除特别多的应用可能接近1，对于每次更新很多行的应用就会比较小。
使用率：1-(key_blocks_unused*key_cache_block_size/key_buffer_size)，一般在0.8左右比较合适。

mysql> show variables like 'key_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| key_buffer_size          | 8388608 |
| key_cache_age_threshold  | 300     |
| key_cache_block_size     | 1024    |
| key_cache_division_limit | 100     |
+--------------------------+---------+
4 rows in set (0.00 sec)

mysql> show status like 'key_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 7173  |
| Key_blocks_used        | 2     |
| Key_read_requests      | 8     |
| Key_reads              | 4     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+

使用多索引缓存

多索引缓存机制，可以将不同的表索引缓存存放到不同的key buffer中，减少session键对key buffer的竞争导致数据被淘汰。

# 创建新的索引缓存：
set global key_buffer_name.key_buffer_size = n;
# 删除索引缓存：
# 注意：不能删除默认key_bufffer
set global key_buffer_name.key_buffer_size = 0;
# 指定表的索引缓存（不指定则使用默认索引缓存）
cache index tb_name[,...] in key_buffer_name;
# 将索引预装到默认key_buffer中
load index into cache tb_name[,...];

1.3 table_cache

每个连接进来，都会至少打开一个表缓存。因此，table_cache 与 max_connections 有关，例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200×N，这里 N 是可以执行的查询的一个联接中表的最大数量。

可以通过检查mysqld的状态变量open_tables和opened_tables确定这个参数是否过小;

open_tables：表示当前session打开的表缓存数，值不会随每次查询累加。
opened_tables：表示曾经打开的表缓存数。值会随每次查询累加。

mysql> show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2     |
+---------------+-------+

1.4 InnoDB内存优化

1.4.1 innodb_buffer_pool_size的设置

这个参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和MyISAM 存储引擎不同，MyISAM 的 key_buffer_size 只缓存索引键，而 innodb_buffer_pool_size却是同时为数据块和索引块做缓存，这个特性和 Oracle 是一样的。这个值设得越高，访问表中数据需要的磁盘 I/O 就越少。在专用数据库服务器上，可分配80%的物理内存。

可通过SHOW STATUS LIKE 'innodb_buffer_pool%'查看缓存池的使用情况。

InnoDB缓存池命中率：1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request。

mysql> show status like 'innodb_buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 470         |
| Innodb_buffer_pool_bytes_data         | 7700480     |
| Innodb_buffer_pool_pages_dirty        | 0           |
| Innodb_buffer_pool_bytes_dirty        | 0           |
| Innodb_buffer_pool_pages_flushed      | 20          |
| Innodb_buffer_pool_pages_free         | 1961        |
| Innodb_buffer_pool_pages_misc         | 1           |
| Innodb_buffer_pool_pages_total        | 2432        |
| 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      | 20445       |
| Innodb_buffer_pool_reads              | 470         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 28          |
+---------------------------------------+-------------+

1.4.2 InnoDB日志优化

当更新数据时，InnoDB内部的操作流程大致是：

将数据写入缓存池，并对相关记录加独占锁。
将UNDO信息写入undo表空间的回滚段中。
更新缓存页中的数据，并将更新记录写入重做日志缓存池（另一个redo buffer）中。
提交时，根据innodb_flush_log_at_trx_commit的设置，用不同的方式将重做日志缓存池中的更新记录刷新到重做日志文件中，然后释放独占锁。
后台IO线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

innodb_flush_log_at_trx_commit设置

值为0：在事务提交时，不会立即将缓存中的redo日志写到磁盘文件，而是每秒触发一次，并调用操作系统fsync刷新IO缓存。如果数据库崩溃，数据就会丢失。
值为1（默认值）：事务提交时，立即将缓存中的redo日志回写到磁盘文件，并调用操作系统fsync刷新IO缓存。
值为2：事务提交时，立即将缓存中的redo日志回写到磁盘文件，但并不马上调用fsync刷新IO缓存，而是每秒触发一次。如果数据库崩溃，只要操作系统没有崩溃，数据就不会丢失。

1.4.3 其他参数

innodb_lock_wait_timeout：MySQL 可以自动地监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用于在出现类似情况的时候等待指定的时间后回滚。系统默认值是 50 秒，用户可以根据应用的需要进行调整。

innodb_log_buffer_size决定InnoDB重做日志缓存池的大小，默认是8MB。

1.5 MySQL并发相关参数

max_connections设置

max_connections控制允许连接到MySQL数据库的最大数量，默认是151。

如果状态变量connection_errors_max_connections不为0且一直增长，说明不断有连接请求因数据库连接数已达到最大允许值而失败。

table_open_cache设置

table_open_cache控制所有SQL执行线程可打开的表缓存数量。该值应设置为：max_connections*N，N为每个连接执行关联查询时所涉及到的表的最大个数。

thread_cache_size设置

thread_cache_size控制MySQL缓存可供重用的客户服务线程的数量。

可以通过线程cache的失效率threads_created/connections来衡量tread_cache_size的设置是否合适。

innodb_lock_wait_timeout设置

innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间，默认为50ms。