此文章使用的是“延遲關(guān)聯(lián)”查詢方案進(jìn)行 測試于分析

其他方案：可采用 SELECT * FROM user_info WHERE id > last_id ORDER BY id LIMIT 10;
last_id(上一個(gè)查詢的最后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

??預(yù)期結(jié)果(venture無索引的情況下)

結(jié)論：方案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個(gè)ID - 取接下來的4000條記錄的 id

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

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

??性能對比分析

掃描成本對比：

??預(yù)期結(jié)果(venture有索引的情況下)

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

詳細(xì)執(zhí)行步驟：

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

• • 遍歷824000個(gè)索引條目
  • 對每個(gè)索引條目進(jìn)行回表操作（獲取完整記錄）

1. 獲取目標(biāo)數(shù)據(jù) - 繼續(xù)遍歷4000條記錄，回表獲取 user_id
2. 返回結(jié)果 - 返回4000個(gè) user_id 值

關(guān)鍵問題： 雖然有索引，但仍需要跳過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個(gè)索引條目
4. 獲取ID值 - 繼續(xù)遍歷4000條，但只需要獲取主鍵ID（不需要回表?。?/li>
5. 返回ID列表 - 返回4000個(gè)ID值：[1000001, 1000002, ..., 1005000]

外層查詢執(zhí)行：

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

執(zhí)行步驟：

1. 主鍵查找 - 對4000個(gè)ID進(jìn)行主鍵索引查找（非?？欤。?/li>
2. 獲取字段值 - 直接從主鍵索引或數(shù)據(jù)頁獲取 user_id
3. 返回結(jié)果 - 返回4000個(gè) user_id 值

??性能差異對比

疑問？？？

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

理想執(zhí)行流程：

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

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

實(shí)際執(zhí)行流程：

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

?? 為什么MySQL不能用"計(jì)數(shù)器跳過"？

1. 索引結(jié)構(gòu)限制

? Apply

idx_venture 索引結(jié)構(gòu)：
venture='TH' -> [record_ptr_1, record_ptr_2, record_ptr_3, ...]

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

2. WHERE條件的復(fù)雜性

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

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

3. LIMIT的語義要求

LIMIT 824000, 4000 的語義是：

• "給我第824001到828000條符合條件的記錄"
• MySQL必須確保前824000條確實(shí)符合WHERE條件
• 這就要求逐條驗(yàn)證和計(jì)數(shù)

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

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

MySQL會根據(jù)數(shù)據(jù)量、索引情況等因素選擇不同的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

特點(diǎn)：

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

2. Hash Join（哈希連接）

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

1. 將子查詢結(jié)果(4000個(gè)ID)構(gòu)建成哈希表
2. 掃描主表相關(guān)記錄，與哈希表匹配

特點(diǎn)：

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

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

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

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

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

??測試

demo有200w數(shù)據(jù)

--方案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í)行記錄：

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

無索引下查詢耗時(shí)：900ms左右

有索引下查詢耗時(shí)：2200ms左右

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

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

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

無索引下查詢耗時(shí)：918ms左右

有索引下查詢耗時(shí)：245ms左右

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

測試結(jié)果疑問？？

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

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

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

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

結(jié)果如下：

{
  "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) 表示：強(qiáng)制不走索引查詢

結(jié)果如下：

{
  "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 強(qiáng)制全表掃描

關(guān)鍵發(fā)現(xiàn)

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

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

2. 成本構(gòu)成的巨大差異

索引方式：

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

全表掃描：

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

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

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

1. LIMIT大偏移量的真實(shí)成本

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

1. 回表操作的隱藏成本

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

1. 數(shù)據(jù)訪問模式差異

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

深層原因分析

為什么數(shù)據(jù)傳輸量差這么多？

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

評估成本的巨大差異

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

結(jié)論

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

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

實(shí)際建議：

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

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

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

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

數(shù)據(jù)分析

實(shí)際數(shù)據(jù)：

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

為什么掃描行數(shù)比實(shí)際記錄數(shù)多？

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

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

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

2. 索引掃描的額外開銷

可能的原因包括：

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

3. LIMIT 大偏移量的影響

現(xiàn)在我們知道：

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

這意味著：

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

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

最新評論