最佳化你的系統--索引(一) 正確使用索引
最佳化你的系統--索引(一) 正確使用索引
正確應用索引可以明顯提高應用效能.
正確應用索引包括兩部分,建立正確的索引和這確使用索引.
這裡我們關心後者,開發人員如何正確使用索引.
首先建立測試環境.
SQL> create table test (id number,tu_mdn varchar2(20),name varchar2(20)) tablespace users;
表已建立。
[@more@]最佳化你的系統--索引(一) 正確使用索引
正確應用索引可以明顯提高應用效能.
正確應用索引包括兩部分,建立正確的索引和這確使用索引.
這裡我們關心後者,開發人員如何正確使用索引.
首先建立測試環境.
SQL> create table test (id number,tu_mdn varchar2(20),name varchar2(20)) tablespace users;
表已建立。
SQL> begin
2 for i in 1..10000 loop
3 insert into test values (i,13300000000+i,'索引測試'||i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> select count(*) from test;
COUNT(*)
----------
10000
SQL> select * from test where rownum<10;
ID TU_MDN NAME
---------- -------------------- --------------------
667 13300000667 索引測試667
668 13300000668 索引測試668
669 13300000669 索引測試669
670 13300000670 索引測試670
671 13300000671 索引測試671
672 13300000672 索引測試672
673 13300000673 索引測試673
674 13300000674 索引測試674
675 13300000675 索引測試675
已選擇9行。
SQL> analyze table test compute statistics;
表已分析。
SQL> create index test_tu_mdn_idx on test(tu_mdn);
索引已建立。
SQL> analyze index test_tu_mdn_idx compute statistics;
索引已分析
瞭解了什麼情況下會導致索失效
典型的幾種索引失效的例子.
1 隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤.
由於表的欄位tu_mdn定義為varchar2(20),但在查詢時把number型別做為where條件傳給Oracle,這樣會導致索引失效.
SQL> set autotrace on
SQL> select * from test where tu_mdn=13333333333;
未選定行
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=14)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
269 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
正確的sql
SQL> select * from test where tu_mdn='13333333333';
未選定行
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=14)
2 1 INDEX (RANGE SCAN) OF 'TEST_TU_MDN_IDX' (NON-UNIQUE) (Co
st=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
269 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
2 對索引列進行運算導致索引失效,我所指的對索引列進行運算包括(+，-，*，/，! 等)
SQL> create index test_id on test(id);
SQL> analyze index test compute statistics;
SQL> select * from test where id-1=9;
ID TU_MDN NAME
---------- -------------------- --------------------
10 13300000010 索引測試10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=260
0)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=5 Card=100 Bytes=2600)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
499 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
正確sql:
SQL> select * from test where id=10;
ID TU_MDN NAME
---------- -------------------- --------------------
10 13300000010 索引測試10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=26)
2 1 INDEX (RANGE SCAN) OF 'TEST_ID' (NON-UNIQUE) (Cost=1 Car
d=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
499 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3 使用Oracle內部函式導致索引失效.
對於這樣情況應當建立基於函式的索引.
SQL> select * from test where round(id)=10;
ID TU_MDN NAME
---------- -------------------- --------------------
10 13300000010 索引測試10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=260
0)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=5 Card=100 Bytes=2600)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
499 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
建立基於函式的索引解決問題:
SQL> create index test_id_fbi_idx on test(round(id));
索引已建立。
SQL> analyze index test_id_fbi_idx compute statistics;
索引已分析
SQL> select * from test where round(id)=10;
ID TU_MDN NAME
---------- -------------------- --------------------
10 13300000010 索引測試10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=260
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=100 B
ytes=2600)
2 1 INDEX (RANGE SCAN) OF 'TEST_ID_FBI_IDX' (NON-UNIQUE) (Co
st=1 Card=40)
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
499 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
還有一些操作會導致索引實效，我只說明原因和避免方法.
4 避免在索引列使用not,is null,is not null,Oracle不維護空值.
避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引 ．
對於單列索引，如果列包含空值，索引中將不存在此記錄.
對於複合索引，如果每個列都為空，索引中同樣不存在此記錄.
因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.
5 用>=代替> 當然前提是你對業務做出正確的判斷.
比如我想找出id大於9000的資料,那麼有兩種方法可以查出.
select * from test where id > 9000; t1
已用時間: 00: 00: 00.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1000 Bytes=2
6000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=16 Card=1000
Bytes=26000)
2 1 INDEX (RANGE SCAN) OF 'TEST_ID_IDX' (NON-UNIQUE) (Cost=6
Card=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
150 consistent gets
0 physical reads
0 redo size
38761 bytes sent via SQL*Net to client
1229 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
select * from test where id >= 9001; t2
已用時間: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1002 Bytes=2
6052)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=16 Card=1002
Bytes=26052)
2 1 INDEX (RANGE SCAN) OF 'TEST_ID_IDX' (NON-UNIQUE) (Cost=6
Card=1002)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
150 consistent gets
0 physical reads
0 redo size
38820 bytes sent via SQL*Net to client
1229 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/76065/viewspace-789301/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL最佳化案例-正確的使用索引（二）SQL索引
- 圖解MySQL索引(三)—如何正確使用索引？圖解MySql索引
- 如何理解並正確使用 MySQL 索引MySql索引
- SQL優化案例-正確的使用索引（二）SQL優化索引
- Oracle優化案例-正確的使用索引（二）Oracle優化索引
- MySQL 5.6建索引的正確姿勢MySql索引
- MySQL 調優之如何正確使用聯合索引MySql索引
- 【恩墨學院】如何理解並正確使用MySql索引MySql索引
- SQL最佳化(一) 索引SQL索引
- 索引@oracel索引技術之索引最佳化索引
- Mysql系列第二十三講 如何正確的使用索引？MySql索引
- 使用for迴圈批量註冊的事件不能正確獲取索引值事件索引
- 索引使用最佳化的兩個操作索引
- 解讀mysql的索引和事務的正確姿勢MySql索引
- MySQL 索引使用策略及最佳化MySql索引
- 關於 Oracle 分割槽索引的正確 DROP 和 TRUNCATE 方法Oracle索引
- Oracle 索引的最佳化Oracle索引
- Mysql利用explain確認是否使用索引MySqlAI索引
- 【索引】使用索引分析快速得到索引的基本資訊索引
- Elasitcsearch索引最佳化索引
- mysql最佳化索引MySql索引
- MongoDB正規表示式在索引中的使用MongoDB索引
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- kk系統索引的問題索引
- 在OLTP系統使用索引組織表IOT索引
- MySql如何使用索引(一)MySql索引
- 《PostgreSQL》 索引與最佳化SQL索引
- SQL最佳化案例一則--複合索引沒有被使用SQL索引
- MySQL 中索引是如何實現的，有哪些型別的索引，如何進行最佳化索引MySql索引型別
- 最佳化SQL Server索引的技巧SQLServer索引
- 企業正確使用CRM系統的方法及流程
- SQLT 最佳化SQL 用複合索引代替單列索引的案例SQL索引
- SQL最佳化案例-分割槽索引之無字首索引（六）SQL索引
- 如果你的系統需要在一張很大的表上建立一個索引，你會考慮哪些因素？索引
- MongoDB之索引（唯一索引）MongoDB索引
- 你真的會使用資料庫的索引嗎？資料庫索引
- 之家廣告索引系統設計索引
- 系統相關內容索引索引