MySQL数据库BUG导致查询不到本该查到的数据

在数据库的日常使用中，我们常常会遇到一些看似匪夷所思的查询问。最近就看到一个因为MySQL BUG导致无法查到本该查询到数据的案例。

1. 问题背

数据库版本：MySQL8.0.40

假设我们创建了一个名为 product_info 的表，用于存储产品的相关信息。该表包含三个字段：product_id（产品编号）、category_id（类别编号）和 brand_id（品牌编号）。其中，product_id 被设置为主键，并且采用降序排列。

代码语言：javascript代码运行次数：0运行复制

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;

以下是创建表的 SQL 语句：随后，我们向表中插入了一些数据：

代码语言：javascript代码运行次数：0运行复制

INSERT INTO product_info VALUES
('P001','C01','B02'),
('P002','C02','B01'),
('P003','C02','B01'),
('P004','C01','B02'),
('P005','C03','B01'),
('P006','C03','B01');

数据插入完成后，我们进行了两次查询操作。第一次查询是筛选出 category_id 为 C02 的记录：

代码语言：javascript代码运行次数：0运行复制

SELECT * FROM product_info WHERE category_id='C02';

这次查询正常返回了两条记录，结果如下：

代码语言：javascript代码运行次数：0运行复制

+------------+-------------+----------+
| product_id | category_id | brand_id |
+------------+-------------+----------+
| P003       | C02         | B01      |
| P002       | C02         | B01      |
+------------+-------------+----------+

然而，当我们进行第二次查询，增加了 brand_id 为 B01 的条件时：

代码语言：javascript代码运行次数：0运行复制

mysql> SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01';
Empty set (0.00 sec)

本应返回上述两条记录，但实际结果却为空集，这显然与预期不符。

2. 问题分析及排查

2.1 字符集和校对规则方面

表和字段都采用了 utf8mb4_general_ci 字符集和校对规则。通常情况下，对于数字和字母组成的字符串比较，这种校对规则不会出现问题。但我们不能排除隐式类型转换或者存在不可见字符的可能性。为了验证这一点，我们可以使用 HEX 函数查看 brand_id 的实际存储值：

代码语言：javascript代码运行次数：0运行复制

SELECT product_id, category_id, brand_id, HEX(brand_id) FROM product_info WHERE category_id='C02';

如果 brand_id 的值确实是 B01，那么 HEX 函数的结果应该是 423031。若结果中出现其他字符，比如尾随空格，可能会导致比较时出现不匹配的情况。但是此案例明显不是。

2.2 索引相关问题

• 索引选择问题当执行组合条件查询时，优化器可能会选择不合适的索引。对于 SELECT * FROM product_info WHERE category_id='C02' AND brand_id='B01' 这个查询，优化器可能只选择了 idx_category_id 或 idx_brand_id 其中一个索引，从而无法有效地结合两个条件进行查询。

代码语言：javascript代码运行次数：0运行复制

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)

代码语言：javascript代码运行次数：0运行复制

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      |
+------------+-------------+----------+

可见强制走其中一个索引都能正常

• 索引合并问题以上可以看出优化器选择使用索引合并（如 index merge intersect），将 idx_category_id 和 idx_brand_id 的结果合并，但由于主键降序排列等因素，可能会导致两个索引的结果无法正确交集，进而出现查询结果为空的情况。因此我们关闭index_merge_intersection或者index_merge测试一下：

代码语言：javascript代码运行次数：0运行复制

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表，再导入原样的数据，再查询一遍

代码语言：javascript代码运行次数：0运行复制

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)

通过对比可以发现，修改为非降序索引后确实也正常了。

2.4 MySQL 版本兼容性

不同的 MySQL 版本对降序索引的支持和处理方式可能存在差异。某些旧版本可能存在与降序索引相关的 bug，导致在使用降序主键和二级索引进行查询时出现问题。出现问题的版本是MySQL8.0.40，我们用最新的MySQL8.0.41（截止当前最新版本）再看一下，发现新版本已经解决

3. 小结

通过以上案例及分析，最终有如下建议：

• 尽量不要使用降序主键，如需使用降序特性，建议创建二级索引解决
• 如非必要不要开启index_merge或index_merge_intersection，以免导致性能问题或检索错误问题，如果需要，可以考虑先建组合索引解决
• 以上案例和数据自身也有关系，只是部分数据会出现此情况，大家如需复现可以用我案例中的数据进行测试

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。原始发表：2025-03-21，如有侵权请联系 cloudcommunity@tencent 删除数据索引数据库bugmysql