基于Oracle數(shù)據(jù)庫的索引優(yōu)化查詢分析

朱勇

摘要：本文對Oracle數(shù)據(jù)庫的查詢技術(shù)進(jìn)行分析與研究，通過索引優(yōu)化查詢技術(shù)達(dá)到提高查詢速度的目的。在對Oracle數(shù)據(jù)表創(chuàng)建合理的索引后，通過對優(yōu)化SQL語句，提升了數(shù)據(jù)庫查詢效率。

關(guān)鍵字：Oracle數(shù)據(jù)庫索引 優(yōu)化查詢 分析

1緒論

作為主流的關(guān)系型數(shù)據(jù)庫，Oracle數(shù)據(jù)庫被廣泛應(yīng)用于各種管理信息系統(tǒng)。Oracle數(shù)據(jù)庫使用SQL語言進(jìn)行數(shù)據(jù)的增刪改查等操作，查詢效率已成為影響數(shù)據(jù)庫性能的關(guān)鍵因素之一，開展查詢優(yōu)化研究已成為研究關(guān)注的問題。

Oracle數(shù)據(jù)庫體系結(jié)構(gòu)決定了數(shù)據(jù)庫系統(tǒng)內(nèi)存、硬件及各系統(tǒng)進(jìn)程和程序的使用，了解Oracle的體系結(jié)構(gòu)有助于分析和研究數(shù)據(jù)庫的優(yōu)化問題。Oracle數(shù)據(jù)庫主要由內(nèi)存結(jié)構(gòu)、進(jìn)程結(jié)構(gòu)以及存儲結(jié)構(gòu)等部分組成。其中，內(nèi)存結(jié)構(gòu)包含系統(tǒng)全局區(qū)域（SGA）以及程序全局區(qū)域（PGA）。進(jìn)程結(jié)構(gòu)包括用戶進(jìn)程和服務(wù)器進(jìn)程以及各種后臺進(jìn)程。存儲結(jié)構(gòu)分為邏輯存儲和物理存儲。SGA對數(shù)據(jù)庫性能影響最為關(guān)鍵，也對查詢優(yōu)化起著重要作用。

Oracle數(shù)據(jù)庫提供了大量的索引選項(xiàng)，建立索引是進(jìn)行查詢優(yōu)化的重要方法。使用索引可以避免全表掃描，并減少查詢所造成的I/O開銷，提高數(shù)據(jù)的查詢效率和數(shù)據(jù)庫的性能。但索引會占據(jù)數(shù)據(jù)庫的空間，根據(jù)實(shí)際的查詢需要建立索引才能達(dá)到優(yōu)化查詢的目的。

2創(chuàng)建合理的索引

索引可以只有一個字段，也可以同時具有多個字段。一般情況下多使用單字段索引，因?yàn)樘嗟膹?fù)合索引在有單個字段索引的情況下是沒有意義的，還會降低數(shù)據(jù)增刪操作時的效率。但當(dāng)復(fù)合索引中包含所有要查詢的列時，Oracle數(shù)據(jù)庫僅查詢索引塊就可以獲取查詢結(jié)果，使用復(fù)合索引比多個單列索引的查詢速率會高得多。

索引將數(shù)據(jù)表中的邏輯值映射到rowID，在查詢時可以快速定位到數(shù)據(jù)的物理位置。但是研究發(fā)現(xiàn)，對一個大型表建立索引后并不能加快查詢的速率，反而影響數(shù)據(jù)庫性能。這是源于SGA數(shù)據(jù)管理方式。Oracle數(shù)據(jù)庫進(jìn)行數(shù)據(jù)塊高速緩存管理時，帶有索引數(shù)據(jù)會比普通的數(shù)據(jù)具有更高的駐留權(quán)限，在內(nèi)存空間使用中，會將普通數(shù)據(jù)先行移除。對含有索引的大型表查詢時，索引數(shù)據(jù)可能用盡數(shù)據(jù)塊的緩存空間，數(shù)據(jù)庫必須進(jìn)行頻繁的磁盤讀寫來獲取數(shù)據(jù)。因此，可對大型表進(jìn)行分區(qū)處理，根據(jù)相應(yīng)的分區(qū)表來建立分區(qū)索引。在對表建立索引時，應(yīng)保證創(chuàng)建索引的字段盡可能出現(xiàn)在Where條件子句中。

有效的利用索引可以很好的提升查詢效率。索引不是越多越好，必須要適當(dāng)使用才能做到事半功倍，不僅提升查詢速率，還能不過度消耗磁盤容量。

3基于索引的SQL語句優(yōu)化

正確地使用索引可以加快數(shù)據(jù)查詢速度，此時還需要良好的SQL語句來支持。接下來本文著重分析建立索引后利用SQL語句來優(yōu)化查詢。

（1） is Null與is not Null

在任何列中如果包含了Null指針，則該列不會被包含在索引中。即使索引建立在多列中，一旦列中出現(xiàn)了空值，該列索引也不會提高性能。而且在Where子句后使用is Null或is not Null時，優(yōu)化器認(rèn)為索引是無效的。 （2）連接列 當(dāng)一個SQL查詢語句中使用連接符進(jìn)行列連接時，此查詢語句中的列即使建立了索引，優(yōu)化器也不會使用該列的索引。例如有查詢語句（1）：

SQL> select * from student where snol‖'‖sage=192110121 '語句（1）

此時在sno字段上有索引，但是未通過索引對表進(jìn)行查詢。查詢語句（1）執(zhí)行109Rows、324Bytes的性能為：SelectStatement操作和Table Access Full操作均耗時28s。

將語句（1）改為語句（2）進(jìn)行查詢：

SQL> select*from student where sno='1921101' andsage=21;語句（2）

此時通過sno字段上帶有的索引進(jìn)行了查詢，提高了查詢速度。查詢語句（2）執(zhí)行109Rows、324Bytes的性能為：SelectStatement操作、Table Access By Index Rowid操作和IndexUnique Scan操作均耗時23s。

（3）模糊查詢like語句中帶有通配符（%）

執(zhí)行一條SQL查詢語句時，like語句后的通配符如果出現(xiàn)在要搜索的詞匯之首，則要查詢的列不使用索引;如果通配符不出現(xiàn)在詞首，則索引是有效的。例如有查詢語句（3）：

SQL> select sno from student where sno like'%92110%';語句（3）

此時查詢不使用索引。查詢語句（3）執(zhí)行109Rows、324Bytes的性能為：Select Statement操作和Table AccessFull操作均耗時31s。

將語句（3）改為語句（4）進(jìn)行查詢：

SQL> select sno from student where sno like''821101%';

語句（4）

此時查詢使用索引。查詢語句（4）執(zhí)行109Rows、324Bytes的性能為：Select Statement操作和Index Unique Scan操作均耗時21s。

（4） order by子句

order by子句可以對返回的查詢結(jié)果進(jìn)行排序。查詢時使用order by子句需保證所查詢的列中含有索引，且索引被使用，否則將會減慢查詢速度。

（5）比較運(yùn)算符

SQL查詢語句where子句中常會使用>、<、=、<>等比較運(yùn)算符。例如，有<>（不等于比較運(yùn)算符）查詢語句（5）和<（小于比較運(yùn)算符）、>（大于比較運(yùn)算符）查詢語句（6）：

SQL> select*from student where sno <>21;語句（5）

SQL> select*from student where sno<21 0r sno>21;語句（6）

語句（5）和語句（6）的運(yùn)行結(jié)果是一樣的，但是語句（6）會比語句（5）的查詢速度更快，因?yàn)镺racle數(shù)據(jù)庫對于語句（6）的查詢方案允許使用索引。

（6） in和exists的使用

使用in子句時，首先進(jìn)行子查詢，把查詢的結(jié)構(gòu)存放在臨時表中，之后再進(jìn)行主查詢，這就加大了系統(tǒng)查詢時間。因此，使用in子句更為直觀，而使用exists則會有更好的效率。例如有m查詢語句（7）：

SQL> select*from student where sno in （select snofrom se where sno=c0021）;語句（7）

查詢語句（7）執(zhí)行600Rows、500Bytes的性能為：SelectStatement操作、TabLe Access Full和Index Range Scan操作均耗時68s。

如果SQL語句where子句的列中帶有索引，使用exists子句時，優(yōu)化器不通過全表掃描而是僅僅根據(jù)索引就可以完成操作。而且當(dāng)使用Exists時，Oracle數(shù)據(jù)庫先進(jìn)行主查詢?nèi)缓筮M(jìn)行子查詢，直到找到第一個與條件相匹配的值為止，節(jié)省了查詢時間。例如有exists查詢語句（8）：

SQL> select*from student where exists （select snofrom se where sno=c0021）;語句（8）

查詢語句（8）執(zhí)行600Rows、500Bytes的性能為：SelectStatement操作、Table Access by Index Rowid，和IndexRange Scan操作均耗時58s。View操作耗時16s。查詢所消耗的時間和資源明顯比查詢語句（7）要少，使用exists可以直接通過索引來獲取數(shù)據(jù)，而使用in則不能，在大型表查詢中使用exists的優(yōu)勢是明顯的。

4結(jié)論

數(shù)據(jù)庫查詢作為數(shù)據(jù)庫一項(xiàng)重要的功能要盡力提高其效率。本文從建立索引、優(yōu)化SQL語句方面對Oracle數(shù)據(jù)庫查詢優(yōu)化進(jìn)行研究，有效提高了查詢效率。

參考文獻(xiàn)

[1]劉鵬.基于關(guān)系數(shù)據(jù)庫的查詢優(yōu)化研究[J]網(wǎng)絡(luò)安全技術(shù)與應(yīng)用，2018 （02）：66-67.

[2]饒淑珍.基于Oracle數(shù)據(jù)庫的幾種常見SQL優(yōu)化策略[J].電腦知識與技術(shù)，2018，l4 （08）：12-13.

[3]賈欽，數(shù)據(jù)庫管理系統(tǒng)中查詢優(yōu)化的設(shè)計和實(shí)現(xiàn)措施[J]電子技術(shù)與軟件工程，2018（15）：142

[4]方芳，謝慧，關(guān)于Oracle數(shù)據(jù)庫的SQL查詢語句優(yōu)化探究[J]，河南科技，2018 （04）：25-26.

[5]蘇現(xiàn)鵬，基于Oracle數(shù)據(jù)庫海量數(shù)據(jù)的查詢優(yōu)化研究[J].現(xiàn)代工業(yè)經(jīng)濟(jì)和信息化，2017，7 （03）：84-85.

[6]. Information Technology - Database Management; New Datafrom Technical University of Munich Illuminate Findings inDatabase Management （Query optimization through the lookinggLass， and what we found running the Join Order Benchmark）[J]. Computers， Networks&Communications， 2018.