mysql 查询优化 ~ 多表查询基础知识
一 什么是驱动表
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
2）未指定联接条件时，行数少的表为[驱动表]（Important!）。
表现 explain第一行出现的就是驱动表
二 多表查询的分类
1 多表join查询
2 多表join+子查询
三 驱动表的分类
1 单表
单表执行顺序有2种情况
1 单表先执行条件过滤,结算出结果再进行关联join
2 单表先进行关联join,再结果进行条件过滤
可以根据explain驱动表的索引选择进行判断是根据哪种情况执行
1 选择条件索引,先过滤
2 选择连接字段索引,先关联
2 派生表
explain->type-><derived>名称 即为派生表
eg SELECT column_list FROM (SELECT column_list FROM table_1) derived_table_nameWHERE derived_table_name.column > 1
注意 执行的是 select from之后 where和join之前的语句
执行顺序
1 执行子查询的内容
2 把子查询的结果写到临时表中
3 回读,应用上层SELECT的WHERE条件
四 多表查询几点注意
1 对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序
2 一旦连接字段没有应用索引,就会出现BNL字样,进行系统优化
五 多表查询优化
1 永远用小结果集驱动大结果集（Important!）
2 多表join的连接字段一定都要有索引
3 多表join的排序一定选择驱动表
4 注意出现派生表的情况,效率会很低(子查询)
5 多表查询善用 STRAIGHT_JOIN,可以改变驱动表
6 采用join on而并非where a.id=b.id这种方式改写sql
六 通用优化原则
增加索引->改写sql(通常将子查询改写成链接查询)
七 基础
1 为什么会选择驱动表数量小的表
1 驱动表 扫描为ALL 被驱动表拥有索引 那么时间复杂度为 N*2*log2M。
2 驱动表 扫描为index 被驱动表拥有索引 那么时间复杂度为 2*logN+N*2*log2M。
我们通过这个计算方式可以发现,N的变化率增幅远远高于M的变化率,所以需要N尽可能的减少
2 出现BNL会有什么后果
一旦被驱动表没有选择索引,就会选择BNL,将驱动表的bf放置在join_buffer中,如果结果集太大,那么会先处理一批,然后清空join_buffer.再进行填充,这样做的坏处就是整个LRU列表的old不断被刷新,挤占yang区,影响整体的命中率(请注意,分割多个block,会读取多次数据页,导致数据页被认为是活跃数据,但是实际对开发者来说只有一次)
3 mysql 5.6+ 对于索引查询的优化
参数 mrr_cost_based = OFF ,因为现阶段的mysql优化器大多数情况下默认不选择MRR,所以需要关闭此参数,
传统过程: 传统情况下,表根据非主键索引访问主键,然后取得列数据，过程没有错,但是有个问题,辅助索引对应的主键索引可能是无序的,那么访问底层数据就并非顺序读.
优化过程: 1 MRR 根据辅助索引查询的主键不先进行查询,先把主键索引一起放置到缓存中,进行排序,然后再进行统一查找,将随机读变为顺序读. 缓存参数为 read_rnd_buffer_length 控制大小
2 BKA 根据驱动表查询的数据放入内存中,当内存满时,再进行与被驱动表的匹配,而之前是一条一条进行的匹配,缓存参数为join_buffer_size,这个参数同样也优化着BNL算法
开启命令 set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
八 什么时候会用到临时表
1、UNION查询；
2、ORDER BY和GROUP BY的子句不一样时；
3、表连接中，ORDER BY的列不是驱动表中的；
4、DISTINCT查询并且加上ORDER BY时；
5、FROM中的子查询；
九 多表联合 索引失效场景
1 join连接 两张表的字符集分别为utf8和 utf8mb4,或者指定了特殊字符集的列
2 join连接使得单表查询的联合索引在多表join中失效
