MySql优化- join匹配原理(一)
疑问
表: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为驱动表
情况4
因为left join相当于强制要求了lb为主表 虽然lh.SALES_BILL_NO有索引 但是join索引主要是给非驱动表用的 所以出现以上情况
join优化原则
尽量减少驱动表条数 非驱动表关联条件建立索引
虽然大部分会经过mysql优化器自动优化,复杂sql最好通过执行计划查看一下 是否有性能瓶颈
注意不要通过left join 影响sql优化器 将大表作为驱动表
记住join 索引只有在非驱动表上面才能体现作用
针对LEFT JOIN，ON和Where跟条件的区别
on 后面跟上筛选表达式
与理想一样，user表为驱动表 坐标关联右表没有的数据显示null
执行计划
查询结果
on条件放到where
变成了类型 left join后根据结果筛选,所以优化后驱动表发生了变更 排除了无法关联的数据
将on条件放到where执行计划
查询结果
查询结果2 带上无法关联的id
总结
我的理解 放在on后面如果是非驱动表,我觉得是在关联的时候匹配条件, 放在where后面 是关联后的结果筛选 (当然sql优化引擎 有时会选择where 后的条件到表连接时使用 而不是最终结果集筛选)
