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中的子查询;
最新文章
- Android Studio导入项目遇到的问题
- C语言 &#183; Interval &#183; 求矩阵元素和
- redis缓存
- 关于MFC文本框输入内容的获取 与 设置文本框的内容
- Paip.语义分析----情绪情感词汇表总结
- view保存为图片
- java多线程-Exchanger
- JavaScript显示输出
- poj2001 Shortest Prefixes (trie)
- MySQL提供的错误日志中的错误级别一共有3个分别为:
- Rewrite的QSA是什么意思?
- Project Euler problem 62
- cf A. Vasily the Bear and Triangle
- win7程序关闭后弹出 程序兼容性助手 这个程序可能安装不正确 如果此程序没有正确安装,处理方式
- 【ant项目构建学习点滴】--(3)打包及运行jar文件
- c# datagridview 设置某行不可见解决办法
- 来自后端的突袭? --浅尝最新开源的C# Web引擎 Blazor
- restTemplate.postForObject上传文件中文乱码(???.xls)
- vue 之 .sync 修饰符
- C# 哈希表HashTable的简单使用
热门文章
- error2019-01-17 宏STDOUT_FILENO
- 第一节,windows和ubuntu下深度学习theano环境搭建
- css 中 position属性
- 一名全栈设计师的Mac工具箱(设计,开发,效率)
- Redis之路
- YouCompleteMe/third_party/ycmd/third_party/cregex"; does not appear to contain CMakeLists.txt.
- IO流----操作文件的9种方法代码实现
- jspdf简单使用
- Date类、SimpleDateFormat类
- HTTP Method小记