摘要：最近解決了一個生產慢查詢的問題，排查問題之后發(fā)現(xiàn)一些比較隱匿且容易忽略的問題。所以實際在數(shù)據(jù)庫查詢如下可能這里發(fā)生一次隱式轉換。這次查詢走的是索引。

Photo by Iga Palacz on Unsplash

最近解決了一個生產 SQL 慢查詢的問題，排查問題之后發(fā)現(xiàn)一些比較隱匿且容易忽略的問題。

最近業(yè)務上需要上線一個預警功能，需要查出一段時間內交易，求出當前交易成功率。當成功率低于設定閾值時，短信預警。業(yè)務邏輯很簡單，測試環(huán)境測試也沒問題之后，部署上線。實際生產運行時卻發(fā)現(xiàn)每次 SQL 查詢需要花費 60 多秒。

Spring boot + Mybatis + Oracle。

需要查詢的表數(shù)量級為億級。

交易表結構(已經簡化)大致如下。

create table TB_TEST
(
  BANK_CODE   VARCHAR2(20),
  CREATE_TIME DATE,
  OID_BILL    NUMBER(16) not null
)
/
create index TB_TEST_CREATE_TIME_INDEX
  on TB_TEST (CREATE_TIME)
/

create unique index TB_TEST_OID_BILL_UINDEX
  on TB_TEST (OID_BILL)
/

alter table TB_TEST
  add constraint TB_TEST_PK
    primary key (OID_BILL)
/

該項目的增刪改查語句使用 MybatisGenerate 自動生成，查詢語句使用 CREATE_TIME 做為條件查詢，自動生成 sql 如下。

select *
from TB_TEST
where CREATE_TIME >= #{start_time}
  and CREATE_TIME < #{end_time};

我們通過設置 Druid 的配置，將具體查詢 SQL 日志輸出到控制臺。具體設置如下。

  
      ... ...
      
  

  
    
        
    

具體 sql 日志如下：

從日志中我們可以清楚看到實際運行的 SQL，以及查詢參數(shù)與類型。

從查詢語句看來，我們查詢條件正確，且由于 CREATE_TIME 存在獨立索引，所以查詢會走索引，查詢速度應該很快，不至于每次查詢需要花費 60 多秒。

所以當時猜測這次查詢由于某些原因發(fā)生了全表掃描，未走索引才導致慢查詢。在 Google 搜索相關資料，看見一篇文章 https://www.cnblogs.com/chen-...。

根據(jù)文章描述的是 Oracle 中存在隱式轉換的情況，當類型不匹配的時，Oracle 會主動將類型轉換成目標類型。查看我們表結構，CREATE_TIME 為 Date 類型，而根據(jù)日志我們查詢參數(shù)傳遞的 CREATE_TIME 卻為 TIMESTAMP 類型。

所以實際在數(shù)據(jù)庫查詢 SQL 如下：

SELECT *
FROM TB_TEST
WHERE (CREATE_TIME >= to_timestamp("2018-03-03 18:45:32", "yyyy-mm-dd hh24:mi:ss") and
       CREATE_TIME < to_timestamp("2019-01-03 18:45:32", "yyyy-mm-dd hh24:mi:ss"));

可能這里發(fā)生一次隱式轉換。

如何證明這個猜想那？我們可以使用 EXPLAIN PLAN ，分析 SQL 執(zhí)行計劃.上面 SQL 執(zhí)行計劃如下。

從上圖我們可以從 TB ACCESS FULL 看出，這次查詢慢確實由于是全表掃描導致。

然后我們查看執(zhí)行計劃中的 Predicate Information 信息，Oracle 使用 INTERNAL_FUNCATIPON 轉換 CREATE_TIME 類型 。從這點那可以看出查詢過程索引字段發(fā)生一次內聯(lián)函數(shù)轉換。

SQL 性能優(yōu)化往往會有一點，避免在索引字段使用函數(shù)。

既然知道原因，那么解決辦法也沒有這么難了。我們將查詢 sql 改為如下就能解決。

select *
from TB_TEST
where CREATE_TIME >= TO_DATE(#{start_time}, "yyyy-mm-dd hh24:mi:ss")
  and CREATE_TIME < TO_DATE(#{end_time}, "yyyy-mm-dd hh24:mi:ss");

-- 或者使用 cast 函數(shù)
select *
from TB_TEST
where CREATE_TIME >= cast(#{start_time} as date)
  and CREATE_TIME < cast(#{end_time} as date);

解決完問題，我們分析下 Java 類型中的　Date 類型為什么最終會轉換成 Oracle 中的 TIMESTAMP 類型。

這次案例中我們使用 Mybatis 框架，框架內部會將 Java 數(shù)據(jù)類型轉換成對應的 JDBC 數(shù)據(jù)類型。查看Mybatis 類型轉換 這一節(jié)我們可以發(fā)現(xiàn) Java Date 類型將會轉換成 java.sql.TIMESTAMP。

然后我們查看 Oracle JDBC 數(shù)據(jù)類型轉換規(guī)則。在 https://docs.oracle.com/cd/B1... 我們可以看到，TIMESTAMP 將轉換成 Oracle 中 TIMESTAMP。

假設我們將 CREATE_TIME 類型修改成 TIMESTAMP，然后查詢的時候將 CREATE_TIME 轉換成 Date 類型，是否也會發(fā)生內聯(lián)函數(shù)轉換，然后導致全表掃描那？查詢 sql 如下。

--  CREATE_TIME 類型為 TIMESTAMP
select *
from TB_TEST
where CREATE_TIME >= TO_DATE("2018-02-27 19:36:21", "yyyy-mm-dd hh24:mi:ss")
  and CREATE_TIME < TO_DATE("2018-12-27 19:36:21", "yyyy-mm-dd hh24:mi:ss")

。。。。

。。。。

。。。。

我們用 EXPLAIN PLAN 分析這個 SQL。

我們可以看到，確實發(fā)生了一次內聯(lián)轉化，但是卻在另外一邊。這次查詢走的是索引。

從這個例子我們可以看出，在索引字段上使用函數(shù)會導致全表掃描。但是在傳入查詢參數(shù)上使用函數(shù)并不會導致索引失效。

1 SQL 查詢時需要注意兩邊數(shù)據(jù)類型的一致性，雖然數(shù)據(jù)庫隱式轉換會幫我們解決數(shù)據(jù)不一致的問題，但是這種隱式轉化帶來一些隱蔽問題，讓我們第一時間并不能很快發(fā)現(xiàn)。所以使用顯示轉換代替隱式轉換。這樣我們的 SQL 清晰易懂，而且更加可控。

2 學會使用 EXPLAIN PLAN 分析慢 SQL。

3 索引字段上使用相關函數(shù)會導致慢查詢，查詢時切勿在索引字段上使用函數(shù)。

1、 https://docs.oracle.com/cd/B1...
2、 https://dev.mysql.com/doc/ref...

如果覺得好的話，請幫作者點個贊唄~ 謝謝

喜歡本文的讀者們，歡迎長按關注訂閱號程序通事~讓我與你分享程序那些事。