290万行表SQL查询慢、REGEXP性能异常优化求助

• 所有WHERE条件中包含的动态计算逻辑（CASE WHEN 函数、price/pricebefore 除法运算、动态拼接的条件）都无法直接命中索引，会触发全表扫描
• name LIKE '%xxx%' 前导通配符的模糊查询无法使用普通B树索引
• link REGEXP 正则匹配默认走全表扫描，观测到的多关键词更快的现象是因为多关键词匹配到的行更少，扫描到符合条件的行后提前终止的逻辑生效，单关键词匹配到的行数更多，扫描耗时更长
• 大偏移量OFFSET分页会先扫描丢弃大量前序行，数据量越大耗时越高

1. 改写SQL消除动态计算，新增预计算字段 #

把所有需要在WHERE里计算的逻辑改成表的实列，新增字段后提前写入或者用触发器自动更新：

• 新增discount_rate字段，存储100 - price/pricebefore * 100的值，替代原来的动态计算
• 新增price_compare字段，存储CASE WHEN pricebefore3 IS NULL THEN pricebefore ELSE pricebefore3*1.5 END的计算结果
• 新增merchant_tag枚举/字符串字段，提前把link对应的商家标识存下来，比如amazon、idealo这类，替代REGEXP匹配逻辑

所有动态参数统一用mysqli预处理?占位符绑定，不要直接拼接字符串，既避免SQL注入风险，也能让MySQL复用执行计划，修改后的查询逻辑参考：

SELECT id, name, price, pricebefore, link, imagelink, updated, site, siteid 
FROM items 
WHERE price_compare >= pricebefore 
AND price < pricebefore 
AND isbn != -1 
AND (
  (discount_rate > 90 AND updated < NOW() - INTERVAL ? MINUTE)
  OR (discount_rate <=90 AND discount_rate > ?)
)
-- 商家筛选直接用预存字段，不需要REGEXP
AND merchant_tag IN (?)
-- 关键词搜索用全文索引方案
AND MATCH(name) AGAINST(? IN BOOLEAN MODE)
ORDER BY updated DESC 
LIMIT ? OFFSET ?

2. 索引优化 #

• 创建联合覆盖索引：idx_query(updated DESC, discount_rate, isbn, price, pricebefore, merchant_tag)，把排序字段、高频筛选字段放在索引前列，尽可能让查询直接走索引不需要回表
• 全文索引优化名称搜索：给name字段创建全文索引，把name LIKE '%xxx%'改成MATCH(name) AGAINST('xxx' IN BOOLEAN MODE)，性能比前导通配符LIKE高10~100倍；如果有中文/短词搜索需求，可以用MySQL 8.0的NGram全文索引
• 单独给merchant_tag加普通索引，替代原有的REGEXP正则匹配逻辑

3. 分页优化 #

避免大OFFSET分页，改成基于最后一条记录的排序字段滚动分页：比如上次查询最后一条的updated值是last_updated，id是last_id，下一页查询就加AND (updated < ? OR (updated = ? AND id < ?))，去掉OFFSET，直接用索引定位起始位置，大分页场景下性能提升非常明显。

4. REGEXP异常问题补充说明 #

你观察到的多正则分支更快的现象符合MySQL执行逻辑：MySQL正则匹配是逐行扫描，匹配到符合条件的行就会进入下一步判断，当正则包含多个分支时，符合条件的行占比更低，扫描更少行数就能拿到LIMIT需要的结果，单分支正则符合条件的行更多，需要扫描更多行才能凑够LIMIT的数量，所以耗时更长，换成预存的merchant_tag字段筛选后就能完全避免这个问题。

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