MySQL ページング制限の実用的な最適化

序文

クエリステートメントを使用する場合、多くの場合、データの最初の数行または中間行を返す必要があります。このとき、何をすればよいでしょうか。心配しないでください。MySQL はすでにそのような機能を提供しています。

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

LIMIT 句を使用すると、SELECT ステートメントで指定された数のレコードを返すように強制できます。 LIMIT は 1 つまたは 2 つの数値引数を受け入れます。引数は整数定数でなければなりません。 2 つの引数が指定された場合、最初の引数は返される最初の行のオフセットを指定し、2 番目の引数は返される行の最大数を指定します。初期行オフセットは 0 (1 ではありません) です。PostgreSQL との互換性のため、MySQL は LIMIT # OFFSET # という構文もサポートしています。

したがって、通常、データをクエリするときには制限ページングを使用します。これにより、テーブル全体のクエリが回避され、クエリの効率が向上します。ただし、テーブル内のデータ量が増えると、ページングクエリは遅くなります。以下で詳細な紹介を見てみましょう。

MySQL ページング制限の最適化

2,000 万件のデータを含むテストテーブルカードを作成する

mysql> カードから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 20000000 |
+----------+
セット内の 1 行 (0.00 秒)

-まず最初の1000行のクエリ速度をテストします

mysql> カード制限 1000,10 から * を選択します。
+---------+--------------------------------------+
| カードID | カード番号 |
+---------+--------------------------------------+
| 1001 | 13fc90a6-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1002 | 13fc923e-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1003 | 13fc93d5-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1004 | 13fc956a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1005 | 13fc9702-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1006 | 13fc9899-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1007 | 13fc9a31-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1008 | 13fc9bc6-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1009 | 13fc9d5e-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1010 | 13fc9ef5-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
セット内の行数は 10 です (0.00 秒)

-100万回後にクエリをテストする

mysql> カード制限 1000000,10 から * を選択します。
+---------+--------------------------------------+
| カードID | カード番号 |
+---------+--------------------------------------+
| 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000010 | 2d871039-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 行セット (0.18 秒)

-1000万回後にクエリをテストする

mysql> カード制限 10000000,10 から * を選択します。
+----------+--------------------------------------+
| カードID | カード番号 |
+----------+--------------------------------------+
| 10000001 | b11ad76c-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000002 | b11aefd5-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000003 | b11af868-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000004 | b11b0031-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000005 | b11b07ad-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000006 | b11b0f0f-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000007 | b11b1669-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000008 | b11b1db2-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000009 | b11b24fa-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000010 | b11b2c37-2e49-11e8-ae62-9c5c8e6e37cf |
+----------+--------------------------------------+
10 行セット (1.29 秒)

時間が経つにつれてクエリの効率が低下することがわかります。 100 万個を超えるデータを照会する場合、MySQL は最初に 100 万個と 10 個のデータを照会し、次に次の 10 個のデータを傍受します。これらはパフォーマンスの低下を引き起こします。

では、100 万個のデータのスキャンを回避するにはどうすればよいでしょうか? 100 万以降の主キーが 100 万より大きいことが明確にわかります。そこで、インデックスを使用するようにSQLを書き直し、スキャンする行数を減らすことができます。

mysql> select * from card where card_id>=1000000 limit 10;
+---------+--------------------------------------+
| カードID | カード番号 |
+---------+--------------------------------------+
| 1000000 | 2d870088-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
セット内の行数は 10 です (0.00 秒)

これにより、クエリの効率が大幅に向上します。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

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