クラスタ化インデックスの効果を体感しよう

クラスタ化インデックスとは

クラスタ化インデックス（Clustered Index）とは、データベース内のテーブルの物理的なデータの並び順を、指定されたインデックスの順序に基づいて整理する手法です。クラスタ化インデックスが適用されると、テーブルのレコードはそのインデックスによって定義された順序で物理的にディスク上に配置されます。これにより、特定の条件に基づくデータ検索が効率的に行われるようになります。

クラスタ化インデックスの特徴

🗂️ 物理的な順序の決定

クラスタ化インデックスは、テーブルのレコードがどのようにディスクに配置されるかを決定します。インデックスが適用された列の値が近いレコードは、ディスク上でも近くに配置されるため、範囲検索や連続するデータの取得が非常に効率的です。

☝🏼 インデックスは1つのみ

一つのテーブルに対してクラスタ化インデックスは1つしか作成できません。これは、データの物理的な配置順序を決定するインデックスが複数存在することは不可能だからです。

🚀 パフォーマンスの向上

クラスタ化インデックスにより、ディスクI/Oが効率化され、特に大規模なデータセットに対する検索や範囲クエリのパフォーマンスが向上します。

🤔 自動的に再配置されるわけではない

データが頻繁に更新されると、クラスタ化インデックスによって配置されたデータの順序が乱れることがあります。この場合、再クラスタリング（再配置）を行うことで、最適なパフォーマンスを維持できます。

クラスタ化インデックスの利点

🔎 効率的な範囲検索

データが連続して配置されるため、特定の範囲のデータを効率的に検索できます。例えば、日付範囲や連続した数値に基づく検索が高速化されます。

💿 ディスクI/Oの削減

クラスタ化インデックスを使用すると、関連するデータがディスク上で連続して配置されるため、ディスクI/Oが削減され、クエリの応答時間が短縮されます。

🔥 クエリのパフォーマンス向上

特に多くの行にわたる範囲クエリや、特定の列に基づく検索クエリのパフォーマンスが向上します。

実機確認

実際に手を動かして、クラスタ化インデックスの効果を体感しましょう。

必要なもの

• Dokcer
• Python3
• pyenv
• pip
• psql

準備

任意のディレクトリにDockerfile, docker-compose.yml, init.sqlを作成します。

必要なファイルを作る

Dockerfile

FROM postgres:latest

# 環境変数を設定
ENV POSTGRES_DB=my_database
ENV POSTGRES_USER=my_user
ENV POSTGRES_PASSWORD=my_password

#　起動時にテーブルを作成する
COPY ./init.sql /docker-entrypoint-initdb.d/

dokcer-compose.yml

version: '3.8'

services:
  db:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: postgres_cluster_demo
    environment:
      - POSTGRES_DB=my_database
      - POSTGRES_USER=my_user
      - POSTGRES_PASSWORD=my_password
      - TZ=UTC

    ports:
      - "5432:5432"
    volumes:
      - db_lesson_data:/var/lib/postgresql/data

volumes:
  db_lesson_data:
    driver: local

init.sql

-- テーブル定義
CREATE TABLE IF NOT EXISTS people (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    country_code CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2
    date_of_birth DATE NOT NULL
);
-- インデックスをcoutry_codeに貼る
CREATE INDEX IF NOT EXISTS idx_country_code ON people (country_code);

dockerでpostgresを立ち上げる

docker compose up --build

サンプルレコードの挿入

1. pythonの仮想環境に入る。

pyenv activate myenv

2. 必要なモジュールをインストールする。

pip install psycopg2-binary

pip install facker

3. レコードの挿入
   今回はpythonを使って、ダミーレコードを作成と挿入をします。

seed.py

from faker import Faker
import psycopg2
from random import randint

# Fakerインスタンスを生成
fake = Faker()

# PostgreSQLに接続
conn = psycopg2.connect(
    host="localhost",
    database="my_database",  # あなたのデータベース名に置き換えてください
    user="my_user",          # あなたのデータベースユーザーに置き換えてください
    password="my_password"   # あなたのパスワードに置き換えてください
)
cursor = conn.cursor()

# ダミーデータを生成して挿入
for _ in range(10000):  # 10,000件のデータを生成
    first_name = fake.first_name()
    last_name = fake.last_name()
    country_code = fake.country_code()  # ISO 3166-1 alpha-2 コードを生成
    date_of_birth = fake.date_of_birth(minimum_age=18, maximum_age=90)

    cursor.execute(
        """
        INSERT INTO people (first_name, last_name, country_code, date_of_birth)
        VALUES (%s, %s, %s, %s)
        """,
        (first_name, last_name, country_code, date_of_birth)
    )

conn.commit()
cursor.close()
conn.close()

python seed.py

クラスタ化前の情報の確認

レコードの順序

ORDER BY ctidによって、レコードが格納されている順序で取得レコードを表示します。

SELECT * FROM people ORDER BY ctid;

以下のような結果が帰ってきます。
レコードが格納された順序になっていることが確認できます。

  id   | first_name  |  last_name  | country_code | date_of_birth
-------+-------------+-------------+--------------+---------------
     1 | Calvin      | Potter      | DJ           | 1948-12-03
     2 | Allison     | Hicks       | BO           | 1949-05-16
     3 | Karina      | Adams       | AU           | 1972-11-25
     4 | Monica      | Davis       | CR           | 1936-04-22
     5 | Sonia       | Foster      | GH           | 1966-05-11
     6 | Greg        | Lee         | US           | 1969-03-26
     7 | Larry       | Jennings    | BI           | 1998-05-18
     8 | Gail        | Thomas      | CI           | 1966-06-05
     9 | Charles     | Rasmussen   | VN           | 2000-10-17
    10 | Emily       | Foster      | SA           | 1942-10-19
    11 | Aaron       | Mcbride     | TL           | 1964-11-25
    12 | Allen       | Taylor      | RO           | 1968-08-15
    13 | Kimberly    | Ellis       | VN           | 1955-04-21
    14 | Erica       | Martin      | DM           | 1976-10-06
    15 | Joshua      | Sims        | SA           | 1969-08-29
    16 | Yolanda     | Lewis       | SV           | 1981-11-15
    17 | Rachel      | Cook        | GW           | 1948-08-21
    18 | Andrew      | Martinez    | SG           | 1994-11-29
    19 | Anne        | Aguilar     | KR           | 1995-01-20
    20 | Anne        | Baker       | SE           | 1970-01-12
    21 | Stephen     | Ross        | ST           | 1970-10-07
    22 | Teresa      | Harris      | NR           | 1984-05-25
    23 | Courtney    | Morgan      | TL           | 1968-04-05
    24 | Angela      | Morales     | ZA           | 1995-10-21
    25 | James       | Sherman     | CD           | 1956-01-23
    26 | Jasmine     | Strong      | CO           | 1944-02-11
    27 | Lauren      | Randall     | CA           | 1937-12-25
    28 | Samantha    | Moore       | KH           | 1961-12-01
    29 | Jeffrey     | Grant       | IE           | 1940-01-31
    30 | Ruth        | Rogers      | CI           | 1995-11-23
    ...

実行計画

ここではcountry_codeがUSのレコードを取得する実行計画を表示します。

EXPLAIN ANALYZE SELECT * FROM people WHERE country_code = 'US';

以下のような結果が返ってきます。

クラスタ化前のクエリの実行計画

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on people  (cost=4.63..70.95 rows=45 width=25) (actual time=0.219..0.359 rows=47 loops=1)
   Recheck Cond: (country_code = 'US'::bpchar)
   Heap Blocks: exact=35
   ->  Bitmap Index Scan on idx_country_code  (cost=0.00..4.62 rows=45 width=0) (actual time=0.196..0.196 rows=47 loops=1)
         Index Cond: (country_code = 'US'::bpchar)
 Planning Time: 0.297 ms
 Execution

クラスタリング

postgresに接続する

psql -h localhost -p 5432 -U my_user -d my_database

クラスタ化する

レコードの格納順序を、country_codeのインデックスに合わせます。

CLUSTER people USING idx_country_code;

クラスタ化後の情報の確認

物理的な物理的なテーブル内のデータの順序

以下のクエリを実行して、レコードの格納順序を確認します。

SELECT * FROM people ORDER BY ctid;

以下のような結果が返ってきます。
country_codeのインデックスの並びで格納されていることが確認できます。

  id   | first_name  |  last_name  | country_code | date_of_birth
-------+-------------+-------------+--------------+---------------
   247 | Tammy       | Wilson      | AD           | 1996-09-11
   255 | Katie       | Wise        | AD           | 1953-05-10
   531 | James       | Fox         | AD           | 1994-10-06
   963 | Sydney      | Obrien      | AD           | 1971-09-29
  1253 | Scott       | White       | AD           | 1986-04-16
  1553 | Dawn        | Jones       | AD           | 1989-06-23
  1880 | Joseph      | Goodman     | AD           | 1982-06-10
  2013 | Richard     | Hodges      | AD           | 1984-11-05
  2439 | Kathryn     | Maynard     | AD           | 1955-03-11
  2471 | Katherine   | Ortiz       | AD           | 2003-10-02
  2578 | Anthony     | Tanner      | AD           | 1991-10-04
  2677 | Stephen     | Villanueva  | AD           | 1942-01-05
  2828 | Margaret    | Beasley     | AD           | 1952-02-07
  2943 | Elizabeth   | Allen       | AD           | 1956-08-06
  2955 | George      | Macdonald   | AD           | 1970-05-21
  3013 | Ricardo     | Boyle       | AD           | 1941-06-30
  3035 | Derrick     | Martin      | AD           | 2004-05-24
  3104 | Barbara     | May         | AD           | 1967-03-22
  3229 | Carrie      | Thomas      | AD           | 1990-11-05
  3440 | Shelby      | Sparks      | AD           | 1942-04-28
  3466 | Jonathan    | Walker      | AD           | 1994-06-28
  3863 | Debra       | Harris      | AD           | 1960-09-18
  3986 | Gabriel     | Clark       | AD           | 1976-06-21
  4045 | Michael     | Diaz        | AD           | 1964-11-04
  4629 | Donald      | Reyes       | AD           | 1956-01-20
  4807 | Kelsey      | Patrick     | AD           | 1937-08-06
  4858 | Michelle    | Cooper      | AD           | 1959-06-27
  4972 | Samantha    | Scott       | AD           | 1940-03-20
  5159 | Justin      | Taylor      | AD           | 1942-03-09
  5246 | Thomas      | Owens       | AD           | 1992-01-07
  5282 | Douglas     | Neal        | AD           | 1991-09-24
  ...

実行計画を確認

再度、以下のクエリで実行計画を確認します。

EXPLAIN ANALYZE SELECT * FROM people WHERE country_code = 'US';

以下のような結果が返ってきます。

クラスタ化後のクエリの実行計画

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on people  (cost=4.63..70.95 rows=45 width=25) (actual time=0.106..0.110 rows=47 loops=1)
   Recheck Cond: (country_code = 'US'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_country_code  (cost=0.00..4.62 rows=45 width=0) (actual time=0.093..0.094 rows=47 loops=1)
         Index Cond: (country_code = 'US'::bpchar)
 Planning Time: 0.113 ms
 Execution Time: 0.160 ms
(7 rows)

クラスタリング前後の違い

結果を以下の表にまとめました。

比較項目	クラスタ化前	クラスタ化後
読み込んだブロック(ページ)数	35	1
ヒープスキャン	0.219 ms ～ 0.359 ms	0.106 ms ～ 0.110 ms
インデックススキャン	0.196 ms	0.093 ms ～ 0.094 ms
計画時間	0.297ms	0.113ms
実行時間	0.431 ms	0.160 ms

• 読み込んだブロック(ページ)数
  クラスタリング前は35個のブロックに分散されていたのに対し、クラスタリング後は1つのブロックにまとめられました。これがディスクアクセスの効率化につながっています。

• ヒープスキャン
  クラスタリング後は、データの位置が最適化されたため、スキャンの実行時間が約半分に短縮されています。

• インデックススキャン
  クラスタリング後、検索対象となるページ数が減少し、インデックススキャンにかかる時間が短縮されました。

• 計画時間
  クエリのプランニング時間も、クラスタリング後は短縮されています。

• 実行時間
  クエリ全体の実行時間が、クラスタリング後には約3分の1に短縮されています。

まとめ

クラスタリングは、特定の条件に基づいたデータ検索のパフォーマンスを改善する強力な手法であることが、この結果からも明らかです。データの物理的な配置を最適化することで、特に大規模データセットにおいて大きなパフォーマンス向上が期待できます。