Day8——关联查询优化

た入场券 2022-11-27 07:05 84阅读 0赞

### 文章目录 ###

*  一. 回顾
 *  二. 关联查询优化
 *  三. 建议

# 一. 回顾 #

前面学习了[Day7——单表优化][Day7]，今天来学习关联查询优化

# 二. 关联查询优化 #

**注意代码中的注释**，如下：

#建表
    create table if not exists class(
        id int(10) unsigned not null auto_increment,
    		card int(10) unsigned not null,
    		PRIMARY KEY (id)
    );
    
    create TABLE if not exists book(
        bookid int(10) unsigned not null auto_increment,
    		card int(10) unsigned not null,
    		PRIMARY KEY (bookid)
    );
    
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    insert into class(card) VALUES(floor(1+(rand()*20)));
    
    
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    insert into book(card) VALUES(floor(1+(rand()*20)));
    -------------------------------------------------------------
    
    #先读book表，再读class表。扫描20*20行
    EXPLAIN SELECT * from book left join class on class.card = book.card;#先读class表，再读book表。扫描20*1行
    EXPLAIN SELECT * from class left join book on class.card = book.card;
    #总结：left join前的表叫驱动表，后面的叫被驱动表。
    #总结：驱动表一般情况不可避免的进行全表扫描，因此加了索引也没用。
    
    
    #建索引
    alter table book add index y (card);
    
    #先读class表，再读book表。扫描20*1行
    EXPLAIN SELECT * from book inner join class on class.card = book.card;
    #总结：inner join会选择没有索引的作为驱动表，被驱动表不用全表扫描，加索引扫描得快
    #总结：两个表都有索引，那么mysql会选择数据量少的作为驱动表
    -------------------------------------------------------------------------------------
    #写法1 NO4
    select c.name, ab.name ceoname from t_emp c left JOIN (
    SELECT name, deptId from t_emp a inner join t_dept b
    on a.id = b.ceo)ab
    on c.deptId = ab.deptId;
    #写法2 NO3
    SELECT ab.name, c.name ceoname from (
    SELECT a.name, b.ceo from t_emp a left join t_dept b
    on a.deptId = b.id)ab
    left join t_emp c
    on ab.ceo = c.id;
    
    #总结:写法2的子查询放在left join前面，即虚拟表作为驱动表，被驱动表能加且能用索引
    #总结：写法1的子查询放在left join后面，即虚拟表作为被驱动表，虚拟表本身不能加索引，
    # 而驱动表本来也不可以建索引。因此写法2比写法1好，子查询不建议放在被驱动表上。

# 三. 建议 #

1.  保证被驱动表的join字段已经被索引了
2.  left join时，选择小表作为驱动表，大表作为被驱动表。`left join` 前的表称为驱动表，`left join`后面的表称为被驱动表。
3.  inner join时，mysql自动将小结果集的表作为驱动表。（不是100%选择正确的，有时会出现错误）
4.  子查询尽量不要放在被驱动表，子查询得出的是虚拟表，用不到索引。
5.  能够直接多表关联的尽量直接关联，不用子查询。

[Day7]: https://blog.csdn.net/qq_40634846/article/details/107991515