序文日常的にページングが必要な場合、通常は 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 の実行プロセスを見てみましょう。
SQL実行プロセス 実行計画は次のとおりです。 SQL が遅くなる理由は 2 つあります。
サブクエリによる最適化上記の 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 をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
<<: CSSアニメーションがJSによってブロックされるかどうかについての簡単な議論
>>: @Font-face の基本的な使い方と、すべてのブラウザと互換性を持たせる方法
目次序文クロージャの紹介メモリのゴミを識別する方法クロージャのメモリ表現結論序文クロージャは、Jav...
MySQLサービスを停止するWindowsでは、マイコンピュータを右クリック--管理--サービスと...
Ubuntu でサービスを作成し、自動的に起動する方法: 1. [/lib/systemd/syst...
Nginx は、IP ベースの仮想ホスト構成、ポート ベースの仮想ホスト構成、ドメイン名ベースの仮...
TypeScript バンドルwebpack 統合通常、実際の開発では、ビルド ツールを使用してコー...
最近、かなり暇です。大学4年生として数か月間インターンをしていました。インターンとして、Docker...
MySQLテーブルの自動インクリメント列の初期値をリセットする方法1. 問題の説明MySQL データ...
この効果はブラウザ ページで最もよく見られます。まずは効果の画像をご覧ください。 上の図に示すように...
VMware仮想マシンでのCentos7ブリッジネットワーク構成の完全な手順は参考用です。具体的な内...
HTML を記述する過程で、クラス属性に複数の値を定義することがよくありますが、定義した値が無効であ...
コンテナと呼ばれるものは、実際には親イメージに基づいて読み取りおよび書き込み可能なファイル階層を作成...
MySQL ドキュメントでは、MySQL 変数はシステム変数とユーザー変数の 2 つのカテゴリに分類...
目次1. はじめに2. 出力情報の説明3. netstatの共通パラメータ4. netstatネット...
序文サーバーを展開した後、私は大きな喜びを感じながら自分の Web サイトにアクセスし、見たものすべ...
Linuxユーティリティcertbotを使用してhttps証明書を生成するこのツールは Let...