SQL Server: поиск или сканирование

Содержание:
1. Сканирование против поиска (Вы читаете данный раздел);
2. Полусоединения и антиполусоединения;
3. N верхних позиций для группы.

---

Демонстрационные данные для статьи сформированы с помощью кода, приведенного ниже.


Код создает таблицу Orders, содержащую 1 000000 заказов, таблицу Employees, содержащую 200 сотрудников компании, из которых 100 занимались обработкой заказов, и таблицу Customers, содержащую 40000 клиентов, из которых 20000 размещали заказы. Для иллюстрации выбора оптимального решения код создает несколько индексов в таблице Orders.


Существует много задач, решаемых с применением отдельных групп логики, предусматривающей анализ одной или нескольких строк у каждой группы, например:
• Полусоединения (наличие) и анти-полусоединения (отсутствие). Например, вывод работников компании, занимавшихся или не занимавшихся обработкой заказов, вывод клиентов, размещавших или не размещавших заказы.
• Значения min или max для группы. Например, вывод максимального ID заказа для каждого сотрудника компании/клиента.
• N верхних позиций для группы. Например, вывод заказа с максимальным ID для каждого сотрудника компании или клиента.

При решении таких задач полезно применять POC-индекс, построенный для большой таблицы (в нашем случае Orders). Аббревиатура РОС происходит от названий элементов, участвующих в запросе и фигурирующих в описании индекса: секционирование (Partitioning), сортировка (Ordering) и покрытие (Coverage). Например, для задачи «вывести заказ (orderid, orderdate, empid, custid) с максимальным ID» Р = empid, О = orderid DESC, a C = orderdate, custid. Элементы P и О составляют список ключа индекса, а элемент С — список INCLUDE. Действительно, один из индексов, создаваемых в коде, представленном выше, для демонстрации оптимизации решения такой задачи с помощью POC-индекса, следующий:

CREATE INDEX idx_eid_oid_i_od_cid 
ON dbo.Orders(empid, orderid DESC) 
INCLUDE(orderdate, custid);

Что примечательно, в зависимости от плотности элемента «группа или секционирование», предпочтительными являются разные стратегии оптимизации.

Если элемент «секционирование» имеет большую плотность (малое число значений, каждое из которых встречается многократно), наилучшей стратегией будет поиск в индексе по каждому значению. Например, столбец empid в таблице Orders имеет большую плотность (100 различных ID сотрудников компании). Поэтому оптимальной стратегией вывода заказа с максимальным ID будет сканирование таблицы Employees и циклическое применение поиска в POC-индексе к таблице Orders для каждого сотрудника. Учитывая, что в таблицу Employees внесено 200 работников компании, это выльется в 200 операций поиска, что означает в сумме несколько сотен операций чтения. Для сравнения, полное сканирование POC-индекса выльется в несколько тысяч операций чтения.

С другой стороны, столбец custid имеет малую плотность в таблице Orders (20000 различных ID клиентов). Учитывая, что таблица Customers содержит 40000 клиентов, стратегия поисковых проходов будет реализована ценой сотни тысяч операций чтения, тогда как сканирование обойдется всего в несколько тысяч таких операций.


Иными словами, для задач, предполагающих анализ по группам, требующий просмотра небольшого числа строк для каждой группы, при малой плотности оптимальным является сканирование, тогда как при большой плотности — поиск. Теперь вернемся к рассмотрению наших примеров и выясним, в каких случаях оптимизатор самостоятельно приходит к наилучшей стратегии, а когда ему нужна помощь.