MySQL——关联查询优化

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。驱动表一次加载的数据越多，被驱动表的扫表次数就越少。
• 减少驱动表不必要的字段查询