sql优化表连接join⽅式
sql优化核⼼是数据库中解析器+优化器的⼯作，我觉得主要有以下⼏个⼤⽅⾯：
1>扫表的⽅法（索引⾮索引、主键⾮主键、书签查、索引下推）
2>关联表的⽅法（三种），关键是内存如何利⽤
3>处理排序聚合的⽅法，如何利⽤内存
即少扫磁盘多⽤内存
--=====2 表关联⽅式
-----0 概述
类别 Nested Loop Hash Join Merge Join
使⽤条件任何条件等值连接（=）等值或⾮等值连接(>，<，=，>=，<=)，‘<>’除外
相关资源 CPU、磁盘I/O 内存、临时空间内存、临时空间
Nested Loop 优点: 当有⾼选择性索引或进⾏限制性搜索时效率⽐较⾼，能够快速返回第⼀次的搜索结果。
缺点: 当索引丢失或查询条件限制不够时，效率低；当表纪录数多时，效率低
实现: 从⼀张表中读取数据，访问另⼀张表（通常是索引）来做匹配，nested loops适⽤的场合是当⼀个关联表⽐较⼩的时候，效率会更⾼Hash Join 优点: 当⽆索引或索引条件模糊时，Hash Join⽐Nested Loop有效。通常⽐Merge Join快。在数据仓库环境下，如果表的纪录数
多，效率⾼
缺点: 为建⽴哈希表，需要⼤量内存。第⼀次的结果返回较慢
实现: 将⼀个表（通常是⼩⼀点的那个表）做hash运算，将列数据存储到hash列表中，从另⼀个表中抽取记录，做hash运算，到hash 列表中到相应的值，做匹配。
Merge Join 优点: 当⽆索引或索引条件模糊时，Merge Join⽐Nested Loop有效。⾮等值连接时，Merge Join⽐Hash Join更有效
缺点: 所有的表都需要排序。它为最优化的吞吐量⽽设计，并且在结果没有全部到前不返回数据
实现: 先将关联表的关联列各⾃做排序，然后从各⾃的排序表中抽取数据，到另⼀个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多
通常来讲，能够使⽤merge join的地⽅，hash join都可以发挥更好的性能
选择什么连接类型有以下三要素:
1) 表⼤⼩
2) 连接列是否有索引
3) 连接列是否要排序
不同DBMS对表连接的⽀持:
1) SqlServer, Oracle⽀持以下三种连接
2) Mysql5.5前⽀持NestedLoop，之后⽀持对其的优化算法Block Nested-Loop
-----1 Nested Loop Join
驱动表(outer table)，另⼀个为inner table，驱动表中的每⼀⾏与inner表中的相应记录JOIN。类似⼀个嵌套的循环。适⽤于驱动表的记录集⽐较⼩（<10000）且inner表的连接列上要有Index。
注意：驱动表的记录集⼀定要⼩，inner表的连接列要有(UniqueIndex更好)索引。处理过程伪代码：
for oi in count(outer table ⾏数):do
for ii in count(inner table ⾏数):do --若inner连接列有主键索引，则不⽤循环inner表，也不需要回表，效率超⾼
lumn:do --若只是普通索引，还需要回表查相应数据(可能需要⼤量的随机IO)，可能会慢许多，但也⽐没索引强
Send To Client
fi
done
done
sql语句优化方式
-
-Block Nested-Loop Join(仅Mysql⽀持)
因为普通Nested-Loop⼀次只将⼀⾏传⼊内层循环, 所以outer table (的结果集)有多少⾏, 内存循环便要执⾏多少次.
在inner table的连接上有索引的情况下，其扫描成本为O(Rn)，若没有索引，则扫描成本为O(Rn*Sn)。如果inner table有很多记录，则Nested-Loops Join会扫描内部表很多次，执⾏效率会⾮常差。
BNL算法：将outer table结果集存⼊join buffer, 内层循环的每⼀⾏与整个Join buffer中的记录做⽐较，从⽽减少内层循环的次数。
举例，outer table结果集是100⾏，使⽤NLJ 需扫描内部表100次，如使⽤BNL，先把Outer Loop表每次读取的10⾏记录放到join buffer，然后在InnerLoop表中直接匹配这10⾏数据，
内存循环就可以⼀次与这10⾏进⾏⽐较, 这样只需要⽐较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数.
MySQL使⽤Join Buffer有以下要点:
当MySQL的 Join 有使⽤到 Block Nested-Loop Join，那么调⼤变量join_buffer_size 才是有意义的。⽽前⾯的 Index Nested-Loop Join如果仅使⽤索引进⾏Join，那么调⼤这个变量则毫⽆意义
a) 只有在join类型为all, index, range的时候才可以使⽤join buffer。
b) 能够被buffer的每⼀个join都会分配⼀个buffer, 也就是说⼀个query最终可能会使⽤多个join buffer。
c) 第⼀个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
d) 在join之前就会分配join buffer, 在query执⾏完毕即释放。
e) join buffer中只会保存参与join的列, 并⾮整个数据⾏。
f) 5.6版本及以后，优化器管理参数optimizer_switch中的block_nested_loop控制着BNL是否被⽤于优化器。默认条件下是开启，若果设置为off，优化器在选择 join⽅式的时候会选择NLJ算法。
-----2 Hash Join
将两表中较⼩的在内存中构造⼀个HASH表(只对连接列)，扫描另⼀个表，同样对JOIN KEY进⾏HASH后探测是否可以JOIN。适⽤于记录集⽐较⼤的情况。
需要注意的是：如果HASH表太⼤，⽆法⼀次构造在内存中，则分成若⼲个partition，写⼊磁盘的temporary segment，则会多⼀个写的代价，会降低效率
-----3 Sort Merge Join
通常情况下Hash Join的效果都⽐排序合并连接要好，然⽽如果⾏源已经被排过序，在执⾏排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。
可以使⽤USE_MERGE(table_name1 table_name2)来强制使⽤排序合并连接.
Sort Merge join ⽤在没有索引，并且数据已经排序的情况.
将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发⽣时，才会使⽤此种JOIN⽅式：
1.RBO模式且 HASH_JOIN_ENABLED=false
2.不等价关联(>,<,>=,<=,<>)
3.数据源已排序

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。