MySQL实战45讲 笔记（5)

写在最前，本篇文章来源于对MySQL实战45讲的笔记，主要是为了帮助自己理解。如果同时还能对其他人有所裨益，那就更好不过了。如果有谬误的地方，还请不吝指出。

本文并非对文章的直接复制，并且肯定有理解不到位的情况，如果希望系统地学习，还是要去官网支持原作者。

注意：最好拥有一定的MySQL基础再来看本系列文章，可以去b站搜索动力节点的mysql基础教程，或者翻看我做的走进MySQL系列（笔记做的并不是特别详尽，仅作为参考）

查询语句的搜索

执行select * from T where k between 3 and 5这条语句需要多少次树的搜索操作？
已知ID是主键，K是非主键但有索引

1. 从K索引上找到k=3的记录，取得ID=300；
2. 回表找到对应R3
3. 在K索引取得下一个值k=5，取得ID=500；
4. 回表找到对应R4
5. 取得k=6，不满足条件，退出循环

如果区间加大，意味着会有更多次回表操作，怎么样来避免呢？

覆盖索引

如果查询结果仅是主键，那么就可以直接提供结果，不需要回表。也就是说，在这个查询中，索引k已经覆盖了我们的查询请求，称为覆盖索引。

覆盖索引可以减少树的搜索次数，提升查询性能，是一个常用的性能优化手段。

注意：在引擎内部使用覆盖索引在索引k上其实读了三个数据，R3~R5，但对于MySQL的server层来说，它就是找引擎拿了两条记录，因此认为扫描行数为2。

问题：什么时候使用联合索引比较好？
如果有高频请求是针对某字段值查询另一字段值，使用联合索引可以运用覆盖索引，不再需要回表查整行记录，减少语句执行时间。
当然，索引字段的维护是有代价的，是否建立冗余索引来支持覆盖索引需要权衡考虑。

最左前缀原则

如果为每一种查询都设计一个索引，索引会不会太多了？
但如果要去查找一个值，又不能让它走全表扫描。
该怎么做呢？

以（name，age）联合索引分析：

可以看到，索引项是按照索引定义里出现的字段顺序排列的。
当逻辑需求是查到所有“张三”时， 可以迅速定位到ID4，然后向后遍历得到结果。
如果是 like “张%”，先找到ID3，然后向后遍历。

只要满足最左前缀，就能用索引来加速检索。
除了字段内字符外，最左前缀还适用于联合索引的字段。

如果现在已经有了（a，b）的联合字段，一般就不需要在a上建立索引了，因为（a,b）顺序本身就是基于a的。

回到开头的问题，已知(name, age)的联合索引，现在想通过name找到address，怎么办？
还是可以通过(name, age)这个索引先取得name对应的id，再通过回表找到address。这样相当于用到了非主键索引，仅仅是没用到覆盖索引。

如果既有联合查询，又有基于a，b各自的查询呢？
这时候就需要维护另一个索引b，即(a,b) (b)两个索引

索引下推

我们知道，联合索引是先基于最左侧的字段顺序排列，然后再根据其它字段排列。如果通过联合索引中的字段查询，效率会大大提高。

但如果一个查询语句的条件包括了最左字段的模糊查询，该怎样执行？
此时模糊查询可以使用到最左前缀原则，但从组合字段上来看已经不满足最左原则了（模糊查询破坏了它）

比如mysql> select * from tuser where name like '张%' and age=10;
通过索引可以先找到满足第一个字为张的记录

然后呢？
在MySQL 5.6 以前，只能一个个回表，从主键索引上找出数据行，再对比字段值。
在MySQL 5.6 以后，可以在索引遍历过程中，对索引包含的字段先判断，直接过滤掉不满足条件的记录，减少回表次数。这就是索引下推。以上面的语句为例，对每一个记录先判断age是否=10，满足条件的才能够回表。

需要注意的是，索引下推是在“仅能利用最左前缀索引的场景”下，对其它联合索引字段加以利用

精选问题与评论

通过两个alter语句重建索引K，以及通过两个alter语句重建主键索引是否合理？
即alter table T drop index k; alter table T add index(k);
和alter table T drop primary key; alter table T add primary key(id);
前者合理，后者不合理。
重建索引k可以创建新的索引，将数据按顺序插入，这样页面的利用率更高。
但重建主键（不论是删除还是创建）都会将整个表重建。可以用alter table T engine=InnoDB;代替，触发MySQL重建该表，并进行碎片处理。