MySQL隱蔽BUG：組合條件查詢無故返回空集的排查與規(guī)避方案
引言
在數(shù)據(jù)庫日常運(yùn)維中，“查詢結(jié)果不符合預(yù)期” 是高頻問題，但多數(shù)情況可歸因于 SQL 語法、數(shù)據(jù)異?；蛩饕O(shè)計(jì)。而本次遇到的案例，卻源于 MySQL 的底層 BUG—— 明明數(shù)據(jù)存在，單一條件查詢正常，疊加一個(gè)過濾條件后竟返回空集，著實(shí)令人費(fèi)解。本文將完整還原問題場景、排查過程，以及最終的解決方案。
1. 問題背景
數(shù)據(jù)庫版本：MySQL8.0.40
假設(shè)我們創(chuàng)建了一個(gè)名為 product_info 的表，用于存儲(chǔ)產(chǎn)品的相關(guān)信息。該表包含三個(gè)字段：product_id（產(chǎn)品編號）、category_id（類別編號）和 brand_id（品牌編號）。其中，product_id 被設(shè)置為主鍵，并且采用降序排列。
CREATE TABLE product_info( product_id VARCHAR(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '產(chǎn)品編號', category_id VARCHAR(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '類別編號', brand_id VARCHAR(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '品牌編號', PRIMARY KEY(`product_id` DESC), KEY `idx_brand_id`(`brand_id`), KEY idx_category_id(category_id))DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
以下是創(chuàng)建表的 SQL 語句：隨后，我們向表中插入了一些數(shù)據(jù)：
INSERT INTO product_info VALUES('P001','C01','B02'),('P002','C02','B01'),('P003','C02','B01'),('P004','C01','B02'),('P005','C03','B01'),('P006','C03','B01');數(shù)據(jù)插入完成后，我們進(jìn)行了兩次查詢操作。第一次查詢是篩選出 category_id 為 C02 的記錄：
SELECT * FROM product_info WHERE category_id='C02';
這次查詢正常返回了兩條記錄，結(jié)果如下：
+------------+-------------+----------+| product_id | category_id | brand_id |+------------+-------------+----------+| P003 | C02 | B01 || P002 | C02 | B01 |+------------+-------------+----------+
然而，當(dāng)我們進(jìn)行第二次查詢，增加了 brand_id 為 B01 的條件時(shí)：
mysql> SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01';Empty set (0.00 sec)
本應(yīng)返回上述兩條記錄，但實(shí)際結(jié)果卻為空集，這顯然與預(yù)期不符。
2. 問題分析及排查
2.1 字符集和校對規(guī)則方面
表和字段都采用了 utf8mb4_general_ci 字符集和校對規(guī)則。通常情況下，對于數(shù)字和字母組成的字符串比較，這種校對規(guī)則不會(huì)出現(xiàn)問題。但我們不能排除隱式類型轉(zhuǎn)換或者存在不可見字符的可能性。為了驗(yàn)證這一點(diǎn)，我們可以使用 HEX 函數(shù)查看 brand_id 的實(shí)際存儲(chǔ)值：
SELECT product_id, category_id, brand_id, HEX(brand_id) FROM product_info WHERE category_id='C02';
如果 brand_id 的值確實(shí)是 B01，那么 HEX 函數(shù)的結(jié)果應(yīng)該是 423031。若結(jié)果中出現(xiàn)其他字符，比如尾隨空格，可能會(huì)導(dǎo)致比較時(shí)出現(xiàn)不匹配的情況。但是此案例明顯不是。
2.2 索引相關(guān)問題
索引選擇問題
當(dāng)執(zhí)行組合條件查詢時(shí)，優(yōu)化器可能會(huì)選擇不合適的索引。對于 SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01' 這個(gè)查詢，優(yōu)化器可能只選擇了 idx_category_id 或 idx_brand_id 其中一個(gè)索引，從而無法有效地結(jié)合兩個(gè)條件進(jìn)行查詢。
mysql> SELECT * FROM product_info FORCE INDEX (idx_category_id) WHERE category_id='C02' AND brand_id='B01';+------------+-------------+----------+| product_id | category_id | brand_id |+------------+-------------+----------+| P003 | C02 | B01 || P002 | C02 | B01 |+------------+-------------+----------+2 rows in set (0.00 sec)
mysql> SELECT * FROM product_info FORCE INDEX (idx_brand_id) WHERE category_id='C02' AND brand_id='B01';+------------+-------------+----------+| product_id | category_id | brand_id |+------------+-------------+----------+| P003 | C02 | B01 || P002 | C02 | B01 |+------------+-------------+----------+
可見強(qiáng)制走其中一個(gè)索引都能正常
索引合并問題
以上可以看出優(yōu)化器選擇使用索引合并（如 index merge intersect），將 idx_category_id 和 idx_brand_id 的結(jié)果合并，但由于主鍵降序排列等因素，可能會(huì)導(dǎo)致兩個(gè)索引的結(jié)果無法正確交集，進(jìn)而出現(xiàn)查詢結(jié)果為空的情況。因此我們關(guān)閉index_merge_intersection或者index_merge測試一下：
mysql> SET optimizer_switch='index_merge_intersection=off';Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM product_info FORCE INDEX (idx_brand_id) WHERE category_id='C02' AND brand_id='B01';+------------+-------------+----------+| product_id | category_id | brand_id |+------------+-------------+----------+| P003 | C02 | B01 || P002 | C02 | B01 |+------------+-------------+----------+2 rows in set (0.00 sec)
關(guān)閉后確實(shí)可以了。另外關(guān)閉
2.3 主鍵降序排列的影響
二級索引結(jié)構(gòu)
主鍵采用降序排列可能會(huì)對二級索引的存儲(chǔ)結(jié)構(gòu)和掃描方向產(chǎn)生影響。在查詢時(shí)，可能會(huì)因?yàn)檫@種影響導(dǎo)致索引無法正常工作，從而無法正確檢索到符合條件的記錄。
我們建一張product_info2表，再導(dǎo)入原樣的數(shù)據(jù)，再查詢一遍
mysql> CREATE TABLE product_info2( -> product_id VARCHAR(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '產(chǎn)品編號', -> category_id VARCHAR(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '類別編號', -> brand_id VARCHAR(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '品牌編號', -> PRIMARY KEY(`product_id` ), -> KEY `idx_brand_id`(`brand_id`), -> KEY idx_category_id(category_id) -> ) -> DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;Query OK, 0 rows affected (0.01 sec) mysql> insert into product_info2 select * from product_info;Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0 mysql> SET optimizer_switch='index_merge_intersection=off';Query OK, 0 rows affected (0.00 sec) mysql> SET optimizer_switch='index_merge_intersection=on';Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01';Empty set (0.00 sec) mysql> SELECT * FROM product_info2 WHERE category_id='C02' AND brand_id='B01';+------------+-------------+----------+| product_id | category_id | brand_id |+------------+-------------+----------+| P002 | C02 | B01 || P003 | C02 | B01 |+------------+-------------+----------+2 rows in set (0.00 sec)
通過對比可以發(fā)現(xiàn)，修改為非降序索引后確實(shí)也正常了。
2.4 MySQL 版本兼容性
不同的 MySQL 版本對降序索引的支持和處理方式可能存在差異。某些舊版本可能存在與降序索引相關(guān)的 bug，導(dǎo)致在使用降序主鍵和二級索引進(jìn)行查詢時(shí)出現(xiàn)問題。出現(xiàn)問題的版本是MySQL8.0.40，我們用MySQL8.0.41再看一下，發(fā)現(xiàn)新版本已經(jīng)解決
3. 小結(jié)
本次問題的本質(zhì)是 MySQL 8.0.40 版本中，降序主鍵與索引合并交集模式的底層邏輯沖突—— 二級索引的存儲(chǔ)結(jié)構(gòu)受降序主鍵影響，導(dǎo)致索引合并時(shí)無法正確計(jì)算結(jié)果交集，最終查詢 “丟失” 數(shù)據(jù)。通過逐層排查，我們定位了核心誘因，并提供了緊急規(guī)避與長期優(yōu)化方案，即：
- 盡量不要使用降序主鍵，如需使用降序特性，建議創(chuàng)建二級索引解決
- 如非必要不要開啟index_merge或index_merge_intersection，以免導(dǎo)致性能問題或檢索錯(cuò)誤問題，如果需要，可以考慮先建組合索引解決
- 以上案例和數(shù)據(jù)自身也有關(guān)系，只是部分?jǐn)?shù)據(jù)會(huì)出現(xiàn)此情況，大家如需復(fù)現(xiàn)可以用我案例中的數(shù)據(jù)進(jìn)行測試
因此，在平時(shí)數(shù)據(jù)庫運(yùn)維中，看似 “匪夷所思” 的異常，往往與版本 BUG、索引策略或表結(jié)構(gòu)設(shè)計(jì)相關(guān)。遇到類似問題時(shí)，可按 “驗(yàn)證數(shù)據(jù)→排查索引→測試版本兼容性” 的思路定位，同時(shí)優(yōu)先選擇經(jīng)過實(shí)踐驗(yàn)證的表結(jié)構(gòu)與索引設(shè)計(jì)方案，降低踩坑概率。
以上就是MySQL隱蔽BUG：組合條件查詢無故返回空集的排查與規(guī)避方案的詳細(xì)內(nèi)容，更多關(guān)于MySQL BUG組合條件查詢無故返回空集的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了mysql中DCL常用的用戶和權(quán)限控制,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-03-03
隔離性其實(shí)比想象要復(fù)雜。在SQL中定義了四種隔離的級別，每一種隔離級別都規(guī)定了一個(gè)事務(wù)中的修改，哪些是在事務(wù)內(nèi)和事務(wù)間是可見的，哪些是不可見的。較低級別的隔離通常來說能承受更高的并發(fā)，系統(tǒng)的開銷也會(huì)更小2021-09-09
MySQL大表數(shù)據(jù)的分區(qū)與分庫分表的實(shí)現(xiàn)
數(shù)據(jù)庫的分區(qū)和分庫分表是兩種常用的技術(shù)方案,本文主要介紹了MySQL大表數(shù)據(jù)的分區(qū)與分庫分表的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-03-03
從MySQL 7升級到MySQL 8的避坑指南與實(shí)操指南
MySQL 8 作為里程碑式的版本,帶來了諸多重磅特性,但從 MySQL 7升級并非一鍵無腦更,涉及語法、配置、權(quán)限、數(shù)據(jù)類型等多維度兼容問題,本文結(jié)合實(shí)戰(zhàn)經(jīng)驗(yàn),梳理升級核心注意事項(xiàng)、實(shí)操步驟與常見坑點(diǎn),需要的朋友可以參考下2026-03-03
這篇文章主要介紹了MySQL中的交叉連接、自然連接和內(nèi)連接查詢,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-04-04
這篇文章主要給大家介紹了關(guān)于MYSQL慢查詢與日志的設(shè)置與測試，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
批量 kill mysql 中運(yùn)行時(shí)間長的sql
這篇文章主要介紹了批量 kill mysql 中運(yùn)行時(shí)間長的sql,需要的朋友可以參考下2016-01-01
MySQL數(shù)據(jù)類型中DECIMAL的用法實(shí)例詳解
這篇文章主要介紹了MySQL數(shù)據(jù)類型中DECIMAL的用法實(shí)例詳解的相關(guān)資料,希望通過本文能幫助到大家，需要的朋友可以參考下2017-10-10
mysql optimizer_switch查詢優(yōu)化器優(yōu)化策略
查詢優(yōu)化器是一個(gè)至關(guān)重要的組件,它負(fù)責(zé)確定執(zhí)行 SQL 查詢的最有效方法,本文主要介紹了mysql optimizer_switch查詢優(yōu)化器優(yōu)化策略,感興趣的可以了解一下2024-06-06
SSM實(shí)現(xiàn)mysql數(shù)據(jù)庫賬號密碼密文登錄功能
這篇文章主要介紹了SSM實(shí)現(xiàn)mysql數(shù)據(jù)庫賬號密碼密文登錄功能，本文分為三步給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值 ,需要的朋友可以參考下2019-08-08