数据库参数设置与InnoDB存储引擎

admin 2023-10-13 624 阅读 0评论

数据库是用于存取数据的。然而，存取数据会涉及到磁盘I/O的读写操作，这使得I/O读写成为数据库系统的主要性能瓶颈。为了解决这个问题，MySQL数据库采用了许多内存管理技术来优化数据库操作，包括内存优化查询、排序以及写入操作。

然而，也许你会认为我们把内存设置得越大越好，数据刷新到磁盘越快越好，这样就可以解决所有问题。但实际上并非如此，设置内存过大也会带来新的问题。例如，在InnoDB中，数据和索引缓存设置得过大会导致SWAP页交换。此外，数据写入到磁盘也不是越快越好，我们期望的是在高并发时，数据能均匀地写入到磁盘中，从而避免I/O性能瓶颈。

SWAP页交换：SWAP分区在系统的物理内存不够用的时候，就会把物理内存中的一部分空间释放出来，以供当前运行的程序使用。被释放的空间可能来自一些很长时间没有什么操作的程序，这些被释放的空间的数据被临时保存到SWAP分区中，等到那些程序要运行时，再从SWAP分区中恢复保存的数据到内存中。

所以，这些参数的设置跟我们的应用服务特性以及服务器硬件有很大的关系。MySQL是一个高定制化的数据库，我们可以根据需求来调整参数，定制性能最优的数据库。

MySQL结构

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

第二层是Server层，主要实现SQL的一些基础功能，包括SQL解析、优化、执行和缓存等。其中，缓存功能与本文的主题密切相关。

第三层包括了各种存储引擎，主要负责数据的存取。这一层中的Buffer缓存也与本文的主题密切相关。

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

第三层的InnoDB存储引擎作为我们最常用到的存储引擎之一，充分熟悉它的的实现和运行原理，有助于我们更好地创建和维护数据库表，这里我将详细谈下它的实现原理以及架构组成。

InnoDB体系架构

InnoDB主要包括了内存池、后台线程以及存储文件。内存池又是由多个内存块组成的，主要包括缓存磁盘数据、redo log缓冲等；后台线程则包括了Master Thread、IO Thread以及Purge Thread等；由InnoDB存储引擎实现的表的存储结构文件一般包括表结构文件（.frm）、共享表空间文件（ibdata1）、独占表空间文件（ibd）以及日志文件（redo文件等）等。

1. 内存池

我们知道，如果客户端从数据库中读取数据是直接从磁盘读取的话，无疑会带来一定的性能瓶颈，缓冲池的作用就是提高整个数据库的读写性能。

客户端读取数据时，如果数据存在于缓冲池中，客户端就会直接读取缓冲池中的数据，否则再去磁盘中读取；对于数据库中的修改数据，首先是修改在缓冲池中的数据，然后再通过Master Thread线程刷新到磁盘上。

理论上来说，缓冲池的内存越大越好。缓冲池中不仅缓存索引页和数据页，还包括了undo页，插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

InnoDB允许多个缓冲池实例，从而减少数据库内部资源的竞争，增强数据库的并发处理能力。

InnoDB存储引擎会先将重做日志信息放入到缓冲区中，然后再刷新到重做日志文件中。

2. 后台线程

Master Thread 主要负责将缓冲池中的数据异步刷新到磁盘中，除此之外还包括插入缓存、undo页的回收等，IO Thread是负责读写IO的线程，而Purge Thread主要用于回收事务已经提交了的undo log，Pager Cleaner Thread是新引入的一个用于协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。

3. 存储文件

在MySQL中建立一张表都会生成一个.frm文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。

在InnoDB中，存储数据都是按表空间进行存放的，默认为共享表空间，存储的文件即为共享表空间文件（ibdata1）。若设置了参数innodb_file_per_table为1，则会将存储的数据、索引等信息单独存储在一个独占表空间，因此也会产生一个独占表空间文件（ibd）。如果你对共享表空间和独占表空间的理解还不够透彻，接下来我会详解。

而日志文件则主要是重做日志文件，主要记录事务产生的重做日志，保证事务的一致性。

InnoDB逻辑存储结构

InnoDB逻辑存储结构分为表空间（Tablespace）、段(Segment)、区(Extent)、页Page)以及行(row)。

1. 表空间（Tablespace）

InnoDB提供了两种表空间存储数据的方式，一种是共享表空间，一种是独占表空间。InnoDB 默认会将其所有的表数据存储在一个共享表空间中，即ibdata1。

我们可以通过设置innodb_file_per_table参数为1（1代表独占方式）开启独占表空间模式。开启之后，每个表都有自己独立的表空间物理文件，所有的数据以及索引都会存储在该文件中，这样方便备份以及恢复数据。

2. 段(Segment)

表空间是由各个段组成的，段一般分为数据段、索引段和回滚段等。我们知道，InnoDB默认是基于B +树实现的数据存储。

这里的索引段则是指的B +树的非叶子节点，而数据段则是B +树的叶子节点。而回滚段则指的是回滚数据，MVCC就是利用了回滚段实现了多版本查询数据。

3. 区(Extent) / 页（Page）

区是表空间的单元结构，每个区的大小为1MB。而页是组成区的最小单元，页也是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。

4. 行（Row）

InnoDB存储引擎是面向行的（row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200行，即7992行记录。

InnoDB事务之redo log工作原理

InnoDB是一个事务性的存储引擎，而InnoDB的事务实现是基于事务日志redo log和undo log实现的。redo log是重做日志，提供再写入操作，实现事务的持久性；undo log是回滚日志，提供回滚操作，保证事务的一致性。

redo log又包括了内存中的日志缓冲（redo log buffer）以及保存在磁盘的重做日志文件（redo log file），前者存储在内存中，容易丢失，后者持久化在磁盘中，不会丢失。

InnoDB的更新操作采用的是Write Ahead Log策略，即先写日志，再写入磁盘。当一条记录更新时，InnoDB会先把记录写入到redo log buffer中，并更新内存数据。我们可以通过参数innodb_flush_log_at_trx_commit自定义commit时，如何将redo log buffer中的日志刷新到redo log file中。

在这里，我们需要注意的是InnoDB的redo log的大小是固定的，分别有多个日志文件采用循环方式组成一个循环闭环，当写到结尾时，会回到开头循环写日志。我们可以通过参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件的大小。

Buffer Pool中更新的数据未刷新到磁盘中，该内存页我们称之为脏页。最终脏页的数据会刷新到磁盘中，将磁盘中的数据覆盖，这个过程与redo log不一定有关系。

只有当redo log日志满了的情况下，才会主动触发脏页刷新到磁盘，而脏页不仅只有redo log日志满了的情况才会刷新到磁盘，以下几种情况同样会触发脏页的刷新：

系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL认为空闲的时间，这种情况没有性能问题；
MySQL正常关闭之前，会把所有的脏页刷入到磁盘，这种情况也没有性能问题。

在生产环境中，如果我们开启了慢SQL监控，你会发现偶尔会出现一些用时稍长的SQL。这是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

LRU淘汰策略

刚刚我们了解了InnoDB的更新和插入操作的具体实现原理，接下来我们再来了解下读的实现和优化方式。

InnoDB存储引擎是基于集合索引实现的数据存储，也就是除了索引列以及主键是存储在B +树之外，其它列数据也存储在B + 树的叶子节点中。而这里的索引页和数据页都会缓存在缓冲池中，在查询数据时，只要在缓冲池中存在该数据，InnoDB就不用每次都去磁盘中读取页，从而提高数据库的查询性能。

虽然缓冲池是一个很大的内存区域，但由于存放了各种类型的数据，加上存储数据量之大，缓冲池无法将所有的数据都存储在其中。因此，缓冲池需要通过LRU算法将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

InnoDB对LRU做了一些优化，我们熟悉的LRU算法通常是将最近查询的数据放到LRU列表的首部，而InnoDB则是将数据放在一个midpoint位置，通常这个midpoint为列表长度的5/8。

这种策略主要是为了避免一些不常查询的操作突然将热点数据淘汰出去，而热点数据被再次查询时，需要再次从磁盘中获取，从而影响数据库的查询性能。

如果我们的热点数据比较多，我们可以通过调整midpoint值来增加热点数据的存储量，从而降低热点数据的淘汰率。

一条SQL语句是如何执行的？

接下来我们再来了解下，当数据接收到一个SQL语句时，是如何处理的。

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，它缓存的是SQL语句和对应的结果集。这里的缓存是以查询SQL的Hash值为key，返回结果集为value的键值对，判断一条SQL是否命中缓存，是通过匹配查询SQL的Hash值来实现的。

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

我们可以通过设置合适的 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来关闭查询缓存。

经过了Query Cache缓存之后，还会使用到存储引擎中的Buffer缓存。不同的存储引擎，使用的Buffer也是不一样的。这里我们主要讲解InnoDB存储引擎。

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就足够了。