MySQL——关联查询优化
1、数据准备
建表
create table if not exists type
(
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)
);
2、外连接
sql查询分析：type表为驱动表，book表为被驱动表，进行左连接，可以看到，两张表的type类型都是全表扫描
mysql> explain select * from type left join book on type.card = book.card;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 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)
给被驱动表book建立索引：
create index idx_book_card on book(card);
sql查询分析：被驱动表type类型为ref，在被驱动表上建立索引，效率会有很大的提升
mysql> explain select * from type left join book on type.card = book.card;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_book_card | idx_book_card | 4 | testdb1.type.card | 1 | 100.00 | Using index |
+
2 rows in set, 1 warning (0.00 sec)
给驱动表type加上也索引：
create index idx_type_card on type(card);
sql查询分析：驱动表和被驱动表都用上了索引，等值条件的类型要相同
mysql> explain select * from type left join book on type.card = book.card;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | type | NULL | index | NULL | idx_type_card | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ref | idx_book_card | idx_book_card | 4 | testdb1.type.card | 1 | 100.00 | Using index |
+
2 rows in set, 1 warning (0.01 sec)
3、内连接
sql查询分析：两张表都是全表扫描，MySQL自动选择驱动表
mysql> explain select * from type inner join book on type.card = book.card;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+
2 rows in set, 1 warning (0.00 sec)
给被驱动表book建立索引：
create index idx_book_card on book(card);
sql查询分析：
mysql> explain select * from type inner join book on type.card = book.card;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_book_card | idx_book_card | 4 | testdb1.type.card | 1 | 100.00 | Using index |
+
2 rows in set, 1 warning (0.00 sec)
给驱动表type加上索引：
create index idx_type_card on type(card);
sql查询分析：发现 查询优化器把book作为了驱动表，type作为被驱动表
mysql> explain select * from type inner join book on type.card = book.card;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | book | NULL | index | idx_book_card | idx_book_card | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | type | NULL | ref | idx_type_card | idx_type_card | 4 | testdb1.book.card | 1 | 100.00 | Using index |
+
2 rows in set, 1 warning (0.00 sec)
注：如果等值条件中只有一个字段有索引的情况，那么查询优化器会把有索引字段的表作为被驱动表
4、小结
- 小结果集驱动大结果集（本质就是减少外层循环的数据数量）
- 为被驱动表匹配的字段条件增加索引（减少被驱动表的循环匹配次数）
- 增大 join buffer size的大小，驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k。驱动表一次加载的数据越多，被驱动表的扫表次数就越少。
- 减少驱动表不必要的字段查询
|