Sybase 索引的使用和优化
word版本下载
在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。在良好的数据库设计基础上，能有效地使用索引是SQLServer取得高性能的基础，SQLServer采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。所以如果建 立了合理的索引，优化器就能利用索引加速数据的查询过程。但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQLServer应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。本文就SQLServer索引的性能问题进行了一些分析和实践。 一、聚簇索引(clusteredindexes)的使用 聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想 是：
1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。 2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。 3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。 4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。 5、选择聚簇索引应基于where子句和连接操作的类型。
聚簇索引的侯选列是：
● 主键列,该列在where子句中使用并且插入是随机的。
●按范围存取的列，如pri_order> 100 and pri_order < 200 。
● 在group by或order by中使用的列。
● 不经常修改的列。
● 在连接操作中使用的列。
二、非聚簇索引(nonclusteredindexes)的使用 SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺 序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：
● 索引需要使用多少空间。
● 合适的列是否稳定。
● 索引键是如何选择的，扫描效果是否更佳。
● 是否有许多重复值。 对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。
非聚簇索引常被用在以下情况:
● 某列常用于集合函数(如Sum,....)。
● 某列常用于join,orderby,group by。
● 查寻出的数据不超过表中数据量的20%。
三、覆盖索引(coveringindexes)的使用 覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访 问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。
四、索引的选择技术p_detail是住房公积金管理系统中记录个人明细的表，有890000行，观察在不同索引下的查询运行效果，测试在C/S环境下进行，客户机是IBM PII350(内存64M),服务器是DECAlpha1000A(内存128M),数据库为SYBASE11.0.3。
1、 selectcount(*) from p_detail where op_date>’19990101’ and op_date<’19991231’ and pri_surplus1>300
2、 selectcount(*),sum(pri_surplus1) from p_detail where op_date>’19990101’ and pay_month between‘199908’ and ’199912’
不建任何索引 查询1 1分15秒 查询2 1分7秒
在op_date上建非聚簇索引 查询1 57秒 查询2 57秒
在op_date上建聚簇索引 查询1 <1秒 查询2 52秒
在pay_month、op_date、pri_surplus1上建索引 查询1 34秒 查询2 <1秒 在op_date、pay_month、pri_surplus1上建索引查询1 <1秒 查询2 <1秒 从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和 join表达式中。
一般来说建立索引的思路是：
(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。
(2)、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。 (3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。
(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。
(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。
(6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索 引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。
五、索引的维护 上面讲到,某些不合适的索引影响到SQLServer的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将 会影响到索引的使用。这时 需要用户自己来维护索引。索引的维护包括： 1、重建索引 随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：
(1)、数据和使用模式大幅度变化。
(2)、排序的顺序发生改变。
(3)、要进行大量插入操作或已经完成。
(4)、使用大块I/O的查询的磁盘读次数比预料的要多。
(5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。
(6)、dbcc检查出索引有问题。 当重建聚簇索引时,这张表的所有非聚簇索引将被重建.
2、索引统计信息的更新 当在一个包含数据的表上创建索引的时候，SQLServer会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行updatestatistics命令：
(1)、数据行的插入和删除修改了数据的分布。
(2)、对用truncatetable删除数据的表上增加数据行。
(3)、修改索引列的值。 六、结束语 实践表明，不恰当的索引不但于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。
例如下面情况下 建立的索引是不恰当的：
● 在查询中很少或从不引用的列不会受益于索引，因为索引很少或从来不必搜索基于这些列的行。
● 只有两个或三个值的列，如男性和女性(是或否)，从不会从索引中得到好处。另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索
建立Clustered索引原则：
1、Primarykey，但必须满足以下条件：A、常用于Where条件，B、插入记录时的字段值是随机的（不能用于顺序增加的字段，否则将增加最后一个数据页的lock等待时间）
2、用于范围查询的字段，如 col1 between 100 and 200; col12>;62 and <70;
3、用于orderby的字段
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建立索引的原则 |
建立索引
create unique clustered index code1thidx1 on code_1th(Code1,Name,UnitNo1,Used)
on segrun
Unique: 建立唯一性索引
Clustered : 建立聚集索引，使得数据行的逻辑顺序与物理顺序一致，查询速度较快；每个表仅允许建立一个唯一性索引。(非聚集索引nonclustered)
On segrun:将索引放在segrun上
x 删除索引: dropindex code_1th.code1thidx1
一、APL表的索引
1、Clustering Index：
root level：only one page
intermediate level:
leaf level: leaf page就是数据页（所以查询计划通过索引就可以读数据）
2、Noclustering Index（heap table）:
leaf page存放数据页的rowid（所以查询计划通过索引只能拿到key，再去读数据）
二、DOL表的索引
Clustering Index和NonClustering index与在APL表上的非聚集索引结构一样。（所以查询计划通过索引只能拿到key，再去读数据）
索引提示：
1、索引失灵：
当使用>条件查询返回的记录数很大时，用索引查询比全表扫描效率要差很多。
在连接查询中，连接字段类型最好兼容或一致，否则索引失灵
2、索引KEY数据类型优先级：
int>char>varchar
3、插入大量数据时，先删除索引，数据插入完成后，重建索引。
4、创建索引，加大排序缓存（number of sort buffers）。
5、创建索引顺序：群聚索引>非群聚索引
6、创建大量索引后，最好备份数据库（dump database），事务日志会记录index创建事务等信息，使数据库通过事务日志恢复可能比较慢。
7、创建群聚索引过程中，表处于独占锁模式，创建非群聚索引过程中，表处于共享锁，可以select操作。
8、修改表影响了索引中列，需要修改索引列的统计数据，例如：修改authors表的所有索引的所有列统计，update index statistics authors
用SQL建立索引
为了给一个表建立索引，启动任务栏SQL Sever程序组中的ISQL/w程序。进入查询窗口后，输入下面的语句：
CREATE INDEX mycolumn_index ON mytable (myclumn)
这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。
注意：
在本书中你执行任何SQL语句，都会收到如下的信息：
This command did not return data,and it did not return any rows
这说明该语句执行成功了。
索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性）
如果你需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索引后，你可以用下面的SQL语句删除它：
DROP INDEX mytable.mycolumn_index
注意在DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表mytable的索引。
要建立一个聚簇索引，可以使用关键字CLUSTERED。）记住一个表只能有一个聚簇索引。（这里有一个如何对一个表建立聚簇索引的例子：
CREATE CLUSTERED INDEX mycolumn_clust_indexON mytable(mycolumn)
如果表中有重复的记录，当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索引；你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可：
CREATE CLUSTERED INDEX mycolumn_cindex ONmytable(mycolumn) vj1fd}u\"
WITH ALLOW_DUP_ROW
这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。
要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子：
CREATE UNIQUE COUSTERED INDEXmyclumn_cindex ON mytable(mycolumn)
这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。
最后，要建立一个对多个字段的索引──复合索引──在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引：
CREATE INDEX name_index ONusername(firstname,lastname)
这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对16个字段进行索引。
用事务管理器建立索引
用事务管理器建立索引比用SQL语句容易的多。使用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。
使用事务管理器你可以用两种方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。
要用Manage Tables 窗口建立一个新索引，单击按钮Advanced Options(它看起来象一个前面有一加号的表)。这样就打开了AdvancedOptions对话框。这个对话框有一部分标名为Primary Key
要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后，索引会自动被建立。在Manage Tables窗口中的字段名旁边，会出现一把钥匙。
你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索引强制一个字段成为唯一值字段。
要建立没有这些限制的索引，你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes，打开Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。要建立一个新索引，从Index下拉框中选择NewIndex.，然后就可以选择要对之建立索引的字段。单击按钮Add，把字段加人到索引中。
你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮Build，建立该索引。
