SQL如何高效从全量地点表中筛选指定1300条地点数据

现有存储地点信息的location表，共约3000条地点记录，需从全量数据中查询指定1300个地点的详情。当前实现为在WHERE子句中枚举所有location_id通过IN条件匹配，查询耗时较长，原使用的SQL存在字段重复问题，语句如下：

select location_id,location_id,location_code from location where location_id in ('','',''......)

表结构共包含三个字段：location_id、location_name、location_code。

• 优先确认location_id的索引配置
  location_id作为地点的唯一标识，应设置为主键或唯一索引。如果当前没有为该字段建索引，IN查询会走全表扫描，性能差。添加主键索引的语句如下：

  ALTER TABLE location ADD PRIMARY KEY (location_id);
  

  主键索引的等值匹配效率极高，3000条量级的表即使匹配1300条数据，加索引后响应速度会有明显提升。

• 替换超长IN列表为临时表关联查询
  1300个参数直接拼在IN列表中，会大幅提升SQL解析成本，还可能触发SQL长度限制、预编译缓存失效问题。更高效的实现方式是先把待查询的location_id存入临时表，再通过关联查询取数：

  1. 创建临时表，字段类型和location表的location_id保持一致：

  CREATE TEMPORARY TABLE tmp_target_loc (
    location_id VARCHAR(64) NOT NULL PRIMARY KEY
  );
  

  2. 批量将1300个待查询的location_id插入临时表后，执行关联查询：

  SELECT
    l.location_id,
    l.location_name,
    l.location_code
  FROM location l
  INNER JOIN tmp_target_loc t
  ON l.location_id = t.location_id;
  

  该写法通过两张表的主键索引做关联，避免了超长SQL的解析开销，查询效率明显优于长IN列表写法。

• 固定查询场景直接加标记字段
  如果这1300个查询地点是固定集合、不是每次动态传入的，可以直接在location表新增TINYINT类型的标记字段（如is_target），将目标地点的标记值设为1，其余设为0，同时给该字段加索引，查询时直接按标记过滤即可：

  -- 新增字段并加索引
  ALTER TABLE location ADD COLUMN is_target TINYINT NOT NULL DEFAULT 0;
  ALTER TABLE location ADD INDEX idx_is_target(is_target);
  -- 查询语句
  SELECT location_id,location_name,location_code FROM location WHERE is_target = 1;
  

  该方案是固定场景下性能最高的实现，没有额外关联、参数解析开销。

注意：原查询语句重复查询了两次location_id，漏掉了业务需要的location_name字段，实际使用时需要修正字段列表。

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