SQL: Оптимизация медленного запроса

Решение

Анализ проблемы

Оригинальный запрос медленный потому что:

1. Нет индекса на колонках WHERE (CustomerId, OrderDate, Status)
2. **SELECT *** — загружаются ненужные колонки
3. Нет covering index — требуется обращение к основной таблице
4. Сложное условие — несколько WHERE условий без правильного индекса

---

1. Рекомендуемые индексы

-- ✅ Основной индекс: составной (composite index)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate_Status
  ON Orders (CustomerId, OrderDate DESC, Status)
  INCLUDE (TotalAmount);

-- Порядок колонок КРИТИЧЕН!
-- 1. CustomerId — фильтруется по точному значению (Seek)
-- 2. OrderDate DESC — фильтруется по диапазону и сортируется
-- 3. Status — дополнительный фильтр
-- INCLUDE (TotalAmount) — covering index (не нужна основная таблица)

Почему этот порядок?

SQL Server оптимизатор использует индекс слева направо. Порядок колонок:

1. Equality conditions first (=) → CustomerId
2. Range conditions next (>=) → OrderDate
3. Other filters → Status
4. INCLUDE columns — избегаем Table Scan

---

2. Оптимизированный SQL запрос

-- ❌ ПЛОХО — SELECT *, медленно
SELECT * FROM Orders
WHERE CustomerId = @customerId
  AND OrderDate >= @startDate
  AND Status = "Completed"
ORDER BY OrderDate DESC;

-- ✅ ХОРОШО — выбираем только нужные колонки
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = @customerId
  AND OrderDate >= @startDate
  AND Status = "Completed"
ORDER BY OrderDate DESC;

Почему это быстрее:

• Индекс содержит все необходимые колонки (covering)
• SQL Server не обращается к основной таблице
• Меньше данных в памяти

---

3. Clustered vs Non-Clustered индексы

┌─────────────────────────────────────────────────────────┐
│ CLUSTERED INDEX (Кластеризованный)                      │
├─────────────────────────────────────────────────────────┤
│ • PRIMARY KEY по умолчанию создаёт clustered            │
│ • В таблице может быть ТОЛЬКО 1                         │
│ • Листовые узлы содержат ВСЕ данные таблицы            │
│ • Порядок данных в таблице определён индексом          │
│ • Поиск = навигация по дереву → доступ к данным        │
│ • Быстро когда поиск по PRIMARY KEY                    │
│                                                         │
│ Пример: Id (PRIMARY KEY)                               │
│ CREATE CLUSTERED INDEX PK_Orders ON Orders(Id)         │
└─────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────┐
│ NON-CLUSTERED INDEX (Некластеризованный)               │
├─────────────────────────────────────────────────────────┤
│ • Можно создать много (999 в SQL Server)                │
│ • Листовые узлы содержат KEY LOOKUP + Clustered Key    │
│ • Требуется дополнительный поиск (RID/Clustered Key)   │
│ • Используется для WHERE, ORDER BY, JOIN                │
│                                                         │
│ Пример: (CustomerId, OrderDate)                        │
│ CREATE NONCLUSTERED INDEX IX_Orders_Customer            │
│   ON Orders (CustomerId, OrderDate)                    │
└─────────────────────────────────────────────────────────┘

Сравнение:

---

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

Когда использовать?

-- ✅ ХОРОШО — составной индекс (все в одном)
CREATE NONCLUSTERED INDEX IX_Orders_Composite
  ON Orders (CustomerId, OrderDate DESC, Status)
  INCLUDE (TotalAmount);

-- ❌ ПЛОХО — несколько отдельных индексов
CREATE NONCLUSTERED INDEX IX_CustomerId ON Orders(CustomerId);
CREATE NONCLUSTERED INDEX IX_OrderDate ON Orders(OrderDate);
CREATE NONCLUSTERED INDEX IX_Status ON Orders(Status);

Почему составной лучше?

1. Index Intersection — несколько индексов требуют пересечения (медленно)
2. Index Seek — составной индекс может быть использован полностью
3. Memory efficiency — одна структура вместо трёх
4. Maintenance — обновление одного индекса вместо трёх

Порядок колонок в составном индексе (ESR правило):

E = Equality (=)      → WHERE CustomerId = 123
S = Sort (ORDER BY)   → ORDER BY OrderDate DESC
R = Range (>=, <)     → WHERE OrderDate >= 2024-01-01

✅ ПРАВИЛЬНО:
CREATE INDEX IX (...) ON Orders(CustomerId, OrderDate DESC, Status);
                         ↑         ↑               ↑        ↑
                      Equality  Range/Sort    Equality  Filter

❌ НЕПРАВИЛЬНО:
CREATE INDEX IX (...) ON Orders(OrderDate, CustomerId);
                         ↑
        SQL не может использовать для WHERE CustomerId = ...

---

5. Covering Index

-- ✅ COVERING INDEX — индекс содержит ВСЕ необходимые данные
CREATE NONCLUSTERED INDEX IX_Orders_Covering
  ON Orders (CustomerId, OrderDate DESC, Status)  -- Key columns
  INCLUDE (TotalAmount, Description);             -- Non-key columns

-- Преимущества:
-- 1. SQL Server не обращается к основной таблице (Index-Only Scan)
-- 2. Очень быстро для SELECT этих колонок
-- 3. Минимум I/O операций

-- Результат запроса:
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = @customerId
  AND OrderDate >= @startDate
  AND Status = "Completed"
ORDER BY OrderDate DESC;
-- Выполняется только по индексу, без обращения к таблице!

Когда использовать INCLUDE?

-- ❌ НЕ добавляйте в INCLUDE если:
-- - Колонка уже в ключевых колонках
-- - Колонка большая (TEXT, NVARCHAR(MAX))
-- - Колонка редко используется в SELECT

-- ✅ Добавляйте в INCLUDE если:
-- - Часто используется в SELECT
-- - Маленькая (INT, DECIMAL, DATE)
-- - Важна производительность

---

6. Проблемы SELECT *

-- ❌ ПРОБЛЕМЫ SELECT *:

1. Неиспользованные колонки
   SELECT * FROM Orders  -- если нужны только 5 из 20 колонок
   -- загружаются ВСЕ 20!
   -- Лишняя память, медленнее

2. Индекс не может быть covering
   -- SQL Server не может использовать Index-Only Scan
   -- Требуется дополнительный Table Scan

3. Сложнее отследить зависимости
   -- Если добавили новую колонку, она попадёт в SELECT
   -- Может быть проблема, если колонка содержит sensitive data

4. Нарушение API контракта
   -- Приложение ожидает определённые колонки
   -- Добавили новую колонку → неожиданное поведение

-- ✅ РЕШЕНИЕ: Явно указывайте колонки
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE ...

---

7. Полный план оптимизации

Шаг 1: Создание индекса

-- Основной индекс
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate_Status
  ON Orders (CustomerId, OrderDate DESC, Status)
  INCLUDE (TotalAmount, Id)  -- Covering для всех нужных колонок
  WITH (FILLFACTOR = 90);    -- Оставляем место для обновлений

Шаг 2: Проверка плана выполнения

-- Включаем просмотр плана
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Оптимизированный запрос
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = 123
  AND OrderDate >= 2024-01-01
  AND Status = Completed
ORDER BY OrderDate DESC;

-- Выключаем
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Результат должен показать:
-- Index Seek на IX_Orders_CustomerId_OrderDate_Status
-- Не должно быть Table Scan
-- Logical Reads должны быть минимальны

Шаг 3: Мониторинг использования индекса

-- Проверяем, используется ли индекс
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id 
    AND i.index_id = s.index_id
WHERE OBJECT_NAME(i.object_id) = Orders
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

---

8. Дополнительные оптимизации

Паджинация вместо всех результатов:

-- ❌ Медленно — загружаем все
SELECT * FROM Orders WHERE CustomerId = 123

-- ✅ Быстро — паджинация
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = 123
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;

Фильтрация в приложении vs БД:

-- ❌ ПЛОХО — фильтруем в приложении
SELECT * FROM Orders WHERE CustomerId = 123;  -- 1 млн. строк
-- В приложении отфильтровали по Status

-- ✅ ХОРОШО — фильтруем в БД
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = 123 AND Status = Completed;

---

9. Возможные проблемы и решения

Проблема 1: Индекс фрагментирован

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

-- Решение: Перестроение индекса
ALTER INDEX IX_Orders_CustomerId_OrderDate_Status ON Orders REBUILD;
-- Или дефрагментация
ALTER INDEX IX_Orders_CustomerId_OrderDate_Status ON Orders REORGANIZE;

Проблема 2: Статистика устарела

-- Обновляем статистику
UPDATE STATISTICS Orders IX_Orders_CustomerId_OrderDate_Status;

-- Или автоматически
ALTER DATABASE [YourDatabase]
SET AUTO_UPDATE_STATISTICS ON;

Проблема 3: Индекс используется редко (пропалд)

-- Удаляем неиспользуемые индексы
DROP INDEX IX_Orders_Unused ON Orders;

---

10. Полный пример с измерениями

-- ДО оптимизации
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Orders
WHERE CustomerId = 123
  AND OrderDate >= 2024-01-01
  AND Status = Completed
ORDER BY OrderDate DESC;

-- Результат: Table Scan, 100000 logical reads, 5000ms

-- ПОСЛЕ оптимизации
CREATE NONCLUSTERED INDEX IX_Orders_Optimized
  ON Orders (CustomerId, OrderDate DESC, Status)
  INCLUDE (Id, TotalAmount);

SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = 123
  AND OrderDate >= 2024-01-01
  AND Status = Completed
ORDER BY OrderDate DESC;

-- Результат: Index Seek, 50 logical reads, 50ms
-- Улучшение в 100 раз!

---

Выводы

✅ Рекомендуемые индексы:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate_Status
  ON Orders (CustomerId, OrderDate DESC, Status)
  INCLUDE (TotalAmount, Id);

✅ Ключевые принципы:

1. ESR правило (Equality, Sort, Range)
2. Covering index избегает Table Scan
3. SELECT только нужные колонки
4. Составной индекс лучше нескольких
5. Мониторь использование и фрагментацию

✅ Результат:

• Seek вместо Scan
• 50-100x ускорение
• Меньше CPU/Memory
• Лучше масштабируемость