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 をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。

以下もご興味があるかもしれません: