MySql join匹配原理

阅读量：5024 次

发布时间：2019-06-12

本文共 4116 字，大约阅读时间需要 13 分钟。

疑问

表:sl_sales_bill_head 订单抬头表数据行:8474

表:sl_sales_bill 订单明细数据行:8839

字段:SALES_BILL_NO 订单号

情况1

没有任何索引 sql语句

EXPLAIN select * from sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO

lh为主表 lb为子表

改一下sql语句

EXPLAIN select * from  sl_sales_bill_head_copy1 lh join sl_sales_bill_copy1 lb on lh.SALES_BILL_NO = lb.SALES_BILL_NO

疑问:为什么sql语句无论主表是哪个 lh都先执行

情况2

sl_sales_bill_head_copy1 的SALES_BILL_NO为主键索引

ALTER TABLE `sl_sales_bill_head_copy1` ADD PRIMARY KEY (`SALES_BILL_NO`) ;

sql语句1:

EXPLAIN select * from  sl_sales_bill_head_copy1 lh join sl_sales_bill_copy1 lb on lh.SALES_BILL_NO = lb.SALES_BILL_NO

sql语句2:

EXPLAIN select * from   sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO

疑问:为什么无论怎么通过sql语句改变主表始终是lb先执行

情况3

lh.SALES_BILL_NO创建索引

ALTER TABLE `sl_sales_bill_head_copy1` ADD PRIMARY KEY (`SALES_BILL_NO`) ;

EXPLAIN select * from   sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO where lb.SALES_BILL_NO='HP20190410000099'

EXPLAIN select * from   sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO where lh.SALES_BILL_NO='HP20190410000099'

都会正常走索引同时也是lh先执行

如果改为lb的其他字段

EXPLAIN select * from   sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO where lb.id='0001c3fd44454a65a4122b259283f979'

无索引情况

ID有索引情况

变成了lb先执行

情况4

sl_sales_bill_head_copy1 的SALES_BILL_NO为主键索引

ALTER TABLE `sl_sales_bill_head_copy1` ADD PRIMARY KEY (`SALES_BILL_NO`) ;

SQL语句

EXPLAIN select * from   sl_sales_bill_copy1 lb  join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO

sql语句

EXPLAIN select * from   sl_sales_bill_copy1 lb  left join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO

疑问:为什么left join没有走索引了

Join匹配原理

说明

mysql只支持一种算法Nested-Loop Join（嵌套循环链接），不像其他商业数据库可以支持哈希链接和合并连接，不过MySQL的Nested-Loop Join（嵌套循环链接）

Simple Nested-Loop

图片来源:InsideMySQ

R表为驱动表每扫描一行去S表找匹配的数据这种算法是最耗时的总扫描次数为驱动表行数*非驱动表行数

比如R表有200表数据 S表有100条总扫描次数为200*100 可以看出这种算法效率最低

Index Nested-Loop Join

R表为驱动表每扫描一行根据匹配条件通过索引去S表找这种算法需要非驱动表有索引一般我们on r.sid=s.id 索引时给非驱动表用的

比较高效

Block Nested-Loop Join

mysql 5.5对Simple Nested-Loop的优化先扫描驱动表一定量(根据join_buffer_size来定) 放到join_buffer 然后遍历非驱动表非驱动表每次匹配join_buffer里面的数据减少扫描次数

比如我们的join_buffer最多只能存放r表3条数据遍历R表每遍历3条将数据放到join_buffer然后然后再去遍历一次s表每s表遍历一行跟join_buffer里面的数据进行匹配遍历完成释放join_buffer 重复上面操作

在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K

解决疑惑

情况1

lh数据条数8274 lb数据条数8721

疑问:为什么驱动表都是lh表

解答:mysqlsql优化器默认会将小表作为驱动表

好处:

Block Nested-Loop Join算法

比如lh有4条数据 lb数据条数6 join_buffer是只能存放2条数据

计算规则为(驱动表遍历次数*驱动表行数)+(非驱动表遍历次数*非驱动表行数)=总遍历次数

我们将lb作为驱动表扫描行数为(1*6)+(3*4)=18 总扫描行数

我们将lh作为驱动表 (1*4)+(2*6)=16 总扫描行数

可以发现小表作为驱动表扫描的行数更低

情况2:

lh数据条数8274 lb数据条数8721

疑问:为什么lh.SALES_BILL_NO为主键索引驱动表始终是lb

解答:mysql优化器还是以小表为原则如果大表关联关系有索引而小表没有则以有索引的表为驱动表

好处:

这里使用的Index Nested-Loop Join算法

如果使用lh驱动表首先会遍历8274次每次去lb去找因为关联关系lb.SALES_BILL_NO没有做索引所以非驱动表lb也会全表扫描总扫描次数就变成8274*8721

如果使用lb为驱动表会遍历lb表每次通过SALES_BILL_NO去非驱动表lh找因为lh做了索引所以通过索引扫描一次就可以找到数据总扫描次数:8274*1

情况3

lh数据条数8274 lb数据条数8721

疑问:为什么就lh.SALES_BILL_NO有主键索引无论搜索条件是lb.SALES_BILL_NO还是lh.SALES_BILL_NO 都是lh先执行

解答:

因为on lb.SALES_BILL_NO=lh.SALES_BILL_NO where lb.SALES_BILL_NO='HP20190410000099'

这个时候虽然lb.SALES_BILL_NO没有索引但是关联查找为lb.SALES_BILL_NO=lh.SALES_BILL_NO and lb.SALES_BILL_NO='HP20190410000099'

正常查找是lb全表扫描得到HP20190410000099然后去lh通过索引得到SALES_BILL_NO=lh.SALES_BILL_NO的数据

如果设置成lb.SALES_BILL_NO=lh.SALES_BILL_NO and lh.SALES_BILL_NO='HP20190410000099' 得到结果相同以小表为驱动表原则sql优化器会优化为类似这样的语句查找

EXPLAIN select * from   sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO where lb.id='0001c3fd44454a65a4122b259283f979'

lh.SALES_BILL_NO 有索引然后lb.id无论有无索引都是 lb为驱动表因为lb.id已经缩小了数据范围小表原则所以始终是lb为驱动表