⚠️ Step 16: インデックスの落とし穴

インデックスが効かないケースを学ぼう!

📋 このステップで学ぶこと

インデックスが効かない6つのパターン
列に関数をかけると使われない理由
暗黙の型変換の危険性
インデックスのメンテナンス方法
過剰なインデックスの弊害

🚫 1. インデックスが効かないケースとは

1-1. せっかく作ったのに使われない!

Step 14〜15でインデックスの効果を学びました。しかし、インデックスを作っても使われないケースがたくさんあります。

これを知らないと、「インデックスを作ったのに全然速くならない!」という事態になってしまいます。

⚠️ インデックスが効かない6つのパターン

列に関数をかける（UPPER、LENGTH、SUBSTRなど）
列で演算する（age + 5 > 30 など）
暗黙の型変換が発生する
NOT、!= を使う
LIKE の中間・後方一致（’%文字列’）
OR条件を使う（場合による）

それぞれのパターンを詳しく見ていきましょう。

🔧 2. 列に関数をかけるとインデックスが効かない

2-1. 問題の例

customer_name列にインデックスを作成したとします。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_customer_name ON customers(customer_name);

以下のクエリでは、インデックスが使われません。

※横にスクロールできます

— ❌ インデックスが使われない例
SELECT * FROM customers WHERE UPPER(customer_name) = ‘TANAKA’;
SELECT * FROM customers WHERE LENGTH(customer_name) > 5;
SELECT * FROM customers WHERE SUBSTR(customer_name, 1, 2) = ‘田中’;

一方、以下のクエリでは、インデックスが使われます。

※横にスクロールできます

— ✅ インデックスが使われる例
SELECT * FROM customers WHERE customer_name = ‘田中’;
SELECT * FROM customers WHERE customer_name LIKE ‘田中%’;

2-2. なぜ関数をかけると使われないのか

インデックスは元の値で作られています。関数で変換した後の値は、インデックスに含まれていません。

💡 本の索引で例えると…

本の索引に「田中」という単語が載っているとします。

「田中」を探す → 索引で見つかる ✅
「TANAKA」を探す → 索引には「田中」しかない、見つからない ❌
「2文字の名前」を探す → 索引には文字数の情報がない、全ページを確認する必要がある ❌

インデックスの中身イメージ:

インデックスの値	行の位置
佐藤花子	行2
田中太郎	行1
鈴木一郎	行3

インデックスには「田中太郎」という元の値しかありません。
「TANAKA TARO」や「4」（文字数）という情報はないのです。

2-3. よくある関数の例

📌 インデックスが効かなくなる関数の例

関数	使用例	影響
UPPER / LOWER	WHERE UPPER(name) = ‘TANAKA’	❌ 使われない
LENGTH	WHERE LENGTH(name) > 5	❌ 使われない
SUBSTR	WHERE SUBSTR(name, 1, 2) = ‘田中’	❌ 使われない
DATE / strftime	WHERE DATE(created_at) = ‘2024-01-01’	❌ 使われない
COALESCE	WHERE COALESCE(phone, ”) = ”	❌ 使われない

➕ 3. 列で演算するとインデックスが効かない

3-1. 問題の例

age列にインデックスを作成したとします。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_age ON customers(age);

以下のクエリでは、インデックスが使われません。

※横にスクロールできます

— ❌ インデックスが使われない例（列で演算している）
SELECT * FROM customers WHERE age + 5 > 30;
SELECT * FROM customers WHERE age * 2 = 60;
SELECT * FROM customers WHERE age / 10 >= 3;

3-2. 解決策：右辺で計算する

列の方はそのままにして、比較する値の方で計算しましょう!

※横にスクロールできます

— ✅ インデックスが使われる（右辺で計算）
SELECT * FROM customers WHERE age > 25;   — 30 – 5 = 25
SELECT * FROM customers WHERE age = 30;   — 60 / 2 = 30
SELECT * FROM customers WHERE age >= 30;  — 3 * 10 = 30

💡 書き換えのコツ

「列 ○○ 値」の形になるように変形します。
列には何も足したり掛けたりせず、値の方を計算して条件を満たす値を求めます。

3-3. 変換の例

書き換えの例:

変換前（❌）	変換後（✅）	計算
age + 5 > 30	age > 25	30 – 5 = 25
age * 2 = 60	age = 30	60 / 2 = 30
price * 1.1 > 1100	price > 1000	1100 / 1.1 = 1000
salary – 10000 >= 290000	salary >= 300000	290000 + 10000

🔄 4. 暗黙の型変換に注意

4-1. 暗黙の型変換とは

暗黙の型変換とは、データベースが自動的に型を変換することです。例えば、数値型の列に文字列で比較すると、内部で変換が行われます。

※横にスクロールできます

— customer_idはINTEGER型
CREATE INDEX idx_customer_id ON customers(customer_id);

— ❌ 暗黙の型変換が発生（インデックスが使われない可能性）
SELECT * FROM customers WHERE customer_id = ‘123’;  — 文字列で比較

— ✅ 正しい型で比較（インデックスが使われる）
SELECT * FROM customers WHERE customer_id = 123;    — 数値で比較

4-2. なぜ暗黙の型変換は危険なのか

⚠️ 暗黙の型変換の問題点

インデックスが使われない：型変換のためにフルスキャンになる
予期しない結果：’123′ と 123 の比較で思わぬ結果になる場合がある
パフォーマンス低下：変換処理自体にも時間がかかる
バグの原因：気づきにくいエラーにつながる

4-3. よくある型変換の例

型変換が発生するパターン:

列の型	❌ 型変換あり	✅ 正しい比較
INTEGER	WHERE id = ‘123’	WHERE id = 123
TEXT	WHERE code = 123	WHERE code = ‘123’
REAL	WHERE price = ‘1000’	WHERE price = 1000.0

4-4. 型を確認する方法

テーブルの列の型を確認してから、正しい型で比較しましょう。

※横にスクロールできます

— テーブルの構造を確認
PRAGMA table_info(‘customers’);

実行結果イメージ:

cid	name	type	dflt_value	pk
0	customer_id	INTEGER	NULL	1
1	customer_name	TEXT	NULL	0
2	email	TEXT	NULL	0

type 列を見れば、各列のデータ型がわかります。

特定の値の型を確認するには、typeof関数を使います。

※横にスクロールできます

— 値の型を確認
SELECT 
    customer_id,
    typeof(customer_id) AS id_type,
    email,
    typeof(email) AS email_type
FROM customers 
LIMIT 1;

📌 型変換を防ぐコツ

列の型を確認してから比較する
数値型には数値で比較する
文字列型には引用符で囲んで比較する
日付はISO8601形式（’2024-01-01’）で統一する

🚫 5. NOT、!= を使うとインデックスが効きにくい

5-1. 問題の例

status列にインデックスを作成したとします。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_status ON orders(status);

以下のクエリでは、インデックスが効きにくいです。

※横にスクロールできます

— ❌ インデックスが効きにくい例
SELECT * FROM orders WHERE status != ‘完了’;
SELECT * FROM orders WHERE status NOT IN (‘完了’, ‘キャンセル’);
SELECT * FROM orders WHERE NOT (status = ‘完了’);

5-2. なぜNOT条件は効きにくいのか

NOT条件は「これ以外」を探すので、大部分のデータを読む必要があります。

💡 例：注文ステータスの分布

ステータス	件数	割合
完了	80,000件	80%
処理中	10,000件	10%
発送済み	8,000件	8%
キャンセル	2,000件	2%

status != '完了' → 20,000件（20%）を取得
status = '処理中' → 10,000件（10%）を取得

NOT条件は「それ以外全部」なので、結局多くのデータを読む必要があります。

5-3. 解決策：肯定形に書き換える

NOT条件を肯定形（=、IN）に書き換えると、インデックスが効果的に使われます。

※横にスクロールできます

— ❌ NOT条件（効きにくい）
SELECT * FROM orders WHERE status != ‘完了’;

— ✅ 肯定形に書き換え
SELECT * FROM orders WHERE status IN (‘処理中’, ‘発送済み’, ‘キャンセル’);

ポイント:

「完了以外」を探すのではなく、「処理中、発送済み、キャンセル」を探す形に書き換えます。ステータスの種類が限られている場合に有効です。

🔍 6. LIKEの中間・後方一致は効かない

6-1. 前方一致だけがインデックスを使える

email列にインデックスを作成したとします。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_email ON customers(email);

※横にスクロールできます

— ✅ 前方一致はインデックスが使われる
SELECT * FROM customers WHERE email LIKE ‘tanaka%’;

— ❌ 中間一致はインデックスが使われない
SELECT * FROM customers WHERE email LIKE ‘%tanaka%’;

— ❌ 後方一致もインデックスが使われない
SELECT * FROM customers WHERE email LIKE ‘%@example.com’;

6-2. なぜ前方一致だけOKなのか

インデックスは辞書順（あいうえお順）に並んでいます。

💡 辞書で例えると…

辞書で単語を探す場合を考えてみましょう。

「tanaka〜」で始まる単語 → 辞書の「t」の部分を見れば見つかる ✅
「〜tanaka〜」を含む単語 → 全ページを読む必要がある ❌
「〜.com」で終わる単語 → 全ページを読む必要がある ❌

インデックスの中身イメージ:

abc@example.com   → 行5
satou@test.com    → 行2
suzuki@example.com → 行3
tanaka@example.com → 行1  ← 'tanaka%' で見つかる!
yamada@test.com   → 行4

アルファベット順に並んでいるので、「tanaka」で始まるものは連続した範囲にあります。
しかし、「@example.com」で終わるものはバラバラに散らばっています。

6-3. 中間・後方一致が必要な場合の代替案

📌 中間・後方一致を高速化する方法

逆順の列を追加：後方一致を前方一致に変換
全文検索（FTS）：SQLiteのFTS5機能を使う
諦めて全件スキャン：データ量が少なければ許容

例えば、「@example.com」で終わるメールアドレスを探したい場合:

※横にスクロールできます

— 逆順の列を追加
ALTER TABLE customers ADD COLUMN email_reverse TEXT;
UPDATE customers SET email_reverse = 
    — 文字列を逆順にする（SQLiteには標準関数がないので、アプリ側で処理）
    — 例: ‘tanaka@example.com’ → ‘moc.elpmaxe@aknat’

— 逆順列にインデックスを作成
CREATE INDEX idx_email_reverse ON customers(email_reverse);

— 後方一致を前方一致に変換して検索
— ‘@example.com’ → ‘moc.elpmaxe@’ で前方一致検索
SELECT * FROM customers WHERE email_reverse LIKE ‘moc.elpmaxe@%’;

⚡ 7. OR条件はインデックスが効きにくい場合がある

7-1. 問題の例

emailとphone_numberにそれぞれインデックスを作成したとします。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_email ON customers(email);
CREATE INDEX idx_phone ON customers(phone_number);

以下のORクエリでは、両方のインデックスを同時に使えない場合があります。

※横にスクロールできます

— ❌ ORは両方のインデックスを使えない場合がある
SELECT * FROM customers 
WHERE email = ‘test@example.com’ 
   OR phone_number = ‘090-1234-5678’;

7-2. 解決策：UNIONで分ける

OR条件をUNIONで分けると、それぞれのインデックスを確実に使えます。

※横にスクロールできます

— ✅ UNIONで分けて、それぞれインデックスを使う
SELECT * FROM customers WHERE email = ‘test@example.com’
UNION
SELECT * FROM customers WHERE phone_number = ‘090-1234-5678’;

クエリの解説:

SELECT ... WHERE email = ... → idx_emailを使って検索
UNION → 2つの結果を結合（重複は除去）
SELECT ... WHERE phone_number = ... → idx_phoneを使って検索

📌 UNION vs UNION ALL

UNION → 重複を除去（両方の条件に一致する行は1回だけ）
UNION ALL → 重複を許容（両方の条件に一致する行は2回出る）

通常はUNIONを使いますが、重複がないと分かっている場合はUNION ALLの方が高速です。

🔧 8. 関数を使いたい場合の対処法

8-1. 問題：大文字小文字を区別しない検索

メールアドレスを大文字小文字を区別せずに検索したい場合、以下のクエリを書きたくなります。

※横にスクロールできます

— ❌ インデックスが使われない
SELECT * FROM customers WHERE UPPER(email) = ‘TANAKA@EXAMPLE.COM’;

しかし、これではインデックスが使われません。解決策を3つ紹介します。

8-2. 解決策1：正規化した列を追加（おすすめ）

大文字に変換した列を追加し、その列にインデックスを作成します。

ステップ1：正規化した列を追加

※横にスクロールできます

— email_upper列を追加
ALTER TABLE customers ADD COLUMN email_upper TEXT;

ステップ2：既存データを更新

※横にスクロールできます

— 全行のemail_upperを更新
UPDATE customers SET email_upper = UPPER(email);

ステップ3：インデックスを作成

※横にスクロールできます

— 正規化した列にインデックスを作成
CREATE INDEX idx_email_upper ON customers(email_upper);

ステップ4：検索に使用

※横にスクロールできます

— ✅ インデックスが使われる
SELECT * FROM customers WHERE email_upper = ‘TANAKA@EXAMPLE.COM’;

📌 注意点

新しいデータを挿入する際は、email_upper列も一緒に更新する必要があります。トリガーを使うと自動化できます（後述）。

8-3. 解決策2：COLLATE NOCASE（英語のみ）

SQLiteでは、COLLATE NOCASEを使うと大文字小文字を区別しない比較ができます。

※横にスクロールできます

— 検索時に指定
SELECT * FROM customers 
WHERE email = ‘tanaka@example.com’ COLLATE NOCASE;

ポイント:

COLLATE NOCASEは英語（ASCII文字）のみで有効です。日本語には効きません。

8-4. 解決策3：トリガーで自動更新

解決策1の問題点（新規データでemail_upperを更新する必要がある）を解決するために、トリガーを使います。

※横にスクロールできます

— INSERT時に自動でemail_upperを設定するトリガー
CREATE TRIGGER trg_email_upper_insert
AFTER INSERT ON customers
BEGIN
    UPDATE customers 
    SET email_upper = UPPER(NEW.email)
    WHERE rowid = NEW.rowid;
END;

— UPDATE時にも自動で更新するトリガー
CREATE TRIGGER trg_email_upper_update
AFTER UPDATE OF email ON customers
BEGIN
    UPDATE customers 
    SET email_upper = UPPER(NEW.email)
    WHERE rowid = NEW.rowid;
END;

8-5. 日付の一部を使った検索

「2024年1月のデータ」を検索したい場合、以下のように書きたくなります。

※横にスクロールできます

— ❌ 関数を使う（インデックスが使われない）
SELECT * FROM orders 
WHERE strftime(‘%Y-%m’, order_date) = ‘2024-01’;

これは範囲検索に書き換えられます。

※横にスクロールできます

— ✅ 範囲検索に変換（インデックスが使われる）
SELECT * FROM orders 
WHERE order_date >= ‘2024-01-01’ 
  AND order_date < '2024-02-01';

よくある日付検索の書き換え:

目的	❌ 関数を使う	✅ 範囲検索
2024年のデータ	strftime(‘%Y’, date) = ‘2024’	date >= ‘2024-01-01’ AND date < '2025-01-01'
2024年1月のデータ	strftime(‘%Y-%m’, date) = ‘2024-01’	date >= ‘2024-01-01’ AND date < '2024-02-01'
月曜日のデータ	strftime(‘%w’, date) = ‘1’	（範囲検索では表現できない）

💡 関数を使わずに書き換えるコツ

多くの場合、日付関数を使わずに範囲検索で書き換えられます。これがインデックスを効かせる最も簡単で確実な方法です!

🔧 9. インデックスのメンテナンス

9-1. インデックスは劣化する

インデックスは作ったら終わりではありません。長期間使用すると、断片化して遅くなることがあります。

💡 断片化とは?

データの追加・削除を繰り返すと、インデックス内のデータがバラバラに配置され、読み込み効率が下がります。

本の例で言えば、ページの追加・削除を繰り返すと、索引が飛び飛びのページを指すようになり、探しにくくなるイメージです。

断片化のイメージ:

断片化なし（理想的）:

インデックス: [A][B][C][D][E][F][G][H]
データ位置:   1  2  3  4  5  6  7  8
→ 連続して読める（高速）

断片化あり（劣化）:

インデックス: [A][B][C][D][E][F][G][H]
データ位置:   5  1  8  3  2  7  4  6
→ あちこちに飛び飛び（遅い）

9-2. REINDEX：インデックスの再構築

REINDEXコマンドで、インデックスを再構築して断片化を解消できます。

※横にスクロールできます

— 全インデックスを再構築
REINDEX;

クエリの解説:

REINDEX → データベース内の全インデックスを再構築
断片化が解消され、検索速度が回復する

特定のインデックスやテーブルだけを再構築することもできます。

※横にスクロールできます

— 特定のインデックスだけ再構築
REINDEX idx_email;

— 特定のテーブルのインデックスを全て再構築
REINDEX customers;

9-3. VACUUM：データベース全体の最適化

VACUUMコマンドは、データベース全体を最適化します。REINDEXより強力ですが、時間がかかります。

※横にスクロールできます

— データベース全体を最適化
VACUUM;

VACUUMの効果:

断片化を解消：データを連続した領域に再配置
削除領域を回収：DELETEで削除したデータの領域を再利用可能に
ファイルサイズを縮小：不要な空間を削除
インデックスも最適化：全インデックスを再構築

⚠️ VACUUMの注意点

時間がかかる：大きいDBでは数分〜数時間
ロックされる：VACUUM中は書き込みができない
ディスク容量が必要：一時的にDB全体の2倍の空き容量が必要
本番環境では注意：アクセスが少ない時間帯に実行

9-4. ANALYZE：統計情報の更新

データベースは統計情報を使って、どのインデックスを使うかを判断します。データが大きく変わったら、ANALYZEで統計情報を更新しましょう。

※横にスクロールできます

— 統計情報を更新
ANALYZE;

クエリの解説:

ANALYZE → 全テーブルの統計情報を収集・更新
クエリオプティマイザが最適な実行計画を選べるようになる

特定のテーブルだけを分析することもできます。

※横にスクロールできます

— 特定のテーブルだけ分析
ANALYZE customers;

— 統計情報を確認
SELECT * FROM sqlite_stat1;

📌 ANALYZEが重要な理由

統計情報が古いと、クエリオプティマイザが間違った判断をして、インデックスを使わなかったり、遅いインデックスを選んだりします。データが大幅に変わったら、必ずANALYZEを実行しましょう。

9-5. メンテナンススケジュールの目安

推奨メンテナンス頻度:

コマンド	頻度	実行時間	影響
ANALYZE	週1回〜月1回	数秒〜数分	ほぼなし
REINDEX	月1回〜3ヶ月に1回	数分〜10分程度	軽いロック
VACUUM	3ヶ月〜半年に1回	10分〜数時間	重いロック

※ データの更新頻度や量によって調整してください。更新が多いほど、メンテナンス頻度を上げます。

⚖️ 10. 過剰なインデックスの弊害

10-1. インデックスは万能ではない

「インデックスを作れば速くなる」と思って、全ての列にインデックスを作るのは逆効果です!

⚠️ インデックスが多すぎる弊害

INSERT/UPDATE/DELETEが遅くなる
ディスク容量を圧迫する
メモリ使用量が増える
メンテナンスの手間が増える
バックアップの時間が長くなる

10-2. 更新処理への影響

インデックスが5個あるテーブルにINSERTする場合を考えてみましょう。

INSERT時の処理:

データをテーブルに挿入
インデックス1を更新
インデックス2を更新
インデックス3を更新
インデックス4を更新
インデックス5を更新

→ 6倍の書き込みが発生!

UPDATE、DELETEでも同様に、全てのインデックスを更新する必要があります。

💡 具体例：1万件のINSERT

インデックス数	書き込み回数	所要時間（目安）
0個	1万回	1秒
3個	4万回	3秒
5個	6万回	5秒
10個	11万回	10秒

インデックスが増えるほど、INSERT/UPDATE/DELETEが遅くなります。

10-3. ディスク容量への影響

インデックスはディスク容量を消費します。テーブルの半分〜同等のサイズになることもあります。

ディスク容量の例:

項目	サイズ
テーブル本体	100MB
インデックス1（email）	20MB
インデックス2（name）	15MB
インデックス3（prefecture, city）	18MB
合計	153MB（1.5倍!）

10-4. 不要なインデックスを見つける

📌 削除を検討すべきインデックス

作成後一度も使われていないインデックス
重複しているインデックス（他のインデックスでカバー可能）
カーディナリティが低い列のインデックス
検索より更新が多い列のインデックス

10-5. 重複インデックスの例

※横にスクロールできます

— ❌ 重複している例
CREATE INDEX idx_prefecture ON customers(prefecture);
CREATE INDEX idx_location ON customers(prefecture, city);

— idx_location (prefecture, city) があれば、
— prefecture単独の検索もカバーできる
— → idx_prefecture は不要!

※横にスクロールできます

— ✅ 重複を解消
DROP INDEX idx_prefecture;

— idx_location だけで以下の両方をカバー:
— WHERE prefecture = ‘東京都’
— WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’

10-6. 最適なインデックス数の目安

💡 目安となる指標

テーブル規模	推奨インデックス数
小規模（〜1万行）	3〜5個
中規模（1万〜100万行）	5〜8個
大規模（100万行〜）	8〜12個

※ あくまで目安です。実際のクエリパターンに基づいて判断してください。

10-7. インデックス設計のバランス

💡 トレードオフを理解する

インデックスを増やすと…	インデックスを減らすと…
✅ 検索（SELECT）が速くなる	✅ 更新（INSERT/UPDATE/DELETE）が速くなる
❌ 更新が遅くなる	❌ 検索が遅くなる
❌ ディスク容量が増える	✅ ディスク容量が減る
❌ メンテナンス手間が増える	✅ メンテナンスが楽になる

検索が多いシステム（Webサイトなど）→ インデックス多め
更新が多いシステム（ログ収集など）→ インデックス少なめ

📝 練習問題

インデックスの落とし穴を理解しましょう!

問題 1基本

インデックスが使われないクエリ

以下のクエリで、インデックスが使われないものを全て選んでください。
インデックス: CREATE INDEX idx_age ON customers(age);
A) SELECT * FROM customers WHERE age = 30;
B) SELECT * FROM customers WHERE age + 5 > 35;
C) SELECT * FROM customers WHERE age BETWEEN 20 AND 30;
D) SELECT * FROM customers WHERE age * 2 = 60;

解答: BとD

A：✅ 使われる（等価条件）
B：❌ 使われない（列に演算 age + 5）
C：✅ 使われる（範囲条件）
D：❌ 使われない（列に演算 age * 2）

解説:

列に演算をすると、インデックスは使われません。

B → age > 30（35 – 5 = 30）と書き換え
D → age = 30（60 / 2 = 30）と書き換え

問題 2基本

関数を使った検索の書き換え

以下のクエリを、インデックスが使われるように書き換えてください。
SELECT * FROM orders WHERE strftime('%Y', order_date) = '2024';

※横にスクロールできます

— ✅ 範囲検索に書き換え
SELECT * FROM orders 
WHERE order_date >= ‘2024-01-01’ 
  AND order_date < '2025-01-01';

解説:

関数（strftime）を使わずに範囲検索で書き換えると、インデックスが使われます。「2024年のデータ」は「2024-01-01以上、2025-01-01未満」と表現できます。

問題 3基本

LIKE検索

以下のクエリで、インデックスが使われるものはどれですか?
インデックス: CREATE INDEX idx_email ON customers(email);
A) WHERE email LIKE 'tanaka%'
B) WHERE email LIKE '%@example.com'
C) WHERE email LIKE '%tanaka%'

解答: A のみ

A：✅ 前方一致（使われる）
B：❌ 後方一致（使われない）
C：❌ 中間一致（使われない）

解説:

インデックスは辞書順に並んでいるため、前方一致（’tanaka%’）だけが効率的に検索できます。後方一致や中間一致では、全件スキャンが必要になります。

問題 4応用

暗黙の型変換

以下のクエリで、暗黙の型変換が発生するものを選んでください。
customer_idはINTEGER型です。
A) WHERE customer_id = 123
B) WHERE customer_id = '123'
C) WHERE customer_id IN (101, 102, 103)
D) WHERE CAST(customer_id AS TEXT) = '123'

解答: BとD

A：✅ 型一致（変換なし）
B：❌ 暗黙の型変換が発生（文字列→数値）
C：✅ 型一致（変換なし）
D：❌ 明示的な型変換（インデックスが使われない）

解説:

数値型の列に文字列で比較すると、型変換が発生してインデックスが使われない可能性があります。Dは明示的に型変換しているので、同様にインデックスが使われません。

問題 5応用

NOT条件の書き換え

以下のクエリを、インデックスが使われやすい形に書き換えてください。
SELECT * FROM orders WHERE status != '完了';
statusの値: ‘処理中’, ‘発送済み’, ‘完了’, ‘キャンセル’

※横にスクロールできます

— ✅ 肯定形に書き換え
SELECT * FROM orders 
WHERE status IN (‘処理中’, ‘発送済み’, ‘キャンセル’);

解説:

NOT条件（!= ‘完了’）は大部分のデータを読む必要があるため遅くなります。肯定形（IN）で書き換えると、インデックスが効果的に使われます。

問題 6応用

OR条件の書き換え

以下のクエリを、両方のインデックスが使われる形に書き換えてください。
SELECT * FROM customers WHERE email = 'test@example.com' OR phone = '090-1234-5678';

※横にスクロールできます

— ✅ UNIONで分けて、それぞれインデックスを使う
SELECT * FROM customers WHERE email = ‘test@example.com’
UNION
SELECT * FROM customers WHERE phone = ‘090-1234-5678’;

解説:

ORを使うと両方のインデックスを同時に使えない場合があります。UNIONで分けると、それぞれのインデックスを確実に使えます。

問題 7チャレンジ

重複インデックスの特定

以下のインデックスのうち、不要なものを特定してください。
1. CREATE INDEX idx1 ON customers(prefecture);
2. CREATE INDEX idx2 ON customers(prefecture, city);
3. CREATE INDEX idx3 ON customers(city);
4. CREATE INDEX idx4 ON customers(email);

解答: idx1 は不要

※横にスクロールできます

— idx2 (prefecture, city) があれば、
— prefecture単独の検索もカバーできる（左端一致の原則）

— 残すべきインデックス:
— idx2: prefecture と prefecture+city の検索用
— idx3: city単独の検索用（idx2ではカバーできない）
— idx4: email検索用

— 削除すべき
DROP INDEX idx1;

解説:

複合インデックス (prefecture, city) があれば、左端一致の原則により prefecture だけの検索もカバーできます。idx1 は重複しているため不要です。

問題 8チャレンジ

大文字小文字を区別しない検索

email列で大文字小文字を区別せずに検索したいです。インデックスを効かせる方法を提案してください。

解決策: 正規化した列を追加

※横にスクロールできます

— ステップ1: email_lower列を追加
ALTER TABLE customers ADD COLUMN email_lower TEXT;

— ステップ2: 既存データを更新
UPDATE customers SET email_lower = LOWER(email);

— ステップ3: インデックスを作成
CREATE INDEX idx_email_lower ON customers(email_lower);

— ステップ4: 検索に使用
SELECT * FROM customers 
WHERE email_lower = LOWER(‘Tanaka@Example.com’);

— ステップ5: トリガーで自動更新（新規データ用）
CREATE TRIGGER trg_email_lower_insert
AFTER INSERT ON customers
BEGIN
    UPDATE customers 
    SET email_lower = LOWER(NEW.email)
    WHERE rowid = NEW.rowid;
END;

解説:

関数（UPPER、LOWER）を使うとインデックスが効かないため、正規化した列を追加する方法が確実です。トリガーを使えば、新規データでも自動的に更新されます。

📝 Step 16 のまとめ

✅ 学んだこと

インデックスが使われない6つのパターン
列に関数をかけないことの重要性
暗黙の型変換を防ぐ方法
NOT条件を肯定形に書き換える方法
LIKE検索は前方一致のみインデックスが効く
インデックスのメンテナンス（ANALYZE、REINDEX、VACUUM）
過剰なインデックスの弊害

⚠️ 絶対に避けるべきパターン

WHERE句の列に関数をかける
WHERE句の列で演算する
型が違う値で比較する
全ての列にインデックスを作る
メンテナンスを一切しない

📌 インデックス最適化のチェックリスト

☐ WHERE句の列に関数をかけていないか?
☐ 列で演算していないか?
☐ 型は一致しているか?
☐ NOT条件を肯定形に書き換えられるか?
☐ LIKE検索は前方一致になっているか?
☐ 不要なインデックスはないか?
☐ 定期的にメンテナンスしているか?

❓ よくある質問

Q1: 関数を使いたい場合は諦めるしかない?

いいえ。正規化した列を追加する方法があります。例えば、UPPER(email) で検索したいなら、email_upper という列を追加してインデックスを作成します。トリガーを使えば自動更新も可能です。

Q2: インデックスが使われているか確認する方法は?

EXPLAIN QUERY PLAN で確認できます。

※横にスクロールできます

EXPLAIN QUERY PLAN SELECT * FROM customers WHERE email = ‘test@example.com’;

結果に「USING INDEX」と表示されればインデックスが使われています。「SCAN TABLE」と表示されていれば全件スキャンです。

Q3: VACUUMは本当に必要?

データの削除や更新が多い場合は必要です。VACUUMをしないと、削除したデータの領域が再利用されず、ファイルサイズが膨らみ続けます。ただし、読み取り専用のデータベースでは不要です。

Q4: インデックスは何個まで作れる?

技術的には制限ありませんが、実用的には5〜10個が目安です。それ以上作ると、更新処理が遅くなり、メンテナンスの手間も増えます。

Q5: OR条件でインデックスを使う方法は?

UNION で分けて検索する方法があります。

※横にスクロールできます

SELECT * FROM t WHERE a = 1 
UNION 
SELECT * FROM t WHERE b = 2;

こうすれば、aとbそれぞれのインデックスを使えます。

Q6: 中間一致や後方一致を高速化できない?

全文検索インデックス（FTS）を使う方法があります。SQLiteではFTS5という機能で、任意の単語を高速に検索できます。ただし、通常のインデックスより設定が複雑です。

🎓 次のステップでは

Step 17: 実行計画の読み方では、EXPLAIN QUERY PLANを使って、クエリがどのように実行されるかを確認する方法を学びます!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 16

📋 過去のメモ一覧 ▼