2022年如何用索引来提高查询效率 .pdf

《2022年如何用索引来提高查询效率 .pdf》由会员分享，可在线阅读，更多相关《2022年如何用索引来提高查询效率 .pdf（3页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、利用索引提高查询效率如果查询结果仅为一行或很少几行时（高选择性high selectivity)，利用索引进行查询会大大提高效率。相比之下，如果没有索引，查询则只能顺序扫描整个表。在OLTP 环境下，事务处理在很大程度上依赖于索引。只有在表很小时，才会顺序扫描表。系统会根据SQL 语句中的WHERE 子句判断是否使用索引。顺序扫描表会使系统性能受到严重影响。sysmaster中 sysptntab表中的 pf_seqscnas列显示了所进行的顺序扫描。SET EXPLAIN命令同样可以提供关于SQL 语句如何访问数据库中的重要信息。DSS 环境中的应用经常会查询出大量数据（低选择性low se

2、lecviity)，甚至整张表。顺序扫描对于这样的查询更为适合， 因为此时顺序扫描可以利用light scan。light scan缓冲区位于共享内存的虚拟段与驻留段无关。关于 light scan，以后章节中还将详述。建立索引的代价虽然索引可以很大地提高高选择性查询的性能，但维护这些索引是需要付出代价的。以 INSERT语句为例，在进行插入时系统首先将读取被插入表的索引以定位新记录关键字的位置。然后系统在将新记录写入数据页的同时还必须将新索引项写入索引节点。如果导致索引节点分裂，系统则必须多次写索引页与 INSERT语句相似， DELETE 语句也要求读入整个索引以定位索引节点位置，并置上删

3、除标志。在删除索引时还需要处理索引节点合并、整理等问题。在执行 UPDATE语句时，必须首先定位并且删除旧的关键字然后插入新的关键字。所以在UPDATE语句必须两次读取索引。在实际系统中通常把索引的根节点和第一级节点读入共享内存中，但如果需要访问更低层次的索引节点则必须进行磁盘操作。索引类型通常建立分离索引（detached)或基于表达式的索引分片（expression based fragmented）。分离索引和分片索引可以使得索引的extent内页连续，因而能提高性能。而对于不分片的表来说，附加索引（attached)在建立索引时，索引页和数据页交叉存放在一起，因而会增加磁头寻找时间。对

4、于中、小型表应该建立分离索引。对于经常访问的大表应建立基于表达式的分片索引，以减少索引页的数量和提高检索速度。索引分片不宜太多，以免表达式计算开销过大。根据索引的大小，一般可以分为4 片。索引分片不支持轮转法（round robin)。分离式索引与数据页分别存放在不同的数据空间中。例如：CREATE INDEX index1 ON TABLE table_name(col1) in idxldbs; 基于表达式的分片索引在FRAGMENT BY EXPRESSION 子句指定的数据空间中创建。例如：CREATE INDEX index1 on TABLE table_name(col1) FR

5、AGMENT BY EXPRESSION col1=0 and col1=25001 and col1=50001 and col1=750001 and col1= 100000 in idxldbs4; 隐式索引在定义约束条件时如果没有可利用的索引，系统将创建隐式索引。用户不能对隐式索引指定数据空间的位置、分片策略或者填充因子。隐式索引创建在数据库（而非表）所在地数据空间中，这给磁盘管理和性能带来了一些影响。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 3 页 -

6、- - - - - - - - 用户如果需要建立约束条件，最好能先创建与约束条件完全匹配的显式索引然后用ALTER TABLE命令增加约束条件。这样该约束条件就可以利用显式索引而不必再建立隐式索引。例如按如下方式：CREATE TABLE table_name( col1 INTEGER, col2 INTEGER, col3 CHAR(25), ) in tableldbs; CREATE unique INDEX index1 ON TABLE table_name(col1) in idxldbs; ALTER TABLE table_name ADD CONSTRAINT PRIMAR

7、YKEY(col1); 而不要按如下方式：CREATE TABLE table_name( col1 INTEGER, col2 INTEGER, col3 CHAR(25), PRIMARY KEY (col1 ） ) in tableldbs; FILLACTOR(填充因子）对于 SELECT 操作，将FILLFACTOR 设为 100 对于 SELECT 和 DELETE操作将 FILLFACTOR 设为 100 对于 INSERT 和 UPDATE操作将 FILLFACTOR设为 50 到 70 CREATE INDEX index1 ON TABLE table_name(col1)

8、 in idx1 dbs FILLFACTOR 70; 填充因子决定了在建立索引时每一索引页的填充度。该参数是可以改变的。参数FILLFACTOR缺省值为90 。如果只是改变某个索引的设置，请使用CREATE INDEX的 FILLFACTOR子句。设置较大的填充因子可以使索引更紧凑、使缓冲区更有效同时也可以减少检索记录时读取的页数。对于只读表应将 FILLFACTOR设置为 100 。对于只执行读和操作的表，如果将FILLFACTOR设置为 100, 在删除记录时可以减少合并索引节点、整理索引树的可能性。对于有大量插入和更新操作的表，应该将FILLFACTOR设置为50 到 70 。由于填充

9、因子较小，在插入和更新操作时将延迟索引节点（页面）的分裂，从而提高系统性能。有如下的SQL FILLFACTOR例句：CREATE INDEX index1 ON TABLE table_name(col1) in idxldbs FILLFACTOR 70; 建立索引的步骤建立索引必须遵循如下步骤：确定需要建立的索引决定索引的类型，分离索引还是分片索引。如果是分片索引，确定表达式为每一索引确定填充因子计算索引所需空间决定索引数据空间在磁盘上的位置为索引创建数据空间定髓临时数据空间大小和位置。临时数据空间最好能分布存储。利用 DBSPACETEMP 设置临时数据空间的大小，利用onspace命

10、令的 -t 选项创建临时数据空间设置环境变量PDQPRIORITY 和 PSORT_NPROCS 创建相应的配置文件。其中各选项的最优设置参见并行排序和载入环境配置参数建立索引时参数的优化设置与上一章载入环境中的设置相同。在此作一简单回顾：NUMCPUVPS 设置为 CPU 数目名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 3 页 - - - - - - - - - BUFFERS 设置尽量多的缓冲区。初始时应为缓冲区分配最多至25 的内存。 ? SHMVIRTSIZE

11、 赋予初始段最大值。最多至可用内存的75 CKPTINTVL 3000。由物理日志决定何时生成检查点。LRUS 一个 LRU 队列对含500 700 个缓冲并允许LRU 队列对最大至128 LUR_MAX_DIRTY 设置为 80 LUR_MIN_dirty 设置为 70 ? RA_PAGES 设置为 128 ，RA_THRESHOLD 设置为 120 DBSPACETEMP 设置多个大小相同的临时数据空间分布在不同的设备上DS_TOTAL_MEMORY 90*SHMVIRTSIZE DS_MAX_SCANS 待建索引表的最大分片数下面是对环境变量的简单回顾：PSORT_NPROCS 设置为

12、CPU 数目，最大至10 PDQPRIORITY 设置为 100 数据聚集如果经常根据索引从表中读取大量的数据，那么最好对这些数据建立聚集。通过建立聚集可以减少读取页的数量，缩短检索时间，并可以充分利用预读功能顺序扫描数据。数据的聚集通过创建聚集索引（clustered index)完成。利用ALTER INDEX .TO CLUSTER 语句可以将原有索引改为聚集索引，原有索引中的数据记录顺序将作重排。如果直接创建聚集索引，系统也会将数据记录排序。请注意，在ATLTER INDEX .TO CLUSTER 和 CREATE CLUSTER INDEX 语句执行后，系统将建立新表并删除旧表。所以必须有足够的空间来容纳新旧两张表。如果可能，在载入数据前先对其按索引顺序排序，这样建立索引时不必设置CLUSTER子句，从而不必对数据进行聚集操作，避免建新表删旧表过程的开销。2013-3-1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 3 页 - - - - - - - - -