MySQL Limitクエリのパフォーマンスを向上させる方法

MySQL Limitクエリのパフォーマンスを向上させる方法

MySQL データベース操作では、一部のクエリを実行するときにデータベース エンジンが完全なテーブル スキャンを実行することを常に回避する必要があります。完全なテーブル スキャンには長い時間がかかり、ほとんどのスキャンはクライアントにとって無意味だからです。実際、Limit キーワードを使用すると、テーブル全体のスキャンを回避し、効率を向上させることができます。

MySQL 5.0.x には数千万件のレコードを含むテーブルがあり、数億件のレコードを読み取る必要があります。よく使用される方法は、順番に繰り返します。

index_col = xxx limit offset, limit の場合、mytable から * を選択します。

経験: BLOB/テキスト フィールドがなく、単一行レコードが比較的小さい場合は、制限をより大きな値に設定してプロセスを高速化できます。

問題:最初の数万件のレコードは非常に速く読み取られますが、速度は直線的に低下します。同時に、MySQL サーバーの CPU は 99% であり、これは許容できません。

explain select * from mytable where index_col = xxx limit offset , limit; を呼び出します。表示タイプ = ALL

MySQLの最適化ドキュメントには「すべて」の説明が書かれています

以前のテーブルの行の組み合わせごとに、完全なテーブル スキャンが実行されます。テーブルが const としてマークされていない最初のテーブルである場合、これは通常適切ではなく、他のすべての場合に非常に不適切です。通常、以前のテーブルの定数値または列値に基づいてテーブルから行を取得できるようにするインデックスを追加することで、ALL を回避できます。

allに対して、MySQL はより扱いにくい方法を使用するようですが、代わりにrangeメソッドを使用しないのはなぜでしょうか? idが増分されるため、SQL の変更も簡単です。

mytable から * を選択します。id > offset かつ id < offset + limit かつ index_col = xxx

explain では type = range が表示され、結果の速度は非常に理想的で、数十倍速く結果が返されます。

制限構文:

SELECT * FROM テーブル LIMIT [オフセット,] 行 | 行 OFFSET オフセット

LIMIT 句を使用すると、SELECT ステートメントで指定された数のレコードを返すように強制できます。 LIMIT は 1 つまたは 2 つの数値引数を受け入れます。引数は整数定数でなければなりません。

2 つの引数が指定された場合、最初の引数は返される最初の行のオフセットを指定し、2 番目の引数は返される行の最大数を指定します。最初のレコード行のオフセットは 0 (1 ではない) です。

PostgreSQL との互換性のため、MySQL は LIMIT # OFFSET # という構文もサポートしています。

mysql> SELECT * FROM table LIMIT 5,10; // 6-15行目を取得
//特定のオフセットからレコードセットの最後までのすべての行を取得するには、2番目のパラメータを-1に指定します。
mysql> SELECT * FROM table LIMIT 95,-1; // 96行目から最後の行までを取得
//パラメータが1つだけ指定されている場合は、返される行の最大数を示します。つまり、LIMIT nはLIMIT 0,nと同じです。
mysql> SELECT * FROM table LIMIT 5; // 最初の5行を取得します

MySQL の制限はページングに非常に便利ですが、データ量が多い場合、制限のパフォーマンスは急激に低下します。同じ 10 個のデータを使用しても、次の 2 つの文は同じ桁数ではありません。

テーブル制限10000,10から*を選択
テーブル制限 0,10 から * を選択

この記事では、制限は直接使用されません。代わりに、最初にオフセット ID を取得し、次に制限サイズを直接使用してデータを取得します。彼のデータによれば、limit を直接使用するよりも明らかに優れています。

ここでは、データを使用して 2 つの状況でテストします。

1. オフセットが比較的小さい場合:

テーブル制限10,10から*を選択 
// 複数回実行し、時間を 0.0004 ~ 0.0005 の範囲に保ちます。Select * From table Where vid >=(Select vid From table Order By vid limit 10,1) limit 10 
// 複数回実行し、時間は 0.0005 ~ 0.0006 の間で維持されます (主に 0.0006)

結論: オフセットが小さい場合は、limit を直接使用する方が適切です。これが明らかにサブクエリの理由です。

2. オフセットが大きい場合:

テーブル制限10000,10から*を選択 
// 複数回実行しても、時間は 0.0187 程度のままです。Select * From table Where vid >=(Select vid From table Order By vid limit 10000,1) limit 10
// 複数回実行した後も、時間は 0.0061 程度のままで、前回の 1/3 にすぎません。オフセットが大きいほど、後者の方が優れていることが期待できます。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。

以下もご興味があるかもしれません:
  • MySQL クエリの最適化: LIMIT 1 はテーブル全体のスキャンを回避し、クエリの効率を向上させます
  • 制限を使用すると、MySQL のページングがどんどん遅くなるのはなぜですか?
  • MySQL 最適化 query_cache_limit パラメータの説明
  • MySQLのorder byとlimitを混在させる際の落とし穴の詳細な説明
  • MySQL ページングの制限パラメータの簡単な例
  • 大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション
  • MySQL のソートとページング (order by と limit) と既存の落とし穴
  • MySQLの制限を使用して大規模なページングの問題を解決する方法
  • MySQL における制限関数と合計関数の混在使用の問題の詳細な説明
  • MySQL Limitパフォーマンス最適化とページングデータパフォーマンス最適化の詳細な説明
  • MySQL の制限ページング最適化ソリューションの実装に関する簡単な説明
  • MySQL のクエリパフォーマンスに対する制限の影響

<<:  WIN10 に複数のデータベースがインストールされている場合にコンピュータの速度低下を防ぐ方法

>>:  ログインフォームを実装するためのJavaScript

推薦する

サブクエリ最適化における MySQL 選択の実装

以下のデモはMySQLバージョン5.7.27に基づいています。 1. MySQLサブクエリ最適化戦略...

Vue 条件付きレンダリング v-if と v-show

目次1. 動詞-if 2. <template> で v-if を使用する3. キーを使...

仮想マシンに Linux rhel7.3 オペレーティング システムをインストールする (具体的な手順)

仮想化ソフトウェアをインストールする仮想マシンにオペレーティング システムをインストールする前に、ホ...

独自のネイティブ JavaScript ルーターを作成する方法

目次序文導入JavaScript 履歴 API独自のネイティブJSルーティングを実装するHistor...

IE6/IE7/IE8/IE9/FF 向け CSS ハック (概要)

IE8.0の正式版をインストールしたので、基本的なCSS HACKをいくつかまとめてみました。We...

インラインブロックを使用した複数のdiv間の間隔はプログラミング方法とは異なります

inline-block について学習しているときに、境界線と inline-block を持つ複数...

純粋な CSS で中空効果を実現するためのサンプルコード

私は最近、空洞化効果について研究しました。背景クリップ: テキスト背景はテキストの前景色にクリップさ...

docker を使用して複数のネットワーク インターフェースを持つコンテナーを起動する方法の例

コンテナにネットワークインターフェースを追加する1 デフォルトのネットワークモードでコンテナを実行す...

HTML フォームタグチュートリアル (2):

このチュートリアルでは、ウェブデザインにおけるFORMフォームタグのさまざまな属性の応用を紹介します...

Linux環境でグラフデータベースneo4jを構築する方法の説明

Neo4j (Nosql の 1 つ) は、高性能なグラフ データベース (分散をサポートしていませ...

HTML で入力プロンプトのテキスト スタイルを変更するためのサンプル コード

多くのウェブサイトでは、入力ボックスにヒントテキストが表示されています。入力ボックスにヒントテキスト...

Mysqlサーバーのインストール、構成、起動、シャットダウン方法の詳細な説明

1. 公式サイトからダウンロード: https://dev.mysql.com/downloads/...

Vue要素のバックグラウンド認証プロセスの分析

序文:最近、プロジェクトで管理システムに遭遇しました。権限設定が非常に興味深いと思いました。自分の学...

ページのレンダリング時間を短縮してページの実行速度を速めます

ブラウザでのページのレンダリング時間をできるだけ短縮するにはどうすればよいでしょうか? この記事は、...

Vue 親コンポーネントが子コンポーネント関数の実装を呼び出す

Vue親コンポーネントは子コンポーネントの関数を呼び出す親コンポーネントはイベントを通じて子コンポー...