Asp.Net网站优化系列之数据库的优化措施与索引优化方法
程序员文章站
2024-03-07 17:51:27
另外在sql server内存够用的情况下索引会被放到内存中，在内存中查找自然又会提高效率；所以我们必须得合理利用索引。 1）对什么列建索引 数据库默认情况下会对主键建聚集...
另外在sql server内存够用的情况下索引会被放到内存中，在内存中查找自然又会提高效率；所以我们必须得合理利用索引。
1）对什么列建索引
数据库默认情况下会对主键建聚集索引，除了这个索引之外还需要在哪些列上建索引呢？这个问题只能具体情况具体分析，要看需要优化的sql语句（通常是查询次数多，查询相应想要高的语句），根据什么列的条件进行查询。
例如：在论坛的数据库中有一张表是帖子回复表，在论坛的应用中用到最多的就是对指定帖子的某一页的回复进行查询，查询回复表的条件是主贴的id；这时候在主贴字段上建索引就势在必然。
2）一定要在主键上建聚集索引吗
通常情况下sql server会自动给主键加上聚集索引，但也有一些例外的情况我们需要把聚集索引建在其他列上，例如我们用到了表分区，而分区的字段不是主键，这时候就需要将聚集索引建在分区的列上。另外如果查询时根据主键查询较少，而根据其他列的查询较频繁，则也可以考虑将聚集索引建在非主键上。单需要注意的是聚集索引的列必须是不易变的列，如果聚集索引变了一会引起聚集索引内的记录的搬迁，造成页page的分离与碎片；二会引起每一个非聚 集索引被修改，以便于所有相关的非聚集索引的行的索引键的值被纠正。这既浪费时间和空间，也导致需要整理的碎片，增加了不必要的开销(每个列重组聚集键)。
3）复合索引（索引有两个以上的列）要注意列顺序
索引在数据库中是以b树的形式存储的。包含a,b两个列的索引会首先根据a列建b树，a列的叶节点上才会开始根据b列建b树。所以包含两个列的索引就需要根据查询条件所在列来决定两个列在索引中的顺序。
可以用下面的sql做实验：
use [test]
go
/****** 对象: table [dbo].[testindexorder] 脚本日期: 05/27/2010 09:11:26 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create table [dbo].[testindexorder](
[id] [int] identity(1,1) not null,
[firstname] [nvarchar](20) collate chinese_prc_ci_as not null,
[lastname] [nvarchar](20) collate chinese_prc_ci_as not null,
[desc] [nvarchar](400) collate chinese_prc_ci_as null,
constraint [pk_testindexorder] primary key clustered
(
[id] asc
)with (ignore_dup_key = off) on [primary]
) on [primary]
go
/****** 对象: index [ix_testindexorder] 脚本日期: 05/27/2010 09:11:51 ******/
create nonclustered index [ix_testindexorder] on [dbo].[testindexorder]
(
[firstname] asc,
[lastname] asc
)with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on [primary]
go
declare @i int;
declare @random varchar(36);
set @i = 0;
while @i < 100000
begin
set @random = newid();
insert into [testindexorder]
(firstname,lastname,[desc])
values(
substring(@random,1,8),substring(@random,12,8),@random
);
set @i = @i + 1
end
set statistics time on
select * from [testindexorder] where lastname = '6f-4eca-'
select * from [testindexorder] where firstname = 'caabe009'
set statistics time off
4）索引的个数问题
索引提高查询效率是以降低更新、插入、删除的速度为代价的。每当索引列发生变化时都需要对索引数据进行相应的调整。所以一个表上不可以建太多的索引，除非你完全不在乎修改数据的效率。另外sql server本身会对索引的数量和索引的数据长度有限制，具体请参考
5）在必要时重建索引
sql server运行一段时间之后就会形成一些索引碎片，这时候就需要重建索引了，有时候重建索引可以起到意想不到的效果。
查看索引碎片，重建索引，可以通过sql server管理器来重建；也可以通过下面的sql语句来实现：
--显示表testindexorder的索引碎片情况
dbcc showcontig(testindexorder)
--重建表的索引
--第一个参数，可以是表名，也可以是表id。
--第二个参数，如果是''，表示影响该表的所有索引。
--第三个参数，填充因子，即索引页的数据填充程度。如果是，表示每一个索引页都全部填满，此时select效率最高，但以后要插入索引时，就得移动后面的所有页，效率很低。如果是，表示使用先前的填充因子值。
dbcc dbreindex(testindexorder，''，)
数据库优化是一门复杂的学问，需要不断的学习实践，积累经验。
1）对什么列建索引
数据库默认情况下会对主键建聚集索引，除了这个索引之外还需要在哪些列上建索引呢？这个问题只能具体情况具体分析，要看需要优化的sql语句（通常是查询次数多，查询相应想要高的语句），根据什么列的条件进行查询。
例如：在论坛的数据库中有一张表是帖子回复表，在论坛的应用中用到最多的就是对指定帖子的某一页的回复进行查询，查询回复表的条件是主贴的id；这时候在主贴字段上建索引就势在必然。
2）一定要在主键上建聚集索引吗
通常情况下sql server会自动给主键加上聚集索引，但也有一些例外的情况我们需要把聚集索引建在其他列上，例如我们用到了表分区，而分区的字段不是主键，这时候就需要将聚集索引建在分区的列上。另外如果查询时根据主键查询较少，而根据其他列的查询较频繁，则也可以考虑将聚集索引建在非主键上。单需要注意的是聚集索引的列必须是不易变的列，如果聚集索引变了一会引起聚集索引内的记录的搬迁，造成页page的分离与碎片；二会引起每一个非聚 集索引被修改，以便于所有相关的非聚集索引的行的索引键的值被纠正。这既浪费时间和空间，也导致需要整理的碎片，增加了不必要的开销(每个列重组聚集键)。
3）复合索引（索引有两个以上的列）要注意列顺序
索引在数据库中是以b树的形式存储的。包含a,b两个列的索引会首先根据a列建b树，a列的叶节点上才会开始根据b列建b树。所以包含两个列的索引就需要根据查询条件所在列来决定两个列在索引中的顺序。
可以用下面的sql做实验：
复制代码 代码如下:
use [test]
go
/****** 对象: table [dbo].[testindexorder] 脚本日期: 05/27/2010 09:11:26 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create table [dbo].[testindexorder](
[id] [int] identity(1,1) not null,
[firstname] [nvarchar](20) collate chinese_prc_ci_as not null,
[lastname] [nvarchar](20) collate chinese_prc_ci_as not null,
[desc] [nvarchar](400) collate chinese_prc_ci_as null,
constraint [pk_testindexorder] primary key clustered
(
[id] asc
)with (ignore_dup_key = off) on [primary]
) on [primary]
go
/****** 对象: index [ix_testindexorder] 脚本日期: 05/27/2010 09:11:51 ******/
create nonclustered index [ix_testindexorder] on [dbo].[testindexorder]
(
[firstname] asc,
[lastname] asc
)with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on [primary]
go
declare @i int;
declare @random varchar(36);
set @i = 0;
while @i < 100000
begin
set @random = newid();
insert into [testindexorder]
(firstname,lastname,[desc])
values(
substring(@random,1,8),substring(@random,12,8),@random
);
set @i = @i + 1
end
set statistics time on
select * from [testindexorder] where lastname = '6f-4eca-'
select * from [testindexorder] where firstname = 'caabe009'
set statistics time off
4）索引的个数问题
索引提高查询效率是以降低更新、插入、删除的速度为代价的。每当索引列发生变化时都需要对索引数据进行相应的调整。所以一个表上不可以建太多的索引，除非你完全不在乎修改数据的效率。另外sql server本身会对索引的数量和索引的数据长度有限制，具体请参考
5）在必要时重建索引
sql server运行一段时间之后就会形成一些索引碎片，这时候就需要重建索引了，有时候重建索引可以起到意想不到的效果。
查看索引碎片，重建索引，可以通过sql server管理器来重建；也可以通过下面的sql语句来实现：
复制代码 代码如下:
--显示表testindexorder的索引碎片情况
dbcc showcontig(testindexorder)
--重建表的索引
--第一个参数，可以是表名，也可以是表id。
--第二个参数，如果是''，表示影响该表的所有索引。
--第三个参数，填充因子，即索引页的数据填充程度。如果是，表示每一个索引页都全部填满，此时select效率最高，但以后要插入索引时，就得移动后面的所有页，效率很低。如果是，表示使用先前的填充因子值。
dbcc dbreindex(testindexorder，''，)
数据库优化是一门复杂的学问，需要不断的学习实践，积累经验。