【MySQL之轨迹】MySQL索引优化实战（1）单表索引优化过程

实战题目1：单表索引优化

有如下表数据，找出 贴吧id=5，帖子点赞>1 且 帖子浏览 最多的 帖子id

① 初步达成要求：

select post_id from t_post 
where post_bar_id = 5 
and post_liked > 1 
order by post_browsing desc limit 1;

分析：可以看到进行了全表扫描（ALL），没有使用任何索引（key），还惊动了 Using filesort 文件外排序（没有索引又需要排序）

思考1：post_bar_id 作为外键，怎么以索引的形式出现了？
答：在使用 show create table 后发现，外键被自动加上了 key 变成普通索引了。所以得到结论：主键会自动创建主键索引 primary key（特殊的唯一索引），而外键则会被创建普通索引 key

② 添加索引进行优化：

为 post_liked 添加普通索引
create index liked_index on t_post(post_liked);

type 变成 ref 非唯一索引扫描了，而 ref 显示 const 说明起作用的是 where post_bar_id = 5 这一句

思考2：前边没为 post_liked 添加索引时，有 post_bar_id 这个索引为什么没用，依旧进行全表扫描而不是非唯一索引扫描？
前边没有添加索引时，MySQL 优化器将 =5 和 >1 的顺序调换了，因为先找 =5 再遍历一次找 >1 的，不如在找 >1 的同时判断是否 =5，故 >1 的排在了前面，导致后面外键的普通索引失效了，这就是为什么前面进行了全表扫描。

思考3：添加的索引是 post_liked 的，为什么最后使用的不是它而是外键的？

③ 尝试再添加一个索引

再为 post_browsing 添加普通索引
create index browsing_index on t_post(post_browsing);

emm，type 从 ref 降到了 index，效率变低了。看来索引不能随便建，也不是越多越好。不过因为对需要排序的加上了索引，Using filesort 不见了，性能应该提高一点

思考4：预计使用到的索引都没用到，反而用了没有预计的，同时后边没有 Using index 不是覆盖索引，为什么会这样？

---

④ 将 post_liked 的索引删除，看看效果

效果差不多

⑤ 将两个索引删除，创建他们的联合索引

create index liked_browsing_index on t_post(post_liked, post_browsing);

可以发现和单独使用 post_liked 的索引效果是一样的

思考5：为什么效果一样？
答：原因在 post_liked > 1 上，由于使用了范围，使得后一个索引失效了，只用到了 post_liked 的索引

⑥ 尝试给外键加联合索引

create index blb on t_post(post_bar_id, post_liked, post_browsing);

居然能加？！！而且 type 变成了 range，并且使用了覆盖索引（Using index），但文件外排序依旧还在说明排序的索引并有用上（最左前缀进行到 > 1 那里就失效了）

思考6：外键本身就是索引了，为什么还能给他加索引？
答：在查看索引时发现，新增的索引把旧的索引覆盖掉了，普通索引变成了联合索引

⑦ 既然这样，那就把外键和 post_browsing 进行联合索引，把 post_liked 单独拎出来建普通索引

但在删除索引时出了问题：为外键添加索引后删除不了了

drop index blb on t_post;
ERROR 1553 (HY000): Cannot drop index 'blb': needed in a foreign key constraint

思考7：为什么联合索引有了外键就删除不了？
答：据了解，外键是必须有一个索引的，不管是什么索引（默认是普通索引），所以如果把联合索引删除了，就相当于外键没有索引，这是不允许的。
知道了这个就好办了，先为外键单独创建一个索引，再删除联合索引就好了

create index post_bar_id on t_post(post_bar_id);
drop index blb on t_post;

这样就成功了，接下来按思路创建索引

create index bib_index on t_post(post_bar_id, post_browsing);
create index liked_index on t_post(posyt_liked);

解析：ohhhhhhhh！！！成功了，type 是 ref 唯一索引扫描，Using filesort 也不见了！

---

落日熔金，满月寒潮（IceClean）