Индексация. Операторы SQL для работы с индексами.

Индексы - это сбалансированные деревья значений указанных в индексе полей и ссылки на физические записи в таблице. Индексы являются полноценными данными и занимают место в базе данных на ее страницах. Индексы позволяют ускорить работу выполнения запросов в сотни раз и сразу находить нужные данные, вместо того, чтобы последовательно читать всю таблицу и сравнивая каждую запись по указанному условию (такой метод поиска информации называется TABLE SCAN). При выполнении запроса оптимизатор запросов руководствуется двумя характеристиками при вычислении лучшего способа получения информации - это статистика (STATISTICS) и существующие индексы (INDEX). Если по статистике оптимизатор видит, что в запросе скорее всего будет обрабатываться только часть данных из таблицы и что существует подходящий индекс, по которому можно сразу определить нужные записи, то он воспользуется самым удобным и наиболее точно указывающим положение записей индексом.

К строгим операциям относятся операторы "AND", "=" и "IN". С точки зрения производительности это самые выгодные операторы. Если в фильтре на таблицу используются только эти операторы, то оптимизатор запросов сразу же начинает искать на таблицу индекс, который наиболее оптимально подходит под условия выборки.

"AND" - при использовании условий на множество полей оптимизатор будет отдавать предпочтение составным индексам, если они обхватывают множество полей, по которым проводиться оптимизация.

"=" - для операции строго сравнения оптимизатору достаточно в подходящем индексе найти одно нужное значение. Это одна из самых быстрых операций. Если оператор "AND" не используется, то оптимизатор отдаст предпочтение простым индексам, так как для определения найденных записей в составных индексов ему придеться спускаться по всем уровням вложенности вниз, до страниц листов, в отличие от простого индекса, в котором уровень страниц листов идет непосредственно следующим за найденной страницей.

"IN" - для операции сравнения из списка оптимизатор организует виртуальную таблицу "IN" и использовав с ней прямое соединение выберет все нужные записи. Это достаточно быстрая операция, хотя по стоимости она чуть больше, чем операция равенства. Для оператора "IN" справедливы такие же правила выбора индекса, что и оператора "=".

К накладывающим операциям относятся операторы "OR", "<", ">", "<=", ">=", "BETWEEN". При использовании этих операторов вместе со строгими операторами эффективность последних теряется.

"OR" - если этот оператор используется только применимо к одному полю таблицы с условиями равенства, например, "Field1 = 1 OR Field1 = 2", то оптимизатор запросов автоматически преобразует условие сравнения в оператор "IN", приравнивая в данном случае этот оператор до уровня строгого оператора. В случае, если этот оператор используется применимо к полю и переменной (или же к DETERMINISTIC функции), например, "Field1 = 1 OR @Var = 2", то при выполнении запроса оптимизатор запроса проверит на истинность условие сравнения с переменной. Если условие истинно, то оптимизатор исключит из запроса все условие с оператором "OR". В противном случае оптимизатор уберет оператор "OR" и условие сравнения с переменной, оставив только оператор сравнения с полем. В любых других случаях использования оператора "OR", оптимизатор запросов будет всегда использовать алгоритм сканирования таблицы, что приведет к значительным затратам на выполнение запроса.

"BETWEEN" - данный оператор автоматически преобразуется оптимизатором из формы "Field BETWEEN MinValue AND MaxValue" в форму "Field >= MinValue AND Field <= MaxValue" и обрабатывается уже по правилам для операторов последней формы.

К отрицающим операциям относятся операторы "NOT", "!=" (или "<>").

"NOT" - при обработке запроса оптимизатор будет пытаться развернуть этот оператор в обратное условие, если это будет возможно.