MySQL隱蔽BUG：組合條件查詢無故返回空集的排查與規(guī)避方案
引言
在數(shù)據(jù)庫日常運維中，“查詢結果不符合預期” 是高頻問題，但多數(shù)情況可歸因于 SQL 語法、數(shù)據(jù)異?；蛩饕O計。而本次遇到的案例，卻源于 MySQL 的底層 BUG—— 明明數(shù)據(jù)存在，單一條件查詢正常，疊加一個過濾條件后竟返回空集，著實令人費解。本文將完整還原問題場景、排查過程，以及最終的解決方案。
1. 問題背景
數(shù)據(jù)庫版本：MySQL8.0.40
假設我們創(chuàng)建了一個名為 product_info 的表，用于存儲產品的相關信息。該表包含三個字段：product_id（產品編號）、category_id（類別編號）和 brand_id（品牌編號）。其中，product_id 被設置為主鍵，并且采用降序排列。
CREATE TABLE product_info( product_id VARCHAR(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '產品編號', 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ù)插入完成后，我們進行了兩次查詢操作。第一次查詢是篩選出 category_id 為 C02 的記錄：
SELECT * FROM product_info WHERE category_id='C02';
這次查詢正常返回了兩條記錄，結果如下：
+------------+-------------+----------+| product_id | category_id | brand_id |+------------+-------------+----------+| P003 | C02 | B01 || P002 | C02 | B01 |+------------+-------------+----------+
然而，當我們進行第二次查詢，增加了 brand_id 為 B01 的條件時：
mysql> SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01';Empty set (0.00 sec)
本應返回上述兩條記錄，但實際結果卻為空集，這顯然與預期不符。
2. 問題分析及排查
2.1 字符集和校對規(guī)則方面
表和字段都采用了 utf8mb4_general_ci 字符集和校對規(guī)則。通常情況下，對于數(shù)字和字母組成的字符串比較，這種校對規(guī)則不會出現(xiàn)問題。但我們不能排除隱式類型轉換或者存在不可見字符的可能性。為了驗證這一點，我們可以使用 HEX 函數(shù)查看 brand_id 的實際存儲值：
SELECT product_id, category_id, brand_id, HEX(brand_id) FROM product_info WHERE category_id='C02';
如果 brand_id 的值確實是 B01，那么 HEX 函數(shù)的結果應該是 423031。若結果中出現(xiàn)其他字符，比如尾隨空格，可能會導致比較時出現(xiàn)不匹配的情況。但是此案例明顯不是。
2.2 索引相關問題
索引選擇問題
當執(zhí)行組合條件查詢時，優(yōu)化器可能會選擇不合適的索引。對于 SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01' 這個查詢，優(yōu)化器可能只選擇了 idx_category_id 或 idx_brand_id 其中一個索引，從而無法有效地結合兩個條件進行查詢。
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 |+------------+-------------+----------+
可見強制走其中一個索引都能正常
索引合并問題
以上可以看出優(yōu)化器選擇使用索引合并（如 index merge intersect），將 idx_category_id 和 idx_brand_id 的結果合并，但由于主鍵降序排列等因素，可能會導致兩個索引的結果無法正確交集，進而出現(xià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)
關閉后確實可以了。另外關閉
2.3 主鍵降序排列的影響
二級索引結構
主鍵采用降序排列可能會對二級索引的存儲結構和掃描方向產生影響。在查詢時，可能會因為這種影響導致索引無法正常工作，從而無法正確檢索到符合條件的記錄。
我們建一張product_info2表，再導入原樣的數(shù)據(jù)，再查詢一遍
mysql> CREATE TABLE product_info2( -> product_id VARCHAR(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '產品編號', -> 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)，修改為非降序索引后確實也正常了。
2.4 MySQL 版本兼容性
不同的 MySQL 版本對降序索引的支持和處理方式可能存在差異。某些舊版本可能存在與降序索引相關的 bug，導致在使用降序主鍵和二級索引進行查詢時出現(xiàn)問題。出現(xiàn)問題的版本是MySQL8.0.40，我們用MySQL8.0.41再看一下，發(fā)現(xiàn)新版本已經解決
3. 小結
本次問題的本質是 MySQL 8.0.40 版本中，降序主鍵與索引合并交集模式的底層邏輯沖突—— 二級索引的存儲結構受降序主鍵影響，導致索引合并時無法正確計算結果交集，最終查詢 “丟失” 數(shù)據(jù)。通過逐層排查，我們定位了核心誘因，并提供了緊急規(guī)避與長期優(yōu)化方案，即：
- 盡量不要使用降序主鍵，如需使用降序特性，建議創(chuàng)建二級索引解決
- 如非必要不要開啟index_merge或index_merge_intersection，以免導致性能問題或檢索錯誤問題，如果需要，可以考慮先建組合索引解決
- 以上案例和數(shù)據(jù)自身也有關系，只是部分數(shù)據(jù)會出現(xiàn)此情況，大家如需復現(xiàn)可以用我案例中的數(shù)據(jù)進行測試
因此，在平時數(shù)據(jù)庫運維中，看似 “匪夷所思” 的異常，往往與版本 BUG、索引策略或表結構設計相關。遇到類似問題時，可按 “驗證數(shù)據(jù)→排查索引→測試版本兼容性” 的思路定位，同時優(yōu)先選擇經過實踐驗證的表結構與索引設計方案，降低踩坑概率。
以上就是MySQL隱蔽BUG：組合條件查詢無故返回空集的排查與規(guī)避方案的詳細內容，更多關于MySQL BUG組合條件查詢無故返回空集的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了MySQL中order?by排序語句的原理,本文結合示例代碼給大家講解的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-12-12
SQL?ALTER?TABLE語句靈活修改表結構和數(shù)據(jù)類型
這篇文章主要介紹了SQL?ALTER?TABLE語句靈活修改表結構和數(shù)據(jù)類型,有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-12-12
優(yōu)化MySQL數(shù)據(jù)庫中的查詢語句詳解
這篇文章主要介紹了優(yōu)化MySQL數(shù)據(jù)庫中的查詢語句,非常實用的經驗總結,需要的朋友可以參考下2014-07-07
寫SQL語句我們經常需要判斷一個字符串中是否包含另一個字符串,下面這篇文章主要給大家介紹了關于mysql判斷字符串是否存在的幾種常見方式,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-05-05
MySQL統(tǒng)計今日生成create_time的數(shù)據(jù)量的方法小結
create_time通常是一個用于表示某個實體或事件創(chuàng)建時間的字段,在數(shù)據(jù)庫設計、日志記錄或許多軟件系統(tǒng)中常見,它存儲的是一個日期或時間戳,記錄了數(shù)據(jù)首次被創(chuàng)建的具體時刻,本文介紹了MySQL統(tǒng)計今日生成create_time的數(shù)據(jù)量的方法,需要的朋友可以參考下2024-08-08
本篇內容是小編日常收集整理常見的mysql sql技巧，對大家學習mysql sql技巧相關內容有所幫助，感興趣的朋友一起學習吧2015-12-12
Windows server 2008 r2下MySQL5.7.17 winx64安裝版配置方法圖文教程
這篇文章主要為大家詳細介紹了Windows server 2008 r2下MySQL5.7.17 winx64安裝版配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-03-03
這篇文章主要介紹了MySQL創(chuàng)建索引/判斷索引是否生效的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
Linux自動備份MySQL數(shù)據(jù)庫腳本代碼
下面這段Linux的Shell腳本用于每日自動備份MySQL數(shù)據(jù)庫，可通過Linux的crontab每天定時執(zhí)行2013-11-11
這篇文章主要介紹了MySQL?去重實例操作詳情，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的朋友可以參考一下2022-07-07