MySQL编程优化篇（七）优化MySQL Server

喜欢ヅ旅行 2023-06-23 10:59 2阅读 0赞

**目录**

MySQL体系结构概览

内存管理及优化

优化原则

InnoDB优化策略

调整参数来优化InnoDB的性能

InnoDB doublewrite

InnoDB log机制及优化

InnoDB重做日志

innodb\_flush\_log\_at\_trx\_commit

--------------------

# MySQL体系结构概览 #

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

在默认情况下，MySQL有7组后台线程，分别是1个主线程，4组IO线程，1个锁线程，1个错误监控线程。MySQL 5.5之后又新增了一个 purge线程。

后台线程的主要功能略。

# 内存管理及优化 #

## 优化原则 ##

在调整MySQL内存分配时，要注意以下几点。

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

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

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

## InnoDB优化策略 ##

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

在内部，InnoDB缓存池逻辑上由 free list、flush list和LRU list组成。顾名思义，free list是空闲缓存块列表，flush list是需要刷新到磁盘的缓存块列表，而LRU list是 InnoDB正在使用的缓存块，它是 InnoDB buffer pool的核心。

## 调整参数来优化InnoDB的性能 ##

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>innodb_buffer_pool_size</td> 
   <td>InnoDB 存储引擎表数据和索引数据的最大缓存区大小。在一个专用的数据库服务器上，可以将80%的物理内存分配给 InnoDB buffer pool，但一定要注意避免设置过大而导致页交换。</td> 
  </tr> 
  <tr> 
   <td>innodb_old_blocks_pct</td> 
   <td>old sublist的大小，其取值范围是5~95，默认值是37（约等于3/8）</td> 
  </tr> 
  <tr> 
   <td>innodb_old_blocks_time</td> 
   <td>缓存数据块由old sublist转移到young sublist的快慢，当一个缓存数据块被插入到 midpoint（old sublist）后，至少要在 old sublist 停留超过innodb_old_blocks_time（ms）后，才有可能被转移到new sublist</td> 
  </tr> 
  <tr> 
   <td>innodb_buffer_pool_instances</td> 
   <td>调整缓存池数量，减少内部对缓存池数据结构的争用，InnoDB 缓存系统会将参数innodb_buffer_pool_size指定大小的缓存平分为 innodb_buffer_pool_instances个 buffer pool</td> 
  </tr> 
  <tr> 
   <td>innodb_max_dirty_pages_pct</td> 
   <td>缓冲池中脏页的最大比例，默认值是75%，如果脏页的数量达到或者超过该值，InnoDB的后台线程将开始缓存刷新</td> 
  </tr> 
  <tr> 
   <td>innodb_io_capacity</td> 
   <td>磁盘IO能力，一定程度上代表磁盘每秒可完成的IO次数</td> 
  </tr> 
  <tr> 
   <td>sort_buffer_size</td> 
   <td>调整排序缓存区，以改善带有 order by子句或group子句SQL的性能。对于无法通过索引进行连接操作的查询，可以尝试通过增大join_buffer_size的值来改善性能。</td> 
  </tr> 
  <tr> 
   <td>innodb_log_file_size</td> 
   <td>设置日志文件大小，一般来说，平均半小时写满一个日志文件比较合适</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>允许连接到MySQL数据库的最大数量</td> 
  </tr> 
  <tr> 
   <td>innodb_lock_wait_timeout</td> 
   <td>InnoDB事务等待行锁的时间，默认值是50ms</td> 
  </tr> 
 </tbody> 
</table>

## InnoDB doublewrite ##

在进行脏页刷新时，InnoDB 采用了双写（doublewrite）策略。

**原因：**

> MySQL的数据页大小（一般是 16KB）与操作系统的 IO 数据页大小（一般是 4KB）不一致，无法保证InnoDB缓存页被完整、一致地刷新到磁盘，而InnoDB的redo日志只记录了数据页改变的部分，并未记录数据页的完整前像，当发生部分写或断裂写时（比如将缓存页的第一个 4KB写入磁盘后，服务器断电），就会出现页面无法恢复的问题，为解决这个问题，InnoDB引入了doublewrite技术。

# InnoDB log机制及优化 #

## InnoDB重做日志 ##

当更新数据时，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线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

## innodb\_flush\_log\_at\_trx\_commit ##

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

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

innodb\_flush\_log\_at\_trx\_commit参数的默认值是 1，即每个事务提交时都会从 log buffer写更新记录到日志文件，而且会实际刷新磁盘缓存，显然，这完全能满足事务的持久化要求，是最安全的，但这样会有较大的性能损失。