百萬級數據下,傳統LIMIT offset, size深分頁性能急劇下降(掃描丟棄大量行)。本文系統講解四大優化策略:延遲關聯、覆蓋索引、游標分頁、架構分層(讀寫分離/分表/搜索引擎),並提供可落地的SQL改寫示例。
-- 原查詢:深分頁掃描100萬行丟棄99萬,極慢
SELECT DISTINCT A.*, B.url, C.username
FROM tj_logs A
LEFT JOIN tj_domain B ON A.wid = B.wid
LEFT JOIN tj_members C ON A.uid = C.uid
WHERE ...
LIMIT 990000, 20;
先只查主表ID(利用索引),再回表關聯詳情:
-- 步驟1:只查ID(覆蓋索引掃描極快)
SELECT A.id
FROM tj_logs A
WHERE ...
ORDER BY A.aid DESC
LIMIT 990000, 20;
-- 步驟2:IN查詢詳情(或JOIN派生表)
SELECT A.*, B.url, C.username
FROM tj_logs A
LEFT JOIN tj_domain B ON A.wid = B.wid
LEFT JOIN tj_members C ON A.uid = C.uid
WHERE A.id IN (上一步查出的20個ID)
ORDER BY FIELD(A.id, ...); -- 保持順序
為WHERE、ORDER BY、SELECT列建立聯合索引,實現Using index:
-- 單表查詢場景
ALTER TABLE tj_logs ADD INDEX idx_list (aid, wid, uid); -- 包含WHERE/Order By列
-- 甚至包含所有SELECT列(覆蓋索引)
ALTER TABLE tj_logs ADD INDEX idx_cover (aid, wid, uid, col1, col2);
前端傳遞上一頁最後一條aid(游標),而非頁碼:
-- 首頁
SELECT * FROM tj_logs WHERE ... ORDER BY aid DESC LIMIT 20;
-- 下一頁(last_aid = 上頁最後一條aid)
SELECT * FROM tj_logs WHERE ... AND aid < $last_aid ORDER BY aid DESC LIMIT 20;
(aid, id)組合)。<?php
// 構建WHERE條件
$whereSql = "WHERE 1=1";
$params = [];
if ($keyword) {
$whereSql .= " AND (A.col1 LIKE ? OR A.col2 LIKE ?)";
$params[] = "%$keyword%"; $params[] = "%$keyword%";
}
// 1. 獲取ID列表(利用覆蓋索引 idx_aid_wid_uid)
$idSql = "SELECT A.aid FROM tj_logs A $whereSql ORDER BY A.aid DESC LIMIT ?, ?";
$stmt = $pdo->prepare($idSql);
$stmt->execute(array_merge($params, [$offset, $pageSize]));
$ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
if (!$ids) return [];
// 2. 回表詳情
$in = implode(',', array_fill(0, count($ids), '?'));
$detailSql = "SELECT A.*, B.url, C.username
FROM tj_logs A
LEFT JOIN tj_domain B ON A.wid = B.wid
LEFT JOIN tj_members C ON A.uid = C.uid
WHERE A.aid IN ($in)
ORDER BY FIELD(A.aid, $in)"; // 保持原序
$stmt = $pdo->prepare($detailSql);
$stmt->execute($ids);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
EXPLAIN檢查type=ref/range、Using index、rows預估值。long_query_time=1捕獲超時SQL。mysql_slow_queries_total、mysql_query_duration_seconds。綜合採用「延遲關聯+覆蓋索引」解決當前深分頁痛點,長期規劃「游標分頁+ES」支撐業務增長。