mysql インデックスの設計と使用

インデックスの設計と使用
5.1 MySQL インデックスの概要
すべての MySQL 列タイプにインデックスを付けることができます。関連する列にインデックスを使用することは、SELECT 操作のパフォーマンスを向上させる最善の方法です。ストレージ エンジンに応じて、各テーブルのインデックスの最大数と最大インデックス長を定義します。すべてのストレージ エンジンは、テーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。ほとんどのストレージ エンジンには、より高い制限があります。
MySQL 5.1 では、MyISAM および InnoDB テーブルの場合、プレフィックスの長さは最大 1000 バイトです。プレフィックスの制限はバイト単位で測定する必要があるのに対し、CREATE TABLE ステートメントのプレフィックスの長さは文字数として解釈されることに注意してください。マルチバイト文字セットを使用する列のプレフィックス長を指定するときは、必ずこれを考慮してください。
FULLTEXT インデックスを作成することもできます。インデックスは全文検索に使用できます。 MyISAM ストレージ エンジンのみが FULLTEXT インデックスをサポートし、CHAR、VARCHAR、TEXT 列のみをサポートします。インデックスは常に列全体に対して実行され、部分 (プレフィックス) インデックスはサポートされません。空間列タイプに対してインデックスを作成することもできます。 MyISAM ストレージ エンジンのみが空間タイプをサポートします。空間インデックスは R ツリーを使用します。デフォルトでは、MEMORY (HEAP) ストレージ エンジンはハッシュ インデックスを使用しますが、B ツリー インデックスもサポートされています。
5.2 インデックス設計の原則
1. 検索対象となるインデックス列は、必ずしも選択される列ではありません。つまり、インデックス付けに最も適した列は、SELECT キーワードの後の選択リストに表示される列ではなく、WHERE 句に表示される列、または join 句で指定された列です。
2. 一意のインデックスを使用します。列内の値の分布を考えてみましょう。インデックスは、一意の値を持つ列では最もよく機能しますが、複数の重複値を持つ列では最悪です。たとえば、年齢を保持する列には異なる値があるため、行を簡単に区別できます。
性別の記録に使用される列には「M」と「F」のみが含まれるため、この列のインデックス付けはほとんど役に立ちません (どの値が検索されても、約半分の行が取得されます)
3. 短いインデックスを使用します。文字列にインデックスを付ける場合は、プレフィックスの長さを指定する必要があり、これは可能な限り行う必要があります。
たとえば、CHAR(200) 列がある場合、ほとんどの値が最初の 10 文字または 20 文字内で一意である場合は、列全体にインデックスを付けないでください。最初の 10 文字または 20 文字にインデックスを付けると、インデックス領域を大幅に節約でき、クエリが高速になる可能性があります。インデックスが小さいほどディスク I/O が少なくなり、値が短いほど比較が速くなります。
さらに重要なのは、キー値が短い場合、インデックス キャッシュ内のブロックがより多くのキー値を保持できるため、MySQL もメモリ内により多くの値を保持できることです。これにより、インデックス内の大きなブロックを読み取らずに行を見つける可能性が高まります。
(もちろん、ある程度の常識が適用される必要があります。たとえば、このインデックスには多くの異なる値が存在しないため、列値の最初の文字のみによるインデックス付けはあまり有益ではありません。)
4左端のプレフィックスを使用します。 n 列インデックスを作成すると、実際には MySQL が使用できる n 個のインデックスが作成されます。
インデックスの左端の列セットを行の一致に使用できるため、複数列インデックスは複数のインデックスとして機能できます。このような列のセットは、左端のプレフィックスと呼ばれます。 (これは、列の最初の n 文字をインデックス値として使用する列のプレフィックスのインデックス付けとは異なります。)
5. インデックスを付けすぎないでください。 「インデックスは多ければ多いほど良い」とは考えないでください。何事にもインデックスを使用するのは間違いです。インデックスを追加するたびに追加のディスク領域が占有され、書き込み操作のパフォーマンスが低下します。これについてはすでに紹介しました。テーブルの内容が変更されると、インデックスを更新する必要があり、場合によっては再構築が必要になるため、インデックスの数が増えるほど時間がかかります。めったに使用されない、またはまったく使用されないインデックスがある場合、テーブルの変更が不必要に遅くなります。
さらに、MySQL は実行計画を生成するときに各インデックスを考慮する必要があり、これにも時間がかかります。冗長インデックスを作成すると、クエリ最適化のための作業がさらに増えます。インデックスが多すぎると、MySQL が使用する最適なインデックスを選択できなくなる可能性もあります。必要なインデックスのみを保持すると、クエリの最適化が容易になります。すでにインデックスが作成されているテーブルにインデックスを追加する場合は、追加するインデックスが既存の複数列インデックスの左端のインデックスであるかどうかを考慮する必要があります。その場合、このインデックスはすでに存在するため、わざわざ追加する必要はありません。
6. 列で行う比較の種類を検討します。インデックスは、「 < 」、「 < = 」、「 = 」、「 > = 」、「 >」および BETWEEN 演算で使用できます。パターンにリテラルの接頭辞がある場合、インデックスは LIKE 操作でも使用されます。列が他のタイプの操作 (STRCMP() など) にのみ使用される場合、その列にインデックスを付ける価値はありません。
5.3 btreeインデックスとハッシュインデックス
BTREEインデックスとHASHインデックスでは、=、<=>、IN、IS NULL、IS NOT NULL演算子を使用する場合、キー要素と定数値の比較関係が範囲条件に相当します。 。ハッシュ インデックスにはいくつかの追加機能があります。これらは = または <=> 演算子を使用した等価比較にのみ使用されます (ただし高速です)。 オプティマイザーは、ハッシュ インデックスを使用して ORDER BY 操作を高速化することはできません。
(このタイプのインデックスは、シーケンス内の次のエントリの検索には使用できません)。 MySQL は、2 つの値の間におよそ何行あるかを判断できません (これは、範囲オプティマイザがどのインデックスを使用するかを決定するために使用されます)。 MyISAM テーブルをハッシュ インデックス付き MEMORY テーブルに変更すると、一部のクエリが影響を受けます。行の検索にはキーワード全体のみを使用できます。 (B ツリー インデックスを使用すると、任意のキーの左端のプレフィックスを使用して行を検索できます)。
BTREE インデックスの場合、>、<、>=、<=、BETWEEN、!= または <>、または LIKE 'pattern' ('pattern' はワイルドカードで始まらない) 演算子を使用する場合、キー要素と定数値との比較関係が範囲条件に相当します。
「定数値」とは、クエリ文字列内の定数、同じ結合またはシステム テーブル内の列内の定数、相関関係のないサブクエリの結果、前のタイプの部分式のみで構成される式を指します
以下にいくつか挙げます。 WHERE 句に範囲条件を含むクエリの例:
次の範囲クエリは、Btree インデックスとハッシュ インデックスに適用できます。
SELECT * FROM t1WHEREkey_col = 1ORkey_col IN (15,18,20); 次の範囲クエリは、Btree インデックスに適用できます。
SELECT * FROM t1WHERE key_col > 列に特定の値を持つ行。インデックスを使用しない場合、MySQL はレコード 1 から開始して、関連する行が見つかるまでテーブル全体を読み取る必要があります。テーブルが大きいほど時間がかかります。テーブル内のクエリ対象のカラムにインデックスがある場合、MySQL はすべてのデータを調べることなく、データ ファイルの中央を検索する段階にすぐに到達できます。たとえば、テーブルに 1000 行がある場合、これは順次読み取りより少なくとも 100 倍高速になります。行の大部分にアクセスする必要がある場合は、その時点でディスクのシークが回避されるため、順次読み取りの方がはるかに高速であることに注意してください。
ほとんどの MySQL インデックス (PRIMARY KEY、UNIQUE、INDEX、および FULLTEXT) は B ツリーに保存されます。空間列タイプのインデックスのみが R ツリーを使用し、MEMORY テーブルはハッシュ インデックスもサポートします。
データベースがインデックスを使用する状況とデータベースがインデックスを使用しない状況の詳細な説明については、最適化の章の関連する章を参照してください。ここでは繰り返しません。

さらに関連記事を入手したい場合は、php 中国語 Web サイト (www.php.cn) に注目してください。