`

SQL Server的复合索引学习【转载】

 
阅读更多

概要
什么是单一索引,什么又是复合索引呢? 何时新建复合索引,复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。

一.概念

单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。

用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引。当表的行数远远大于索引键的数目时,使用这种方式可以明显加快表的查询速度。

同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,如果不特殊说明的话一般是指单一索引。宽索引也就是索引列超过2列的索引。

设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。拥有更多的窄索引,将给优化程序提供更多的选择余地,这通常有助于提高性能。

二.使用

创建索引 
create index idx1 on table1(col1,col2,col3)  
查询
select * from table1 where col1= A and col2= B and col3 = C

这时候查询优化器,不在扫描表了,而是直接的从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快。   

三.注意事项

1.何时是用复合索引
在where条件中字段用索引,如果用多字段就用复合索引。一般在select的字段不要建什么索引(如果是要查询select col1 ,col2, col3 from mytable,就不需要上面的索引了)。根据where条件建索引是极其重要的一个原则。注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中.

2.对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高。如:  
  IDX1:create   index   idx1   on   table1(col2,col3,col5)  
  select   *   from   table1   where   col2=A   and   col3=B   and   col5=D  

如果是"select   *   from   table1   where   col3=B   and   col2=A   and   col5=D"
或者是"select   *   from   table1   where   col3=B"将不会使用索引,或者效果不明显

3.复合索引会替代单一索引么?
很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列)

IDX1:create   index   idx1   on   Tgongwen(fariqi,neibuyonghu)  


(1)select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi>'2004-5-5'

查询速度:2513毫秒

(2)select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi>'2004-5-5' and neibuyonghu='办公室'

查询速度:2516毫秒

(3)select gid,fariqi,neibuyonghu,title from Tgongwen
where neibuyonghu='办公室'

查询速度:60280毫秒

从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

[参考: 查询优化及分页算法方案 http://blog.csdn.net/chiefsailor/archive/2007/05/28/1628339.aspx]

4.需要在同一列上同时建单一索引和复合索引么?
试验: sysbase   5.0   表table1   字段:col1,col2,col3  

试验步骤:  
(1)建立索引idx1   on   col1  
  执行select   *   from   table1   where   col1=A     使用idx1  
  执行select   *   from   table1   where   col1=A   and   col2=B   也使用idx1  

(2)删除索引idx1,然后建立idx2   on   (col1,col2)复合索引  
  执行以上两个查询,也都使用idx2  

(3)如果两个索引idx1,idx2都存在  
  并不是   where   col1='A'用idx1;where   col1=A   and   col2=B  用idx2。  
  其查询优化器使用其中一个以前常用索引。要么都用idx1,要么都用idx2.  
   
由此可见,
(1)对一张表来说,如果有一个复合索引 on   (col1,col2),就没有必要同时建立一个单索引 on col1。
(2)如果查询条件需要,可以在已有单索引
 on col1的情况下,添加复合索引on   (col1,col2),对于效率有一定的提高。
(3)
同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处,相对而言,建立多个窄字段(仅包含一个,或顶多2个字段)的索引可以达到更好的效率和灵活性。



5. 一定需要覆盖性查询么?
通常最好不要采用一个强调完全覆盖查询的策略。如果Select子句中的所有列都被一个非群集索引覆盖,优化程序会识别出这一点,并提供很好的性能。不过,这通常会导致索引过宽,并会过度依赖于优化程序使用该策略的可能性。通常,是用数量更多的窄索引,这对于大量查询来说可以提供更好的性能。

分享到:
评论

相关推荐

    SqlServer重建索引

    用于SqlServer的索引重建,全语句实现,可根据实际情况进行部分关键表的索引重建。

    SQL Server 索引中include的魅力(具有包含性列的索引)

    SQL Server 索引中 include 的魅力(具有包含性列的索引) SQL Server 索引中 include 的魅力(具有包含性列的索引)是指在非聚集索引中添加非键列,以扩展索引的功能,提高查询性能。通过将非键列添加到非聚集索引...

    SQL SERVER建立索引.pdf

    ### SQL Server 建立索引相关知识点 #### 一、建立索引 索引是在数据库表中的数据列上创建的一种特殊的数据结构,其目的是为了加快数据检索速度。索引类似于书籍的目录,通过索引可以快速定位到所需的数据行,从而...

    SQL Server 2000完结篇系列之七:SQL Server 2000索引优化详解

    在SQL Server 2000中,索引是数据库性能优化的关键组成部分,它极大地影响了数据查询的速度。本文将深入探讨SQL Server 2000中的索引优化,旨在帮助数据库管理员和开发人员理解如何有效地利用索引来提升系统性能。 ...

    SQL SERVER 2008 学习计划

    SQL SERVER 2008 学习计划 SQL Server 2008 是一个功能强大且功能齐全的关系数据库管理系统。本学习计划旨在帮助学习者快速掌握 SQL Server 2008 的基本概念、安装、配置、数据库设计、数据操作、存储过程、触发器...

    SQL Server 索引结构及其使用(聚集索引与非聚集索引)

    "SQL Server 索引结构及其使用(聚集索引与非聚集索引)" 数据库索引是数据库性能优化的关键技术之一。SQL Server 提供了两种索引:聚集索引(clustered index)和非聚集索引(nonclustered index)。本文将详细介绍...

    SqlServer 数据库索引优化详解

    深入理解SqlServer索引机制及合理优化数据库

    sqlserver索引表设计数据类型选择

    该ppt详细描述sqlserver索引优化时带来的查询性能提升和更新锁开销,最后介绍表设计,字段数据类型的选择及使用适当的冗余减少表连接

    SQLServer索引碎片和解决方法

    SQLServer中的索引碎片是影响数据库性能的重要因素,它分为外部碎片和内部碎片两种类型。外部碎片发生在索引页不在其逻辑顺序上时,导致查询过程中需要额外的页切换,尤其是对于返回有序结果集的查询,这将显著降低...

    SQL Server 2000索引结构及使用方法

    "SQL Server 2000 索引结构及使用方法" 本文将详细介绍 SQL Server 2000 的索引结构和使用方法,包括聚集索引和非聚集索引的定义、区别、使用场景和注意事项。 一、索引结构 索引是一种特殊的目录,可以帮助我们...

    sqlserver学习资料(转载!)

    这篇学习资料集合了大师级人物的智慧结晶,对于想要深入理解和掌握SQL Server的人来说是一份宝贵的资源。 1. SQL Server基础概念: - 数据库:在SQL Server中,数据库是存储和管理数据的逻辑容器。 - 表:表是...

    SqlServer索引工作原理

    SqlServer索引工作原理 在了解SqlServer索引工作原理之前,我们需要了解什么是索引。索引是一种特殊的数据库结构,它可以快速地定位和检索数据。索引的作用是加快数据的查询速度,使得数据库中的数据更易于管理和...

    SQL Server 2008完全学习手册视频教程.zip

    第5章 操作架构、索引和视图 SQL 2008完全学习手册 视频教程 第6章 数据查询和管理 第7章 操作查询 第8章 Transact-SQL编程 第9章 存储过程 第10章 触发器 第11章 SQL Server 2008的安全机制 第12章 备份与恢复...

    优化SQL Server索引的小技巧

    优化 SQL Server 索引的小技巧 SQL Server 中有多种可以让您检测、调整和优化 SQL Server 性能的工具。其中,优化数据库索引的使用是提高 SQL Server 性能的关键因素之一。在本文中,我们将讨论如何用 SQL Server ...

    SQL Server 2008完全学习手册PART1

    目录: (1)基础知识篇 (2)日常管理篇 (3)数据处理篇 (4)数据库管理篇 (5)高级应用篇 (6)综合实例篇

    SqlServer初学者学习笔记

    以下是一些关于Sql Server学习的重要知识点: 1. 数据库基础:首先,你需要了解数据库的基本概念,如数据库、表、字段、记录等。Sql Server支持多种数据库模型,最常用的是关系模型,其中,表是数据的主要存储形式...

    SqlServer连接工具

    SQL Server连接工具是数据库管理员和开发人员用于与Microsoft SQL Server交互的重要软件。这些工具提供了多种功能,包括数据库的创建、管理、查询、备份、恢复以及性能优化等。在本篇文章中,我们将深入探讨SQL ...

    SQLServer2005数据库学习笔记

    笔记是本人学习SQLServer一段时间后重新整理出来的,适合有一些入门基础的人学习。 ├─01 安装及使用 │ SQLServer2005安装及使用.txt │ ├─02 常用函数 │ function.sql │ ├─03 建表、建库 │ create.sql ...

    sqlserver自动生成sql语句工具sqlserver转oracle

    首先,标题中的"sqlserver自动生成sql语句工具"指的是可以分析SQL Server数据库结构和数据,自动生成对应的SQL创建语句的软件。这种工具通常能帮助数据库管理员快速获取数据库的定义,便于备份、迁移或在其他环境中...

    Sqlserver索引分析

    Sqlserver索引分析,Sqlserver索引缺失,Sqlserver索引建议

Global site tag (gtag.js) - Google Analytics