为什么别人能用好 MySQL？万字详解其复杂原理( 二 ) _小知识

Adaptive Hash Index自适应哈希索引(AHI)查询非常快，一般时间复杂度为 O(1)，相比 B+ 树通常要查询 3~4次，效率会有很大提升。innodb 通过观察索引页上的查询次数，如果发现建立哈希索引可以提升查询效率，则会自动建立哈希索引，称之为自适应哈希索引，不需要人工干预，可以通过 innodb_adaptive_hash_index 开启，MySQL5.7 默认开启。
考虑到不同系统的差异，有些系统开启自适应哈希索引可能会导致性能提升不明显，而且为监控索引页查询次数增加了多余的性能损耗，MySQL5.7 更改了 AHI 实现机制，每个 AHI 都分配了专门分区，通过 innodb_adaptive_hash_index_parts配置分区数目，默认是8个，如前一节命令列出所示。
Log BufferLog Buffer是重做日志在内存中的缓冲区，大小由 innodb_log_buffer_size 定义，默认是 16M 。一个大的 Log Buffer可以让大事务在提交前不必将日志中途刷到磁盘，可以提高效率。如果你的系统有很多修改很多行记录的大事务，可以增大该值。
配置项 innodb_flush_log_at_trx_commit 用于控制 Log Buffer 如何写入和刷到磁盘。注意，除了 MySQL 的缓冲区，操作系统本身也有内核缓冲区。

默认为1，表示每次事务提交都会将 Log Buffer 写入操作系统缓存，并调用配置的 “flush” 方法将数据写到磁盘。设置为 1 因为频繁刷磁盘效率会偏低，但是安全性高，最多丢失 1个事务数据。而设置为 0 和 2 则可能丢失 1秒以上的事务数据。
为 0 则表示每秒才将 Log Buffer 写入内核缓冲区并调用 “flush” 方法将数据写到磁盘。
为 2 则是每次事务提交都将 Log Buffer写入内核缓冲区，但是每秒才调用 “flush” 将内核缓冲区的数据刷到磁盘。

配置不同的值效果如下图所示：

文章插图

innodb_flush_log_at_timeout 可以配置刷新日志缓存到磁盘的频率，默认是1秒。注意刷磁盘的频率并不保证就正好是这个时间，可能因为MySQL的一些操作导致推迟或提前。
而这个 “flush” 方法并不是C标准库的 fflush 方法(fflush是将C标准库的缓冲写到内核缓冲区，并不保证刷到磁盘)，它通过 innodb_flush_method 配置的，默认是 fsync，即日志和数据都通过 fsync 系统调用刷到磁盘。
可以发现，InnoDB 基本每秒都会将 Log buffer落盘。而InnoDB中使用的 redo log 和 undo log，它们是分开存储的。
redo log在内存中有log buffer，在磁盘对应ib_logfile文件。而undo log是记录在表空间ibd文件中的，InnoDB为undo log会生成undo页，对undo log本身的操作（比如向undo log插入一条记录），也会记录redo log，因此undo log并不需要马上落盘。而 redo log 则通常会分配一块连续的磁盘空间，然后先写到log buffer，并每秒刷一次磁盘。
redo log 必须在数据落盘前先落盘(Write Ahead Log)，从而保证数据持久性和一致性。而数据本身的修改可以先驻留在内存缓冲池中，再根据特定的策略定期刷到磁盘。
3、InnoDB 磁盘上的结构

表空间：分为系统表空间(MySQL 目录的 ibdata1 文件)，临时表空间，常规表空间，Undo 表空间以及 file-per-table 表空间(MySQL5.7默认打开file_per_table 配置）。系统表空间又包括了InnoDB数据字典，双写缓冲区(Doublewrite Buffer)，修改缓存(Change Buffer），Undo日志等。
Redo日志：存储的就是 Log Buffer 刷到磁盘的数据。

为了后面测试方便，我们先建立一个测试数据库 test，然后建立一个测试表 t 。

mysql> create database test;mysql> use test;mysql> create table t (id int auto_increment primary key, ch varchar(5000));mysql> insert into t (ch) values('abc');mysql> insert into t (ch) values('defgh');

建立完成后，可以在 MySQL 目录中看到 test 数据库目录，然后里面有 db.opt，t.frm 和 t.ibd 3个文件。其中 db.opt 保存了数据库test的默认字符集 utf8mb4 和校验方法 utf8mb4_general_ci，t.frm 是表的数据字典信息(InnoDB数据字典信息主要是存储在系统表空间ibdata1文件中，由于历史原因才在 t.frm 多保留了一份)，t.ibd是表的数据和索引。
3.1 InnoDB 表结构InnoDB 与 MyISAM 不同，它在系统表空间存储数据字典信息，因此它的表不能像 MyISAM 那样直接拷贝数据表文件移动。MySQL5.7 采用的文件格式是 Barracuda，它支持 COMPACT 和 DYNAMIC 这两种新的行记录格式。创建表时可以通过 ROW_FORMAT 指定行记录格式，默认是 DYNAMIC 。可以通过命令 SHOW TABLE STATUS 查看表信息，此外，也可使用 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t' 查看。