03 深入浅出索引上下

Myth丶恋晨 2023-01-05 12:15 155阅读 0赞

#### 三种索引：全文索引，哈希索引，B+树索引 ####

#### 索引维护：页分裂。(随机插入，向右分裂) ####

索引的选择：身份证ID or 自增主键  
性能 + 存储。  
性能：身份证ID，是随机插入，页分裂是中间分裂，可能会造成一定的存储浪费，分裂次数页比较多。但是自增主键，只有页满才会将新点分裂到下一个页，效率是最高的。  
存储：一个B+树节点，存储的身份证ID数量 < 自增主键数量不同。

以上对应于：mysql 45讲：深入浅出索引1

#### 回表：使用了辅助索引，搜索完辅助索引，还要再搜索聚集索引。 ####

select \* from T where k between 3 and 5流程：  
在 k 索引树上找到 k=3 的记录，取得 ID = 300；  
再到 ID 索引树查到 ID=300 对应的 R3；  
在 k 索引树取下一个值 k=5，取得 ID=500；  
再回到 ID 索引树查到 ID=500 对应的 R4；  
在 k 索引树取下一个值 k=6，不满足条件，循环结束。

#### 覆盖索引：辅助索引直接查询到所需要的信息。 ####

问题：：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？  
有一个高频请求，要根据市民的身份证号查询他的姓名。  
是要建立联合索引的。因为这样会造成覆盖索引

#### 最左前缀原则： ####

问题：在建立联合索引的时候，如何安排索引内的字段顺序。  
第一原则是：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。  
如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。  
第二原则是：索引空间。

#### MRR优化 ####

Multi-Range Read优化可适用于range，ref，eq\_ref类型的查询。（检索辅助索引之后，将结果在内存中排序，之后检索聚集索引。）  
Multi-Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，

#### 索引下推：ICP优化。 ####

当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层  
问题：联合索引（name, age）。检索出表中名字第一个字是张，而且年龄是 10 岁的所有男孩。  
索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数