MySQL 目次：作成、追加、削除チュートリアル
マーカス·アレン
⚡ スマートサマリー
MySQL インデックスチュートリアルでは、インデックスがデータを高速にソートおよび検索する方法について説明します。インデックスは、1つ以上の列に基づいて作成されるソート済みのルックアップ構造です。CREATE INDEXコマンドでインデックスを追加し、SHOW INDEXESコマンドでインデックスを検査し、書き込み負荷の高いテーブルで読み取り負荷の高いテーブルではDROP INDEXコマンドでインデックスを削除します。
何が MySQL 索引？
An index in MySQL インデックスは、列の値を順序付けて格納するデータ構造であり、エンジンが高速に行を検索できるようにします。インデックスは、データのフィルタリングに最も頻繁に使用される列または複数の列に対して作成されます。インデックスは、アルファベット順にソートされたリストのようなものだと考えてください。ソートされていないリストの中から名前を探すよりも、ソートされたリストから名前を探す方がはるかに高速です。
インデックスにはトレードオフがあります。INSERTやUPDATEのたびにインデックスのメンテナンスが必要になるため、書き込み頻度の高いテーブルにインデックスを多く追加すると、全体的なパフォーマンスが低下する可能性があります。一般的に、書き込みよりも読み取り頻度の高いテーブルでは、WHERE、JOIN、ORDER BY句で使用される列にインデックスを作成するのが良いでしょう。
索引を使う理由とは？
動作の遅いシステムは誰にとっても好ましいものではありません。ほぼすべてのデータベースベースのアプリケーションにとって、高いパフォーマンスは最優先事項です。企業はクエリの高速化のためにハードウェアに多額の投資を行っていますが、ハードウェアだけで実現できる性能には限界があります。インデックスの最適化は、より安価で効果的な手段です。
応答時間が遅いのは、通常、行がディスク上に物理的な順序で格納されていることが原因です。インデックスがない場合、 MySQL 述語に一致する行を見つけるには、すべての行をスキャンする必要があります。これは「フルテーブルスキャン」と呼ばれます。インデックスを使用すると、 MySQL 一致する行に直接ジャンプすることで、Bツリー検索のクエリプランがO(n)からおおよそO(log n)に変わります。
構文: インデックスの作成
インデックスは2つの場所で定義できます。
- テーブル作成時。
- テーブルが既に存在する場合。
例：CREATE TABLE文とインラインでインデックスを作成する
myflixdb データベースでは、フルネーム列での検索が多数発生すると予想されます。以下のスクリプトは新しい members_indexed インデックス付きのテーブル full_names コラム。
CREATE TABLE `members_indexed` (
`membership_number` INT(11) NOT NULL AUTO_INCREMENT,
`full_names` VARCHAR(150) DEFAULT NULL,
`gender` VARCHAR(6) DEFAULT NULL,
`date_of_birth` DATE DEFAULT NULL,
`physical_address` VARCHAR(255) DEFAULT NULL,
`postal_address` VARCHAR(255) DEFAULT NULL,
`contact_number` VARCHAR(75) DEFAULT NULL,
`email` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`membership_number`),
INDEX (`full_names`)
) ENGINE = InnoDB;スクリプトを実行する MySQL 作業台に対して myflixdb データベース。
Refresh myflixdb 新しいものを見る members_indexed テーブル。 ザ・ full_names 列が下に表示されます インデックス ノード。
会員数が増えるにつれて、検索クエリは members_indexed WHEREとORDER BYを使用する full_names 元のクエリよりもはるかに高速です members 索引のないテーブル。
テーブルが既に存在する後にインデックスを追加する
既存のテーブルにインデックスが必要であることはよくあります。検索クエリが遅く、EXPLAIN プランで WHERE 句に含まれる列のフル テーブル スキャンが示されるからです。 CREATE INDEX このステートメントは、テーブルを再作成せずにインデックスを追加します。
CREATE INDEX `id_index` ON `table_name` (`column_name`);具体的な例 - 検索を高速化する title テーブルの movies テーブル：
CREATE INDEX `title_index` ON `movies` (`title`);フィルターするすべてのクエリ movies.title は新しいインデックスによってサポートされるようになりました。他の列でフィルタリングを行うクエリは、独自のインデックスがない限り、引き続きテーブルをスキャンします。
注意： クエリで常に同じ組み合わせに基づいてフィルタリングまたはソートを行う場合は、複数の列にまたがる複合インデックスを作成できます。順序が重要であり、先頭の列によってインデックスが使用できるかどうかが決まります。
テーブル上のインデックスを一覧表示する
SHOW INDEXES テーブルに定義されているすべてのインデックスを表示します。
SHOW INDEXES FROM `table_name`;例 — リストインデックス movies テーブル：
SHOW INDEXES FROM `movies`;ステートメントを実行する MySQL ワークベンチ に対して myflixdb 既存のインデックスと、それらがカバーする列を確認する。
注意： 主キーと外部キーは自動的にインデックス化されます MySQL各インデックスには固有の名前があり、それが対象とする列が一覧表示されます。
構文: Drop Index
DROP INDEX テーブルから既存のインデックスを削除します。これは、書き込み負荷の高いテーブルで、読み取り側で効果を発揮しなくなったインデックスによって速度が低下している場合に役立ちます。
DROP INDEX `index_id` ON `table_name`;具体的な例 - を落とす full_names インデックスから members_indexed:
DROP INDEX `full_names` ON `members_indexed`;の種類 MySQL インデックス
MySQL 複数のインデックスタイプをサポートしており、それぞれ異なるワークロードに適しています。
| タイプ | 目的 |
|---|---|
| 主キー | 一意の行識別子。InnoDBのテーブルデータとクラスタリングされます。 |
| ユニーク | インデックスとして機能しながら、一意性を確保します。 |
| インデックス（Bツリー） | 範囲クエリおよび等価性検索に使用されるデフォルトのセカンダリインデックス。 |
| フルテキスト | MATCH … AGAINST を使用した自然言語テキスト検索に最適化されています。 |
| 空間的な | POINTやPOLYGONなどのGISデータタイプ用のRツリーインデックス。 |
| ハッシュ | 定数時間で等価性検索を実行します。MEMORYストレージエンジンで使用されます。 |
| 複合（複数列） | 複数の列を1つのインデックスに結合します。左端が先頭となるルールに従います。 |
のベスト プラクティス MySQL インデックス
以下の習慣を実践することで、インデックスは有用なものとなり、単なる重荷になるのを防ぐことができます。
- クエリパターンに対するインデックスであり、列名に対するインデックスではありません。 実際のWHERE、JOIN、ORDER BY句に一致するインデックスを追加するのであって、「重要そうに見えるすべての列」を追加するのではありません。
- 複合指数の順序を確認してください。 インデックスを使用するには、先頭の列がクエリに含まれている必要があります。
- 重複するインデックスを避ける： 複合インデックスの先頭の接頭辞は、その接頭辞に対する単一列の検索を既にカバーしています。
- EXPLAINで検査する： プランナーが実際に新しいインデックスを選択していることを確認してください。
- 使用されていないインデックスを削除します。 つかいます
sys.schema_unused_indexesin MySQL 5.7以降では、何も読み取られないインデックスを検出できます。 - データ型を一致させる: WHERE句でVARCHAR型の列を数値と比較する場合、暗黙的な型変換が行われるため、インデックスは使用できません。
