11. 关联、子查询索引优化

关联查询优化

1. 建表SQL

# 关联查询优化
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)));

2. Explain分析

left join

# 分析，不添加索引，两个表将会扫描(book.rows*class.rows)次
EXPLAIN SELECT * FROM class left JOIN book ON class.card = book.card;

# 为book添加索引之后，book.rows变为1
alter table `book` add index Y(`card`)

# 假如为class表也添加一个索引，发现结果class.rows还是20
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

# 得出结论，当两个表进行左连接查询时，
# 通常前面的是驱动表，后面的是被驱动表，
# 驱动表一定会全表扫描，为驱动表加索引起不了太大作用，最多变成覆盖索引index，
# 但还是无法避免全表扫描，所以当要为关联的两个表加索引时，一定要为被驱动表加

inner join

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

# 添加索引
# alter table `book` add index Y(`card`)
# ALTER TABLE class ADD INDEX X (card);

# 删除索引
# DROP INDEX Y ON book;
# DROP INDEX X ON class;

# 为哪个表添加索引，哪个表就是被驱动表

实际案例

# 1. 直接连两个表查询
# 只有一趟查询，被驱动表d、e1都用上了索引，
# 效果最好
explain select sql_no_cache e.`name`,d.CEO,e1.name from emp e
left join dept d on e.deptId = d.id
left join emp e1 on d.CEO=e1.id;

# 2. select后面的子查询，先查出所有人，再查出所有有门派的人，最后查出ceo对应的名字
# 走了两趟查询，
# 按照id，从大到小，从上到下的规则，
# 先进入2，执行了e1，
# 再进入1，执行了e，最后执行了d
# 总共走了3个表，两个被驱动表d、e1都用上了索引
# 效果不错
explain select sql_no_cache e.`name`,d.CEO,
(select e1.`name` from emp e1 where e1.id=d.CEO) from emp e
inner join dept d on e.deptId = d.id;

# 3. from后面的子查询，先查出所有有门派的人，再关联查出这些人的名字
# MySQL5.7将其优化到1趟查询，
# 而使用5.5发现有两趟查询，其中 e2以及ed的type都为all
# 首先，按照id，进入2，先执行驱动表e2，再执行被驱动表d
# 再进入1，先执行了ed这张虚拟表，再执行被驱动表e1
# 因为有两个all，所以对比上面，效果一般
explain select sql_no_cache e1.name,ed.CEO,ed.`name` from 
(select e2.name,d.CEO from emp e2
inner join dept d on e2.deptId=d.id) ed
left join emp e1 on ed.CEO=e1.id;

# 4. join后面的子查询，先查出所有人，再查出所有门派的掌门人名称，然后再关联查出结果
# MySQL5.7将其优化到1趟查询了
# 而使用5.5发现有两趟查询，其中 e1、d以及ed的type都为all
# 首先，按照id，进入2，按照从上到下的顺序，先执行d表，再执行e2表，e2表启动了主键索引，进行了优化
# 1则是先执行e1表，再执行ed这张虚拟表，而虚拟表是无法创建索引的，
# 并且ed还被放在了被驱动表的位置上，所以白白浪费了建立索引优化的机会
# 所以它的查询最慢，效果较差
explain select sql_no_cache e1.name,ed.CEO,ed.`name` from emp e1
left join(select d.id,e2.name,d.CEO from emp e2
inner join dept d on d.CEO=e2.id) ed
on e1.deptId=ed.id;

3. 总结

1. 两个表关联查询时，要确保被驱动表的join字段已经建好索引了
2. left join时，选择小表作为驱动表，大表作为被驱动表
3. inner join时，MySQL会自动选择合适的表作为被驱动表
4. 子查询尽量不要放在被驱动表，因为通常子查询的结果会作为虚拟表与其他表做关联查询，而虚拟表无法建立索引
5. 能够直接多表关联的查询，不要用子查询

子查询优化

1. 案例

# 查出各个门派非掌门人的成员
# 优化前使用了子查询、关联查询
select  * from t_emp e
where e.id not in (
	select CEO from t_dept d
 inner join t_emp e on e.id = d.CEO
)

# 优化后，只使用关联查询
select * from t_emp e 
left join t_dept d on e.id = d.ceo
where d.id is null

2. 结论

尽量不要使用not in或者not exists，会导致索引失效，可以使用left join on xxx is null替代。