萬盛學電腦網

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

PHP + MySQL 查詢優化實戰:LIMIT 深分頁效能百倍提升

前言

MySQL 深分頁(LIMIT 200000, 50)是經典效能殺手。本文透過實測對比,揭示「延遲關聯」與「覆蓋索引」優化原理,助你輕鬆將查詢時間從 40 秒降至 0.06 秒。

問題場景

表 test 有 30 萬筆資料,主鍵 id 為自增索引。需查詢第 208888 筆之後的 50 筆。

❌ 低效寫法(全表掃描 + filesort)

SELECT * FROM `test` ORDER BY id ASC LIMIT 208888, 50;
-- 執行時間:40~50 秒

原因:MySQL 需掃描前 208888 行並排序(雖有索引,但 ORDER BY ... LIMIT offset, n 仍需遍歷 offset 行),產生臨時表與 filesort。

✅ 高效寫法(覆蓋索引 + 範圍查詢)

SELECT * FROM `test` WHERE id BETWEEN 208838 AND 208888;
-- 執行時間:~0.06 秒(快 700 倍!)

原理:直接利用 B+ 樹主鍵索引做範圍查找,避免掃描大量無關行,屬於「Index Range Scan」。

通用優化模式:延遲關聯

當無法直接用 ID 範圍(如按非主鍵排序)時:

-- 步驟 1:只查主鍵(覆蓋索引,極快)
SELECT id FROM `test` ORDER BY create_time DESC LIMIT 200000, 50;

-- 步驟 2:用 IN 關聯回主表查完整欄位
SELECT * FROM `test` WHERE id IN (上一步查出的 50 個 id) ORDER BY create_time DESC;

實戰檢核清單

場景優化手段關鍵點
主鍵遞增分頁WHERE id > last_id LIMIT n游標式分頁,無 offset 痛點
非主鍵排序分頁延遲關聯 / 覆蓋索引只讓索引覆蓋 ORDER BY + LIMIT 欄位
搜尋 + 分頁Elasticsearch / 全文索引MySQL 全文搜尋效能有限
統計總數COUNT(*) 優化 / 緩存總數InnoDB COUNT(*) 需全掃描

避坑指南

  • 避免 SELECT *,只查必要欄位(利用覆蓋索引)。
  • ORDER BY 欄位務必有索引,否則 filesort 災難性。
  • 使用 EXPLAIN 分析執行計劃,關注 type、rows、Extra。
  • 大偏移量分頁改用「游標式」(WHERE id > ? LIMIT n),前端改為「載入更多」而非頁碼跳轉。
copyright © 萬盛學電腦網 all rights reserved