インデックスとBツリー

データベースはどのようにして数百万行の中から 1 つのレコードを数分の一秒で見つけることができるのでしょうか?

デフォルトでは、SELECT * FROM customers WHERE city = 'Milano' を要求すると、Postgres は シーケンシャル スキャン を実行する必要があります。つまり、ディスクからテーブル全体を行ごとに物理的に読み取り、各都市を「ミラノ」と比較します。テーブルに数百万のレコードが保持されている場合、このプロセスによりパフォーマンスが低下します。

これらのボトルネックに対する主な構造的解決策は インデックス です。

インデックス (B ツリー)

索引は、大きな本の巻末にある分析索引とまったく同じように機能します。 「ミラノ」について書かれている箇所を見つけるために本全体を読む代わりに、「ミラノ」という単語が [p. 45, 114, 230] にリダイレクトされる後ろのページを調べます。 データベースは舞台裏で、該当する列ごとに順序付けされた、一致する行の物理ストレージへの直接ポインタを含む、この個別の非常にコンパクトなデータ構造 (多くの場合「B ツリー」) を構築します。

-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);

この 1 つの操作により Postgres エンジンがトリガーされ、バックグラウンドですべての city 値が抽出され、インデックスが作成されます。完了すると、後続のすべての SELECT ... WHERE city = '...' が実行されます。

主キーと一意性

主キー (PRIMARY KEY) と UNIQUE キー ルールが内部で秘密裏にインデックスを使用していることをご存知ですか? DB は行の挿入または変更時に電子メールまたは ID が決して重複しないようにする必要があるため、PostgreSQL はそれらの上に暗黙的な B ツリー インデックスを自動的に作成します。この方法では、一意性チェックにマイクロ秒かかります。

ID フィールドでの結合のパフォーマンスが非常に優れているのはこのためです。

インデックスの削除

重いクエリが変更され、インデックスがまったく参照されないことに気付いた場合は、インデックスを破棄して重要な領域を再利用することが賢明です。

DROP INDEX idx_customers_city;

あなたの番です

セカンダリ一意インデックスの探索