MySQL索引的存储结构

2.3.2. 索引存储结构

索引存储结构有堆表和索引组织表两种方式。

堆表和索引组织表有什么区别？

堆表：

数据和索引是分开存放的，索引是排序后的，但堆数据是无序的进行的都是随机访问，索引的叶子节点中存放的是数据在堆表中的地址，堆表的数据发生改变且位置也发生变更，所有索引中的地址也要更新，非常影响性能。

索引组织表：

索引组织表中数据是根据主键顺序存放在索引中的，即使数据发生了位置变更，主键索引会自动调整数据记录的位置，非主键索引也会根据主键的变化而自动更新。索引就是数据。如果主键是id，表中数据就是按照id顺序存储的。

二级索引：

主键索引外都叫二级索引，跟主键索引不同的是，二级索引的叶子节点不直接存放数据，存放的是索引键值和主键值。通过二级索引只能找到主键值，通过主键索引进行查询才能得到结果。这个操作也叫做“回表”。

二级索引的好处就是，如果当前字段的值发生改变了，只修改当前字段的二级索引，其他的二级索引不需要更新，除非是对应的主键值也被更新了。

函数索引：

正常情况下索引都是建立在字段上，MySQL5.7之后，开始支持索引函数（索引键是一个函数表达式），主要作用是优化业务和配合虚拟字段。有时通过函数索引可以快速解决线上SQL的性能问题；

堆表和索引组织表的应用场景：

堆表比较适合查询操作比较少，写入操作比较多的业务，因为数据直接插入到数据文件中的末尾，不需要考虑索引结构，所以写入操作可以更快地完成。

索引组织表适合应用程序主要进行大量的查询操作时，索引组织表可以通过减少I/O访问提高查询性能。读取性能方面，索引组织表通常更有优势，因为可以通过索引直接访问数据，而不需要回表。

2.3.3. 组合索引

由多个字段组成的索引，既可以是主键索引也可以是二级索引。组合索引是按照索引字段的顺序排序的，查询中的排序条件与索引字段的顺序不一致时，数据库可能无法充分利用索引的有序性。

如何在业务中正确的设计组和索引？

1. 避免额外排序：

例如，电商业务中，订单详情页需要根据用户id查询出订单数据，然后根据购买时间逆序展示，如果只根据用户id字段的索引查询，取出来数据之后还需要再根据购买时间排序。

可以创建用户id和购买时间的组和索引，首先根据用户id进行检索，然后在这个用户id的范围内，根据购买时间的逆序来检索数据，这样在取出结果时就已经是排好序的，不需要在额外排序。

1. 避免回表：

如果要查询的字段不在索引中，就需要回表获取缺失的数据。

数据库执行查询操作时，先会根据查询条件使用的索引来定位到符合条件的记录的位置。如果索引包含了查询需要的所有字段，数据库可以直接从索引中获取数据，无需回表。这种情况称为索引覆盖（Index Covering）。

所以要利用好组合索引包含多个字段的特性（索引覆盖），提升 SQL 的查询性能，用好索引覆盖技术，性能提升 10 倍不是难事。