前言
在數(shù)據(jù)庫(kù)中，索引失效會(huì)導(dǎo)致查詢無(wú)法利用索引來(lái)加速，從而降低查詢性能。
今天就來(lái)探索為什么隱式類型轉(zhuǎn)換，會(huì)導(dǎo)致索引失效呢，為什么不能對(duì)參數(shù)進(jìn)行類型轉(zhuǎn)換再匹配呢，這樣不就能用上索引呢？
隱式類型轉(zhuǎn)換之謎??
為什隱式轉(zhuǎn)換會(huì)導(dǎo)致索引失效
先看一個(gè)例子： create_by 的字段類型為 varchar
看到這兒可能很多人都會(huì)大吃一驚！
MySQL 會(huì)嘗試將 VARCHAR 類型的 create_by 字段值轉(zhuǎn)換為數(shù)字類型，然后再與 116 進(jìn)行比較。在轉(zhuǎn)換過(guò)程中，MySQL 會(huì)從字符串的開(kāi)頭開(kāi)始解析數(shù)字，直到遇到非數(shù)字字符為止。如果字符串開(kāi)頭沒(méi)有有效的數(shù)字，那么轉(zhuǎn)換結(jié)果為 0
上面的SQL 和 下面這個(gè)SQL 執(zhí)行邏輯應(yīng)該是相似的
SELECT distinct create_by FROM t_message WHERE CONVERT(create_by, SIGNED) = 116;
換成這個(gè)SQL我想大家都明白了，為什么索引會(huì)失效了，隱式轉(zhuǎn)換的時(shí)候如果是對(duì)列轉(zhuǎn)，那么索引就一定失效
隱式轉(zhuǎn)換索引一定失效么？轉(zhuǎn)換一定是轉(zhuǎn)列么？
當(dāng)然不是??，請(qǐng)看下面ID是bigint,并且執(zhí)行執(zhí)行計(jì)劃是 使用了主鍵索引的
再來(lái)看一張人大金倉(cāng)的explain, 它這個(gè)就很清楚了，是把參數(shù)轉(zhuǎn)換成 數(shù)據(jù)庫(kù)列的類型，這樣就肯定走索引呢 ： id = '123' ::bigint 如下
隱式轉(zhuǎn)換有什么規(guī)則呢？什么時(shí)候轉(zhuǎn)參數(shù)，什么時(shí)候轉(zhuǎn)列
??其實(shí)Mysql轉(zhuǎn)換是有一套優(yōu)先級(jí)的: 數(shù)字類型 > 時(shí)間類型 > 字符串類型，低級(jí)向高級(jí)轉(zhuǎn)換。
??所以如果列類型的優(yōu)先級(jí)高于參數(shù)類型的優(yōu)先級(jí) 那么就會(huì)對(duì)參數(shù)進(jìn)行類型轉(zhuǎn)換，這時(shí)候類型轉(zhuǎn)換不會(huì)對(duì)性能有啥影響。反之會(huì)去對(duì)列數(shù)據(jù)進(jìn)行轉(zhuǎn)換，性能的影響就比較大
最近工作也遇到很多 數(shù)字 和 boolean 類型的轉(zhuǎn)換，在人大金倉(cāng)中是 將boolean類型轉(zhuǎn)為數(shù)字，mysql 沒(méi)做測(cè)試，應(yīng)該也是一樣。
為什么不能直接對(duì)參數(shù)轉(zhuǎn)換了，這樣就不會(huì)導(dǎo)致索引失效了?
MySQL在處理類型不匹配的比較時(shí)，通常選擇轉(zhuǎn)換列值而非參數(shù)值，從而導(dǎo)致索引失效。這個(gè)設(shè)計(jì)決策背后主要是按照
?MySQL遵循SQL標(biāo)準(zhǔn)中"操作數(shù)類型提升"的規(guī)則，通常會(huì)將低優(yōu)先級(jí)類型轉(zhuǎn)換為高優(yōu)先級(jí)類型，數(shù)值類型優(yōu)先級(jí) > 時(shí)間類型 > 字符串類型
這種轉(zhuǎn)換方向是數(shù)據(jù)庫(kù)引擎的通用設(shè)計(jì)模式
為什么SQL標(biāo)準(zhǔn)"操作數(shù)類型提升" 要這樣設(shè)計(jì)呢
?下面看看DeepSeek 是怎么回答的?
1. 語(yǔ)義一致性（避免歧義）與可預(yù)測(cè)性
SQL 標(biāo)準(zhǔn)要求表達(dá)式的結(jié)果類型必須明確且可預(yù)測(cè)。當(dāng)操作數(shù)類型不同時(shí)，通過(guò)固定的優(yōu)先級(jí)規(guī)則（如數(shù)值 > 時(shí)間 > 字符串）進(jìn)行隱式轉(zhuǎn)換，確保所有數(shù)據(jù)庫(kù)實(shí)現(xiàn)的行為一致。例如：
數(shù)值與字符串比較時(shí)，字符串轉(zhuǎn)為數(shù)值，避免因字符編碼差異導(dǎo)致不確定結(jié)果。 假設(shè) 一個(gè)條件是 var_colum = 123, 如果轉(zhuǎn)換成 var_colum = '123' ,那么 ' 123 ' 這種有空格的就無(wú)法查詢了。這就是語(yǔ)義一致性問(wèn)題。
2. 計(jì)算效率優(yōu)化
高優(yōu)先級(jí)類型（如數(shù)值）通常具有更高效的比較和計(jì)算機(jī)制。例如：
數(shù)值比較直接使用CPU指令，而字符串比較需逐字符處理。優(yōu)先轉(zhuǎn)為數(shù)值可提升性能。
3. 索引結(jié)構(gòu)的限制
我也沒(méi)咋看懂??： 索引（如B-tree）按列的原生類型組織。若強(qiáng)制轉(zhuǎn)換參數(shù)而非列值，存儲(chǔ)引擎仍需按列類型重新轉(zhuǎn)換參數(shù)，反而增加開(kāi)銷。例如：
WHERE varchar_col = 123 若轉(zhuǎn)為 varchar_col = '123'，仍需將字符串'123'轉(zhuǎn)回?cái)?shù)值與索引比較，無(wú)法避免轉(zhuǎn)換。
總結(jié)
本篇文章，分析了查詢隱式轉(zhuǎn)換什么時(shí)候會(huì)索引失效，以及轉(zhuǎn)換規(guī)則優(yōu)先級(jí)，以及為什么SQL標(biāo)準(zhǔn)要這么去定義，DeepSeek 給的答案是主要就是 避免歧義，提升性能。
推薦閱讀：dev.mysql.com/doc/refman/…?
知識(shí)擴(kuò)展
下面第一、二點(diǎn)就是我們今天探索的失效場(chǎng)景
- 類型不匹配 如果查詢條件中的數(shù)據(jù)類型與索引列的數(shù)據(jù)類型不一致，數(shù)據(jù)庫(kù)可能會(huì)進(jìn)行隱式類型轉(zhuǎn)換，破壞索引的有序性，造成索引失效。
SELECT * FROM users WHERE id = 123;
- 索引列上使用函數(shù)或表達(dá)式 當(dāng)在索引列上使用函數(shù)、表達(dá)式時(shí)，數(shù)據(jù)庫(kù)無(wú)法直接使用索引的有序結(jié)構(gòu)來(lái)快速定位數(shù)據(jù)，通常會(huì)導(dǎo)致索引失效。
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
- 范圍查詢右側(cè)列 對(duì)于復(fù)合索引（多列索引），如果在復(fù)合索引的前導(dǎo)列使用范圍查詢，后續(xù)列的索引會(huì)失效。
SELECT * FROM table_name WHERE col1 > 10 AND col2 = 20;
- 模糊查詢以通配符開(kāi)頭 在使用
LIKE 進(jìn)行模糊查詢時(shí)，如果通配符 % 出現(xiàn)在字符串的開(kāi)頭，數(shù)據(jù)庫(kù)無(wú)法利用索引的有序性進(jìn)行快速匹配，會(huì)導(dǎo)致索引失效。
SELECT * FROM users WHERE name LIKE '%john';
5. OR 連接條件 當(dāng)查詢條件使用 OR 連接多個(gè)條件，且這些條件部分沒(méi)有索引或者不全使用同一個(gè)索引時(shí)，可能會(huì)導(dǎo)致索引失效。
SELECT * FROM users WHERE id = 1 OR name = 'john';
6. IS NULL 和 IS NOT NULL 在某些情況下，對(duì)索引列使用 IS NULL 或 IS NOT NULL 可能會(huì)導(dǎo)致索引失效，尤其是在數(shù)據(jù)分布不均勻時(shí)。
SELECT * FROM users WHERE email IS NULL;
全表掃描更快 當(dāng)數(shù)據(jù)庫(kù)的查詢優(yōu)化器認(rèn)為全表掃描比使用索引掃描更快時(shí)，會(huì)選擇全表掃描，此時(shí)索引就不會(huì)被使用。例如，當(dāng)查詢的數(shù)據(jù)量占總數(shù)據(jù)量的比例較大時(shí)，優(yōu)化器可能會(huì)做出這樣的決策。
索引統(tǒng)計(jì)信息不準(zhǔn)確 如果索引的統(tǒng)計(jì)信息不準(zhǔn)確，查詢優(yōu)化器可能會(huì)做出錯(cuò)誤的決策，導(dǎo)致索引失效。例如，表數(shù)據(jù)發(fā)生了大量的插入、刪除、更新操作，但沒(méi)有及時(shí)更新索引統(tǒng)計(jì)信息。
強(qiáng)制索引失效 在 SQL 語(yǔ)句中使用 IGNORE INDEX 關(guān)鍵字可以強(qiáng)制數(shù)據(jù)庫(kù)不使用指定的索引。
轉(zhuǎn)自https://juejin.cn/post/7490856819003785252
該文章在 2025/4/15 15:07:35 編輯過(guò)
400 186 1886
