問題提起：データベースが「悲鳴を上げるとき」
システム担当者にとって、データベースの動作が遅いのは悪夢です。すべてが停滞し、ユーザーは不満を漏らし、チームは原因究明に苦慮します。
私はある出来事を鮮明に覚えています。プロジェクトが拡大し、users テーブルが1,000万行以上に急増したときのことです。当時、ユーザー名やメールアドレスで検索するような単純なクエリが突然「這うように」遅くなりました。そのとき私は気づきました。Indexを理解し最適化し、EXPLAIN を使用することは、もはやオプションではありません。システムが負荷で「ダウン」しないための必須要素なのです。
この記事では、私のチームがIndexとEXPLAINを使ってMySQLのパフォーマンスを「救済」した実体験を共有します。これらの知識が、特に初心者の方や同様の問題に直面している方にとって役立つことを願っています。
核心概念：IndexとEXPLAIN
Indexとは何か、なぜIndexが必要なのか？
Indexを本の目次のように想像してみてください。特定の情報を見つける必要があるとき、本全体を読む必要はありません。代わりに、目次が正確に必要なページを教えてくれます。データベースのIndexも同様です。
- 定義：Indexは、データベースエンジンによって作成される特殊なデータ構造（通常はB-Tree形式）です。データの検索とクエリを高速化するのに役立ちます。Indexは、テーブル内の1つ以上の列からの値のセットと、データ行の実際の場所を指すポインタを格納します。
- 主な利点：
- クエリ速度の向上：これが最大の利点です。Indexを使用すると、MySQLはデータ行を迅速に特定し、テーブル全体のスキャン（フルテーブルスキャン）を必要としません。
- データソートの高速化：
ORDER BYおよびGROUP BYコマンドは、Indexを使用してデータをより速くソートおよびグループ化します。これにより、コストのかかる「filesort」操作が削減または排除されます。 - 一意性の保証：
PRIMARY KEYやUNIQUE INDEXなどのIndexは、速度を向上させるだけでなく、列内のデータの一意性も保証します。 - Indexのデメリット：
- ストレージスペースの消費：Indexはディスクスペースを占有します。
- 書き込み速度の低下：データを追加（
INSERT）、変更（UPDATE）、または削除（DELETE）するたびに、データベースはメインテーブルと関連するIndexを更新する必要があります。これによりI/Oコストが増加し、書き込みパフォーマンスが低下します。 - 管理コスト：MySQLはIndexを維持するためにリソースを必要とします。
したがって、Indexを作成する際には慎重に検討し、無闇に作成しないようにする必要があります。
MySQLでよく使われるIndexの種類
MySQLには、一般的に使用されるいくつかの主要なIndexタイプがあります：
- PRIMARY KEY（主キー）：メインとなるIndex。テーブル内の各行が一意であり、NULLにすることはできません。各テーブルには1つのPRIMARY KEYのみが存在します。
- UNIQUE INDEX（ユニークインデックス）：PRIMARY KEYに似ていますが、NULL値を許可します（列が許可する場合）。データのユニーク性も保証します。
- NORMAL INDEX（非ユニークインデックス）：通常のIndex。重複値とNULL値を許可します。頻繁に検索される列に使用します。
- FULLTEXT INDEX（全文インデックス）：全文検索（full-text search）に使用します。このブログでは別途記事がありますので、ここでは深く掘り下げません。
- COMPOSITE INDEX（複合インデックス）：複数の列に作成されるIndex。このIndexにおける列の順序は非常に重要です。
EXPLAINとは何か、どのような役に立つのか？
Indexが武器であるならば、EXPLAINはその武器がどれほど効果的であるかを知るための分析ツールです。MySQLのEXPLAINコマンドは、SQLステートメントの実行計画（execution plan）を表示します。これにより、MySQLがどのようにデータにアクセスするか、Indexを使用するかどうか、そしてクエリを完了するまでにどれだけのステップがかかるかを示します。
簡単な構文：
EXPLAIN SELECT * FROM your_table WHERE your_column = 'value';
EXPLAINの結果は、多くの重要な情報列を含むテーブルです：
id: クエリ内のステップの順序番号。select_type: クエリのタイプ（SIMPLE、PRIMARY、SUBQUERY、UNIONなど）。table: クエリが処理しているテーブル名。type: これは非常に重要な列で、MySQLがデータにアクセスする方法を示します。良好な値はsystem、const、eq_ref、ref、rangeです。良くない値にはindex（Index全体のスキャン）とALL（テーブル全体のスキャン – 非常に悪い）が含まれます。possible_keys: MySQLが使用できるIndex。key: MySQLが使用することを選択した実際のIndex。key_len: 使用されたIndex部分の長さ（バイト）。ref: 検索のためにkeyとともに使用される列。rows: MySQLが結果を見つけるためにチェックする必要があると推定される行数。少ないほど良いです。Extra: MySQLがクエリを処理する方法に関する非常に役立つ追加情報、例えば “Using filesort”, “Using temporary”, “Using index” など。
詳細な実践：EXPLAINの使用とIndexの作成
理解を深めるために、具体的な例を使用します。以下のproductsテーブルがあると仮定します：
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category_price (category_id, price)
);
INSERT INTO products (product_name, category_id, price) VALUES
('Laptop Dell XPS 15', 1, 1500.00),
('Smartphone Samsung S23', 2, 999.00),
('Smart TV Sony 55 inch', 3, 750.00),
('Bàn phím cơ Logitech', 1, 120.00),
('Chuột gaming Razer', 1, 70.00),
('Máy giặt Electrolux', 4, 600.00),
('Tủ lạnh Panasonic', 4, 850.00),
('Loa Bluetooth JBL', 2, 150.00),
('Tai nghe Sony WH-1000XM5', 2, 350.00),
('Màn hình Dell UltraSharp', 1, 450.00);
-- 大規模なテーブルをシミュレートするためのダミーデータを追加
DELIMITER //
CREATE PROCEDURE InsertDummyProducts()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO -- 100万件の製品を追加
INSERT INTO products (product_name, category_id, price) VALUES
(CONCAT('Product ', FLOOR(RAND() * 1000000)), FLOOR(1 + RAND() * 4), ROUND(RAND() * 2000 + 50, 2));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertDummyProducts();
さて、特定の価格帯にあるすべての製品を検索したいが、まだprice列にIndexがないと仮定します。
EXPLAINによるクエリ分析（Index作成前）
次のクエリを実行します：
EXPLAIN SELECT product_name, price FROM products WHERE price BETWEEN 100 AND 200;
結果は次のようになります：
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 1000010 | 11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
type列がALL、keyがNULL、そしてrowsが1,000,010であることに注目してください。これは、MySQLが**フルテーブルスキャン**を実行していることを示しています。つまり、price BETWEEN 100 AND 200の条件を満たす製品を見つけるために、100万行のデータを走査しているということです。大規模なテーブルでは、これがパフォーマンス低下の主な原因となります。
最適化のためのIndexの作成と管理
次に、パフォーマンスを向上させるためにprice列にIndexを作成します：
CREATE INDEX idx_price ON products (price);
Indexを作成した後、前のクエリに対して再度EXPLAINコマンドを実行します：
EXPLAIN SELECT product_name, price FROM products WHERE price BETWEEN 100 AND 200;
結果は大幅に変わります：
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | products | NULL | range | idx_price | idx_price | 5 | NULL | 10000 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+-------------+
今や、type列はrange（ALLよりもはるかに良い）です。key列にはidx_priceが表示されています（MySQLはIndexを使用しました！）。最も重要なのは、rows列が約10,000に減少していることです。これは、MySQLが結果を見つけるために、Indexされたデータのごく一部を走査するだけで済むことを意味し、テーブル全体をスキャンする必要がなくなります。
複合インデックス（Composite Index）
WHERE条件が複数の列で構成される場合があります。例えば、特定のカテゴリ内の製品と、特定の価格帯にある製品を見つけたい場合です：
EXPLAIN SELECT product_name FROM products WHERE category_id = 1 AND price BETWEEN 100 AND 500;
idx_priceのみが存在する場合、MySQLはそれを使用するかもしれませんが、最も最適ではありません。MySQLは、見つけられた行に対してさらにcategory_idでフィルタリングする必要があるかもしれません。(category_id, price)に複合Indexを作成する方がはるかに効果的です：
CREATE INDEX idx_category_price ON products (category_id, price);
次に、再度EXPLAINを実行します：
EXPLAIN SELECT product_name FROM products WHERE category_id = 1 AND price BETWEEN 100 AND 500;
keyがidx_category_priceとなり、typeがrangeまたはrefになることがわかります。MySQLはこのIndexを使用して両方の条件を同時にフィルタリングするため、rowsの数がさらに大幅に減少します。
複合Indexにおける列の順序に関する重要な注意点：列の順序は、WHERE条件での使用方法と一致する必要があります。MySQLはIndexを左から右に利用します。(col1, col2)というIndexを作成した場合、WHERE col1 = ...またはWHERE col1 = ... AND col2 = ...を使用するクエリには役立ちます。しかし、WHERE col2 = ...のみを使用する場合には、ほとんど、またはまったく役立ちません。
カバリングインデックス（Covering Index）
カバリングインデックスは、クエリが必要とするすべての列（SELECT句とWHERE句の両方）を含むIndexです。MySQLがメインテーブルにアクセスすることなく、Indexからすべてのデータを取得できる場合、クエリは非常に高速になります。
例：SELECT category_id, price FROM products WHERE category_id = 1 AND price > 100;というクエリを頻繁に実行する場合、Index (category_id, price)はWHERE条件に役立つだけでなく、SELECT内の列も「カバー」します。この場合、EXPLAINはExtra: Using indexと表示します。これは、MySQLがクエリを完了するためにIndexを読み取るだけで済むことを示しており、非常に効率的です。
不要なIndexの削除
Indexが不要になったり、書き込み操作に負担をかける場合、削除することができます：
DROP INDEX idx_price ON products;
Indexを使用する際の注意点
- 頻繁にクエリされる列のみをIndex化する：無闇にIndexを作成しないでください。
WHERE、JOIN、ORDER BY、GROUP BYによく登場する列が理想的な候補です。 - 列のカーディナリティ（Cardinality）：Indexは、email、製品コードなど、ユニークな値が多い（カーディナリティが高い）列で最も効果的です。性別（男性/女性）のようにユニークな値が少ない（カーディナリティが低い）列は、Indexがほとんど必要ありません。なぜなら、MySQLはIndexを使用してフィルタリングするよりも、テーブル全体をスキャンする方が速い場合があるからです。
- Indexされた列での関数の使用を避ける：
WHERE句でIndexされた列に関数を使用すると（例：WHERE YEAR(created_at) = 2023）、MySQLはそのIndexを通常使用しません。代わりに、クエリを書き換えて列と直接比較するようにしてください（例：WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31 23:59:59'）。 LIKE演算子：IndexはLIKE 'prefix%'（で始まる検索）で使用できます。しかし、LIKE '%suffix'やLIKE '%substring%'では使用されません。これは、MySQLがIndexのツリー構造を利用できないためです。ORとIndex：WHERE条件でORを使用すると、MySQLがIndexを使用しない場合があります。場合によっては、クエリをUNIONまたはUNION ALLに分割する方が最適化されます。特に、ORの各部分が個別のIndexを使用できる場合に有効です。- 小規模なテーブルにはIndexは不要：数百または数千行しかないテーブルでは、Indexを維持するコストがメリットを上回ることがあります。MySQLはテーブル全体を非常に速くスキャンできます。
結論：継続的な監視と最適化
私の経験からすると、IndexとEXPLAINを使ったMySQLクエリの最適化は、データベースを扱うすべての人にとって不可欠なスキルです。これにより、システムがより高速に動作するだけでなく、データベースの動作原理をより深く理解することができます。
しかし、これは一度行えば終わりというものではありません。システムとデータは常に変化し、成長し続けています。パフォーマンスを定期的に監視し、新しいクエリや遅いクエリをEXPLAINで確認し、適切なIndexを調整することは、システム開発ライフサイクルにおいて不可欠な役割を果たします。
EXPLAINを信頼できるパートナーと考えてください。これにより、MySQLがデータをどのように処理しているかを「見通す」ことができ、最もインテリジェントなIndex最適化の決定を下すことができます。MySQLの力を「活用」する皆さんの成功を願っています！
