MySQLインデックス：2秒の検索から30ミリ秒の結果へ

適切なインデックスは、2秒の製品検索を30ミリ秒に変えることができます – 98％の改善です。しかし、ほとんどのWordPressサイトはデフォルトのインデックスのみで実行され、多くのカスタムPHPアプリケーションはインデックス戦略なしで構築されています。このガイドでは、データベースを高速化するインデックスを設計する方法を教えます。

インデックスがWebパフォーマンスに重要な理由

ユーザーがデータベースにクエリを実行するページ（製品リスト、検索結果、ユーザープロファイル）を訪問すると、MySQLは関連する行を見つける必要があります。インデックスがない場合、MySQLは全表スキャンを実行します – 一致するものを見つけるまで全ての行を読み取ります。100,000行のテーブルの場合、全スキャンには200〜500ミリ秒かかります。1,000人の同時ユーザーがいると、それは数秒の遅延になり、サーバーのCPUは最大になります。

インデックスは本の索引のようなものです：行を正確に見つける場所をMySQLに指示し、O(n)操作をO(log n)に変えます。結果として、クエリは秒ではなくミリ秒で返されます。

MySQLインデックスの仕組み（簡単に説明）

MySQLはデフォルトでB‑Treeインデックスを使用します。列の値をソートされたツリー構造で格納します。インデックス列にWHERE句を使用してクエリを実行すると、MySQLはツリーをトラバースして、約log₂(N)ステップで一致する値を見つけます。100万行の場合、これは100万回のスキャンではなく約20ステップです。

重要なルール： インデックスは等価（=）、範囲（<, >, BETWEEN）、またはプレフィックス一致（LIKE 'term%'）でフィルタリングする場合に役立ちます。LIKE '%term%'（先頭ワイルドカード）やWHERE DATE(created_at) = '2025-01-01'のような関数呼び出しでは役立ちません。

遅いクエリの識別 – EXPLAINの使用

インデックスを追加する前に、最も遅いクエリを見つけます。MySQLのスロークエリログを有効にします：

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

次にmysqldumpslow -s t /var/log/mysql/slow.logを実行して、トップの遅いクエリを確認します。

次に、疑わしいクエリの前にEXPLAINを付けて、MySQLがどのように実行するかを確認します。例：

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price < 100;

type列を確認します：

• ALL – 全表スキャン（最悪）。
• rangeまたはref – インデックス使用（良好）。
• const – 主キールックアップ（最良）。

また、rows – スキャンされた行数も確認します。テーブルの合計に近い場合は、インデックスを追加します。

実用的なインデックス戦略 – 単一列インデックスと複合インデックス

単一列インデックス

頻繁に1つの列でフィルタリングする場合に使用します。

CREATE INDEX idx_user_id ON orders (user_id);

複合（複数列）インデックス

複数の列でフィルタリングする場合、複合インデックスは個別のインデックスよりもはるかに効率的です。MySQLはテーブル参照ごとに1つのインデックスしか使用できませんが、複合インデックスは複数の列をカバーします。

-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);

-- Now: 30ms, scans 127 rows

順序ルール： 最も選択性の高い列を最初に配置します（最も多くの行をフィルタリングする列）。通常、category_idは50,000 → 5,000に減らし、次にpriceは200に減らします。逆にすると（price, category_id）、インデックスは価格の範囲クエリに対して効率が低下します。

カバリングインデックス（聖杯）

クエリがインデックス内の列のみを必要とする場合、MySQLはテーブルデータに触れることなくインデックスから完全にクエリに答えることができます。これは非常に高速です。

-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;

-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);

-- EXPLAIN will show "Using index" in Extra column

実際の例：Eコマース製品フィルタリング – 前と後

50,000製品のオンラインストアには、カテゴリ、ブランド、価格帯でフィルタリングする検索ページがありました。元のクエリ：

SELECT * FROM products 
WHERE category_id = 12 
  AND brand_id IN (3,7,9) 
  AND price BETWEEN 50 AND 200 
ORDER BY price LIMIT 24;

インデックスなし： 全表スキャン – 2.3秒、50,000行スキャン。

分析後： 複合インデックスを追加しました：

CREATE INDEX idx_category_brand_price ON products (category_id, brand_id, price);

結果： 同じクエリが80ミリ秒で実行され（96％改善）、わずか312行をスキャンしました。ページ読み込み時間は3.2秒から0.6秒に短縮されました。

WordPressのインデックス（どうしても残る場合）

WordPressは汎用スキーマのため、遅いクエリで悪名高いです。wp_postmetaテーブルはすべてのカスタムフィールドをキーと値のペアとして保存し、数百万行になります。カスタムインデックスを追加して、一般的なクエリを改善できます：

-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));

しかし、インデックスがあっても、WordPressは依然としてwp_queryのオーバーヘッドに悩まされます。適切なスキーマとインデックスで設計されたカスタムPHPアプリケーションは常に高速です。

インデックスのメンテナンス – いつ追加し、いつ削除するか

• インデックスを追加 WHERE、JOIN、ORDER BY、GROUP BYで使用される列に。
• インデックスを削除 ほとんど使用されない列やカーディナリティが非常に低い列（例：2つの値しかない`status`列）。各インデックスは、MySQLがインデックスも更新する必要があるため、INSERT、UPDATE、DELETEを遅くします。
• インデックスの使用状況を監視 SHOW INDEX FROM table;およびSELECT * FROM sys.schema_unused_indexes;（MySQL 8.0）を使用。

インデックス分析ツール

• EXPLAIN – 組み込み、常にここから始めます。
• MySQL Workbench – ビジュアルEXPLAINとパフォーマンスダッシュボード。
• pt‑query‑digest（Percona Toolkit） – スロークエリログを分析し、インデックスを提案します。
• phpMyAdmin – 「プロファイリング」機能でクエリ実行の詳細を表示。

クライアント事例：不動産プラットフォーム

不動産ウェブサイトには200,000の物件リストがありました。ユーザーは都市、価格帯、寝室数、物件タイプで検索しました。クエリが毎回テーブル全体をスキャンしていたため、検索ページの読み込みに4〜6秒かかりました。

解決策： スローログを分析しEXPLAINを使用した後、複合インデックスを追加しました：

CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);

また、`SELECT *`の代わりに必要な列（id、タイトル、価格、サムネイル）のみを選択してカバリングインデックスを使用するようにクエリを変更しました。

結果：

• クエリ時間：5.2秒 → 90ミリ秒。
• サーバーCPU負荷が70％低下。
• ページ読み込み時間：6.5秒 → 1.2秒（フロントエンド含む）。
• ユーザーが遅い検索を放棄しなかったため、ユーザーエンゲージメントが34％向上。

よくある間違いとその修正方法

間違い1：すべての列をインデックス化する

問題： インデックスが多すぎると書き込みが遅くなります。修正： WHERE、JOIN、またはORDER BYに現れる列のみをインデックス化します。

間違い2：インデックス列で関数を使用する

悪い： WHERE DATE(created_at) = '2025-01-01' – created_atのインデックスを無視します。良い： WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'。

間違い3：LIKEの先頭ワイルドカード

悪い： WHERE name LIKE '%widget%' – インデックスを決して使用しません。良い： 部分一致には全文検索インデックスを使用します。

間違い4：EXPLAINを前後で使用しない

インデックスを追加する前にEXPLAINを実行し、再度追加した後に実行して改善を検証します。

上級：インデックス提案の自動化

MySQL 8.0では、パフォーマンススキーマを有効にしてsysスキーマを使用し、不足しているインデックスを見つけることができます：

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

古いバージョンでは、percona-toolkitのpt-index-usageがスローログを分析し、インデックスを提案します。

データベースを最適化する準備はできましたか？

私は初日から適切なデータベースインデックスを使用してカスタムPHPアプリケーションを構築しています。遅いWordPressサイトでも、クエリのボトルネックがあるカスタムアプリでも、スローログを分析し、適切なインデックスを追加し、多くの場合ページ読み込み時間を50％以上削減できます。

データベースのパフォーマンス問題について話し合いましょう。最も遅いクエリの無料評価を提供します。

すべてのパフォーマンス数値は、Hostinger VPS上のMySQL 8.0を使用した実際のクライアント監査からのものです。結果はテーブルサイズとサーバーハードウェアによって異なる場合があります。