快捷導(dǎo)航

數(shù)據(jù)庫類型轉(zhuǎn)換導(dǎo)致SQL不走索引的案例

更新時(shí)間：2026年05月20日 10:21:35 作者：瀚高PG實(shí)驗(yàn)室

在SQL中數(shù)據(jù)類型轉(zhuǎn)換是基礎(chǔ)且關(guān)鍵的操作,分為隱式和顯式轉(zhuǎn)換,隱式轉(zhuǎn)換由系統(tǒng)自動(dòng)完成,雖便捷但可能帶來性能損耗、索引失效及數(shù)據(jù)準(zhǔn)確性風(fēng)險(xiǎn),這篇文章主要介紹了數(shù)據(jù)庫類型轉(zhuǎn)換導(dǎo)致SQL不走索引的案例,需要的朋友可以參考下

環(huán)境

系統(tǒng)平臺(tái)：Linux x86-64 Red Hat Enterprise Linux 7

版本：4.5

文檔用途

本文檔主要介紹對字段添加類型轉(zhuǎn)換，會(huì)導(dǎo)致SQL不走索引，從而影響查詢性能。

詳細(xì)信息

表的結(jié)構(gòu)說明

表 table_varchar_id 的字段 id 是 varchar 類型，存在索引 idx_table_varchar_id 。

表 table_int_id 的字段 id 是 bigint 類型，存在索引 idx_table_int_id 。

執(zhí)行計(jì)劃示例

highgo=# EXPLAIN (ANALYZE, BUFFERS)
SELECT v.id as varchar_id, i.id as int_id, v.col1, i.col1
FROM table_varchar_id v
, table_int_id i
WHERE CAST(v.id AS BIGINT) = i.id
AND i.id BETWEEN 100000000000000000 AND 100000000005000000;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.45..586.02 rows=7 width=53) (actual time=0.026..2.209 rows=5 loops=1)
   Hash Cond: ((v.id)::bigint = i.id)
   Buffers: shared hit=418
   ->  Seq Scan on table_varchar_id v  (cost=0.00..515.00 rows=10000 width=32) (actual time=0.004..0.806 rows=10000 loops=1) 
         Buffers: shared hit=415
   ->  Hash  (cost=8.38..8.38 rows=5 width=21) (actual time=0.012..0.012 rows=5 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=3
         ->  Index Scan using idx_table_int_id on table_int_id i  (cost=0.28..8.38 rows=5 width=21) (actual time=0.009..0.010 rows=5 loops=1)
               Index Cond: ((id >= '100000000000000000'::bigint) AND (id <= '100000000005000000'::bigint))
               Buffers: shared hit=3
 Planning Time: 0.117 ms
 Execution Time: 2.237 ms
(13 行記錄)

上面執(zhí)行計(jì)劃中，查詢 table_varchar_id 表時(shí)，執(zhí)行了順序掃描，沒有使用 id 字段的索引，如下所示：

->  Seq Scan on table_varchar_id v  (cost=0.00..515.00 rows=10000 width=32) (actual time=0.004..0.806 rows=10000 loops=1) 
      Buffers: shared hit=415

原因

SQL語句中對 table_varchar_id 表的字段 id 添加了 CAST(v.id AS BIGINT) 類型轉(zhuǎn)換，導(dǎo)致沒有執(zhí)行 id 字段的索引。

方案

方案一：修改表字段的數(shù)據(jù)類型

針對 table_varchar_id 表的字段 id ，確認(rèn)業(yè)務(wù)上常用的數(shù)據(jù)類型是數(shù)值型還是字符串型，然后統(tǒng)一修改為相同的數(shù)據(jù)類型，示例如下：

highgo=# alter table table_varchar_id alter COLUMN id type bigint using id::bigint;  --統(tǒng)一使用 bigint 類型

highgo=# analyze table_varchar_id;        --收集表的統(tǒng)計(jì)信息，保證被修改數(shù)據(jù)類型的 id 字段的索引立即生效

數(shù)據(jù)類型修改統(tǒng)一后，SQL語句中的 cast 轉(zhuǎn)換可以刪除。

方案二：創(chuàng)建 cast 類型轉(zhuǎn)換的索引

highgo=# create index idx_table_varchar_cast_id on table_varchar_id(cast(id as bigint));

highgo=# analyze table_varchar_id;        --收集表的統(tǒng)計(jì)信息，保證類型轉(zhuǎn)換索引、表達(dá)式索引立即生效

注：類型轉(zhuǎn)換索引、表達(dá)式索引的維護(hù)成本比較高，尤其是數(shù)據(jù)變更頻繁的表，影響性能

方案三：更換為對條件值進(jìn)行類型轉(zhuǎn)換

SQL語句中 table_varchar_id 表的字段 id 去掉 cast 轉(zhuǎn)換，表 table_int_id 的字段 id 作為條件值添加 varchar 類型的轉(zhuǎn)換，修改后的SQL及執(zhí)行計(jì)劃

如下：

highgo=# EXPLAIN (ANALYZE, BUFFERS)
SELECT v.id as varchar_id, i.id as int_id, v.col1, i.col1
FROM table_varchar_id v
, table_int_id i
WHERE v.id = i.id::varchar      --i.id 字段添加 varchar 類型轉(zhuǎn)換，v.id 可以執(zhí)行索引掃描
AND i.id BETWEEN 100000000000000000 AND 100000000005000000;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.57..49.97 rows=5 width=53) (actual time=0.021..0.029 rows=5 loops=1)
   Buffers: shared hit=16 read=2
   ->  Index Scan using idx_table_int_id on table_int_id i  (cost=0.28..8.38 rows=5 width=21) (actual time=0.003..0.004 rows=5 loops=1)
         Index Cond: ((id >= '100000000000000000'::bigint) AND (id <= '100000000005000000'::bigint))
         Buffers: shared hit=3
   ->  Index Scan using idx_table_varchar_id on table_varchar_id v  (cost=0.29..8.31 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=5)
         Index Cond: ((id)::text = ((i.id)::character varying)::text)
         Buffers: shared hit=13 read=2
 Planning Time: 0.123 ms
 Execution Time: 0.048 ms
(10 行記錄)