数据库性能调优技术系列

数据库性能调优技术---索引调优作者：杨万富一、概述随着数据库在各个领域的使用不断增长，越来越多的应用提出了高性能的要求。数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。调优需要有广泛的知识，这使得它既简单又复杂。说调优简单，是因为调优者不必纠缠于复杂的公式和规则。许多学术界和业界的研究者都在尝试将调优和查询处理建立在数学基础之上。称调优复杂，是因为如果要完全理解常识所依赖的原理，还需要对应用、数据库管理系统、操作系统以及硬件有广泛而深刻的理解。数据库调优技术可以在不同的数据库系统中使用。如果需要调优数据库系统，最好掌握如下知识：1）查询处理、并发控制以及数据库恢复的知识；2）一些调优的基本原则。这里主要描述索引调优。二、索引调优索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效率。因此，适当的索引调优是很重要的。对于索引调优存在如下的几个误区：误区1：索引创建得越多越好？实际上：创建的索引可能建立后从来未使用。索引的创建也是需要代价的，对于删除、某些更新、插入操作，对于每个索引都要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。误区2：对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤？实际上：假设查询语句如下select*fromt1wherec1=1andc2=2，c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。索引优化的基本原则：1.将索引和数据存放到不同的文件组没有将表数据和索引数据存储到不同的文件组，而不加区别地将它们存储到同一文件组。这样，不但会造成I/O竞争，也为数据库的维护工作带来不变。2.组合索引的使用假设存在组合索引it1c1c2(c1,c2)，查询语句select*fromt1wherec1=1andc2=2能够使用该索引。查询语句select*fromt1wherec1=1也能够使用该索引。但是，查询语句select*fromt1wherec2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。根据where条件的不同，归纳如下：1)c1=1andc2=2：使用索引it1c1c2进行等值查找。2)c1=1andc22：使用索引it1c1c2进行范围查找，可以有两种方法。方法1，使用通过索引键(1,2)在B树中命中一条记录，然后向后扫描找出第一条符合条件的记录，从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于：如果c1=1andc2=2对应的记录数很多，会产生很多无效的扫描。方法2，如果c2对应的int型数据，可以使用索引键(1,3)在B树中命中一条记录，从此记录往后的每一条记录都是符合条件的。本文中的例子均采用方法1。3）c11andc2=2：因为索引的第一个列不是等于号的，索引即使后面出现了c2=2，也不能将c2=2应用于索引查找。这里，通过索引键(1,-∞)在B树中命中一条记录，向后扫描找出第一条符合c11的记录，此后的每一条记录判断是否符合c2=2，如果符合则输出，否则过滤掉。这里我们称为c2=2没有参与到索引运算中去。这种情况在实际应用中经常发现。4）c11：通过索引键(1,-∞)在B树中命中一条记录，以此向后扫描找出第一条符合c11的记录，此后的每条记录都是符合条件的。3.唯一索引与非唯一索引的差异假设索引int1c1(c1)是唯一索引，对于查询语句selectc1fromt1wherec1=1，达梦数据库使用索引键(1)命中B树中一条记录，命中之后直接返回该记录（因为是唯一索引，所以最多只能有一条c1=1的记录）。假设索引it1c2(c2)是非唯一索引，对于查询语句selectc2fromt2wherec2=2，达梦数据库使用索引键(2)命中B树中一条记录，返回该记录，并继续向后扫描，如果该记录是满足c=2，返回该记录，继续扫描，直到遇到第一条不符合条件c2=2的记录。于是，我们可以得知，对于不存在重复值的列，创建唯一索引优于创建非唯一索引。4.非聚集索引的作用每张表只可能一个聚集索引，聚集索引用来组织真实数据。语句“createtableemployee(idintclusterprimarykey,namevarchar(20),addrvarchar(20))”。表employee的数据用id来组织。如果要查找id=1000的员工记录，只要用索引键（1000）命中该聚集索引。但是，对于要查找name=’张三’的员工记录就不能使用该索引了，需要进行全表扫描，对于每一条记录判断是否满足name=’张三’，这样会导致查询效率非常低。要使用聚集索引，必需提供id，我们只能提供name，于是需要引入一个辅助结构实现name到id的转换，这就是非聚集索引的作用。该非聚集索引的键是name，值是id。于是语句“select*fromemployeewherename=’张三’”的执行流程是：通过键（’张三’）命中非聚集索引，得到对应的id值3（假设’张三’对应的id为3），然后用键(3)命中聚集索引，得到相应的记录。5.是不是使用非聚集索引的查询都需要进行聚集的查询？不是的，虽然在上一点中查询转换为聚集索引的查找，有时候可以只需要使用非聚集索引。创建表并创建相应的索引：createtablet1(c1int,c2int,c3int);createindexit1c2c3ont1(c2,c3)。查询语句为：selectc3fromt1wherec2=1。因为索引it1c2c3(c2,c3)覆盖查询语句中的列(c2,c3)。所以，该查询语句的执行流程为：通过索引键(1,-∞)命中索引it1c2c3，对于该记录直接返回c3对应的值，继续向后扫描，如果索引记录中c1还是等于1，那么输出c3，以此类推，直到出现第一条c1不等于1的索引记录,结束查询。6.创建索引的规则创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量和表中记录的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。数据库性能调优技术--深入理解单表执行计划作者：杨万富一、概述这篇文章是数据库性能调优技术的第二篇。上一篇讲解的索引调优是数据库性能调优技术的基础。这篇讲解的深入理解单表执行计划，是数据库性能调优的有力工具。查询语句可以有多种可选执行计划，如何选择效率最高的执行计划?达梦数据库、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行的