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

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 の実行プロセスを見てみましょう。

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

SQL実行プロセス

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

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

  1. limit ステートメントは、最初に offset+n 行をスキャンし、次に offset の前の行を破棄して、次の n 行のデータを返します。つまり、limit 100000,10 の場合は 100010 行がスキャンされますが、limit 0,10 の場合は 10 行のみがスキャンされます。
  2. 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}
</選択>

要約する

これで、MySQL ディープ ページングの問題に関するこの記事は終了です。MySQL ディープ ページングの問題の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • JavaSwing+MySQL に基づく学生クラブ管理システムの設計と実装
  • MySQL 接続制御プラグインの紹介
  • MySQL のクエリパフォーマンスに対する制限の影響
  • JavaSwing をベースに設計・実装されたホテル管理システム
  • JavaSwing 戦車バトルゲームの設計と実装
  • JavaSwingの基本を詳しく解説レイアウトに関する知識
  • JavaSwing バックグラウンドミュージック mp3
  • Mysql+JavaSwing に基づくスーパーマーケット商品管理システムの設計と実装

<<:  CSSアニメーションがJSによってブロックされるかどうかについての簡単な議論

>>:  @Font-face の基本的な使い方と、すべてのブラウザと互換性を持たせる方法

推薦する

ブラウザがクロージャをどのように認識するかについて詳しく説明します

目次序文クロージャの紹介メモリのゴミを識別する方法クロージャのメモリ表現結論序文クロージャは、Jav...

MySQL ルートパスワードエラー番号 1045 の解決方法

MySQLサービスを停止するWindowsでは、マイコンピュータを右クリック--管理--サービスと...

Ubuntu ブート自動起動サービス設定

Ubuntu でサービスを作成し、自動的に起動する方法: 1. [/lib/systemd/syst...

Nginx 仮想ホストを構成する 3 つの方法 (ドメイン名に基づく)

Nginx は、IP ベースの仮想ホスト構成、ポート ベースの仮想ホスト構成、ドメイン名ベースの仮...

webpackを使用してTypeScriptコードをパッケージ化およびコンパイルする方法を教えます

TypeScript バンドルwebpack 統合通常、実際の開発では、ビルド ツールを使用してコー...

LinuxにDockerをインストールする(非常に簡単なインストール方法)

最近、かなり暇です。大学4年生として数か月間インターンをしていました。インターンとして、Docker...

MySQLテーブルの自動インクリメント列の初期値をリセットする方法

MySQLテーブルの自動インクリメント列の初期値をリセットする方法1. 問題の説明MySQL データ...

ズームインとズームアウトの閉じるボタンを実現する CSS (サンプル コード)

この効果はブラウザ ページで最もよく見られます。まずは効果の画像をご覧ください。 上の図に示すように...

VMware で Centos7 ブリッジ ネットワークを構成する手順の詳細な説明

VMware仮想マシンでのCentos7ブリッジネットワーク構成の完全な手順は参考用です。具体的な内...

HTML で複数のクラス属性を定義する場合の無効な解決策

HTML を記述する過程で、クラス属性に複数の値を定義することがよくありますが、定義した値が無効であ...

Dockerのローカルイメージ作成方法の分析

コンテナと呼ばれるものは、実際には親イメージに基づいて読み取りおよび書き込み可能なファイル階層を作成...

MySQL 変数の原理と応用例

MySQL ドキュメントでは、MySQL 変数はシステム変数とユーザー変数の 2 つのカテゴリに分類...

Linux の netstat コマンドの詳細な紹介

目次1. はじめに2. 出力情報の説明3. netstatの共通パラメータ4. netstatネット...

nginx ssl を設定して https アクセスを実装する手順 (初心者向け)

序文サーバーを展開した後、私は大きな喜びを感じながら自分の Web サイトにアクセスし、見たものすべ...

Nginx設定の原理と実装プロセスの詳細な説明https

Linuxユーティリティcertbotを使用してhttps証明書を生成するこのツールは Let&#...