前言
写过SQL的同学对join
关键字都不会陌生,join
关键字用来对多张表进行连接查询。
MySQL实现连接查询,靠的是嵌套循环算法(nested-loop algorithm)或者其变种形式。
嵌套循环算法总共分为三种:简单嵌套循环(Simple Nested-Loop)、块嵌套循环(Block Nested-Loop)、索引嵌套循环(Index Nested-Loop)
简单嵌套循环
简单嵌套循环算法的表示图大致如下
表a和表b分别有m、n条数据。简单嵌套循环算法,会将驱动表a中的每条数据和表b中每条数据进行对比。表示成代码的形式大致如下:
for (Object aRecord : tableA) {
for (Object bRecord : tableB) {
if (aRecord.xxx == bRecord.xxx) {
// 取出对应记录...
}
}
}
也就是双重循环,这样的话,对比的次数是m * n
,显然效率非常低。也正是如此,MySQL提供了一种变种的方式,就是块嵌套循环(Block Nested-Loop Join)
块嵌套循环
块嵌套循环的表示图大致如下:
相比于简单嵌套循环,这种实现方式引入了join buffer
,用来缓存驱动表a中的部分或者全部数据(如果大小足够)。
这种join
方式的优化思路是一次性从驱动表a中取出大量或者全部的数据,放入buffer
(内存)中,然后利用buffer
中的数据直接去匹配被驱动表b中的数据。这样就大大减少了对被驱动表b的读取次数。假如驱动表a中有10条数据,那么采用简单嵌套循环的方式会对被驱动表b中的每条数据读取10次,而采用块嵌套循环的方式,一次性将a表中的10条数据全部读入buffer
,那么被驱动表b中的每条数据只需要被读取1次。
使用块嵌套循环需要开启block_nested_loop
,默认是开启的
mysql> show variables like 'optimizer_switc%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| optimizer_switch | ... block_nested_loop=on ... |
+------------------+------------------------------+
1 row in set (0.00 sec)
join_buffer_size
默认大小是:262144 B
= 256 KB
,这个大小是分配给每个join
的
One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.
mysql> show variables like 'join_buffer_size%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)
索引嵌套循环
前面所描述的两种方式,都是在被驱动表中的连接字段没有建立索引的情况。如果被驱动表中的连接字段建立了合适的索引,MySQL便会使用索引嵌套循环(Index Nested-Loop)。索引嵌套循环的表示图大致如下:
该方式的优化思路就是,利用被驱动表b中的索引,来减少对b表数据的扫描数量。
相比于简单嵌套循环中,驱动表a中的每条数据都要和被驱动表b中的每条数据进行比对。而现在表a中的每条数据只需要在索引上进行查找,每条数据查找的次数就是B+树的深度,大大减少了对b表数据的扫描数量。
实践
SQL脚本
CREATE TABLE S (
SNO INT PRIMARY KEY,
SNAME VARCHAR(10)
);
CREATE TABLE C(
CNO INT PRIMARY KEY,
CNAME VARCHAR(10),
CTEACHER VARCHAR(10)
);
CREATE TABLE SC(
SNO INT,
CNO INT,
SCGRADE INT
);
INSERT INTO s VALUES (1, 'zhangsan');
INSERT INTO s VALUES (2, 'lisi');
INSERT INTO s VALUES (3, 'wangwu');
INSERT INTO c VALUES (1, '数学', 'liming');
INSERT INTO c VALUES (2, '语文', 'liming');
INSERT INTO c VALUES (3, '历史', 'xueyou');
INSERT INTO c VALUES (4, '物理', 'guorong');
INSERT INTO c VALUES (5, '化学', 'liming');
INSERT INTO sc VALUES (1, 1, 59);
INSERT INTO sc VALUES (1, 2, 70);
INSERT INTO sc VALUES (2, 1, 30);
INSERT INTO sc VALUES (1, 3, 16);
INSERT INTO sc VALUES (2, 3, 61);
INSERT INTO sc VALUES (3, 1, 17);
INSERT INTO sc VALUES (3, 2, 100);
INSERT INTO sc VALUES (3, 5, 25);
INSERT INTO sc VALUES (1, 4, 99);
测试
执行如下SQL语句,不太了解explain
关键字朋友可以参考 MySQL优化:explain、show profile和show processlist
explain select * from s left join sc on s.sno = sc.sno left join c on sc.cno = c.cno;
执行结果如下:
mysql> explain select * from s left join sc on s.sno = sc.sno left join c on sc.cno = c.cno;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
| 1 | SIMPLE | sc | ALL | NULL | NULL | NULL | NULL | 9 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | test.sc.CNO | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
3 rows in set (0.00 sec)
首先看s
和sc
的join
查询,Extra
列可以看到,使用了Block Nested Loop
,因为sc
表中的sno
列并没有建立索引。
再看sc
和c
的join
查询,type
列可以看到是eq_ref
,意思就是:使用PRIMARY KEY
或者UNIQUE NOT NULL
索引进行连接查询。因为c
表中cno
是主键。
总结
知道了join
查询的原理,相信你已经知道该怎么优化连接查询了。主要总结为以下几点:
- 被驱动表的连接字段尽量建立合适的索引,或者直接通过主键来关联
- 设置合适的
join_buffer_size
,最好能够一次性把驱动表的数据全部容纳下 - 不要查询返回不需要的字段,因为
join_buffer
中存放的不仅仅是连接条件相关的字段,还有select
子句后面的字段。查询返回的列越少,join_buffer
中能容纳的数据量越大
参考
- https://dev.mysql.com/doc/refman/5.6/en/nested-loop-joins.html