如何优化处理百万级数据的LIKE查询？

分享几个针对这类慢查询的实用优化思路，都是实际项目中验证过的：

• 预处理生成辅助列，彻底避免全表扫描
  你的查询核心是找包含*的sample_id，还要替换*生成term，可以提前把这些操作的结果预存在表中：

  1. 新增两个辅助列：

     ALTER TABLE sample.table 
     ADD COLUMN has_asterisk TINYINT(1) DEFAULT 0 COMMENT '是否包含*',
     ADD COLUMN term VARCHAR(255) COMMENT '替换*后的sample_id';
     

  2. 批量初始化数据：

     UPDATE sample.table 
     SET has_asterisk = 1, term = REPLACE(sample_id, '*', '')
     WHERE sample_id LIKE '%*%';
     

  3. 之后新增/修改数据时，同步更新这两个列（可以用触发器或者业务代码处理）
  4. 最终查询改成：

     SELECT sample_id, term 
     FROM sample.table 
     WHERE has_asterisk = 1 
     ORDER BY sample_id ASC;
     

  给has_asterisk建个普通索引，查询时直接走索引过滤，不需要再做全表扫和实时REPLACE，速度能提几个数量级。

• 用字符串定位函数替代LIKE（小幅提升）
  部分数据库中，LOCATE('*', sample_id) > 0 的执行效率略高于 LIKE '%*%'，虽然本质还是全表扫描，但可以试试替换：

  SELECT sample_id, REPLACE(sample_id, '*', '') AS term
  FROM sample.table
  WHERE LOCATE('*', sample_id) > 0
  ORDER BY sample_id ASC;
  

  这个适合临时救急，长远来看还是辅助列更靠谱。

• 分区表隔离目标数据
  如果你的表数据量极大，可以按has_asterisk做LIST分区，把包含*的行单独放在一个分区里：

  ALTER TABLE sample.table 
  PARTITION BY LIST(has_asterisk) (
      PARTITION p0 VALUES IN (0),
      PARTITION p1 VALUES IN (1)
  );
  

  查询时只会扫描p1分区，避免扫描全表，排序的压力也会小很多。

• 离线同步到专用表（非实时场景首选）
  如果这个查询不是实时需求，比如每天只需要一次结果，可以用定时任务（比如crontab）把符合条件的数据同步到一张专用表：

  TRUNCATE TABLE sample.table_filtered;
  INSERT INTO sample.table_filtered (sample_id, term)
  SELECT sample_id, REPLACE(sample_id, '*', '') AS term
  FROM sample.table
  WHERE sample_id LIKE '%*%'
  ORDER BY sample_id ASC;
  

  业务直接查询sample.table_filtered，速度快到飞起。如果有增量数据，也可以改成增量同步，只处理新增/修改的行。

• 优化分批查询的姿势
  如果你必须用分批查询，别用LIMIT offset, size的方式（offset越大，扫描的数据越多），改用基于sample_id的范围分页：

  -- 第一次查
  SELECT sample_id, REPLACE(sample_id, '*', '') AS term
  FROM sample.table
  WHERE sample_id LIKE '%*%'
  ORDER BY sample_id ASC
  LIMIT 1000;
  
  -- 后续分页，用上一次最后一个sample_id作为条件
  SELECT sample_id, REPLACE(sample_id, '*', '') AS term
  FROM sample.table
  WHERE sample_id LIKE '%*%' AND sample_id > '上次最后一个ID'
  ORDER BY sample_id ASC
  LIMIT 1000;
  

  这样每次查询都能利用sample_id的索引（如果有的话），避免全表扫。

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