提高 MySQL 存储引擎 InnoDB 性能

InnoDB 存储引擎的架构非常复杂，是专门为高并发性和复杂事务性活动和设计的。它有许多高级功能，应该在改进性能前优先考虑这些功能。我们主要关注那些可以被监控和改进的功能，包括索引、缓冲池、日志文件和表空间。

 

InnoDB 表使用聚集索引。即使未指定索引，InnoDB 也会为每行分配一个内部值，用于使用聚集索引。聚集索引是一种数据结构，它不仅存储索引，还存储数据本身。也就是说，一旦定位到某索引中的某个值，就可以直接检索数据而无需额外的磁盘寻道。当然，主键索引或者表的第一个唯一索引都采用聚集索引创建。

 

如果创建了二级索引，聚集索引的关键字（主关键字、唯一键或者行 ID）信息都会存在二级索引中。这样可以快速重新定位和回访聚集索引中的原始数据。这也意味着如果使用主关键字列扫描二级索引，则只需要用二级索引来检索数据。

 

缓冲池是用于管理事务和读写磁盘数据的缓存机制，如果配置得当，将会提高磁盘访问速率。缓冲池同时还是崩溃恢复的一个重要组成部分，因为缓冲池内的信息将会定期被写入磁盘（例如关机时）。缓冲池是一个内存组件，必须监控其有效性，保证配置的正确性。

 

InnoDB 也使用缓冲池来存储数据变更和事务。InnoDB 通过将数据变更保存到缓冲池中的数据页（块）中进行缓存。每次引用数据页都会放到缓冲池中，发生改变后就标记为“dirty”。然后，这个改变被写入到磁盘中以更新数据，并向日志中写入一个副本。这些日志文件的名字为 ib_logfile 或 ib_logfile1。可以在 MySQL 服务器的数据目录中看到这些文件。

 

InnoDB 存储引擎使用两种基于磁盘的机制存储数据：日志文件表空间。在关机或死机之前， InnoDB 还使用这些日志来重建（或重做）数据变更。在程序启动时，InnoDB 读取日志并自动将脏数据写入磁盘，从而在系统崩溃前可以恢复缓冲池中的数据变更。

 

表空间是一个组织工具，在 InnoDB 中作为独立于机器的文件使用，包括数据、索引及回滚机制（回滚事务）。默认情况下，所有表共享一个表空间。 也可以将表存储在它们自己的表空间中。这些表空间同时包含数据和表的索引。表空间可以自动扩展成多个文件，从而使你可以在表中存储更多的数据，该数据量多于操作系统可以处理的数据量。你还可以将表空间划分为多个文件，然后存储在不同的磁盘上。

使用 innodb_file_per_table 为每个表创建单独的表空间。在设置该选项之前创建的表将存储在共享表空间中。使用此命令只影响新创建的表。

 

监控日志文件

 

InnoDB 日志文件在你的数据和操作系统之间缓冲数据，这些文件正常运行可以确保系统性能良好。还可以通过查看以下系统状态变量，直接监控这些日志文件。

 

mysql> SHOW STATUS LIKE  'innodb%log%';

+------------------------------+-------+

| Variable_name                | Value |

+------------------------------+-------+

| Innodb_log_waits             | 0     |

| Innodb_log_write_requests    | 0     |

| Innodb_log_writes            | 1     |

| Innodb_os_log_fsyncs         | 3     |

| Innodb_os_log_pending_fsyncs | 0     |

| Innodb_os_log_pending_writes | 0     |

| Innodb_os_log_written        | 512   |

| Innodb_available_undo_logs   | 128   |

+------------------------------+-------+

 

Innodb_log_waits 

当日志文件太小（即没有足够空间）时，操作必须等待日志刷新的等待时间计数器。如果该值开始增加并长期大于零（除批量操作以外），可以增加日志文件的大小。

 

Innodb_log_write_requests

日志写入请求的数量。

 

Innodb_log_writes

数据被写入日志的次数。

 

Innodb_os_log_fsyncs

操作系统文件同步的数量（即 fsync() 方法调用）。

 

Innodb_os_log_pending_fsyncs

阻塞（pend）的文件同步请求的数量。如果该值开始增加并长期大于零，可能需要检查磁盘访问问题。

 

Innodb_os_log_pending_writes

阻塞（pend）的日志写请求的次数。如果该值开始增加并长期大于零，可能需要检查磁盘访问问题。

 

Innodb_os_log_written

写到日志中的字节总量。

 

所有这些选项显示的都是数字信息，可以在 MySQL 管理器中建立描述这些信息的自定义图标。

 

监控缓冲池

 

缓冲池是 InnoDB 缓存频繁访问数据的地方，对缓冲池内数据的任何更新也会被缓存。缓冲池还存储当前事务的相关信息。因此，缓冲池是关乎性能的关键机制。

 

使用 SHOW ENGINE INNODB STATUS 命令查看缓冲池的相关信息，如例 9-5 所示。为了方便查看，我们在这里重新讲解缓冲池和内存的知识。

 

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 94550
Buffer pool size   8192
Free buffers       7766
Database pages     426
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 426, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 426, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

 

下面给出了该报告中需要重点注意的信息。我们将在后面更详细地讨论具体的状态变量。

 

Free buffers

空的且可用于缓冲数据的缓冲段个数。

 

Modified db pages

发生变化（脏）的页数

 

Pending reads

等待中的读请求个数，该值应该保持在低水平

 

Pending writes

等待中的读请求个数，该值应该保持在低水平

 

Hit rate

成功访问缓冲区的请求个数与总请求个数之间的比例，这个比值最好接近 1:1 。

 

 

还可以查看更对状态变量的信息。下面显示了 InnoDB 缓冲池的状态变量：

 

mysql> SHOW STATUS LIKE  'innodb%buf%';

+---------------------------------------+-------------+

| Variable_name                         | Value       |

+---------------------------------------+-------------+

| Innodb_buffer_pool_dump_status        | not started |

| Innodb_buffer_pool_load_status        | not started |

| Innodb_buffer_pool_pages_data         | 426         |

| Innodb_buffer_pool_bytes_data         | 6979584     |

| Innodb_buffer_pool_pages_dirty        | 0           |

| Innodb_buffer_pool_bytes_dirty        | 0           |

| Innodb_buffer_pool_pages_flushed      | 1           |

| Innodb_buffer_pool_pages_free         | 7766        |

| Innodb_buffer_pool_pages_misc         | 0           |

| Innodb_buffer_pool_pages_total        | 8192        |

| 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      | 4168        |

| Innodb_buffer_pool_reads              | 427         |

| Innodb_buffer_pool_wait_free          | 0           |

| Innodb_buffer_pool_write_requests     | 1           |

 

+---------------------------------------+-------------+

 

缓冲池有很多状态变量用于显示关于缓冲池性能的主要统计信息，如缓冲池的页状态、缓冲池的读写信息，以及缓冲池中读写等待的频率。下面详细地介绍了各个状态变量：

 

Innodb_buffer_pool_pages_data

含有数据的页数，包括不变和改变的页（即脏页）

 

Innodb_buffer_pool_pages_dirty

改变的页（即脏页）的数目

 

 Innodb_buffer_pool_pages_flushed

缓冲池页面被刷新的次数

 

Innodb_buffer_pool_pages_free

空页面的数目

 

Innodb_buffer_pool_pages_misc 

InnoDB 引擎用于管理工作的页数，其计算方式如下：

X = Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data

 

Innodb_buffer_pool_pages_total 

缓冲池中的总页数

 

Innodb_buffer_pool_read_ahead_rnd

InnoDB 扫描大块数据时发生随机读头的数量

 

Innodb_buffer_pool_read_ahead_seq

顺序全表扫描时发生的顺序读取表头的数量

 

Innodb_buffer_pool_read_requests 

逻辑读请求的次数

 

Innodb_buffer_pool_reads

直接从磁盘中逻辑读取（而不是从缓冲池读）的次数

 

Innodb_buffer_pool_wait_free

如果缓冲池繁忙且没有空页，InnoDB 可能需要等待页面刷新。该值表示等待的次数。如果这个值增加且始终大于 0 ，可能是缓冲池过小或磁盘访问出问题。

 

Innodb_buffer_pool_write_requests

写入 InnoDB 缓冲池的次数。

 

所有这些选项显示的都是数字信息，可以在 MySQL 管理器中建立描述这些信息的自定义图表。

 

监控表空间

 

如果 InnoDB 可以在运行缓慢时扩展表空间，那么 InnoDB 的表空间基本可以自给自足。配置

--innodb_data_file_path=indata1:10M:autoextend

详见 MySQL 在线参考手册的“InnoDB 配置”章节。

使用  SHOW ENGINE INNODB STATUS 命令查看当前的表空间配置信息。还可以通过打开 InnoDB 表空间监控器查看表空间的详细信息。

 

使用 information_schema 表

如果你安装的 MySQL 版本含有 InnoDB 存储引擎插件。还可以在 information_schema 数据库中访问七个特殊表。

 

从技术上来讲，这些表并不是真正意义上的表。其数据不是存储在磁盘上的，而是在查询表时生成的。这些表提供了另一种监控 InnoDB 的方法，并为管理员提供性能信息。这些表用于监控压缩、事务和锁，我们将依次简单介绍。

 

INNODB_CMP

显示压缩表的详细信息和统计信息。

 

INNODB_CMP_RESET

与INNODB_CMP显示相同的信息，让是其有个特性就是：在查询表时将重置统计信息，这使你可以定期（如每小时、每天等）跟踪统计信息。

 

INNODB_CMPMEM

显示在缓冲池中使用压缩的详细信息和统计信息。

 

INNODB_CMPMEM_RESET

与INNODB_CMPMEM显示相同的信息，但是其有个特性就是：在查询表时将重置统计信息，这使你可以定期（如每小时、每天等）跟踪统计信息。

 

INNODB_TRX

显示所有事务的详细信息和统计信息，包括事务状态和当前正在运行的查询信息。

 

INNODB_LOCKS

显示事务请求的锁的详细信息和统计信息。描述每个锁的状态、模式、类型等信息。

 

INNODB_LOCK_WAITS

显示备阻塞的事务请求的锁的详细信息和统计信息，描述每个锁的状态、模式、类型和阻塞事务。

 

可以使用压缩表监控表的压缩信息，其中包括以下细节信息：页大小、使用哪些页、压缩时间和解压时间等。如果使用了压缩并希望压缩带来的开销不影响数据库服务器性能，那么这些信息将是重要监控对象。

 

可以使用事务和锁定表来监控事务。这是一个非常有用的工具， 可以保证事务数据库顺利运行。更重要的是，通过它可以精确地确定各个事务的状态，以及哪些事务受阻，哪些处于锁定状态。这些信息对于诊断复杂的事务问题（如死锁或低性能）也是很重要的。

 

其他需要考虑的参数

还有很多其他参数用于监控和优化 InnoDB 存储引擎。前面我们只讨论了其中的一部分，主要介绍有关监控子系统和提高性能的部分。当然，还有一些其他参数可能需要考虑。

 

某些情况下，可以通过调节 innodb_thread_concurrency 选项提高线程性能。该参数默认值是 0 ，一般情况下，该值是足够的。但如果在多处理器及多个独立磁盘的服务器上运行 MySQL（并频繁使用 InnoDB），那么将此值设置为处理器个数加上独立磁盘数的和，系统性能会提高，确保 InnoDB 使用足够的线程最大化并发操作。如果该值大于系统所能支持的值，则起不到任何作用或作用不大——如果没有任何可用的线程，这样的设置将无法达到最大值。

 

如果系统频繁或甚至定期被关闭（例如，在 Linux 系统启动时运行 MySQL）,可能关闭 InnoDB 需要花费很长时间。幸运的是，可以通过设置 innodb_fast_shutdown 选项来快速关闭 InnoDB。这不会影响数据的完整性，也不会导致内存（缓冲）管理损失。快速关闭 InnoDB 只是简单地跳过清除内部缓存和合并插入缓冲这些潜在的高成本操作，仍然执行一个可控的关闭过程，并在磁盘上储存缓冲池。

 

MySQL 的早期发行版本有并发控制和锁问题。对于这些早期版本，可以通过设置 innodb_wait_timeout 变量来控制 InnoDB 如何处理死锁。该变量有全局和会话范围，控制 InnoDB 允许事务在终止之前等待行锁的等待时间，默认值是 50 秒。如果有很多锁等待超时，可以增加该变量值，缓和一些并发问题。

 

如果你正在导入大量数据，确保将传入的数据文件按主键的顺序排序，这样可以改善装载时间。此外。 将 AUTOCOMMIT 设为 0 关闭自动提交，保证整个装载只提交一次。还可以通过关闭外键和唯一约束来改善批量装载。