MySQL存储引擎

存储引擎是MySQL组件，用于处理不同类型的SQL操作。

使用 SHOW ENGINES 语句查看服务器支持哪些存储引擎，Support列中表示是否可以使用，DEFAULT表示默认值。（下图版本为MySQL 5.7.26，Windows 10）

InnoDB 是默认的且用途最广的存储引擎，Oracle官方建议将其用于表（特殊用例除外）。（默认情况下 ，MySQL 5.7 or MySQL 8.0 中使用 CREATE TABLE 语句创建InnoDB表。）

InnoDB

事务型数据库的首选引擎，支持事务安全（ACID），具有提交（COMMIT），回滚（ROLLBACK）和奔溃恢复（crash-recovery）功能 InnoDB and the ACID Model

行级锁定和Oracle风格的一致读取可提高多用户的并发性和性能 InnoDB Locking and Transaction Model

InnoDB将数据存放在磁盘上，以基于主键优化查询，每个InnoDB表都有一个称为聚集索引（The Clustered Index）的主键索引，该索引组织数据以最小化主键查找I/O Clustered and Secondary Indexes

为了保持数据的完整性，InnoDB支持 FOREIGN KEY 约束，使用外键检查插入，更新和删除，以确保它们不会导致相关表之间的不一致。 FOREIGN KEY Constraints

ACID

  • 原子性（Atomicity）

    一个事务要么全部提交成功，要么全部回滚失败，不能只执行其中的一部分操作

  • 一致性（Consistency）

    事务的执行不能破坏数据库的完整性和一致性，在一个事务在执行前后，数据库都必须处于一致性状态，以防止数据崩溃

  • 隔离性（Isolation）

    在并发的环境中，并发事务是相互隔离的，并发执行的事务直接不能互相干扰

  • 持久性（Durability）

    一旦事务提交，则对应数据库中的数据状态的变更就会永久的保存到数据库中

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB首选

MyISAM

所有数据值首先存储在低字节中（The Low Byte First），这使得MyISAM独立于操作系统，可以轻松地将其从Windows服务器移植到Linux服务器

每个MyISAM表都以三个文件存储在磁盘上。这些文件具有以表名开头的名称，并具有用于指示文件类型的扩展名，.frm 文件存储表格式，.MYD（MYData）文件存储数据，.MYI（MYIndex）存储索引

支持并发插入，即适合在插入密集型表中使用，例如管理邮件或Web服务器日志数据

MyISAM存储引擎在筛选大量数据时非常迅速，适合插入密集型表

Memory

将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中进行快速访问，当mysqld守护进程崩溃时，所有的Memory数据都会丢失。

要求存储在Memory数据表里的数据使用的是长度不变的格式，不支持可变长度数据类型（包括BLOB和TEXT），VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

一般在目标数据较小，而且访问非常频繁适合使用，造成内存的使用可以通过参数max_help_table_size控制Memory表的大小，如果数据是临时的，而且要求必须被立即使用，就可以存放在内存表之中，若数据丢失，不会对应用服务产生实质的负面影响，Memory同时支持散列索引和B数索引。

CSV

实际上是带有逗号分隔符的文本文件，CVS表允许以CSV格式导入或转储数据，以便与读取和写入相同格式的脚本和应用程序交换数据。

CSV 存储引擎因为自身文件格式的原因，所有列必须强制指定 NOT NULL 。

CSV 存储引擎也会包含一个存储表结构的 .frm 文件，还会创建一个 .csv 存储数据的文件，还会创建一个同名的元信息文件，该文件的扩展名为 .CSM ，用来保存表的状态及表中保存的数据量。每个数据行占用一个文本行。

ARCHIVE

归档，仅支持最基本的插入和查询两种功能，MySQL 5.5以后的版本中开始支持索引，Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

BLACKHOLE

黑洞存储引擎，所有插入的数据并不会保存，BLACKHOLE 引擎表永远保持为空，写入的任何数据都会消失。

InnoDB底层原理

架构图：

Buffer Pool

缓冲池

InnoDB为了做数据的持久化，会将数据存储到磁盘上。但是面对大量的请求时，CPU的处理速度和磁盘的IO速度之间差距太大，为了提高整体的效率， InnoDB引入了缓冲池。

缓冲池是内存中的一个区域，在InnoDB访问表和索引数据的时候会在其中进行高速缓存，缓冲池允许直接从内存访问经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达80％的物理内存分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被划分为多个页面，这些页面可以潜在地容纳多行，为了提高缓存管理的效率，使用最近最少使用（LRU）算法的变体，将很少使用的数据从缓存中老化掉。

如何利用缓冲池将经常访问的数据保留在内存中是MySQL调优的重要方面。

Buffer Pool LRU Algorithm

缓冲池LRU算法

缓冲池使用LRU算法的变体作为列表进行管理。当需要空间以将新页面添加到缓冲池时，将驱逐最近使用最少的页面，并将新页面添加到列表的中间。此中点插入策略将列表视为两个子列表：

  • 最前面是最近访问过的新页面（“年轻”） 的子列表

  • 在末尾，是最近访问过的旧页面的子列表

官方结构图如下：

  • 3/8的缓存池专用于旧的子列表
  • 列表中点是新子列表的尾部与旧子列表的头部相交的界面
  • 当InnoDB将页面读入缓冲池时，首先插入中点
  • 访问旧子列表中的页面会使得其变为”年轻“，然后移至新子列表的开头
  • 随着数据库的运行，通过移至列表的末尾，缓冲池中未访问的页面将“老化”，新的和旧的子列表中的页面都会随着其他页面的更新而老化，随着在中点插入页面，旧子列表中的页面也会老化。最终，未使用的页面到达旧子列表的尾部并被逐出。

Change Buffer

插入缓冲

插入缓冲针对的操作是更新或者插入，我们考虑最坏的情况，那就是需要更新的数据都不在缓冲池中。那么此时等数据达到某个阈值（例如50条）才批量的写入磁盘

innodb_change_buffering 变量控制InnoDB 执行更改缓冲的程度，默认值为all。

  • all

    默认值：缓冲区插入，删除标记操作和清除。

  • none

    不要缓冲任何操作。

  • inserts

    缓冲区插入操作。

  • deletes

    缓冲区删除标记操作。

  • changes

    缓冲插入和删除标记操作。

  • purges

    缓冲在后台发生的物理删除操作。

innodb_change_buffer_max_size 变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。默认情况下， innodb_change_buffer_max_size设置为25。最大设置为50。

Log Buffer

日志缓冲

日志缓冲区是存储区域，用于保存要写入磁盘上的日志文件的数据。日志缓冲区大小由innodb_log_buffer_size变量定义 。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I/O。

Adaptive Hash Index

自适应哈希索引

自适应索引就跟JVM在运行过程中，会动态的把某些热点代码编译成Machine Code一样，InnoDB会监控对所有索引的查询，对热点访问的页建立哈希索引，以此来提升访问速度。

Doublewrite Buffer

双写缓冲区

插入缓冲提高了MySQL的性能，而两次写则在此基础上提高了数据的可靠性。当数据还在缓冲池中的时候，当机器宕机了，发生了写失效，有Redo Log来进行恢复。但是如果是在从缓冲池中将数据刷回磁盘的时候宕机了呢？

这种情况叫做部分写失效，此时重做日志就无法解决问题。

在刷脏页时，并不是直接刷入磁盘，而是copy到内存中的Doublewrite Buffer中，然后再拷贝至磁盘共享表空间（你可以就理解为磁盘）中，每次写入1M，等copy完成后，再将Doublewrite Buffer中的页写入磁盘文件。

有了两次写机制，即使在刷脏页时宕机了，在实例恢复的时候也可以从共享表空间中找到Doublewrite Buffer的页副本，直接将其覆盖原来的数据页即可。

尽管数据被写入两次，但双写缓冲区不需要两倍的I / O开销或两倍的I / O操作。只需一次fsync()调用操作系统即可将数据按较大的顺序块写入doublewrite缓冲区（除非 innodb_flush_method设置为 O_DIRECT_NO_FSYNC）。

在MySQL 8.0.20之前，doublewrite缓冲区存储区位于InnoDB系统表空间中。从MySQL 8.0.20开始，doublewrite缓冲区存储区位于doublewrite文件中。

Redo Log

重做日志

用于记录事务操作的变化，且记录的是修改之后的值。

不管事务是否提交都会记录下来。例如在更新数据时，会先将更新的记录写到Redo Log中，再更新缓存中页中的数据。然后按照设置的更新策略，将内存中的数据刷回磁盘。

Undo Log

撤消日志

记录事务开始之前的一个版本，可用于事务失败之后发生的回滚。

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引分为：

  1. 主键索引

    primary key() 要求关键字不能重复，也不能为null,同时增加主键约束

    主键索引定义时，不能命名

  2. 唯一索引

    unique index() 要求关键字不能重复，同时增加唯一约束

  3. 普通索引

    index() 对关键字没有要求

  4. 全文索引

    fulltext key() 关键字的来源不是所有字段的数据，而是字段中提取的特别关键字

先了解一下索引的数据结构 B-Tree（MySQL主要使用 B-tree 平衡树）

B树

B树也称B-树,它是一颗多路平衡查找树。大致结构如下

B树的阶为节点的最多的子节点数

B数的搜索方式从根节点开始，对节点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子节点，叶子节点和非叶子节点都存放数据，搜索有可能在非叶子节点结束。

B+树

B+ 树是 B 树的变体，也是一种多路搜索树。

B+树只有到达叶子节点才命中，即所有关键字都出现在叶子节点链表中

  • 数据只能在叶子节点，也叫 稠密索引，且链表中的关键字（数据）恰好是有序的。

非叶子节点相当于是叶子节点的索引，也叫 稀疏索引，叶子节点相当于是存储（关键字）数据的数据层

B+数对于B数的优势

  • 单一节点存储的元素多，使得查询的IO次数更少，适合做MySQL的底层数据结构
  • 所有查询都要查到叶子节点，查询性能稳定
  • 所有叶子节点形成一个有序的链表，便于查找

哈希索引

只有精确匹配索引所有列的查询才有效。每行数据存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。因为索引自身只存储对应的哈希值，所以索引的结构十分紧凑，哈希索引查找的速度非常快。但是

  • 不按照索引顺序存储，无法用于排序
  • 不支持部分索引查找匹配
  • 不支持范围查找

聚集索引

由B+树实现，一个表只能有一个聚集索引，InnoDB表中聚集索引的索引列就是主键，所以聚集索引也叫主键索引。如下表：

create table Student(
    id int(11) primary key auto_increment,
    last_name varchar(50) not null, 
    first_name varchar(50) not null, 
    birthday date not null
);

聚集索引的结构如下：

聚簇索引：索引的叶节点指向数据

非聚簇索引：索引的叶节点指向数据的引用

myisam使用非聚簇索引，innodb使用聚簇索引

二级索引

对于InnoDB表，在非主键列的其他列上建的索引就是二级索引（因为聚集索引只有一个）。

在MySQL中主键索引的叶子节点存的是整行数据，而二级索引叶子节点内容是主键的值。

详见：MySQL · 引擎特性 · 二级索引分析

索引优化

  • 尽量保证全值匹配，即索引字段和select字段相同且顺序一致

  • 最佳左前缀法则：如果索引多列，则查询要从索引的最左列开始，且中间不跳过索引中的列

  • 不在索引列上做任何操作(计算、函数、类型转换、不要出现隐式转)，会导致索引失效而全表扫描

    假设目标行 name = 'july'
select * from info where name='july';           ## 走索引 
select * from info where left(name,4)='july';   ## 不走索引

  • 一旦出现非等值字段条件判断，则该字段后的索引列皆失效

    select * from info where a=10 and b=100 and c=1000;   ## 全索引  a_b_c  
select * from info where a=10 and b>100 and c=1000;   ## 部分索引 a_b  
## 非等值条件包括：in < > != like 等  
## 注意：当like 'aaa%' 通配符在右时，仍然能够走全索引  
select * from info where a=10 and b like '100%' and c=1000;   ## 全索引  a_b_c  
select * from info where a=10 and b like '%100' and c=1000;   ## 部分索引 a

  • 尽量使用覆盖索引，即查询列为索引列的子集，减少select * 的使用

  • MySQL在使用不等于(!=或者<>)时无法使用索引，会导致全表扫描

    select * from info where a=100;     ## 走索引  
select * from info where a!=100;    ## 不走索引，全表扫描

  • 查询条件为 is NULL 和 is not NULL情况时也无法使用索引

    select * from info where a is null;      ## 不走索引  
select * from info where a is not null;  ## 不走索引

  • like以通配符开头('%abc...')时索引也会失效，变为全表扫描；但通配符结尾依然会走索引，该字段后的索引依然失效

    select name, age from info where name like "%aaa";  ## 索引失效  
select name, age from info where name like "aaa%";  ## 索引有效  
## 当业务要求必须使用左通配符时，可使用覆盖索引的方法来避免索引失效  
## 在上面例子中即建立联合索引 name_age

  • 字符串不加单引号会导致索引失效 原因：隐式转换

    ## id为varchar类型  
select * from info where id='2000';  
select * from info where id=2000;  ## 会有隐式类型转换

  • 尽量少用or，用它来连接查询条件可能会导致索引失效

  • group by基本上都需要进行排序，当group by的字段顺序和索引顺序不一致的时候，就会导致临时表的产生，即同时出现 Using temporary 和 Using filesort，因此一定要极力避免

    ## 索引为 A_B_C  
select * from info where A=10 group by C, B;  ## 走索引A，产生临时表

小结

  • 对于单值索引，尽量选择针对当前查询过滤性更好的索引字段
  • 在选择联合索引时，当前查询中过滤性最好的字段在索引字段顺序中位置越靠前越好
  • 在选择联合索引时，尽可能选择可以包含当前查询的where子句中更多字段的索引，即如果可能的话，尽量达到索引覆盖，这样不仅能够避免索引失效，也能够避免回表等影响查询性能等操作
  • 尽可能通过分析统计信息和调整查询语句的写法来达到适应选择的索引

参考

  1. The InnoDB Storage Engine
  2. Alternative Storage Engines
  3. 数据库存储引擎
  4. 简单了解InnoDB底层原理
  5. B 树、B+ 树、B*树
  6. 为什么mysql索引要使用B+树，而不是B树，红黑树
  7. MySQL索引的原理，B+树、聚集索引和二级索引的结构分析
  8. MySQL优化之索引优化