帶你讀 MySQL 原始碼：limit, offset

資料庫工作筆記發表於2023-04-10
MySql原始碼MIT

來源：一樹一溪

我一直想寫 MySQL 原始碼分析文章，希望能夠達成 2 個目標：

  • 不想研究原始碼的朋友，可以透過文章瞭解 MySQL 常用功能的實現邏輯，做到知其然，也知其所以然。

  • 想研究原始碼的朋友，能夠以文章為切入點，邁進 MySQL 原始碼研究之門。

目標是明確的，任務是艱鉅的。

MySQL 原始碼數量龐大，各種功能的程式碼盤根錯節，相互交織在一起，形成一張複雜的網。

想要把這張網中的某些部分拎出來寫成文章，還要做到通俗易懂，這並不是件容易的事，我也就遲遲沒有動手。

萬事開頭難，但是再難，總得開始，才能有後續，所以，就有了這篇文章。

寫文章是件費時費力的事，寫出來了總希望有更多人看，否則就沒有寫下去的動力了。

對 MySQL 原始碼感興趣的朋友們，如果想看到原始碼分析系列的更多文章，請幫忙把文章傳播出去，分享給更多人。

嘮叨完前因後果，再說說我準備怎麼寫這個系列文章：

  • 我會挑一些常用功能，每篇文章介紹一個單點功能的原始碼，從簡單功能開始，逐漸過渡到複雜功能。

  • 每篇文章只會介紹核心原始碼邏輯，原始碼之中增加註釋，原始碼之外儘可能用文字展開介紹原始碼邏輯，以幫助大家更好的理解原始碼。

  • 每篇文章不會太長，如果功能複雜導致內容太長，我會拆分文章，儘量降低大家的閱讀負擔。

接下來，我們開始原始碼分析系列的第 1 篇文章。

本文內容基於 MySQL 8.0.32 原始碼。

目錄

  • 1. 準備工作

  • 2. 整體介紹

  • 3. 原始碼分析

    • 3.1 ExecuteIteratorQuery()

    • 3.2 LimitOffsetIterator::Read()

  • 4. 總結


正文

1. 準備工作

建立測試表：

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `str1` varchar(255) NOT NULL DEFAULT '',
  `i1` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入測試資料：

INSERT INTO t1(id, str1, i1) VALUES
(1, 's1', 10),
(2, 's2', 20),
(3, 's3', 30),
(4, 's4', 40),
(5, 's5', 50),
(6, 's6', 60),
(7, 's7', 70),
(8, 's8', 80);

示例 SQL：

select * from t1 limit 5, 2

2. 整體介紹

我們先透過 explain 來看一下執行計劃：

帶你讀 MySQL 原始碼：limit, offset

從 explain 輸出可以看到，執行計劃比較簡單，SQL 執行過程包含 2 個迭代器：

  • Limit/Offset，對應 LimitOffsetIterator 迭代器。

  • Table scan，對應 TableScanIterator 迭代器。

程式碼執行時堆疊如下：

| > handle_connection(void*) sql/conn_handler/connection_handler_per_thread.cc:302
| + > do_command(THD*) sql/sql_parse.cc:1439
| + - > dispatch_command(...) sql/sql_parse.cc:2036
| + - x > dispatch_sql_command(THD*, Parser_state*) sql/sql_parse.cc:5322
| + - x = > mysql_execute_command(THD*, bool) sql/sql_parse.cc:4688
| + - x = | > Sql_cmd_dml::execute(THD*) sql/sql_select.cc:578
| + - x = | + > Sql_cmd_dml::execute_inner(THD*) sql/sql_select.cc:778
| + - x = | + - > Query_expression::execute(THD*) sql/sql_union.cc:1823
| + - x = | + - x > // 查詢入口
| + - x = | + - x > Query_expression::ExecuteIteratorQuery(THD*) sql/sql_union.cc:1770
| + - x = | + - x = > // 實現 limit, offset
| + - x = | + - x = > LimitOffsetIterator::Read() sql/iterators/composite_iterators.cc:128
| + - x = | + - x = | > // 從儲存引擎讀取一條記錄
| + - x = | + - x = | > TableScanIterator::Read() sql/iterators/basic_row_iterators.cc:218

3. 原始碼分析

TableScanIterator 迭代器用於從儲存引擎讀取記錄，留到以後的文章介紹。

limit, offset 由 LimitOffsetIterator 迭代器實現，我們會介紹兩個方法的程式碼：

  • Query_expression::ExecuteIteratorQuery(THD*)，這是查詢入口方法，介紹了它，流程才算完整。

  • LimitOffsetIterator::Read()，limit, offset 的邏輯都在這個方法裡實現。

3.1 ExecuteIteratorQuery()

// sql/sql_union.cc
bool Query_expression::ExecuteIteratorQuery(THD *thd) {
  ...
  {
    ...
    for (;;) {
      // 從儲存引擎讀取一條記錄
      int error = m_root_iterator->Read();
      DBUG_EXECUTE_IF("bug13822652_1", thd->killed = THD::KILL_QUERY;);

      // 讀取出錯，直接返回
      if (error > 0 || thd->is_error())  // Fatal error
        return true;
      // error < 0
      // 表示已經讀完了所有符合條件的記錄
      // 查詢結束
      else if (error < 0)
        break;
      // SQL 被客戶端幹掉了
      else if (thd->killed)  // Aborted by user
      {
        thd->send_kill_message();
        return true;
      }
      ...
      // 傳送資料給客戶端
      if (query_result->send_data(thd, *fields)) {
        return true;
      }
      ...
    }
  }
  ...
}

從以上程式碼可以看到，select 查詢入口方法的主體是一個無限 for 迴圈。

每一輪迴圈都會呼叫 m_root_iterator->Read() 方法從儲存引擎讀取一條記錄。

對於示例 SQL 來說，m_root_iterator->Read() 就是 LimitOffsetIterator::Read()。

for 迴圈會一直執行，直到 m_root_iterator->Read() 的返回值命中以下任意一個條件才會結束：

  • if (error > 0 || thd->is_error())，讀取出錯了，以錯誤狀態結束查詢。

  • if (error < 0)，已經讀完所有符合條件的記錄，以正常狀態結束查詢。

  • if (thd->killed)，SQL 被客戶端透過 kill <query_id> 幹掉了，中止查詢。

    <query_id> 為 show processlist 中的 Id 欄位。

for 迴圈中，每次從儲存引擎讀取到一條記錄，都會呼叫 query_result->send_data(thd, *fields) 方法。

對於示例 SQL 來說，這個方法的行為就是把記錄傳送給客戶端。

3.2 LimitOffsetIterator::Read()

// sql/iterators/composite_iterators.cc
int LimitOffsetIterator::Read() {
  // 這個 if 括號裡的條件理解起來會有點困難
  // 所以被省略了，眼不見為淨
  //【重點】只有讀取第一條和最後一條記錄時才會進入這個 if 分支
  if (...) {
    ...
    // m_needs_offset = true
    // 表示 SQL 語句中指定了 offset
    if (m_needs_offset) {
      ...
      // 迴圈從儲存引擎讀取 m_offset 條記錄
      // 每讀取到一條記錄，直接丟棄
      for (ha_rows row_idx = 0; row_idx < m_offset; ++row_idx) {
        // 讀取一條記錄之後
        // 如果沒有出錯，就接著讀取下一條記錄
        int err = m_source->Read();
        // 讀取出錯，直接返回錯誤碼
        if (err != 0) {
          return err;
        }
        ...
      }
      // 讀取 m_offset 條記錄並丟棄之後
      // 把 m_seen_rows 設定為已讀取記錄數 
      m_seen_rows = m_offset;
      // 然後把 m_needs_offset 設定為 false
      // 表示不需要再處理 offset 邏輯了（因為已處理完成）
      // 下次讀取時也就不需要再跳過 m_offset 條記錄了
      m_needs_offset = false;
      ...
    }
    // 如果已經讀取了 m_limit 條記錄
    // 就返回 -1，表示讀取結束
    // m_limit = SQL 中的 limit + offset
    if (m_seen_rows >= m_limit) {
      ...
      return -1;
    }
  }

  // 讀取需要返回給客戶端的記錄
  const int result = m_source->Read();
  ...
  // 已讀取記錄數加 1
  ++m_seen_rows;
  // 返回當前讀取的記錄
  // 給 Query_expression::ExecuteIteratorQuery() 方法
  return result;
}

除了處理 offset 邏輯之外，LimitOffsetIterator::Read() 每次只讀取一條記錄，這個方法的核心邏輯分為三部分：

第 1 部分：if (m_needs_offset)，SQL 語句中指定了 offset，返回第一條記錄給客戶端之前，需要讀取 offset 條記錄並丟棄，從第 offset + 1 條記錄開始返回給客戶端。

這部分的主要邏輯是一個 for 迴圈，會迴圈 offset 次，每次讀取一條記錄。

如果讀取成功，就接著讀取下一條記錄，而不會對這條記錄做任何操作，也就相當於丟棄了。

如果讀取失敗，直接返回錯誤碼，讀取結束，客戶端會收到報錯資訊。

第 2 部分：if (m_seen_rows >= m_limit)，表示已經讀取了 m_limit 條記錄，返回 -1 表示讀取正常結束。

m_limit = SQL 中的 limit + offset。

第 3 部分：result = m_source->Read() 從儲存引擎讀取一條記錄，然後，把結果返回給 Query_expression::ExecuteIteratorQuery() 方法。

4. 總結

limit, offset 邏輯比較簡單，全部由 LimitOffsetIterator::Read() 實現，核心邏輯總結如下：

  • 從儲存引擎讀取返回給客戶端的第 1 條記錄之前，會先讀取 offset 條記錄並丟棄，然後再讀取一條記錄，用於返回給客戶端。

  • 從儲存引擎讀取第 2 ~ limit + offset 條記錄時，每讀取一條記錄，都返回給 Query_expression::ExecuteIteratorQuery()，由該方法把記錄返回給客戶端。

  • 讀取 limit + offset 條記錄之後，返回 -1 表示讀取流程正常結束。

從 LimitOffsetIterator::Read() 的實現邏輯來看，offset 越大，讀取之後被丟棄的記錄就越多，讀取這些記錄所做的都是無用功。

為了提高 SQL 的執行效率，可以透過改寫 SQL 讓 offset 儘可能小，理想狀態是 offset = 0。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2944736/，如需轉載，請註明出處，否則將追究法律責任。

相關文章