基于Oracle數據庫的索引優化查詢分析

朱勇

摘要：本文對Oracle數據庫的查詢技術進行分析與研究，通過索引優化查詢技術達到提高查詢速度的目的。在對Oracle數據表創建合理的索引后，通過對優化SQL語句，提升了數據庫查詢效率。

關鍵字：Oracle數據庫索引 優化查詢 分析

1緒論

作為主流的關系型數據庫，Oracle數據庫被廣泛應用于各種管理信息系統。Oracle數據庫使用SQL語言進行數據的增刪改查等操作，查詢效率已成為影響數據庫性能的關鍵因素之一，開展查詢優化研究已成為研究關注的問題。

Oracle數據庫體系結構決定了數據庫系統內存、硬件及各系統進程和程序的使用，了解Oracle的體系結構有助于分析和研究數據庫的優化問題。Oracle數據庫主要由內存結構、進程結構以及存儲結構等部分組成。其中，內存結構包含系統全局區域（SGA）以及程序全局區域（PGA）。進程結構包括用戶進程和服務器進程以及各種后臺進程。存儲結構分為邏輯存儲和物理存儲。SGA對數據庫性能影響最為關鍵，也對查詢優化起著重要作用。

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

2創建合理的索引

索引可以只有一個字段，也可以同時具有多個字段。一般情況下多使用單字段索引，因為太多的復合索引在有單個字段索引的情況下是沒有意義的，還會降低數據增刪操作時的效率。但當復合索引中包含所有要查詢的列時，Oracle數據庫僅查詢索引塊就可以獲取查詢結果，使用復合索引比多個單列索引的查詢速率會高得多。

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

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

3基于索引的SQL語句優化

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

（1） is Null與is not Null

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

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

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

將語句（1）改為語句（2）進行查詢：

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

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

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

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

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

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

將語句（3）改為語句（4）進行查詢：

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

語句（4）

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

（4） order by子句

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

（5）比較運算符

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

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

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

語句（5）和語句（6）的運行結果是一樣的，但是語句（6）會比語句（5）的查詢速度更快，因為Oracle數據庫對于語句（6）的查詢方案允許使用索引。

（6） in和exists的使用

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

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

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

如果SQL語句where子句的列中帶有索引，使用exists子句時，優化器不通過全表掃描而是僅僅根據索引就可以完成操作。而且當使用Exists時，Oracle數據庫先進行主查詢然后進行子查詢，直到找到第一個與條件相匹配的值為止，節省了查詢時間。例如有exists查詢語句（8）：

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

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

4結論

數據庫查詢作為數據庫一項重要的功能要盡力提高其效率。本文從建立索引、優化SQL語句方面對Oracle數據庫查詢優化進行研究，有效提高了查詢效率。

參考文獻

[1]劉鵬.基于關系數據庫的查詢優化研究[J]網絡安全技術與應用，2018 （02）：66-67.

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

[3]賈欽，數據庫管理系統中查詢優化的設計和實現措施[J]電子技術與軟件工程，2018（15）：142

[4]方芳，謝慧，關于Oracle數據庫的SQL查詢語句優化探究[J]，河南科技，2018 （04）：25-26.

[5]蘇現鵬，基于Oracle數據庫海量數據的查詢優化研究[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.