萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP+MySQL百萬級數據分頁排序優化:延遲關聯、覆蓋索引、游標分頁與架構分層

百萬級數據下,傳統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);

優化策略三:游標分頁(無Offset)

前端傳遞上一頁最後一條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;
  • 優點:性能恆定O(log n),無深分頁問題。
  • 缺點:不能隨意跳頁、需前端配合、排序列必須唯一(或(aid, id)組合)。

優化策略四:架構分層

  1. 讀寫分離:主庫寫、從庫讀,分頁查詢走從庫。
  2. 分表/分庫:按時間/用戶分表,單表控制在200萬以內。
  3. 引入搜索引擎:Elasticsearch/MeiliSearch承擔列表檢索、排序、聚合,MySQL僅存儲詳情。
  4. 緩存熱門頁:首頁、熱門分類頁Redis緩存HTML片段或JSON數據。

原文SQL改寫示例(結合延遲關聯+覆蓋索引)

<?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。
  • Prometheus監控mysql_slow_queries_total、mysql_query_duration_seconds。

綜合採用「延遲關聯+覆蓋索引」解決當前深分頁痛點,長期規劃「游標分頁+ES」支撐業務增長。

copyright © 萬盛學電腦網 all rights reserved