<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"><span style="white-space:pre"> </span>好久没写关于mysql了,这次主要是关于join三表查询分析研究。</span>
如有错误,希望大家指正!
如下建表sql:
<span style="font-size:14px;">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)));</span><span style="font-size: 24px;">
</span>
<span style="font-size:14px;">
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)));
</span>
先看第一种情况,当三个表的连接字段都没有索引时:
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;
linux执行结果:
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 18 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
三张表都为全表扫描,没有用到索引,扫描的行数20,20,18为表的所有行数,这是查询中最可怕的情况。
第二种,当我们主表加上索引时:
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;
linux执行结果:
alter table class add index idx_class_card (card);
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | class | index | NULL | idx_class_card | 4 | NULL | 20 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 18 | |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
class表card字段使用了索引,类型为索引扫描,但是rows依然为20,原因是因为left join 为查询左表的全部信息,所以查询的rows为全表。
为join连接时:
<pre name="code" class="sql">EXPLAIN SELECT * FROM class JOIN book ON class.card=book.card <span style="font-family: Arial, Helvetica, sans-serif;">JOIN phone ON book.card = phone.card</span><span style="font-family: Arial, Helvetica, sans-serif;">;</span>
+----+-------------+-------+------+----------------+----------------+---------+--------------------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+----------------+---------+--------------------+------+--------------------------------+
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 18 | |
| 1 | SIMPLE | class | ref | idx_class_card | idx_class_card | 4 | dbtest2.phone.card | 1 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer |
明显可以看到 class表查询rows数为 1,这是由于此时mysql优化器会自动进行优化,将phone作为主表,class表为从表,从phone表中取出一条记录,然后和class表进行比较,此时就用到了索引,秩序查询一条即可。
同时 表book的extra中使用了 Using where; Using join buffer 是由于是将表class和phone的结果集进行联合查询。
我们调整下 表的查询顺序‘
EXPLAIN SELECT * FROM book JOIN phone ON book.card = phone.card JOIN class ON class.card=book.card ;
+----+-------------+-------+------+----------------+----------------+---------+--------------------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+----------------+---------+--------------------+------+--------------------------------+
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 18 | |
| 1 | SIMPLE | class | ref | idx_class_card | idx_class_card | 4 | dbtest2.phone.card | 1 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer |
+----+-------------+-------+------+----------------+----------------+---------+--------------------+------+--------------------------------+
可以看到结果一模一样,和上面,就更加验证了我们的推断。
好了下来是第三种:join表也都加上索引时:
alter table book add index idx_book_card (card);
alter table phone add index idx_phone_card (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 | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
| 1 | SIMPLE | class | index | NULL | idx_class_card | 4 | NULL | 20 | Using index |
| 1 | SIMPLE | book | ref | idx_book_card | idx_book_card | 4 | dbtest2.class.card | 1 | Using index |
| 1 | SIMPLE | phone | ref | idx_phone_card | idx_phone_card | 4 | dbtest2.book.card | 1 | Using index |
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
可以看到,book和phone 都达到了ref级别,需要扫描的行数也为1,此时该sql已达最优。
变种:
mysql> explain select * from class join book on class.card = book.card <span style="color:#ff0000;">left join</span> phone on book.card=phone.card;
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
| 1 | SIMPLE | class | index | idx_class_card | idx_class_card | 4 | NULL | 20 | Using index |
| 1 | SIMPLE | book | ref | idx_book_card | idx_book_card | 4 | dbtest2.class.card | 1 | Using index |
| 1 | SIMPLE | phone | ref | idx_phone_card | idx_phone_card | 4 | dbtest2.class.card | 1 | Using index |
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
3 rows in set (0.00 sec)
mysql> explain select * from class join book on class.card = book.card <span style="color:#ff0000;"> join</span> phone on book.card=phone.card;
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+--------------------+------+-------------+
| 1 | SIMPLE | phone | index | idx_phone_card | idx_phone_card | 4 | NULL | 18 | Using index |
| 1 | SIMPLE | class | ref | idx_class_card | idx_class_card | 4 | dbtest2.phone.card | 1 | Using index |
| 1 | SIMPLE | book | ref | idx_book_card | idx_book_card | 4 | dbtest2.phone.card | 1 | Using index |
显然当我们在第二个join连接使用left和不用时,会有很大的差别,
原因分析:
mysql优化器会优化查询的顺序,选择数据量小的作为驱动表。
综上,其实三表join查询甚至n表join查询的原理都和二表一样,先是前两个表join出一个结果集再和第三个表进行join查询。
此外,在我们日常开发中,如果使用left或者right join 查询时,左表或者右表尽量匹配多的过滤条件,即选择数据量小的表作为驱动表。
这些都是些个人看法,如有错误,还希望大家指正,共同学习!