MySQL索引进阶-聚簇索引和二级索引
MySQL索引进阶-聚簇索引和二级索引
索引分类
Mysql中索引主要分为以下几类：
1）数据结构
分为B+树索引、hash索引、FULLTEXT索引、R-Tree索引
2）逻辑角度
分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE)，普通索引(INDEX)，组合索引(INDEX)，全文索引（FULLTEXT）
3）物理存储
分为聚簇索引和非聚簇索引。
主键索引也被称为聚簇索引（clustered index），也叫作聚集索引。其余都称呼为非主键索引也被称为二级索引（secondary index），也叫作辅助索引。
一、聚集索引
InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据 。
我们也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
如下图：
特点：
1）自动建立，一个表只有1个。
2）叶子节点包含所有用户记录（包括隐藏列），record_type为0
3）每层节点都是按照主键从小到大排序
4）内节点（非叶子节点）：存储主键值以及页号， record_type为1
注意：
聚集索引的存储并不是物理上的连续，而是逻辑上的连续。这其中有两点
a. 前面说过的页通过双向链表链接，页按照主键的顺序排序，
b. 每个页中的记录是通过单向链表进行维护的，物理存储上可以同样不按照主键存储。
二、二级（辅助）索引
对于辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表时索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
如下图：
特点：
1）手动创建，可以有多个
2）内节点包含索引列、主键列、页号（page_no）
3）叶子节点只包含索引列以及记录主键的值
4）每层节点都是按照索引列的值从小到大排序（索引列值相同时按照主键排序）
三、联合索引
联合索引是一种特殊的二级索引。联合索引指的是同时对多列创建的索引，创建联合索引后，叶子节点会同时包含每个索引列的值，并且同时根据多列排序，这个排序和我们所理解的字典序类似。
每个叶子节点同时保存了所有的索引列，除此之外，还是只包含了主键id。
如下图：
特点：
1）手动创建，可以有多个
2）内节点包含索引列、主键列、页号
3）叶子节点只包含索引列以及记录主键的值
4）每层节点先按照索引中的第1列排序。第1列值相等时，按第2列排序。第2列值相等时，按第3列排序 依次类推，所有列都相等时按照主键排序。
四、覆盖索引-covering index
即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
简单来说，覆盖索引指的就是只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点：
1）索引项通常比记录要小，所以MySQL访问更少的数据
2）索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O
3）覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
五、常见问题解答
比前提：test表中有c1,c2,c3这列，建立联合索引（c1,c2,c3）
1、 索引的命中与查询sql中字段的顺序有关吗？
比如 select * from test where c1=2 and c2=5; 与 select * from test where c2=5 and c1=2？
答案：与查询的字段顺序无关，因为查询优化器会对搜索字段顺序跟索引字段顺序不一致的sql进行优化。
2、最左前缀原则如何理解，它的原理是什么？
最左前缀原是指从索引的有效命中来说，并不是触发。只要是索引，或者某个联合索引的一部分，就会被触发，具体命中了那些索引字段，要根据key_len来做判断。
原理：
我们从上面的联合索引介绍中看到，它的排序方式是：每层节点先按照索引中的第1列排序。第1列值相等时，按第2列排序。第2列值相等时，按第3列排序 依次类推，所有列都相等时按照主键排序。
所以我们可以这样来分析：
对于联合索引中c1字段是放在最前面的，所以c1是完全有序的，但是c1不知情的条件下，对于c2字段就是无序的，没办法排序。因此只有当c1相同的时候，c2字段的索引排序才是完全有序的。
因此，在联合索引中你只有使用以下的规则的方式查询才会使用到索引：
c1
c1 ,c2
c1, c2, c3
我们再思考一下，select * from test where c2>=5 and c3=7; 这条sql语句会命中索引么？
分析：这里使用了c2和c3这两个字段作为查询条件，但是没有使用c1字段，因为在c1不知情的条件下，对于c2是无序的。对于c2>=5的条件可能在很多的c1不同中都有符合条件的出现，所以就没有办法使用索引，这也是索引实现的原因，一定要遵循「查找有序，充分利用索引的有序性」。
再比如 select * from test where c1>=5 and c2=9;
分析：这个查询语句中，c1列索引会被命中，c2列却不会，只有c1列值相等时，才会按c2列排序。但是因为这里的c1>=5，那么c1列是不确定的，后面也就无法按照c2来排序，c2不会被命中。
3、前导模糊查询为什么会导致索引失效？
比如 select * from test where c1 like '%d%';
字符串的查询是对字符串里面的字符一个一个的匹配，「若是字符串最左边为%表示一个不确定的字符串，那么是没办法利用到索引的有序性」。
但是若是修改为 ：where c1 like 'd%'；就可以使用索引，因为最左边的字符串是确定的，这种称为「匹配列前缀」。
补充说明：实际业务场景中联合索引的创建，「我们应该把识别度比较高的字段放在前面，提高索引的命中率，充分的利用索引」。
4、 如何解决数量占表记录比重较大，查询优化器放弃索引，直接全表扫描？
通过限制查询条数来避免索引失效，比如 select * from test where c2=8 limit 10;
5、为什么MySQL的InnoDB引擎采用B+树而不是B树？
1）范围查询效率高
B+树在非叶子节点只存储键值信息，而不存储数据记录的具体位置，这使得B+树在进行范围查询时更加高效。范围查询通常涉及到一系列相邻的键值，B+树的叶子节点形成了一个有序链表，可以很方便地进行范围扫描。
2）更适合磁盘存储
B+树的叶子节点形成了一个有序链表，便于顺序I/O，减少磁盘I/O的次数。这对于数据库来说非常重要，因为磁盘I/O是一个相对较慢的操作，通过减少I/O次数，可以提高查询性能。
3）更适合范围查询
B+树中叶子节点使用双向指针相连接，形成一条双休有序链表，以及在叶子节点存储了所有关键字的信息，使得范围查询更为高效。而B树则需要在非叶子节点中存储所有关键字的信息，限制了非叶子节点的容量，不太适合范围查询。
4）更适合内存的使用
B+树的内部节点只存储键值信息，而不存储具体数据，这意味着在同样的内存空间下，B+树可以容纳更多的节点，提高了缓存命中率，减少了内存占用。总体而言，B+树更适合数据库索引的应用场景，特别是对于范围查询和大数据集的情况。因此，InnoDB引擎选择了B+树作为其索引结构。
6、二级索引与null值的关系
值为NULL的二级索引记录被放在了B+树的最左边。这是因为InnoDB的设计中有这样的规定：
We define the SQL null to be the smallest possible value of a field.
翻译：我们把SQL中的NULL值认为是列中最小的值。
六、如何挑选索引
1、只为用于搜索、排序或分组的列创建索引
2、考虑列的基数
列基数：列值不重复的行数，基数越大索引效果越好
3、索引列的类型尽量小
1）数据类型越小，在查询时进行的比较操作越快
2）数据类型越小，数据页内就可以放下更多的记录，从而减小磁盘I/O带来的性能损耗
4、索引字符串的前缀
5、索引列在比较表达式中单独出现
6、主键顺序插入（综合评估页分裂、回表、索引树大小）
七、索引总结：
1、B+树索引在空间和时间上都有代价，所以必须合理创建索引
2、B+树索引适用于下边这些情况：全值匹配
1）匹配左边的列
2）匹配范围值
3）精确匹配某一列并范围匹配另外一列
4）用于排序
5）用于分组
3、在使用索引时需要注意下边这些事项
1） 只为用于搜索、排序或分组的列创建索引
2） 为列的基数大的列创建索引
3） 索引列的类型尽量小
4） 可以只对字符串值的前缀建立索引
5） 只有索引列在比较表达式中单独出现才可以适用索引
6） 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有auto_increment属性
7） 定位并删除表中的重复和冗余索引
8）尽量使用覆盖索引进行查询，避免回表带来的性能损耗。
这里有几点要注意下：
1）字符串比较大小：逐字比较，费时，效率低，不建议。
2）key_len表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的。
参考链接：
https://www.dyxmq.cn/databases/mysql/clustered-nonclustered-union-and-unique-indexes-in-mysql.html
