查询优化_关联查询与子查询优化

1.关联查询优化

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

1.2.例子

1.2.1.采用左连接：left join

# 下面开始explain分析
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;
#结论：type 有All

添加索引优化

CREATE INDEX idx_book_card ON book(card);

【右表被驱动表】，可以避免全表扫描

CREATE INDEX idx_class_card ON class(card);

【左表驱动表】，无法避免全表扫描

1.2.2.采用内连接：inner join

换成inner join（MySQL自动选择驱动表）
先删除之前建立的索引！class book;

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

添加索引优化

1.3.建议

1)保证被驱动表的join字段被索引
2)left join 时，选择小表作为驱动表，大表作为被驱动表 3)inner join时，mysql会自动将小结果集的表选为驱动表。选择相信mysql优化策略。
4)能够直接多表关联的尽量直接关联，不用子查询

证明：

explain SELECT a.name ,(SELECT c.name FROM t_emp c WHERE c.id=b.ceo) AS ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id ;

explain SELECT a.name,c.name AS ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 	LEFT JOIN t_emp c ON b.ceo = c.id ;

2.子查询优化

两种：非掌门人员  
先创建一个索引
CREATE INDEX idx_ceo ON t_dept (ceo);
#①不推荐
Explain SELECT * FROM t_emp a WHERE a.id NOT IN 
(SELECT b.ceo FROM t_dept b WHERE b.ceo IS NOT NULL);
#②推荐
Explain SELECT a.* FROM t_emp a 
LEFT JOIN t_dept b
ON a.id = b.ceo 
WHERE b.id IS NULL;
结论：
NOT IN -->LEFT JOIN xxx ON xx WHERE xx IS NULL
尽量不要使用not in  或者 not exists