Mysql多组字符串组合条件查询耗时30秒求助

表结构 #

create table my_table
(
    id          int auto_increment
        primary key,
    name        varchar(255)                             null,
    vendor_name varchar(250) default ''                  not null,
    constraint id
        unique (id, name, vendor_name),
)
create index name
    on my_table (name);

create index vendor_name
    on my_table (vendor_name);

create index tuple_index
    on my_table (name, vendor_name);

数据与查询情况 #

• 表内约200万行数据
• 执行查询语句：

SELECT *
FROM my_table
WHERE (name, vendor_name) in ((str1, str2), (str3, str4), ..., (str6000, str6001))

• 查询返回约300条结果，但耗时约30秒

EXPLAIN分析结果 #

| id | select_type | table    | type | possible_keys                | key | key_len | ref | rows    | Extra       |
| -- | ----------- | -------- | ---- | ---------------------------- | --- | ------- | --- | ------- | ----------- |
|  1 | SIMPLE      | my_table | ALL  | name,vendor_name,tuple_index |     |         |     | 1947772 | Using where |

疑问 #

针对字符串列的这类SELECT查询耗时如此之久是否正常？

这种耗时完全不正常，核心问题是MySQL没有使用你创建的tuple_index联合索引，而是执行了全表扫描（type: ALL），这才导致200万行数据的查询耗时飙升至30秒。

为什么索引没被用到？ #

MySQL对(col1, col2) IN ((val1, val2), ...)这种多值元组IN查询的索引支持，在部分场景下会出现优化器选择偏差——尤其是当IN列表中的元组数量过多（这里达6000组）时，优化器可能错误判断全表扫描的成本更低，从而放弃索引。

解决办法 #

1. 强制使用联合索引
   在查询中添加FORCE INDEX(tuple_index)，强制优化器使用联合索引定位匹配数据：

   SELECT *
   FROM my_table FORCE INDEX(tuple_index)
   WHERE (name, vendor_name) in ((str1, str2), (str3, str4), ..., (str6000, str6001))
   

   这会直接通过联合索引快速定位目标元组，避免全表扫描，能大幅缩短耗时。

2. 改用临时表+JOIN查询
   如果强制索引效果不佳，可以将6000组元组插入临时表，再通过JOIN获取结果：

   -- 创建临时表并添加索引
   CREATE TEMPORARY TABLE temp_pairs (
       name varchar(255),
       vendor_name varchar(250),
       PRIMARY KEY (name, vendor_name)
   );
   -- 批量插入待匹配的元组
   INSERT INTO temp_pairs VALUES ('str1','str2'), ('str3','str4'), ..., ('str6000','str6001');
   -- 通过JOIN查询目标数据
   SELECT t.*
   FROM my_table t
   JOIN temp_pairs tp ON t.name = tp.name AND t.vendor_name = tp.vendor_name;
   

   临时表的主键索引会让JOIN操作效率极高，适合处理大量元组匹配的场景。

3. 更新表统计信息
   执行ANALYZE TABLE my_table;更新表的统计信息，让优化器能更准确地评估索引扫描和全表扫描的成本，从而做出正确选择。

4. 检查MySQL版本
   较旧的MySQL版本（如5.7之前）对多列IN元组的索引支持不完善，升级到8.0版本可能会优化优化器的判断逻辑，自动选择合适的索引。

额外优化建议 #

你的表中存在冗余的唯一约束unique (id, name, vendor_name)——因为id已经是自增主键（唯一且非空），这个约束没有实际意义，建议删除以减少表的维护成本。

内容的提问来源于stack exchange，提问作者Muslimbek Abduganiev