2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@TOMOSIA-HieuNTin株式会社TOMOSIA VIET NAM

SQL Indexing 用 AI エージェントスキル集

2
Last updated at Posted at 2026-07-07

Gemini_Generated_Image_9jfacn9jfacn9jfa-clean.png

インデックスは馴染みのあるツールですが、設計を誤りやすいものです。「念のため」列を足す、SELECT * でカバリングを壊す、使われないインデックスが書き込みだけ遅くする——そんな失敗はよくあります。AI エージェントの時代では、SQL インデックスに関する明確なルールとガイドを用意することで、プロジェクトをより速く・正確にできます。

実務経験と信頼できる資料をもとに、SQL インデックス作業向けのスキルをまとめたリポジトリを公開しました。MySQL/InnoDB の 4 つのインデックススキルを、複合インデックス設計・読み取りパス最適化・全文検索・本番監査・メンテナンスまで一つの実践ロードマップにまとめています。

本記事は HieuNT44/indexing の 4 つの主要スキルファイル（各章 1 ファイル）に基づくリポジトリの解説です。以下では orders テーブルのケーススタディと EXPLAIN の結果を使って、理論だけでなくすぐ使える形で説明します。

リポジトリ: https://github.com/HieuNT44/indexing

章 ソースファイル 学ぶこと
第 1 章 composite-indexes.md 複合インデックス設計、leftmost prefix、列の順序
第 2 章 covering-indexes.md インデックスオンリースキャン、ICP とカバリング、EXPLAIN で検証
第 3 章 fulltext-indexes.md MATCH() AGAINST() による全文検索
第 4 章 index-maintenance.md 未使用・冗長インデックスの監査、メンテナンス、安全な削除

読み方のロードマップ:

第 1 章（設計）  →  第 2 章（読み取り最適化）  →  第 3 章（特殊検索）  →  第 4 章（運用）

目次

はじめに: 各章で使うケーススタディ

第 1・2 章の説明には orders テーブルと 3 つのクエリを使います:

CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NOT NULL,
  status        TINYINT UNSIGNED NOT NULL,
  total         DECIMAL(12, 2) NOT NULL,
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  KEY idx_user_id (user_id),
  KEY idx_status (status)
);

-- Q1: テナント + ステータス別の注文一覧、期間で絞り込み
SELECT id, status, total, created_at
FROM orders
WHERE tenant_id = 1 AND status = 2 AND created_at > '2026-01-01'
ORDER BY created_at DESC;

-- Q2: ユーザー別ダッシュボード
SELECT user_id, status, total
FROM orders
WHERE user_id = 42;

-- Q3: 1 日分のステータス別注文数
SELECT status, COUNT(*)
FROM orders
WHERE tenant_id = 1 AND created_at >= '2026-07-01' AND created_at < '2026-07-02'
GROUP BY status;

第 3 章は別テーブル articles（全文検索）を使用します。
第 4 章は監査のため再び orders に戻ります。

第 1 章: 複合インデックス設計

出典: skills/mysql/references/composite-indexes.md

複合インデックスは基礎です。OLTP テーブルのインデックスの多くは複数列です。

1.1 Leftmost prefix ルール

インデックス (a, b, c) は次に使えます:

クエリ 使われるインデックス列
WHERE a a
WHERE a AND b a, b
WHERE a AND b AND c a, b, c
WHERE a AND c a のみ — b がないと c はフィルタに使えない

WHERE b のみ、または WHERE b AND c には使えません。

よくある失敗: INDEX (status, created_at) を作ったのに WHERE created_at > ? がフルスキャンのまま、という疑問。

1.2 列の順序: equality を先に、range/sort を後に

Q1 の場合:

-- Query: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at);

重要: range 述語（>, <, BETWEEN, LIKE 'prefix%'、大きな IN (...) など）は、以降の列のフィルタ利用を止めます。ただし range の後の列は次に有用です:

  • カバリングインデックス（第 2 章）
  • インデックス prefix と一致する ORDER BY / GROUP BY

1.3 ソート順はインデックスと一致させる

インデックス (status, created_at) の場合:

ORDER BY status ASC, created_at ASC    -- 一致（最適）
ORDER BY status DESC, created_at DESC  -- リバーススキャン OK
ORDER BY status ASC, created_at DESC   -- 方向混在 → filesort の可能性

-- MySQL 8.0+: 降順インデックス列
CREATE INDEX idx_orders_status_created
  ON orders (status ASC, created_at DESC);

Q1 は ORDER BY created_at DESC — インデックスに created_at DESC を検討するか、ソート方向に合わせて設計します。

1.4 複合インデックス vs 単一列インデックス複数

MySQL には index merge（union/intersection）がありますが、複合インデックスの方が通常は速く安定します。index merge は prefix を共有しない列の組み合わせに有用ですが、オーバーヘッドが大きくスケールしにくいです。

1.5 選択性（Selectivity）

equality 列のうち、可能ならカーディナリティの高い列を先に。ただしクエリパターンと頻度の方が純粋な選択性より重要なことが多いです。

1.6 GROUP BY と複合インデックス

GROUP BY はグループ列がインデックス prefix と一致すると恩恵を受けます。MySQL はソート回避にインデックスを使える場合があります（Using index for group-by）。条件は厳しく、GROUP BY 列はインデックスの連続した prefix である必要があり、途中の range 列で途切れてはいけません。

Q3 の場合:

WHERE tenant_id = 1 AND created_at >= ... AND created_at < ...
GROUP BY status

推奨インデックス:

CREATE INDEX idx_orders_tenant_created_status
  ON orders (tenant_id, created_at, status);
  • tenant_id equality を先、created_at range — WHERE を最適化。
  • このインデックスは Q3（SELECT status, COUNT(*)）もカバー — Using index が出る可能性（第 2 章参照）。
  • 末尾の status は GROUP BY status のソート回避を保証しません。スキャン順は (created_at, status) であり、status 単位には並びません。集約は通常 hash/temp table です。

インデックス追加後は EXPLAIN / EXPLAIN ANALYZE で検証してください。

補足: (tenant_id, status, created_at) は status の値が少ないとき loose index scan に役立つ場合がありますが、tenant_id + created_at range のみの WHERE には leftmost prefix の観点で不利です。Q3 では (tenant_id, created_at, status) を優先してください。

1.7 1 つのインデックスで複数クエリに対応

-- 対応: WHERE user_id=?, WHERE user_id=? AND status=?,
--       WHERE user_id=? AND status=? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

1.8 InnoDB セカンダリインデックスの挙動

InnoDB のセカンダリインデックスは常に主キーを保持します。明示しなくても PK ルックアップをカバーできることがあります — 詳細は第 2 章。

第 1 章のまとめ: 「インデックスにできそう」な列ではなく、実際のクエリ形状に合わせて複合インデックスを設計する。

第 2 章: カバリングインデックス

出典: skills/mysql/references/covering-indexes.md

正しい複合インデックス（第 1 章）の次は読み取りパス最適化 — 可能ならテーブルルックアップを避けます。

2.1 カバリングインデックスとは

カバリングインデックスはクエリに必要なすべての列を含みます。InnoDB はインデックスだけで結果を返せます — EXPLAIN の Extra に Using index と出ます。

-- Query: SELECT user_id, status, total FROM orders WHERE user_id = 42
-- Covering index (filter columns first, then included columns):
CREATE INDEX idx_orders_cover ON orders (user_id, status, total);

2.2 InnoDB の暗黙のカバリング

セカンダリインデックスは PK を保持するため、INDEX(status) で次はカバーされます:

SELECT id FROM orders WHERE status = 2;  -- id は PK

インデックス定義に id を足す必要はありません。

2.3 ICP とカバリングインデックス

Extra フラグ 意味
Using index condition ICP — インデックスでフィルタするがテーブルルックアップあり
Using index カバリング — クエリ全体をインデックスのみで処理、ルックアップなし

Using index condition で Using index でない場合 — インデックスは助けているがカバーしていない。SELECT 列をインデックスに追加を検討。

2.4 EXPLAIN のシグナル

EXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- Extra: Using index

関連するその他の Extra フラグ:

フラグ 意味 対応
Using filesort インデックス経由でないソート ORDER BY 列をインデックス化（第 1 章）
Using temporary GROUP BY 用の一時テーブル グループ列をインデックス化（第 1 章）
Using join buffer 結合にインデックス不足 結合列をインデックス化

インデックス追加後は EXPLAIN ANALYZE（MySQL 8.0.18+）で実測してください。

2.5 使うべきとき / 避けるべきとき

使うべき:

  • 広いテーブルから少数列を高頻度で読む

避けるべき:

  • 書き込みが多いテーブル
  • 広い結果セット（TEXT/BLOB）
  • まれなクエリでトレードオフに見合わない

2.6 トレードオフ

  • 書き込み増幅 — INSERT/UPDATE/DELETE のたびに関連インデックスを更新
  • インデックスサイズ — 幅の広いインデックスはディスクとバッファプールを消費
  • メンテナンス — 大きいインデックスは ALTER TABLE の再構築に時間がかかる

2.7 ガイドライン

  • 新規インデックスより既存インデックスに列を追加
  • 順序: フィルタ列を先、カバー列を後
  • 追加後に EXPLAIN で Using index を確認
  • 落とし穴: SELECT * はカバリングを壊す — 必要な列だけ SELECT
-- BAD
SELECT * FROM orders WHERE user_id = 42;

-- GOOD
SELECT user_id, status, total FROM orders WHERE user_id = 42;

第 2 章のまとめ: 複合（第 1 章）+ カバリング（第 2 章）+ EXPLAIN = 読み取りパス設計の三つ組。

第 3 章: 全文検索インデックス

出典: skills/mysql/references/fulltext-indexes.md

全文検索は別種のインデックス — 通常の OLTP の B-tree の代わりにはなりません。テキストのキーワード検索に使います。

3.1 基本的な作成とクエリ

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);

-- 自然言語モード（デフォルト、関連度でソート）
SELECT title, body,
  MATCH(title, body) AGAINST('database performance') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('database performance');

-- Boolean モード: + 必須, - 除外, * ワイルドカード, "フレーズ"
WHERE MATCH(title, body) AGAINST('+mysql -postgres +optim*' IN BOOLEAN MODE);

3.2 MySQL で足りるとき / 検索エンジンが必要なとき

MySQL 全文検索はシンプルなキーワード検索向け。複雑なランキング、ファジーマッチ、大規模ドキュメント検索には Elasticsearch、Meilisearch など専用エンジンを検討。

3.3 主な落とし穴

落とし穴 詳細
最小語長 デフォルト 3 文字（innodb_ft_min_token_size）。変更後は FULLTEXT インデックスの drop/recreate が必要
ストップワード 一般的な語は除外。innodb_ft_enable_stopword、innodb_ft_user_stopword_table でカスタム — インデックス作成前に設定し rebuild
部分一致なし LIKE '%term%' とは異なりトークン全体が必要（boolean の * を除く）
MATCH() はインデックスと一致 MATCH(title, body) には (title, body) の FULLTEXT インデックスが必要
Boolean で + なし 必須語なし → インデックスの大半にマッチし遅い
書き込みオーバーヘッド 全文検索は書き込みコスト増 — 書き込み多いテーブルでは注意

第 3 章のまとめ: テキスト検索は全文検索。OLTP の filter/join は B-tree（第 1–2 章）のまま。

第 4 章: インデックスのメンテナンスと整理

出典: skills/mysql/references/index-maintenance.md

設計後（第 1–3 章）、本番では定期監査が必要 — 余分なインデックスは読み取りに役立たず書き込みだけ遅くします。

4.1 未使用インデックスの検出

-- performance_schema が必要（MySQL 5.7+ でデフォルト有効）
-- 「未使用」= 直近の再起動以降 read/write なし
SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND COUNT_READ = 0
  AND COUNT_WRITE = 0
ORDER BY COUNT_WRITE DESC;

書き込みはあるが読み取りなしのインデックス — クエリに利益なしのオーバーヘッド:

SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND COUNT_READ = 0
  AND COUNT_WRITE > 0
ORDER BY COUNT_WRITE DESC;

COUNT_READ = 0 でも UNIQUE/PK 用に必要なインデックスがあります。カウンタは再起動でリセット — 1 営業サイクル以上観察してから drop。

4.2 冗長インデックスの検出

(a) は (a, b) があれば冗長 — leftmost prefix でカバー。(a,b) と (a,c) は冗長ではない — 手動レビュー。

SELECT table_schema, table_name,
  redundant_index_name, redundant_index_columns,
  dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'mydb';

4.3 インデックスサイズの確認

SELECT database_name, table_name, index_name,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND database_name = 'mydb'
ORDER BY stat_value DESC;

4.4 インデックスの書き込みオーバーヘッド

INSERT、UPDATE、DELETE のたびに全インデックスを更新:

  • INSERT — セカンダリインデックスごとに 1 書き込み
  • UPDATE — インデックス列の変更で関連インデックスをすべて更新
  • DELETE — 全インデックスからエントリ削除

InnoDB は change buffer で一部を遅延しますが、過剰なインデックスは書き込みスループットを下げます。

テーブルあたりのインデックス数 推奨
1–5 通常
6+ 冗長性と使用状況を監査

4.5 統計情報の更新 — ANALYZE TABLE

オプティマイザはカーディナリティに依存。大量データ変更後:

ANALYZE TABLE orders;

統計を更新 — テーブルは再構築しない。

4.6 再構築 / 領域回収 — OPTIMIZE TABLE

OPTIMIZE TABLE orders;

InnoDB では実質的にテーブルとインデックスを再構築 — 大きいテーブルでは遅い、慎重に。

4.7 不可視インデックス（MySQL 8.0+）

drop せずにインデックス削除を試す:

ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- 1〜2 週間観察、問題なければ DROP INDEX idx_status
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;  -- ロールバック

不可視インデックスは書き込みでは依然メンテナンス — オプティマイザから隠すだけ。

4.8 メンテナンスツール

オンライン DDL（組み込み）:

ALTER TABLE orders
  ADD INDEX idx_status (status),
  ALGORITHM=INPLACE, LOCK=NONE;

pt-online-schema-change / gh-ost — 超大規模テーブルや高書き込み向け（トレードオフ: 運用の複雑さ、権限、トリガー/binlog）。

4.9 ガイドライン

  • テーブルあたり 1–5 インデックスは通常。6+ は監査
  • 毎月: performance_schema と頻出クエリの EXPLAIN を組み合わせる
  • 本番でインデックスを drop する前は必ずチームと確認

第 4 章のまとめ: メンテナンスでループを閉じる — 計測 → 監査 → 不可視でテスト → 制御された drop。

まとめ

章 答える問い
1 — 複合 複数列インデックスはどう並べる？
2 — カバリング クエリはインデックスのみから読める？
3 — 全文検索 テキスト検索に FULLTEXT が必要？
4 — メンテナンス どのインデックスが余分か、残すか削除するか？

実務のワークフロー:

第 1 章 設計  →  第 2 章 EXPLAIN 検証  →  第 3 章（検索が必要なら）  →  第 4 章 定期監査

最良のインデックスは、プランナーが選び、EXPLAIN が証明し、本番で測定できるもの — 「速くなりそう」と思ったものではありません。

最後に

ここまでお読みいただき、誠にありがとうございます。

もし**「システム開発を依頼したい」「技術的な相談をしたい」「AIを活用した課題解決のアイデアがある」** といったお悩みがございましたら、ぜひ私たちTOMOSIA VIETNAMにご相談ください。

私たちの主なサービス

  • 💻 ソフトウェア受託開発（Web・アプリ・システム）
  • 🤖 AIソリューション（チャットボット、画像処理、LLM応用）
  • 📱 モバイルアプリ開発（iOS / Android）
  • 🔌 Fintech / IoT開発
  • 🔧 ブリッジSE（BrSE）支援（日本語でのコミュニケーションをスムーズに）

私たちの強み

  • ISO/IEC 27001（情報セキュリティ）認証取得済み – 安心して任せていただけます
  • 日本語対応チーム – 日本の文化・ビジネス習慣を理解したメンバーが直接対応
  • 「Win-Win, Happy Together」の文化 – 長期的なパートナーシップを大切にします

🔗 公式サイト: tomosia.com

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?