MySQL - インデックス

概要

インデックス (Index) は、テーブルから特定の行を高速に検索するためのデータ構造である。

インデックスがない場合、MySQLはテーブル全体をスキャンする必要があるが (O(n))、インデックスを使用すると対数時間 (O(log n)) で検索が可能になる。
これにより、大量のデータを持つテーブルでも高速なクエリ実行が可能となる。

インデックスは、WHERE 句、JOIN 句、ORDER BY 句、GROUP BY 句で使用される列に対して作成すると効果的である。
一方で、インデックスは追加のディスク容量を消費し、INSERT、UPDATE、DELETE 操作時にインデックスの更新が必要となるため、更新コストが増加する。

MySQLでは、B-Treeインデックス (デフォルト)、UNIQUEインデックス、PRIMARY KEY、FULLTEXTインデックス、SPATIALインデックス、ハッシュインデックス (MEMORYエンジン) といった複数のインデックスタイプが利用可能である。

MySQL 8.0以降では、降順インデックス (DESC)、不可視インデックス (INVISIBLE)、関数インデックス、Multi-Valued Index (JSON配列用) といった新機能が追加されている。

インデックスの効果的な使用には、選択性 (Cardinality) の高い列を選び、適切な列順序で複合インデックスを作成し、定期的に統計情報を更新することが重要である。

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

CREATE INDEX構文

インデックスは、CREATE INDEX 文を使用して作成する。

 # 基本的なインデックスの作成
 CREATE INDEX idx_name ON table_name (column_name);
 
 # UNIQUEインデックスの作成
 CREATE UNIQUE INDEX idx_unique_email ON users (email);
 
 # 複合インデックスの作成
 CREATE INDEX idx_dept_salary ON employees (department_id, salary);
 
 # 降順インデックス (MySQL 8.0以降)
 CREATE INDEX idx_created_desc ON posts (created_at DESC);
 
 # プリフィックスインデックス (文字列の先頭N文字)
 CREATE INDEX idx_name_prefix ON users (name(20));
 
 # FULLTEXTインデックスの作成
 CREATE FULLTEXT INDEX idx_fulltext_content ON articles (content);
 
 # SPATIALインデックスの作成
 CREATE SPATIAL INDEX idx_location ON places (coordinates);

ALTER TABLE ADD INDEX構文

既存のテーブルにインデックスを追加する場合は、ALTER TABLE ... ADD INDEX 構文を使用する。

 # 基本的なインデックスの追加
 ALTER TABLE employees ADD INDEX idx_department (department_id);
 
 # ユニークインデックスの追加
 ALTER TABLE users ADD UNIQUE INDEX idx_unique_username (username);
 
 # 複数のインデックスを同時に追加
 ALTER TABLE products
    ADD INDEX idx_category (category_id),
    ADD INDEX idx_price (price),
    ADD INDEX idx_status (status);
 
 # 主キーの追加
 ALTER TABLE users ADD PRIMARY KEY (id);
 
 # 複合インデックスの追加
 ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);

詳細は MySQL - ALTER TABLE のページを参照すること。

DROP INDEX構文

インデックスを削除する場合は、DROP INDEX 文または ALTER TABLE ... DROP INDEX 構文を使用する。

 # DROP INDEX文による削除
 DROP INDEX idx_name ON table_name;
 
 # ALTER TABLE ... DROP INDEX構文による削除
 ALTER TABLE table_name DROP INDEX idx_name;
 
 # 主キーの削除
 ALTER TABLE table_name DROP PRIMARY KEY;
 
 # 複数のインデックスを同時に削除
 ALTER TABLE products
    DROP INDEX idx_category,
    DROP INDEX idx_price;

インデックス情報の確認

テーブルのインデックス情報は、SHOW INDEX 文で確認できる。

 # インデックス情報を表示
 SHOW INDEX FROM table_name;
 
 # 詳細な情報を確認
 SHOW INDEX FROM employees\G

SHOW INDEX の主な出力列は以下の通りである。

SHOW INDEXの主要カラム

カラム	説明
Table	テーブル名
Non_unique	0 (UNIQUE) または 1 (非UNIQUE)
Key_name	インデックス名
Seq_in_index	複合インデックス内での列の順序
Column_name	列名
Collation	ソート順序 (A: 昇順、D: 降順、NULL: ソートなし)
Cardinality	インデックス内の一意な値の推定数
Index_type	インデックスのタイプ (BTREE, HASH, FULLTEXT, SPATIAL)
Visible	インデックスの可視性 (YES: 可視、NO: 不可視)

 # インデックス情報の確認例
 SHOW INDEX FROM employees;

 # 出力例:
 # +--------+------------+---------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
 # | Table  | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
 # +--------+------------+---------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
 # | employees | 0       | PRIMARY |            1 | id          | A         |       10000 |     NULL | NULL   |      | BTREE      |
 # | employees | 1       | idx_dept|            1 | dept_id     | A         |          50 |     NULL | NULL   | YES  | BTREE      |
 # +--------+------------+---------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

インデックスの種類

B-Treeインデックス

B-Treeインデックスは、MySQLのデフォルトのインデックスタイプである。

B-Treeインデックスは、等価検索 (=)、範囲検索 (<, >, <=, >=, BETWEEN)、前方一致LIKE ('prefix%')、IS NULL 検索に対応している。
InnoDBとMyISAMの両方で使用可能であり、ほとんどのユースケースに適している。

 # B-Treeインデックスの作成 (デフォルト)
 CREATE INDEX idx_name ON employees (name);
 
 # 等価検索
 SELECT * FROM employees WHERE name = 'Alice';
 
 # 範囲検索
 SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
 
 # 前方一致LIKE
 SELECT * FROM employees WHERE name LIKE 'Ali%';
 
 # IS NULL検索
 SELECT * FROM employees WHERE manager_id IS NULL;

B-Treeインデックスは、以下の場合には使用されない。

• 後方一致LIKE ('%suffix')
• 中間一致LIKE ('%pattern%')
• 関数適用 (MySQL 8.0以前) (UPPER(name), YEAR(date) 等)
• 型不一致 (文字列列に数値で検索等)

 # インデックスが使用されない例
 
 SELECT * FROM employees WHERE name LIKE '%son';  # 後方一致
 SELECT * FROM employees WHERE UPPER(name) = 'ALICE';  # 関数適用 (8.0以前)
 SELECT * FROM employees WHERE age = '30';  # 型不一致 (ageがINT型の場合)

UNIQUEインデックス

UNIQUEインデックスは、列の値が一意であることを保証するインデックスである。

NULLは複数許可される。(SQL標準に従い、NULL = NULL は FALSE のため)

 # UNIQUEインデックスの作成
 CREATE UNIQUE INDEX idx_unique_email ON users (email);
 
 # ALTER TABLEでの作成
 ALTER TABLE users ADD UNIQUE INDEX idx_unique_username (username);
 
 # 複合UNIQUEインデックス
 CREATE UNIQUE INDEX idx_unique_user_product ON favorites (user_id, product_id);

UNIQUEインデックスに重複した値を挿入しようとすると、エラーが発生する。

 # 重複挿入時のエラー
 INSERT INTO users (email) VALUES ('test@example.com');
 # OK
 
 INSERT INTO users (email) VALUES ('test@example.com');
 # ERROR 1062 (23000): Duplicate entry 'test@example.com' for key 'idx_unique_email'
 
 # NULLは複数許可される
 INSERT INTO users (email) VALUES (NULL);
 INSERT INTO users (email) VALUES (NULL);
 # OK (NULLは複数挿入可能)

PRIMARY KEY

PRIMARY KEYは、テーブルの各行を一意に識別するための特別なインデックスである。

InnoDBでは、PRIMARY KEYはクラスタインデックスとして実装される。
これは、テーブルのデータがPRIMARY KEYの順序で物理的に格納されることを意味する。

InnoDBのセカンダリインデックス (PRIMARY KEY以外のインデックス) には、PRIMARY KEYの値が自動的に含まれる。
そのため、PRIMARY KEYは可能な限り小さくすることが推奨される。

 # 主キーの作成 (テーブル作成時)
 CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
 );
 
 # 複合PRIMARY KEY
 CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
 );
 
 # 既存テーブルにPRIMARY KEYを追加
 ALTER TABLE users ADD PRIMARY KEY (id);
 
 # AUTO_INCREMENTとの組み合わせ
 CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
 );

PRIMARY KEYには以下に示す制約がある。

• テーブルあたり1つのみ
• NULLを許可しない
• 値は一意である必要がある

FULLTEXTインデックス

FULLTEXTインデックスは、全文検索を高速化するためのインデックスである。

CHAR、VARCHAR、TEXT 型の列に対して作成でき、MATCH ... AGAINST 構文で検索を行う。
InnoDBとMyISAMの両方で使用可能である。

 # FULLTEXTインデックスの作成
 CREATE FULLTEXT INDEX idx_fulltext_content ON articles (content);
 
 # 複数列に対するFULLTEXTインデックス
 CREATE FULLTEXT INDEX idx_fulltext_title_content ON articles (title, content);
 
 # 自然言語検索 (デフォルト)
 SELECT * FROM articles
    WHERE MATCH(content) AGAINST('MySQL インデックス');
 
 # ブール検索
 SELECT * FROM articles
    WHERE MATCH(content) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);
 
 # クエリ拡張検索
 SELECT * FROM articles
    WHERE MATCH(content) AGAINST('database' WITH QUERY EXPANSION);

FULLTEXTインデックスの検索モードは以下の通りである。

• NATURAL LANGUAGE MODE (デフォルト)

  自然言語検索。単語の出現頻度に基づいてランキング

• BOOLEAN MODE

  ブール検索。+、-、*、"" 等の演算子を使用可能

• WITH QUERY EXPANSION

  クエリ拡張検索

  検索結果から関連語を抽出して再検索

日本語の全文検索には、ngramパーサーの使用が推奨される。

 # ngramパーサーを使用したFULLTEXTインデックス (日本語対応)
 CREATE FULLTEXT INDEX idx_fulltext_content ON articles (content) WITH PARSER ngram;
 
 # ngram_token_sizeの設定 (デフォルト : 2)
 SET GLOBAL ngram_token_size = 2;
 
 # 日本語の検索例
 SELECT * FROM articles
    WHERE MATCH(content) AGAINST('データベース');

SPATIALインデックス

SPATIALインデックスは、空間データ (GEOMETRY, POINT, LINESTRING, POLYGON型) を高速に検索するためのインデックスである。

R-Treeアルゴリズムを使用し、地理情報システム (GIS) アプリケーションで利用される。

 # SPATIALインデックスの作成
 CREATE TABLE places (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT NOT NULL,
    SPATIAL INDEX idx_location (coordinates)
 );
 
 # 既存テーブルにSPATIALインデックスを追加
 ALTER TABLE places ADD SPATIAL INDEX idx_location (coordinates);
 
 # 空間検索の例
 SELECT name FROM places
    WHERE ST_Contains(
       ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
       coordinates
    );

SPATIALインデックスには以下の制約がある。

• 空間データ型の列のみに作成可能
• 列は NOT NULL である必要がある
• InnoDBではMySQL 5.7以降で使用可能

ハッシュインデックス

ハッシュインデックスは、MEMORYストレージエンジンで使用可能なインデックスタイプである。

等価検索 (=) のみに対応し、範囲検索には使用できない。
等価検索では非常に高速だが、用途は限定的である。

 # MEMORYエンジンテーブルでのハッシュインデックス
 CREATE TABLE cache (
    key_name VARCHAR(100),
    value TEXT,
    INDEX USING HASH (key_name)
 ) ENGINE=MEMORY;
 
 # 等価検索 (高速)
 SELECT value FROM cache WHERE key_name = 'config_key';
 
 # 範囲検索 (インデックス使用不可)
 SELECT value FROM cache WHERE key_name > 'a';

InnoDBには、適応ハッシュインデックス (Adaptive Hash Index) という自動的なハッシュインデックス機能がある。
これはユーザが作成するものではなく、InnoDBが頻繁にアクセスされるB-Treeインデックスページに対して自動的に作成する。

複合インデックス

複合インデックスの基本

複合インデックスは、複数の列を組み合わせたインデックスである。

最大16カラムまで指定可能であり、列の順序が非常に重要である。

 # 複合インデックスの作成
 CREATE INDEX idx_dept_salary ON employees (department_id, salary);
 
 # 3列の複合インデックス
 CREATE INDEX idx_user_date_status ON orders (user_id, order_date, status);
 
 # 複合UNIQUEインデックス
 CREATE UNIQUE INDEX idx_unique_user_product ON favorites (user_id, product_id);

左端プリフィックスルール

複合インデックスは、左端プリフィックスルール (最左一致原則) に従う。

インデックス (col1, col2, col3) は、以下のクエリパターンで使用される。

• col1
• col1, col2
• col1, col2, col3

以下のパターンでは使用されない。

• col2 : (col1がない)
• col3 : (col1がない)
• col2, col3 : (col1がない)

 # インデックス: (department_id, salary, hire_date)
 CREATE INDEX idx_dept_sal_date ON employees (department_id, salary, hire_date);
 
 # インデックスが使用される例
 SELECT * FROM employees WHERE department_id = 10;
 SELECT * FROM employees WHERE department_id = 10 AND salary > 50000;
 SELECT * FROM employees WHERE department_id = 10 AND salary > 50000 AND hire_date > '2020-01-01';
 
 # インデックスが使用されない例
 SELECT * FROM employees WHERE salary > 50000;  # department_idがない
 SELECT * FROM employees WHERE hire_date > '2020-01-01';  # department_idがない
 SELECT * FROM employees WHERE salary > 50000 AND hire_date > '2020-01-01';  # department_idがない

カバリングインデックス

カバリングインデックスは、クエリに必要な全ての列がインデックスに含まれている状態である。

この場合、MySQLはテーブルデータにアクセスせず、インデックスだけでクエリを完結できるため、非常に高速である。
EXPLAIN の Extra 列に Using index と表示される。

 # カバリングインデックスの例
 CREATE INDEX idx_dept_salary ON employees (department_id, salary);
 
 # このクエリはインデックスだけで完結 (カバリングインデックス)
 SELECT department_id, salary FROM employees WHERE department_id = 10;
 # EXPLAIN: Extra = Using index
 
 # このクエリはテーブルアクセスが必要 (nameがインデックスに含まれない)
 SELECT department_id, salary, name FROM employees WHERE department_id = 10;
 # EXPLAIN: Extra = NULL (テーブルアクセスあり)

InnoDBでは、セカンダリインデックスに PRIMARY KEYの値が自動的に含まれるため、PRIMARY KEYの列もカバリング対象となる。

 # PRIMARY KEY: id
 # インデックス: (department_id, salary)
 CREATE TABLE employees (
    id INT PRIMARY KEY,
    department_id INT,
    salary DECIMAL(10, 2),
    name VARCHAR(100),
    INDEX idx_dept_salary (department_id, salary)
 );
 
 # このクエリもカバリングインデックス (idが主キーのため)
 SELECT id, department_id, salary FROM employees WHERE department_id = 10;
 # EXPLAIN: Extra = Using index

複合インデックスの列順序

複合インデックスの列順序は、パフォーマンスに大きく影響する。

一般的な列順序の指針は以下の通りである。

• 等価条件 (=) の列を最初に配置
• 選択性の高い列を優先
• 範囲条件 (<, >, BETWEEN) の列を後に配置
• ORDER BY / GROUP BY の列を考慮

 # クエリパターン: WHERE department_id = ? AND salary > ? ORDER BY hire_date
 
 # 推奨順序: (department_id, hire_date, salary)
 # - department_id: 等価条件
 # - hire_date: ORDER BY
 # - salary: 範囲条件
 CREATE INDEX idx_dept_date_sal ON employees (department_id, hire_date, salary);
 
 # 非推奨順序: (salary, department_id, hire_date)
 # - salaryが範囲条件なので最初に配置すると、department_idのインデックス効率が低下

等価条件が複数ある場合、選択性の高い列を優先する。

 # department_id: 10種類、status: 5種類
 # statusの方が選択性が低い (Cardinalityが低い)
 
 # 推奨順序: (department_id, status)
 CREATE INDEX idx_dept_status ON employees (department_id, status);

MySQL 8.0以降の新機能

降順インデックス

MySQL 8.0以降では、降順インデックス (DESC) が実際に有効になった。

MySQL 5.7以前では、DESC を指定してもインデックスは昇順で作成されていた。
MySQL 8.0では、混在ソート (一部昇順、一部降順) のクエリも最適化される。

 # 降順インデックスの作成
 CREATE INDEX idx_created_desc ON posts (created_at DESC);
 
 # 混在ソートのインデックス
 CREATE INDEX idx_user_date ON orders (user_id ASC, order_date DESC);
 
 # このクエリは混在ソートインデックスを効率的に使用
 SELECT * FROM orders WHERE user_id = 123 ORDER BY order_date DESC;

降順インデックスは、ORDER BY ... DESC のクエリで特に効果的である。

 # 降順ソートのクエリ
 SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
 
 # インデックス: (created_at DESC) があれば、ソート不要で高速
 # EXPLAIN: Extra = Using index (ソート処理なし)
 
 # インデックス: (created_at ASC) の場合、逆順スキャンが必要
 # EXPLAIN: Extra = Backward index scan (MySQL 8.0以降)

不可視インデックス

MySQL 8.0以降では、不可視インデックス (INVISIBLE) 機能が追加された。

不可視インデックスは、オプティマイザーから見えないインデックスであり、削除前のテストに使用できる。
インデックス自体は維持されるため、再度可視 (VISIBLE) に戻すことが可能である。

 # 不可視インデックスの作成
 CREATE INDEX idx_name ON employees (name) INVISIBLE;
 
 # 既存インデックスを不可視に変更
 ALTER TABLE employees ALTER INDEX idx_name INVISIBLE;
 
 # 不可視インデックスを可視に戻す
 ALTER TABLE employees ALTER INDEX idx_name VISIBLE;

不可視インデックスは、インデックスの削除前テストに有用である。

 # ステップ1: インデックスを不可視にする
 ALTER TABLE employees ALTER INDEX idx_department INVISIBLE;
 
 # ステップ2: パフォーマンスを監視
 # クエリのパフォーマンスが低下しないか確認
 
 # ステップ3a: 問題なければ削除
 DROP INDEX idx_department ON employees;
 
 # ステップ3b: 問題があれば可視に戻す
 ALTER TABLE employees ALTER INDEX idx_department VISIBLE;

optimizer_switch の use_invisible_indexes を有効にすると、不可視インデックスも使用される。

 # 不可視インデックスを一時的に使用
 SET SESSION optimizer_switch='use_invisible_indexes=on';

関数インデックス

MySQL 8.0.13以降では、関数インデックス (Functional Index) が使用可能である。

式やカラムに対する関数の結果にインデックスを作成できる。

 # 関数インデックスの作成
 CREATE INDEX idx_upper_name ON employees ((UPPER(name)));
 
 # 日付の年部分にインデックス
 CREATE INDEX idx_year_hire ON employees ((YEAR(hire_date)));
 
 # JSON列の特定キーにインデックス
 CREATE INDEX idx_json_age ON users ((CAST(profile->>'$.age' AS UNSIGNED)));
 
 # 複合関数インデックス
 CREATE INDEX idx_full_name ON employees ((CONCAT(first_name, ' ', last_name)));

関数インデックスを使用するクエリ例を以下に示す。

 # 大文字変換での検索 (関数インデックスを使用)
 SELECT * FROM employees WHERE UPPER(name) = 'ALICE';
 
 # 年での検索 (関数インデックスを使用)
 SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
 
 # JSON列の検索 (関数インデックスを使用)
 SELECT * FROM users WHERE CAST(profile->>'$.age' AS UNSIGNED) > 30;

関数インデックスは、内部的には仮想カラム (Virtual Column) として実装される。

 # 関数インデックスは以下と等価
 ALTER TABLE employees
    ADD COLUMN name_upper VARCHAR(100) AS (UPPER(name)) VIRTUAL,
    ADD INDEX idx_name_upper (name_upper);

Multi-Valued Index

MySQL 8.0.17以降では、JSON配列に対するMulti-Valued Indexが使用可能である。

JSON配列の要素に対してインデックスを作成できる。

 # Multi-Valued Indexの作成
 CREATE INDEX idx_tags ON articles ((CAST(tags AS UNSIGNED ARRAY)));
 
 # 文字列配列の例
 CREATE INDEX idx_categories ON products ((CAST(categories AS CHAR(50) ARRAY)));
 
 # テーブル作成時の例
 CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    tags JSON,
    INDEX idx_tags ((CAST(tags AS UNSIGNED ARRAY)))
 );

Multi-Valued Indexは、MEMBER OF() 関数と組み合わせて使用する。

 # JSON配列の挿入
 INSERT INTO articles (id, title, tags)
 VALUES (1, 'MySQL Tutorial', JSON_ARRAY(1, 2, 5));
 
 # MEMBER OF()での検索 (インデックスを使用)
 SELECT * FROM articles WHERE 2 MEMBER OF(tags);
 
 # 複数の値を検索
 SELECT * FROM articles WHERE 1 MEMBER OF(tags) OR 5 MEMBER OF(tags);

Multi-Valued Indexは、タグ検索やカテゴリ検索等のユースケースで有用である。

インデックスのパフォーマンス

EXPLAINによる実行計画の確認

EXPLAIN 文を使用すると、クエリの実行計画を確認できる。

インデックスが使用されているか、どのインデックスが使用されているかを確認できる。

 # 実行計画を確認
 EXPLAIN SELECT * FROM employees WHERE department_id = 10;
 
 # より詳細な情報
 EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 10;
 
 # MySQL 8.0.18以降: 実際の実行時間も確認
 EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

EXPLAIN の主要な出力列は以下の通りである。

EXPLAINの主要な出力列

カラム	説明	備考
type	アクセスタイプ	良い順 : const > eq_ref > ref > range > index > ALL
key	使用されるインデックス名	NULLの場合はインデックス未使用
key_len	使用されるインデックスの長さ	複合インデックスでどこまで使用されたかを示す。
rows	スキャンされる行数の推定値	少ないほど良い。
Extra	追加情報	Using index (カバリングインデックス)、Using where、Using filesort 等

type 列の値の意味は以下の通りである。

EXPLAINのtypeカラムの値

type	説明	例
const	PRIMARY KEYまたはUNIQUEインデックスでの等価検索 (最速)	WHERE id = 1
eq_ref	JOINでのPRIMARY KEYまたはUNIQUEインデックス参照	JOIN ON t1.id = t2.id
ref	非UNIQUEインデックスでの等価検索	WHERE dept_id = 10
range	範囲検索	WHERE id BETWEEN 1 AND 100
index	インデックス全体のスキャン	SELECT id FROM t (idがPRIMARY KEY)
ALL	テーブル全体のスキャン (最遅)	WHERE name LIKE '%son'

 # typeの確認例
 EXPLAIN SELECT * FROM employees WHERE id = 100;
 # type: const (PRIMARY KEYでの等価検索)
 
 EXPLAIN SELECT * FROM employees WHERE department_id = 10;
 # type: ref (非UNIQUEインデックスでの等価検索)
 
 EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
 # type: range (範囲検索)
 
 EXPLAIN SELECT * FROM employees WHERE name LIKE '%son';
 # type: ALL (テーブルフルスキャン)

選択性 (Cardinality)

選択性は、インデックスの効率を示す指標である。

Cardinality は、インデックス内の一意な値の推定数であり、高いほど選択性が高い。
選択性が高いインデックスほど、絞り込み効果が高く、効率的である。

 # Cardinalityの確認
 SHOW INDEX FROM employees;
 
 # 出力例:
 # Column_name | Cardinality
 # id          | 10000       (PRIMARY KEY、全て一意)
 # dept_id     | 50          (50種類の部署)
 # status      | 3           (3種類のステータス)

選択性の計算式は以下の通りである。

選択性 = DISTINCT数 / 総行数

一般的に、選択性が20[%]以上 (DISTINCT数 / 総行数 >= 0.2) の列にインデックスを作成すると効果的である。
選択性が低い列 (例 : 性別、ブール値) にインデックスを作成しても、効果は限定的である。

 # 選択性の確認例
 SELECT
    COUNT(DISTINCT department_id) / COUNT(*) AS selectivity
 FROM employees;
 # 結果: 0.05 (5%) → 選択性が低い、インデックスの効果は限定的
 
 SELECT
    COUNT(DISTINCT email) / COUNT(*) AS selectivity
 FROM users;
 # 結果: 1.0 (100%) → 選択性が高い、インデックスの効果が大きい

インデックスヒント

インデックスヒントを使用すると、オプティマイザーに使用するインデックスを指示できる。

ただし、MySQL 8.0.20以降では、インデックスヒントの使用は推奨されない。
オプティマイザーは通常、適切なインデックスを自動的に選択する。

 # USE INDEX (推奨インデックス)
 SELECT * FROM employees USE INDEX (idx_department) WHERE department_id = 10;
 
 # FORCE INDEX (強制的にインデックスを使用)
 SELECT * FROM employees FORCE INDEX (idx_department) WHERE department_id = 10;
 
 # IGNORE INDEX (特定のインデックスを無視)
 SELECT * FROM employees IGNORE INDEX (idx_department) WHERE department_id = 10;
 
 # 複数のインデックスを指定
 SELECT * FROM employees USE INDEX (idx_department, idx_salary) WHERE department_id = 10 AND salary > 50000;

インデックスヒントは、オプティマイザーが誤った判断をする場合の一時的な回避策として使用する。
長期的には、統計情報の更新やインデックス設計の見直しを検討すべきである。

更新コスト

インデックスは、INSERT、UPDATE、DELETE 操作時に更新が必要となる。

インデックスが多いほど、更新コストが増加する。
そのため、不要なインデックスは削除し、テーブルあたり5個程度を上限の目安とすることが推奨される。

 # インデックスが多いテーブルでのINSERT
 # 各インデックスに対して挿入処理が必要
 INSERT INTO employees (id, name, department_id, salary, hire_date)
 VALUES (1001, 'Alice', 10, 60000, '2024-01-01');
 
 # インデックスが5個ある場合、以下の更新が発生
 # - PRIMARY KEY (id)
 # - idx_name (name)
 # - idx_department (department_id)
 # - idx_salary (salary)
 # - idx_hire_date (hire_date)

大量のデータを一括挿入する場合、一時的にインデックスを無効化することで高速化できる。

 # MyISAMエンジンの場合
 ALTER TABLE employees DISABLE KEYS;
 # 大量挿入
 INSERT INTO employees ...
 ALTER TABLE employees ENABLE KEYS;
 
 # InnoDBエンジンの場合 (外部キー制約の無効化)
 SET foreign_key_checks = 0;
 SET unique_checks = 0;
 # 大量挿入
 INSERT INTO employees ...
 SET foreign_key_checks = 1;
 SET unique_checks = 1;

インデックスが使用されない場合

以下の場合、インデックスは使用されない。

• LIKE '%xxx' (後方一致、中間一致)

  前方一致 ('xxx%') のみインデックス使用可能

• 関数適用 (MySQL 8.0以前)

  UPPER(name)、YEAR(date) 等。8.0以降は関数インデックスで対応可能

• 型不一致

  文字列列に数値で検索、数値列に文字列で検索等

• 左端プリフィックス違反

  複合インデックス (col1, col2) で col2 のみを検索

• OR条件

  WHERE col1 = 1 OR col2 = 2 (各列に個別インデックスがあっても非効率)

 # インデックスが使用されない例
 
 # 後方一致LIKE
 SELECT * FROM employees WHERE name LIKE '%son';
 
 # 関数適用 (8.0以前)
 SELECT * FROM employees WHERE UPPER(name) = 'ALICE';
 
 # 型不一致
 SELECT * FROM employees WHERE age = '30';  # ageがINT型の場合
 
 # 左端プリフィックス違反
 # インデックス: (department_id, salary)
 SELECT * FROM employees WHERE salary > 50000;  # department_idがない
 
 # OR条件
 SELECT * FROM employees WHERE department_id = 10 OR salary > 50000;

OR 条件の代わりに UNION を使用すると、各クエリでインデックスが使用される。

 # OR条件 (インデックス非効率)
 SELECT * FROM employees WHERE department_id = 10 OR salary > 50000;
 
 # UNIONに書き換え (各クエリでインデックス使用)
 SELECT * FROM employees WHERE department_id = 10
 UNION
 SELECT * FROM employees WHERE salary > 50000;

推奨される事柄

インデックス作成の指針

効果的なインデックス作成のための指針を以下に示す。

• 頻繁なWHERE条件のカラムにインデックスを作成

  クエリログを分析し、頻繁に検索される列を特定する

• JOINの結合キーにインデックスを作成

  外部キー列には必ずインデックスを作成

• ORDER BY、GROUP BYの列にインデックスを作成

  ソート処理を避け、インデックスの順序を利用

• 選択性の高い列を優先

  DISTINCT数 / 総行数 >= 0.2 を目安

• テーブルあたり5個程度を上限目安

  過剰なインデックスは更新コストを増加させる

詳細は MySQL - JOIN のページも参照すること。

プリフィックスインデックス

長い文字列列には、プリフィックスインデックス (先頭N文字) を使用する。

これにより、インデックスのサイズを削減できる。

 # プリフィックスインデックスの作成 (先頭20文字)
 CREATE INDEX idx_name_prefix ON employees (name(20));
 
 # URLの先頭50文字にインデックス
 CREATE INDEX idx_url_prefix ON pages (url(50));
 
 # 通常のインデックスとの比較
 CREATE INDEX idx_name_full ON employees (name);  # 全体にインデックス
 CREATE INDEX idx_name_prefix ON employees (name(20));  # 先頭20文字のみ

プリフィックス長の選択は、選択性を確認して決定する。

 # 選択性の確認
 SELECT
    COUNT(DISTINCT name) AS full_selectivity,
    COUNT(DISTINCT LEFT(name, 10)) AS prefix_10,
    COUNT(DISTINCT LEFT(name, 20)) AS prefix_20,
    COUNT(DISTINCT LEFT(name, 30)) AS prefix_30
 FROM employees;
 
 # 結果例:
 # full_selectivity: 9500
 # prefix_10: 8000
 # prefix_20: 9300
 # prefix_30: 9480
 # → 先頭20文字で十分な選択性が得られる

統計情報の更新

ANALYZE TABLE を定期的に実行し、統計情報を更新する。

統計情報が古いと、オプティマイザーが誤った実行計画を選択する可能性がある。

 # 統計情報を更新
 ANALYZE TABLE employees;
 
 # 複数のテーブルを一度に更新
 ANALYZE TABLE employees, departments, orders;
 
 # 結果の確認
 # Msg_type | Msg_text
 # status   | OK

InnoDBでは、innodb_stats_auto_recalc が有効な場合、テーブルの10%以上が変更されると自動的に統計情報が更新される。

 # 自動統計更新の設定確認
 SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
 
 # テーブルごとの設定
 CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
 ) STATS_AUTO_RECALC=1;
 
 # 既存テーブルの設定変更
 ALTER TABLE employees STATS_AUTO_RECALC=1;

未使用インデックスの特定

performance_schema を使用して、未使用のインデックスを特定できる。

 # performance_schemaの有効化
 SET GLOBAL performance_schema = ON;
 
 # 未使用インデックスの確認 (MySQL 8.0以降)
 SELECT
    object_schema AS database_name,
    object_name AS table_name,
    index_name
 FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
 ORDER BY object_schema, object_name;

未使用のインデックスは、不可視インデックスにしてテストした後、削除する。

 # 未使用インデックスを不可視に
 ALTER TABLE employees ALTER INDEX idx_old_column INVISIBLE;
 
 # パフォーマンスを監視
 # 問題なければ削除
 DROP INDEX idx_old_column ON employees;

インデックスのメンテナンス

インデックスの断片化を定期的に解消する。

 # テーブルの最適化 (インデックスの再構築)
 OPTIMIZE TABLE employees;
 
 # InnoDBの場合、ALTER TABLEでも再構築可能
 ALTER TABLE employees ENGINE=InnoDB;
 
 # 特定のインデックスを再構築
 ALTER TABLE employees DROP INDEX idx_name, ADD INDEX idx_name (name);

OPTIMIZE TABLE は、テーブルをロックするため、本番環境では注意が必要である。
InnoDBでは、ALTER TABLE ... ENGINE=InnoDB の方が推奨される。