從隱式轉換案例，來挖掘開發人員的技能提升
- 2020 年 3 月 6 日
- 筆記
碰到個問題(https://www.modb.pro/issue/1440)，有點意思，值得在這說下。
問題說明：
TBL_TallyABC存在聯合主鍵，
WorkDate，ReceiveDate，StatDate，BillCycle，SettlementOrgID，StatType
SQL的執行計劃是全表掃描，
update TBL_TallyABC set Count = Count + 1,Amount = Amount + :1 where WorkDate = :2 and ReceiveDate = :3 and StatDate = :4 and BillCycle = :5 and SettlementOrgID = :6 and StatType = :7
發現這個SQL造成資料庫高峰時段75%的enq: TX – row lock contention等待事件，且鎖的模型是6是什麼原因呢，怎樣減少enq: TX – row lock contention？
從上面的資訊，可以知道這幾點，
1. update語句的where條件欄位都是複合索引的欄位。
2. 但是update語句沒用上索引，為全表掃描。
3. 執行update語句期間，資料庫出現enq: TX – row lock contention爭用。
基於以上資訊，推測之所以出現行鎖爭用，是這樣的邏輯，
當對錶行進行DML操作的時候，需要獲取相應鎖，enq: TX – row lock contention就是行鎖爭用，之所以出現這個爭用，就是因為UPDATE用了全表掃描，導致一條SQL的執行時間比以前更久，大量UPDATE操作，雪崩效應，就會讓行鎖爭用更明顯。
朋友雖然沒給出這個問題中具體的表結構、綁定變數值，但是，我們是可以反推出實驗的，如下所示，表t_001，含三個欄位，分別是timestamp、date和number類型，此時，創建(timestamp, date, number)複合索引，
SQL> create table t_001(a_ts timestamp, a_date date, id number); Table created. SQL> alter table t_001 add constraint pk_t_001 primary key(a_ts, a_date, id); Table altered.
執行select，where條件涉及時間的，統一使用to_date轉成date類型，左值a_ts欄位是timestamp類型，右值date類型，因為timestamp優先順序高於date，因此在這左值不用隱式轉換，從使用的索引唯一掃描就能證明這點，複合索引的三個欄位都用上了，
SQL> select * from t_001 where id = 1 and a_ts = to_date('2020-02-15','yyyy-mm-dd') and 2 a_date = to_date('2020-02-15','yyyy-mm-dd'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 76550962 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 35 | 0 (0)| 00:00:01 | |* 1 | INDEX UNIQUE SCAN| PK_T_001 | 1 | 35 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A_TS"=TIMESTAMP' 2020-02-15 00:00:00' AND "A_DATE"=TO_DATE(' 2020-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ID"=1)
但是，當使用下面的SQL，where條件涉及時間的統一使用to_timestamp轉成timestamp類型，左值a_date是date類型，右值轉成timestamp類型，因為timestamp優先順序高於date，此時左值會進行隱式轉換，需要將date轉成timestamp，而且從謂詞資訊，可以看到，Oracle對a_date使用了INTERNAL_FUNCTION函數，對欄位使用了函數操作，就會導致欄位上的索引失效，謂詞從access改為了filter，這個複合索引，僅可用到其中一部分欄位，其他欄位只能作為過濾條件，從使用的索引範圍掃描就能證明這個推測，
SQL> select * from t_001 where id = 1 and a_ts = to_timestamp('2020-02-15 00:01:00','yyyy-mm-dd hh24:mi:ss') 2 and a_date = to_timestamp('2020-02-15 00:01:00','yyyy-mm-dd hh24:mi:ss'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 491049371 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 35 | 0 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| PK_T_001 | 1 | 35 | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A_TS"=TIMESTAMP' 2020-02-15 00:01:00.000000000' AND "ID"=1) filter("ID"=1 AND INTERNAL_FUNCTION("A_DATE")=TIMESTAMP' 2020-02-15 00:01:00.000000000')
隱式轉換，說白了，就是當where條件「=」號右值的優先順序高於「=」號左值的優先順序，此時就需要對左值進行函數操作，藉此轉換成和右值相同的類型，就像Oracle中nvarchar2、varchar2、char以及timestamp和date。
對這個問題，如果各位有什麼其他的見解，歡迎在文末留言，我們一同探討。
然而，實際情況是，開發人員很可能因為對欄位類型的錯誤理解，導致寫出來的程式出現隱式轉換，而且在測試環境，數據量很小，這種性能問題是不能暴露出來的，可到了生產環境，他就會對系統的穩定運行產生影響。
因此，作為開發人員，除了需要了解資料庫的CRUD，對可能產生性能隱患的一些資料庫基礎知識同樣需要了解，不能做code的搬運工，而是要做創造者，才可能提升自己的個人價值。
當你要確定自己寫的SQL程式碼在性能上是否存在隱患的時候，就可能會用到執行計劃，你要知道怎麼得到真實的執行計劃，判斷執行計劃的正確，根據執行計劃糾正自己的SQL。
當你要對錶結構做調整，例如增加欄位、刪除欄位，你可能需要了解在執行過程中他會持有什麼級別的鎖，知道這個操作對資料庫有什麼影響。
當你要刪除大表數據，或者更新大表數據的時候，你可能需要根據實際情況，以及對資料庫原理的理解，來判斷選擇合適的方案，方案選擇的對，可能秒級完成，否則，幾個小時都未必能完成。
其實像這樣的例子還很多，開發人員對資料庫原理了解的越多，就會越讓你在關鍵時刻做出正確的判斷。
當然，這些都取決於你在日常的積累。我這個雜貨鋪就是了解這些知識的渠道之一，這張圖是雜貨鋪中和Oracle相關的文章分類，其中一些是站在開發人員的角度講解的知識，或是碰到的實際問題，歡迎各位品讀、嘗試和討論，
