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

数据库干货铺 6天前

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

1. 问题背

数据库版本：MySQL8.0.40

假设我们创建了一个名为 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;
复制

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

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
的记录：

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 字符集和校对规则方面

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

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
其中一个索引，从而无法有效地结合两个条件进行查询。

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

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

索引合并问题
以上可以看出优化器选择使用索引合并（如 index merge intersect
），将 idx_category_id
和 idx_brand_id
的结果合并，但由于主键降序排列等因素，可能会导致两个索引的结果无法正确交集，进而出现查询结果为空的情况。因此我们关闭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表，再导入原样的数据，再查询一遍

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)




复制