序文日常的にページングが必要な場合、通常は 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 の基本的な使い方と、すべてのブラウザと互換性を持たせる方法
テーブル構造とそのデータをコピーする次のステートメントは、データを新しいテーブルにコピーします。注:...
目次1. ロックとラッチ2. 繰り返し読み取り3. インサートロックプロセス3.1 ロックモード3....
方法:説明: どちらも達成できません:方法1: sp_product から sp.productid...
Flash での HTML と CSS の適用:同僚の Den が Flash で HTML と C...
目次序文一般的な方法1. 親コンポーネントを介して子コンポーネントの発行イベントをリッスンしてpro...
目次1. 需要2. 解決策3. 最初の改善4.砂糖を加える5. 理解不能6. 問題点7. オブジェク...
予備的注釈1.Vue2.xとVue3.xの違い: Vue 3.x にはヘルパー関数はありません。 V...
1. Reactでクラス宣言する際のヒント 上記のように、Child クラスは class キーワー...
:not疑似クラスセレクターは、式に一致しない要素をフィルタリングできます。例 テーブル tbod...
借財宝は最近人気が出ている携帯電話ローンソフトウェアプラットフォームです。知人同士の貸し借りが特徴で...
序文最近、テスト環境で MySQL データベースが自動的に再起動し続ける問題が発生しました。原因は、...
1. ファイアウォール設定ファイルを変更する # vi /etc/sysconfig/iptable...
MySQL で使用される自動インクリメント ID には多くの種類があり、各自動インクリメント ID ...
目次1. キープアライブの使用使用例: 1. すべてのページをキャッシュする: 2. 条件に基づいて...
Nginx をコンパイルしてインストールするときに、http_ssl_module などの一部のモジ...