MYSQL连接查询的优化
表连接算法
- Nested Loop Join（NLJ）算法：
首先介绍一种基础算法:NLJ，嵌套循环算法。循环外层是驱动表，循坏内层是被驱动表。驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录，然后从头扫描被驱动表，逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。被驱动表查找完后，再从驱动表中取出第二个记录，然后从头扫描被驱动表，逐一查找与驱动表第二条记录匹配的记录，连接起来形成结果表中的一条记录。重复上述操作，直到驱动表的全部记录都处理完毕为止。这就是嵌套循环连接算法的基本思想，伪代码如下。
foreach row1 from t1
foreach row2 from t2
if row2 match row1 //row2与row1匹配，满足连接条件
join row1 and row2 into result //连接row1和row2加入结果集
首先加载t1，然后从t1中取出第一条记录，之后加载t2表，与t2表中的记录逐个匹配，连接匹配的记录。
- Block Nested Loop Join(BNLJ)算法：
再介绍一种高级算法：BNLJ，块嵌套循环算法，可以看作对NLJ的优化。大致思想就是建立一个缓存区，一次从驱动表中取多条记录，然后扫描被驱动表，被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配，如果匹配则连接并加入结果集。缓冲区越大，驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。
影响性能的因素
- 1.内循环的次数：现在考虑这么一个场景，当t1有100条记录，t2有10000条记录。那么，t1驱动t2与t2驱动t1，他们之间在效率上孰优孰劣？如果是单纯的分析指令执行次数，他们都是100*10000,但是考虑到加载表的次数呢。首先分析t1驱动t2，t1表加载1次，t2表需要加载100次。然后分析t2驱动t1，t2表首先加载1次，但是t1表要加载10000次。所以，t1驱动t2的效率要优于t2驱动t1的效率。由此得出，小表驱动大表能够减少内循环的次数从而提高连接效率。
另外，如果使用Block Nested Loop Join算法的话，通过扩大一次缓存区的大小也能减小内循环的次数。由此又可得，设置合理的缓冲区大小能够提高连接效率 - 2.快速匹配：扫描被驱动表寻找合适的记录可以看做一个查询操作，如何提高查询的效率呢？建索引啊！由此还可得出，在被驱动表建立索引能够提高连接效率
- 3.排序：假设t1表驱动t2表进行连接操作，连接条件是t1.id=t2.id，而且要求查询结果对id排序。现在有两种选择，方式一[…ORDER BY t1.id]，方式二[…ORDER BY t2.id]。如果我们使用方式一的话，可以先对t1进行排序然后执行表连接算法，如果我们使用方式二的话，只能在执行表连接算法后，对结果集进行排序（Using temporary），效率自然低下。由此最后可得出，优先选择驱动表的属性进行排序能够提高连接效率。
JOIN优化
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT
);
SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
CREATE TABLE t2 (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT
);
SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
| 100 |
+----------+
内连接谁当驱动表
实际业务场景中，左连接、右连接可以根据业务需求认定谁是驱动表，谁是被驱动表。但是内连接不同，根据嵌套循环算法的思想，t1内连接t2和t2内连接t1所得结果集是相同的。那么到底是谁连接谁呢？谨记一句话即可，小表驱动大表可以减小内循环的次数。下面用 STRAIGHT_JOIN强制左表连接右表。By the way，STRIGHT_JOIN比较冷门，在这里解释下，其作用相当于内连接，不过强制规定了左表驱动右边。
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.type=t2.type;
+----+-------+------+------+-------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+-------+----------------------------------------------------+
| 1 | t1 | ALL | NULL | 10000 | NULL |
| 1 | t2 | ALL | NULL | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+-------+----------------------------------------------------+
EXPLAIN SELECT * FROM t2 STRAIGHT_JOIN t1 ON t2.type=t1.type;
+----+-------+------+------+-------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+-------+----------------------------------------------------+
| 1 | t2 | ALL | NULL | 100 | NULL |
| 1 | t1 | ALL | NULL | 10000 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+-------+----------------------------------------------------+
对于第一条查询语句，t1是驱动表，其有10000条记录，内循环也就有10000次，这还得了？
对于第二条查询语句，t2是驱动表，其有100条记录，内循环100次，感觉不错，我喜欢!
这些SQL语句的执行时间也说明了，当内连接时，务必用小表驱动大表。
最佳实践：直接让MySQL去判断
但是，表的记录数是会变化的，有没有一劳永逸的写法？MySQL自带的Optimizer会优化内连接，优化策略就是上面讲的小表驱动大表。所以，以后写内连接不要纠结谁内连接谁了，直接让MySQL去判断吧。
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;
EXPLAIN SELECT * FROM t2 INNER JOIN t1 ON t1.type=t2.type;
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.type=t2.type;
EXPLAIN SELECT * FROM t2 JOIN t1 ON t1.type=t2.type;
EXPLAIN SELECT * FROM t1,t2 WHERE t1.type=t2.type;
EXPLAIN SELECT * FROM t2,t1 WHERE t1.type=t2.type;
+----+-------+------+------+--------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+--------+----------------------------------------------------+
| 1 | t2 | ALL | NULL| 100 | NULL |
| 1 | t1 | ALL | NULL | 110428 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+--------+----------------------------------------------------+
上面6条内连接SQL，MySQL的Optimizer都会进行优化。
JOIN优化之快速匹配
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT
);
SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 110000 |
+----------+
CREATE TABLE t2 (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT
);
SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
| 100 |
+----------+
左连接
左连接中，左表是驱动表，右表是被驱动表。想要快速查找被驱动表中匹配的记录，所以我们可以在右表建索引，从而提高连接性能。
-- 首先两个表都没建索引
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.type=t2.type;
+----+-------+------+------+--------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+--------+----------------------------------------------------+
| 1 | t1 | ALL | NULL | 110428 | NULL |
| 1 | t2 | ALL | NULL | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+--------+----------------------------------------------------+
-- 尝试在左表建立索引，改进不大
CREATE INDEX idx_type ON t1(type);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.type=t2.type;
+----+-------+-------+----------+--------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+-------+----------+--------+----------------------------------------------------+
| 1 | t1 | index | idx_type | 110428 | Using index |
| 1 | t2 | ALL | NULL | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+-------+----------+--------+----------------------------------------------------+
-- 尝试在右表建立索引，效果拔群，Using index!!!
DROP INDEX idx_type ON t1;
CREATE INDEX idx_type ON t2(type);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.type=t2.type;
+----+-------+------+---------------+----------+--------+-------------+
| id | table | type | possible_keys | key | rows | Extra |
+----+-------+------+---------------+----------+--------+-------------+
| 1 | t1 | ALL | NULL | NULL | 110428 | NULL |
| 1 | t2 | ref | idx_type | idx_type | 1 | Using index |
+----+-------+------+---------------+----------+--------+-------------+
右连接
右连接中，右表是驱动表，左表是被驱动表，想要快速查找被驱动表中匹配的记录，所以我们可以在左表建索引，从而提高连接性能。
DROP INDEX idx_type ON t2;
-- 两个表都没有索引
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.type=t2.type;
+----+-------+------+------+--------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+--------+----------------------------------------------------+
| 1 | t2 | ALL | NULL | 100 | NULL |
| 1 | t1 | ALL | NULL | 110428 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+--------+----------------------------------------------------+
-- 在右边建立索引，改进不大
CREATE INDEX idx_type ON t2(type);
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.type=t2.type;
+----+-------+-------+---------------+----------+--------+----------------------------------------------------+
| id | table | type | possible_keys | key | rows | Extra |
+----+-------+-------+---------------+----------+--------+----------------------------------------------------+
| 1 | t2 | index | NULL | idx_type | 100 | Using index |
| 1 | t1 | ALL | NULL | NULL | 110428 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+-------+---------------+----------+--------+----------------------------------------------------+
-- 尝试在左边建立索引，效果拔群！
DROP INDEX idx_type ON t2;
CREATE INDEX idx_type ON t1(type);
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.type=t2.type;
+----+-------+------+---------------+--------------+------+-------------+
| id | table | type | possible_keys | ref | rows | Extra |
+----+-------+------+---------------+--------------+------+-------------+
| 1 | t2 | ALL | NULL | NULL | 100 | NULL |
| 1 | t1 | ref | idx_type | test.t2.type | 5 | Using index |
+----+-------+------+---------------+--------------+------+-------------+
内连接
我们知道，MySQL Optimizer会对内连接做优化，不管谁内连接谁，都是用小表驱动大表，所以如果要优化内连接，可以在大表上建立索引，以提高连接性能。
另外注意一点，在小表上建立索引时，MySQL Optimizer会认为用大表驱动小表效率更快，转而用大表驱动小表。
DROP INDEX idx_type ON t1;
-- 两个表都没有索引，t2驱动t1
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;
+----+-------+------+------+--------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+--------+----------------------------------------------------+
| 1 | t2 | ALL | NULL | 100 | NULL |
| 1 | t1 | ALL | NULL | 110428 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+--------+----------------------------------------------------+
-- 在t2表上建立索引，MySQL的Optimizer发现后，用大表驱动了小表
CREATE INDEX idx_type ON t2(type);
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;
+----+-------+------+----------+--------+-------------+
| id | table | type | key | rows | Extra |
+----+-------+------+----------+--------+-------------+
| 1 | t1 | ALL | NULL | 110428 | Using where |
| 1 | t2 | ref | idx_type | 1 | Using index |
+----+-------+------+----------+--------+-------------+
-- 在t1表上建立索引，再加上t1是大表，符合“小表驱动大表”的原则，性能比上面的语句要好
DROP INDEX idx_type ON t2;
CREATE INDEX idx_type ON t1(type);
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;
+----+-------+------+---------------+----------+------+-------------+
| id | table | type | possible_keys | key | rows | Extra |
+----+-------+------+---------------+----------+------+-------------+
| 1 | t2 | ALL | NULL | NULL | 100 | Using where |
| 1 | t1 | ref | idx_type | idx_type | 5 | Using index |
+----+-------+------+---------------+----------+------+-------------+