数据库创建索引的必要性
数据库创建索引的必要性
⼀、为什么要创建索引
平常我们观察项⽬的数据库（⽐较厉害的⼤神创建的），会有⼀些索引，对于这个，⼩⽩我表⽰不太懂，于是看了⼀些⽂档和博客，发现索引是⼀些数据表必须具备的，⽐如⼀些查询次数较多的字段，⼀些经常排序的字段，⼀些经常当作where条件的字段，以下参数⼀些优点：
1，通过创建唯⼀性索引，可以保证数据库表中每⼀⾏数据的唯⼀性。
2，可以⼤⼤加快数据的检索速度，这也是创建索引的最主要的原因。
3，可以加速表和表之间的连接，特别是在实现数据的参考完整性⽅⾯特别有意义。
4，在使⽤分组和排序⼦句进⾏数据检索时，同样可以显著减少查询中分组和排序的时间。
5，通过使⽤索引，可以在查询的过程中，使⽤优化隐藏器，提⾼系统的性能。
也许会有⼈要问：增加索引有如此多的优点，为什么不对表中的每⼀个列创建⼀个索引呢？这种想法固然有其合理性，然⽽也有其⽚⾯性。虽然，索引有许多优点， 但是，为表中的每⼀个列都增加索引，是⾮常不明智的。这是因为，增加索引也有许多不利的⼀个⽅⾯。
1，创建索引和维护索引要耗费时间，这种时间随着数据 量的增加⽽增加。
2，索引需要占物理空间，除了数据表占数据空间之外，每⼀个索引还要占⼀定的物理空间，如果要建⽴聚簇索引，那么需要的空间就会更⼤。
3，当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
⼆、哪些需要创建索引
1，在作为主键的列上，强制该列的唯⼀性和组织表中数据的排列结构；
2，在经常⽤在连接的列上，这 些列主要是⼀些外键，可以加快连接的速度；
3，在经常需要根据范围进⾏搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
4，在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利⽤索引的排序，加快排序查询时间；
5，在经常使⽤在WHERE⼦句中的列上⾯创建索引，加快条件的判断速度。
三、哪些不能创建索引
1，对于那些在查询中很少使⽤或者参考的列不应该创建索引。这是因 为，既然这些列很少使⽤到，因此有索引或者⽆索引，并不能提⾼查询速度。相反，由于增加了索引，反⽽降低了系统的维护速度和增⼤了空间需求。
2，对于那 些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如⼈事表的性别列，在查询的结果中，结果集的数据⾏占了表中数据⾏的很⼤⽐ 例，即需要在表中搜索的数据⾏的⽐例很⼤。增加索引，并不能明显加快检索速度。
3，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当⼤，要么取值很少。
4，当修改性能远远⼤于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相⽭盾的。当增加索引时，会提⾼检索性能，但是会降低修改性能。当减少索引时，会提⾼修改性能，降低检索性能。因 此，当修改性能远远⼤于检索性能时，不应该创建索引。
四、让你的sql⽐别⼈更快的⽅法
举个列⼦：
⼀、不合理的索引设计会让你的sql变慢
表record有620000⾏，试看在不同的索引下，下⾯⼏个 SQL的运⾏情况：
1、在date上建有⼀⾮个集索引
select count(*) from record where date >
'19991201' and date < '19991214'and amount >
2000 (25秒)
select date,sum(amount) from record group by date
(55秒)
select count(*) from record where date >
'19990901' and place in ('BJ','SH') (27秒)
---- 分析：
-
---date上有⼤量的重复值，在⾮集索引下，数据在物理上随机存放在数据页上，在
范围查时，必须执⾏⼀次表扫描才能到这⼀范围内的全部⾏。
2、在date上的⼀个集索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 （14秒）
select date,sum(amount) from record group by date
（28秒）
select count(*) from record where date >
'19990901' and place in ('BJ','SH')（14秒）
---- 分析：
-
--- 在集索引下，数据在物理上按顺序在数据页上，重复值也排列在⼀起，因⽽在范
围查时，可以先到这个范围的起末点，且只在这个范围内扫描数据页，避免了⼤范
围扫描，提⾼了查询速度。
遗嘱的效力3、在place，date，amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 （26秒）
select date,sum(amount) from record group by date
（27秒）
select count(*) from record where date >
'19990901' and place in ('BJ', 'SH')（< 1秒）
-
--- 分析：
---- 这是⼀个不很合理的组合索引，因为它的前导列是place，第⼀和第⼆条SQL没有引
⽤place，因此也没有利⽤上索引；第三个SQL使⽤了place，且引⽤的所有列都包含在组
合索引中，形成了索引覆盖，所以它的速度是⾮常快的。
4、在date，place，amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000(< 1秒)
select date,sum(amount) from record group by date
（11秒）
select count(*) from record where date >
'19990901' and place in ('BJ','SH')（< 1秒）
---- 分析：
---- 这是⼀个合理的组合索引。它将date作为前导列，使每个SQL都可以利⽤索引，并
且在第⼀和第三个SQL中形成了索引覆盖，因⽽性能达到了最优。
公众平台登录页
5、总结：
---- 缺省情况下建⽴的索引是⾮集索引，但有时它并不是最佳的；合理的索引设计要
建⽴在对各种查询的分析和预测上。⼀般来说：
---- ①.有⼤量重复值、且经常有范围查询
（between, >,< ，>=,< =）和order by
10条感恩句子、group by发⽣的列，可考虑建⽴集索引；
---- ②.经常同时存取多列，且每列都含有重复值可考虑建⽴组合索引；
-
--- ③.组合索引要尽量使关键查询形成索引覆盖，其前导列⼀定是使⽤最频繁的列。⼆、不充份的连接条件：
---- 例：表card有7896⾏，在card_no上有⼀个⾮聚集索引，表account有191122⾏，在
account_no上有⼀个⾮聚集索引，试看在不同的表连接条件下，两个SQL的执⾏情况： select sum(a.amount) from account a,  card b where a.card_no = b.card_no（20秒）
---- 将SQL改为：
select sum(a.amount) from account a,
card b where a.card_no = b.card_no and a.
account_no=b.account_no（< 1秒）
---- 分析：
---- 在第⼀个连接条件下，最佳查询⽅案是将account作外层表，card作内层表，利⽤
card上的索引，其I/O次数可由以下公式估算为：
---- 外层表account上的22541页+（外层表account的191122⾏*内层表card上对应外层
表第⼀⾏所要查的3页）=595907次I/O
---- 在第⼆个连接条件下，最佳查询⽅案是将card作外层表，account作内层表，利⽤
account上的索引，其I/O次数可由以下公式估算为：
---- 外层表card上的1944页+（外层表card的7896⾏*内层表account上对应外层表每⼀
⾏所要查的4页）= 33528次I/O
---- 可见，只有充份的连接条件，真正的最佳⽅案才会被执⾏。
---- 总结：
---- 1.多表操作在被实际执⾏前，查询优化器会根据连接条件，列出⼏组可能的连接⽅
案并从中出系统开销最⼩的最佳⽅案。连接条件要充份考虑带有索引的表、⾏数多的
表；内外表的选择可由公式：外层表中的匹配⾏数*内层表中每⼀次查的次数确定，乘
积最⼩为最佳⽅案。
---- 2.查看执⾏⽅案的⽅法-- ⽤set showplanon，打开showplan选项，就可以看到连
接顺序、使⽤何种索引的信息；想看更详细的信息，需⽤sa⾓⾊执⾏dbcc(3604,310,30
2)。
⼆、优化where⼦句
---- 1.例：下列SQL条件语句中的列都建有恰当的索引，但执⾏速度却⾮常慢：
select * from record where
substring(card_no,1,4)='5378'(13秒)
select * from record where
amount/30< 1000（11秒）
select * from record where
convert(char(10),date,112)='19991201'（10秒）
---- 分析：
---- where⼦句中对列的任何操作结果都是在SQL运⾏时逐列计算得到的，因此它不得不
进⾏表搜索，⽽没有使⽤该列上⾯的索引；如果这些结果在查询编译时就能得到，那么
就可以被SQL优化器优化，使⽤索引，避免表搜索，因此将SQL重写成下⾯这样：
select * from record where card_no like
'5378%'（< 1秒）
select * from record where amount
< 1000*30（< 1秒）
select * from record where date= '1999/12/01'
（< 1秒）
-
--- 你会发现SQL明显快起来！
---- 2.例：表stuff有200000⾏，id_no上有⾮集索引，请看下⾯这个SQL：
select count(*) from stuff where id_no in('0','1')
（23秒）
---- 分析：
---- where条件中的'in'在逻辑上相当于'or'，所以会将in ('0','1')转化
为id_no ='0' or id_no='1'来执⾏。我们期望它会根据每个or⼦句分别查，再将结果
相加，这样可以利⽤id_no上的索引；但实际上（根据showplan），它却采⽤了"OR策略"
，即先取出满⾜每个or⼦句的⾏，存⼊临时数据库的⼯作表中，再建⽴以去掉
重复⾏，最后从这个临时表中计算结果。因此，实际过程没有利⽤id_no上索引，并且完
立冬当天吃什么传统食物成时间还要受tempdb数据库性能的影响。
-
--- 实践证明，表的⾏数越多，⼯作表的性能就越差，当stuff有620000⾏时，执⾏时
间竟达到220秒！还不如将or⼦句分开：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
---- 得到两个结果，再作⼀次加法合算。因为每句都使⽤了索引，执⾏时间只有3秒，
在620000⾏下，时间也只有4秒。或者，⽤更好的⽅法，写⼀个简单的存储过程(存储过程是⼀组为了完成特定功能的SQL语句集，是利⽤SQL Server所提供的Transact-SQL语⾔所编写的程序。经编译后存储在数据库中。存储过程是数据库中⼀个重要的对象。)：
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no='0'
select @b=count(*) from stuff where id_no='1'
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d
五、创建索引的⽅法
创建索引有多种⽅法，这些⽅法包括直接创建索引的⽅法和间接创建索引的⽅法。直接创建索引，例如使⽤CREATE INDEX语句或者使⽤创建索引向导，间接创建索引，例如在表中定义主键约束或者唯⼀性键约束时，同时也创建了索引。虽然，这两种⽅法都可以创建索引，但是，它们创建索引的具体
内容是有区别的。
使⽤CREATE INDEX语句或者使⽤创建索引向导来创建索引，这是最基本的索引创建⽅式，并且这种⽅法最具有柔性，可以定制创建出符合⾃⼰需要的索引。在使⽤这种⽅式创建索引时，可以使⽤许多选项，例如指定数据页的充满度、进⾏排序、整理统计信息等，这样可以优化索引。使⽤这种⽅法，可以指定索引的类型、唯⼀性和复合性，也就是说，既可以创建聚簇索引，也可以创建⾮聚簇索引，既可以在⼀个列上创建索引，也可以在两个或者两个以上的列上创建索引。教师自查自纠材料
通过定义主键约束或者唯⼀性键约束，也可以间接创建索引。主键约束是⼀种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统⾃动创建了⼀个唯⼀性的聚簇索引。虽然，在逻辑上，主键约束是⼀种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯⼀性的聚簇索引。换句话说，在物理实现上，不存在主键约束，⽽只存在唯⼀性的聚簇索引。同样，在创建唯⼀性键约束时，也同时创建了索引，这种索引则是唯⼀性的⾮聚簇索引。因此，当使⽤约束创建索引时，索引的类型和特征基本上都已经确定了，由⽤户定制的余地⽐较⼩。
当在表上定义主键或者唯⼀性键约束时，如果表中已经有了使⽤CREATE INDEX语句创建的标准索引时，那么主键约束或者唯⼀性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯⼀性键约束创建的索引的优先级⾼于使⽤CREATE INDEX语句创建的索引。
六、索引的特征
索引有两个特征，即唯⼀性索引和复合索引。滴滴代驾司机注册
唯⼀性索引保证在索引列中的全部数据是唯⼀的，不会包含冗余数据。如果表中已经有⼀个主键约束或者唯⼀性键约束，那么当创建表或者修改表时，SQL Server⾃动创建⼀个唯⼀性索引。然⽽，如果必须保证唯⼀性，那么应该创建主键约束或者唯⼀性键约束，⽽不是创建⼀个唯⼀性索引。当创建唯⼀性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯⼀性键约束时，SQL Server⾃动创建⼀个唯⼀性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使⽤插⼊语句插⼊数据或者使⽤修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执⾏，并且返回⼀个错误消息；确保表中的每⼀⾏数据都有⼀个唯⼀值，这样可以确保每⼀个实体都可以唯⼀确认；只能在可以保证实体完整性的列上创建唯⼀性索引，例如，不能在⼈事表中的姓名列上创建唯⼀性索引，因为⼈们可以有相同的姓名。
复合索引就是⼀个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为⼀个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成⼀个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来⾃同⼀个表中，不能跨表建⽴复合列；在复合索引中，列的排
列顺序是⾮常重要的，因此要认真排列列的顺序，原则上，应该⾸先定义最唯⼀的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使⽤复合索引，查询语句中的WHERE⼦句必须参考复合索引中第⼀个列；当表中有多个关键列时，复合索引是⾮常有⽤的；使⽤复合索引可以提⾼查询性能，减少在⼀个表中所创建的索引数量。
PS. 由于本⼈是⼩⽩，⽂中谬误较多，还望⼤家批评指正，⽂章内容主要参考上⾯这篇博⽂，有些内容⽆法注明第⼀源地址，如有版权问题请站内信联系，第⼀时间处理。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。