インデックスがボトルネックになるとき: 実際のデータベースアンチパターンの物語インデックスがボトルネックになるとき: 実際のデータベースアンチパターンの物語

まずは、よくある失敗例から始めましょう。「すべてにインデックスを付ける」アプローチです。これは魅力的な戦略ですよね？1つのインデックスが速度を上げるなら、10個のインデックスを付ければデータベースが飛ぶように速くなるはず！ああ、甘い考えです…

これがどれほどひどいことになるかの簡単な例を見てみましょう：


CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP,
    last_login TIMESTAMP,
    status VARCHAR(20)
);

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);
CREATE INDEX idx_last_login ON users(last_login);
CREATE INDEX idx_status ON users(status);

一見無害に見えますよね？でも違います。このインデックスの乱用は以下のような問題を引き起こします：

各インデックスを更新する必要があるため、INSERT、UPDATE、DELETE操作が遅くなる
ディスクスペースの使用量が増加する
クエリオプティマイザが混乱し、最適でない実行プランを選択する可能性がある

皆さん、覚えておいてください：インデックスはスパイスのようなものです。データベースの味を引き立てるために慎重に使いましょう、過剰に使ってはいけません。

複合インデックスのジレンマ

次に紹介するアンチパターンは、複合インデックスの使い方を誤解することです。WHERE句の各列に対して個別のインデックスを作成する開発者を見たことがありますが、複合インデックスの列の順序が重要であることを理解していません。

このクエリを考えてみてください：


SELECT * FROM orders
WHERE status = 'shipped'
AND created_at > '2023-01-01'
AND total_amount > 100;

3つの個別のインデックスを作成したくなるかもしれません：


CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_total_amount ON orders(total_amount);

しかし実際には、1つの複合インデックスの方が効率的です：


CREATE INDEX idx_status_created_total ON orders(status, created_at, total_amount);

ここでの鍵は、インデックスの選択性とデータベースがインデックスをどのように使用するかを理解することです。最も選択性の高い列（通常は最も多くの異なる値を持つ列）を複合インデックスの最初に配置するべきです。

「インデックスのみのスキャン」の幻想

ああ、インデックスのみのスキャン – クエリ最適化の聖杯。しかし、これに注意してください。過剰なインデックス作成とパフォーマンスの低下につながる危険な道に導かれる可能性があります。

この一見無害なテーブルを考えてみてください：


CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    stock INT
);

「すべての一般的なクエリをカバーするインデックスを作成しよう！」と思うかもしれません：


CREATE INDEX idx_products_all ON products(name, price, stock, description);

確かに、これでインデックスのみのスキャンが可能になるかもしれませんが、どんな代償を払うことになるでしょうか？インデックスにテーブル全体を複製したようなものです！これにより以下の問題が発生します：

大きなストレージオーバーヘッド
書き込み操作の遅延
キャッシュのためのメモリ使用量の増加

代わりに、最も重要なクエリに基づいて部分インデックスやカバリングインデックスを慎重に使用することを検討してください。

「設定して忘れる」症候群

私が遭遇した最も厄介なデータベースのアンチパターンの1つは、インデックスを「設定して忘れる」インフォマーシャル製品のように扱うことです。データベースのスキーマとクエリパターンは時間とともに進化し、インデックス戦略もそうするべきです。

実際の恐怖の話をしましょう：私はかつて、前のチームが初期のクエリパターンに基づいてインデックスを作成したプロジェクトを引き継ぎました。2年後、アプリケーションの使用方法は完全に変わっていました。しかし、古いインデックスは、忘れられたジムの会員権のように、リソースを消費しながら何の利益も提供していませんでした。

これを避けるために、定期的なインデックスの健康チェックを実施してください：

インデックス使用統計を監視する
定期的にインデックス戦略を見直し、更新する
PostgreSQLのpg_stat_statementsのようなツールを使用して、頻繁に実行されるクエリを特定し、それに応じて最適化する

PostgreSQLで未使用のインデックスを特定するためのクイッククエリを以下に示します：


SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
         pg_relation_size(i.indexrelid) DESC;

「万能」幻想

特に厄介な神話について話しましょう：インデックス作成には万能のアプローチがあるという考えです。データの分布やクエリパターンに関係なく、同じインデックス戦略を異なるテーブルに適用する開発者を見たことがあります。これは、ナッツを割るために大槌を使うようなものです。

例えば、ユーザーログインを追跡するテーブルを考えてみてください：


CREATE TABLE user_logins (
    id SERIAL PRIMARY KEY,
    user_id INT,
    login_time TIMESTAMP,
    ip_address INET,
    success BOOLEAN
);

user_idにインデックスを付ければすべてのクエリが速くなると思うかもしれません。しかし、最も一般的なクエリが実際には、過去1時間の失敗したログイン試行を探している場合はどうでしょうか？

この場合、部分インデックスの方がはるかに効果的かもしれません：


CREATE INDEX idx_failed_logins_recent ON user_logins (login_time)
WHERE success = FALSE AND login_time > (CURRENT_TIMESTAMP - INTERVAL '1 hour');

このインデックスは、特定のクエリパターンに対してはるかに小さく、効率的です。

自動インデックス作成ツールの危険性

AIと自動化の時代において、自動インデックス作成ツールにすべての重労働を任せるのは魅力的です。これらのツールは役立つことがありますが、盲目的に信頼するのは、GPSに従って崖から落ちるようなものです – 技術的には効率的ですが、実際には災難です。

自動インデックス作成ツールは、全体のパフォーマンスを考慮せずに個々のクエリのパフォーマンスに焦点を当てることがよくあります：

既存のインデックスと重複する冗長なインデックスを作成する可能性がある
システム全体の書き込み負荷を考慮しない
アプリケーションのビジネスコンテキストや将来の計画を理解できない

これらのツールに完全に依存するのではなく、独自の分析の出発点として使用してください。アプリケーションのニーズと将来の方向性を理解した上で、提案を組み合わせてください。

インデックスの隠れたコスト

あまり注目されないことについて話しましょう：インデックスの隠れたコストです。クエリのパフォーマンス向上に焦点を当てるのは簡単ですが、インデックスには独自の負担があります：

ストレージ要件の増加
キャッシュのためのメモリ使用量の増加
メンテナンスのための追加のCPU負荷
書き込み操作の遅延

これを説明するために、簡単な例を見てみましょう。10百万行のテーブルがあり、VARCHAR(255)列にインデックスを追加することにしたとします。インデックスだけでデータベースサイズに数ギガバイトを簡単に追加することができます。これを複数のテーブルにわたる複数のインデックスで掛け合わせると、ストレージとバックアップコストが大幅に増加します。

さらに、すべてのINSERT、UPDATE、またはDELETE操作はこれらのインデックスを更新する必要があります。かつては単純なテーブルへの追加だったものが、複数のBツリー構造を再編成することを伴うかもしれません。

これらのコストを軽減するために：

定期的にインデックスの使用状況を分析し、未使用のインデックスを削除する
大きなテーブルでは、完全なインデックスが必要ない場合に部分インデックスを使用することを検討する
SQL ServerのフィルタードインデックスやPostgreSQLの機能インデックスを使用して、適切な場合にインデックスサイズを削減する

重複インデックスの呪い

重複インデックスは、あなたが言ったことを繰り返す友人のようなものです – 冗長で少し迷惑です。それでも、開発者が意図せずに大幅に重複する複数のインデックスを作成したデータベースを数多く見てきました。

例えば：


CREATE INDEX idx_lastname_firstname ON employees(last_name, first_name);
CREATE INDEX idx_lastname ON employees(last_name);

この場合、idx_lastnameは冗長です。なぜなら、idx_lastname_firstnameはlast_nameだけを含むクエリにも使用できるからです。この冗長性はスペースを無駄にし、オプティマイザの仕事を複雑にします。

これに対抗するために：

定期的にインデックス定義を見直す
PostgreSQLのpg_stat_indexesやSQL Serverのsys.dm_db_index_usage_statsのようなツールを使用して冗長なインデックスを特定する
複合インデックスを設計する際に「左端のプレフィックス」ルールを考慮する

「すべての外部キーにインデックスを付ける」神話

データベースの世界には、常に外部キーにインデックスを付けるべきだという神話が根強くあります。多くの場合、これは良いアドバイスですが、盲目的に従うと不要なインデックスが作成され、パフォーマンスが低下する可能性があります。

例えば、「orders」テーブルに「customers」テーブルへの外部キーがあるシナリオを考えてみてください。顧客情報に基づいて注文をクエリすることがほとんどなく、「customers」テーブルが比較的小さい場合、外部キーにインデックスを付けても何の利益ももたらさないかもしれません。

すべての外部キーに自動的にインデックスを付けるのではなく：

クエリパターンを分析する
参照されるテーブルのサイズを考慮する
外部キー列のカーディナリティを考える

覚えておいてください、追加するインデックスはすべてトレードオフです。利益がコストを上回ることを確認してください。

ビットマップインデックスの誘惑

ビットマップインデックスは、特定のデータやクエリに対して非常に強力です。特にデータウェアハウジングのシナリオで。しかし、OLTPシステムで誤用されると、パフォーマンスの悪夢になる可能性があります。

かつて、忙しいOLTPシステムで高カーディナリティの列にビットマップインデックスを作成した開発者を見たことがあります。その結果、データベースがビットマップ構造を維持するのに苦労し、書き込み操作が停止しました。

ビットマップインデックスは以下に最適です：

低カーディナリティ（異なる値が少ない）列
更新が少ないテーブル
データウェアハウジングと分析クエリ

頻繁に更新されるシステムや高カーディナリティの列を扱っている場合は、Bツリーインデックスを使用してください。

関数ベースのインデックスの誘惑

関数ベースのインデックスは、最適化の武器として強力ですが、独自の落とし穴もあります。データのあらゆる変換に対して関数ベースのインデックスを作成する開発者を見たことがあります。

例えば：


CREATE INDEX idx_lower_email ON users (LOWER(email));
CREATE INDEX idx_substr_phone ON users (SUBSTR(phone_number, 1, 3));
CREATE INDEX idx_year_dob ON users (EXTRACT(YEAR FROM date_of_birth));

これらは特定のクエリを高速化するかもしれませんが、DML操作を大幅に遅くし、データベースを膨らませる可能性があります。さらに、クエリ内の関数がインデックス化された式と正確に一致しない場合、インデックスは使用されません。

関数ベースのインデックスを検討する際には：

最も一般的でパフォーマンスが重要なクエリと一致していることを確認する
書き込み操作に対する追加のオーバーヘッドに注意する
同じ結果が慎重なクエリ作成やアプリケーションレベルのロジックで達成できるかどうかを考慮する

カバリングインデックスの魅力

カバリングインデックス – クエリに必要なすべての列を含むインデックス – は、素晴らしいパフォーマンス向上を提供することができます。しかし、これは1つのシナリオに最適化することで他の問題を引き起こす典型的な例でもあります。

かつて、前のチームが最も一般的なクエリのために巨大なカバリングインデックスを作成したシステムに遭遇しました。クエリのパフォーマンスは確かに印象的でしたが、システム全体は以下の問題に苦しんでいました：

膨らんだデータベースサイズ
遅い書き込みパフォーマンス
バックアップと復元時間の増加

カバリングインデックスを検討する際には：

選択的に – 最も重要なクエリのみに使用する
そのサイズと書き込みパフォーマンスへの影響を監視する
クエリの書き換えや非正規化がより良い代替手段であるかどうかを考慮する

まとめ：インデックス啓蒙への道

見てきたように、データベース最適化への道は善意で舗装され、誤ったインデックス戦略の残骸で散らばっています。しかし、恐れることはありません、勇敢なデータ探検家よ！これらの悲劇と知恵の物語を武器に、データベースインデックスの危険な水域をよりよく航行する準備が整いました。

これらの重要なポイントを覚えておいてください：

インデックスは強力なツールですが、大きな力には大きな責任が伴います
インデックスの影響を常に考慮してください – 読み取りパフォーマンスだけでなく、書き込み、ストレージ、システム全体の健康にも
アプリケーションが進化するにつれて、定期的にインデックス戦略を見直し、改善する
万能の解決策はありません – あるシステムでうまくいくものが、別のシステムでは災難になるかもしれません
ツールと自動化を使用して意思決定を支援しますが、盲目的に信頼しないでください

データベースの最適化は、科学であると同時に芸術でもあります。それは、データ、クエリ、ビジネスニーズを深く理解することを必要とします。さあ、実験し、測定し、あなたのクエリが常に迅速で、インデックスが常に効率的であることを願っています！

あなた自身のデータベースの恐怖の物語がありますか？コメントに書いてください – データ管理の世界では、苦しみは仲間を愛します！

複合インデックスのジレンマ

「インデックスのみのスキャン」の幻想

「設定して忘れる」症候群

「万能」幻想

自動インデックス作成ツールの危険性

インデックスの隠れたコスト

重複インデックスの呪い

「すべての外部キーにインデックスを付ける」神話

ビットマップインデックスの誘惑

関数ベースのインデックスの誘惑

カバリングインデックスの魅力

まとめ：インデックス啓蒙への道

このカテゴリの詳細 Programming

バックエンド開発のためのRust: 本番環境での経験

高度なGoスケジューラのチューニング: HFTワークロードのためのゴルーチンをOSスレッドに固定する

Firecracker MicroVMs: OS抽象化でサーバーレスを強化

エキゾチックなファイルシステムを深掘り: Ceph、MooseFS、JuiceFS

私たちのコミュニティに参加 👋