6 MySQL 中的连表是怎样实现的？为什么大厂不使用连表查询？

DBAjoinjoin

这是为什么呢？今天我们就来聊聊这个话题。

joinjoinjoinIndex Nested-Loop JoinSimple Nested-Loop JoinBlock Nested-Loop Join

为了方便测试，我创建如下数据表：

-- 创建数据表
CREATE TABLE test_joinv1(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    m INT(11) NOT NULL,
    n INT(11) NOT NULL,
    KEY `index_1` (`m`)
)ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;
-- 复制test_joinv2数据表
CREATE TABLE test_joinv2 LIKE test_joinv1;
-- 通过存储过程造部分数据给 test_joinv2
drop procedure create_data;
delimiter ;;
create procedure create_data()
begin 
    declare i int; 
    set i=1; 
    while(i<=10000)do 
        insert into test_joinv1 values(i, i, i); 
        set i=i+1; 
    end while;
end;;
delimiter ;
call create_data();
-- 复制部分 test_joinv1 的数据给 test_joinv2
insert into test_joinv2 (select * from test_joinv1 where id >= 1000 and id < 2000);

Index Nested-Loop Join

Index Nested-Loop JoinINLJ

SELECT * FROM test_joinv1 STRAIGHT_JOIN test_joinv2 ON (test_joinv1.m = test_joinv2.m)

joinjoinSTRAIGHT_JOINjoin

explaintest_joinv2

这种状态下该条 SQL 的执行流程如下：

test_joinv1mtest_joinv2test_joinv2test_joinv1

test_joinv110000mtest_joinv2test_joinv2test_joinv2100010000 + 1000 = 11000

INLJtest_joinv2

SELECT * FROM test_joinv2 STRAIGHT_JOIN test_joinv1 ON (test_joinv1.m = test_joinv2.m)

explaintest_joinv1

这时查询的步骤具体分析如下：

test_joinv2mtest_joinv1test_joinv1test_joinv2

test_joinv21000test_joinv1test_joinv110001000 + 1000 = 2000

通过对比，我们可以得出：当需要基于索引连表查询时，我们采用较为小的数据表（业内称之为：小表）作为驱动表，有利于提高查询的效率。

test_joinv110000test_joinv2testjoinv2100010000 + 1000 = 11000

join

下面，我们再来讨论一下在不使用索引的情况下，连表查询的性能又该是如何？

Simple Nested-Loop Join

Simple Nested-Loop JoinSNLJ

SELECT * FROM test_joinv1 STRAIGHT_JOIN test_joinv2 ON (test_joinv1.m = test_joinv2.n)

简单嵌套循环链接其实就是简单粗暴地进行链接查询，具体分析如下。

SQLtest_joinv110000test_joinv1test_joinv210000 * 1000 = 1千万次

SNLJ

那么，在没有使用索引的情况下，MySQL 的连表查询情况到底该是如何呢？我们一起往下看。

Block Nested-Loop Join

Block Nested-Loop JoinBNLJ

SNLJSQLexplain

ExtraUsing join buffer (Block Nested Loop)SQLBNLJ

SQL

test_joinv1join_buffertest_joinv2join_buffertest_joinv1.m = test_joinv2.n

由此，我们可以得出的是：

test_joinv110000test_joinv21000test_joinv1.m = test_joinv2.njoin_buffer10000 * 1000 = 1千万次

join_buffertest_joinv1

下面我们就来分析这一过程：

test_joinv1join_buffertest_joinv2join_bufferjoin_buffer

join_bufferjoin_buffer_size

总结一下，就是：BNLJ 算法相比较 SNLJ 来说是以空间换时间。SNLJ 的判断发生在数据磁盘之上，而 BNLJ 是发生在内存之中，相比较而言，内存的速度要远快于磁盘。所以，BNLJ 算法的性能要高于 SNLJ 算法。

test_joinv1join_bufferSNLJjoin_buffer

joinjoin

Index Nested-Loop JoinBlock Nested-Loop Join

总结

MySQLjoin

INLJSNLJBNLJSNLJjoin_buffer

joinjoinjoin_buffer