MySQL InnoDB索引使用实践--深入浅出

一、概念

1. 本文前提

本文所有内容、概念、存储结构都基于MySQL5.7及以上版本，存储引擎为：InnoDB

2. 名词

• Clustered Index : 聚(集)簇索引
• Primary Key : 主键
• Unique Index : 唯一索引
• Secondary Indexs：二级索引或非聚簇索引（所有非Clustered Index）
• Multiple-Column Indexes或composite indexes ：多列索引、复合索引、组合索引
  

3. 概念

• InnoDB 每个Table只能拥有一个且必有一个Clustered Index 聚簇索引
• Table拥有Primary Key时，该索引和Clustered Index等价
• InnoDB可以不定义Primary Key，但一定会有Clustered Index
• 所有非Clustered Index，都可以称为Secondary Indexs 二级索引
• Multiple-Column(composite indexes)组合索引，并非聚簇或聚簇索引(表无主键且拥有非空多列唯一索引除外)，而是包含多列的二级索引

二、InnoDB 索引物理结构

InnoDB 索引都是B-Tree(变体)，索引记录的节点按大小顺序存放在同一层的叶子节点中，每个叶子节点之间通过指针连接。 Index Page的默认大小为 16KB，Index Page大小由 MySQL 实例初始化时的 innodb_page_size 设置决定。
B 树索引可用于使用 =、>、>=、<、<= 或 BETWEEN 运算符的表达式中的列比较。如果 LIKE 的参数是不以通配符开头的常量字符串，则索引也可用于 LIKE 比较。
B+ Tree 结构

B+ Tree具有以下特点：

B+树的非叶子节点只存储key，占用的空间很小，所以每一层节点可以索引的数据范围要广得多，每次IO操作可以搜索到更多的数据。
支持范围查询，且部分范围查询非常高效，每个节点可以索引更大更准确的范围，这意味着B+树单磁盘IO信息比B树更大，I/O效率更高。
原因是数据存放在叶子节点层，还有指向其他叶子节点的指针，所以范围查询只需要遍历叶子节点层，不需要遍历整棵树。
叶子节点成对连接，符合磁盘的预读特性。比如叶子节点存储50和55，它有一个指向叶子节点60和62的指针，当我们从磁盘读取50和55对应的数据时，由于磁盘的预读特性，60和62对应的数据也将被顺便读取。这次是顺序读不是磁盘寻道。

简单理解B+Tree

• 把上图Index理解为Clustered Index值，有主键即主键值
• InnoDB的最小储存单元：页（Page），一个页的默认大小是 16K，Page可以储存指针，也可以储存行记录，其中指针指向下一个page的地址
• 假设主键为int类型，InnoDB 的bigint占用4个字节，一个指针（64位系统）占8个字节，4+8=12，所以一个page能存放的指针个数为16k/(4+8)约等于1365
• Page能存储多少条行记录：假设单条行记录大小约为1k，那么一个page能存储的行记录数为16k/1k=16

当新记录插入到 InnoDB 聚簇索引中时，InnoDB 会尝试留出 1/16 的页面以供将来插入和更新索引记录。
如果按顺序（升序或降序）插入索引记录，则生成的索引页大约为 15/16 满。
如果记录以随机顺序插入，则页面从 1/2 到 15/16 满。 如果索引页的填充因子低于 1/2，InnoDB 会尝试收缩索引树以释放该页。

顺序和无序Clustered Index的数据写入过程

MySQL是行存储（与列存储相对）结构，在行存储中每一行都是按顺序存储的。MySQL会预先分配多个Page的存储空间，Page在类似双向链表中依次具有指向彼此的指针。如果分配了另一个范围（已分配页面已满且需要插入另一行），则该范围中的最后一页将具有指向下一个范围中的第一页的指针，这种结构使用读取任何特定页面时能够轻松遍历相邻页面

顺序插入一行(Clustered Index有序)

使用用自增或其它方式生成的有序主键，MySQL向Page写入数据，直到Page中剩余约94%（1/16）的空间，此时将开始向新Page写入数据。插入的数据按其主键值顺序排序，数据库引擎将继续将行写入新Page，直到该页面已使用容量接近1/16，一直重复相同的写入流程

无序插入一行(Clustered Index无序)

如使用UUID随机主键值写入数据，可能将数据随机写入仅剩 6% (1/16) 容量的较旧的Page，如果Page中有足够的空间，MySQL 将插入该行数据，但是如果没有足够的空间，则需要将Page一分为二，并可能需要使用另一个范围内的新页面。拆分Page时原始页面中的一半记录留在原页面，另一半需要移动到新Page，因为数据不是一页接一页地排序，导致磁盘上出现碎片。
随着无序数据的不断写，最终会创建更多的Page，这些Page的填充率在 50-100% 之间，导致读取和写入效率低下，因为数据库引擎在读取和写入时可能必须读/写2个或多个Page

三、Clustered Index 聚簇索引

MySQL InnoDB 引擎具有强制聚簇索引，通常使用主键。也就是主键就是Clustered Index，如果没有主键按以下规则生成。

Clustered Index 条件优化级：

1. 表有明确的PRIMARY KEY：使用PRIMARY KEY
2. 无PRIMARY KEY：InnoDB 默认使用第一个 UNIQUE INDEX，且索引列需要全部定义为非空列（NOT NULL）作为Clustered Index
3. 如无PRIMARY KEY，也没有合适的UNIQUE INDEX，InnoDB将会在包含行ROW ID的合成列上生成一个名为GEN_CLUST_INDEX的隐藏Clustered Index

ROW ID：ROW ID是6 byte字段，由InnoDB分配，用于行排序。插入新行而单调增加，在物理上插入按ROW ID顺序排列

注：UNIQUE INDEX 包含的列需要全部定义为NOT NULL非空，才会被当做Clustered Index

MyISAM 存储引擎不支持聚簇索引并且一直使用堆表

2. 聚簇索引如何加速查询

通过聚簇索引访问行很快，因为索引搜索直接指向包含行数据的页面。如果表很大，与使用与索引记录不同的页来存储行数据的存储组织相比，聚簇索引架构通常可以节省磁盘 I/O 操作。

3. Clustered Index 示例及查询：

INNODB_INDEXES 表type字段说明：

• 0 = 非唯一索引的二级索引 ：nonunique secondary index;
• 1 = 自动生成的聚簇索引：automatically generated clustered index (GEN_CLUST_INDEX);
• 2 = 唯一索引（非聚簇索引）： unique nonclustered index;
• 3 = 聚簇索引 clustered index;
• 32 = 全文索引 full-text index

不同MySQL版本表名不同，使用命令查询：SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB_%’;

自动生成名为GEN_CLUST_INDEX的Clustered Index示例：

-- 创建无主键、无唯一索引 
CREATE TABLE `clustered_index_demo` (
  `id` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 查询表索引 
-- 如5.7以下版本表名不同，使用命令查询：SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
SELECT 
  t2.INDEX_ID ,
  t2.`NAME` , 
  t2.TABLE_ID , 
  t2.`TYPE` , 
  t2.N_FIELDS , 
  t2.PAGE_NO , 
  t2.`SPACE` , 
  t2.MERGE_THRESHOLD
FROM information_schema.INNODB_TABLES t1 
INNER JOIN information_schema.INNODB_INDEXES t2 ON t1.TABLE_ID = t2.TABLE_ID
WHERE t1.`NAME` = 'wiki/clustered_index_demo';

-- 查询结果

| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
|----------|-----------------|----------|------|----------|---------|-------|-----------------|
|     3616 | GEN_CLUST_INDEX |     3276 |    1 |        5 |       4 |  2113 |              50 |

增加包含NOT NULL列的唯一索引示例：

Tips : 修改表结构，InnoDB将删除原自动生成的GEN_CLUST_INDEX索引

-- 增加两列
ALTER TABLE `wiki`.`clustered_index_demo`
ADD COLUMN `username` varchar(32) NOT NULL,
ADD COLUMN `name` varchar(64) NOT NULL;
-- 增加唯一索引 
ALTER TABLE `wiki`.`clustered_index_demo`
ADD UNIQUE INDEX `IDX_UNIQUE` (`username`,`name`) USING BTREE;

| INDEX_ID | NAME       | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
|----------|------------|----------|------|----------|---------|-------|-----------------|
|     3620 | IDX_UNIQUE |     3278 |    3 |        5 |       4 |  2115 |              50 |

唯一索引包含NULL列

-- 将唯一索引，其中一列改为NULL, Clustered Index将被删除，重新生成GEN_CLUST_INDEX
ALTER TABLE `wiki`.`clustered_index_demo`
CHANGE `username