MySQL索引进阶-聚簇索引和二级索引
MySQL索引进阶-聚簇索引和二级索引
索引分类
Mysql中索引主要分为以下几类：
1）数据结构
分为B+树索引、hash索引、FULLTEXT索引、R-Tree索引
2）逻辑角度
分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE)，普通索引(INDEX)，组合索引(INDEX)，全文索引（FULLTEXT）
3）物理存储
分为聚簇索引和非聚簇索引。
主键索引也被称为聚簇索引（clustered index），也叫作聚集索引。其余都称呼为非主键索引也被称为二级索引（secondary index），也叫作辅助索引。
一、聚集索引
InnoDB 使用索引组织表的方式存储数据，表中的数据按照聚集索引的顺序组织。
聚集索引通常根据主键构建一棵 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+ 树的非叶子节点只存储索引键和子节点指针，不存储完整的数据记录，因此单个节点可以容纳更多索引项，降低树的高度，减少磁盘 I/O 次数。
2）范围查询效率更高
B+ 树的叶子节点按照键值有序排列，并通过双向链表连接。进行范围查询时，只需要定位到起始位置，就可以沿着叶子节点顺序扫描，不需要反复从根节点查找。
3）更适合缓存
由于非叶子节点存储的数据量较少，同样大小的内存可以缓存更多索引项，提高索引节点的缓存命中率。
总结：B+ 树具有树高较低、磁盘 I/O 次数少、范围查询效率高等优点，因此更适合作为 InnoDB 的索引结构。
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+树索引适用于下边这些情况：全值匹配
- 匹配左边的列
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 用于排序
- 用于分组
3. 在使用索引时需要注意下边这些事项
- 只为用于搜索、排序或分组的列创建索引
- 为列的基数大的列创建索引
- 索引列的类型尽量小
- 可以只对字符串值的前缀建立索引
- 只有索引列在比较表达式中单独出现才可以适用索引
- 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有auto_increment属性
- 定位并删除表中的重复和冗余索引
- 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。
这里有几点要注意下：
1）字符串比较大小：不建议
逐字比较，费时，效率低，不建议。
2）key_len理论上越短越好
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的。
参考链接：
https://www.dyxmq.cn/databases/mysql/clustered-nonclustered-union-and-unique-indexes-in-mysql.html
浙公网安备 33010602011771号