数据库性能调优技术-mysql教程

一、概述 这些内容，我会在后续的几篇文章中进行详细的描述。在此之前，我们首先需要掌握如何理解数据库执行计划。这篇文章讲解只涉及单表操作的执行计划。达梦数据库、oracle 数据库、sql server 数据库都可以显示给定语句的执行计划。我详细分析了这三个数

一、概述

这些内容，我会在后续的几篇文章中进行详细的描述。在此之前，我们首先需要掌握如何理解数据库执行计划。这篇文章讲解只涉及单表操作的执行计划。达梦数据库、oracle 数据库、sql server 数据库都可以显示给定语句的执行计划。我详细分析了这三个数据库的执行计划，三者之间并无本质区别。所以本文的内容适合于这三个数据库。同样，也应该适合绝大多数其它的数据库。

单表执行的深入理解，是了解多表执行计划的基础。达梦数据库显示的执行计划时显示的信息会多一些。因此，这篇文章中我选择达梦数据库作为实例数据库来讲解执行计划的原理。读完本文后，应该能够读懂这三个数据库的单表执行计划。

二、深入理解数据库执行计划

达梦数据库的执行计划有两种显示方式：第一种为图形化的显示方式；第二种为文本式的显示方式。这里采用第二种方式进行讲解。理解执行计划，是迈向理解数据库性能调优的重要一步。从执行计划中，我们可以看出数据库是如何执行查询语句，并根据执行计划判断出该查询语句的执行是否高效，以及如何进行优化。

下面我们将通过一些例子来理解数据库执行计划。

1.没有索引的全表扫描过滤如何执行？

构造处执行场景：

create table t1(c1 int,c2 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

insert into t1 values(4,4);

insert into t1 values(5,5);

insert into t1 values(6,6);

查询语句为：

select * fromt1 wherec1=2;

该语句的执行过程，如果用语言描述可以描述成这样：

1）如果是第一次执行该步骤，则取得表的第一条记录；否则取得当前记录的下一条记录。如果记录已经扫描结束，则执行步骤 4，否则执行步骤 2。

2）判断该记录是否满足过滤条件c1=2，满足则执行步骤 3，否则执行步骤 1。

3）把该记录放到结果集中，执行步骤1。

4）将结果集返回给客户端。

实际上，数据库执行查询语句的过程也是类&＃20284;的，下面是该查询语句的执行计划：

#RSET:[21, 1, 1];

#XFLT:[0, 0, 0]; EXPR0 = 2

#CSEK:[21, 1, 1]; INDEX33555545(T1),FULL_SCAN

该执行计划中出现的内容，在此做出解释：

1)CSEK（查找）类&＃20284;于上文中描述的步骤1，方括号中的内容是执行该操作的评估代价，本文不作分析。“INDEX33555545(T1)”说明使用了 T1 表的聚集索引， “FULL_SCAN”表示对聚集索引 INDEX33555545(T1)进行全扫描。这里需要注意的是，达梦数据库中的表默认情况下是索引组织的。如果建表时指定了 cluster primary key，那么数据以该 clsuter primary key 组织数据，否则以 rowid 组织数据。

2）XFLT（过滤）类&＃20284;于上文中描述的步骤 2，“EXPR0 = 2”是过滤条件。

3）RSET（结果集）类&＃20284;于上文中描述的步骤 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）不符合条件，将控制权传给 CSEK

11）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 中描述的一样。

创建索引： create index it1_c1 on t1(c1);

查询语句“select* from t1 where c1=2;”对应的执行计划为：

#RSET:[201, 2, 1];

#CSEK(SECOND):[201, 2, 1]; IT1C1(T1), INDEX_EQU_SEARCH

CSEK 行的“SECOND”表示使用非聚集索引“IT1C1”，对该索引进行索引等&＃20540;（INDEX_EQU_SEARCH）查找。

该执行计划的执行流程为：

1）CSEK 使用 c1=2 查找非聚集索引，得到第一条 c1=2 的索引记录（2，rowid1）中的 rowid1（为数&＃20540;）。使用 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 列上创建唯一索引：

drop indexit1 c1;

create uniqueindex uit1 c1 on t1(c1);

查询语句“select* from t1 where c1=2;”对应的执行计划为：

#RSET:[201, 2, 1];

#CSEK(SECOND):[201, 2, 1]; UIT1C1(T1), INDEX_EQU_SEARCH

该执行计划的执行流程为：

1）CSEK 使用 c1=2 查找非聚集索引，得到 c1=2 的索引记录（2，rowid1）中的rowid1（为数&＃20540;）。使用 rowid1 查找聚集索引得到对应的数据记录（2，2）传递给 RSET， 将 控制权传给 RSET。（当然，有人也许会问，如果没有记录满足 c1=2怎么办呢？那么， 此处什么记录都不传递给RSET，通知RSET查询操作结束，最后返回空集给客户端）。

2）RSET 将记录（2，2）放入结果集，操作结束（因为是唯一索引，所以最多只有 1 条记录满足 c1=2）。

3）发送结果集（包含记录（2，2））给客户端。

这里我们发现，例3使用了唯一索引，例2使用了非唯一索引。例3的执行速度大于例2的执行速度。

4.如何理解执行计划中的 top n 操作？

查询语句“select top 10 * from t1 wherec1>2;”对应的执行计划为：

#RSET:[21, 1, 1];

#XTOP:[0, 0, 0]; top_off(0), top_num(10)

#XFLT:[0, 0, 0]; EXPR1 > 2

#CSEK:[21, 1, 1]; INDEX33555545(T1),FULL_SCAN

XTOP（取得前 N 条记录）：将 XFLT 操作符传递来的记录放入到 RSET（结果集）中，并判断记录数是否已经等于给定&＃20540; 10（语句中的 top 10）。如果已经等于 10，则查询已经执行成功，退出。否则将控制权限传给 XFLT，继续执行。依次执行，直到取得 10 条记录，或者表CSEK操作已经查询结束（即符合条件的记录不满 10 条）。

5.如何理解执行计划中的 order by 操作？

查询语句“select top 10 * from t1 where c2>2order by c1;”对应的执行计划为：

#RSET:[21, 1, 1];

#XSORT:[0, 0, 0]; keys_num(1),is_distinct(FALSE)

#XFLT:[0, 0, 0]; EXPR1 > 2

#CSEK:[21, 1, 1]; INDEX33555545(T1),FULL_SCAN

XSORT（对记录进行排序）：将 XFLT 操作符传递来的记录插入到 XSORT 维护的临时空间中的合理位置，按 c1 进行有序排列。然后将控制权传给 XFLT 以取得下一条符合条件的记录。等处理完所有符合条件的记录。XSORT操作符才会将控制权限传给 RSET。

6．是不是查询语句中一旦出现 order by 字句，执行计划中就会出现 XSORT 操作

符？

不是。比如，查询语句“selectc1 from t1 order by c1;”对应的执行计划为：

#RSET:[0, 0, 0];

#CSEK:[0, 0, 0]; UIT1C1(T1), FULL_SCAN

从执行中我们可以看出，达梦直接对索引 UIT1C1进行全索引扫描，对于得到的每一条记录不需要进行 XSORT 排序操作，直接放入 RSET（结果集）中。因为索引 UIT1C1 本身就是按照 c1 进行排序的。

7．有文档说，对于语句“select max(c1) from t1”，可以在 c1 列上创建索引从而查询速度变快。那么在执行计划中是如何体现的呢？

查询语句“selectmax(c1) from t1”对应的执行计划：

#RSET:[0, 0, 0];

#XEVL:[0, 0, 0];

#FAGR:[0, 0, 0]; function_num(1)

在这个执行计划中，我们没有看到 CSEK 操作符。因为 c1 上存在索引 UIT1C1，该索引叶子节点的最右端就是 c1 的最大&＃20540;。FARG 直接返回该最大&＃20540;。语句“select min(c1) from t1;”、语句“select count(*) fromt1;”的执行原理一样。XEVL是表达式计算，本文不进行讲解。

8．如果列上存在索引，如何理解中的 group by 操作？

查询语句“selectc1,count(*) from t1 where c1>=2 group by c1;”对应的执行计划为：

#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，将计数&＃43;1。

3）重复执行步骤 2，直到取得第一条不满足 c1=2 的记录，将(2,对应的计算)传递给 XEVL，再传给 RSET（结果集）。接着对 c1=3 的记录组执行同样的流程。依此类推，直到处理完所有符合条件的记录。

这里我们的分组函数是count(*)，如果是其它的分组函数，处理过程类&＃20284;。

9．如果列上不存在索引，如何理解中的 group by 操作？

查询语句“selectc2,count(*) from t1 where c2>=2group by c2;”对应的执行计划为：

#RSET:[21, 1, 1];

#XEVL:[0, 0, 0];

#HAGR:[0, 0, 0]; group_by_num(1),function_num(1)

#XFLT:[0, 0, 0]; EXPR0 >= 2

#CSEK:[21, 1, 1]; INDEX33555550(T1),FULL_SCAN

这里因为 c2 上没有索引，HARG 的作用是 HASH 分组。

HARG 的执行流程是：

1）从 XFLT 取得一条记录

2）记录的 c1=m，如果在 hash 表中已经对应项，计数&＃43;1，如果不存在对应项，在创建一个新的 hash 项。

3）所有的符合过滤条件的记录处理完成之后，HARG 才会将控制权限传给上层操作符，HARG 每次向上层操作符传递一条（m,m 对应的计数）。

这里我们的分组函数是 count(*)，如果是其它的分组函数，处理过程类&＃20284;。