• 选择操作
  
• 全表扫描
• 索引扫描


• 排序
  
• 基于索引的排序
• 快速排序
• 外部排序
  
• 在数据不能全部放在内存的情况下

• 连接运算
  
• 镶套循环连接
  
• 小表在前
• 大表在后


• 索引镶套循环连接
  
• 归并连接
• hash连接

• 分组
  
• 去重

以下原理mysql执行器有的有, 有的没有

• 分析编译
  
• 语法分析转化为语法分析树
  
• 预处理器
  
• 虚视图转换为语法树


• 语法分析树->逻辑查询计划
  
• 转换为关系代数
  
• 查询重写
  
• 逻辑计划的改进

• 物理计划生成
  

• 查询重用技术
  
• 查询结果的重用--缓存命中
• 查询计划的重用


• 查询重写技术
  
• 代数优化
• 目标
  
• 把查询转换为等价的,效率更高的形式
• 把查询重写为等价,简单且不受表顺序限制的形式
  
• 为物理优化提供更多的选择

• 优化思路:
  
• 将过程性查询转换为描述性查询(如视图重写)
• 复杂查询(镶套子查询,外连接,嵌套连接)转为多表连接
• 效率低的谓语转换为等价的效率高的谓词
• 利用等式不等式的特性,简化where,having,on条件等


• 查询算法优化
  
• 物理优化
• 目标
  
• 对查询树,求解制定的高效执行计划

• 查询树
  
• 树叶是对象
• 父节点是连接操作符


• 求解策略
  
• 基于规则优化
• 基于代价优化

• 并行查询优化
  
• 找到具有最小相应时间的查询执行计划


• 分布式查询优化
  
• 以减少传输次数与数据量为目标

(代数优化/等价转换)

• 子查询上拉 (转为连接)
  
• 子查询比较耗时
• 子查询不同位置对优化的影响
  
• 目标列
  
• 只有一行的查询叫标量子查询


• from子局
  
• 子查询作为外查询的查询表
• 相关子查询不能出现在from子局中
• 非相关子查询可以把子查询拉到父层

• where子句
  
• 条件化简
• 条件下拉


• 子查询可能执行多次,连接不用执行多次
  
• 优化器可以根据统计信息来选择不同的连接方法和连接方式
• 子查询中的条件可以转到父查询中,优化器可以对这些条件下推,提高效率

• 投影与选择 条件相关的
  
• 投影和选择条件下拉,更早的做选择
• 谓语优化
  
• 关系模型转化,更好的利用索引


• 条件化简
  

• 连接消除
  
• 外连接转为内连接
• 嵌套连接消除

• 子查询优化的方法
  
• 子查询合并
  
• 关机关系计算
• 根据关系计算化简
• 多个子查询合并为一个


• 子查询展开
  
• 把某类子查询重写为等价的多表连接操作
• SPJ格式中,子查询上拉到上层,方便做多表连接

• in类型
  
• any all some
• exists


• 等价谓语重写
  
• 思想:等价转换sql语句,使之更好的利用索引
• like转换为 大于和小于操作
• between and 转化为大于小于操作
• in 转换为or

• 外连接/嵌套连接消除
  
• 意义
  
• 外连接操作比内连接操作容易
• 优化器选择连接顺序时,可以更加灵活


• 满足外连接可以转内连接的条件: 外连接比内连接多的部分为空
  

• 条件化简
  
• 少用null
• 少用不等于
• mysql支持也不多


• 对非SPJ查询的支持
  
• groupby聚集函数利用索引优化
  
• count
• min/max
• sum
• avg

• orderby
  
• distict优化

• 选择最优的单表扫描方式
• 选择更优的两表连接方式
• 选择更优的多表连接方式
• 查询代价估计
  
• 总代价
  
• IO代价
• CPU代价

• 常用的算法
  
• 顺序扫描
  
• 适用于
  
• 无索引可用
• 访问表中大部分数据
• 表的数据量很小


• 顺序IO更快
  

• 索引扫描
  
• 适用于
  
• 适用于选择率低的( 0.1 ),数据花费的IO时间会少
• 总体花费会小


• 只读索引扫描
  
• mysql中叫做覆盖索引
• 索引中的数据就能满足要求

• 组合多个索引扫描
  
• 行扫描
• 并行表扫描
• 并行索引扫描

• 嵌套循环连接
  
• 特点
  
• 支持索引
• 前后表有顺序
• 一趟


• 执行原理
  
• 使用场景
  
• 适用于:内连接 外连接等
• 适用于外层循环小,内层循环条件列有序
  
• 有索引
• 索引加在被驱动表上

• 排序归并连接算法
  
• 执行原理
  
• 要求内外表都有序
  
• 若连接列有序则可以用索引列排序


• hash连接
  
• 简单hash连接
  
• 选一个小表做hash驱动表
• 用另一个表相关的列去匹配

• 只适用于等值连接
  


• 对比
  

• 多表连接顺序
• 多表连接搜索空间

• 使用索引的原则
  
• 索引列作为条件出现在where,having.on子句中
• 索引列是被连接的表(内表)对象的列存在于连接条件中
• 在索引列上 排序,min max
• 联合索引和多个索引
  
• 联合索引单拿前面的可以使用


• groupBy会用索引吗?
  

• 索引类型
  
• B-Tree
• hash索引

• 库表结构/存储引擎
• 索引优化
• 查询语句优化
• 缓存层面优化
• 分布式数据库优化

• mysql的话考虑不同的存储引擎
  
• 不同引擎存储方式不同
• 索引方式也不同


• 范式和反范式权衡
  
• 数据类型选择
  
• 通常更小的更好
  
• 整型和浮点数
• 单浮点,双浮点
• short int long

• 简单就好
  
• 存储时间尽量用内建类型而不是字符
• 整型比字符代价更低
• 枚举代替字符串
  
• 字符串在表中换成整数
• 映射存在表中
• 排序会出问题,按照表中数字排序
• 使用要慎重!


• 避免空值
  
• 索引不容易命中

• 字符串
  
• varchar
  
• 变长字符串
• 使用额外的字节记录字符串长度
• 节省存储空间
• 更新时麻烦
  
• myisam把行拆成不同片段存储
• innodb分裂页来使行可以放入页中


• char
  
• 定长字符串
• 适合密码md5值之类的
• 不容易产生碎片

• 索引选择问题
  
• 是否使用索引
  
• 利
• 弊


• 索引类型选择
  
• 多列索引与多个单列索引
  
• 组合索引效率更高
• 组合索引被更新的几率更大

• 高级
  
• 表连接使用索引
  
• 只使用第二个表上的
• 加入排序使之能够利用索引


• 索引上的运算会使索引失效
  
• 索引上的不等于等操作会使索引失效

• 思想
  
• 确定性能瓶颈
• 明确优化目标
• 避免多余数据
  
• 多余行
• 多余列


• 更好的利用索引
  
• 排序中
• 条件选择时
  
• 仅仅使用最有效的过滤条件
• 选择条件的次序
• 避免空值
• 避免在条件上做运算
• 避免使用不等于操作符.

• 表连接时
  
• 聚合时怎么做


• 处理好子查询和join
  
• 小结果集驱动大结果集
  
• 减少镶套循环中的次数
• 减少IO总量和CPU运算次数

• 复杂查询改为简单查询
  
• 减少锁的占用
• 提高并发


• 总之:要明白查询优化器做了啥,然后做到查询优化器没做到的,纠正查询优化器做错的!
  

• 语句优化
  
• 子查询优化

https://www.cnblogs.com/wangning528/p/6388538.html
- 关联查询代替子查询 - 尽可能只用关联查询代替 - 但不一定会更快,需要测试 - mysql查询优化器做了什么? 又没做什么? - 合理使用EXISTS,NOT EXISTS子句 - 能够用BETWEEN的就不要用IN - 关联子查询

• 索引相关
  
• 强制使用索引
  
• 如果你认为你制定的索引方案比查询优化器好


• 限定使用索引的范围
  

• 表连接
  
• 强制连接顺序
• 如何更好的利用索引


• 临时表提升性能
  
• 结果集中数据较多时
• 把中间结果写到临时表中
• 尽快释放表锁

• orderby/GroupBy,Distinct优化
  

• 高可用应用中会有多级缓存
• 上述优化已到瓶颈时,考虑加入缓存层
• mysql缓存
  
• 计数表与汇总表
  
• 允许少量脏数据的情况下
• 空间换时间