MySQL 5.7中BigInt列的正确比较方法及性能优化

我查看了StackOverflow上的《Mysql 5.0.91 BIGINT column value comparison with '1'》帖子，试图寻找BigInt列比较的最佳实践，但未找到相关内容。

我有一个类型为BigInt(20)的列，在查询的WHERE子句中使用IN()对该列进行比较时，查询性能产生了较大影响。尽管该列已创建索引，但移除IN()条件后，查询性能大幅提升。

因此想咨询：针对该场景的最佳比较方式是什么？IN()是推荐做法吗？是否有更优的实现方案？

表结构（已模糊处理） #

Field,Type,Null,Key,Default,Extra
id,bigint(20),NO,PRI,NULL,auto_increment
status,varchar(64),NO,,NULL,
dono_id,bigint(20),YES,,NULL,
dono_tipo,varchar(64),YES,MUL,NULL,
MyBigIntField,bigint(20),YES,MUL,NULL

查询语句 #

explain select
  DISTINCT(MyBigIntField)
FROM
  MyTable
WHERE
  MyBigIntField IN ('16', '49', '58', '155', '226')
  AND NOT (status = 'Failure')
  AND dono_id <> 1106
  and dono_tipo = 'Purchase';

执行计划 #

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "23.21"
    },
    "duplicates_removal": {
      "using_filesort": false,
      "table": {
        "table_name": "MyTable",
        "access_type": "range",
        "possible_keys": [
          "idx_MyTable_owner",
          "IDX_MyTable_PI"
        ],
        "key": "IDX_MyTable_PI",
        "used_key_parts": [
          "MyBigIntField"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 13,
        "rows_produced_per_join": 5,
        "filtered": "45.00",
        "index_condition": "(`MyDatabase`.`MyTable`.`MyBigIntField` in (16,49,58,155,226))",
        "cost_info": {
          "read_cost": "22.04",
          "eval_cost": "1.17",
          "prefix_cost": "23.21",
          "data_read_per_join": "231K"
        },
        "used_columns": [
          "id",
          "status",
          "dono_id",
          "dono_tipo",
          "MyBigIntField"
        ],
        "attached_condition": "((`MyDatabase`.`MyTable`.`status` <> 'Failure') and (`MyDatabase`.`MyTable`.`dono_id` <> 1106) and (`MyDatabase`.`MyTable`.`dono_tipo` = 'Purchase'))"
      }
    }
  }
}

1. 先修正一个基础问题：去掉IN()里的字符串引号 #

你的查询中MyBigIntField IN ('16', '49', ...)给数值加了字符串引号，虽然MySQL会自动做类型转换，但这会触发隐性类型转换，导致索引无法高效匹配——数据库需要先把每个字符串转成BIGINT再做比较，直接拖慢性能。改成IN(16,49,58,155,226)（去掉引号），这是最直接的性能提升点。

2. IN()本身不是问题，核心是优化索引策略 #

从执行计划看，当前已经用到了IDX_MyTable_PI索引，但后续还要过滤status、dono_id、dono_tipo三个条件，意味着数据库用索引找到符合IN条件的行后，还要回表查询原数据做过滤，额外增加IO开销。

最优方案：创建覆盖联合索引

针对你的查询条件，建议创建覆盖联合索引，让数据库只扫索引就能完成所有过滤和查询：

CREATE INDEX idx_MyTable_composite ON MyTable(dono_tipo, MyBigIntField, status, dono_id);

索引顺序设计逻辑：

• 把过滤性强的dono_tipo = 'Purchase'放在最前面，快速缩小数据范围；
• 接着是MyBigIntField，直接匹配IN条件；
• 最后把status和dono_id加入索引，实现覆盖查询，不需要回表读取原表数据。

3. 仅当IN列表极大时，考虑替代方案 #

如果你的IN列表包含几百上千个值，IN()可能导致执行计划退化，这时可以用两种替代方案：

• 临时表+JOIN：

  CREATE TEMPORARY TABLE temp_ids (id bigint(20) PRIMARY KEY);
  INSERT INTO temp_ids VALUES(16),(49),(58),(155),(226);
  SELECT DISTINCT t.MyBigIntField
  FROM MyTable t
  JOIN temp_ids ti ON t.MyBigIntField = ti.id
  WHERE t.dono_tipo = 'Purchase' AND t.status != 'Failure' AND t.dono_id != 1106;
  

• EXISTS子查询：

  SELECT DISTINCT t.MyBigIntField
  FROM MyTable t
  WHERE EXISTS (
      SELECT 1 FROM (
          SELECT 16 AS id UNION ALL SELECT 49 UNION ALL SELECT 58 UNION ALL SELECT 155 UNION ALL SELECT 226
      ) ti WHERE ti.id = t.MyBigIntField
  ) AND t.dono_tipo = 'Purchase' AND t.status != 'Failure' AND t.dono_id != 1106;
  

但你的场景里IN只有5个值，IN()本身是高效的，没必要用这些替代方案，重点还是优化索引和去掉类型转换。

4. DISTINCT的自动优化 #

因为你只查询MyBigIntField，如果用了上面的覆盖联合索引，DISTINCT的成本会极低——索引本身是有序的，数据库可以直接在索引层面完成去重，不需要额外排序或哈希操作。

内容的提问来源于stack exchange，提问作者Daniel Ferreira Castro