5 つの mysql 最適化制限クエリステートメントの概要-mysql チュートリアル-php.cn

5 つの mysql 最適化制限クエリステートメントの概要

伊谢尔伦

リリース： 2017-06-28 14:12:43

オリジナル

1512 人が閲覧しました

この記事では主に、MySQL での制限クエリステートメントを最適化するための 5 つの方法を紹介します。それらは、サブクエリ最適化、逆テーブル最適化、逆検索最適化、制限最適化、および検索専用インデックスメソッドです。必要な方は、

mysql の

を参照してください。データを取得するには、オフセットと長さを制限するだけで十分です。ただし、オフセットと長さが比較的大きい場合、mysql のパフォーマンスは明らかに低下します

まず、最初の部分を見つけます。データの場合、このデータ以上の ID が取得されるデータになります

欠点: データは連続的である必要があり、where 条件は存在できないと言えます。where 条件によってデータがフィルタリングされるため、結果として次のような結果が得られます。データの連続性が失われます。具体的な方法については、次のクエリ例を参照してください:

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Member;
+----------+
| count(*) |
+----------+
|   169566 | 
+----------+
1 row in set (0.00 sec)
mysql> pager grep !~-
PAGER set to &#39;grep !~-&#39;
mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)
mysql> nopager
PAGER set to stdout

mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
   Query: select count(*) from Member
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00167000
   Query: select * from Member limit 10, 100
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00112400
   Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00263200
   Query: select * from Member limit 1000, 100
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00134000
   Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.09956700
   Query: select * from Member limit 100000, 100
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.02447700
   Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

ログイン後にコピー

結果から、オフセットが 1000

を超える場合、サブクエリ

メソッドを使用すると効果的にパフォーマンスを向上できることがわかります。

2. 逆テーブルの最適化方法

逆テーブルの方法は、テーブルを使用してページ数を維持し、効率的な接続を通じてデータを取得するインデックスの作成に似ています

欠点: 状況にのみ適していますデータの数は固定されており、データは 削除

できず、ページテーブルの保守も困難

転置テーブルの概要: (転置インデックスは、特に

検索エンジンのアルゴリズムの基礎と呼ばれます)

転置テーブルは、メモリに格納されている転置インデックスを追加できる転置レコードを参照します。転置テーブルはミニ転置インデックスです。

一時転置ファイルとは、ディスク上に保存され、ファイルの形式で保存される転置インデックスを指し、転置レコードを追加することはできません。一時転置ファイルは中規模の転置インデックスです。

最終転置ファイルとは、ディスク上にファイル形式で保存されている一時転置ファイルを結合して得られる転置インデックスを指します。最終的な転置ファイルは、より大きな転置インデックスになります。

転置インデックスは抽象的な概念であり、転置リスト、一時転置ファイル、および最終転置ファイルは、転置インデックスの 3 つの異なる表現です。

3. 逆検索最適化方法

オフセットがレコード数の半分を超える場合、オフセットが逆になるようにソートが最初に使用されます

欠点: 最適化による順序付けはより面倒であり、インデックスが必要ですインデックスがデータ変更効率に影響を与えるため、レコードの総数を知る必要があります。オフセットがデータの半分より大きい場合

オフセットアルゴリズムの制限:

前方検索: (現在のページ - 1) * ページの長さ

逆引き検索: 合計レコード - 現在のページ* ページの長さ

パフォーマンスを実験して確認してください

総レコード数: 1,628,775
ページあたりのレコード数: 40

総ページ数: 1,628,775 / 40 = 40720

中ページ数: 40720 / 2 = 20360

No. 21000 ページ
前方参照SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

ログイン後にコピー

時間: 1.8696秒

逆引きSQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

ログイン後にコピー

時間: 1.8336秒

ページ30000

前方参照SQL :

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

ログイン後にコピー

Time: 2.6493 秒

逆引き参照 sql:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

ログイン後にコピー

Time: 1.0035 秒

逆引き参照の結果は降順であり、InputDate はレコードの挿入時刻であることに注意してください。主キージョイントインデックスを使用することもできますが、不便です。

4. 制限の最適化方法

制限オフセットを特定の数値以下に制限します。。この数を超えるとデータが存在しないことを意味します。Alibaba の DBA がこれを実行したと言ったのを覚えています

5. インデックスのみの方法

MySQL の制限の動作原理は、最初に n 個のレコードを読み取り、その後最初の n 個のレコードを破棄することです。必要な項目を m 個読み取るため、n が大きくなるほどパフォーマンスは低下します。

最適化前の SQL:

SELECT * FROM member ORDER BY last_active LIMIT 50,5

ログイン後にコピー

最適化後の SQL:

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

ログイン後にコピー

違いは、最適化前の SQL では、最初にインデックスが読み取られ、次にデータが読み取られ、その後不要な行が読み取られるため、より多くの I/O 無駄が必要になることです。捨てられた。最適化された SQL (サブクエリ) 読み取り専用インデックス (カバーインデックス) で十分であり、その後、必要な列が member_id を通じて読み取られます。

以上が5 つの mysql 最適化制限クエリステートメントの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。