SQL優化之把運算放在列的另一端

space6212發表於2019-05-29
SQL優化

今天幫朋友解決oracle程式異常中斷後不能登入的問題後，順便幫他看了一下造成IDLE幾乎為0的原因。
通過分析發現，都是因為不良SQL造成的。這些不良SQL的一個共同點就是：把運算放在了列端。
朋友的資料量非常大，我在本機上簡單模擬現場，資料量上會小很多，但是仍然可以不同的寫法對執行效率的差別。


1、準備資料
SQL> create table test(name varchar2(100),code number(6),birth date);

Table created
--這個表中，code表示地區編碼，前兩位表示所屬省份；birth表示出生日期

SQL> insert into test select 'suk',110000,trunc(sysdate-30000)+rownum from dual connect by rownum<30000;

29999 rows inserted

SQL> insert into test values('suk',120000,trunc(sysdate-10000));

1 row inserted

SQL> commit;

Commit complete

SQL> create index idx_test_name_code on test(name,code);

Index created

SQL> create index idx_test_name_birth on test(name,birth);

Index created

SQL> analyze table test compute statistics for table for all indexes for all indexed columns;

Table analyzed

2、查詢
在本文中，舉兩個例子：
SQL1)查詢在12這個省中與'suk'同名的人的個數
SELECT COUNT(1) FROM TEST WHERE SUBSTR(CODE,1,2)=12 AND NAME='suk';
SQL2)查詢名字叫'suk'且出生日期是'1980-03-11'的人的個數
SELECT COUNT(1) FROM TEST WHERE NAME='suk' AND TO_CHAR(BIRTH,'YYYY-MM-DD')='1980-03-11';

這兩個SQL有一個共同點：就是把運算都放在了列上：SUBSTR(CODE,1,2)、TO_CHAR(BIRTH,'YYYY-MM-DD')。
我們在(name,code)，(name,birth)都建立的組合索引，這兩個查詢都直接可以從查詢得到結果，而不用在回表查詢了。
但是，CODE的資料類似的NUMBER，那麼，規矩oracle資料類似轉換的規則可知，在實際執行的時候，SUBSTR(CODE,1,2)=12會轉換成：
TO_NUMBER(SUBSTR(TO_CHAR(CODE),1,2))=12。
在SQL1中，根據索引，oracle很快可以定位到name='suk'的葉塊（在本例中佔了絕大部分葉塊），但因為對CODE進行了各種的函式轉換，oracle無法快速確定SUBSTR(CODE,1,2)=12所在也塊，它需要在name='suk'得到的葉塊上一一排查，再結合根據資料分佈情況分析，SQL1的查詢計劃應該是全索引快速掃描。
我們可以檢視一下執行計劃：
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE SUBSTR(CODE,1,2)=12 AND NAME='suk';

COUNT(1)
----------
1

已用時間: 00: 00: 00.17

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_TEST_NAME_CODE' (NON-UNIQ
UE) (Cost=4 Card=300 Bytes=1500)


統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets

要調整這個SQL就要讓條件中出現的索引欄位不經過任何轉換，這樣就可以快速定位記錄了。SQL1可以等價改寫為如下：
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE CODE>=120000 AND CODE<130000 AND NAME='suk';

COUNT(1)
----------
1

已用時間: 00: 00: 00.12

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NAME_CODE' (NON-UNIQUE)
(Cost=2 Card=1 Bytes=5)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets

同理可以調整SQL2：
--調整前
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE NAME='suk' AND TO_CHAR(BIRTH,'YYYY-MM-DD')='1980-03-11
';

COUNT(1)
----------
2

已用時間: 00: 00: 00.17

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_TEST_NAME_BIRTH' (NON-UNI
QUE) (Cost=5 Card=300 Bytes=3600)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
104 consistent gets

--調整後
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE NAME='suk' AND BIRTH=TO_DATE('1980-03-11','YYYY-MM-DD'
);

COUNT(1)
----------
2

已用時間: 00: 00: 00.14

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NAME_BIRTH' (NON-UNIQUE)
(Cost=1 Card=300 Bytes=3600)


統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets

可以看到，調整後的SQL用索引範圍掃描代替了全索引快速掃描，邏輯讀大大降低。

3、簡單總結

SQL調優的一個原則就是不要在條件中出現的列上做任何操作（包括不要加函式、不要發生資料隱式轉換等），我們要把操作儘量放在常量端。
換一句話說就是：建立索引時索引列是怎麼定義的，在where條件中列也要按相同的方式寫。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-63840/，如需轉載，請註明出處，否則將追究法律責任。

相關文章