MySQL クエリ最適化の詳細な紹介

1. はじめに

優れた Web アプリケーションで最も重要なことは、優れたアクセス パフォーマンスです。データベース MySQL は Web アプリケーションに不可欠な部分であり、そのパフォーマンスを決定する重要な部分です。したがって、MySQL のパフォーマンスを向上させることが重要です。

MySQL のパフォーマンスの向上は、ハードウェア、ネットワーク、ソフトウェアの 3 つの部分に分けることができます。このうち、ハードウェアとネットワークは会社の財源に依存し、多額の費用がかかるため、ここでは触れません。ソフトウェアは多くの種類に分類されます。ここでは、MySQL クエリの最適化によってパフォーマンスの向上を実現します。

最近、クエリ最適化に関する本を何冊か読み、オンラインで先輩が書いた記事も読みました。

以下は、私がコンパイルして学んだクエリ最適化に関する概要です:

2. SQL ステートメントのインターセプト

1. 包括的なクエリ ログ

2. 遅いクエリ ログ

3. バイナリ ログ

4. プロセスlist

Show FULL PROCESSLIST;

。 。 。

3. クエリ最適化のための基本的な分析コマンド

1. EXPLAIN {PARTITIONS|EXTENDED}

2. SHOW CREATE TABLE タブ;

3. SHOW INDEXS FROM tab;

4. SHOW TABLE STATUS LIKE 'tab';

5. SHOW [GLOBAL|SESSION] STATUS LIKE '';

6. SHOW VARIABLES

。 。 。 。

追伸：個人的には、上記のものはどれも栄養的には欠けていると感じています。こちらが実物です。

4. クエリ最適化のためのいくつかの方向性

1. フルテキストスキャンを回避し、対応するフィールドにインデックスを追加し、クエリにインデックスを使用するようにしてください

2. 未使用または重複したインデックスを削除します

3. クエリの書き換え、等価変換(述語、サブクエリ、結合クエリ)

4. 繰り返しコンテンツのある不要なステートメントを削除し、ステートメントを合理化します

5. 繰り返し実行されるステートメントを統合します

6. クエリ結果をキャッシュします

5. インデックスの最適化

5.1. インデックスの利点:

1. データの整合性を維持します

2. データのクエリパフォーマンスを改善します

3. テーブルの結合操作（jion）を改善します

4. クエリ結果を並べ替えます。インデックスがない場合、内部ファイルソートアルゴリズムがソートに使用され、低速になります

5. 集計データの操作を簡素化します

5.2. インデックスのデメリット

1. インデックスは一定量のストレージスペースを占有する必要があります

2. データの挿入、更新、削除はインデックスの影響を受け、パフォーマンスが低下します。データが変化するため、インデックスも更新する必要があります

3. 複数のインデックス、オプティマイザーに時間がかかる場合は、最適なものを選択します

5.3. インデックスの選択

1. データ量が多い場合に使用します

2. データの反復性が高い場合は使用しないでください

3. クエリがデータの 20% を超えて取得する場合は、インデックスを作成せずに全文スキャンが使用されます

5.4. インデックスの詳細な検討

データ クエリ:

MySQL の InnoDB と MyISAM は B-Tree タイプのインデックスです

B-Tree には PRIMARY KEY、UNIQUE、INDEX、FULLTEXT が含まれます

B-Tree タイプのインデックスはサポートされていません (つまり、フィールドで次の記号が使用されている場合) 、インデックスは使用されません):

>, <, >=, <=, BETWEEN , !=, <>,like '%**'

【カバーインデックスを紹介しますまずは】

分かりやすくご紹介させて頂きます。カバーインデックスは、主キーインデックスや一意インデックスのように実際には存在しません。これは、インデックス適用のための特定のシナリオを定義するだけです (別の理解: クエリされた列はインデックス列であるため、その列はインデックスによってカバーされます)。従来の制限を突破し、上記の演算子を使用し、クエリにインデックスを使用することができます。

クエリ対象の列はインデックス列であるため、行を読み取る必要はなく、列フィールドのデータのみを読み取る必要があります。 [たとえば、本を読んでいて、特定の内容を見つける必要があり、その内容がたまたま目次に表示されている場合、ページごとにめくる必要はなく、目次でそのページを見つけるだけです。コンテンツと検索]

カバーインデックスを有効にするにはどうすればよいですか？具体的なシナリオとは何ですか?

インデックスフィールドは選択に表示されるだけです。

複合インデックスには他の特別なシナリオも存在する可能性があります。たとえば、3 列の複合インデックスの場合、カバー インデックスの使用を有効にするには、select、where、group by、order by で複合インデックスの左端の列を 1 回出現させるだけで済みます。

View:

EXPLAIN の余分に「インデックスを使用」と表示され、このステートメントがカバーインデックスを使用していることを示します。

結論:

クエリ時に select*from を使用することは推奨されません。クエリのパフォーマンスを向上させるために、必要なフィールドを記述し、対応するインデックスを追加する必要があります。

上記演算子の実際の測定結果:

1. select*from の形式では、where の主キーを使用して kill [like を除く] (クエリインデックスに主キーを使用することはできません)。全て。

2. select field a from tab where field a "上記の演算子" の形式でテストしますが、結果はインデックスを使用してクエリすることができます。 【カバーリングインデックスの使用】

他のインデックス最適化方法:

1. 接続条件としてインデックスキーワードを使用します

2. 複合インデックスを使用します

3. インデックスのマージ、または関連するフィールドを複合インデックスにマージします

4. where と group by が含まれますフィールドとインデックス

6. サブクエリの最適化

fromには、親レイヤーにプルアップできる非相関サブクエリがあります。複数テーブルの結合クエリでは、選択する前に結合コストを考慮してください。

クエリオプティマイザーは通常、サブクエリに対してネストされた実行を使用します。つまり、親クエリの各行に対してサブクエリを1回実行するため、サブクエリは何度も実行されます。この実行方法は非常に非効率的です。

サブクエリを結合クエリに変換する利点:

1. サブクエリを何度も実行する必要がない

2. オプティマイザは情報に基づいてさまざまな方法と接続シーケンスを選択できます

3. 接続条件とフィルタリングサブクエリの条件が親クエリのフィルタ条件に変更され、効率が向上します。

最適化:

サブクエリのマージ。複数のサブクエリがある場合は、可能な限りそれらをマージするようにしてください。

サブクエリの展開、つまりプルアップは複数テーブルのクエリになります（常に等価な変更が保証されます）

注:

サブクエリに集計関数、GROUP BY、が含まれる場合、サブクエリの展開は単純なクエリのみを展開できます。および DISTINCT の場合、プルアップできません。

select*from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age

select*from t1,tab as t2 where t1.age>10 and t2. age<25 および t2.id>10;

具体的な手順:

1. from と from をマージし、対応するパラメーターを変更します

2. where と where をマージし、connect with and

3. 対応する述語を変更します (in =)

7. 同等の述語の書き換え:

1. BETWEEEN AND を >=、<= などに書き換えます。実測：データ10万件、書き換え前後の時間、1.45秒、0.06秒

2.倍数またはinで変換します。フィールドがインデックスの場合、両方ともインデックスを使用できるか、より効率的です

3. 'abc%' のような名前は、name>='abc' および name<'abd';

注: millions として書き換えられます。名前にインデックスがないレベルのデータ テストは、後者のクエリよりも高速です。フィールドにインデックスを追加した後は、後者のクエリの方が少し高速ですが、両方のメソッドがクエリ時にインデックスを使用するため、大きな違いはありません。

。 。 。 。

8. 条件の単純化と最適化

1. where、having(groupbyやaggregate関数がない場合)、join-on条件を可能な限り組み合わせる

2. 不要な括弧を削除し、構文の断片化を減らす or andとツリーレイヤーによりCPU消費量が削減されます

3. 一定の転送。 a=b および b=2 は、a=2 および b=2 に変換されます。変数 a=b または a=@var

を使用しないようにします

4. 無駄な SQL 条件を削除します

5. where 等号の右側で式の計算を実行したり、関数を使用したりしないようにしてください。 where のフィールド

6. 恒等変換と不等式変換。例: 数百万のデータ a>b および b>10 をテストすると、a>b および a>10 および b>10 になります。 最適化は重要です

9. 外部接続の最適化

これは、外部接続を内部接続に変換することです。

利点:

1. 最適化プロセッサーは外部結合をより多くのステップで処理し、内部結合よりも時間がかかります

2. 外部結合が排除された後、オプティマイザーは複数テーブルの順序を選択する際により多くの選択肢を持ちます結合を結合し、最適なものを選択できます

3. 条件を最も絞り込むことができます 外面（接続順序の最初、多層ループ本体の外ループ層）として厳密なテーブルを使用すると、不要な I/O オーバーヘッドを削減し、アルゴリズムの実行を高速化します。

on a.id=b.id と where a.id=b.id の違いは、on はテーブルが接続されることを意味し、where はデータを比較します

注: 結果が NULL であることが前提である必要があります(つまり、条件が NULL ではない) 意味的に言えば、データ行、内部結合)

最適化原則:

クエリの合理化、接続の削除、等価変換、冗長なテーブル オブジェクト接続の削除

例: 主キー/一意キー接続条件として、中間テーブルの列は etc としてのみ使用されます。値の条件では、中間テーブルの接続を削除できます

10. その他のクエリの最適化

1. 以下により、インデックス クエリが破棄され、完全になります。使用するテキスト スキャン

1.1. where 句で != または <> 演算子を使用します。非主キーはサポートされていません

1.2. orの使用を避ける

テスト後、orを使用してもインデックスが使用できないというわけではありませんが、場合によっては使用されない場合もあります。が使用されるため、特定の状況を分析します。

同様の最適化:

select * from tab name='aa' or name='bb';

=>

select * from tab name='aa'

union all

Select * from tab name ='bb';

実測値:

1. インデックスなしで 100,000 個のデータでテストしたところ、上記のクエリ速度は以下のクエリの 2 倍速かった。

2. 30万データのテスト、aaとbbが別々にインデックス付けされている場合、次のクエリ速度はorよりも少し速くなります。

1.3. not in の使用を避ける

not in は一般にインデックスを使用できません

1.4. where では null 判定を使用しないようにしてください

1.5. Like の前に「%.com」を付けることはできません

解決策:

1. % プレフィックスを使用する必要があり、URL などデータ長が大きくない場合は、データを反転してデータベースに保存し、再度確認することができます。 LIKE REVERSE'%.com';

2. カバーリングインデックスを使用する

1.6. インデックスフィールドを条件として使用する場合、複合インデックスの場合、インデックスの一番左のプレフィックスを持つフィールド名を使用する必要があります

2.存在を in に置き換えます

select num from a where num in(select num from b)

select num from a where names(select 1 from b where num=a.num)

100万個のデータなら6.65かかります59,417 個のデータをフィルタリングするには 4.18 秒。他の最適化は行われず、exists を in に置き換えるだけです。

3. フィールド定義は文字列であり、クエリ時に引用符は使用されず、フルテキスト スキャンは実行されません。

【以下は深夜のLuantanqinブログ投稿http://www.cnblogs.com/lingiu/p/3414134.htmlからの抜粋です】対応するテストは行っていません】

4.テーブル変数を使ってみる。一時テーブルの代わりに

5. システム テーブル リソースの消費を削減するために、一時テーブルの頻繁な作成と削除を避けてください

6. 一時テーブルを使用する場合は、最初にストアド プロシージャの最後にすべての一時テーブルを明示的に削除する必要がありますtruncate table, thendrop table, これにより、システム テーブルの長期ロックを回避できます

7. カーソルによって操作されるデータが 10,000 行を超える場合は、カーソルの使用を避けるようにしてください

。

8. データ量が大きい場合 データ量が大きすぎる場合は、対応する要件が妥当であるかどうかを検討する必要があります。

9. 大規模なトランザクション操作を避け、システムの同時実行性を向上させるようにしてください。

以上がMySQL クエリ最適化の詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。