10万+数据表的PHP+SQL模糊搜索查询优化方案咨询

先解决当前查询的核心痛点 #

你当前执行的SQL存在两个致命性能问题：

SELECT id FROM table_1 WHERE username LIKE '%$search%' OR record_data LIKE '%$search%'

• 前缀通配符%xxx会让普通B树索引直接失效，数据库只能执行全表扫描，10万+数据每次全扫自然耗时过长。
• OR条件会放大查询开销，数据库无法高效利用单字段索引。

优先尝试这些低成本、高收益的优化手段：

1. 改用全文索引
   如果你的数据库支持原生全文索引（比如MySQL、PostgreSQL），给username和record_data创建联合全文索引，然后用全文查询替代LIKE：

   SELECT id FROM table_1 
   WHERE MATCH(username, record_data) AGAINST('$search' IN BOOLEAN MODE);
   

   全文索引专门为模糊搜索做了优化，查询速度比全表扫描快几个数量级，10万级数据完全能轻松应对。
2. 拆分OR为UNION（临时过渡方案）
   要是暂时没法用全文索引，把OR拆成两个独立查询用UNION合并，这样每个子查询可以单独尝试利用字段索引（若为后缀模糊场景）：

   SELECT id FROM table_1 WHERE username LIKE '%$search%'
   UNION
   SELECT id FROM table_1 WHERE record_data LIKE '%$search%';
   

   UNION会自动去重，比直接用OR的效率更高，因为数据库可以分别优化两个子查询的执行计划。
3. 尽量避免前缀模糊
   如果业务场景允许，引导用户使用后缀模糊（比如username LIKE 'xxx%'），这种情况下普通B树索引就能生效，查询速度会大幅提升。

关于分表的必要性 #

10万+数据量完全不需要分表。分表是应对百万级以上数据量或单表存储/IO压力过载的方案，现在就分表只会增加系统复杂度（比如跨表查询逻辑、数据路由规则），属于过度设计。

只有当未来数据量持续增长到百万级，且全文索引、索引优化等手段已经无法满足性能需求时，再考虑分表。分表策略要结合业务场景：比如按用户ID哈希分表，或者按数据创建时间分表。

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