MySQL 中高效实现分页查询与总数统计的联合优化方案-php教程-PHP中文网

MySQL 中高效实现分页查询与总数统计的联合优化方案

本文介绍在千万级 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);
}