如何用索引来提高查询效率.doc

利用索引提高查询效率利用索引提高查询效率 如果查询结果仅为一行或很少几行时 高选择性 high selectivity 利用索引进行查询会大大提高效率 相 比之下 如果没有索引 查询则只能顺序扫描整个表 在 OLTP 环境下 事务处理在很大程度上依赖于索引 只有在表很小时 才会顺序扫描表 系统会根据 SQL 语句中的 WHERE 子句判断是否使用索引 顺序扫描表会 使系统性能受到严重影响 sysmaster 中 sysptntab 表中的 pf seqscnas 列显示了所进行的顺序扫描 SET EXPLAIN 命令同样可以提供关于 SQL 语句如何访问数据库中的重要信息 DSS 环境中的应用经常会查询出大量数据 低选择性 low selecviity 甚至整张表 顺序扫描对于这样的 查询更为适合 因为此时顺序扫描可以利用 light scan light scan 缓冲区位于共享内存的虚拟段与驻留段无 关 关于 light scan 以后章节中还将详述 建立索引的代价建立索引的代价 虽然索引可以很大地提高高选择性查询的性能 但维护这些索引是需要付出代价的 以 INSERT 语句为例 在进行插入时系统首先将读取被插入表的索引以定位新记录关键字的位置 然后系统在将新记录写入数据页的 同时还必须将新索引项写入索引节点 如果导致索引节点分裂 系统则必须多次写索引页 与 INSERT 语句相似 DELETE 语句也要求读入整个索引以定位索引节点位置 并置上删除标志 在删除索 引时还需要处理索引节点合并 整理等问题 在执行 UPDATE 语句时 必须首先定位并且删除旧的关键字然后插入新的关键字 所以在 UPDATE 语句必 须两次读取索引 在实际系统中通常把索引的根节点和第一级节点读入共享内存中 但如果需要访问更低层次的索引节点则必 须进行磁盘操作 索引类型索引类型 通常建立分离索引 detached 或基于表达式的索引分片 expression based fragmented 分离索引 和分片索引可以使得索引的 extent 内页连续 因而能提高性能 而对于不分片的表来说 附加索引 attached 在建立索引时 索引页和数据页交叉存放在一起 因而会增加磁头寻找时间 对于中 小型表应该建立分离索引 对于经常访问的大表应建立基于表达式的分片索引 以减少索引页的数 量和提高检索速度 索引分片不宜太多 以免表达式计算开销过大 根据索引的大小 一般可以分为 4 片 索 引分片不支持轮转法 round robin 分离式索引与数据页分别存放在不同的数据空间中 例如 CREATE INDEX index1 ON TABLE table name col1 in idxldbs 基于表达式的分片索引在 FRAGMENT BY EXPRESSION 子句指定的数据空间中创建 例如 CREATE INDEX index1 on TABLE table name col1 FRAGMENT BY EXPRESSION col1 0 and col1 25001 and col1 50001 and col1 750001 and col1 100000 in idxldbs4 隐式索引隐式索引 在定义约束条件时如果没有可利用的索引 系统将创建隐式索引 用户不能对隐式索引指定数据空间的位置 分片策略或者填充因子 隐式索引创建在数据库 而非表 所在地数据空间中 这给磁盘管理和性能带来了一 些影响 用户如果需要建立约束条件 最好能先创建与约束条件完全匹配的显式索引然后用 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 PRIMARYKEY 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 in idx1 dbs FILLFACTOR 70 填充因子决定了在建立索引时每一索引页的填充度 该参数是可以改变的 参数 FILLFACTOR 缺省值为 90 如果只是改变某个索引的设置 请使用 CREATE INDEX 的 FILLFACTOR 子句 设置较大的填充因子可以使索引更紧凑 使缓冲区更有效同时也可以减少检索记录时读取的页数 对于只读 表应将 FILLFACTOR 设置为 100 对于只执行读和操作的表 如果将 FILLFACTOR 设置为 100 在删除记录时可以减少合并索引节点 整理索 引树的可能性 对于有大量插入和更新操作的表 应该将 FILLFACTOR 设置为 50 到 70 由于填充因子较小 在插入和更 新操作时将延迟索引节点 页面 的分裂 从而提高系统性能 有如下的 SQL FILLFACTOR 例句 CREATE INDEX index1 ON TABLE table name col1 in idxldbs FILLFACTOR 70 建立索引的步骤建立索引的步骤 建立索引必须遵循如下步骤 确定需要建立的索引 决定索引的类型 分离索引还是分片索引 如果是分片索引 确定表达式 为每一索引确定填充因子 计算索引所需空间 决定索引数据空间在磁盘上的位置 为索引创建数据空间 定髓临时数据空间大小和位置 临时数据空间最好能分布存储 利用 DBSPACETEMP 设置临时数据空间的大小 利用 onspace 命令的 t 选项创建临时数据空间 设置环境变量 PDQPRIORITY 和 PSORT NPROCS 创建相应的配置文件 其中各选项的最优设置参见并行排序和载入环境 配置参数配置参数 建立索引时参数的优化设置与上一章载入环境中的设置相同 在此作一简单回顾 NUMCPUVPS 设置为 CPU 数目 BUFFERS 设置尽量多的缓冲区 初始时应为缓冲区分配最多至 25 的内存 SHMVIRTSIZE 赋予初始段最大值 最多至可用内存的 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 设置为 CPU 数目 最大至 10 PDQPRIORITY 设置为 100 数据聚集数据聚集 如果经常根据索引从表中读取大量的数据 那么最好对这些数据建立聚集 通过建立聚集可以减少读取页的 数量 缩短检索时间 并可以充分利用预读功能顺序扫描数据 数据的聚集通过创建聚集索引 clustered index 完成 利用 ALTER INDEX TO CLUSTER 语句可以 将原有索引改为聚集索引 原有索引中的数据记录顺序将作重排 如果直接创建聚集索引 系统也会将数据记