本文讲解如何通过改进 sql 查询、使用全文索引和优化分页逻辑，显著提升带关键词搜索的 mysql 分页性能，解决因 `like '%keyword%'` 导致的全表扫描和加载缓慢问题。
在 PHP + MySQLi 开发中，常见搜索分页实现容易陷入性能陷阱：例如使用 WHERE title LIKE '%{$strKeyword}%' 进行模糊匹配，会导致 MySQL 无法利用索引，每次搜索都触发全表扫描；再加上先执行 SELECT * 获取总行数再分页（即“查总数 + LIMIT OFFSET”模式），当数据量增大时，页面加载会急剧变慢，甚至超时。
✅ 核心优化策略
1. 替换 LIKE '%keyword%' 为 FULLTEXT 全文索引（推荐）
LIKE 左侧通配符（如 %abc）完全禁止索引使用。而 FULLTEXT 索引专为文本搜索设计，配合 MATCH ... AGAINST(... IN NATURAL LANGUAGE MODE) 可实现毫秒级响应：
-- 首次执行（仅需一次）：为 title 字段添加 FULLTEXT 索引 ALTER TABLE crawl ADD FULLTEXT(title);
// PHP 中改用全文搜索（更安全、更快）
$strKeyword = mysqli_real_escape_string($conn, trim($_GET['q'] ?? ''));
if (!empty($strKeyword)) {
$sql = "SELECT id, title, ogimage FROM crawl
WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)
ORDER BY MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE) DESC";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $strKeyword, $strKeyword);
mysqli_stmt_execute($stmt);
$query = mysqli_stmt_get_result($stmt);
}⚠️ 注意：FULLTEXT 要求表引擎为 InnoDB（MySQL 5.6+）或 MyISAM；默认停用词（如 “the”, “and”）和最小词长（ft_min_word_len=4）可能影响短关键词匹配，可通过配置调整。
2. 摒弃 COUNT(*) + OFFSET 分页（避免性能雪崩）
原代码中先 mysqli_num_rows() 获取总数，再用 LIMIT $offset, $per_page 分页，存在两大隐患：
- COUNT(*) 在无 WHERE 条件或低选择性条件下极慢；
- OFFSET 越大（如第 1000 页），MySQL 仍需扫描前 1000×$per_page 行，效率线性下降。
✅ 推荐方案：游标分页（Cursor-based Pagination） 或 键集分页（Keyset Pagination）
以主键 id 为游标，只查下一页所需数据，无需总数、不依赖 OFFSET：
$per_page = 20;
$last_id = (int)($_GET['last_id'] ?? 0);
if ($last_id > 0) {
$sql = "SELECT id, title, ogimage FROM crawl
WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)
AND id < ?
ORDER BY id DESC LIMIT ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "sii", $strKeyword, $last_id, $per_page);
} else {
$sql = "SELECT id, title, ogimage FROM crawl
WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)
ORDER BY id DESC LIMIT ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "si", $strKeyword, $per_page);
}
mysqli_stmt_execute($stmt);
$results = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
// 渲染下一页链接（只需传最后一条的 id）
$next_last_id = !empty($results) ? end($results)['id'] : null;
if ($next_last_id) {
echo '<a href="?q=' . urlencode($strKeyword) . '&last_id=' . $next_last_id . '">下一页</a>';
}3. 其他关键加固措施
- 参数化查询：杜绝 SQL 注入（原代码直接拼接 $strKeyword 极其危险）；
- 字段精简：SELECT * 改为明确列出需要的字段（如 id, title, ogimage），减少网络传输与内存开销；
- 缓存总页数（可选）：若业务允许弱一致性，可用 Redis 缓存搜索结果总数，过期时间设为 30 秒；
- 前端防抖：搜索框添加 debounce，避免用户连续输入触发多次请求。
总结
真正的搜索分页优化不是“调小 LIMIT”，而是从查询机制重构：
? 用 FULLTEXT + MATCH 替代 LIKE 实现高效文本检索；
? 用基于主键/时间戳的 游标分页 替代 OFFSET，消除深度分页性能衰减；
? 始终使用预处理语句，保障安全与执行计划稳定性。
完成上述改造后，即使百万级数据，关键词搜索分页响应时间也能稳定在 50ms 内。
立即学习“PHP免费学习笔记（深入）”；
