`
雨中伞
  • 浏览: 43908 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

索引调优

阅读更多
数据库性能调优技术 ---索引调优 2008-01-21

一、概述
  随着数据库在各个领域的使用不断增长,越来越多的应用提出了高性能的要求。数据库性能调优是知识密集型的学科,需要综合考虑各种复杂的因素:数据库缓 冲区的大小、索引的创建、语句改写等等。总之,数据库性能调优的目的在于使系统运行得更快。
  调优需要有广泛的知识,这使得它既简单又复杂。
  说调优简单,是因为调优者不必纠缠于复杂的公式和规则。许多学术界和业界的研究者都在尝试将调优和查询处理建立在数学基础之上。
  称调优复杂,是因为如果要完全理解常识所依赖的原理,还需要对应用、数据库管理系统、操作系统以及硬件有广泛而深刻的理解。
  数据库调优技术可以在不同的数据库系统中使用。如果需要调优数据库系统,最好掌握如下知识:1)查询处理、并发控制以及数据库恢复的知识;2)一些调 优的基本原则。
  这里主要描述索引调优。

二、索引调优
  索引是建立在表上的一种数据组织,它能提高访问表中一条或多条记录的特定查询效率。因此,适当的索引调优是很重要的。
  对于索引调优存在如下的几个误区:
  误区1:索引创建得越多越好?
  实际上:创建的索引可能建立后从来未使用。索引的创建也是需要代价的,对于删除、某些更新、插入操作,对于每个索引都要进行相应的删除、更新、插入操 作。从而导致删除、某些更新、插入操作的效率变低。
  误区2:对于一个单表的查询,可以索引1进行过滤再使用索引2进行过滤?
  实际上:假设查询语句如下select * from t1 where c1=1 and c2=2,c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤,产生的结果集是临时数据,不再具有索引,所以不可使用 ic2(或ic1)进行再次过滤。

索引优化的基本原则:
1.将索引和数据存放到不同的文件组
  没有将表数据和索引数据存储到不同的文件组,而不加区别地将它们存储到同一文件组。这样,不但会造成I/O竞争,也为数据库的维护工作带来不变。

2.组合索引的使用
  假设存在组合索引it1c1c2(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值。
  根据where条件的不同,归纳如下:
  1)c1=1 and c2=2:使用索引it1c1c2进行等值查找。
  2) c1=1 and c2>2:使用索引it1c1c2进行范围查找,可以有两种方法。
  方法1,使用通过索引键(1,2)在B树中命中一条记录,然后向后扫描找出     第一条符合条件的记录,从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于:如果c1=1 and c2=2对应的记录数很多,会产生很多无效的扫描。
  方法2,如果c2对应的int型数据,可以使用索引键(1,3)在B树中命中一条记录,从此记录往后的每一条记录都是符合条件的。
  本文中的例子均采用方法1。
  3)c1>1 and c2=2:因为索引的第一个列不是等于号的,索引即使后面出现了c2=2,也不能将c2=2应用于索引查找。这里,通过索引键(1,- ∞)在B树中命中一条记录,向后扫描找出第一条符合c1>1的记录,此后的每一条记录判断是否符合c2=2,如果符合则输出,否则过滤掉。这里我们 称c2=2没有参与到索引运算中去。这种情况在实际应用中经常出现。
  4)c1>1:通过索引键(1,- ∞)  在B树中命中一条记录,以此向后扫描找出第一条符合c1>1的记录,此后的每条记录都是符合条件的。

3.唯一索引与非唯一索引的差异
  假设索引int1c1(c1)是唯一索引,对于查询语句select c1 from t1 where c1=1,达梦数据库使用索引键(1)命中B树中一条记录,命中之后直接返回该记录(因为是唯一索引,所以最多只能有一条c1=1的记录)。
  假设索引it1c2(c2)是非唯一索引,对于查询语句select c2 from t2 where c2=2,达梦数据库使用索引键(2)命中B树中一条记录,返回该记录,并继续向后扫描,如果该记录是满足c=2,返回该记录,继续扫描,直到遇到第一条 不符合条件c2=2的记录。
  于是,我们可以得知,对于不存在重复值的列,创建唯一索引优于创建非唯一索引。

4.非聚集索引的作用
  每张表只可能一个聚集索引,聚集索引用来组织真实数据。语句“create table employee (id int cluster primary key,name varchar(20),addr varchar(20))”。表employee的数据用id来组织。如果要查找id=1000的员工记录,只要用索引键(1000)命中该聚集索引。但 是,对于要查找name=’张三’的员工记录就不能使用该索引了,需要进行全表扫描,对于每一条记录判断是否满足name=’张三’,这样会导致查询效率 非常低。
  要使用聚集索引,必需提供id,我们只能提供name,于是需要引入一个辅助结构实现name到id的转换,这就是非聚集索引的作用。该非聚集索引的 键是name,值是id。于是语句“select  * from employee where name=’张三’”的执行流程是:通过键(’张三’)命中非聚集索引,得到对应的id值3(假设’张三’对应的id为3),然后用键(3)命中聚集索 引,得到相应的记录。

5.是不是使用非聚集索引的查询都需要进行聚集的查询?
  不是的,虽然在上一点中查询转换为聚集索引的查找,有时候可以只需要使用非聚集索引。
  创建表并创建相应的索引:create table t1(c1 int,c2 int,c3 int);create index it1c2c3 on t1(c2,c3)。查询语句为:select c3 from t1 where c2=1。
  因为索引it1c2c3(c2,c3)覆盖查询语句中的列(c2,c3)。所以,该查询语句的执行流程为:通过索引键(1,- ∞)命中索引it1c2c3,对于该记录直接返回c3对应的值,继续向后扫描,如果索引记录中c1还是等于1,那么输出c3,以此类推,直到出现第一条 c1不等于1的索引记录,结束查询。

6.创建索引的规则
  创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量和表中记录的行数,就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行 数”的比值越接近于1,则该列的可选择行越高。在可选择性高的列上进行查询,返回的数据就较少,比较适合索引查询。相反,比如性别列上只有两个值,可选择 行就很小,不适合索引查询。

分享到:
评论

相关推荐

    SQLServer索引调优实践

    ### SQL Server索引调优实践 #### 索引的重要性 在数据库性能优化的过程中,索引扮演着极其重要的角色。不恰当的索引使用会导致其他优化措施的效果大打折扣,甚至变得毫无意义。因此,了解如何正确地创建、管理和...

    DB2缓冲池和索引调优的方法

    DB2 缓冲池和索引调优的方法 本文主要介绍了 DB2 缓冲池和索引调优的方法,以解决 DB2 性能问题。在性能问题的表现中,应用系统的响应时间长,耗时数秒到数十秒,操作系统上的表现是 CPU 和 IO 占用不会持续超过 50...

    数据库性能调优技术--索引调优

    ### 数据库性能调优技术——索引调优 #### 一、概述 随着信息技术的快速发展,数据库在各行各业的应用越来越广泛,对于数据处理速度和效率的要求也越来越高。数据库性能调优是一项涉及多方面知识的综合性技能,它...

    数据库性能调优技术1索引调优.pdf

    数据库性能调优是确保数据库系统高效运行的关键环节,其中索引调优是数据库调优中至关重要的一个方面。索引作为一种特殊的数据结构,能够提高数据库查询的速度,尤其在数据量大的情况下,合理的索引使用对查询效率的...

    DB2缓冲池和索引调优的方法.doc

    DB2 缓冲池和索引调优的方法 DB2 缓冲池和索引调优是 DB2数据库性能优化的关键。缓冲池是 DB2 中的一块区域,用于存放频繁访问的数据,以提高数据库的性能。索引是 DB2 中的一种数据结构,用于快速定位数据。缓冲池...

    sql学习 某SQL建索引调优案例.doc

    SQL索引优化案例分析 在本文中,我们将对一个SQL索引优化案例进行深入分析,从而揭示索引优化的重要性和实践方法。 索引优化的必要性 在数据库中,索引是一种非常重要的优化手段。索引可以大大提高查询速度,减少...

    mysql高级特性 mysql 架构、索引调优、事务、日志备份文档

    MySQL高级特性之索引优化 MySQL架构中,索引是提高数据库查询效率的关键技术之一。索引是帮助MySQL高效获取数据的数据结构,满足特定查找算法。这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上...

    DB2数据库调优索引

    DB2数据库的索引调优,保证大家看了不觉得后悔

    SQL Server索引设计与调优

    再者,索引调优包括合理选择索引列、避免冗余索引和使用覆盖索引。索引列的选择应根据查询语句的WHERE子句来确定,确保最常使用的查询条件被包含在内。冗余索引会导致不必要的空间占用和维护成本,应通过索引视图或...

    SQL_Server索引设计和调优技巧大全

    #### 七、SQL Server索引调优 ##### 7.1 使用Index Tuning Wizard Index Tuning Wizard是SQL Server提供的一款用于分析和建议索引优化方案的工具,可以帮助用户更好地理解数据库索引的基本原理: - **分析查询计划*...

    SQL Server索引设计和调优技术大全

    #### 七、索引调优工具 - **SQL Server Index Tuning Wizard**:这是一个强大的工具,可以帮助识别和优化索引,提供自动化的索引建议。 - **使用技巧**: - 分析查询计划以确定哪些查询可以从额外的索引中受益。 -...

    sql调优精致脚本

    本文将基于提供的标题、描述、标签以及部分内容,深入探讨Oracle索引调优的相关知识点,旨在帮助读者理解和掌握如何有效优化Oracle数据库中的SQL查询。 ### SQL调优精致脚本——Oracle索引调优 #### 一、统计信息...

    数据库性能调优技术系列

    ### 数据库性能调优技术系列:索引调优 #### 一、概述 随着信息技术的发展,数据库在各行各业的应用越来越广泛,对于数据处理速度和效率的需求也在不断提升。为了满足这一需求,**数据库性能调优**成为了必不可少...

    创建索引和调优索引

    《创建索引和调优索引:SQL Server 2005中的索引管理》 在SQL Server 2005中,索引是数据库管理的关键要素,它们极大地提升了数据检索速度,尤其是在处理大规模数据时。索引可以分为多种类型,包括聚集索引和非聚集...

    oracle 数据库性能调优技术 1 中文

    ### Oracle数据库性能调优技术——索引调优 #### 一、概述 随着信息技术的快速发展,数据库在各行各业的应用越来越广泛,对于高性能的需求也随之增加。**数据库性能调优**是一项复杂而又重要的任务,它旨在通过一...

    人工智能遇见人工智能:利用查询执行来改进 指数推荐.pdf

    传统的索引调优技术依赖于查询优化器的成本估计来寻找能最大程度提高执行效率的索引配置。然而,由于查询优化器在成本估算方面的局限性,在很多情况下,预计能够改善查询执行成本(如CPU时间)的索引实际上在实施后...

Global site tag (gtag.js) - Google Analytics