建立Clustered索引原则:
1、Primary key,但必须满足以下条件:A、常用于Where条件,B、插入记录时的字段值是随机的(不能用于顺序增加的字段,否则将增加最后一个数据页的lock等待时间)
2、用于范围查询的字段,如 col1 between 100 and 200; col12>;62 and <70;
3、用于order by的字段
4、不经常改变(update)的字段
5、用于连接条件的字段
其他有关建立索引的原则:
1、如果索引是唯一的,将其定义为Unique
2、如果使用引用表索引(foreign key ... reference),被引用的列必须建立唯一索引
3、如果建立索引的表有频繁的插入(Insert)操作,使用fillfactor减少数据页的分裂、提高并发性能并减少死锁
4、如果在一个只读表上建立索引,将其fillfactor设为100以尽可能压缩数据和索引空间
5、尽可能减少单个索引的长度
6、在满足使用的前提下,尽可能使用小容量的数据类型(如用numeric代替char)
7、数值(numeric)的比较在内部操作上比字符(string)略快
8、变长字符串(varchar)和二进制(binary)类型比定长(fixed-length)类型需要更多的系统负载
9、只要可能,使用定长(fixed-length)、非空(non-null)、短(short)的数据类型字段作为索引
10、连接条件两端的索引必须是兼容的(最好是同一类型),如果需要进行数据转换的话,连接索引将不能发挥作用
建立复合字段索引的原则
1、当条件内不包含复合索引的第一个字段时,有两种可能:A、当所有select的字段均包含在复合索引内时,做所有索引页的遍历查询,B、当select中包含非复合索引字段时,做全表数据页的遍历查询,既此时索引对检索无效。
2、复合索引优点:A、提供索引覆盖(index covering)的机会,但仅限于所有查询(select)字段均包含在索引内,B、当查询条件使用到复合索引的所有字段时,所需要时间少于这些字段建立单独索引的情况,C、复合索引有利于强制多个属性合并后的唯一性限制
3、复合索引缺点:A、需要更多的索引空间,减少每个索引页包含的索引记录数,并增加索引页数目,B、对复合索引中任何一个字段的变化(update或insert)都会更新索引,因此复合索引必须选择不进行变化的字段组成
4、差的复合索引特点:A、复合索引长度接近于记录长度,B、复合索引中只有少量字段用于查询
SYBASE建立索引的原则
补充一点:
--创建非聚集索引
1.满足查询条件的数据不超过20%
2.能实现 index covering
3.用于集函数、连接、group by和order by的列
4.要权衡索引对查询速度的加快与降低修改速度之间的利弊
--删除影响性能的索引
如果一个应用在白天执行数据修改,在夜间生成报表,
就需要在早晨删除索引,在晚上再把索引重建起来。
另外许多系统设计者创建许多优化器很少使用的索引,
可以根据showplan,把没有用的索引删除。
1、Primary key,但必须满足以下条件:A、常用于Where条件,B、插入记录时的字段值是随机的(不能用于顺序增加的字段,否则将增加最后一个数据页的lock等待时间)
2、用于范围查询的字段,如 col1 between 100 and 200; col12>;62 and <70;
3、用于order by的字段
4、不经常改变(update)的字段
5、用于连接条件的字段
其他有关建立索引的原则:
1、如果索引是唯一的,将其定义为Unique
2、如果使用引用表索引(foreign key ... reference),被引用的列必须建立唯一索引
3、如果建立索引的表有频繁的插入(Insert)操作,使用fillfactor减少数据页的分裂、提高并发性能并减少死锁
4、如果在一个只读表上建立索引,将其fillfactor设为100以尽可能压缩数据和索引空间
5、尽可能减少单个索引的长度
6、在满足使用的前提下,尽可能使用小容量的数据类型(如用numeric代替char)
7、数值(numeric)的比较在内部操作上比字符(string)略快
8、变长字符串(varchar)和二进制(binary)类型比定长(fixed-length)类型需要更多的系统负载
9、只要可能,使用定长(fixed-length)、非空(non-null)、短(short)的数据类型字段作为索引
10、连接条件两端的索引必须是兼容的(最好是同一类型),如果需要进行数据转换的话,连接索引将不能发挥作用
建立复合字段索引的原则
1、当条件内不包含复合索引的第一个字段时,有两种可能:A、当所有select的字段均包含在复合索引内时,做所有索引页的遍历查询,B、当select中包含非复合索引字段时,做全表数据页的遍历查询,既此时索引对检索无效。
2、复合索引优点:A、提供索引覆盖(index covering)的机会,但仅限于所有查询(select)字段均包含在索引内,B、当查询条件使用到复合索引的所有字段时,所需要时间少于这些字段建立单独索引的情况,C、复合索引有利于强制多个属性合并后的唯一性限制
3、复合索引缺点:A、需要更多的索引空间,减少每个索引页包含的索引记录数,并增加索引页数目,B、对复合索引中任何一个字段的变化(update或insert)都会更新索引,因此复合索引必须选择不进行变化的字段组成
4、差的复合索引特点:A、复合索引长度接近于记录长度,B、复合索引中只有少量字段用于查询
![](http://www.lslnet.com/images/icon_new.gif)
补充一点:
--创建非聚集索引
1.满足查询条件的数据不超过20%
2.能实现 index covering
3.用于集函数、连接、group by和order by的列
4.要权衡索引对查询速度的加快与降低修改速度之间的利弊
--删除影响性能的索引
如果一个应用在白天执行数据修改,在夜间生成报表,
就需要在早晨删除索引,在晚上再把索引重建起来。
另外许多系统设计者创建许多优化器很少使用的索引,
可以根据showplan,把没有用的索引删除。