第16期：索引设计（MySQL 的索引结构）
上一章讲了数据库基本上都用 B+ 树来存储索引的原因：适合磁盘存储，能够充分利用多叉平衡树的特性，磁盘预读，并且很好的支持等值，范围，顺序扫描等。这篇主要介绍 MySQL 两种常用引擎，MyISAM 和 InnoDB 的索引组织方式，了解这些存储方式，对数据库优化很有帮助。
聚集索引：也称 Clustered Index。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放，一张表最多也只能存在一个聚集索引。与非聚集索引相比，聚集索引有着更快的检索速度。
MySQL 里只有 INNODB 表支持聚集索引，INNODB 表数据本身就是聚集索引，也就是常说 IOT，索引组织表。非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。
非聚集索引：也叫 Secondary Index。指的是非叶子节点按照索引的键值顺序存放，叶子节点存放索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外，其他的都是二级索引。MYISAM，memory 等引擎的表索引都是非聚集索引。简单点说，就是索引与行数据分开存储。一张表可以有多个二级索引。
MYISAM 表是典型的数据与索引分离存储，主键和二级索引没有本质区别。比如在 MYISAM 表里主键、唯一索引是一样的，没有本质区别。
假设表 t1 为 MYISAM 引擎，列为 ID，姓名，性别，年龄，手机号码。其中 ID 为主键，年龄为二级索引。记录如下：
那对应的两个 B+ 树索引如下图所示，
上图是一个 3 阶的 B+ 树，非叶子节点按照主键的值排序存储，叶子节点同样按照主键的值排序存储，并且包含指向磁盘上的物理数据行指针。
上图年龄字段索引树同样是一个 3 阶的 B+ 树，非叶子节点按照年龄字段的值顺序存储，叶子节点保存年龄字段的值以及指向磁盘上的物理数据行指针。
从上面两张图可以看出，MYISAM 表的索引存储方式最大的缺点没有按照物理数据行顺序存储，这样无论对主键的检索还是对二级索引的检索都需要进行二次排序。
举个简单例子演示下，
以下 SQL 1 默认没有排序，乱序输出；需要按照 ID 顺序输出，就得用 SQL 2，显式加 ORDER BY 。
mysql# SQL 1mysql> select * from t1;+-------+----------+--------+------+--------------+| id | username | gender | age | phone_number |+-------+----------+--------+------+--------------+| 10001 | 小花 | 女 | 18 | 18501877098 || 10005 | 小李 | 女 | 21 | 15827654555 || 10006 | 小白 | 男 | 38 | 19929933000 || 10009 | 小何 | 男 | 35 | 19012378676 || 10002 | 小王 | 男 | 20 | 17760500293 || 10003 | 小赵 | 女 | 29 | 13581386000 || 10004 | 小青 | 女 | 25 | 13456712000 || 10007 | 小米 | 男 | 23 | 19800092354 || 10008 | 小徐 | 女 | 22 | 18953209331 |+-------+----------+--------+------+--------------+9 rows in set (0.00 sec)# SQL 2mysql> select * from t1 order by id;+-------+----------+--------+------+--------------+| id | username | gender | age | phone_number |+-------+----------+--------+------+--------------+| 10001 | 小花 | 女 | 18 | 18501877098 || 10002 | 小王 | 男 | 20 | 17760500293 || 10003 | 小赵 | 女 | 29 | 13581386000 || 10004 | 小青 | 女 | 25 | 13456712000 || 10005 | 小李 | 女 | 21 | 15827654555 || 10006 | 小白 | 男 | 38 | 19929933000 || 10007 | 小米 | 男 | 23 | 19800092354 || 10008 | 小徐 | 女 | 22 | 18953209331 || 10009 | 小何 | 男 | 35 | 19012378676 |+-------+----------+--------+------+--------------+9 rows in set (0.00 sec)
接下来看看 INNODB 的主键索引和二级索引的组成方式。
INNODB 表本身是索引组织表，也就是说索引就是数据。下图表T1的数据行以聚簇索引的方式展示，非叶子节点保存了主键的值，叶子节点保存了主键的值以及对应的数据行，并且每个页有分别指向前后两页的指针。
INNODB 表不同于 MYISAM，INNODB 表有自己的数据页管理，默认 16KB。MYISAM 表数据的管理依赖文件系统，比如文件系统一般默认 4KB，MYISAM 的块大小也是 4KB，MYISAM 表的没有自己的一套崩溃恢复机制，全部依赖于文件系统。
INNODB 表这样设计的优点有两个：
1. 数据按照主键顺序存储。主键的顺序也就是记录行的物理顺序，相比指向数据行指针的存放方式，避免了再次排序。我们知道，排序消耗最大。现在表 t1 的直接拿出来就是按照主键 ID 排序。
mysqlmysql> select * from t1;+-------+----------+--------+------+--------------+| id | username | gender | age | phone_number |+-------+----------+--------+------+--------------+| 10001 | 小花 | 女 | 18 | 18501877098 || 10002 | 小王 | 男 | 20 | 17760500293 || 10003 | 小赵 | 女 | 29 | 13581386000 || 10004 | 小青 | 女 | 25 | 13456712000 || 10005 | 小李 | 女 | 21 | 15827654555 || 10006 | 小白 | 男 | 38 | 19929933000 || 10007 | 小米 | 男 | 23 | 19800092354 || 10008 | 小徐 | 女 | 22 | 18953209331 || 10009 | 小何 | 男 | 35 | 19012378676 |+-------+----------+--------+------+--------------+9 rows in set (0.00 sec)
2. 两个叶子节点分别含有指向前后两个节点的指针，这样在插入新行或者进行页分裂时，只需要移动对应的指针即可。
再来看下 INNODB 表的二级索引，如下图所示：
INNODB 二级索引的非叶子节点保存索引的字段值，上图索引为表 t1 的字段 age。叶子节点含有索引字段值和对应的主键值。
这样做的优点是当出现数据行移动或者数据页分裂时，避免二级索引不必要的维护工作。当数据需要更新的时候，二级索引不需要重建，只需要修改聚簇索引即可。
但是也有缺点：
1. 二级索引由于同时保存了主键值，体积会变大。特别是主键设计不合理的时候，比如用 UUID 做主键。下一篇我详细介绍如何设计合理的主键。
2. 对二级索引的检索需要检索两次索引树。第一次通过检索二级索引叶子节点，找到过滤行对应的主键值；第二次通过这个主键的值去聚簇索引中查找对应的行。
举个例子：
如下 SQL 语句，检索年龄为 23 的行记录：
mysqlselect * from t1 where age = 23;
会拆分成以下两个 SQL 语句：
先通过索引字段 age 找到对应的主键值：10005.
mysqlselect id from t1 where age=23;
再去聚簇索引上根据主键 ID = 10005 检索到需要的数据行，如果表第一次读取，就需要回表。
mysqlselect * from t1 where id = 10005;
不过 MySQL 对这块做了很好的优化，提前做了数据预热（数据预热，这里就不讲了，可以参考 MySQL 手册，手册上介绍的很详细）。
关于爱可生
上海爱可生信息技术股份有限公司是国内开源数据库解决方案领导者、工业互联网高维数据应用创新者。爱可生为产业互联网创新应用提供高性价比、快速落地实现的多数据库管理平台、分布式数据库系统、数据库容器云平台、多地多中心跨云容灾等解决方案。
在工业互联网相关垂直行业，深入分析数据价值，构建数据中台和业务中台的基础软件PaaS平台，用数据技术驱动企业高质量增长。公司产品已被广泛应用于各行业，累计用户超过400家，其中包括工商银行、中国人寿、中国太保、国家电网、上汽集团、中国移动、华为等30多家世界500强企业。