文章目录
- 一、单表查询优化
- 1、单表查询的优化点
- 2、开始优化
- 二、双表查询优化
- 1、双表查询优化点分析
- 2、开始优化
- (1)左表建立索引(试优化)
- (2)右表建立索引(试优化)
- 3、双表创建索引建议
- 三、三表查询优化
- 1、三表查询优化点
- 2、开始优化
- 四、子查询优化
一、单表查询优化
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语句优化:永远用小的结果集驱动大的结果集,即小表驱动大表
!
四、子查询优化
建议先看下:
上述两种方法结果一样,但是一个使用的是in,一个使用的是exists
我们可以说in是子查询驱动主查询
,即先得出子查询的结果,再去主查询拿到id符合条件的记录!
exists可以说是主查询驱动子查询
,先是遍历主查询得到结果,再拿着主查询的结果,执行子查询的where的条件是否成立,成立则返回True,不成立则返回False。因此exists的select语句并没用,因此你可以随便填!
因此得出结论:
1. 当子查询的数据集小于主查询的数据集,in优于exists
2. 当主查询的数据集小于子查询的数据集,exists优于in
为什么?'因为必须符合小表驱动大表的原则!'
3. 至少驱动表的相应索引字段应该建立索引