MySQL存储引擎InnoDB、索引和索引下推

Mysql 5.5 及以前版本默认引擎是Myisam，Mysql5.5以后默认引擎是InnoDB。

InnoDB在性能、事务支持和容错能力等方面具有较好的特性，适合大多数应用场景。表数据文件本身按B+Tree组织的一个索引结构文件，由2个文件组成：表结构是*.frm 和索引+数据*.ibd。以下是InnoDB的主要优点和特性

1.1、支持事务（ACID）

InnoDB 支持 ACID（原子性、一致性、隔离性、持久性）事务，确保数据在并发环境中的一致性和可靠性。

原子性 (Atomicity)：通过回滚日志（Undo Log）来确保事务的原子性，当事务回滚时，所有对数据的修改都可以被撤销。

一致性 (Consistency)： 确保事务从一个一致的状态转移到另一个一致的状态。

隔离性 (Isolation)：支持多种事务隔离级别(读未提交、读已提交、可重复读和可串行化)，默认隔离级别是可重复读，使用多版本并发控制（MVCC）和间隙锁避免幻读。

持久性 (Durability)：使用重做日志（Redo Log）确保数据的持久性，在系统崩溃后，重做日志可以恢复未提交的数据。

1.2、行级锁（Row-Level Locking）

InnoDB 采用行级锁而非表级锁，这提高了并发性能，允许多个事务同时对不同的行进行读写操作，减少了锁竞争。

1.3、自动崩溃恢复

InnoDB 使用日志（redo log 和 undo log）来支持崩溃恢复。在系统故障或崩溃后，可以通过日志文件恢复未提交的数据和事务，确保数据的持久性。

1.4、支持外键

InnoDB 支持外键约束，建立表与表之间的连接，实现数据之间的关联和参照完整性，可以保证数据的完整性。

1.5、聚簇索引（Clustered Index）

InnoDB 使用聚簇索引（主键索引），数据按主键顺序物理存储，每个表只能有一个聚簇索引，通过主键查找数据非常高效。

1.6、MVCC（多版本并发控制）

InnoDB 通过多版本并发控制（MVCC）实现高效的并发读写操作。MVCC主要通过Undo多版本链和Read View机制，来实现一行数据在并发场景下，不需要加锁保证读、写的数据隔离性。

Undo多版本链：MVCC通过为每行数据项维护多个版本来实现并发控制。当一个事务对数据进行修改时，它不会直接覆盖现有的数据，而是创建一个新的版本。这些不同数据版本形成了单向版本链。

Read View机制：在事务开始时，ReadView 并不会立即创建，而是等到第一次需要读取数据的时候。RR 隔离级别下，ReadView 只会在事务第一次读取数据时创建，并在整个事务中保持不变，保证事务的快照一致性。RC 隔离级别下，每次读取数据时都会创建新的 ReadView，保证事务读取到的是其他事务提交的最新数据。

MVCC通过从undolog多版本链的头部开始遍历，根据数据版本的事务ID，结合readview以及可见性的规则，判断哪些数据版本对当前事务是否可见。

InnoDB采用页、区、段和表空间的层次结构管理数据。

页是所有数据的基本存储单元，每个页面(16KB)可以包含多行数据。

区是由 64 个连续的页构成，大小为 1MB，InnoDB 通过区分配空间，从而有效管理表的增长。

段是由多个区组成的逻辑空间，数据表的索引段和数据段都是由多个区组成。

表空间是数据和索引的物理存储位置，支持单独和共享两种方式，表的物理文件就是表空间文件。

InnoDB的索引可分为聚簇索引（Clustered Index）和二级索引（Secondary Index）。聚簇索引又称为主键索引，二级索引是非聚簇索引。

3.1、聚簇索引（Clustered Index）

聚簇索引(主键索引)的数据按主键顺序物理存储，每个表只能有一个聚簇索引。如果没有主键可分两种情况，有唯一非空索引，以该索引作为聚簇索引，无唯一索引，生成一个6字节的隐藏列row_id作为聚簇索引，它会自动递增。

聚簇索引的值必须是唯一的，不能重复，且不能为 NULL。查询高效，因为数据按照主键顺序存储，尤其是在范围查询时，例如 where id between 1 and 50。

3.2、二级索引（Secondary Index）

二级索引是非聚簇索引，存储索引列值和主键值的对应关系，不直接存储数据，一张表可以创建多个二级索引。

使用二级索引查询时，通常先通过二级索引找到主键值，再根据主键值回表找到对应的数据行，也就是回表。除非查询是覆盖索引，即查询的所有字段都在二级索引中。

你是否会有疑问为什么 InnoDB 的二级索引存储主键值？

主要有两个原因：

(1) 主键的稳定性：主键是唯一且固定的，数据行的物理存储地址可能会因数据页的拆分或合并发生变化。通过存储主键值，InnoDB 只需在数据移动时更新聚簇索引，不需更新所有的二级索引。

(2) 聚簇索引的设计：由于表数据是按照主键顺序存储，使用主键值可以快速定位到数据所在的页和位置，通过主键回表效率较高。

索引下推是MySQL 5.6 引入的一种查询优化技术，能够减少回表操作次数，提升查询效率。适用于二级索引的查询优化，不适用于主键索引，MySQL 默认开启索引下推。

4.1、原理与作用

原理是将 WHERE 子句中的一部分过滤条件下推到索引扫描阶段，在索引扫描时过滤掉不符合条件的记录，再回表读取完整的数据行，减少回表次数。

作用是减少不必要的回表操作，优化二级索引的查询效率，提高查询性能。

4.2、适用场景

索引下推适用场景：复合索引、范围查询，范围查询就是涉及范围查询的场景(如 >、<、BETWEEN)，单列索引也不适合。

索引下推只适用于二级索引，因为主键索引和二级索引的存储结构不同。

主键索引是聚簇索引，主键索引的叶子节点直接存储完整的行数据，所有过滤条件都是在读取数据时一起应用，不需要回表操作，索引下推没有优势。

二级索引是非聚簇索引，存储索引列和主键值的对应关系，查询时需要先通过索引定位到主键值，再根据主键值去回表获取完整的数据，索引下推提前应用部分过滤条件，减少不必要的回表操作。

(1) 全值匹配。

(2) 最左前缀法则，如果索引了多列，要遵守最左前缀法则，查询从索引的最左前列开始并且不跳过索引中的列。

(3) 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，否则会导致索引失效而转向全表扫描。

(4) 存储引擎不能使用索引中范围条件右边的列。

(5) 尽量使用覆盖索引，减少 select * 语句。

(6) like以通配符开头（'%abc...'）索引失效会变成全表扫描操作。

(7) 字符串不加单引号索引失效。

敬请关注我的公众号：大象只为你，持续更新技术知识......

原文始发于微信公众号（大象只为你）：MySQL存储引擎InnoDB、索引和索引下推