上一章（第15期：索引设计（索引组织方式B+树））讲了数据库基本上都用B+树来存储索引的原因：适合磁盘存储，能够充分利用多叉平衡树的特性，磁盘预读，并且很好的支持等值，范围，顺序扫描等。这篇主要介绍MySQL两种常用引擎，MyISAM和InnoDB的索引组织方式，了解这些存储方式，对数据库优化很有帮助。
MySQL的索引按照存储方式分为两类：
聚集索引：也称ClusteredIndex。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放，一张表最多也只能存在一个聚集索引。与非聚集索引相比，聚集索引有着更快的检索速度。
MySQL里只有INNODB表支持聚集索引，INNODB表数据本身就是聚集索引，也就是常说IOT，索引组织表。非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对INNODB表进行全表顺序扫描会非常快。
非聚集索引：也叫SecondaryIndex。指的是非叶子节点按照索引的键值顺序存放，叶子节点存放索引键值以及对应的主键键值。MySQL里除了INNODB表主键外，其他的都是二级索引。MYISAM，memory等引擎的表索引都是非聚集索引。简单点说，就是索引与行数据分开存储。一张表可以有多个二级索引。
MYISAM表：
MYISAM表是典型的数据与索引分离存储，主键和二级索引没有本质区别。比如在MYISAM表里主键、唯一索引是一样的，没有本质区别。
假设表t1为MYISAM引擎，列为ID，姓名，性别，年龄，手机号码。其中ID为主键，年龄为二级索引。记录如下：
那对应的两个B+树索引如下图所示，
主键字段索引树：
上图是一个3阶的B+树，非叶子节点按照主键的值排序存储，叶子节点同样按照主键的值排序存储，并且包含指向磁盘上的物理数据行指针。
年龄字段索引树：
上图年龄字段索引树同样是一个3阶的B+树，非叶子节点按照年龄字段的值顺序存储，叶子节点保存年龄字段的值以及指向磁盘上的物理数据行指针。
从上面两张图可以看出，MYISAM表的索引存储方式最大的缺点没有按照物理数据行顺序存储，这样无论对主键的检索还是对二级索引的检索都需要进行二次排序。
举个简单例子演示下，
以下SQL1默认没有排序，乱序输出；需要按照ID顺序输出，就得用SQL2，显式加ORDERBY。
mysql#SQL1mysqlselect*fromt1;+-------+----------+--------+------+--------------+
id
username
gender
age
phone_number
+-------+----------+--------+------+--------------+
小花
女
18
小李
女
21
小白
男
38
小何
男
35
小王
男
20
小赵
女
29
小青
女
25
小米
男
23
小徐
女
22
+-------+----------+--------+------+--------------+9rowsinset(0.00sec)#SQL2mysqlselect*fromt1orderbyid;+-------+----------+--------+------+--------------+
id
username
gender
age
phone_number
+-------+----------+--------+------+--------------+
小花
女
18
小王
男
20
小赵
女
29
小青
女
25
小李
女
21
小白
男
38
小米
男
23
小徐
女
22
小何
男
35
+-------+----------+--------+------+--------------+9rowsinset(0.00sec)
接下来看看INNODB的主键索引和二级索引的组成方式。
INNODB表：
INNODB表本身是索引组织表，也就是说索引就是数据。下图表T1的数据行以聚簇索引的方式展示，非叶子节点保存了主键的值，叶子节点保存了主键的值以及对应的数据行，并且每个页有分别指向前后两页的指针。
INNODB表不同于MYISAM，INNODB表有自己的数据页管理，默认16KB。MYISAM表数据的管理依赖文件系统，比如文件系统一般默认4KB，MYISAM的块大小也是4KB，MYISAM表的没有自己的一套崩溃恢复机制，全部依赖于文件系统。
INNODB表这样设计的优点有两个：
1.数据按照主键顺序存储。主键的顺序也就是记录行的物理顺序，相比指向数据行指针的存放方式，避免了再次排序。我们知道，排序消耗最大。现在表t1的直接拿出来就是按照主键ID排序。
mysqlmysqlselect*fromt1;+-------+----------+--------+------+--------------+
id
username
gender
age
phone_number
+-------+----------+--------+------+--------------+
小花
女
18
小王
男
20
小赵
女
29
小青
女
25
小李
女
21
小白
男
38
小米
男
23
小徐
女
22
小何
男
35
+-------+----------+--------+------+--------------+9rowsinset(0.00sec)
2.两个叶子节点分别含有指向前后两个节点的指针，这样在插入新行或者进行页分裂时，只需要移动对应的指针即可。
再来看下INNODB表的二级索引，如下图所示：
INNODB二级索引的非叶子节点保存索引的字段值，上图索引为表t1的字段age。叶子节点含有索引字段值和对应的主键值。
这样做的优点是当出现数据行移动或者数据页分裂时，避免二级索引不必要的维护工作。当数据需要更新的时候，二级索引不需要重建，只需要修改聚簇索引即可。
但是也有缺点：
1.二级索引由于同时保存了主键值，体积会变大。特别是主键设计不合理的时候，比如用UUID做主键。下一篇我详细介绍如何设计合理的主键。
2.对二级索引的检索需要检索两次索引树。第一次通过检索二级索引叶子节点，找到过滤行对应的主键值；第二次通过这个主键的值去聚簇索引中查找对应的行。
举个例子：
如下SQL语句，检索年龄为23的行记录：
mysqlselect*fromt1whereage=23;
会拆分成以下两个SQL语句：
先通过索引字段age找到对应的主键值：.
mysqlselectidfromt1whereage=23;
再去聚簇索引上根据主键ID=检索到需要的数据行，如果表第一次读取，就需要回表。
mysqlselect*fromt1whereid=;
不过MySQL对这块做了很好的优化，提前做了数据预热（数据预热，这里就不讲了，可以参考MySQL手册，手册上介绍的很详细）。
本篇内容介绍到此，简单回顾下本篇内容。本篇主要介绍MySQL常见的两种引擎MYISAM和INNODB的索引组织方式以及各自的优缺点。有问题欢迎批评指正，下一篇我来介绍MySQL如何很好的对主键进行设计。
文章推荐：
第15期：索引设计（索引组织方式B+树）第14期：数据页合并
第13期：表统计信息的计算
关于MySQL的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！本文关键字：#索引##innodb##myisam#想看更多技术好文，点个“在看”吧！预览时标签不可点收录于话题#个上一篇下一篇
