此文章使用的是“延遲關聯”查詢方案進行 測試于分析

其他方案：可采用 SELECT * FROM user_info WHERE id > last_id ORDER BY id LIMIT 10;
last_id(上一個查詢的最后id)

方案1：
select user_id from user_info where venture = 'TH' limit 824000,4000
方案2：
select user_id
from user_info a join (select id 
from user_info where venture = 'TH' limit 824000,4000) b on a.id = b.id

??預期結果(venture無索引的情況下)

結論：方案1更快

方案1執(zhí)行流程：

執(zhí)行步驟：

1. 全表掃描 - 從第一行開始逐行檢查 venture 字段
2. 過濾匹配 - 找到 venture='TH' 的記錄
3. 跳過前824000條 - 繼續(xù)掃描直到跳過824000條匹配記錄
4. 返回4000條 - 取接下來的4000條記錄的 user_id

方案2執(zhí)行流程：

子查詢執(zhí)行步驟：

1. 全表掃描 - 從第一行開始逐行檢查 venture 字段
2. 過濾匹配 - 找到 venture='TH' 的記錄
3. 跳過前824000條 - 繼續(xù)掃描直到跳過824000條匹配記錄
4. 返回4000個ID - 取接下來的4000條記錄的 id

外層查詢執(zhí)行步驟：

1. 主鍵查找 - 通過主鍵索引直接定位4000個ID對應的記錄
2. 獲取user_id - 返回對應的 user_id 字段

??性能對比分析

掃描成本對比：

??預期結果(venture有索引的情況下)

方案1執(zhí)行流程：

詳細執(zhí)行步驟：

1. 使用索引定位 - 通過 idx_venture 索引快速找到所有 venture='TH' 的記錄位置
2. 按索引順序遍歷 - 沿著索引鏈表/B+樹遍歷匹配的記錄
3. 跳過前824000條 - 這是最耗時的步驟！需要：

• • 遍歷824000個索引條目
  • 對每個索引條目進行回表操作（獲取完整記錄）

1. 獲取目標數據 - 繼續(xù)遍歷4000條記錄，回表獲取 user_id
2. 返回結果 - 返回4000個 user_id 值

關鍵問題： 雖然有索引，但仍需要跳過824000條記錄，每條(824000+4000)都要回表！

方案2執(zhí)行流程：

子查詢執(zhí)行：

select id from user_info
where venture = 'TH' limit 824000, 4000

執(zhí)行步驟：

1. 使用索引定位 - 通過 idx_venture 索引找到所有 venture='TH' 的記錄
2. 按索引順序遍歷 - 沿著索引遍歷匹配的記錄
3. 跳過前824000條 - 遍歷824000個索引條目
4. 獲取ID值 - 繼續(xù)遍歷4000條，但只需要獲取主鍵ID（不需要回表！）
5. 返回ID列表 - 返回4000個ID值：[1000001, 1000002, ..., 1005000]

外層查詢執(zhí)行：

select user_id from user_info a 
join (...) b on a.id = b.id

執(zhí)行步驟：

1. 主鍵查找 - 對4000個ID進行主鍵索引查找（非?？欤。?/li>
2. 獲取字段值 - 直接從主鍵索引或數據頁獲取 user_id
3. 返回結果 - 返回4000個 user_id 值

??性能差異對比

疑問？？？

方案1為什么需要回表前面的824000次，它不是有個計數器，從824001開始算有效數據，只回表有效數據嗎？

理想執(zhí)行流程：

1. 使用索引找到 venture='TH' 的記錄
2. 用計數器跳過前824000條（只計數，不回表）
3. 從第824001條開始回表獲取 user_id
4. 只回表4000次

MySQL的實際執(zhí)行流程：

實際執(zhí)行流程：

1. 使用 idx_venture 索引找到第一條 venture='TH' 的記錄
2. 回表獲取完整記錄（包括 user_id）
3. 計數器 +1，判斷是否達到824000
4. 如果未達到，繼續(xù)下一條記錄，重復步驟2-3
5. 達到824000后，繼續(xù)處理4000條記錄并返回

?? 為什么MySQL不能用"計數器跳過"？

1. 索引結構限制

? Apply

idx_venture 索引結構：
venture='TH' -> [record_ptr_1, record_ptr_2, record_ptr_3, ...]

• 索引只能順序遍歷，不能直接跳到第824001個位置
• 每個索引條目都需要逐個檢查才能確定是否匹配條件

2. WHERE條件的復雜性

即使是簡單的 where venture = 'TH'，MySQL也需要：

• 讀取索引條目
• 驗證條件匹配
• 如果需要其他字段（如 user_id），必須回表

3. LIMIT的語義要求

LIMIT 824000, 4000 的語義是：

• "給我第824001到828000條符合條件的記錄"
• MySQL必須確保前824000條確實符合WHERE條件
• 這就要求逐條驗證和計數

方案2拿到4000個主鍵id后的jion操作，是一條條的拿id去查詢，還是批量的去查詢?

?? MySQL JOIN的執(zhí)行策略

MySQL會根據數據量、索引情況等因素選擇不同的JOIN算法：

1. Nested Loop Join（嵌套循環(huán)連接）

執(zhí)行方式：一條條查詢

for each row in subquery_result (4000 rows):
    lookup row in user_info where id = subquery_row.id

特點：

• 對子查詢的每一行，都去主表中查找匹配的記錄
• 4000次獨立的主鍵查找
• 適用于小結果集的情況

2. Hash Join（哈希連接）

執(zhí)行方式：批量處理

1. 將子查詢結果(4000個ID)構建成哈希表
2. 掃描主表相關記錄，與哈希表匹配

特點：

• MySQL 8.0.18+ 支持
• 更適合大數據量的JOIN
• 批量處理，效率更高

3. 實際上最可能的執(zhí)行方式

對于此場景（4000個主鍵ID），MySQL最可能采用：

優(yōu)化后的主鍵批量查找：

SQL-- MySQL內部可能優(yōu)化為類似這樣的查詢
select user_id from user_info
where id IN (1000001, 1000002, 1000003, ..., 1005000)

??測試

demo有200w數據

--方案1：
select c2 from demo where c1 = 'VN' limit 824000,4000
--方案2：
select c2
from demo a join (select id 
from demo where c1 = 'VN' limit 824000,4000) b on a.id = b.id

方案1的執(zhí)行記錄：

第一行是無索引的情況，第二行是有索引的情況

無索引下查詢耗時：900ms左右

有索引下查詢耗時：2200ms左右

可以看出加了索引，耗時更久了，原因是：需要回表828000次

方案2的執(zhí)行記錄：

第一行是無索引的情況，第二行是有索引的情況

無索引下查詢耗時：918ms左右

有索引下查詢耗時：245ms左右

可以看出加了索引，耗時快了好幾倍，原因是：需要只需要回表4000次

測試結果疑問？？

問題1：為什么方案1使用"索引查詢"方式更慢的情況下，而MySQL并沒有選擇使用時間更短的"全表掃描"方式去查詢？它不是有優(yōu)化器嗎？？

查看優(yōu)化器估算成本信息

1、查看"索引"情況下的優(yōu)化器估算成本信息

-- 查看優(yōu)化器的成本估算
EXPLAIN FORMAT=JSON 
SELECT c2 FROM demo WHERE c1 = 'VN' LIMIT 824000,4000;

結果如下：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "123426.40"
    },
    "table": {
      "table_name": "demo",
      "access_type": "ref",
      "possible_keys": [
        "idx_c1"
      ],
      "key": "idx_c1",
      "used_key_parts": [
        "c1"
      ],
      "key_length": "138",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 992739,
      "rows_produced_per_join": 992739,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "24152.50",
        "eval_cost": "99273.90",
        "prefix_cost": "123426.40",
        "data_read_per_join": "840M"
      },
      "used_columns": [
        "c1",
        "c2"
      ]
    }
  }
}

2、查看"全表掃描"情況下的優(yōu)化器估算成本信息

EXPLAIN FORMAT=JSON 
SELECT c2 FROM demo IGNORE INDEX (idx_c1) 
WHERE c1 = 'VN' LIMIT 824000,4000;
IGNORE INDEX (idx_c1) 表示：強制不走索引查詢

結果如下：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "206070.21"
    },
    "table": {
      "table_name": "demo",
      "access_type": "ALL",
      "rows_examined_per_scan": 1985479,
      "rows_produced_per_join": 198547,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "186215.42",
        "eval_cost": "19854.79",
        "prefix_cost": "206070.21",
        "data_read_per_join": "168M"
      },
      "used_columns": [
        "c1",
        "c2"
      ],
      "attached_condition": "(`demo`.`demo`.`c1` = 'VN')"
    }
  }
}

成本對比分析

使用索引 vs 強制全表掃描

關鍵發(fā)現

1. 優(yōu)化器的成本估算矛盾

• 優(yōu)化器認為索引更優(yōu)：成本 123,426 < 206,070
• 實際性能卻相反：索引 2000-2300ms > 全表掃描 823-966ms
• 這說明優(yōu)化器的成本模型存在系統(tǒng)性偏差

2. 成本構成的巨大差異

索引方式：

• 讀取成本低（24,152），但評估成本極高（99,273）
• 數據傳輸量大（840M vs 168M）

全表掃描：

• 讀取成本高（186,215），但評估成本很低（19,854）
• 數據傳輸量小得多

3. 為什么優(yōu)化器判斷錯誤？

優(yōu)化器沒有正確評估的因素：

1. LIMIT大偏移量的真實成本

• • 索引需要遍歷99萬行才能跳過82.4萬行
  • 全表掃描雖然掃描198萬行，但是順序讀取

1. 回表操作的隱藏成本

• • 索引查詢需要99萬次回表操作
  • 每次回表都是隨機I/O，成本被嚴重低估

1. 數據訪問模式差異

• • 全表掃描：順序I/O，對磁盤友好
  • 索引+回表：隨機I/O，磁盤性能差

深層原因分析

為什么數據傳輸量差這么多？

• 索引方式 840M：包含了大量的索引遍歷和回表開銷
• 全表掃描 168M：只傳輸最終需要的數據，過濾效率高

評估成本的巨大差異

• 索引方式：99,273（高CPU成本，大量條件判斷和回表）
• 全表掃描：19,854（簡單的WHERE條件過濾）

結論

這個對比完美解釋了MySQL優(yōu)化器的局限性：

1. 成本模型過于簡化：沒有準確反映大偏移量LIMIT的真實開銷
2. I/O模式評估不準確：低估了隨機I/O vs 順序I/O的性能差異
3. 回表成本計算有誤：大量回表操作的真實成本被嚴重低估

實際建議：

• 在這種場景下，應該刪除或忽略這個索引
• 或者使用覆蓋索引 (c1, c2) 避免回表
• 繼續(xù)使用子查詢優(yōu)化方案，這是最佳選擇

問題2：通過問題1發(fā)現“索引需要遍歷99萬行才能跳過82.4萬行”這句話，跟我們前面理解的“掃描824000+4000行”，條數相差有點大，多掃描了10w+的條數

1、先統(tǒng)計VN的全量數據

SELECT COUNT(1) FROM demo WHERE c1 = 'VN';
只有873557條

數據分析

實際數據：

• c1 = 'VN' 的總記錄數：873,557 條
• 執(zhí)行計劃顯示的掃描行數：992,739 條

為什么掃描行數比實際記錄數多？

這個差異（992,739 - 873,557 = 119,182）說明了幾個重要問題：

1. 優(yōu)化器估算不準確(數據量大或者復雜sql場景下，優(yōu)化器的局限性有限)

• 優(yōu)化器高估了匹配記錄數
• 實際只有 87萬條，但估算了 99萬條
• 這進一步證明了統(tǒng)計信息可能不夠準確

2. 索引掃描的額外開銷

可能的原因包括：

• 索引頁的預讀：MySQL 可能讀取了額外的索引頁
• 索引碎片：索引不夠緊湊，需要掃描更多頁面
• 緩沖區(qū)管理：為了找到所有匹配記錄，可能掃描了額外的索引條目

3. LIMIT 大偏移量的影響

現在我們知道：

• 總共有 873,557 條 c1='VN' 的記錄
• 需要跳過前 824,000 條
• 只返回 4,000 條

這意味著：

• 需要處理 95% 的匹配數據才能到達目標位置
• 幾乎要遍歷所有的匹配記錄
• 這就是為什么性能這么差的根本原因

到此這篇關于MySql深頁查詢實現方案的文章就介紹到這了,更多相關mysql深頁查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！