数据库参数设置优化

MySQL体系结构

我们一般可以将MySQL的结构分为四层，最上层为客户端连接器，主要包括了数据库连接、授权认证、安全管理等，该层引用了线程池，为接入的连接请求提高线程处理效率。

第二层是Server层，主要实现SQL的一些基础功能，包括SQL解析、优化、执行以及缓存等。

第三层包括了各种存储引擎，主要负责数据的存取，这一层涉及到的Buffer缓存。

最下面一层是数据存储层，主要负责将数据存储在文件系统中，并完成与存储引擎的交互。

1. 查询语句

一个应用服务需要通过第一层的连接和授权认证，再将SQL请求发送至SQL接口。SQL接口接收到请求之后，会先检查查询SQL是否命中Cache缓存中的数据，如果命中，则直接返回缓存中的结果；否则，需要进入解析器。

解析器主要对SQL进行语法以及词法分析，之后，便会进入到优化器中，优化器会生成多种执行计划方案，并选择最优方案执行。

确定了最优执行计划方案之后，执行器会检查连接用户是否有该表的执行权限，有则查看Buffer中是否存在该缓存，存在则获取锁，查询表数据；否则重新打开表文件，通过接口调用相应的存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返回结果集。

2. 更新语句

数据库更新SQL的执行流程其实跟查询SQL差不多，只不过执行更新操作的时候多了记录日志的步骤。在执行更新操作时MySQL会将操作的日志记录到 binlog（归档日志）中，这个步骤所有的存储引擎都有。而InnoDB除了要记录 binlog 之外，还需要多记录一个 redo log（重做日志）。

redo log 主要是为了解决 crash-safe 问题而引入的。我们知道，当数据库在存储数据时发生异常重启，我们需要保证存储的数据要么存储成功，要么存储失败，也就是不会出现数据丢失的情况，这就是crash-safe了。

我们在执行更新操作时:

• 首先会查询相关的数据，
• 之后通过执行器执行更新操作，并将执行结果写入到内存中，
  • 同时记录更新操作到redo log的缓存中，此时redo log中的记录状态为prepare，
  • 并通知执行器更新完成，随时可以提交事务。
• 执行器收到通知后会执行binlog的写入操作，此时的binlog是记录在缓存中的，
• 写入成功后会调用引擎的提交事务接口，更新记录状态为commit。
• 之后，内存中的redo log以及binlog都会刷新到磁盘文件中。

内存调优

基于以上两个SQL执行过程，我们可以发现，在执行查询SQL语句时，会涉及到两个缓存。

Query Cache调优

第一个缓存是刚进来时的Query Cache，它缓存的是SQL语句和对应的结果集。这里的缓存是以查询SQL的Hash值为key，返回结果集为value的键值对，判断一条SQL是否命中缓存，是通过匹配查询SQL的Hash值来实现的。

很明显，Query Cache可以优化查询SQL语句，减少大量工作，特别是减少了I/O读取操作。我们可以通过以下几个主要的设置参数来优化查询操作：

参数	功能
have_query_cache	表示是否支持QueryCache
query_cache_limit	表示QueryCache存放的单条Query最大结果集，默认值为1M，结果集大小超过该值的Query不会被Cache
query_cache_min_res_unit	表示QueryCache每个结果集存放的最小内存大小，默认为4k
query_cache_size	表示系统中用于QueryCache的内存大小
query_cache_type	OFF、DEMAND（DEMAND表示只有在查询语句中使用 SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存)

我们可以通过设置合适的 query_cache_min_res_unit 来减少碎片，这个参数最合适的大小和应用程序查询结果的平均大小直接相关，可以通过以下公式计算所得：

(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

Qcache_free_memory 和 Qcache_queries_in_cache 的值可以通过以下命令查询：

show status like 'Qcache%'

Query Cache虽然可以优化查询操作，但也仅限于不常修改的数据，如果一张表数据经常进行新增、更新和删除操作，则会造成Query Cache的失效率非常高，从而导致频繁地清除Cache中的数据，给系统增加额外的性能开销。

这也会导致缓存命中率非常低，我们可以通过以上查询状态的命令查看 Qcache_hits，该值表示缓存命中率。如果缓存命中率特别低的话，我们还可以通过query_cache_size = 0或者query_cache_type来关闭查询缓存。

Buffer缓存调优

不同的存储引擎，使用的Buffer也是不一样的。这里我们主要讲解两种常用的存储引擎。

1. MyISAM存储引擎参数设置调优

MyISAM存储引擎使用key buffer缓存索引块，MyISAM表的数据块则没有缓存，它是直接存储在磁盘文件中的。

我们可以通过key_buffer_size设置key buffer缓存的大小，而它的大小并不是越大越好。正如我前面所讲的，key buffer缓存设置过大，实际应用却不大的话，就容易造成内存浪费，而且系统也容易发生SWAP页交换，一般我是建议将服务器内存中可用内存的1/4分配给key buffer。

如果要更准确地评估key buffer的设置是否合理，我们还可以通过缓存使用率公式来计算：

1 - ((key_blocks_unused * key_cache_block_size) / key_buffer_size)

key_blocks_unused表示未使用的缓存簇（blocks）数

key_cache_block_size表示key_buffer_size被分割的区域大小。
key_blocks_unused*key_cache_block_size则表示剩余的可用缓存空间（一般来说，缓存使用率在80%作用比较合适）。

2. InnoDB存储引擎参数设置调优

InnoDB Buffer Pool（简称IBP）是InnoDB存储引擎的一个缓冲池，与MyISAM存储引擎使用key buffer缓存不同，它不仅存储了表索引块，还存储了表数据。查询数据时，IBP允许快速返回频繁访问的数据，而无需访问磁盘文件。InnoDB表空间缓存越多，MySQL访问物理磁盘的频率就越低，这表示查询响应时间更快，系统的整体性能也有所提高。

我们一般可以通过多个设置参数来调整IBP，优化InnoDB表性能。

innodb_buffer_pool_size

IBP默认的内存大小是128M，我们可以通过参数innodb_buffer_pool_size来设置IBP的大小，IBP设置得越大，InnoDB表性能就越好。但是，将IBP大小设置得过大也不好，可能会导致系统发生SWAP页交换。所以我们需要在IBP大小和其它系统服务所需内存大小之间取得平衡。MySQL推荐配置IBP的大小为服务器物理内存的80%。

我们也可以通过计算InnoDB缓冲池的命中率来调整IBP大小：

(1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_request) * 100

但如果我们将IBP的大小设置为物理内存的80%以后，发现命中率还是很低，此时我们就应该考虑扩充内存来增加IBP的大小。

innodb_buffer_pool_instances

InnoDB中的IBP缓冲池被划分为了多个实例，对于具有数千兆字节的缓冲池的系统来说，将缓冲池划分为单独的实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统的并发性。该参数项仅在将innodb_buffer_pool_size设置为1GB或更大时才会生效。

• 在windows 32位操作系统中，如果innodb_buffer_pool_size的大小超过1.3GB，innodb_buffer_pool_instances默认大小就为innodb_buffer_pool_size/128MB；否则，默认为1。
• 而在其它操作系统中，如果innodb_buffer_pool_size大小超过1GB，innodb_buffer_pool_instances值就默认为8；否则，默认为1。

为了获取最佳效率，建议指定innodb_buffer_pool_instances的大小，并保证每个缓冲池实例至少有1GB内存。通常，建议innodb_buffer_pool_instances的大小不超过innodb_read_io_threads + innodb_write_io_threads之和，建议实例和线程数量比例为1:1。

innodb_read_io_threads / innodb_write_io_threads

在默认情况下，MySQL后台线程包括了主线程、IO线程、锁线程以及监控线程等，其中读写线程属于IO线程，主要负责数据库的读取和写入操作，这些线程分别读取和写入innodb_buffer_pool_instances创建的各个内存页面。MySQL支持配置多个读写线程，即通过innodb_read_io_threads和innodb_write_io_threads设置读写线程数量。

读写线程数量值默认为4，也就是总共有8个线程同时在后台运行。innodb_read_io_threads和innodb_write_io_threads设置的读写线程数量，与innodb_buffer_pool_instances的大小有关，两者的协同优化是提高系统性能的一个关键因素。

在一些内存以及CPU内核超大型的数据库服务器上，我们可以在保证足够大的IBP内存的前提下，通过以下公式，协同增加缓存实例数量以及读写线程。

( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffe_pool_instances

如果我们仅仅是将读写线程根据缓存实例数量对半来分，即读线程和写线程各为实例大小的一半，肯定是不合理的。例如我们的应用服务读取数据库的数据多于写入数据库的数据，那么增加写入线程反而没有优化效果。我们一般可以通过MySQL服务器保存的全局统计信息，来确定系统的读取和写入比率。

我们可以通过以下查询来确定读写比率：

SHOW GLOBAL STATUS LIKE 'Com_select';//读取数量

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');//写入数量

如果读大于写，我们应该考虑将读线程的数量设置得大一些，写线程数量小一些；否则，反之。

innodb_log_file_size

除了以上InnoDB缓存等因素之外，InnoDB的日志缓存大小、日志文件大小以及日志文件持久化到磁盘的策略都影响着InnnoDB的性能。 InnoDB中有一个redo log文件，InnoDB用它来存储服务器处理的每个写请求的重做活动。执行的每个写入操作都会在日志文件中获得重做条目，以便在发生崩溃时可以恢复更改。

当日志文件大小已经超过我们参数设置的日志文件大小时，InnoDB会自动切换到另外一个日志文件，由于重做日志是一个循环使用的环，在切换时，就需要将新的日志文件脏页的缓存数据刷新到磁盘中（触发检查点）。

理论上来说，innodb_log_file_size设置得越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘I/O。那是不是将这个日志文件设置得越大越好呢？如果日志文件设置得太大，恢复时间就会变长，这样不便于DBA管理。在大多数情况下，我们将日志文件大小设置为1GB就足够了。

innodb_log_buffer_size

这个参数决定了InnoDB重做日志缓冲池的大小，默认值为8MB。如果高并发中存在大量的事务，该值设置得太小，就会增加写入磁盘的I/O操作。我们可以通过增大该参数来减少写入磁盘操作，从而提高并发时的事务性能。

innodb_flush_log_at_trx_commit

这个参数可以控制重做日志从缓存写入文件刷新到磁盘中的策略，默认值为1。

• 当设置该参数为0时，InnoDB每秒种就会触发一次缓存日志写入到文件中并刷新到磁盘的操作，这有可能在数据库崩溃后，丢失1s的数据。
• 当设置该参数为 1 时，则表示每次事务的 redo log 都会直接持久化到磁盘中，这样可以保证 MySQL 异常重启之后数据不会丢失。
• 当设置该参数为 2 时，每次事务的 redo log 都会直接写入到文件中，再将文件刷新到磁盘。

在一些对数据安全性要求比较高的场景中，显然该值需要设置为1。

而在一些可以容忍数据库崩溃时丢失1s数据的场景中，我们可以将该值设置为0或2，这样可以明显地减少日志同步到磁盘的I/O操作。

其他调优参数

本章我们仅仅是了解了与内存优化相关的参数设置。除了这些参数设置，我们还有一些常用的提高MySQL并发的相关参数设置，总结如下：

参数	调优
max_connections	控制允许连接到MySQL数据库的最大连接数量，默认为151。我们查看状态变量connection_errors_max_connections的值大于零或遇到MySQL：ERROR1040:Toomanyconnections时，应该考虑增加连接数。
back_log	TCP连接请求排队等待栈，并发量比较大的情况下，可以适当调大该参数，增加短时间内处理连接请求量。
thread_cache_size	MySQL接收到客户端的连接时，需要生成线程用于处理连接。当连接断开时，线程并不会立刻销毁，而是对线程进行缓存，便于下一个连接使用，减少线程的创建和销毁。我们可以查看状态变量Threads_created是否过大，如果该状态变量值过大，说明MySQL一直在创建处理连接的线程，我们就可以适当调大 thread_cache_size