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

Question

claude-haiku-4.5 · Accepted Answer

## Некластеризованный индекс SQL (Non-Clustered Index)

**Некластеризованный индекс** (non-clustered index) — это отдельная структура данных в SQL базе, которая создаёт отсортированный список значений столбца(ов) с указателями на соответствующие строки. Это позволяет быстро находить данные без сканирования всей таблицы, но не влияет на физическое расположение данных в БД.

### Кластеризованный vs Некластеризованный

```
КЛАСТЕРИЗОВАННЫЙ ИНДЕКС (Clustered Index)
┌─────────────────────┐
│ Определяет порядок  │
│ ФИЗИЧЕСКОГО         │
│ расположения строк  │
└─────────────────────┘

Обычно PRIMARY KEY создаёт кластеризованный индекс
В таблице может быть ТОЛЬКО ОДИН

Пример: таблица отсортирована по id
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│ 1  │ Alice    │  ← Строки физически расположены
│ 2  │ Bob      │     в порядке id
│ 3  │ Charlie  │
└────┴──────────┘

НЕКЛАСТЕРИЗОВАННЫЙ ИНДЕКС (Non-Clustered Index)
┌─────────────────────┐
│ УКАЗАТЕЛЬ на стро ки│
│ без изменения их    │
│ физического порядка │
└─────────────────────┘

В таблице может быть МНОГО (до 999 в SQL Server)

Пример: индекс по name
Индекс:               Таблица:
┌─────┐────┐        ┌────┬──────────┐
│name │ptr │        │id  │name      │
├─────┼────┤        ├────┼──────────┤
│Alice│ 1  │───────→│ 1  │Alice     │
│Bob  │ 2  │───────→│ 2  │Bob       │
│Char │ 3  │───────→│ 3  │Charlie   │
└─────┴────┘        └────┴──────────┘
```

### Создание некластеризованного индекса

```sql
-- Базовый синтаксис
CREATE INDEX idx_email ON users(email);

-- По нескольким столбцам
CREATE INDEX idx_name_email ON users(first_name, last_name, email);

-- С условием (фильтрованный индекс)
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;

-- С INCLUDE (покрывающий индекс)
CREATE INDEX idx_email_include ON users(email)
INCLUDE (first_name, last_name, phone);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Удаление индекса
DROP INDEX idx_email ON users;  -- MySQL
DROP INDEX users.idx_email;  -- PostgreSQL
```

### Как работает поиск

```
БЕЗ ИНДЕКСА: Table Scan

Запрос: SELECT * FROM users WHERE email = 'alice@example.com'

BD проверяет КАЖДУЮ строку:
┌──────┬─────────────────────┬─────┐
│id    │email                │name │
├──────┼─────────────────────┼─────┤
│1     │john@example.com     │ ✗ │
│2     │alice@example.com    │ ✓ Найда!
│3     │bob@example.com      │ ✗ │
│...   │...                  │... │
│10000 │charlie@example.com  │ ✗ │
└──────┴─────────────────────┴─────┘

10000 операций чтения! МЕДЛЕННО

С ИНДЕКСОМ: Index Seek

Построенный индекс (B-tree):

[alice...]
          /            \
    [alice..john]  [john..zoe]
    /    |    \
  [a]   [i]   [j]...  ← Листья с указателями
   ↓
alice@example.com → строка 2

БДО делает бинарный поиск:
Операция: log₂(10000) ≈ 14 операций! БЫСТРО
```

### Практический пример с Python/SQL

```python
import psycopg2
from time import time

conn = psycopg2.connect("dbname=test user=postgres")
cursor = conn.cursor()

# Таблица с 1 млн записей
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id BIGSERIAL PRIMARY KEY,
        email VARCHAR(255),
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# Вставляем тестовые данные (1 млн)
for i in range(1000000):
    cursor.execute(
        "INSERT INTO users (email, first_name, last_name) VALUES (%s, %s, %s)",
        (f"user{i}@example.com", f"First{i}", f"Last{i}")
    )
conn.commit()

# Тест 1: Поиск БЕЗ индекса
start = time()
cursor.execute("EXPLAIN ANALYZE SELECT * FROM users WHERE email = %s", 
               ("user999999@example.com",))
print("Без индекса:")
for row in cursor:
    print(row)
print(f"Время: {time() - start:.3f}s")

# Создаём индекс
start = time()
cursor.execute("CREATE INDEX idx_users_email ON users(email)")
conn.commit()
print(f"Создание индекса: {time() - start:.3f}s")

# Тест 2: Поиск С индексом
start = time()
cursor.execute("EXPLAIN ANALYZE SELECT * FROM users WHERE email = %s", 
               ("user999999@example.com",))
print("
С индексом:")
for row in cursor:
    print(row)
print(f"Время: {time() - start:.3f}s")

conn.close()
```

### EXPLAIN: как понять план запроса

```sql
-- БЕЗ индекса
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- Результат:
Seq Scan on users  (cost=0.00..5000.00 rows=1)  ← Sequential scan
  Filter: (email = 'alice@example.com')
  Planning Time: 0.1 ms
  Execution Time: 150.2 ms  ← МЕДЛЕННО

-- С индексом
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- Результат:
Index Scan using idx_email on users  (cost=0.42..4.44 rows=1)
  Index Cond: (email = 'alice@example.com')
  Planning Time: 0.2 ms
  Execution Time: 0.5 ms  ← БЫСТРО
```

### Типы некластеризованных индексов

#### 1. Простой индекс (Single Column)

```sql
-- Индекс по одному столбцу
CREATE INDEX idx_last_name ON employees(last_name);

-- Поиск по фамилии будет быстрым
SELECT * FROM employees WHERE last_name = 'Smith';
```

#### 2. Составной индекс (Composite Index)

```sql
-- Индекс по нескольким столбцам (ORDER важен)
CREATE INDEX idx_name ON employees(last_name, first_name);

-- Эффективен для обоих запросов:
SELECT * FROM employees WHERE last_name = 'Smith';

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

-- Но НЕ эффективен для:
SELECT * FROM employees WHERE first_name = 'John';
-- Индекс не помогает без last_name (leading column)
```

#### 3. Покрывающий индекс (Covering Index)

```sql
-- Индекс INCLUDE включает доп. столбцы
CREATE INDEX idx_email_include ON users(email)
INCLUDE (first_name, last_name, phone);

-- Запрос может быть выполнен ТОЛЬКО из индекса
SELECT first_name, last_name, phone FROM users WHERE email = 'alice@example.com';

-- БД читает индекс и находит все данные
-- НЕ нужно обращаться к основной таблице (Index Only Scan)
```

#### 4. Фильтрованный индекс (Filtered Index)

```sql
-- Индекс только на активных пользователей
CREATE INDEX idx_active_emails ON users(email)
WHERE is_active = true;

-- Экономит место, ускоряет поиск
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = true;

-- Но НЕ помогает для неактивных:
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = false;
```

#### 5. Уникальный индекс (Unique Index)

```sql
-- Гарантирует уникальность значений
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Попытка вставить дубликат → ошибка
INSERT INTO users (email) VALUES ('alice@example.com');
INSERT INTO users (email) VALUES ('alice@example.com');  -- ERROR: duplicate key
```

### Производительность индексов

```python
# Правило 3:16:97

# КОГДА ИСПОЛЬЗОВАТЬ ИНДЕКС:
# - Таблица > 10000 строк
# - Столбец часто используется в WHERE
# - Запросы читают < 10% данных

# КОГДА НЕ ИСПОЛЬЗОВАТЬ:
# - Таблица маленькая (< 1000)
# - Столбец имеет низкую selectivity (много одинаковых значений)
#   Пример: пол (M/F) → индекс бесполезен
# - Часто делаются INSERT/UPDATE/DELETE на этот столбец
#   Индекс нужно обновлять → медленнее
```

### Проблемы с индексами

```sql
-- ❌ Проблема 1: Фрагментация индекса
-- После много DELETE/UPDATE индекс становится фрагментированным
ALTER INDEX idx_email REBUILD;  -- Пересоздание
ALTER INDEX idx_email REORGANIZE;  -- Реорганизация

-- ❌ Проблема 2: Неиспользуемые индексы
-- Занимают место и замедляют INSERT/UPDATE

-- Найти неиспользуемые индексы (PostgreSQL):
SELECT indexname FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE 'pg_toast%';

-- ❌ Проблема 3: Слишком много индексов
-- Каждый индекс должен обновляться при вставке
-- 10+ индексов → каждый INSERT медленнее

-- ❌ Проблема 4: Плохая selectivity
CREATE INDEX idx_is_deleted ON users(is_deleted);
-- Таблица: 1M активных, 100 удалённых
-- Поиск "is_deleted = true" вернёт 100 из 1M
-- Это 0.01% → индекс помогает
-- Но поиск "is_deleted = false" вернёт 1M из 1M
-- Индекс не поможет → таблица сканируется
```

### Оптимизация запросов с индексами

```sql
-- ✓ Хорошо: используется индекс
SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan

-- ❌ Плохо: индекс не используется
SELECT * FROM users WHERE email LIKE '%@example.com';
-- % в начале → Seq Scan

-- ✓ Хорошо: LIKE с префиксом работает
SELECT * FROM users WHERE email LIKE 'alice%';
-- Index Scan может использоваться

-- ❌ Плохо: функция над столбцом
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Seq Scan (индекс на email не помогает)

-- ✓ Хорошо: индекс на функцию
CREATE INDEX idx_email_upper ON users(UPPER(email));
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Index Scan

-- ❌ Плохо: OR условия
SELECT * FROM users 
WHERE email = 'alice@example.com' OR phone = '555-1234';
-- Нужны два индекса на разных столбцах

-- ✓ Хорошо: IN вместо OR
SELECT * FROM users 
WHERE status IN ('active', 'pending', 'review');
-- Может использовать индекс лучше
```

### Мониторинг индексов

```sql
-- PostgreSQL: индексы и их размер
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- MySQL: список индексов
SHOW INDEX FROM users;

-- SQL Server: информация об индексах
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    s.user_updates,
    s.user_seeks + s.user_scans + s.user_lookups AS user_reads
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s 
    ON i.object_id = s.object_id 
    AND i.index_id = s.index_id
WHERE OBJECT_NAME(i.object_id) = 'users'
ORDER BY user_updates DESC;
```

### Рекомендации

```python
# 1. Начни без индексов (YAGNI)
# Добавляй индексы только когда нужны

# 2. Профилируй (EXPLAIN ANALYZE)
# Посмотри план запроса перед созданием индекса

# 3. Индексируй WHERE, JOIN, ORDER BY
# Столбцы в этих частях запросов выигрывают от индексов

# 4. Составные индексы
# Порядок столбцов важен
# (A, B) помогает WHERE A=? AND B=?
# Но НЕ помогает WHERE B=?

# 5. Покрывающие индексы
# Когда нужны только несколько столбцов → используй INCLUDE

# 6. Удаляй неиспользуемые
# Мониторь и удаляй индексы с idx_scan = 0

# 7. Баланс
# INSERT/UPDATE медленнее с индексами
# Каждый индекс нужно обновлять
```

### Ключевые моменты

- **Некластеризованный индекс** создаёт отсортированный список с указателями
- **Один кластеризованный** (PRIMARY KEY) + много некластеризованных
- **B-tree структура** позволяет быстрый поиск за O(log n)
- **Составные индексы** требуют правильного порядка столбцов
- **Покрывающие индексы** могут сделать поиск без обращения к таблице
- **EXPLAIN ANALYZE** показывает использует ли запрос индекс
- **Фрагментация** происходит при много DELETE/UPDATE
- **Selectivity** критична — индекс на булеве поле часто бесполезен

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

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

Некластеризованный индекс SQL (Non-Clustered Index)

Кластеризованный vs Некластеризованный

Создание некластеризованного индекса

Как работает поиск

Практический пример с Python/SQL

EXPLAIN: как понять план запроса

Типы некластеризованных индексов

1. Простой индекс (Single Column)

2. Составной индекс (Composite Index)

3. Покрывающий индекс (Covering Index)

4. Фильтрованный индекс (Filtered Index)

5. Уникальный индекс (Unique Index)

Производительность индексов

Проблемы с индексами

Оптимизация запросов с индексами

Мониторинг индексов

Рекомендации

Ключевые моменты