MySQL Server优化

1、MySQL内存管理及优化

1.1、内存优化原则

（1）将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序的运行预留足够的内存，否则如果产生SWAP页交换，将严重影响系统性能。

（2）MyISAM的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

（3）排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费内存资源，而且在并发连接较高时会导致物理内存耗尽。

1.2、MyISAM内存优化

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

（一）key_buffer_size设置

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

（二）使用多个索引缓存

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

（三）调整“中点插入策略”

默认情况下，MySQL使用简单的LRU策略来选择要淘汰的索引数据块。但这种算法不是很精细，在某些情况下会导致真正的热块被淘汰。

如果出现这种情况，除了使用上面介绍的多个索引缓存机制外，还可以利用“中点插入策略”来优化索引块淘汰算法。所谓“中点插入策略”，是对简单LRU淘汰算法的改进，它将LRU链表分成两部分：hot子表和warm子表，当一个索引块读入内存时，先被放到LRU链表的“中点”，即warm子表的尾部，当达到一定的命中数后，该索引块会被晋升到hot子表的尾部；此后，该数据库在hot子表流转，如果其到达hot子表的头部并超过一定时间，它将由hot子表的头部降级到warm子表的头部；当需要淘汰索引块时，缓存管理程序会选择优先淘汰warm表头部的内存块。不难理解，这种算法能够避免偶尔被访问的索引块将访问频繁的热块淘汰。

（四）调整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分配的，默认值不能设置太大。

1.2、InnoDB内存优化

（一）InnoDB缓存机制

InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存InnoDB的索引块，而且也用来缓存InnoDB的数据块。

在内部，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的头部；随着时间的推移，yound sublist和old sublist中较少被访问的缓存块将从各自链表的头部逐渐向尾部移动；需要淘汰数据库时，优先从链表尾部淘汰。这种设计同样是为了防止偶尔被访问的索引块将访问频繁的热快淘汰。

脏页的刷新存在于flush list和LRU list这两个链表，LRU上也存在可以刷新的脏页，这里是直接可以刷新的，默认BP（INNODB_BUFFER_POOL）中不存在可用的数据页的时候，会扫描LRU list尾部的innodb_lru_scan_depth个数据页（默认是1024个数据页），进行相关刷新操作，从LRU list淘汰的数据页会立刻放入到free list中去。

（二）innodb_buffer_pool_size的设置

innodb_buffer_pool_size决定InnoDB存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O就越少，性能也就越高。

（三）调整old sublist大小

在LRU list中，old sublist的比例由系统参数innodb_old_blocks_pct决定，其取值范围是5~95，默认是37。

（四）调整innodb_old_blocks_time的设置

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

（五）调整缓存池数量，减少内部对缓存池数据结构的争用

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

（六）控制innodb_buffer刷新，延长数据缓存时间，减缓磁盘I/O

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

我们通常都希望buffer pool中的数据在缓存中停留的时间尽可能长，以备重用，从而减少磁盘IO的次数。同时，磁盘IO较慢，是数据库系统最主要的性能瓶颈，我们往往也希望通过延迟缓存刷新来减轻IO系统的压力。

（七）InnoDB doublewrite

在进行脏页刷新时，InnoDB采用了双写（doublewrite）策略，这么做的原因是：MYSQL的数据页大小（一般是16KB）与操作系统的IO数据页大小（一般是4KB）不一致，无法保证InnoDB缓存页被完整、一致地刷新到磁盘，而InnoDB的redo日志只记录了数据页改变的部分，并未记录数据页的完整前像，当发生部分写或者断裂写，就会出现页面无法恢复的问题，为解决这个问题，InnoDB引入了doublewrite技术。对于要求超高性能，又能容忍极端情况下少量数据丢失的应用，可以通过在配置文件中增加innodb_doublewrite=0参数设置来关闭doublewrite，以尽量满足性能方面的要求。

1.3、调整用户服务线程排序缓存区

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

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

不过需要注意的是，sort buffer和join buffer都是面向客户服务线程分配的，如果设置过大可能造成内存浪费，甚至导致内存交换。尤其是join buffer，如果是多表关联的复杂查询，还可能会分配多个join buffer，因此最好的策略是设置较小的全局join_buffer_size，而对需要做复杂连接操作的session单独设置较大的join_buffer_size。

2、InnoDB log机制及优化

支持事务的数据库系统都需要一套机制来保证事务更新的一致性和持久性，InnoDB与Oracle等支持事务的关系数据库一样，也采用redo log机制来保证事务更新的一致性和持久性。

2.1、InnoDB重做日志

redo log是InnoDB保证事务ACID属性的重要机制，其工作原理如下图所示：

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

（1）将数据读入InnoDB buffer pool，并对相关记录加独占锁。

（2）将UNDO信息写入undo表空间的回滚段中。

（3）更改缓存页中的数据，并将更新记录写入redo buffer中。

（4）提交时，根据innodb_flush_log_at_trx_commit的设置，用不同的方式将redo buffer中的更新记录刷新到InnoDB redo log file中，然后释放独占锁。

（5）最后，后台IO线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

2.2、innodb_flush_log_at_trx_commit的设置

innodb_flush_log_at_trx_commit参数可以控制将redo buffer中的更新记录写入到日志文件以及将日志文件数据刷新到磁盘的操作时机。通过调整这个参数，可以在性能和数据安全之间做取舍。

（1）如果这个参数设置为0，在事务提交时，InnoDB不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存。

（2）如果这个参数设置为1，在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存。

（3）如果这个参数设置为2，在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作。

2.3、设置log file size，控制检查点

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

那是不是将innodb_log_file_size设得越大越好呢？理论上是，但如果日志文件设置得太大，恢复时将需要更长时间，同时也不便于管理。一般来说，平均每半个小时写满1个日志文件比较合适。

2.4、调整innodb_log_buffer_size

innodb_log_buffer_size决定InnoDB重做日志缓存池的大小，默认值是8MB，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘操作。因此，对于会在一个事务中更新、插入或删除大量记录的应用，我们可以通过增大innodb_log_buffer_size来减少日志写磁盘操作，从而提高事务处理的性能。

3、调整MySQL并发相关的参数

3.1、调整max_connections，提高并发连接

参数max_connections控制允许连接到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors_max_connections不为零，并且一直在增长，就说明不断有连接请求因数据库连接数已达到最大允许的值而失败，应考虑增大max_connections的值。

3.2、调整back_log

back_log参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果需要数据库在较短时间内处理大量连接请求，可以考虑适当增大back_log的值。

3.3、调整table_open_cache

每一个SQL执行线程至少都要打开1个表缓存，参数table_open_cache控制所有SQL执行线程可打开表缓存的数量。在未执行flush tables命令的情况下，如果MySQL状态变量opened_tables的值较大，就说明table_open_cache设置得太小，应适当增大。增大table_open_cache的值，会增加MySQL对文件描述符的使用量，因此，也要注意评估open_files_limit的设置是否够用。

3.4、调整thread_cache_size

为加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制MySQL缓存客户服务线程的数量。

3.5、innodb_lock_wait_timeout的设置

参数innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间，默认值是50ms，可以根据需要动态设置。对于需要快速反馈的交互式OLTP应用，可以将行锁等待超时时间调小，以避免事务长时间挂起；对于后台允许的批处理操作，可以将行锁等待超时时间调大，以避免发生大的回滚操作。