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 の基本的な使い方と、すべてのブラウザと互換性を持たせる方法

推薦する

MySQL テーブルをコピーする 3 つの方法 (要約)

テーブル構造とそのデータをコピーする次のステートメントは、データを新しいテーブルにコピーします。注:...

MySQL InnoDB トランザクション ロック ソースコード分析

目次1. ロックとラッチ2. 繰り返し読み取り3. インサートロックプロセス3.1 ロックモード3....

MySQLで行または列をソートする方法

方法:説明: どちらも達成できません:方法1: sp_product から sp.productid...

Flash での HTML と CSS の適用

Flash での HTML と CSS の適用:同僚の Den が Flash で HTML と C...

子コンポーネントを通じて親コンポーネントのプロパティを変更するための Vue のさまざまな実装方法

目次序文一般的な方法1. 親コンポーネントを介して子コンポーネントの発行イベントをリッスンしてpro...

JS にこれがあるのはなぜですか?

目次1. 需要2. 解決策3. 最初の改善4.砂糖を加える5. 理解不能6. 問題点7. オブジェク...

Vuex でゲッターとアクションを使用するための追加手順

予備的注釈1.Vue2.xとVue3.xの違い: Vue 3.x にはヘルパー関数はありません。 V...

React コンポーネントのコンストラクタとスーパーの知識ポイントのまとめ

1. Reactでクラス宣言する際のヒント 上記のように、Child クラスは class キーワー...

CSSの複数条件の書き方の詳細説明:

:not疑似クラスセレクターは、式に一致しない要素をフィルタリングできます。例 テーブル tbod...

誰もが登録できるようにJiedaibaoを宣伝するにはどうすればよいでしょうか? ジエダイバオのプロモーション方法とスキル

借財宝は最近人気が出ている携帯電話ローンソフトウェアプラットフォームです。知人同士の貸し借りが特徴で...

MySQL が自動的に再起動する問題の解決方法

序文最近、テスト環境で MySQL データベースが自動的に再起動し続ける問題が発生しました。原因は、...

Linux で開いているポートへのリモート アクセスを許可する方法

1. ファイアウォール設定ファイルを変更する # vi /etc/sysconfig/iptable...

MySQL の自動増分 ID (主キー) が不足した場合の解決策

MySQL で使用される自動インクリメント ID には多くの種類があり、各自動インクリメント ID ...

Vue組み込みコンポーネントのキープアライブの使用例

目次1. キープアライブの使用使用例: 1. すべてのページをキャッシュする: 2. 条件に基づいて...

Nginxを再コンパイルしてモジュールを追加する方法

Nginx をコンパイルしてインストールするときに、http_ssl_module などの一部のモジ...