`
robinsoncrusoe
  • 浏览: 747231 次
  • 性别: Icon_minigender_2
  • 来自: 深圳
社区版块
存档分类
最新评论

怎样才能充分利用SQL索引

阅读更多
怎样才能充分利用SQL索引
背景:目前WEB的普及太快,很多网站都会因为大流量的数据而发生服务器习惯性死机,一个查询语句只能适用于一定的网络环境.没有优化的查询当遇上大数据量时就不适用了.

本文主旨:讨论什么情况下能利用上索引.

索引:创建索引可以根据查询业务的不同分为两种:单一列的索引,联合索引. 顾名思义,单一列索引就是指在表的某一列上创建索引,联合索引是在多个列上联合创建索引.

优缺点比较:


1):索引所占用空间:单一列索引相对要小.

2):索引创建时间:单一列索引相对短.


3):索引对insert,update,delete的影响程序:单一列索引要相对低.


4):在多条件查询时,联合索引效率要高.


索引的使用范围:单一列索引可以出现在where 条件中的任何位置,而联合索引需要按一定的顺序来写.


本文所用测试软件环境如下:SQL05

DEMO:创建一个人员表,包含人员ID,姓名.在人员ID上创建一个聚集索引,在first_name和last_name上创建一个联合

索引.


create table person (id int, last_name varchar(30), first_name varchar(30))
create unique clustered index person_id on person (id)
create index person_name on person (last_name, first_name)



在上例中,id上创建了聚集索引,下面的查询都会用了聚集索引.


where id=1
where id>1
where id<1
where id between 1 and n
where id like '1%'

where id in(1,2,3...)

说明: id 列出现在条件中的位置并不一定要求第一列,不受位置影响.

不过下面的查询方式则不会用上聚集索引.
where person_id +1=n
where person_id like '%5'
where person_id like '%5%'
where person_id abs(15)

联合索引列比起单一列索引最大的好处在于,对于多条件的查询它比起单一列索引更加精确.拿上面的人员表来说吧,如果

要查询一个人的全名,只知道first_name是很难马上找到这个人的全名的,如果知道first_name和last_name则会非常容易找到.下面根据不同的条件与输出列顺序说明索引的应用.

第一种情况:--条件和输出列和索引列顺序相同
select last_name,first_name from person where last_name='1' and first_name='1'
stmtText
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]

AND [bdg_web_vaction].[dbo].[person].[first_name]=[@2]) ORDERED FORWARD)

结果:利用person_name联合索引查找

第二种情况:--条件列与索引列顺序不同,但输出列相同
select last_name,first_name from person where first_name='1' and last_name='1'
stmtText
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@2] AND [bdg_web_vaction].
[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

结果:利用person_name联合索引查找


第三种情况:--条件列与输出列与索引列的顺序都不相同
select first_name,last_name from person where first_name='1' and last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].
[last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

结果:利用person_name联合索引查找


第四种情况:--条件列在first_name和last_name中间加入另外一个条件
SELECT id, first_name,last_name from person where first_name='1' AND id=1 and last_name='1'
Clustered Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_id]),
SEEK:([bdg_web_vaction].[dbo].[person].[id]=CONVERT_IMPLICIT(int,[@2],0)),
WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1] AND [bdg_web_vaction].[dbo].[person].[las

结果:不能利用person_name联合索引查找

第五种情况:--在输出列中分开first_name和last_name
SELECT first_name,id,last_name from person where first_name='1' and last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].
[last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1])
ORDERED FORWARD)

结果:利用person_name联合索引查找


第六种情况:条件列没有出现联合索引的第一列
SELECT first_name,id,last_name from person where first_name='1'
SELECT first_name,last_name from person where first_name='1'
SELECT last_name ,first_name from person where first_name='1'

Index Scan(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1]))

结果:不能利用person_name联合索引.

第七种情况:--条件列出现联合索引的第一列
SELECT first_name,id,last_name from person where last_name='1'
SELECT first_name,last_name from person where last_name='1'
SELECT last_name ,first_name from person where last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]) ORDERED FORWARD)

结果:利用person_name联合索引查找

联合索引使用总结:

1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.


2):条件列中只要条件相连在一起,以本文例子来说就是:

last_name='1' and first_name='1'



first_name='1' and last_name='1'


,无论前后,都会利用上联合索引.


3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.


单一列索引的应用总结:

1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.


两者的共同点:

1):要想利用索引,都要符合SARG标准.

2) :都是为了提高查询速度.

3):都需要额外的系统开销,磁盘空间.


补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.

总结:即使表上创建了索引,但如果查询语句写的不科学的话(不符合SARG标准),也于事无补,要根据表索引情况来优化查询语句,如没有合适的索引可用,则要创建相应索引.
分享到:
评论

相关推荐

    SQLServer索引碎片和解决方法

    而内部碎片则是指索引页未充分利用,即填充因子不足,这可能导致更多的读取操作,因为服务器需要访问更多的页来获取相同数量的数据。 要检测SQLServer中的索引碎片,可以使用`DBCC SHOWCONTIG`命令。此命令提供了...

    Oracle+SQL优化之使用索引提示一例

    即使在上述优化后,`Person`表在外层查询中仍然采用全表扫描,未充分利用索引,导致更新操作效率低下。为了解决这一问题,引入了索引提示`/*+ INDEX (tablename indexname) */`,强制SQL执行计划使用指定索引`INDEX3...

    SQL索引一步到位1

    对于复合索引,应根据查询条件中字段的使用频率来排列字段顺序,以充分利用索引。 **总结**: SQL索引对于优化数据库性能至关重要,正确设计和使用索引可以显著提升查询速度。理解索引的类型和存储机制,以及何时何...

    sql索引、试图简介

    - 查询优化器会根据连接条件选择最优方案,应充分利用带有索引的表和行数较多的表。 - **WHERE子句的使用** - 避免在索引列上进行运算,否则可能导致表扫描,影响索引的利用。 ### 视图 视图是数据库中的一种...

    深入浅出讲解SQL索引结构

    3. 对于具有少量不同值的列,聚集索引可能不适用,因为索引的目的是减少I/O操作,大量重复的值无法充分利用索引。 4. 对于大量不同值的列,非聚集索引更适合,因为它可以提供更有效的查找。 5. 经常更新的列不适合...

    Lucene.net与Sql索引源码

    在“Lucene.net与Sql索引源码”这个主题中,我们将探讨如何将Lucene.NET与SQL数据库集成,构建一个强大的混合搜索解决方案。 首先,让我们理解Lucene.NET的工作原理。Lucene.NET是基于倒排索引(Inverted Index)的...

    sql server 索引设计与优化

    - **理解限制**:了解索引的局限性对于充分利用其优势至关重要。例如,避免页面拆分可以提高索引的性能。 - **操作指南**:通过具体的例子和实践技巧,指导用户如何有效地使用索引,并避免一些常见的陷阱。 - **DO与...

    基于索引的SQL语句优化

    2. **避免不必要的类型转换**:确保比较的两边数据类型一致,以充分利用索引。类型不匹配会导致索引无法被有效利用。 3. **增加查询的范围限制**:通过限制查询的范围,如使用BETWEEN或者LIMIT,减少扫描的数据量,...

    SQL优化-索引

    自增ID作为聚集索引,虽然简化了数据排序,但可能无法充分利用聚集索引在特定查询场景下的优势。因此,在设计数据库时,应仔细考虑每个表的具体需求,灵活选择和设计索引策略。 此外,索引的维护同样重要。随着数据...

    充分发挥SQL的能力.pdf

    总的来说,充分利用SQL的能力不仅在于掌握其基本语法,还需要理解其执行机制,包括嵌入式SQL的使用、解析过程、优化策略以及索引的作用。这些知识对于开发高效、优化的数据库应用程序至关重要。通过深入学习和实践,...

    对SQL Server索引的探讨.pdf

    在对SQL Server索引的探讨中,需要掌握的关键知识点包括索引的类型、作用、以及如何管理和维护索引。以下是对这些知识点的详细阐述: 1. 索引的类型 索引分为聚集索引(Clustered Index)和非聚集索引...

    通过SQL_Server_2005索引视图提高性能

    ### 通过SQL Server 2005索引视图提高性能 #### 一、什么是索引视图? 索引视图是一种特殊的视图,在SQL Server 2005 Enterprise Edition中得到了增强。传统上,视图是基于一个或多个表的虚拟表,主要用于实现数据...

    sql综合利用工具最新版,SQL综合利用

    SQL(Structured Query Language)是一种用于管理和处理关系数据库的标准编程语言,广泛应用于数据的存储、查询...为了充分利用这些工具,用户需要了解基本的SQL语法和数据库管理概念,同时熟悉工具的界面和操作流程。

    充分利用索引作用提高Oracle数据库的性能.pdf

    优化Oracle数据库性能的关键在于合理设计和利用索引。数据库设计、应用程序调优、内存管理、I/O优化等多方面因素共同作用,而索引作为其中重要一环,通过正确创建和维护,可以显著提升查询效率,保障系统的高性能...

    SQL效率提升之一些SQL编写建议并有效利用索引

    在SQL查询优化中,了解如何编写高效的SQL语句和充分利用索引是至关重要的。以下是对标题和描述中提到的一些关键知识点的详细解释: 1. **日期属性列**:日期时间类型的列,即使包含精确到秒的数据,也不会显著影响...

    行业-75当我们在SQL里进行排序的时候,如何才能使用索引?.rar

    然而,并非所有排序操作都能充分利用索引。本篇文章将深入探讨如何在SQL中进行排序并有效地利用索引。 首先,我们需要理解索引的工作原理。索引是一种特殊的数据结构,它为表中的列创建了一个指向实际数据行的指针...

    SQL Optimizer for SQL Server 10.0.3

    SQL Optimizer for SQL Server 10.0.3 正是一款针对SQL Server 语句进行优化的专业工具,旨在帮助用户最大化利用资源,提升查询效率,减少数据库系统的瓶颈。本文将深入探讨该工具的核心功能、使用场景以及T-SQL语言...

Global site tag (gtag.js) - Google Analytics