数据库查询代价估算优化的深度介绍

今天在看一本书的时候发现在查询代价估算一节讲的不是很清晰，受篇幅限制吧。例如原文会出现这样两句话“索引列出现在JOIN/ON子句中，作为连接条件，不可使用索引”，“索引列出现在JOIN/ON子句中，作为限制条件满足”key<op>常量”格式可用索引”，这样的话就很容易误导读者，没有将本质叙述出来。真实原因，如果驱动表行数很少，在约束条件上内表走索引过滤性强的时候，那么通常选择走索引代价会更小。举个例子：

test=# create table t(c1 int unique, c2 int unique);test=# create table t1(c1 int primary key, c2 int, c3 int, c4 int unique);test=# explain select * from t join t1 on t.c2 = t1.c4 where t.c1 = 1;+---------------------------------------------------------------------------+| QUERY PLAN|+---------------------------------------------------------------------------+| Nested Loop  (cost=0.31..16.36 rows=1 width=24)   ||   ->  Index Scan using t_c1_key on t  (cost=0.16..8.17 rows=1 width=8)|| Index Cond: (c1 = 1)  ||   ->  Index Scan using t1_c4_key on t1  (cost=0.15..8.17 rows=1 width=16) || Index Cond: (c4 = t.c2)   |+---------------------------------------------------------------------------+5 rows in set

在t1索引列c4上，并没有常量condition，也没有其等值的t.c2的常量condition。但t.c1 = 1这个equal condition可以估算返回1行数据，获得这一行数据后t.c2为常量值，那么可以利用t1.c4 = t.c2这一条件走索引t1_c4_key快速找到需要的行做NL-JOIN。

自己已经工作两年，在这段时间中一项任务就是负责代价估算的开发工作，所以在这里写一下自己对CBO（基于代价的优化）的认识作为分享和总结，希望对大家提高对CBO的认识有帮助，也希望大家多多拍砖帮助自己更快成长。

接下来，我会按照自己项目中各个Operator的代价估算来讲，从底层基表访问到上层LIMIT逐一讲述。在介绍的过程中，我会举实际SQL的例子，也会加一些自己所在团队数据库（后面简称为OurDB）的基础实现介绍，希望能帮助你更好的理解。所以别闲我的介绍啰嗦啊，我是希望大部分有数据库基础知识的人都能看懂。由于能有条件安装OurDB的会非常非常少，当然我也没提DB名字，因此这里举例主要用PostgreSQL，即使是OurDB的例子，也会写Postgre的等价SQL。

基表访问(TABLE/INDEX SCAN/GET)的代价估算

基表访问路径选择的重要性

基表的访问路径(Table Access Path)的代价估算和选择，自己认为是最重要的。

就其自身而言，如果SQL最优路径是走索引A，结果走了前缀索引列不相关或者需要大量回表的索引B，那这条SQL的执行很可能比最优路径性能差几个数量级。

同时如果基表路径返回行数估算偏差大，对于上层Operator的选择往往是毁灭性的。例如两表t、t1做JOIN时，t被估算返回1行数据，然后优化器在选择JOIN type的时候选择了t作为外表，t1作为内表的无MATERIAL的NestLoop Join。如下：

test=# explain select * from t, t1 where t.c1 = 1;+------------------------------------------------------------------------+| QUERY PLAN |+------------------------------------------------------------------------+| Nested Loop  (cost=0.16..53.57 rows=1770 width=24) ||   ->  Index Scan using t_c1_key on t  (cost=0.16..8.17 rows=1 width=8) || Index Cond: (c1 = 1)   ||   ->  Seq Scan on t1  (cost=0.00..27.70 rows=1770 width=16)|+------------------------------------------------------------------------+4 rows in set

如果行数估算错误，等到实际执行时候，t表返回了1000行，这样实际执行时内表要执行1000次的Seq Scan，真正执行的代价会是估算代价的近1000倍。这个时候有MATERIAL的NL-JOIN会比所选择的执行效率高很多。

test=# create table t2(c1 int unique, c2 int);test=# explain select * from t2, t1 where t2.c2 = 1;+----------------------------------------------------------------+| QUERY PLAN |+----------------------------------------------------------------+| Nested Loop  (cost=0.00..307.85 rows=19470 width=24)   ||   ->  Seq Scan on t1  (cost=0.00..27.70 rows=1770 width=16)||   ->  Materialize  (cost=0.00..36.81 rows=11 width=8)  || ->  Seq Scan on t2  (cost=0.00..36.75 rows=11 width=8) ||   Filter: (c2 = 1) |+----------------------------------------------------------------+5 rows in set

读者可以在介绍JOIN OPERATOR代价估算之前估计一下，以下两条SELECT SQL会选择的JOIN方式，然后在PostgreSQL上执行一下，看自己的判断是否准确。

test=# create table t1(c1 int primary key, c2 int, c3 int, c4 int unique);test=# create table t2(c1 int unique, c2 int);test=# explain select * from t2, t1 where t1.c1 = t2.c1 and t2.c1 = 1;test=# explain select * from t2, t1 where t1.c1 = t2.c1 and t2.c2 = 1;

经过上面的介绍，相信你也会认为最底层的TABLE ACCESS PATH选择是非常关键的。接下来我会基于OurDB的实现来介绍TABLE ACCESS PATH的代价估算。

OurDB背景介绍

在OurDB中，索引表和主表在存储层存储方式、访问方式是一样的，在内部会加前缀__index作为区分，比如创建t_c1_key，在内部可以理解为一张名为__index_t_c1_key的table。在访问主表或者索引表的时候，都会根据SQL抽取query_range计算需要访问的数据区间。例如下面SQL中你可以看到，Query1选择走主表，其range为(1,2)。Query2选择走索引t_c2，range为((1, MAX)-(3,MIN))。在t_c2的range expr中，你可以看到c1，是因为索引表为保证row的唯一性，会加入主表的primay key。

(root@test)> create table t(c1 int primary key, c2 int, c3 int, key t_c2(c2));Query1：(root@test)> explain extended_noaddr select * from t where c1 > 1 and c1 < 2\G*************************** 1. row ***************************Query Plan: ===================================|ID|OPERATOR  |NAME|EST. ROWS|COST|-----------------------------------|0 |TABLE SCAN|t   |1|37  |===================================Outputs & filters:-------------------------------------  0 - output([t.c1], [t.c2], [t.c3]), filter(nil),  access([t.c1], [t.c2], [t.c3]), partitions(p0),  is_index_back=false,  range_expr([t.c1]), range(1 ; 2)Query2：(root@test)> explain extended_noaddr select * from t where c2 > 1 and c2 < 3\G*************************** 1. row ***************************Query Plan: ======================================|ID|OPERATOR  |NAME   |EST. ROWS|COST|--------------------------------------|0 |TABLE SCAN|t(t_c2)|1|164 |======================================Outputs & filters:-------------------------------------  0 - output([t.c1], [t.c2], [t.c3]), filter(nil),  access([t.c2], [t.c1], [t.c3]), partitions(p0),  is_index_back=true,  range_expr([t.c2], [t.c1]), range(1,MAX ; 3,MIN)

在Postgre中的等价SQL为：

test=# create table t(c1 int primary key, c2 int, c3 int);test=# create index t_c2 on t(c2);test=# explain verbose select * from t where c1 > 1 and c1;test=# explain verbose select * from t where c2 > 1 and c2 < 3;

query range(index cond)

我们知道table的所有行数 * table filter的选择率就是基表访问返回的行数。但是对于基表路径的选择，更为关键的是query range的选择性，即索引表上需要访问多少行数据。同一个表，同样的filters，各个路径经过filter返回的行数是一致的，但是query range决定了需要在访问路径上访问多少数据。例如，上面例子中Qury1选择走主表和索引t_c2最终都会是1行。但它们一个是range（1,2）的范围扫描，一个是range(min,min);(max，max)的Seq Scan，其代价差距会非常大。

(root@test)> explain extended_noaddr select * from t use index(t_c2) where c1 > 1 and c1 < 2\G*************************** 1. row ***************************Query Plan: ======================================|ID|OPERATOR  |NAME   |EST. ROWS|COST|--------------------------------------|0 |TABLE SCAN|t(t_c2)|1|302 |======================================Outputs & filters:-------------------------------------  0 - output([t.c1], [t.c2], [t.c3]), filter([t.c1 > 1], [t.c1 < 2]),  access([t.c1], [t.c2], [t.c3]), partitions(p0),  is_index_back=true, filter_before_indexback[true,true],  range_expr([t.c2], [t.c1]), range(MIN,MIN ; MAX,MAX)always true

在PostgreSQL中等价SQL，由于pg不支持Hint，所以需要自己安装插件，可参考文档https://yq.aliyun.com/articles/4796和http://pghintplan.osdn.jp/pg_hint_plan.html：

test=# /*+IndexScan(t3 t3_c2)*/explain verbose select * from t3 where c1 > 1 and c1 < 2;STATEMENT:  /*+IndexScan(t3 t3_c2)*/explain verbose select * from t3 where c1 > 1 and c1 < 2;  QUERY PLAN------------------------------------------------------------------------------- Seq Scan on public.t3  (cost=10000000000.00..10000000039.10 rows=10 width=12)   Output: c1, c2, c3   Filter: ((t3.c1 > 1) AND (t3.c1 < 2))(3 rows)test=# /*+IndexScan(t3 t3_pkey)*/explain verbose select * from t3 where c1 > 1 and c1 < 2;STATEMENT:  /*+IndexScan(t3 t3_pkey)*/explain verbose select * from t3 where c1 > 1 and c1 < 2; QUERY PLAN---------------------------------------------------------------------------- Index Scan using t3_pkey on public.t3  (cost=0.15..32.35 rows=10 width=12)   Output: c1, c2, c3   Index Cond: ((t3.c1 > 1) AND (t3.c1 < 2))    

query range sel

并不是所有在索引列上的条件都可以成为index cond，对于同时包含索引列和其他列计算的filter，复杂计算无法抽取成range的filter都会无法成为index cond。例如在PostgreSQL中：

test=# create table t4 (c1 int primary key, c2 varchar(10), c3 varchar(10));test=# create index t4_c3 on t4(c3);QUERY1：test=# explain select * from t4 where c3 >= 'a' and c3 <= 'b';+-------------------------------------------------------------------------------+| QUERY PLAN|+-------------------------------------------------------------------------------+| Bitmap Heap Scan on t4  (cost=4.19..12.66 rows=4 width=80)||   Recheck Cond: (((c3)::text >= 'a'::text) AND ((c3)::text <= 'b'::text)) ||   ->  Bitmap Index Scan on t4_c3  (cost=0.00..4.19 rows=4 width=0)|| Index Cond: (((c3)::text >= 'a'::text) AND ((c3)::text <= 'b'::text)) |+-------------------------------------------------------------------------------+QUERY2：test=#  explain select * from t4 where c3 between 'a' and 'b';+-------------------------------------------------------------------------------+| QUERY PLAN|+-------------------------------------------------------------------------------+| Bitmap Heap Scan on t4  (cost=4.19..12.66 rows=4 width=80)||   Recheck Cond: (((c3)::text >= 'a'::text) AND ((c3)::text <= 'b'::text)) ||   ->  Bitmap Index Scan on t4_c3  (cost=0.00..4.19 rows=4 width=0)|| Index Cond: (((c3)::text >= 'a'::text) AND ((c3)::text <= 'b'::text)) |+-------------------------------------------------------------------------------+QUERY3：test=# explain select * from t4 where c3 like 'a%';+----------------------------------------------------+| QUERY PLAN |+----------------------------------------------------+| Seq Scan on t4  (cost=0.00..19.25 rows=4 width=80) ||   Filter: ((c3)::text ~~ 'a%'::text)   |+----------------------------------------------------+QUERY4：test=# explain select * from t4 where c3 like '%a';+------------------------------------------------------+| QUERY PLAN   |+------------------------------------------------------+| Seq Scan on t4  (cost=0.00..19.25 rows=148 width=80) ||   Filter: ((c3)::text ~~ '%a'::text) |+------------------------------------------------------+QUERY5：test=# explain select * from t4 where repeat(c3, 4) = 'a';+----------------------------------------------------+| QUERY PLAN |+----------------------------------------------------+| Seq Scan on t4  (cost=0.00..21.10 rows=4 width=80) ||   Filter: (repeat((c3)::text, 4) = 'a'::text)  |+----------------------------------------------------+

可以看到只有QUERY1和QUERY2抽取除了Index Cond（其实like ‘a%’可以抽取出Index Cond[a,b) ）。

如果索引有两列(a,b)，如果range为(1,2)-(2,3)，那么其range选择率可以近似为(1,min)-(2,max)。当需要回表的时候，使用range选择数据，可以通过索引列上的filter做过滤获取rowkey后再回表。

cost计算

未完待续！