数据库性能调优技术系列--深入理解单表扫执计划

数据库性能调优技术--深入理解单表执行计划作者：杨万富一、概述这篇文章是数据库性能调优技术的第二篇。上一篇讲解的索引调优是数据库性能调优技术的基础。这篇讲解的深入理解单表执行计划，是数据库性能调优的有力工具。查询语句可以有多种可选执行计划，如何选择效率最高的执行计划?达梦数据库、oracle数据库、sqlserver数据库都是采用基于成本的查询优化，对备选执行计划进行打分，选择大家最小的执行计划进行执行。这些内容，我会在后续的几篇文章中进行详细的描述。在此之前，我们首先需要掌握如何理解数据库执行计划。这篇文章讲解只涉及单表操作的执行计划。达梦数据库、oracle数据库、sqlserver数据库都可以显示给定语句的执行计划。我详细分析了这三个数据库的执行计划，三者之间并无本质区别。所以本文的内容适合于这三个数据库。同样，也应该适合绝大多数其它的数据库。单表执行的深入理解，是了解多表执行计划的基础。达梦数据库显示的执行计划时，显示的信息会多一些。因此，这篇文章中我选择达梦数据库作为实例数据库来讲解执行计划的原理。读完本文后，应该能够读懂这三个数据库的单表执行计划。二、深入理解数据库执行计划达梦数据库的执行计划有两种显示方式：第一种为图形化的显示方式；第二种为文本式的显示方式。这里采用第二种方式进行讲解。理解执行计划，是迈向理解数据库性能调优的重要一步。从执行计划中，我们可以看出数据库是如何执行查询语句，并根据执行计划判断出该查询语句的执行是否高效，以及如何进行优化。下面我们将通过一些例子来理解数据库执行计划。1.没有索引的全表扫描过滤如何执行？构造处执行场景：createtablet1(c1int,c2int);insertintot1values(1,1);insertintot1values(2,2);insertintot1values(3,3);insertintot1values(4,4);insertintot1values(5,5);insertintot1values(6,6);查询语句为：select*fromt1wherec1=2;该语句的执行过程，如果用语言描述可以描述成这样：1）如果是第一次执行该步骤，则取得表的第一条记录；否则取得当前记录的下一条记录。如果记录已经扫描结束，则执行步骤4，否则执行步骤2。2）判断该记录是否满足过滤条件c1=2，满足则执行步骤3，否则执行步骤1。3）把该记录放到结果集中，执行步骤1。4）将结果集返回给客户端。实际上，数据库执行查询语句的过程也是类似的，下面是该查询语句的执行计划：#RSET:[21,1,1];#XFLT:[0,0,0];EXPR0=2#CSEK:[21,1,1];INDEX33555545(T1),FULL_SCAN该执行计划中出现的内容，在此做出解释：1)CSEK（查找）类似于上文中描述的步骤1，方括号中的内容是执行该操作的评估代价，本文不作分析。“INDEX33555545(T1)”说明使用了T1表的聚集索引，“FULL_SCAN”表示对聚集索引INDEX33555545(T1)进行全扫描。这里需要注意的是，达梦数据库中的表默认情况下是索引组织的。如果建表时指定了clusterprimarykey，那么数据以该clsuterprimarykey组织数据，否则以rowid组织数据。2）XFLT（过滤）类似于上文中描述的步骤2，“EXPR0=2”是过滤条件。3）RSET（结果集）类似于上文中描述的步骤3，用来存放符合条件的记录集。我们可以看出，数据库的执行过程和我们用语言描述的步骤是一致的。该查询语句完整的执行流程如下：1）CSEK取得第一条记录（1，1）传给XFLT，将控制权传给XFLT。2）XFLT发现该记录（1，1）不符合条件，将控制权传给CSEK。3）CSEK取得下一条记录（2，2）传给XFLT，将控制权传给XFLT。4）XFLT发现记录（2，2）符合条件，将该记录传给RSET，将控制权传给RSET。5）RSET将记录（2，2）放入结果集，将控制权传给XFLT。6）XFLT给控制权传给CSEK。7）CSEK取得下一条（3，3）传给XFLT，将控制权传给XFLT。8）XFLT发现该记录（3，3）不符合条件，将控制权传给CSEK。9）CSEK取得下一条（4，4）传给XFLT，将控制权传给XFLT。10）XFLT发现该记录（4，4）不符合条件，将控制权传给CSEK11）CSEK取得下一条（5，5）传给XFLT，将控制权传给XFLT。12）XFLT发现该记录（5，5）不符合条件，将控制权传给CSEK。13）CSEK取得下一条（6，6）传给XFLT，将控制权传给XFLT。14）XFLT发现该记录（6，6）不符合条件，将控制权传给CSEK。15）CSEK发现描述操作已经结束，通知XFLT结束。将控制权传给XFLT。16）XFLT得知查询操作结束，通知RSET结束。将控制权传给RSET。17）RSET得知操作结束。18）发送结果集（包含记录（2，2））给客户端。2.如果表t1上的c1列有非唯一索引，如何执行呢？表t1的定义以及数据和1中描述的一样。创建索引：createindexit1c1ont1(c1);查询语句“select*fromt1wherec1=2;”对应的执行计划为：#RSET:[201,2,1];#CSEK(SECOND):[201,2,1];IT1C1(T1),INDEX_EQU_SEARCHCSEK行的“SECOND”表示使用非聚集索引“IT1C1”，对该索引进行索引等值（INDEX_EQU_SEARCH）查找。该执行计划的执行流程为：1）CSEK使用c1=2查找非聚集索引，得到第一条c1=2的索引记录（2，rowid1）中的rowid1（为数值）。使用rowid1查找聚集索引得到对应的数据记录（2，2）传递给RSET，将控制权传给RSET。2）RSET将记录（2，2）放入结果集，将控制权传给CSEK。（因为c1上的索引是非唯一的，所以可能出现两条以上的记录满足c1=2，所以需要将控制权传给CSEK）。3）CSEK取得当前非聚集记录的下一条记录（3，rowid2），因为3!=2，所以扫描结束。将控制权传给RSET。（如果满足c1=2的记录数大于1条，需要继续传递记录给RSET，以此类推，直到遇到不满足c1=2的那条记录，结束操作。）4）RSET得知操作结束。5）发送结果集（包含记录（2，2））给客户端。3.如果表t1上的c1列有唯一索引，如何执行呢？首先删除c1列上的非唯一索引，然后在c1列上创建唯一索引：dropindexit1c1;createuniqueindexuit1c1ont1(c1);查询语句“select*fromt1wherec1=2;”对应的执行计划为：#RSET:[201,2,1];#CSEK(SECOND):[201,2,1];UIT1C1(T1),INDEX_EQU_SEARCH该执行计划的执行流程为：1）CSEK使用c1=2查找非聚集索引，得到c1=2的索引记录（2，rowid1）中的rowid1（为数值）。使用rowid1查找聚集索引得到对应的数据记录（2，2）传递给RSET，将控制权传给RSET。（当然，有人也许会问，如果没有记录满足c1=2怎么办呢？那么，此处什么记录都不传递给RSET，通知RSET查询操作结束，最后返回空集给客户端）。2）RSET将记录（2，2）放入结果集，操作结束（因为是唯一索引，所以最多只有1条记录满足c1=2）。3）发送结果集（包含记录（2，2））给客户端。这里我们发现，例3使用了唯一索引，例2使用了非唯一索引。例3的执行速度大于例2的执行速度。4.如何理解执行计划中的topn操作？查询语句“selecttop10*fromt1wherec12;”对应的执行计划为：#RSET:[21,1,1];#XTOP:[0,0,0];top_off(0),top_num(10)#XFLT:[0,0,0];EXPR12#CSEK:[21,1,1];INDEX33555545(T1),FULL_SCANXTOP（取得前N条记录）：将XFLT操作符传递来的记录放入到RSET（结果集）中，并判断记录数是否已经等于给定值10（语句中的top10）。如果已经等于10，则查询已经执行成功，退出。否则将控制权限传给XFLT，继续执行。依次执行，直到取得10条记录，或者表CSEK操作已经查询结束（即符合条件的记录不满10条）。5.如何理解执行计划中的orderby操作？查询语句“selecttop10*fromt1wherec22orderbyc1;”对应的执行计划为：#RSET:[21,1,1];#XSORT:[0,0,0];keys_num(1),is_distinct(FALSE)#XFLT:[0,0,0];EXPR12#CSEK:[21,1,1];INDEX33555545(T1),FULL_SCANXSORT（对记录进行排序）：将XFLT操作符传递来的记录插入到XSORT维护的临时空间中的合理位置，按c1进行有序排列。然后将控制权传给XFLT以取得下一条符合条件的记录。等处理完所有符合条件的记录。XSORT操作符才会将控制权限传给RSET。6.是不是查询语句中一旦出现orderby字句，执行计划中就会出现XSORT操作符？不是。比如，查询语句“selectc1fromt1orderbyc1;”对应的执行计划为：#RSET:[0,0,0];#CSEK:[0,0,0];UIT1C1(T1),FULL_SCAN从执行中我们可以看出，达梦直接对索引UIT1C1进行全索引扫描，对于得到的每一条记录不需要进行XSORT排序操作，直接放入RSET（结果集）中。因为索引UIT1C1本身就是按照c1进行排序的。7.有文档说，对于语句“selectmax(c1)fromt1”，可以在c1列上创建索引从而查询速度变快。那么在执行计划中是如何体现的呢？查询语句“selectmax(c1)fromt1”对应的执行计划：#RSET:[0,0,0];#XEVL:[0,0,0];#FAGR:[0,0,0];function_num(1)在这个执行计划中，我们没有看到CSEK操作符。因为c1上存在索引UIT1C1，该索引叶子节点的最右端就是c1的最大值。FARG直接返回该最大值。语句“selectmin(c1)fromt1;”、语句“selectcount(*)fromt1;”的执行原理一样。XEVL是表达式计算，本文不进行讲解。8.如果列上存在索引，如何理解中的groupby操作？查询语句“selectc1,count(*)fromt1wherec1=2groupbyc1;”对应的执行计划为：#RSET:[11,1,1];#XEVL:[0,0,0];#SAGR:[0,0,0];group_by_num(1),function_num(1)#CSEK:[11,1,1];UIT1C1(T1),INDEX_GE_SEARCH我们可以得到，CSEK使用了索引UIT1C1进行了范围查找。首先传递给SARG的是连续的c1=2的记录组，然后是c1=3的记录组，然后是c1=4的记录组，……此处SARG的执行流程是1）从CSEK取得一条c1=2记录，将计数加1，2）从CSEK取得下一条记录，如果该记录满足c1，将计数+1。3）重复执行步骤2，直到取得第一条不满足c1=2的记录，将(2,对应的计算)传递给XEVL，再传给RSET（结果集）。接着对c1=3的记录组执行同样的流程。依此类推，直到处理完所有符合条件的记录。这里我们的分组函数是count(*)，如果是其它的分组函数，处理过程类似。9.如果列上不存在索引，如何理解中的groupby操作？查询语句“selectc2,count(*)fromt1wherec2=2groupbyc2;”对应的执行计划为：#RSET:[21,1,1];#XEVL:[0,0,0];#HAGR:[0,