MySQL のインデックス作成と最適化

1. インデックスのデータ型を選択します

MySQL は、データを保存するための適切なデータ型を選択することがパフォーマンスに大きな影響を与えます。一般的に、次のガイドラインに従うことができます:

(1) 通常、データ型が小さいほど優れています。通常、データ型が小さいほど、必要なディスク、メモリ、CPU キャッシュのスペースが少なくなり、処理が速くなります。
(2) 単純なデータ型の方が優れています: 文字列の比較がより複雑であるため、整数データは文字よりも処理オーバーヘッドが少なくなります。 MySQL では、時刻を格納するには文字列の代わりに組み込みの日付と時刻のデータ型を使用し、IP アドレスを格納するには整数データ型を使用する必要があります。
(3) NULL を避けるようにしてください。NULL を格納する場合を除き、列は NOT NULL として指定する必要があります。 MySQL では、NULL 値を含むカラムはインデックス、インデックス統計、比較操作を複雑にするため、クエリを最適化することが困難です。 null 値は 0、特別な値、または空の文字列に置き換える必要があります。

1.1. 識別子の選択
適切な識別子の選択は非常に重要です。選択するときは、ストレージ タイプだけでなく、MySQL が操作と比較を実行する方法も考慮する必要があります。データ型を選択したら、関連するすべてのテーブルが同じデータ型を使用するようにする必要があります。
(1) 整数: より高速に処理でき、AUTO_INCREMENT に設定できるため、通常は識別子として最適な選択です。

(2) 文字列: 文字列を識別子として使用することは避けてください。文字列はより多くのスペースを消費し、処理が遅くなります。また、一般に、文字列はランダムであるため、インデックス内の文字列の位置もランダムであり、ページ分割、ディスクへのランダム アクセス、およびクラスター化インデックスの分割 (クラスター化インデックスを使用するストレージ エンジンの場合) が発生する可能性があります。

2. インデックスの概要
DBMS にとって、インデックスは最適化にとって最も重要な要素です。データ量が少ない場合、適切なインデックスがないことによる影響は大きくありませんが、データ量が増加すると、パフォーマンスが急激に低下します。
複数のカラムにインデックスを付ける場合 (結合インデックス)、MySQL はインデックスの左端のプレフィックスに対してのみ効果的な検索を実行できます。例:
複合インデックス it1c1c2(c1,c2) があり、クエリ ステートメント select * from t1 where c1=1 および c2=2 がこのインデックスを使用できるとします。クエリ ステートメント select * from t1 where c1=1 でも、このインデックスを使用できます。ただし、クエリ ステートメント select * from t1 where c2=2 では、このインデックスを使用できません。これは、結合インデックスの先頭列がないためです。つまり、検索に列 c2 を使用する場合は、c1 が特定の値に等しい必要があります。

2.1. インデックスの種類
インデックスはサーバー層ではなくストレージエンジンに実装されます。したがって、各ストレージ エンジンのインデックスは必ずしも完全に同じであるとは限らず、すべてのストレージ エンジンがすべてのインデックス タイプをサポートしているわけではありません。
2.1.1、B ツリー インデックス
次のテーブルがあるとします。

CREATE TABLE People (
     last_name varchar(50)    not null,
     first_name varchar(50)    not   null,
     dob        date           not null,
     gender     enum('m', 'f') not   null,
     key(last_name, first_name, dob)
);

そのインデックスには、テーブル内の各行の last_name、first_name、および dob 列が含まれています。その構造はおおよそ次のとおりです:

インデックスに格納される値は、インデックス列の順序で配置されます。 B ツリー インデックスを使用すると、すべてのキーワード、キーワード範囲、およびキーワード プレフィックスをクエリできます。もちろん、インデックスを使用する場合は、インデックスの左端のプレフィックスでクエリを実行する必要があります。
(1) 完全な値と一致する: インデックス内のすべての列に特定の値を指定します。たとえば、上の図のインデックスは、1960 年 1 月 1 日に生まれたキューバ アレンを見つけるのに役立ちます。
(2) 一番左の接頭辞と一致する: インデックスを使用して、インデックスの最初の列のみを使用して、姓が Allen である人を検索できます。
(3) 列の接頭辞を照合する: たとえば、インデックスを使用して、姓が J で始まる人を検索できます。これでは、インデックスの最初の列のみが使用されます。
(4) 値の範囲を一致させる: インデックスを使用して、姓が Allen と Barrymore の間にある人を検索できます。インデックスの最初の列のみを使用します。
(5) ある部分を正確に一致させ、別の部分の範囲を一致させる: インデックスを使用して、姓が Allen で名前が文字 K で始まる人を検索できます。
(6) インデックスのみのクエリ: クエリされた列がすべてインデックス内にある場合、タプルの値を読み取る必要はありません。
B ツリー内のノードは順番に保存されるため、インデックスを使用して検索 (特定の値を見つける) したり、クエリ結果を ORDER BY したりすることができます。もちろん、B ツリー インデックスの使用にはいくつかの制限があります:
(1) クエリはインデックスの左端の列から開始する必要があります。この点については何度も言及されてきました。たとえば、インデックスを使用して、特定の日に生まれた人を検索することはできません。
(2) インデックス列はスキップできません。たとえば、インデックスを使用して、姓がスミスで特定の日に生まれた人を検索することはできません。
(3) ストレージ エンジンはインデックス内の範囲条件の右側の列を使用できません。たとえば、クエリ ステートメントが WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23' の場合、LIKE は範囲クエリであるため、クエリはインデックス内の最初の 2 列のみを使用します。 。

上記は Mysql のインデックス作成と最適化の内容です。さらに関連する内容については、PHP 中国語 Web サイト (www.php.cn) に注目してください。