第14章查询优化

在这里插入图片描述

前导知识

what

查询优化就是MySQl会将程序员编写的一些比较耗费性能的语句进行查询重写

条件化简

移除不必要的括号

常量传递

某个表达式是某个列和某个常量的等值匹配，会直接用常量替换列名

移除没用的条件

比如永远为true或false

表达式计算

表达式只包含常量的话，值会被计算出来
如果某个列在函数中或者以运算形式出现，优化器不会进行化简

having和where子句的合并

查询语句中没有sum、max这样的聚集函数以及group子句的话，优化器会将having和where子句合并
having子句用于分组后过滤，where用于分组前合并

常量表检测

what：使用主键等值匹配、使用唯一二级索引列等值匹配进行查询的表称为常量表
优化器会优先执行常量表查询，因为速度非常快

外连接消除

优化器会将右连接转换为左连接查询

空值拒绝

在外连接查询中，指定的where子句中包含被驱动表的列不为null值的条件（就是不允许查出来的记录中含有null值）
被驱动表的where子句符合空值拒绝的条件后，外连接和内连接可以相互转换

子查询优化

子查询简介

what
- 在一个查询语句中的某个位置可以出现另一个查询语句，这另一个查询就叫子查询
按出现位置分类
- 在select子句中
  - select (select m1 from t1 limit 1);
- 在from子句中
  - select m, n from (select …);
  - 将这种子查询的结果当做一个表，在from子句中的子查询称为派生表
- 在where或on子句中
  - select * from t1 where m1 in (select …);
按返回的结果集分类
- 标量子查询
  - 只返回一个单一值
  - select (select m1 from t1 limit 1);
- 行子查询
  - 返回一条记录，需要包含多个列
  - select * from t1 where (m1, n1) = (select m2, n2 from t2 limit 1);
    使用limit 1保证子查询的结果只有一条记录
- 列子查询
  - 返回一个列的数据，可能包含多条纪录
  - select * from t1 where m1 in (select m2 from t2);
- 表子查询
  - 子查询的结果既有多条纪录，又有多个列
  - select * from t1 where (m1, n1) = (select m2, n2 from t2);
按外层查询关系分类
- 不相关子查询
  - 子查询可以单独运行出结果，不依赖于外层查询的值
  - 上面都是例子
- 相关子查询
  - 子查询的执行需要依赖于外层查询的值
  - select * from t1 where m1 in (select m2 from t2 where n1 = n2);
子查询的注意事项
- 必须用小括号括起来
- select子句中的子查询必须是标量子查询
- 要想得到标量子查询/行子查询，应该使用limit 1
- 对于[not] in/any/som/all子查询来说，子查询不允许出现limit
- 子查询中不必使用order by：子查询相当于一个集合，集合没必要排序
- 子查询中不必使用distinct，因为集合也不需要去重
- 子查询中没有聚集函数以及having子句时，不必使用group by子句

子查询的执行方式

不优化
- 对于不相关子查询，先执行子查询，再将子查询的结果作为外层查询的参数
- 对于相关子查询，先从外层查询中取一条记录，取相关列进行子查询，如此循环
in子查询的优化
- 物化
  - what：将子查询的结果写入临时表中，该临时表就是物化表
  - 基于内存的物化表建立哈希索引，基于磁盘的物化表建立B+树索引
- 半连接
  - s1表和s2表半连接的意思是：对于s1表的某条肌理，只关心在s2表中是否存在与之匹配的记录，而不关心与多少条记录与之匹配
  - 这只是MySQL内部执行子查询的一种方式，不面向用户
  - 实现方式
    - table pullout（表上拉）
      - 查询列表只有主键或唯一索引列是，将子查询中的表上拉到外层查询的from子句中
    - 重复值消除
      - 用临时表消除半连接结果集的重复值
    - 松散扫描
    - 半连接物化
    - 首次匹配