MySQLインデックスの使用に関するヒントと注意事項

1. インデックスの役割

一般的なアプリケーション システムでは、読み取りと書き込みの比率は約 10:1 であり、挿入操作と一般的な更新操作でパフォーマンス上の問題が発生することはほとんどありません。最も一般的で最も問題となる操作は、一部の複雑なクエリ操作であるため、クエリ ステートメントの最適化が最優先事項であることは明らかです。

データ量やアクセス数が多くない場合、MySQL のアクセスは非常に高速であり、インデックスを追加するかどうかはアクセスにほとんど影響しません。ただし、データ量やアクセス数が急増すると、MySQL の速度が低下したり、クラッシュしたりすることがあります。この場合、SQL の最適化を検討する必要があります。データベースに適切かつ適切なインデックスを確立することは、MySQL を最適化するための重要な手段です。

インデックスの目的は、クエリの効率を向上させることです。これは辞書に例えることができます。「mysql」という単語を検索する場合は、文字 m を見つけ、次に文字 y を下から下まで見つけ、次に sql の残りの部分を見つける必要があります。索引がない場合、必要な単語を見つけるためにすべての単語を調べる必要がある場合があります。辞書以外にも、駅の電車の時刻表や本のカタログなど、生活のあらゆるところに索引の例が見られます。それらの原理は同じで、取得したいデータの範囲を継続的に絞り込むことで最終的な目的の結果をフィルタリングし、同時にランダムなイベントを連続したイベントに変換することです。つまり、常に同じ検索方法を通じてデータをロックします。

インデックスを作成するときは、SQL クエリで使用される列を考慮し、それらの列に対して 1 つ以上のインデックスを作成する必要があります。実際、インデックスは、主キーまたはインデックス フィールドと、各レコードを実際のテーブルにポイントするポインターを格納するテーブルでもあります。インデックスはデータベース ユーザーには見えず、クエリを高速化するためにのみ使用されます。データベース検索エンジンはインデックスを使用してレコードをすばやく見つけます。

インデックス付きのテーブルでは、INSERT ステートメントと UPDATE ステートメントの実行に時間がかかりますが、SELECT ステートメントはより高速に実行されます。これは、挿入または更新を実行するときに、データベースでもインデックス値を挿入または更新する必要があるためです。

2. インデックスの作成と削除

インデックスの種類:

1. UNIQUE（ユニークインデックス）：同じ値は出現できず、NULL値は許可されます
2. INDEX（通常のインデックス）：同じインデックスコンテンツを表示できます
3. PROMARY KEY（主キーインデックス）：重複した値は許可されません
4. 全文インデックス: 値内の単語をターゲットにするために使用できますが、効率は十分ではありません。
5. 複合インデックス：本質的には、複数のフィールドが1つのインデックスに組み込まれ、列の値の組み合わせは一意である必要があります。

（1）ALTER TABLE文を使用して、

テーブルの作成後に適用されます。

ALTER TABLE テーブル名 ADD インデックスタイプ (ユニーク、主キー、フルテキスト、インデックス) [インデックス名] (フィールド名)

//通常のインデックス alter table table_name add index index_name (column_list);
// ユニークインデックス alter table table_name add unique (column_list);
//主キー インデックス alter table table_name add primary key (column_list);

ALTER TABLE を使用すると、通常のインデックス、UNIQUE インデックス、PRIMARY KEY インデックスの 3 つのインデックス形式を作成できます。table_name はインデックスを追加するテーブルの名前、column_list はインデックスを作成する列を示します。複数の列がある場合は、列はカンマで区切られます。インデックス名 index_name はオプションです。デフォルトでは、MySQL は最初のインデックス列に基づいて名前を割り当てます。さらに、ALTER TABLE を使用すると、1 つのステートメントで複数のテーブルを変更できるため、複数のインデックスを同時に作成できます。

（２）CREATE INDEX文を使用してテーブルにインデックスを追加する

CREATE INDEX は、テーブルに共通インデックスまたは UNIQUE インデックスを追加するために使用でき、テーブルの構築時にインデックスを作成するために使用できます。

CREATE INDEX index_name ON table_name(username(length));

CHAR または VARCHAR 型の場合、長さはフィールドの実際の長さより短くてもかまいません。BLOB または TEXT 型の場合は、長さを指定する必要があります。

//create ではこれら 2 つのインデックスのみを追加できます。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name、column_list は、ALTER TABLE ステートメントの場合と同じ意味を持ちます。インデックス名はオプションではありません。また、CREATE INDEX ステートメントを使用して PRIMARY KEY インデックスを作成することはできません。

（３）インデックスを削除する

インデックスの削除は、ALTER TABLE または DROP INDEX ステートメントを使用して実行できます。 DROP INDEX は、次の形式で ALTER TABLE 内の単一のステートメントとして処理できます。

table_name のインデックス index_name を削除します。

テーブル table_name を変更し、インデックス index_name を削除します。

テーブル table_name を変更し、主キーを削除します。

前の 2 つのステートメントでは、table_name 内のインデックス index_name が削除されます。最後のステートメントでは、PRIMARY KEY インデックスを削除するためにのみ使用されます。テーブルには 1 つの PRIMARY KEY インデックスしか設定できないため、インデックス名を指定する必要はありません。 PRIMARY KEY インデックスが作成されていないが、テーブルに 1 つ以上の UNIQUE インデックスがある場合、MySQL は最初の UNIQUE インデックスを削除します。

テーブルから列を削除すると、インデックスに影響します。複数の列を持つインデックスの場合、列の 1 つを削除すると、その列もインデックスから削除されます。インデックスを構成するすべての列を削除すると、インデックス全体が削除されます。

（４）複合索引とプレフィックス索引

ここで指摘しておくべきことは、複合インデックスとプレフィックス インデックスはインデックスの種類ではなく、インデックス作成手法の名前であるということです。わかりやすく説明するために、次のようなデモ テーブルを作成します。

テーブル USER_DEMO を作成する
（
  ID int not null auto_increment コメント '主キー',
  LOGIN_NAME varchar(100) NULLでないコメント 'ログイン名',
  PASSWORD varchar(100) NULLでないコメント 'パスワード',
  CITY varchar(30) NULLでないコメント 'City',
  AGE int NULLでないコメント '年齢',
  SEX int not null コメント '性別 (0: 女性 1: 男性)',
  主キー (ID)
);

MySQL の効率をさらに高めるには、複合インデックスの作成、つまり LOGIN_NAME、CITY、AGE を 1 つのインデックスに組み込むことを検討してください。

テーブルを作成するとき、LOGIN_NAME の長さは 100 です。名前の長さは通常 16 を超えないため、ここでは 16 が使用されます。これにより、インデックス クエリが高速化され、インデックス ファイルのサイズが削減され、INSERT および UPDATE の更新速度が向上します。

LOGIN_NAME、CITY、AGE のそれぞれに単一列インデックスを作成して、テーブルに 3 つの単一列インデックスがある場合、クエリの効率は結合インデックスの場合とは大きく異なり、結合インデックスよりもはるかに低くなる可能性があります。現時点では 3 つのインデックスがありますが、MySQL は最も効率的と思われる単一列のインデックスのみを使用できます。他の 2 つは使用されない、つまり、依然として完全なテーブル スキャン プロセスであることを意味します。

このような複合インデックスを確立することは、それぞれ次の 3 つの複合インデックスを確立することと同等です。

ログイン名、都市、年齢
ログイン名、都市
ログイン名

CITY、AGE などの結合インデックスがないのはなぜですか?これは、MySQL 複合インデックスの「左端のプレフィックス」の結果によるものです。簡単に理解すると、組み合わせは左端の列から始まり、これら 3 つの列を含むすべてのクエリがこの結合インデックスを使用するわけではないということです。つまり、name_city_age(LOGIN_NAME(16),CITY,AGE) は左から右にインデックス付けされます。左端のインデックスがない場合、MySQL はインデックス クエリを実行しません。

インデックス列が長すぎると、この列にインデックスを作成するときに大きなインデックス ファイルが生成され、操作が不便になります。プレフィックス インデックスを使用してインデックスを作成できます。プレフィックス インデックスは適切なポイントで制御する必要があり、黄金値 0.31 内で制御できます (この値より大きい場合は作成できます)。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- この値が 0.31 より大きい場合は、プレフィックス インデックスを作成し、Distinct を使用して重複を削除できます。 ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- プレフィックス インデックス SQL を追加し、10 で name のインデックスを作成します。これにより、インデックス ファイルのサイズが縮小され、インデックス クエリが高速化されます。

3. インデックスの使用と注意事項

EXPLAIN は、開発者が SQL の問題を分析するのに役立ちます。EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。これにより、より適切なインデックスを選択し、より最適化されたクエリ ステートメントを記述できるようになります。

これを使用するには、select ステートメントの前に Explain を追加するだけです。

select * from user where id=1; について説明します。

インデックスを使用しない次の SQL ステートメントは避けるようにしてください。

SELECT `sname` FROM `stu` W​​HERE `age`+10=30;-- インデックス列はすべて計算に関係するため、インデックスは使用されません。SELECT `sname` FROM `stu` W​​HERE LEFT(`date`,4) <1990; -- 関数演算が使用されるため、インデックスは使用されません。原理は上記と同じです。SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- インデックスを使用します。SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- インデックスを使用しないでください。-- 正規表現ではインデックスを使用しないので、簡単に理解できるはずです。では、SQL で regexp キーワードがわかりにくいのはなぜでしょうか。-- 数字との文字列比較ではインデックスを使用しません。
テーブル `a` を作成します (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- インデックスを使用します。 EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- インデックスを使用しません。 select * from dept where dname='xxx' or loc='xx' or deptno=45 -- 条件に or がある場合、条件にインデックスがあっても使用されません。つまり、使用されるすべてのフィールドにインデックスを付ける必要があります。 or キーワードの使用は避けることをお勧めします。

-- MySQL がフルテーブルスキャンの方がインデックスよりも高速であると見積もった場合、インデックスは使用されません。

インデックスには多くの利点がありますが、インデックスを過度に使用すると逆の問題が発生する可能性があります。インデックスには欠点もあります。

1. インデックスによりクエリ速度は大幅に向上しますが、INSERT、UPDATE、DELETE などのテーブル更新の速度も低下します。テーブルを更新すると、MySQL はデータを保存するだけでなく、インデックス ファイルも保存します。
2. インデックスを構築すると、インデックス ファイル用のディスク領域が消費されます。通常、この問題は深刻ではありませんが、大きなテーブルに複数の結合インデックスを作成すると、インデックス ファイルが非常に広くなります。

インデックス作成は、効率を向上させる方法の 1 つにすぎません。MySQL に大量のデータが含まれるテーブルがある場合は、最適なインデックスを作成する方法やクエリ ステートメントを最適化する方法を時間をかけて調査する必要があります。

インデックスを使用する際のヒントがいくつかあります。

1. インデックスにはNULLの列は含まれません

列に NULL 値が含まれている限り、その列はインデックスに含まれません。複合インデックス内の 1 つの列に NULL 値が含まれている限り、その列はこの複合インデックスに対して無効になります。

2. 短いインデックスを使用する

リスト列にインデックスを付ける場合、可能であればプレフィックスの長さを指定する必要があります。たとえば、char(255) 列があり、最初の 10 文字または 20 文字の範囲内でほとんどの値が一意である場合は、列全体にインデックスを付けないでください。短いインデックスを使用すると、クエリ速度が向上するだけでなく、ディスク領域と I/O 操作も節約できます。

3. インデックス列の並べ替え

MySQL クエリは 1 つのインデックスのみを使用するため、where 句でインデックスがすでに使用されている場合、order by 句の列ではインデックスは使用されません。したがって、データベースのデフォルトのソートで要件を満たすことができる場合は、ソート操作を使用しないでください。複数の列のソートを含めないようにしてください。必要な場合は、これらの列の複合インデックスを作成するのが最適です。

4.Like文の操作

一般的に言えば、like 操作の使用は推奨されません。使用する必要がある場合は、正しい使用方法に注意してください。 '%aaa%' のようにインデックスは使用されませんが、 'aaa%' のようにインデックスを使用できます。

5. 列に対して操作を行わない

6. NOT IN、<>、! は使用しないでください。 = 演算ですが、<、<=、=、>、>=、BETWEEN、IN ではインデックスを使用できます。

7. 頻繁に選択されるフィールドにインデックスを作成する必要があります。

これらの列がほとんど使用されない場合は、インデックスの有無によってクエリ速度に大きな変化が生じないためです。逆に、インデックスを追加すると、システムのメンテナンス速度が低下し、必要なスペースが増加します。

8. インデックスは、比較的一意の値を持つフィールドに作成する必要があります。

9. テキスト、イメージ、ビット データ型として定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に多いか、値が非常に少ないためです。

10. where および join に表示される列にはインデックスを付ける必要があります。

11. where クエリ条件に不等号がある場合 (where column != ...)、MySQL はインデックスを使用できません。

12. where 句のクエリ条件で関数が使用されている場合 (where DAY(column)=… など)、MySQL はインデックスを使用できません。

13. 結合操作（複数のテーブルからデータを抽出する必要がある場合）では、MySQL は主キーと外部キーのデータ型が同じ場合にのみインデックスを使用できます。そうでない場合は、インデックスが確立されていても使用されません。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。