疑问
表: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 索引只有在非驱动表上面才能体现作用