性能监控: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 可以用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,但可自己模拟