联接/集合

缺乏、安全感 2022-03-31 08:38 112阅读 0赞

性能监控：set @@profiling=1;  
show profiles;  
show profile for query 2;  
show profile block io,cpu for query 2;

cross只应用笛卡儿积/inner/outer join（只有left outer 和right outer ，无full outer，可省略outer ）  
select …from a,b where a.x=b.x 就是inner join  
inner join 条件指定在on和where没有区别，可以没有on（变成cross join）  
on [a.id=b.id][a.id_b.id] 可以用using(id)简化  
outer join 必须有on  
natural join 等同于inner join+using，将两个表中相同名称列的进行匹配，不用再on了  
join实际不用笛卡尔，用index处理的话(1 遍历左表，右表索引匹配 2 遍历右表，左表索引匹配)，一般optimizer会选择更快的方 式，但是使用straight\_join 就是强制遍历左表，所以这个很少用  
self join (自己join自己，注意要有别名，这是一种使用方式)  
semi join 只从一张表取列  
多表join 如果全是inner join可打乱顺序（optimizer很可能这样做），使用straight\_join强制不打乱  
算法：  
simple nested-loops join 内部表有索引，走索引，（inner join可调换内部表和外部表，哪张表有索引，哪张表作为内部表，都有索引，记录多的作为内部表）（但是outer join 不可调换顺序，需保证内部表有索引）  
block nested-loops join 没索引怎么办，join buffer（举例：一次将10条外部表数据(只放需要匹配的列数据)放入join buffer(join\_bufffer\_size)，内部表遍历一次完成匹配）  
batched key access join: 先join buffer, 然后辅助索引找到内部表对应的多个(index column-primary key),放入mrr（按primary key 排序），最后获取内部表记录。结合了simple/block和mrr  
classic hash join: 1.build：先join buffer，但是产生hashtable 2.probe：内部表scan进行匹配  
没索引：bnl-ch 有索引：snl-bka

union = union distinct：去重，创建临时表，表加unique key,即此unique key包含所有列  
union all：不去重  
列数必须相同（数据类型不同隐式转换，别名不同按第一个来）  
mysql 不支持except和intersect，但可自己模拟

[a.id_b.id]: http://a.id=b.id