三种索引:全文索引,哈希索引,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), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数