MySQL ディープページング問題の解決の実践記録

序文

日常的にページングが必要な場合、通常は limit を使用して実装しますが、オフセットが特に大きい場合は、クエリの効率が低下します。この記事では、4 つのソリューションで何百万もの MySQL データのディープページング問題を最適化する方法について説明し、本番環境での低速 SQL の最適化に関する最近の実践的なケーススタディを紹介します。

ディープページングを制限すると遅くなるのはなぜですか?

まず、次のテーブル構造を見てみましょう。

テーブルアカウントの作成（
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主キーID',
  name varchar(255) DEFAULT NULL COMMENT 'アカウント名',
  バランス int(11) デフォルト NULL コメント 'バランス',
  create_time datetime NOT NULL COMMENT '作成時刻',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時刻',
  主キー (id)、
  キーidx_name（名前）、
  KEY idx_update_time (update_time) //index) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='アカウント テーブル';

ディープページングのために実行される SQL が次のとおりであると仮定します。

update_time> '2020-09-19' の limit 100000,10 のアカウントから id、name、balance を選択します。

この SQL の実行時間は次のとおりです。

完了するまでに 0.742 秒かかります。ディープページングが遅くなるのはなぜですか?制限を0,10に変更すると、わずか0.006秒しかかかりません

まず、この SQL の実行プロセスを見てみましょう。

共通セカンダリインデックスツリー idx_update_time を使用して、update_time 条件をフィルターし、条件を満たすレコード ID を見つけます。
IDを介して主キーインデックスツリーに戻り、レコードを満たす行を見つけて、表示された列を取得します（テーブルに戻ります）
条件を満たす 100010 行をスキャンし、最初の 100000 行を破棄して戻ります。

SQL実行プロセス

実行計画は次のとおりです。

SQL が遅くなる理由は 2 つあります。

limit ステートメントは、最初に offset+n 行をスキャンし、次に offset の前の行を破棄して、次の n 行のデータを返します。つまり、limit 100000,10 の場合は 100010 行がスキャンされますが、limit 0,10 の場合は 10 行のみがスキャンされます。
limit 100000,10 より多くの行をスキャンすると、返されるテーブルも増えます。

サブクエリによる最適化

上記の SQL はテーブルを 100010 回返すため、実際に必要なのは 10 個のデータだけ、つまりテーブルを 10 回返すだけで済みます。したがって、テーブル戻り値の数を減らすことで最適化できます。

B+ツリー構造のレビュー

では、テーブル返品数を減らすにはどうすればよいでしょうか?まずはB+ツリーのインデックス構造を復習しましょう〜

InnoDBでは、インデックスは主キーインデックス（クラスター化インデックス）とセカンダリインデックスに分かれています。

主キーインデックス、リーフノードはデータの行全体を格納する
セカンダリインデックス。リーフノードにはプライマリキーの値が格納されます。

条件を主キーインデックスツリーに転送する

クエリ条件を主キーインデックスツリーに戻すと、返されるテーブルの数を減らすことができます。主キーインデックスツリークエリに切り替える場合は、クエリ条件を主キー ID に変更する必要があります。以前の SQL update_time 条件はどうでしょうか。サブクエリはどこにありますか?

サブクエリをどのように抽出しますか?セカンダリインデックスのリーフノードには主キー ID があるため、update_time に基づいて主キー ID を直接クエリできます。同時に、limit 100000 の条件もサブクエリに転送します。完全な SQL は次のとおりです。

アカウントから id、名前、残高を選択します。id >= (アカウント a から a.id を選択します。a.update_time >= '2020-09-19' 制限 100000、1) 制限 10;

クエリの効果は同じで、実行時間はわずか 0.038 秒です。

実行計画を見てみましょう。

実行プランから、クエリのサブクエリテーブルが idx_update_time インデックスを使用することがわかります。まず、インデックスからクラスター化インデックスの主キー ID を取得し、テーブルに戻る必要性を排除します。その後、2 番目のクエリは、最初のクエリの ID に基づいてさらに 10 個を直接クエリできます。

したがって、この計画は実行可能です〜

INNER JOIN 遅延結合

遅延結合の最適化の考え方は、実際にはサブクエリのそれと同じです。どちらも条件を主キーインデックスツリーに転送し、テーブル戻り値の数を減らします。違いは、遅延結合ではサブクエリではなく内部結合が使用されることです。

最適化された SQL は次のとおりです。

アカウント acct1 から acct1.id、acct1.name、acct1.balance を選択し、INNER JOIN (アカウント a から a.id を選択し、a.update_time >= '2020-09-19' を ORDER BY a.update_time LIMIT 100000, 10 を ORDER BY acct2 として、acct1.id= acct2.id で結合します。

クエリ効果も活用され、わずか0.034秒しかかかりません

実行計画は次のとおりです。

クエリのアイデアは、まず idx_update_time セカンダリインデックスツリーを介して条件を満たす主キー ID をクエリし、次に主キー ID を介して元のテーブルに接続することです。このようにして、主キーインデックスは後で直接使用され、テーブルリターンの数も削減されます。

レーベルレコーディング

制限ディープページング問題の根本的な理由は、オフセットが大きいほど、MySQL がスキャンして破棄する行数が増えることです。その結果、クエリのパフォーマンスが低下します。

実際には、ラベル記録方式を使用できます。つまり、前回クエリされた項目をマークし、次回チェックするときにその項目からスキャンを開始します。それは本を読むのとまったく同じです。前回読み終えたところに折りたたんだり、ブックマークを付けたりできるので、次に読むときにそのページに直接進むことができます。

最後のレコードが 100000 だったと仮定すると、SQL は次のように変更できます。

select id,name,balance FROM account where id > 100000 order by id limit 10;

こうすることで、後から何ページめくっても、id インデックスにヒットするのでパフォーマンスは良くなります。しかし、この方法には制限があります。連続的に自己増加するフィールドに似たフィールドが必要になります。

...と...の間で使用してください。

多くの場合、制限クエリは位置が既知のクエリに変換できるため、MySQL は ... との間を範囲スキャンして対応する結果を取得できます。

境界値が 100000 と 100010 であることがわかっている場合は、次のように最適化できます。

select id,name,balance FROM account where id between 100000 and 100010 order by id desc;

実践的なケース

実際の事例を見てみましょう。テーブル構造が次の通りで、データが 200 万件あると仮定します。

テーブルアカウントの作成（
 id varchar(32) COLLATE utf8_bin NOT NULL COMMENT '主キー',
 account_no varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'アカウント番号'
 金額 小数点(20,2) デフォルト NULL コメント '金額'
 タイプ varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT 'タイプ A、B'
 create_time datetime DEFAULT NULL COMMENT '作成時刻',
 update_time datetime DEFAULT NULL COMMENT '更新時刻',
 主キー (id)、
 キー `idx_account_no` (アカウント番号)、
 キー `idx_create_time` (create_time)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='アカウントテーブル'

ビジネス要件は次のとおりです。2021 年の最新のタイプ A アカウントデータを取得し、ビッグデータプラットフォームに報告します。

一般的なアイデアをどのように実装するか

多くのパートナーは、この要件を受け取ったらすぐに実装します。

//レポートの合計数を照会します。Integer total = accountDAO.countAccount();

//レポートの総数に対応するSQLをクエリする
<select id ='countAccount' resultType="java.lang.Integer">
  選択数(1)
   アカウントから
  create_time >='2021-01-01 00:00:00' の場合
  タイプ = 'A'
</選択>

//ページ数を計算します int pageNo = total % pageSize == 0 ? total / pageSize : (total / pageSize + 1);

//ページングクエリ、レポート for(int i = 0; i < pageNo; i++){
 リスト<AcccountPO> リスト = accountDAO.listAccountByPage(startRow,pageSize);
 開始行 = (ページ番号-1)*ページサイズ;
 // ビッグデータを報告 postBigData(list);
}

 // ページング クエリ SQL (アカウント テーブルには何百万ものデータがあるため、深いページングの制限の問題が発生する可能性があります)
<select id ='listAccountByPage' >
  選択*
   アカウントから
  create_time >='2021-01-01 00:00:00' の場合
  タイプ = 'A'
  制限 #{startRow}、#{pageSize}
</選択>

実践的な最適化計画

上記の実装では、アカウントテーブルに数百万のデータが含まれているため、ディープページングが制限されるという問題が発生します。では、どのように最適化すればよいのでしょうか?

実は、タグ記録方式も使えます。一部の友人は戸惑うかもしれません。ID主キーは連続していません。タグ記録は本当に使えますか？

もちろん、ID が連続していない場合は、order by を使用して連続させることができます。最適化スキームは次のとおりです。

//最小IDを照会する
文字列 lastId = accountDAO.queryMinId();

//最大IDに対応するSQLをクエリする
<select id="queryMinId" returnType="java.lang.String">
MIN(id)を選択
 アカウントから
create_time >='2021-01-01 00:00:00' の場合
タイプ = 'A'
</選択>

//ページあたりのエントリ数 Integer pageSize = 100;

リスト<AccountPO> リスト;
する{
   リスト = listAccountByPage(lastId、ページサイズ);
   //タグ記録方法、前回照会したIDを記録する
   lastId = list.get(list,size()-1).getId();
    // ビッグデータを報告 postBigData(list);
}while(CollectionUtils.isNotEmpty(リスト));

<select id="listAccountByPage">
  *を選択
   アカウントから
   create_time >='2021-01-01 00:00:00' の場合
  および id > #{lastId}
  タイプ = 'A'
  ID昇順で並べ替え
    制限 #{pageSize}
</選択>