所有现代数据库都是使用基于成本的优化策略来优化查询。其核心思想是评估每一个操作的成本,然后找到最便宜的一系列操作并得到结果,这样就找到了降低查询成本的最优方案。
为了理解成本优化器是怎么工作的,我认为举一个例子来感受这个任务背后的复杂性是一种好的方式。在这一部分,先介绍两张表的3种常用方式,我们会看到即时是最简单的join查询,优化器也需要做很多工作。最后,我们会介绍真正的优化器是怎么工作的。
对于上述的3种join方式,我们会聚焦于其自身的时间复杂度,而不是优化器计算的CPU、磁盘IO及内存消耗。二者的不同之处在于,CPU消耗是近似的,为了计算它,需要计算每一个操作(例如:加法、if判断、乘法、遍历…)的消耗,而且:
- 每一个高级语言的操作都包含了特定数量的底层CPU指令操作;
- CPU每个操作的消耗是不同的,它依赖于CPU本身的架构体系。
我有时会提到磁盘IO,因为它非常重要,记住:磁盘IO是最大的瓶颈,而不是CPU。
索引
我们之前介绍的B+Tree索引,它们是有序的。另外,还有位图索引,它对CPU、磁盘IO及内存的消耗和B+Tree索引是不同的。而且很多现代数据库为了提升查询性能,可以为当前查询创建临时索引。
访问路径
在执行join操作之前,首先需要拿到数据。这一部分讲解如何拿到数据。因为访问路径真正的难题是和磁盘IO相关,因此不会谈论太多时间复杂度。
全表扫描(Full scan)
如果你在一个SQL的执行计划中看到full scan 或者 just scan(MySQL是All、Index),则表明其访问路径是全表扫描。全表扫描意味着数据库简单的扫描所有表数据或者整个索引,很明显扫描全表数据比扫描整个索引更加耗费磁盘IO。
范围扫描(Range Scan)
有一种扫描称为索引范围扫描,当SQL中包含像“WHERE AGE > 20 AND AGE <40“这种条件时,会使用这种扫描,当然前提是在AGE字段上有一个索引可以使用。
我们在之前章节已经看到范围查询的时间复杂度是M+log(N),M是范围内行的数量,N是当前索引中节点的数量。(参见【连载】关系型数据库是如何工作的?(5) - B+Tree索引)N和M都可以从统计信息中拿到,对于一个范围查询,不需要读取整个索引,因此相对全表扫描其磁盘IO消耗很小。
唯一性扫描(Unique scan)
如果你只需要索引中的某一个值,那就会用到唯一性扫描。
row id访问(Access by row id)
大部分时候,数据库都是要用一个索引,所以必须能够找到和索引关联的行,这是通过row id来实现的。例如下边的SQL:
SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 28
如果在PERSON.AGE上有一个索引,那么优化器就可以用索引找到所有28岁的人;因为索引中只有AGE信息,为了获取LASTNAME、FIRSTNAME,就必须找到表中关联的行。但是如果如是下边的SQL:
SELECT TYPE_PERSON.CATEGORY from PERSON ,TYPE_PERSON
WHERE PERSON.AGE = TYPE_PERSON.AGE
在PERSON上的所以会用于关联TYPE_PERSON,并且不会通过row id访问PERSON,因为你只是访问了TYPE_PERSON.CATEGORY。
当访问行数比较少时这样效果是比较好的,通过row id访问最需要关系的是磁盘IO。如果需要很多的访问,那么数据库就会可能会选择全表扫描。(译者注:因为全表扫描实际上一般是顺序磁盘IO,通过rowid访问个别行是随机磁盘IO,所以如果频繁的随机磁盘IO还不如直接顺序扫描全表结果来的更快)。
其他访问路径
我没有展示索引的访问路径(译者注:而且每个数据库不尽相同),如果想了解更多,可以查看Oracle Optimizer Access Paths。而且,每个数据库对于相同的访问路径可能会有不同的命名。