【初心者向け】MySQL のインデックスの基礎 - データベース検索を高速化する仕組み

MySQLにおけるINDEX、PRIMARY、UNIQUE、FULLTEXTの違い

MySQLでは、4種類の主要なインデックス类型があります。それぞれ異なる特性を持ち、状況に応じて使い分けることが重要です。

INDEX

• 基本的なインデックスです。
• データの検索速度を向上させる。
• 一意性やNULL値の許可など、制約は設けられない。

PRIMARY KEY

• 特殊なUNIQUEインデックスです。
• テーブル内に必ず1つ存在する。
• すべての行を一意に識別する。
• NULL値は許可されない。

UNIQUE

• 一意性の制約を設けるインデックスです。
• 同じ値を持つ行は2つ以上存在できない。

FULLTEXT

• テキストデータに対する全文検索を高速化するインデックスです。
• 部分一致検索や前方一致検索などの高度な検索が可能になる。

それぞれの違いをまとめた表

使い分けの例

• 氏名など、重複が許されない列にはPRIMARY KEYまたはUNIQUEインデックスを使用する。
• 商品名など、部分一致検索を行う可能性がある列にはFULLTEXTインデックスを使用する。
• その他の列には、検索速度向上目的でINDEXを使用する。

補足

• インデックスは、データの更新や削除を行う際にオーバーヘッドが発生するため、必要に応じて適切なインデックスを選択することが重要です。
• 複数のインデックスを組み合わせることで、より効率的な検索が可能になる場合もあります。

CREATE INDEX index_name ON table_name (column_name);

例：

CREATE INDEX idx_last_name ON customers (last_name);

CREATE TABLE table_name (
  column_name1 datatype,
  column_name2 datatype,
  PRIMARY KEY (column_name1)
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255)
);

CREATE UNIQUE INDEX index_name ON table_name (column_name);

CREATE UNIQUE INDEX idx_email ON customers (email);

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

CREATE FULLTEXT INDEX idx_product_description ON products (description);

複合インデックス

複数の列を組み合わせてインデックスを作成することもできます。

CREATE INDEX index_name ON table_name (column_name1, column_name2);

CREATE INDEX idx_city_state ON customers (city, state);

インデックスの削除

DROP INDEX index_name ON table_name;

DROP INDEX idx_last_name ON customers;

MySQLでインデックスを作成する他の方法

CREATE TABLEステートメント

テーブルを作成時に、PRIMARY KEYやUNIQUE制約を指定することで、インデックスを同時に作成することができます。

CREATE TABLE table_name (
  column_name1 datatype,
  column_name2 datatype,
  PRIMARY KEY (column_name1),
  UNIQUE (column_name2)
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255) UNIQUE
);

MySQL WorkbenchなどのGUIツールを使用すれば、GUI操作で簡単にインデックスを作成することができます。

データベース管理ツール

phpMyAdminなどのデータベース管理ツールを使用すれば、Webブラウザ上でインデックスを作成することができます。

それぞれの方法のメリットとデメリット