MYSQL高级之关联查询优化

建表

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)));

题目

查询card对应的book的所有的信息

查询一

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

我们可以看到type中都是all，我们优化中最主要关注的就是type，也就是说他是全表扫描，我们对他进行优化

查询二

添加索引

ALTER TABLE `book` ADD INDEX Y ( `card`);

再次执行查询语句

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

• 我们会发现表book查询type变为了ref，rows的变化也非常明显
• left join 条件用于确定如何从右表搜索行，左边一定有，所以右边是我们的关键点，一定需要建立索引
• 假设我们给左表新建索引，那么查询如下

# 删除原先的索引
DROP INDEX Y ON book;
# 新建索引
ALTER TABLE class ADD INDEX X (card);
# 查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

我们会发现他优化的并不明显

总结

1. 保证被驱动表的join字段已经被索引
2. left join时，选择小表作为驱动表，大表作为被驱动表。
3. inner join的时候mysql自己会帮你把最小结果集的表选为驱动表
4. 子查询尽量不要放在被驱动表，有可能使用不到索引
   1. 如果必须用到子查询，可将子查询设置为驱动表，因为驱动表的type肯定是all，而子查询返回的结果表没有索引，必定也是all，例子如下

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.

5. 上面代码用到了子查询，其中临时表bc必然没有索引，那么他就会进行全表的扫描
6. 可以使用两个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

这样，所有的条件都会用到索引