第9章关联查询优化
1. 建表语句
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. 案例
2.1 left join
①EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
②如何优化?在哪个表上建立索引?
ALTER TABLE `book` ADD INDEX idx_card( `card`);
③删除book表的索引:drop index idx_card on book;
在class表上建立索引:alter table class add index idx_card(card);
结论:
①在优化关联查询时,只有在被驱动表上建立索引才有效!
②left join时,左侧的为驱动表,右侧为被驱动表!
2.2 inner join
①EXPLAIN SELECT * FROM book inner join class on class.card=book.card;
②两个查询字段调换顺序,发现结果也是一样的!
③在book表中,删除9条记录
④结论:inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
⑤straight_join: 效果和inner join一样,但是会强制将左侧作为驱动表!
2.3 四个关联查询案例分析
EXPLAIN SELECT ed.name '人物',c.name '掌门' FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed LEFT JOIN t_emp c on ed.ceo= c.id; |
上述两个案例,第一个查询效率较高,且有优化的余地。第二个案例中,子查询作为被驱动表,由于子查询是虚表,无法建立索引,因此不能优化。
结论:
子查询尽量不要放在被驱动表,有可能使用不到索引;
left join时,尽量让实体表作为被驱动表。
本教程由尚硅谷教育大数据研究院出品,如需转载请注明来源。