摘要：最近解決了一個(gè)生產(chǎn)慢查詢的問(wèn)題，排查問(wèn)題之后發(fā)現(xiàn)一些比較隱匿且容易忽略的問(wèn)題。所以實(shí)際在數(shù)據(jù)庫(kù)查詢?nèi)缦驴赡苓@里發(fā)生一次隱式轉(zhuǎn)換。這次查詢走的是索引。

Photo by Iga Palacz on Unsplash

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

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

Spring boot + Mybatis + Oracle。

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

交易表結(jié)構(gòu)(已經(jīng)簡(jiǎn)化)大致如下。

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)
/

該項(xiàng)目的增刪改查語(yǔ)句使用 MybatisGenerate 自動(dòng)生成，查詢語(yǔ)句使用 CREATE_TIME 做為條件查詢，自動(dòng)生成 sql 如下。

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

我們通過(guò)設(shè)置 Druid 的配置，將具體查詢 SQL 日志輸出到控制臺(tái)。具體設(shè)置如下。

  
      ... ...
      
  

  
    
        
    

具體 sql 日志如下：

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

從查詢語(yǔ)句看來(lái)，我們查詢條件正確，且由于 CREATE_TIME 存在獨(dú)立索引，所以查詢會(huì)走索引，查詢速度應(yīng)該很快，不至于每次查詢需要花費(fèi) 60 多秒。

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

根據(jù)文章描述的是 Oracle 中存在隱式轉(zhuǎn)換的情況，當(dāng)類型不匹配的時(shí)，Oracle 會(huì)主動(dòng)將類型轉(zhuǎn)換成目標(biāo)類型。查看我們表結(jié)構(gòu)，CREATE_TIME 為 Date 類型，而根據(jù)日志我們查詢參數(shù)傳遞的 CREATE_TIME 卻為 TIMESTAMP 類型。

所以實(shí)際在數(shù)據(jù)庫(kù)查詢 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ā)生一次隱式轉(zhuǎn)換。

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

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

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

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

既然知道原因，那么解決辦法也沒(méi)有這么難了。我們將查詢 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);

解決完問(wèn)題，我們分析下 Java 類型中的　Date 類型為什么最終會(huì)轉(zhuǎn)換成 Oracle 中的 TIMESTAMP 類型。

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

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

假設(shè)我們將 CREATE_TIME 類型修改成 TIMESTAMP，然后查詢的時(shí)候?qū)?CREATE_TIME 轉(zhuǎn)換成 Date 類型，是否也會(huì)發(fā)生內(nèi)聯(lián)函數(shù)轉(zhuǎn)換，然后導(dǎo)致全表掃描那？查詢 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 分析這個(gè) SQL。

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

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

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

2 學(xué)會(huì)使用 EXPLAIN PLAN 分析慢 SQL。

3 索引字段上使用相關(guān)函數(shù)會(huì)導(dǎo)致慢查詢，查詢時(shí)切勿在索引字段上使用函數(shù)。

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

如果覺(jué)得好的話，請(qǐng)幫作者點(diǎn)個(gè)贊唄~ 謝謝

喜歡本文的讀者們，歡迎長(zhǎng)按關(guān)注訂閱號(hào)程序通事~讓我與你分享程序那些事。