本文介绍在千万级 mysql 表中，如何避免重复全表扫描，在返回前10条匹配结果的同时快速获取总记录数，涵盖索引优化、全文检索、物化统计及异步策略等专业实践。
本文介绍在千万级 mysql 表中，如何避免重复全表扫描，在返回前10条匹配结果的同时快速获取总记录数，涵盖索引优化、全文检索、物化统计及异步策略等专业实践。
在处理超大规模数据（如 1000 万+ 行）时，常见的「先查数据再 COUNT」模式极易引发性能瓶颈。例如以下典型场景：
-- 快：仅扫描必要行，利用 LIMIT 早期终止 SELECT * FROM `bigTable` WHERE `name` LIKE '%steve%' LIMIT 10; -- 慢：必须遍历所有满足条件的行（即使只用 COUNT），无法利用 LIMIT 优化 SELECT COUNT(1) FROM `bigTable` WHERE `name` LIKE '%steve%';
问题根源在于 LIKE '%steve%' 是前导通配符模糊查询，导致无法使用常规 B-Tree 索引进行范围扫描，MySQL 被迫执行全表扫描（或全索引扫描）以完成计数——即使实际只需返回前10条结果。
✅ 推荐优化路径（按优先级排序）
1. 启用 FULLTEXT 全文索引（首选）
适用于自然语言搜索场景，显著加速含通配符的文本匹配，并支持高效计数：
-- 创建 FULLTEXT 索引（需 MyISAM 或 InnoDB ≥ 5.6）
ALTER TABLE `bigTable` ADD FULLTEXT(`name`);
-- 查询：返回匹配记录 + 获取准确总数（均走索引）
SELECT * FROM `bigTable`
WHERE MATCH(`name`) AGAINST('steve' IN NATURAL LANGUAGE MODE)
LIMIT 10;
-- 同时获取总数（毫秒级）
SELECT COUNT(*) FROM `bigTable`
WHERE MATCH(`name`) AGAINST('steve' IN NATURAL LANGUAGE MODE);⚠️ 注意：FULLTEXT 不支持 '%steve%' 这类纯通配符语法，但 AGAINST('steve') 在自然语言模式下可匹配包含 "steve" 的词项（如 "Steven", "mysteve"），且可通过布尔模式（'steve*'）支持后缀通配。
2. 构建倒排索引表（精准可控）
当需要严格匹配子串（如必须命中 'steve' 作为子串）且 FULLTEXT 不适用时，可维护轻量级倒排表：
-- 倒排表：word → row_id 映射（建议用 TINYINT/SMALLINT 分区优化） CREATE TABLE `name_word_index` ( `word` VARCHAR(32) NOT NULL, `row_id` BIGINT UNSIGNED NOT NULL, PRIMARY KEY (`word`, `row_id`), INDEX `idx_word` (`word`) ) ENGINE=InnoDB; -- 插入/更新时通过触发器自动同步（示例：INSERT） DELIMITER $$ CREATE TRIGGER `bigTable_after_insert` AFTER INSERT ON `bigTable` FOR EACH ROW BEGIN INSERT INTO `name_word_index` (`word`, `row_id`) SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.`name`, ' ', n.n), ' ', -1) AS word, NEW.id FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) n WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.`name`, ' ', n.n), ' ', -1) LIKE '%steve%'; END$$ DELIMITER ;
查询时转为高效等值连接：
SELECT t.* FROM `bigTable` t INNER JOIN `name_word_index` i ON t.id = i.row_id WHERE i.word = 'steve' LIMIT 10; -- 总数秒出 SELECT COUNT(*) FROM `name_word_index` WHERE `word` = 'steve';
3. 客户端异步 + 缓存策略（用户体验最优）
若实时总数非强需求，推荐前端分离加载：
- PHP 首先返回 LIMIT 10 结果（
- 后端异步启动计数任务，结果写入 Redis（键：count:steve，TTL=300s）；
- 前端轮询或 WebSocket 推送总数，用户无感知等待。
// 示例：PHP 异步计数（使用消息队列更佳）
if (!($total = $redis->get("count:steve"))) {
$total = $pdo->query("SELECT COUNT(*) FROM `bigTable` WHERE `name` LIKE '%steve%'")->fetchColumn();
$redis->setex("count:steve", 300, $total);
}? 关键总结
- ❌ 避免对 LIKE '%xxx%' 字段直接 COUNT —— 无索引可依，必慢；
- ✅ 优先尝试 FULLTEXT 索引，兼顾性能与语义合理性；
- ✅ 对子串精度要求极高时，用倒排表 + 触发器物化统计，牺牲写入性能换取读取极速；
- ✅ 生产环境建议组合使用：前端异步展示总数 + 后端缓存 + 定期后台校准，平衡速度与准确性。
通过以上任一方案，均可将“显示前10条 + 显示总数”的整体响应时间从数秒降至百毫秒级，真正实现海量数据下的流畅用户体验。
