Для ограничений UNIQUE и PRIMARY KEY автоматически создается индекс, который упрощает поиск необходимых данных. Что такое индекс? Если говорить простыми словами, то это способ отсортировать данные по определенной колонке. Когда список отсортирован, намного проще производить поиск необходимых данных.
Понимание того, как хранятся данные – является основой понимание того, как получить к ним доступ. Для начала нам нужно разобраться с таким понятием как куча – это коллекция страниц данных, содержащих строки для таблицы:
Сервер SQL получает доступ к данным одним из следующих способов:
Первым делом, SQL Server определяет, какие индексы существуют. Оптимизатор запроса (компонент, предназначенный для генерирования оптимального плана для запроса) определяет что использовать – сканировать таблицу или индексы. Индексы более предпочтительны.
Когда вы рассматриваете, нужно ли создавать индексы, рассчитайте два фактора, для гарантирования, что индексы будут более эффективны, чем сканирование таблицы: природа данных и природа запросов к таблице.
Индексы ускоряют доступ к данным. Для примера, без индекса, без индексы вам понадобится перелистать постранично всю книгу для определения содержания. По содержанию легче найти интересующую информацию.
Сервер SQL использует индексы для указания на расположение строки в странице данных вместо просматривания всех страниц таблицы. Рассматривайте следующие факты и рекомендации об индексах:
Индексы достаточно полезны, но они занимают место на диске и берут на себя дополнительные накладные расходы и расходы на эксплуатацию. Индексы могут создать и проблемы:
Индексы бывают кластерными (CLUSTERED) и не кластерными (NONCLUSTERED). В кластерном индексе строки физически сортируются на диске в соответствии с индексируемым полем. Я думаю, что не надо объяснять, почему кластерный индекс может быть только один на таблицу? Нельзя же одновременно физически отсортировать данные по двум ключам.
При не кластерном индексе строки могут на диске храниться в любом порядке, а сортировка осуществляется с помощью определенной таблицы или дерева индекса. В SQL сервере используется принцип дерева.
Конечно, это все из области администрирования SQL сервера, но вы должны понимать, что такое индекс и для чего он нужен.
Все первичные ключи, начиная с SQL Server 2000, по умолчанию создаются кластерными. Ограничения UNIQUE по умолчанию создаются не кластерными.
Чтобы лучше понимать индексы и разницу между кластерным и не кластерным индексом, посмотрим на рисунок 1.6, где показана примерная схема индекса. Каждый прямоугольник – это страница данных из 8 кб. Вверху находится корень дерева, а внизу листья дерева.
Допустим, что нам нужно найти имя Анатолий. Из корня мы узнаем, что для поиска информации об имени нужно спуститься влево вниз. Здесь также будет ссылка о том, что нужно спуститься еще влево вниз и тогда мы окажемся в листовом блоке.
В не кластерном индексе в листовом блоке находится ссылка на строку с данными, где можно найти имя Анатолий, а в кластерном индексе данные находятся непосредственно в листовом блоке.
Кластерный индекс иногда работает быстрее, а иногда удобнее. У меня был случай, когда я разрабатывал программу на Delphi + MS SQL Server, которая состояла из двух частей. Серверная часть программы сохраняла в базе данных строки, полученные с производственного оборудования, а клиентская их читала. Когда сервер сохранял данные в базе, программа направляла программе клиенту по сети сообщение. Получив это сообщение, клиент должен был прочитать сохраненную строку и вывести пользователю на экран.
Чтобы упростить чтение на клиентской стороне, без выполнения лишних запросов на выборку данных, я использовал готовый метод компонентов доступа к базам данных Delphi – переход на последнюю строку. Этот метод очень удобен тем, что быстро позволяет получить последнюю строку. Все прекрасно работало, но почему-то в определенные моменты переход блокировался на определенной строке, и какое-то время новее строки не отображались.
Проблема крылась как раз в не кластерном индексе. Когда базе данных не хватало места, она выделяла блок памяти на диске в произвольном месте. Если это место оказывалось в конце базы, то переходы происходили удачно. Если находилось свободное место в уже существующих блоках памяти, но не заполненных до конца (в любом месте базы данных, но не в конце), то переход не осуществлялся, потому что физически строка оказывалась не в конце. Проблему решило использование кластерного индекса.
Следующий пример, показывает, как можно создать не кластерный индекс:
CREATE TABLE Names ( idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY NONCLUSTERED (idName), )
Если нужно, чтобы первичный ключ был кластерным, то для MS SQL Server 2000 это по умолчанию и явное указание оператора CLUSTERED нужно только для MS SQL Server 7. Учитывайте эту разницу при обработке первичного ключа разными версиями MS SQL Server во время разработке собственных приложений. А лучше ни на кого не надеяться, а всегда явно указывать тип необходимого ключа.
В таблице может быть создано 249 не кластерных индексов и только один индекс может быть кластерным. Такое ограничение количества кластерных индексов связано с тем, что физически можно упорядочить только по одному полю, а не из-за прихоти разработчиков MS SQL Server.
Как мы уже знаем, кластерным может быть и ограничение уникальности. Для ограничения уникальности создается индекс, а свойство кластерный/не кластерный относится как раз к индексу. Например:
CREATE TABLE Names
(
idName int ,
vcName varchar(50),
vcLastName varchar(50),
vcSurName varchar(50),
dBirthDay datetime,
CONSTRAINT cn_unique UNIQUE CLUSTERED(vcName, vcLastName,
vcSurName, dBirthDay)
)
В данном примере создается только индекс для полей, с ограничением уникальности. При этом главного ключа нет. А что если его создать:
CREATE TABLE Names
(
idName int ,
vcName varchar(50),
vcLastName varchar(50),
vcSurName varchar(50),
dBirthDay datetime,
CONSTRAINT pk_idName PRIMARY KEY (idName),
CONSTRAINT cn_unique UNIQUE CLUSTERED(vcName, vcLastName,
vcSurName, dBirthDay)
)
В данном случае создается первичный ключ и по умолчанию он должен быть кластерным. Но так как кластерным создается ограничение уникальности cn_unique, то первичный ключ автоматически получит не кластерный индекс.
А если явно указать, что мы хотим первичный ключ и ограничение уникальности сделать кластерными? В этом случае произойдет ошибка: "Cannot add more than one clustered index for constraints on table 'Names' " (не могу добавлять более чем один кластерный индекс для ограничения на таблицу Names). Я же говорил, что кластерным может быть только один индекс.
Чтобы вам было удобно видеть, что делает сервер после создания таблицы, лучше всего выполнить команду:
sp_help Names
Команда sp_help (точнее это процедура SQL сервера) отображает подробную информацию о указанной таблице, в данном случае это таблица Names. Ее команду лучше всего выполнять в программе Query Analyzer, которая поставляется вместе с MS SQL Server, потому что ее результат состоит из нескольких таблиц, а Query Analyzer умеет их отображать в удобочитаемом виде.
Выполните команду и посмотрите на результат выполнения sp_help для таблицы Names. Обратите внимание на последние две строки. Здесь отображается список ограничений и их имена. Чуть выше показаны две строки индексов для этих ограничений. Первая строка соответствует ограничению cn_unique и во второй колонке видно, что этот индекс кластерный (clustered). Вторая строка – это индекс pk_idNames и во второй колонке указано, что индекс не кластерный (nonclustered).
Ваше бизнес окружение, характеристики данных и использование данных определяют колонки, которые должны быть проиндексированы. Полезность индекса напрямую зависит от процента возвращаемых запросом строк. Маленький или большой процент более эффективен.
Создавайте индексы на следующие поля:
Вы должны создавать только самые необходимые индексы, потому что каждый лишний индекс может серьезно ударить по производительности во время добавления новых записей. Это особенно становится заметным, при массовой загрузке данных.
Попробуем разобраться, какой индекс нужно создавать – кластерный или нет. Для того чтобы сделать правильный выбор нужно понимать, как будет использоваться ваша таблица.
Когда вы оптимизируете производительность для вставки данных для часто используемой таблицы, рассмотрите создание кластерного индекса на первичный ключ уникальной колонки. Для увеличения скорости вставки в маленькие группы страниц в конец таблицы. Частый доступ помещает эти страницы в память.Таблицы, которые часто используются для отчетов, группировки для агрегации или поиска ряда значений могут принести пользу при кластерном индексе на сортируемую колонку.
Сервер SQL использует значение кластерного индекса в качестве идентификатора строки внутри каждого не кластерного индекса. Кластерный индекс может повторяться много раз в структуре вашей таблицы.
Рекомендуется делать кластерный индекс минимальным по размеру. Для предотвращения больших кластерных индексов:
Когда вы определяете плотность ваших данных, помните, что плотность связана с определенными элементами данных. Плотность может изменяться. Что значит плотность? Рассмотрим ее на примере таблицы работников, которая содержит даты рождения. Допустим, что у вас на фирме работает 100 человек в возрасте от 23 до 30 и 10 человек в возрасте старше 30. В диапазоне от 23 до 30 получается высокая плотность, потому что здесь находиться очень много записей.
Так как данные распределяются не равномерно, оптимизатор запросов может использовать или не использовать индексы. Оптимизатор может:
Разброс данных связан с плотностью. Когда вы определяете плотность данных, вы должны также рассматривать и разброс.
Разброс данных определяет количество данных в определенных рамках значений и как много строк попадает в эти пределы. Если индексированная колонка имеет мало уникальных значений, получение данных может быть медленным. Например, если у вас есть таблица с полем отсортированным по фамилии, то данные могут быть неравномерно разбросаны по алфавиту. На некоторые буквы фамилий больше.
Сервер сам по себе не может знать о разбросе данных. Для этого ему необходимо собрать определенную статистику о полях и содержащихся в таблице значениях, чтобы можно было принять эффективное решение. Имея статистику, сервер сможет принять более эффективное решение о том, надо ли использовать индекс, или сканирование таблицы будет выполняться быстрее. О статистике мы еще не раз будем говорить в главе 4.
Теперь посмотрим, как создавать индексы вручную. До этого момента мы использовали индексы, которые сервер создавал автоматически для первичного ключа и уникального поля. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE, но бывает необходимость создать индекс на поле без этих ограничений.
Для создания индекса на произвольное поле используется оператор CREATE INDEX, а для удаления используется DROP INDEX. Вы должны быть владельцем базы данных или администратором, чтобы выполнять эти операторы.
Информация об индексах храниться в системной таблице sysindexes. В главе 2 мы научимся работать с таблицами и просматривать их содержимое. Просто ради интереса попробуйте просмотреть системную таблицу sysindexes. Только не вздумайте ее изменять вручную, системные таблицы можно только просматривать.
Лучше всего, если индекс создается на поле с маленьким типом данных, такой индекс будет более эффективным. Когда вы создаете кластерный индекс, все существующие не кластерные индексы перестраиваются, поэтому желательно в первую очередь создавать кластерный индекс.
В общем виде команда создания индекса выглядит следующим образом:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]
Чтобы удобнее было понять команду, я разбиваю ее на строчки. В первой строке указывается ключевые слова CREATE и INDEX, между которыми можно указать UNIQUE, чтобы индекс был уникальным и CLUSTERED или NONCLUSTERED, чтобы сделать индекс кластерным или не кластерным соответственно. После INDEX указывается имя индекса.
Имя должно быть понятным, должно отображать, что это индекс и желательно, чтобы отражалось имя поля. Я рекомендую использовать для этого формат: "I_CL_Имя". Первая буква I, указывает на то, что это индекс. Затем я ставлю CL или UCL, что будет показывать кластерный или не кластерный индекс. И в самом конце перечисляются имена полей, которые индексируются. В данном случае только одно поле "vcName".
Во вторую строку я пишу ключевое слово ON, за которым идет имя таблицы и в скобках имена индексируемых полей.
Следующий пример создает кластерный индекс на колонку vcName:
CREATE CLUSTERED INDEX I_CL_vcName ON TestTable(vcName)
После имени колонки нужно указать направление сортировки индекса. Направление задается ключевыми словами ASC (возрастание) или DESC (убывание). Следующий пример создает не кластерный индекс по убыванию:
CREATE NONCLUSTERED INDEX I_CL_vcName ON TestTable(vcName DESC)
Теперь поговорим о удалении индексов. Можно удалять только созданные вами индексы. Для этого используется оператор DROP INDEX. Вы не можете использовать этот оператор для удаления индекса, который был автоматически создан на ограничения PRIMARY KEY или UNIQUE. Вы должны удалить ограничение, прежде чем удалять индекс. Нельзя удалять индексы системных таблиц.
Если удалить кластерный индекс, то все не кластерные индексы будут автоматически перестроены.
В общем виде команда удаления индекса выглядит следующим образом:
DROP INDEX 'table.index | view.index' [ ,...n ]
В следующем примере удаляется созданный нами ранее индекс:
DROP INDEX TestTable.I_CL_vcName
Ранее мы уже создавали индекс уникальности, но делали мы это только на этапе создания таблицы. Если она уже существует, то индекс уникальности можно добавить с помощью оператора CREATE UNIQUE INDEX.
Уникальный индекс гарантирует, что все данные в колонке с таким индексом – уникальны, и не содержат повторяющихся значений. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE.
Сервер SQL проверяет дубликаты каждый раз, когда вы выполняете операторы INSERT или UPDATE. Если дубликат существует, то сервер отклоняет ваши операторы и возвращает сообщение об ошибке.
Если повторяющиеся значения существуют, когда вы создаете уникальный индекс, операция CREATE INDEX отклоняется. Сервер возвращает сообщение об ошибке с первым дубликатом, но могут существовать и еще дубликаты. Используйте следующий простой сценарий для любых таблиц, чтобы найти дублирующие значения в колонке.
SELECT индексная колонка, COUNT(индексная колонка) FROM имя таблицы GROUP BY индексная колонка HAVING COUNT (индексная колонка)>1 ORDER BY индексная колонка
Снова мы забегаем вперед, потому что запросы SELECT это тема следующей главы. Если вы не работали с SQL, то этот запрос еще не понятен для вас, но вернитесь к нему после прочтения второй главы, и все встанет на свои места.
Составные индексы используют более одной колонки в качестве ключевого значения. Создавайте составные индексы, когда две или более полей чаще всего используются для поиска в качестве ключа и если запрос ссылается только на все поля в составном индексе. Если запрос будет использовать не все поля, то индекс, скорей всего использоваться не будет.
Для примера, телефонный справочник является хорошим примером. Справочник организован по фамилии. Вместе с фамилией для поиска регулярно используется имя, потому что часто существует много записей для одной фамилии с разными именами и вполне логично создать индекс из фамилии и имени одновремнно.
Вы можете объединять до 16 колонок в составной индекс. Сумма длины всех колонок составного индекса должна быть менее 900 байт. При этом, все поля должны быть из одной таблицы.
Объявляйте сначала уникальные колонки. Первые колонки, описанные в операторе CREATE INDEX, имеют высший приоритет при сортировке. При поиске данных в таблице, ваш запрос должен будет обязательно ссылаться на первую колонку индекса, иначе индекс точно использоваться не будет.
Индекс на поля "Фамилия" и "Имя" это не то же самое, что индекс на поля "Фамилия" и "Имя". Эти индексы имеют разный порядок полей. Например, для первого случая сортировка будет следующей:
Фамилия Имя ----------------------------------------- Иванов Андрей Иванов Сергей Петров Андрей Петров Василий
Те же самые поля, но с индексом "Имя" и "Фамилия" будут отсортированы следующим образом:
Фамилия Имя ----------------------------------------- Иванов Андрей Петров Андрей Петров Василий Иванов Сергей
В данном случае главным является имя, и именно оно сортируется первым.
Составной индекс позволяет повысить производительность запросов и уменьшить количество индексов на таблицу. Производительность повышается за счет того, что сервер для поиска необходимых данных сканирует только один индекс.
Следующий пример создает не кластерный составной индекс для таблицы телефонного справочника. Обратите внимание, что поле "Фамилия" описывается первой, потому что она чаще всего является основой при выборке данных из таблицы:
CREATE UNIQUE NONCLUSTERED INDEX I_NCL_Фамилия_Имя ON [Телефонный справочник] (Фамилия, Имя)
Так как индекс уникальный, в таблицу нельзя будет записать двух людей с фамилией и именем Иванов Андрей.
Сервер SQL предлагает опции, которые могут ускорить создание индекса, а также увеличить производительность индексов.
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.