MySQL基础（九）：查询优化

下面是小凰凰的简介，看下吧！
💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活
💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中)
💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！
有相关技能问题可以写在下方评论区，我们一起学习，一起进步。
后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

一、单表查询优化

1、单表查询的优化点

现在我创建一个单表，我相信理解这些应该不需要看表了吧，针对问题，优化就完事了！
需求：查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。
很显然,type 是 ALL,即全表扫描。Extra 里还出现了 Using filesort（文件内排序）

2、开始优化

# 1.1 新建索引+删除索引 
# ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );  alter增加索引，我们采用下面的create方法
create index idx_article_ccv on article(category_id,comments,views); 
 
 
 
# 1.2 第2次EXPLAIN 
EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1; 
  
# 结论： 
# type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。 
# 但是我们已经建立了索引,为啥没用呢? 
# 这是因为按照 BTree 索引的工作原理, 
# 先排序 category_id, 
# 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。 
# 当 comments 字段在联合索引里处于中间位置时, 
# 因comments > 1 条件是一个范围值(所谓 range), 
# MySQL 无法利用索引再对后面的 views 部分进行检索,即 范围类型查询字段后面的索引无效。 
 
 
# 1.3 删除第一次建立的索引 
DROP INDEX idx_article_ccv ON article; 
 
# 1.4 第2次新建索引 
create index idx_article_cv on article(category_id,views); 
 
# 1.5 第3次EXPLAIN 
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; 
# 结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。 

二、双表查询优化

1、双表查询优化点分析

#  下面开始 explain 分析 
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; 
# 结论： type  有 All ，且双表都是ALL，接下来我们需要加索引
# 那么我们到底是选择给那张表的card字段加索引呢？

2、开始优化

（1）左表建立索引（试优化）

#  添加索引优化 
ALTER TABLE class ADD INDEX X (card); 
 
#  第 2 次 explain 
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; 

有一定的优化，type由全表扫描变成了索引树遍历！接下来我们看看右表card字段建立索引！

（2）右表建立索引（试优化）

#  删除旧索引  +  新建  +  第 3 次 explain 
DROP INDEX X ON class; 
ALTER TABLE `book` ADD INDEX Y ( `card`);  # 给左连接的右表加索引
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; 

可以看到第二行的 type 变为了 ref,rows 也由20变成了1，优化比较明显。
这是由左连接特性决定的。 LEFT JOIN 条件用于确定如何从右表搜索行 , 左边一定都有 , 所以右边是我们的关键点 , 一定需要建立索引。

3、双表创建索引建议

• left join 时，选择小表作为驱动表，大表作为被驱动表。
• 保证被驱动表的join字段已经被索引
• inner join 时，mysql 自动选择小表作为驱动表。因为 驱动表无论如何都会被全表扫描。所以扫描次数越少越好
• 子查询尽量不要放在被驱动表，有可能使用不到索引。

select a.name ,bc.name from t_emp a left join
         (select b.id , c.name from t_dept b 
         inner join t_emp c on b.ceo = c.id )bc 
         on bc.id = a.deptid.
上段查询中用到了子查询，必然 bc 表没有索引。肯定会进行全表扫描
上段查询 可以直接使用 两个 left join 优化
select a.name , c.name from t_emp a
    left outer join t_dept b on a.deptid = b.id
    left outer join t_emp c on b.ceo=c.id
所有条件都可以使用到索引
 
若必须用到子查询，可将子查询设置为驱动表，，因为驱动表的type 肯定是 all，而子查询返回的结果表没有索引，必定也是all

三、三表查询优化

1、三表查询优化点

2、开始优化

三表连接查询，会出现连接缓存！按照两个表的规律，分别建立两个索引：
效果如下：
总结：
后2行的type都是ref且总的rows优化效果很好，效果不错，因此索引最好设置在需要经常查询的字段中。
join语句优化：永远用小的结果集驱动大的结果集，即小表驱动大表！

四、子查询优化

建议先看下：https://blog.csdn.net/weixin_44571270/article/details/107027121
上述两种方法结果一样，但是一个使用的是in，一个使用的是exists
我们可以说in是子查询驱动主查询，即先得出子查询的结果，再去主查询拿到id符合条件的记录！
exists可以说是主查询驱动子查询，先是遍历主查询得到结果，再拿着主查询的结果，执行子查询的where的条件是否成立，成立则返回True,不成立则返回False。因此exists的select语句并没用，因此你可以随便填！

因此得出结论：

1. 当子查询的数据集小于主查询的数据集，in优于exists
2. 当主查询的数据集小于子查询的数据集，exists优于in
为什么？'因为必须符合小表驱动大表的原则！'
3. 至少驱动表的相应索引字段应该建立索引