PrepBro
← Назад к вопросам

Что такое кластеризованный индекс SQL?

3.0 Senior🔥 121 комментариев
#DevOps и инфраструктура#Django

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Clustered Index — Кластеризованный индекс в SQL

Clustered Index (Кластеризованный индекс) — это специальный индекс, который определяет физический порядок хранения данных в таблице на диске. Таблица может иметь только один кластеризованный индекс, потому что данные могут быть отсортированы только одним способом. Обычно это PRIMARY KEY.

Основное различие: Clustered vs Non-Clustered

Clustered Index:

  • Определяет физический порядок строк на диске
  • Можно только один на таблицу
  • Поиск по clustered index ОЧЕНЬ быстрый (попадаем прямо на нужные данные)
  • Обычно это PRIMARY KEY

Non-Clustered Index:

  • Отдельная структура, которая ссылается на строки
  • Может быть много (до 999 в SQL Server)
  • Требует дополнительный "прыжок" к исходным данным (если нужны другие колонки)

Как работает Clustered Index

# Пример таблицы users с clustered index по id

# CREATE TABLE users (
#     id INT PRIMARY KEY CLUSTERED,
#     name VARCHAR(100),
#     email VARCHAR(100),
#     age INT
# )

# Физическое расположение на диске:
# [Блок 1]  id=1, name='Alice', email='alice@...', age=25
# [Блок 2]  id=2, name='Bob', email='bob@...', age=30
# [Блок 3]  id=3, name='Charlie', email='charlie@...', age=35
# [Блок 4]  id=4, name='Diana', email='diana@...', age=28

# Поиск: SELECT * FROM users WHERE id=2
# БД находит корневой узел B-tree → идёт к нужному блоку → возвращает строку
# ОДНОГО обращения к диску достаточно!

Создание Clustered Index

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()
engine = create_engine('mssql+pyodbc://user:password@localhost/db')

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)  # Это будет CLUSTERED by default
    name = Column(String(100))
    email = Column(String(100))
    age = Column(Integer)

# В SQL Server PRIMARY KEY по умолчанию — CLUSTERED
# В PostgreSQL нет явного разделения, PRIMARY KEY = уникальный индекс

# Явное создание в raw SQL
with engine.connect() as conn:
    conn.execute('''
        CREATE CLUSTERED INDEX idx_users_id ON users(id)
    ''')
    conn.commit()

Пример: Поиск с Clustered Index

from sqlalchemy import select, create_engine
from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:password@localhost/db')

# Запрос с использованием clustered index (очень быстро)
def find_user_by_id(user_id: int):
    with Session(engine) as session:
        # Это использует PRIMARY KEY (clustered в большинстве БД)
        result = session.execute(
            select(User).where(User.id == user_id)
        )
        return result.scalar()

# Запрос БЕЗ использования clustered index (медленнее)
def find_user_by_name(name: str):
    with Session(engine) as session:
        # Это требует FULL TABLE SCAN (если нет non-clustered индекса на name)
        result = session.execute(
            select(User).where(User.name == name)
        )
        return result.scalars().all()

Clustered vs Non-Clustered на примере

# Таблица с PRIMARY KEY (clustered) по id
CREATE TABLE orders (
    id INT PRIMARY KEY CLUSTERED,  -- Физический порядок на диске: 1, 2, 3, 4...
    user_id INT,
    total DECIMAL,
    created_at DATETIME,
    INDEX idx_user_id (user_id)    -- Отдельный non-clustered индекс
)

# Сценарий 1: Поиск заказа по ID
SELECT * FROM orders WHERE id = 5
# Результат: B-tree clustered index → находит строку → возвращает ВСЕ колонки
# Скорость: ОЧЕНЬ БЫСТРО (1 обращение к диску)

# Сценарий 2: Поиск заказов пользователя
SELECT * FROM orders WHERE user_id = 3
# Результат: Non-clustered index idx_user_id → список ID заказов → для каждого ID идёт в clustered index
# Скорость: БЫСТРО (если индекс есть), но медленнее чем Сценарий 1

# Сценарий 3: Поиск без индекса
SELECT * FROM orders WHERE total > 100
# Результат: FULL TABLE SCAN (сканирует все строки подряд)
# Скорость: МЕДЛЕННО

B-Tree структура Clustered Index

Корневой узел (индекс на диск 1-100)
    |
    ├─ Узел 1 (индекс ID 1-25) → [Блок 1]
    ├─ Узел 2 (индекс ID 26-50) → [Блок 2]
    └─ Узел 3 (индекс ID 51-100) → [Блок 3]

SELECT * FROM users WHERE id = 35
1. Идём к корневому узлу
2. Видим: 35 находится в диапазоне 26-50
3. Идём к Узлу 2
4. Находим ID 35 в блоке данных
5. Возвращаем строку

Performance: с индексом vs без

import time
from sqlalchemy import select, create_engine
from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:password@localhost/db')

# С CLUSTERED INDEX (быстро)
start = time.time()
with Session(engine) as session:
    for i in range(1, 1001):
        result = session.execute(
            select(User).where(User.id == i)
        ).scalar()
duration_with_index = time.time() - start
print(f'С индексом: {duration_with_index:.3f}s')  # ~0.05s

# Без индекса (медленно)
# Пришлось бы сканировать таблицу целиком для каждого поиска
# Результат: ~5-10s (100x медленнее!)

Кластеризованный индекс в PostgreSQL

PostgreSQL не различает явно clustered и non-clustered индексы, но приблизительно:

from sqlalchemy import create_engine, Column, Integer, String, Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)  # PRIMARY KEY (похож на clustered)
    name = Column(String(100), index=True)  # Отдельный индекс (похож на non-clustered)
    email = Column(String(100))

# В PostgreSQL индексы часто создаются как B-tree (как clustered в других БД)
# Но физический порядок на диске определяется PRIMARY KEY, а не индексом

with engine.connect() as conn:
    # Явное создание индекса
    conn.execute('''
        CREATE INDEX idx_users_name ON users(name)
    ''')
    conn.commit()

Проблемы с неправильным выбором Clustered Index

# Плохой выбор: clustered по email вместо id

# CREATE TABLE users (
#     id INT,
#     name VARCHAR(100),
#     email VARCHAR(100) PRIMARY KEY CLUSTERED,  -- НЕПРАВИЛЬНО!
#     age INT
# )

# Проблемы:
# 1. Email строки имеют переменную длину → фрагментация
# 2. Поиск по ID становится медленнее (нужен non-clustered)
# 3. Вставка случайной почты → переставляет строки на диске (дорого)

# Хороший выбор: clustered по id

# CREATE TABLE users (
#     id INT PRIMARY KEY CLUSTERED,  -- ПРАВИЛЬНО!
#     name VARCHAR(100),
#     email VARCHAR(100),
#     age INT
# )

# Преимущества:
# 1. INT имеет фиксированный размер → нет фрагментации
# 2. ID генерируются последовательно → вставка быстрая
# 3. Поиск по ID максимально быстрый

Fragmentation и Maintenance

from sqlalchemy import create_engine, text

engine = create_engine('mssql+pyodbc://user:password@localhost/db')

# Проверить фрагментацию индекса
with engine.connect() as conn:
    result = conn.execute(text('''
        SELECT 
            index_type_desc,
            avg_fragmentation_in_percent
        FROM sys.dm_db_index_physical_stats(
            DB_ID(),
            OBJECT_ID('users'),
            NULL, NULL, 'LIMITED'
        )
        WHERE index_id > 0
    '''))
    
    for row in result:
        print(f'Индекс: {row[0]}, Фрагментация: {row[1]:.2f}%')
        if row[1] > 30:
            print('  → Требуется REBUILD')
        elif row[1] > 10:
            print('  → Рекомендуется REORGANIZE')

# Дефрагментация clustered index
with engine.connect() as conn:
    conn.execute(text('ALTER INDEX idx_users_id ON users REBUILD'))
    conn.commit()

Выбор правильного столбца для Clustered Index

Хорошие кандидаты:

  • ✓ PRIMARY KEY (обычно INT или UUID)
  • ✓ Столбец с уникальными значениями
  • ✓ Столбец, который часто используется в WHERE и JOIN
  • ✓ Столбец, по которому часто сортируют (ORDER BY)

Плохие кандидаты:

  • ✗ Очень длинные строки (VARCHAR(MAX))
  • ✗ Столбцы, которые часто обновляются
  • ✗ Столбцы с низкой избирательностью (много дублей)

Резюме: Clustered Index — это индекс, который определяет физический порядок данных на диске. Таблица может иметь только один clustered index (обычно PRIMARY KEY), который обеспечивает очень быстрый поиск по этому столбцу. Правильный выбор clustered index критичен для производительности БД.

Что такое кластеризованный индекс SQL? | PrepBro