怎样才能充分利用SQL索引

落日映苍穹つ 2021-12-24 04:19 277阅读 0赞

怎样才能充分利用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):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.

两者的共同点: