DBMS/数据库（五）查询处理

xzh
446 阅读15分钟

查询处理

查询处理简述

什么是查询处理

查询处理就是DBMS处理用户发起的查询SQL的过程。

查询处理的流程

发起和接受查询
  • 用户发起SQL查询语句；
  • DBMS接收到用户发起的请求；
语法分析与检查
  • SQL是一个便于用户使用的语言，但对于DBMS来说，SQL需要转换为程序语言，因此在收到SQL时，DBMS需要解析SQL为语法树；
  • DBMS还需要对语法树进行正确性检查，如SQL是否有效等；
  • 关系型DBMS一般遵循SQL92/03等标准；非关系型有自己的一套标准；
查询优化
  • 同一个SQL，可能会生成多种不同的查询计划，例如顺序查找所有的记录还是使用索引进行查询。两者都可以，但查询优化器需要计算多个计划的代价并从其中选择一个最优的计划；
  • 计划之间的抉择涉及到索引、排序、合并等一些操作的成本计算；
执行SQL计划
  • 在查询优化器选择好最优的查询计划后，SQL执行引擎执行此计划。
返回结果集
  • 返回所有匹配的记录；
SQL 执行计划是什么

SQL必须转化为DBMS的执行函数，这个执行函数就是执行计划。

一个SQL可能会有多种不同的DBMS执行函数，即一个SQL可能有多种不同的执行计划。

查询代价是什么

执行查询计划所需要的一些成本就是查询代价。

通常DBMS需要考虑以下两种代价：

  • 磁盘 IO 代价；DBMS的核心就是数据的读写，对于非易失性存储来说，数据往往要持久化，而数据的持久化以及从存储介质中获取数据将会使得性能降低。因为内存的性能和持久化设备的性能差距太大。因此优秀的DBMS永远是在努力的避免磁盘的IO开销。
  • CPU 代价；CPU 在DBMS中的影响并不很高（CPU的处理速度远远大于内存和持久化设备），因此影响通常体现在涉及到算法执行等操作中，如排序、合并、数据计算等。

查询选择（查询运算）

对于一个查询来说，查询计划即可以是使用全表扫描又可以使用索引进行查询，而使用哪种计划，需要查询优化器进行成本计算并选择。

全表扫描

不使用索引，直接顺序扫描所有记录，找到符合查询条件的记录，就加入到结果集。

索引

当可选执行计划包含使用索引计划时，根据不同的索引类型计算最终的成本。

主键等值索引

主键的等值索引时，直接可以定位到最终的记录。

主键范围索引

主键范围索引时，需要定位到临界值上，然后遍历链表，直到第一个不符合条件的记录。

辅助索引

辅助索引时，如果是聚簇索引，那么先定位到主键，然后根据主键定位记录（需要注意辅助索引时是否使用覆盖索引，如果有覆盖索引优化，那么很可能不需要回表）；如果是非聚簇索引，那么直接定位到记录。

Group By - Having（分组运算）

Group By的目标是实现分组，一般有三种实现方式。

临时表

使用临时表来存储分组数据。

  1. 创建临时表，列等于Query；
  2. 执行查询计划，全表扫描或索引扫描（From和Where执行） ；
  3. 在筛选目标记录时，尝试向临时表中添加记录；
    1. 如果有Having，那么判断条件是否成立，不符合Having条件则忽略，符合则下一步；
    2. 如果目标记录在临时表中不存在，那么添加；
    3. 如果不存在，那么忽略或进行函数处理（如Count就是自增，Min就是比较和替换）；
  1. 当执行完成后，临时表进行排序，排序原理参考Order By；
  2. 返回最终数据；
紧凑索引扫描

在临时表之外一种简单的方式是依赖于索引进行分组，因为临时表占用了资源以及需要额外的排序。

注意：只有Group By的字段最左前缀是索引字段，才会进行索引扫描。

  1. 执行查询计划，全表扫描或索引扫描（From和Where执行）；
  2. 不同于临时表，此时不向临时表添加数据，而是利用GroupBy的字段进行索引扫描；
    1. 根据索引字段，在上一个结果集中 扫描所有符合条件 的记录，如果与上一个字段相同，那么属于一个分组，忽略或执行自增等计算；如果不同，那么创建一个新的分组。
  1. 由于聚簇索引是有序的，因此无需排序。
  2. 返回所有的分组。
松散索引扫描

由于紧凑索引扫描需要索引扫描所有的结果集，因此在紧凑索引扫描的基础上，只扫描结果集中每一项的记录即可。

注意：松散索引扫描和紧凑索引扫描并不是必须用某一个，这两种方式适合于不同的环境，松散索引扫描性能较好，但仅适用于使用函数MIN和MAX时，因为松散索引扫描只会扫描结果集中每一组的一个记录。

  1. 执行查询计划，全表扫描或索引扫描（From和Where执行）；
  2. 索引扫描。
    1. 根据索引字段，读取某一个值的第一个记录；
    2. 然后直接读取下一个值的第一个记录，而紧凑索引扫描会继续读取当前值的第二个记录。
  1. 由于聚簇索引是有序的，因此无需排序。
  2. 返回所有的分组。

Order By（排序运算）

在实际查询中，往往需要针对某个字段进行排序，如日期、金额等。此时就需要进行排序运算。

索引排序

最好的方式是使用B-Tree的索引来避免Order By的排序。因为B-Tree类型下，索引是有序的，此时不需要额外的再次排序。

尽可能的保证排序字段是索引字段，这样可以在创建记录后同步索引结构时直接排序，避免查询时的排序。

注意：索引排序都是逻辑上的，在物理层面上，磁盘IO很可能是随机的。

内存排序

在sort buffer中使用不同的排序算法进行排序。

Sort Buffer

DBMS为每一个排序进程分配一个内存缓冲区，专门用于将结果集加载到Sort Buffer中进行排序。

快速排序算法

sort buffer空间足够时，使用快速排序对结果集进行排序。

归并排序算法

当sort buffer不够时，产生临时文件，并使用外排序。

堆排序算法

通常用于order by x limit m, n; 解决m-n之间的排序问题。使用堆排序可以降低时间复杂度，且仅需要m-n的内存空间。

外排序

sort buffer空间不够时，结果集就需要依赖于磁盘来进行排序。

基本思想
  1. 预处理；大文件分割并对每个子文件进行内部排序；
  2. 归并；对每个子文件进行归并排序，最终生成一个有序大文件；
二路归并

  1. 分割无序大文件为多个子文件R1-R10，加载子文件到内存并对其内部排序（快排等） => 有序的子文件集；
  2. 对R1-R10进行两两归并排序，每两个可以合并为一个有序子文件 => k-1 / 2 个子文件；
    1. 注意：两两归并时创建一个新的有序文件（R1&R2=>R1'; R3&R4=>R2'） ，每次各加载归并段的一个记录进行比较。
    2. 当内存缓冲区满了时，那么数据持久化追加到当前新的有序文件中。
    3. 如果两个归并段都没有记录数据了，那么重新二路归并 新的两个归并段 ，重复a、b、c三个步骤；
  1. 重复2步骤，直到最终只有一个有序文件；
  2. 返回唯一有序文件。
多路归并

由于二路归并每次都是两个文件比较，当文件较大时会产生频繁的文件读写，而频繁的磁盘IO对于DBMS来说是非常耗费性能的，因此产生了多路归并优化文件读写次数。

  1. 分割无序大文件为多个子文件R1-R10，加载子文件到内存并对其内部排序（快排等） => 有序的子文件集；
  2. 对R1-R10进行多路归并排序，每多个文件合并为一个有序子文件；
    1. 注意：2路归并使用归并排序实现，而多路归并使用 堆排序 实现，归并排序一次只能比较两个数据，而堆排序可以多路同时比较。
    2. 当内存缓冲区满了时，那么数据持久化追加到当前新的有序文件中。
    3. 如果多个归并段都没有记录数据了，那么重新多路归并 新的多路归并段 ，重复a、b、c三个步骤；
  1. 重复2步骤，直到最终只有一个有序文件；
  2. 返回唯一有序文件。

注意：多路归并可以使用胜者树、败者树优化。

Join （连接运算）

内连接、外连接
  • 内连接（inner join）：仅返回所有满足条件的T1+T2记录。
  • 左外连接（left join）：返回所有的 T1 记录 和 满足条件的T1+T2记录。
  • 右外连接（right join）：返回所有的 T2 记录 和 满足条件的T1+T2记录。
嵌套循环连接

for r1 in T1 do #遍历T1表
	for r2 in T2 do	#遍历T2表
  	if match(r1.x == r2.y) then #判断ON条件是否满足
      add_to_result(r1 & r2);	#满足则加入到结果集
		end
	end
end

实现Join的最简单方法就是，嵌套两个表的循环遍历，然后从中选出符合条件的。

但由于笛卡尔积很大，因此这种方式性能很低。

总结：避免未经优化的嵌套循环连接。

块嵌套循环连接

for B1 in T1 do #遍历T1的块
	for B2 in T2 do	#遍历T2的块
  	for r1 in B1 do #遍历块1中的所有缓存记录
    	for r2 in B2 do #遍历块2中的所有缓存记录
  			if match(r1.x == r2.y) then #判断ON条件是否满足
      		add_to_result(r1 & r2);	#满足则加入到结果集
      	end
    	end
		end
	end
end

块嵌套循环连接优化了磁盘IO次数，由于简单的嵌套循环每次都需要加载磁盘数据到内存，所以为了避免磁盘加载开销，因此使用缓存块优化磁盘IO。

总结：通过join_buffer_size优化Join时的缓存块。

索引嵌套循环连接
for r1 in T1 do #遍历T1表
	var r2s = fetch_from_T2_index(r1.x, T2.y); #根据T2的索引和t1记录找出所有满足条件的记录
  for r2 in r2s do #遍历所有匹配的记录
  	if match(r1.p == r2.q) then #判断ON其他条件是否满足
      add_to_result(r1 & r2);	#满足则加入到结果集
		end
	end
end

索引嵌套循环连接优化了时间复杂度，遍历外表记录后，在内表的索引结构中查找符合条件的所有记录，对此步骤来说，优化前，需要遍历每一个记录（M行），优化后只需要遍历索引结构（如果是树，那么只有层数*节点最大存储数）。

遍历所有记录的时间复杂度远远大于遍历索引结构。因为索引结构的目的就是优化查询。

注意：1. 索引嵌套循环连接生效的条件是ON使用的列必须是索引列。2.ON条件越少越好，否则当从索引中筛选出所有的匹配记录后，还需要继续遍历匹配结果，比较剩余的条件是否满足。

总结：尽可能的使用索引字段进行条件匹配。

归并连接

略

散列连接

略

Join 的优化
  • 在ON中尽可能的使用索引列；这样可以使用到索引嵌套循环连接。
  • ON中条件尽可能的少；避免匹配太过于复杂。
  • 外表最好是小表；这样可以在索引嵌套循环连接时减小时间复杂度。
  • 合适的Join Buffer；这样可以减少磁盘IO，通常大多数DBMS默认使用此方式。
  • Query数据尽可能的少；减少Block占用。

SubQuery（子查询运算）

子查询对于SQL来说非常常见，因为很多时候可以实现多张表的联合查询。

相关/非相关子查询
  • 相关子查询；子查询不能单独执行，必须依赖于父查询返回的字段数据；
    • select * from T1 as t1 where id exist ( select * from T2 as t2 where t2.id = t1.id ); 子查询中使用了t1中的id字段，因此每次子查询都必须父查询先进行。
  • 非相关子查询；子查询可以单独执行，不依赖于父查询的字段或数据；
    • select * from T1 as t1 where id exist ( select * from T2 as t2 where t2.id > 100 ); 子查询不依赖于父查询。
From 中的子查询

对于不相关子查询：

  1. 子查询先执行；
  2. 子查询返回一个虚拟表，然后连接结果集。

对于相关子查询：

  1. 先从父查询中查询出一条记录；
  2. 再根据查找出的记录在子查询中查找对应的记录；
  3. 此时再根据父查询的条件判断是否选择当前记录；
  4. 如果不满足条件，那么忽略，重复步骤1。
Where 中的子查询

对于不相关子查询：

  1. 子查询先执行；
  2. 子查询返回一列，然后父查询根据子查询返回的一列进行条件筛选。

对于相关子查询：

  1. 先从父查询中查询出一条记录；
  2. 再根据查找出的记录在子查询中查找对应的记录；
  3. 此时再根据父查询的条件判断是否选择当前记录；
  4. 如果不满足条件，那么忽略，重复步骤1。
Query 中的子查询

同Where。

GroupBy 中的子查询

同Where。

OrderBy 中的子查询

同Where。

为什么在子查询中不要使用 IN？

Count（统计运算）

单独字段记录

对于不支持事务或序列化执行的DBMS来说，可以在每次插入或删除记录时同步的修改当前表的统计字段。因为字段的值是随意修改的，不会出错。

查询时扫描

对于支持事务和并发读写的DBMS来说，在执行count时，进行扫描统计。

由于事务的不同隔离级别下，一个事务能看到的所有记录不同，因此没办法通过一个单独的字段来进行统计。因此实施查询便是最合适了，适应于每一个场景，虽然耗费了一定的性能，但DBMS可以通过优化来解决。

如A事务添加了记录，B事务也同时添加了记录，可能在可重复读级别时A使用count只能返回1，无法看到B事务的记录。但有可能在可重复读级别以下，A使用count返回了3，又可以看到B的新增记录。

如果分别在不同的事务隔离级别下统计当前所有的记录数，那么理论上可以实现，但需要注意回滚也要将原有对于该字段的修改撤销。这中间的过程非常复杂且容易出错。

count(*) 、count(id)、 count(1)、count(column) 如何选择？

单独字段记录时，这些比较没有意义。但如果是实时查询，那么不同的统计选择将非常的重要。

  • *；SQL92的标准，建议使用此方式。
    • 查找 * 时，DBMS会做一定的优化，当定位到符合条件的记录时，不需要将记录读取出来，而是仅仅统计。
  • primary key；使用主键。
    • 使用主键查找时，DBMS会从记录中读取到主键，然后进行统计。
  • 常量；
    • 使用常量时，DBMS不会读取真实的记录，而是以虚拟的常量来替代记录。
    • 性能和count(*)类似，但不够标准。
  • column；其他索引或索引列。
    • 同使用主键统计一样，DBMS通常会加载具体的字段来进行统计。
count 如何更快
  • 使用count(*)，DBMS会进行优化。
  • ID自增的话，获取最新的ID，倒序且limit 1。

Client Function（客户端函数运算）

DBMS所对应的客户端驱动包在客户端实现函数计算。

Server Function（服务端函数运算）

Date等大多数服务端函数都是在服务端结果集处理完成后，才进行的函数处理。

avatar
文章
阅读
粉丝