8. 两表和三表优化案例

数据准备

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

-- 书籍分类表增加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 class(card) VALUES (floor(1 + (rand() * 20)));
INSERT INTO class(card) VALUES (floor(1 + (rand() * 20)));


-- 给书籍表增加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)));

查询SQL

SELECT * from book LEFT JOIN class on book.card = class.card;

分析

EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card;

mysql> EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   21 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

添加索引优化

1.先给book表加索引

CREATE INDEX idx_book_card ON book(card);

再分析

mysql> EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card;
+----+-------------+-------+------------+------+---------------+---------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL          | NULL    | NULL                |   21 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_book_card | idx_book_card | 4       | jointest.class.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------------+---------+---------------------+------+----------+-------------+

第二次尝试

2.在class表上加索引

CREATE INDEX idx_book_card ON class(card);

再次分析

mysql> EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card;
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | index | NULL          | idx_book_card | 4       | NULL |   21 |   100.00 | Using index                                        |
|  1 | SIMPLE      | book  | NULL       | ALL   | NULL          | NULL          | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

左连接特性:LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以说右边是我们的关键点,一定需要建立索引

右连接特性: RIGHT JOPIN 条件用于确定如何从左表搜索行,右连一定有,所以左连是我们的关键点,一定要建索引。

三表优化案例

再加一张表,没有实际意义,主要为了建立关联。

CREATE TABLE IF NOT EXISTS `phone`(
  `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`phoneid`)
) ENGINE = INNODB;

INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES (floor(1 + (RAND() * 20)));

需求SQL

SELECT * from class LEFT JOIN book on class.card = book.card LEFT JOIN  phone on book.card = phone.card;

分析SQL

mysql> EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card LEFT JOIN  phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   21 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

发现全是全表扫描

建立索引

CREATE INDEX idx_book_card ON book(card);
CREATE INDEX idx_book_card ON phone(card);

再分析

mysql> EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card LEFT JOIN  phone on book.card = phone.card;
+----+-------------+-------+------------+------+----------------+----------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                |   21 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_book_card  | idx_book_card  | 4       | jointest.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | idx_phone_card | idx_phone_card | 4       | jointest.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+---------------------+------+----------+-------------+

结论:

  • 1.索引最好设置在需要经常查询的字段中。
  • 2.尽可能减少JOIN语句中的NestedLoop的很循环总次数;记住 永远用小结果集驱动大的结果集。
  • 3.优化优化NestedLoop的内层循环(优先化嵌套查询)
  • 4.保证JOIN语句中被驱动表上join条件字段已经被索引 。
  • 5.当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要吝惜JoinBuffer的设置 。