MySQL インデックス作成とクエリ最適化の詳細な紹介

この記事では、MySQL のインデックス作成とクエリの最適化について詳しく説明します。必要な方は参考にしていただければ幸いです。

記事「MySQL クエリ分析」では、MySQL の低速クエリと Explain コマンドを使用して、パフォーマンスのボトルネックとなる SQL ステートメントを特定した後、非効率な SQL ステートメントを分析する必要があります。最適化。この記事では主に、MySQL インデックスの原則と一般的に使用される SQL クエリの最適化について説明します。

簡単な比較テスト

前のケースでは、c2c_zwdb.t_file_count テーブルには自動インクリメント ID が 1 つだけあり、FFileName フィールドの SQL 実行状況はありません。インデックス付けは次のとおりです。

上の図では、type=all、key=null、rows=33777 です。この SQL はインデックスを使用しないため、非常に非効率的な全テーブル スキャンになります。結合クエリやその他の制約が追加されると、データベースは異常なメモリを消費し、フロントエンド プログラムの実行に影響を与えます。

この時点で、FFileName フィールドにインデックスを追加します。

alter table c2c_zwdb.t_file_count addindexindex_title(FFileName);

上記のクエリ ステートメントを再度実行します。

この図では、type=ref、key=インデックス名 (index_title)、rows=1 です。この SQL はインデックス Index_title を使用し、インデックスに基づいて 1 行だけをスキャンします。

インデックスを追加しない状況と比較すると、インデックスを追加した後のクエリ効率のコントラストは非常に明白です。

MySQL インデックス

上記の比較テストから、インデックスが高速検索の鍵であることがわかります。 MySQL インデックスの確立は、MySQL を効率的に運用するために非常に重要です。データ量が少ない場合、適切なインデックスがないことによる影響は大きくありませんが、データ量が増加すると、パフォーマンスが急激に低下します。複数のカラムにインデックスが付けられている場合 (結合インデックス)、カラムの順序は非常に重要であり、MySQL はインデックスの左端のプレフィックスに対してのみ効果的な検索を実行できます。

次に、いくつかの一般的な MySQL インデックス タイプを紹介します。

インデックスは、単一列インデックスと結合インデックスに分かれています。単一列インデックスは、インデックスに 1 つの列のみが含まれることを意味します。テーブルには複数の単一列インデックスを含めることができますが、これは結合されたインデックスではありません。結合インデックス、つまり、インデックスに複数の列が含まれています。

1. MySQL インデックス タイプ

(1) 主キー インデックス PRIMARY KEY

これは、null 値を許可しない特別な一意のインデックスです。通常、主キーインデックスはテーブル作成時に同時に作成されます。

もちろん、ALTER コマンドを使用することもできます。テーブルには主キーを 1 つだけ持つことができることに注意してください。

(2) 一意インデックス UNIQUE

一意インデックス列の値は一意である必要がありますが、NULL 値も許可されます。複合インデックスの場合、列値の組み合わせは一意である必要があります。テーブルの作成時に指定することも、

ALTER TABLE table_name ADD UNIQUE (column)

# のようにテーブル構造を変更することもできます。

# #(3) 通常のインデックス INDEX

これは最も基本的なインデックスであり、制限はありません。テーブルの作成時に指定することも、

ALTER TABLE

table_name ADD INDEX Index_name (column) のようにテーブル構造を変更することもできます。

(4) 結合インデックス INDEX

結合インデックス、つまり、インデックスに複数の列が含まれます。テーブルの作成時に指定することも、次のようにテーブル構造を変更することもできます。

ALTER TABLE

table_name ADD INDEX Index_name(column1, column2 、 column3)

(5) フルテキスト インデックス FULLTEXT

フルテキスト インデックス (フルテキスト検索とも呼ばれます) ) は、現在検索エンジン技術で使用されているキーです。単語分割テクノロジーなどのさまざまなアルゴリズムを使用して、テキスト内のキーワードの頻度と重要性をインテリジェントに分析し、特定のアルゴリズム ルールに従って必要な検索結果をインテリジェントにフィルタリングできます。

はテーブルの作成時に指定することも、次のようにテーブル構造を変更することもできます。

ALTER TABLE

table_name ADD FULLTEXT (column )

2. インデックスの構造と原理

B mysql ではインデックスとしてツリーがよく使われますが、クラスター化インデックスと非クラスター化インデックスによって実装が異なりますので、この記事ではこの点については説明しません。今のところ。

b ツリーの紹介

以下の b ツリーの写真は、さまざまな場所で見ることができます。なぜここでこの写真を選んだかというと、この写真だと思うからです。インデックス検索プロセスを非常によく説明できます。

上に示すように、これは B ツリーです。水色のブロックはディスク ブロックと呼ばれ、各ディスク ブロックには複数のデータ項目 (濃い青で表示) とポインター (黄色で表示) が含まれていることがわかります。たとえば、ディスク ブロック 1 にはデータ項目 17 と 35 が含まれています。 P1、P2、および P3 は 17 未満のディスク ブロックを表し、P2 は 17 ～ 35 のディスク ブロックを表し、P3 は 35 を超えるディスク ブロックを表します。

実際のデータはリーフ ノード、つまり 3、5、9、10、13、15、28、29、36、60、75、79、90、99 に存在します。非リーフ ノードには実際のデータは格納されず、検索方向をガイドするデータ項目のみが格納されます。たとえば、17 と 35 は実際にはデータ テーブルに存在しません。

検索プロセス

上図で、データ項目 29 を検索したい場合、まずディスク ブロック 1 がディスクからメモリにロードされ、この時点で IO が発生します。メモリ内で二分探索を使用して、29 が 17 ～ 35 の間にあることを確認します。ディスク ブロック 1 の P2 ポインタをロックします。メモリ時間は非常に短いため (ディスクの IO と比較して) 無視できます。 )、ディスク ブロック 1 の P2 ポインタのディスク アドレスを渡します。 ディスク ブロック 3 をディスクからメモリにロードします。2 番目の IO が発生します。29 は 26 と 30 の間です。ディスク ブロック 3 の P2 ポインタをロックし、ディスク ブロック 8 をロードします。ポインタを介してメモリにアクセスすると、3 回目の IO が発生し、同時にメモリ内でバイナリ検索を実行して 29 を見つけてクエリを終了します。合計 3 回の IO が発生します。実際の状況では、3 層の B ツリーは数百万のデータを表すことができ、数百万のデータ検索に必要な IO が 3 つだけであれば、パフォーマンスは大幅に向上します。インデックスがなければ、各データ項目に 1 つの IO が必要になります。 . の場合、合計数百万の IO が必要となり、明らかに非常にコストがかかります。

プロパティ

(1) インデックス フィールドはできるだけ小さくする必要があります。

上記の b ツリーの検索プロセスを通じて、または実際のデータがリーフ ノードに存在するという事実から、IO の数が b の数値の高さ h に依存することがわかります。

現在のデータテーブルのデータ量を N、各ディスクブロックのデータ数を m とすると、データ量 N のとき、木の高さ h=㏒(m 1)N となります。は定数です。m 値が大きいほど、h は小さくなります。

m = ディスク ブロックのサイズ/データ項目のサイズは、データ ページのサイズです。これは固定されており、データ項目が占めるスペースが小さい場合、データ項目の数 m が大きくなるほど、ツリーの高さ h は低くなります。このため、各データ項目、つまりインデックス フィールドはできるだけ小さくする必要があります。たとえば、int は、bigint の 8 バイトの半分である 4 バイトを占めます。

(2) インデックスの左端の一致する特性。

b ツリーのデータ項目が (名前、年齢、性別) などの複合データ構造である場合、(Zhang San ,20, F) このようなデータを取得する場合、b ツリーは名前の比較を優先して次の検索方向を決定します。名前が同じ場合は、順番に年齢と性別を比較し、最後に取得したデータを取得します。 20,F ) 名前のないデータが来た場合、B ツリーは次にどのノードをチェックすればよいかわかりません。名前は検索ツリーを構築するときの最初の比較要素であり、次にどこに行くかを知るためには最初に名前に基づいて検索する必要があるからです。問い合わせてください。たとえば、(Zhang San, F) のようなデータを取得する場合、B ツリーは名前を使用して検索方向を指定できますが、次のフィールド age が欠落しているため、名前が Zhang San に等しいすべてのデータしか検索できません。これは非常に重要なプロパティ、つまりインデックスの左端の一致特性です。

インデックスを構築するためのいくつかの主要な原則

(1) 左端のプレフィックス マッチングの原則

複数列インデックスの場合は、常に先頭から開始します。インデックス フィールドは開始して継続しており、途中をスキップすることはできません。たとえば、複数列のインデックス (名前、年齢、性別) を作成する場合、最初に名前フィールドが照合され、次に年齢フィールド、最後に性別フィールドが照合されます。中間のフィールドはスキップできません。 MySQL は、範囲クエリ (>、

通常、複数列インデックスを作成する場合、where 句で最も頻繁に使用される列が左端に配置されます。

左端のプレフィックス マッチング原則に準拠する補完と、この原則に準拠する補完の比較例を見てください。

例: テーブル c2c_db.t_credit_detail にはインデックス (Flistid,Fbank_listid)

があります。

左端のプレフィックス一致原則に準拠しない SQL ステートメント:

select * from t_credit_detail where Fbank_listid='201108010000199'G

この SQL ステートメントは 2 番目のインデックス フィールド Fbank_listid を直接使用します。スキップしてください。最初のインデックス フィールド Flistid が削除されますが、これは左端のプレフィックス マッチング原則に準拠しません。以下に示すように、explain コマンドを使用して SQL ステートメントの実行計画を表示します。

上の図からわかるように、SQL ステートメントはインデックスを使用せず、効率の低い全テーブル スキャンです。

左端のプレフィックス一致原則に準拠する SQL ステートメント:

select * from t_credit_detail where Flistid='2000000608201108010831508721' および Fbank_listid='201108010000199'G

この SQL は、最初にインデックスの最初のフィールド Flistid を使用し、次にインデックスの 2 番目のフィールド Fbank_listid を使用します。これは、左端のプレフィックス マッチングの原則に準拠しています。以下に示すように、explain コマンドを使用して SQL ステートメントの実行計画を表示します。

上の図からわかるように、SQL ステートメントはインデックスを使用し、1 行のみをスキャンします。

比較すると、全テーブル スキャンから定数スキャンまで、左端のプレフィックス マッチング原則に準拠する SQL ステートメントの効率は、この原則に準拠しない SQL ステートメントに比べて大幅に向上していることがわかります。

(2) 微分性の高い列をインデックスとして選択するようにしてください。

たとえば、インデックスとして学生番号を選択しますが、性別は選択しません。

(3) = と in は順序が異なる場合があります。

たとえば、a = 1、b = 2、c = 3 の場合、(a, b, c) インデックスは次のように作成できます。任意の順序、mysql クエリ オプティマイザーは、インデックスが認識できる形式に最適化するのに役立ちます。

(4) インデックス列は計算に参加できません。列を「クリーン」な状態に保ちます。

例: Flistid 1>'2000000608201108010831508721'。理由は非常に単純です。インデックス列が計算に関与する場合、インデックスが取得されるたびにインデックスが 1 回計算されてから比較されることになるため、明らかにコストが高くなりすぎます。

(5) インデックスを可能な限り拡張し、新しいインデックスを作成しないでください。

たとえば、テーブル内にすでに a のインデックスがあり、(a, b) のインデックスを追加したい場合は、元のインデックスを変更するだけで済みます。

インデックスの欠点

インデックスを使用するとクエリの効率が向上しますが、インデックスには独自の欠点もあります。

インデックスの追加オーバーヘッド:

(1) スペース: インデックスはスペースを占有する必要があります。

(2) 時間: インデックスのクエリには時間がかかります。 ##( 3) メンテナンス: インデックスをメンテナンスする必要があります (データ変更時)

インデックスの使用は推奨されません:

(1) 少量のデータを含むテーブル。

(2) スペースが狭いです

一般的に使用される最適化の概要

最適化ステートメントは数多くあり、注意が必要な点も数多くあります。通常の状況に基づくいくつかのポイント:

1. インデックスはありますが、使用されません (非推奨)

(1) Like のパラメーターがワイルドカード文字 # で始まる場合。

## Like のパラメータをワイルドカード文字で始めることは避けてください。ワイルドカード文字で始まると、データベース エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。

ワイルドカードで始まる SQL ステートメント。例: select * from t_credit_detail where Flistid like '%0'G

これがすべてですテーブル スキャンはインデックスを使用しないため、推奨されません。

ワイルドカードで始まらない SQL ステートメント。例: select * from t_credit_detail where Flistid like '2%'G

明らかに、これは範囲検索であるインデックスを使用しており、ワイルドカード文字で始まる SQL ステートメントよりもはるかに効率的です。

(2) where条件が左端プレフィックス一致原則に従わない場合

左端プレフィックス一致原則の内容に例を示しました。

(3) 使ってください！ = または 演算子

は使用しないようにしてください。 = または 演算子を使用しない場合、データベース エンジンはインデックスの使用を放棄し、テーブル全体のスキャンを実行します。 > または select * from t_credit_detail where Flistid != '2000000608201108010831508721'G

(4) インデックス列が計算に参加します

where 句内のフィールドに対する式操作は避ける必要があります。これにより、エンジンがインデックスの使用を断念し、テーブル全体のスキャンが実行されます。

select * from t_credit_detail where Flistid 1 > '2000000608201108010831508722'G

(5) フィールドの null 値を判定します

where 句でフィールドの null 値を判断しないようにしてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。 非効率: select * from t_credit_detail where Flistid is null;

Flistid にデフォルト値 0 を設定し、テーブルの Flistid 列に null 値がないことを確認してから、次のようにクエリを実行できます。 効率的: select * from t_credit_detail where Flistid =0;

(6) 条件を接続するには or を使用します

条件を接続するために where 句で or を使用することは避けてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。 : 非効率: select * from t_credit_detail where Flistid = '2000000608201108010831508721' または Flistid = '10000200001';

次のクエリを使用して上記またはクエリを置き換えることができます: 効率的: select

from t_credit_detail where Flistid = '2000000608201108010831508721' Union all select

from t_credit_detail where Flistid = '10000200001' ;

#2. *

の選択は避けてください。解析プロセス中に、「*」がすべての列名に順番に変換されます。これはデータ ディクショナリにクエリを実行することによって行われるため、より時間がかかります。

ですから、必要なものは何でも摂取する良い習慣を身につけるべきです。

3. Order by ステートメントの最適化

Order by ステートメント内の非インデックス項目または計算式は、クエリ速度を低下させます。

方法: 1. インデックスを使用するように order by ステートメントを書き換えます。

  2.为所使用的列建立另外一个索引

  3.绝对避免在order by子句中使用表达式。

4. GROUP BY ステートメントの最適化

不要なステートメントを削除して、GROUP BY ステートメントの効率を向上させます。 GROUP BY

Ineficiency:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP by JOB

## の前にレコードがフィルターで除外されます。 #HAVING JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

効率的:

SELECT JOB , AVG(SAL)

EMP

WHERE JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

GROUP by JOB

5. in

の代わりに、existes を使用します。多くの場合、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) )

6. char/nchar の代わりに varchar/nvarchar を使用します。

第一に、可変長フィールドの記憶領域が制限されるため、できるだけ char/nchar の代わりに varchar/nvarchar を使用してください。次に、クエリに関しては、比較的小さなフィールド内で検索する方が明らかに効率的です。

7. DISTINCT を使用できる場合は、GROUP BY

SELECT OrderID FROMDetails WHERE UnitPrice > 10 GROUP BY OrderID

を次のように変更する必要はありません。

SELECT DISTINCT OrderID FROM 詳細 WHERE UnitPrice > 10

8. UNION ALL を使用できる場合は、UNION を使用しないでください

UNION ALL は SELECT DISTINCT を実行しません。これにより、多くの不要なリソースが削減されます。

9. テーブルを結合してインデックスを付ける場合は、同等のタイプの例を使用します。

アプリケーションに多数の JOIN クエリがある場合は、2 つのテーブルの結合フィールドがインデックス付けされていることを確認する必要があります。このようにして、MySQL は内部で Join SQL ステートメントを最適化するメカニズムを開始します。

さらに、結合に使用されるこれらのフィールドは同じ型である必要があります。例: DECIMAL フィールドを INT フィールドと結合する場合、MySQL はそれらのインデックスを使用できません。これらの STRING 型の場合も、同じ文字セットを持つ必要があります。 (2 つのテーブルの文字セットは異なる場合があります)

この記事はここで終了です。MySQL について詳しくは、php 中国語 Web サイトの

MySQL チュートリアル の列を参照してください。 ！ ！

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