07-SQL优化的基础

Charlie

关系代数核心:等价变换->可以自动对表达式做等价变换,做查询路径优化

1. SQL与查询优化器

group by, order by不属于关系代数,无法被查询优化器优化

  • 过程

    1. 优化器借助关系理论提供的语义无误的原始查询进行有效的等价变换
    2. 优化器根据数据库的实际情况对理论上等价的不同优化方案做出权衡
    3. 产生可能的最优查询执行方案
    4. 实际将一个SQL查询优化成更高效的方案
  • 类型

    1. RBO基于规则的优化器:给每个算子赋值一个权重,计算整个查询的总权重
    2. CBO基于成本的优化器:还要考虑结果集大小
      • 优化目标:中间结果集的数量最少

2. 基于规则的优化

  • 要点:结构匹配和替换
    1. 应用规则算法一般需要现在关系代数结构上匹配一部分局部结构
    2. 在根据结构的特点进行变换,替换

image.png

3. 基于成本的优化

要点:执行计划的成本估算

遍历不同关系代数表示,枚举功能,得到最优方案。

3.1. 优化主要方向-连接

3.1.1. 嵌套循环连接(Nested-Loop Join)

image.png

image.png

1
2
3
t1.m1 > 1
t1.m1 = t2.m2
t2.n2 < 'd'
  • 左外连接

    • T1 T2左外连接,T1记录都保留,T2如果没有相等的,结果集中是空值
  • 驱动表只访问一次,但被驱动表却可能被多次访问,访问次数取决于 对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接 ( Nested-Loop Join )

image.png

3.1.2. 基于索引的连接优化

基于索引的连接优化:通过索引读取有限条的T2数据与T1连接

  1. 被驱动表有索引的情况下,被驱动表被驱动表筛选的数据进行多次基于索引的查询
  2. 如果有多个条件,需要多个索引,优化器选择某个索引进行执行
  3. 连接查询和过滤条件一般只涉及被驱动表的部分列,所以真实工作环境不要使用*作为查询列表
    image.png

3.1.3. 基于块的连接优化(Block Nested-Loop Join)

image.png

  1. 尽量减少访问被驱动表的次数(驱动表的记录不会都放入 join buffer,只会将部分列放入)
  2. join buffer 足够大,就可以一次访问被驱动表完成连接
  3. join buffer 一般 256KB(相比来看,索引仍然是最好的选择)

3.2. 连接小结

  1. 本质上,连接就是把各个表中的路径都取出来依次进行匹配,并把匹配的组合返回
  2. 外连接和内连接的本质都是确定驱动表
  3. 嵌套循环连接算法:驱动表连接一次,但被驱动表可能会访问多次,访问次数取决于被驱动表执行单表查询后结果集中有多少记录
  4. 被驱动表会被连接多次,可以用索引加速
  5. 被驱动表很大,多次访问会导致更多的磁盘 I/O,基于块的嵌套循环算法来缓解

3.3. 成本计算

I/O成本:物理读写
CPU成本:比较,逻辑读写

3.4. 基于成本的优化步骤

  1. 根据搜索条件,找出所有可能使用的索引
  2. 计算全表扫描的代价(row, data_length)
  3. 计算使用不同索引的代价
  4. 对比各个查询优化方案的代价,找到成本最低的访问方式

好条件先做

SQL->表达式->树->不断调整树的节点,找到最优执行路径->得到执行计划:可执行的代码

MySQL 是怎样运行的 - 基于成本的优化 - 掘金

3.4.1. 全表扫描成本

  • 查询成本=I/O成本+CPU成本
    1. I/O成本:聚簇索引占用的页面数,记录从磁盘到内存
    2. CPU成本:该表中的记录行数,检测记录是否满足条件,对结果集排序

row:记录行数
data_length:占用字节数 = 页面数量 X 页面大小

物理读取一个页面默认成本是 1.0
逻辑读取和检测条件默认为 0.2

3.4.2. 使用不同索引执行查询的代价

  • 二级索引 + 回表
    1. 估算范围区间数量:查询优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的
    2. 需要回表的数量:优化器需要计算二级索引的某个范围区间到底包含多少条记录,可能是估算出来的
  • I/O成本:范围区间的数量 (读取一个页的成本)+ 回表操作(二级索引记录条数)
  • CPU成本:计算二级索引记录条数的成本(二级索引记录条数,微调) + 读取并检测回表后聚簇索引记录的成本(二级索引记录条数)

3.4.3. 两表连接的成本分析

  • 连接查询的总成本 = 单次访问驱动表的成本 + 驱动表扇出 * 单次被驱动表的成本
  • 不同驱动表的成本不一样,寻找成本最低的那个
  • 扇出值:根据条件占比计算
  • 多表连接类似,,只是可选路径是 N 的阶乘

4. SQL的执行顺序

  1. SQL
  2. 语法语义检查
  3. 解析:最消耗资源的步骤,选择最优执行路径,
  4. 执行计划
  5. 执行引擎
  6. 存储引擎
  7. 数据库

5. 软解析:绑定变量

  • 绑定变量:把所有常量变成变量
    • 数据库一般不默认启用
    • 但ORM框架一般默认绑定变量,处于安全性考虑
  • 好处:
    1. 相似查询使用变量,当成一个查询。实现软解析。
    2. 避免sql串的拼接,防止注入
  • 缺点:
    1. 绑定变量之后,无法通过范围查询的范围估计扇出数量,进而估计最优查询路径。只能使用平均值估算。

5.1. 防止SQL注入攻击

不把SQL串组合起来之后直接放到查询优化器里,而是先保留变量生成执行计划后再代入变量

1
2
3
SELECT
FROM
WHERE A=a

变量a作为执行计划的参数传入,而不是直接拼到SQL串里

5.2. 把相似查询当成同一个查询,只是参数不同

  • 如果查询与数据值有关,绑定变量导致无法优化,如WHERE 3 < x < 5

硬解析:相似查询当成多条查询,单独执行,数据值固定。

1
2
3
4
WHERE x = 5
WHERE x = 3
//把所有常量编程变量
WHERE x = A//绑定变量

6. 优化器只能对关系领域进行优化

order by,group by等不属于关系代数的操作无法优化,是脱离关系代数独立执行的

例子:查询不适经理的员工中,工资最高的五个人?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
%% rownum是ORACLE方言 %%

%% wrong %%
%% order by在前面的查询全部完成后再排序,就是只查询前5%%
select empname, salary
from employees
where status != 'EXEC'
and rownum <= 5
order by salary desc

%% right %%
%% 选择出符合条件并排序后,再选择前5%%
select *
from (
select empname, salary
from employees
where status != 'EXEC'
order by salary desc
)
where rownum <= 5
  • 有效范围
    1. 优化器需要借助数据库中找到的信息
    2. 能够数据意义上等价变化
    3. 优化器考虑整体响应时间
    4. 优化器改善的是独立的查询

7. 优化器自动条件化解

  1. 移除不必要的括号
    1. select from (t1, (t2, t3))
    2. select from t1, t2, t3
  2. 常量传递
  3. 移除没用的条件
  4. 表达式计算:只会对右侧修改,不会修改左侧
    1. a=5+1
    2. a=6
    3. 注意:-a<8, a-1=6优化器不会优化
      • 语法语义检查的时候,无法预测左侧是不是函数索引f(x)
  5. having子句和where子句合并
    1. 没有聚合函数,group by子句时
  6. 常量表检查,当成常量使用
    1. 查询表中,只有一条或者没有记录
    2. 使用主键或唯一的二级索引键的等值查询

8. 使用SQL需要考虑的因素

  1. 获得结果集所需访问的数据量(表大小)
  2. 定义结果集所需的查询条件
    • 过滤条件:where子句
  3. 结果集的大小
  4. 获得结果集所涉及表的数量
    • 表的数量,from子句,不要超过5-8张表
  5. 同时修改这些数据用户的多少

9. 子查询

出现在某个查询语句的某个位置中的查询称为子查询

9.1. 嵌套子查询

  • exist:
    1. 关联嵌套子查询
    2. 内层查询绑定变量依赖外层查询,外层每有一个符合条件内查询都执行一次
    3. 外部条件好时,可以做到整个查询都在索引上完成,exist查询效率更高
  • in:
    1. 非关联嵌套子查询
    2. 内层查询不再依赖外层查询,只执行一次
    3. 内部结果集和外部结果集最差情况下要笛卡尔积。优化器要对内连接做优化。

9.2. 按返回结果区分子查询

  1. 标量子查询

9.3. 优化

  • 选择分辨率最大的条件
  • 关键时In子查询的优化
    1. 不相关子查询
    2. 如果子查询结果集记录少,内外层分别优化
    3. 子查询结果记录多,内存放不下。临时表,去重,构架哈希表。
    4. 大到内存放不下了临时表,磁盘物化表,构建B+树索引

10. SQL优化的其他问题

  • 标题: 07-SQL优化的基础
  • 作者: Charlie
  • 创建于 : 2024-05-13 14:05:00
  • 更新于 : 2024-07-05 12:55:04
  • 链接: https://chillcharlie357.github.io/posts/b68ed7c/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
评论