SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Индексы. Теоретические основы.

ПУБЛИКАЦИИ  

Вольный перевод главы книги Design SQL Server 2000 Exam 70-229 © Sybex 2001
Автор: Геннадий Гречкосий

СОДЕРЖАНИЕ

1. Введение

Наверное нет большой нужды повторять что индексы могут существенно ускорить доступ к данным. Это не является гарантированным правилом и, например, при небольшом размере таблицы, простой перебор записей может быть более эффективен чем выборка по индексам.

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

[В начало]

2. Кучи и Индексы

Физически данные хранятся на 8Кб страницах. Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения. Рис 1. иллюстрирует таблицу Customers из базы данных Northwind, хранящихся в виде кучи.

Рис. 1 Куча (A heap)

Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу что бы найти искомые записи. Например мы хотим найти запись, удовлетворяющую условию:

SELECT * FROM Customers WHERE CustomerID = ‘ROMEY’

SQL Server прочитает все записи начиная с первой и заканчивая последней и выберет те, которые будут удовлетворять указанному условию. SQL Server не знает что в таблице существует только одна запись, удовлетворяющая условию, пока в таблице не существует unique constraint, unique index или primary key. Во всех трёх перечисленных случаях создается индекс для поддержания ограничения. Приведенный пример иллюстрирует две базовые функции индексов:

  • увеличение скорости доступа к данным

  • поддержка уникальности данных

Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей. Однако алгоритмы построения индексов разработаны таким образом что бы иметь как можно меньший негативный эффект для указанных операций и даже позволяет выполнять их быстрее, как будет показано позднее.

В SQL Server индексы хранятся в виде B-деревьев (B-tree). “B” означает сбалансированное (не путать с бинарным). Рис 2 показывает индекс, созданный для поля CustomerID для таблицы Customers.

Рис. 2 B-Tree индекс

Теперь если выполнить предыдущий запрос по поиску записи CustomerID = ‘ROMEY’, будут прочитаны только страницы 30, 22 и 10 в указанном порядке.

Как указывалось ранее индексы в SQL Server представляют собой сбалансированные деревья. Это означает, что длины веток для всех ответвлений индекса, одинаковы. Если посмотреть на Рис. 2 сверху вниз, вам придется просканировать только три страницы что бы найти запись удовлетворяющую условию. Каждая ветка сбалансирована и внутренний механизм построения индексов держит это дерево сбалансированным при любых изменениях в таблице.

Обратите внимание что на Рис. 2 данные не отсортированы. Это значит что при создании индекса должен быть создан уровень листьев (leaf level), содержащий указатели на данные отсортированные по указанному ключу (это уровень, обозначенный страницами с 20 по 23). На Рис.2 указатель представляет собой row ID, который имеет следующий формат: НомерФайла:НомерСтраницы:ПозицияЗаписи. Таким образом ID 1:13:5 указывает пятую запись, тринадцатой страницы в файле номер один. Любой другой уровень индексов называется не лепестковым или промежуточным уровнем. Самый первый уровень индексов является “входной дверью” в индекс и называется корневым. Корневой уровень состоит только из одной страницы и содержит указатели на ключи следующих уровней индекса.

Все индексы имеют одинаковую В-tree структуру. SQL Server предлагает к использованию два типа индекса: кластерный (clustered) и некластерный (nonclustered). Разница между этими типами индексов будет освещена ниже.

[В начало]

3. Кластерный индекс

В RDBMS, понятие “кластерный” имеет много значений, но общая идея этого понятия – рассмотрение двух физических объектов как единую сущность. Например, в области построения сетей, кластер есть группа двух и более серверов, видимых как единая логическая сущность и используется для отказоустойчивости и выравнивания (балансировки) нагрузки. В SQL Server кластер означает индекс, смешанный с данными. Таблица представляет собой часть индекса, или индекс представляет собой часть таблицы в зависимости от вашей точки зрения.

Рис.3 Кластерный индекс

Фактически, для кластерного индекса leaf level этого индекса есть сами страницы таблицы с данными. Рис.3 показывает кластерный индекс, созданный по полю CustomerID таблицы Customers.

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

В SQL Server кластерный индекс является уникальным индексом по определению. Это означает что все ключи записей должны быть уникальные. Если существуют записи с одинаковыми значениями, SQL Server делает их уникальными, добавляя номера из внутреннего (невидимого снаружи) счетчика. Рис. 4 иллюстрирует этот случай. Почему архитектура SQL Server имеет такую функциональность? Ответ прост – потому что есть только два способа найти необходимую запись: по row ID или по ключу кластерного индекса. Row ID используется когда нет кластерного индекса, и кластерный индекс – в противном случае.

На Рис.3 ключ кластерного индекса построен по полю CustomerID, которое по определению имеет только уникальные значения. На Рис.4, например, предположим что кластерный ключ был изменен на поле City. Более чем один клиент может существовать в каждом городе. Например, есть 4 клиента в Mexico. Таким образом SQL Server добавит номера счетчика к дублирующимся записям Mexico, делая эти записи уникальными. Если еще одно значение Mexico будет добавлено в таблицу, его кластерный ключ будет Mexico4.

Первое дублирующееся значение не имеет значения счетчика. Счетчик начинается с первого повторения значения.

Рис.4 Кластерный индекс

Номер, добавляемый к дублирующимся значениям является результатом работы автоматического счетчика. Но добавляемое значение не будет видно ни конечному пользователю, ни разработчику. Оно просто имеет внутреннюю поддержку для идентификации записи.

Запомните правило, когда работаете с SQL Server: запись может быть найдена либо по row ID либо по кластерному ключу. Это важное замечание, поскольку row ID или кластерный ключ будут сохраняться внутри не кластерного индекса и использоваться для получения реальных данных из записей.

[В начало]

4. Не кластерный индекс

Некластерный индекс имеет leaf level, который содержит все ключевые значения, отсортированные в том виде как был определен индекс, вместе с row ID или кластерным ключом. Сами данные не хранятся в индексе и вынимаются из таблицы, используя row ID или ключ кластерного индекса. Рис.5 иллюстрирует некластерный индекс по полю City. Как видно в указанном примере, таблица не имеет кластерного индекса потому что ссылкой на запись является row ID.

Рис.5 Некластерный индекс

Кластерный индекс использует row locator и он является частью не кластерного индекса на leaf level. Этот факт приводит к важному правилу SQL Server: создавайте кластерные ключи как можно более короткими. Каждый некластерный индекс будет использовать значения кластерного индекса. Следовательно увеличение размера кластерного индекса приводит к многократному увеличению требований по памяти для всех не кластерных индексов. Последнее приводит к увеличению времени на процессы чтения, сканирования данных и, как следствие, к снижению общей производительности системы. Еще одно наблюдение – увеличение длины ключа приводит к снижению количества записей индекса, способных уместиться в пределах одной страницы, как следствие – к увеличению операций чтения-записи. Рис.6 показывает как строится некластерный индекс поверх кластерного.

Рис 6 Некластерный индекс поверх кластерного

[В начало]

5. Составной ключ

До этого момента мы обсуждали индексы, созданные на базе одного поля таблицы. Но индекс может быть создан на основании нескольких полей. В этом случае существует только одно ограничение – длина ключа индекса не должна превышать 900 байтов. Если индекс построен по полям с фиксированным размером, сумма длин этих полей должна не превышать эти 900 байт, если индекс построен по полям с переменной длинной, сумма максимальных размеров полей может превышать 900 байт, но само значение сумм по каждой записи не может быть больше 900 байт. Например в таблице есть два поля переменной длины по 500 байт. SQL Server позволяет создать составной (композитный) ключ на базе этих двух полей, если нет записей, сумма длин по обеим полям превышает 900 байт.

Стоит обратить внимание на тот момент, что составной индекс для (Column1, Column2) является совершенно отличным от (Column2, Column1), а так же от индексов созданных по двум этим полям в отдельности. Как будет показано позже, Query Optimizer может использовать все эти индексы в зависимости от структуры запроса.

[В начало]

6. Уникальные индексы

Уникальный индекс существует для реализации целостности данных в БД. Как рассказывалось ранее целостность данных может быть гарантирована или Unique constrain или primary key. В основе обоих лежит уникальный индекс.

Уникальный индекс гарантирует что каждое значение является уникальным в индексируемом поле или в случае с составным индексом – любая группа значений по полям составного индекса является уникальной. После создания уникального индекса, при попытке вставить повторяющееся значение, вы получите сообщение об ошибке 2601: Cannot insert duplicate key row in object tablename with index indexname.

Теперь вы знакомы с терминологией и типами индексов используемых в SQL Server и мы можем приступить к рассмотрению как SQL Server производит работу с данными при наличии индексов и при их отсутствии.

[В начало]

7. Доступ к записям при наличии или отсутствии индексов

В зависимости от наличия/отсутствия индексов и их типов, SQL Server может выбрать различные способы доступа к данным таблицы:

  • Сканирование таблицы. Когда индексы не используются и таблица не имеет кластерного индекса. Таблица храниться как куча (heap)

  • Выборка данных по кластерному индексу

  • Выборка данных по не кластерному индексу

[В начало]

8. Сканирование таблицы

Когда запрос выполняется к таблице не имеющей кластерного индекса и без использования не кластерных индексов, выполняется простое сканирование таблицы. Для того что бы найти данные в куче (heap), SQL Server использует Index Allocation Map (IAM). IAM представляет страницу которая содержит карту всех экстентов, которые содержат данные указанной таблицы. SQL Server использует IAM что бы найти все страницы с данными. Рис.7 иллюстрирует методологию используемую SQL Server. 1 означает что экстент используется объектом, а 0 – что не используется.

Любой запрос, который не может использовать индексы, работает по следующей методологии:

  1. SQL Server делает запрос к системной таблице Sysindexes для нахождения FirstIAM страницы.

  2. SQL Server берет IAM страницу и находит экстенты где хранится таблица.

  3. SQL Server берет данные из страниц и экстентов, найденных по IAM

Как вы видите, последовательность записей, получаемых конечным пользователем зависит от того в каком порядке данные хранятся в IAM а не от порядка вставки записей в таблицу. Это объясняет что нет никакой логики в порядке полученных данных, поскольку порядок их получения зависит от их порядка в IAM.

Выполним следующий запрос:

SELECT id, indid, fistIAM FROM sysindexes WHERE id=OBJECT_ID(‘CustomerCustomerDemo’)

Получим результат:

id indid FirstIAM
1189579276 0 0x510100000100

Рис.7 Доступ к таблице без индекса

Заметьте что indid имеет значение 0, что означает что данная таблица хранится как heap.

В случае когда производится поиск по таблице без индексов, производится простое сканирование всех записей.

Эта операция показывается в query execution plan следующей иконкой:

[В начало]

9. Доступ к данным с использованием кластерного индекса

Как только в таблице имеется кластерный индекс, IAM более не используется для доступа к данным. IAM не исчезает вообще, но используется только для сопровождения таблицы как объекта в базе данных. Страницы данных взаимосвязаны и данные в них находятся в соответствии с кластерным индексом.

Если выполнить запрос SELECT * FROM Customers без каких-либо условий WHERE, то система выполнить сканирование таблицы с использованием кластерного индекса. Эта операция очень похожа на простое сканирование таблицы. Главное различие между сканированием по кластерному индексу то, что результат сканирования вернется в порядке сортировки по кластерному индексу.

Эта операция показывается в query execution plan следующей иконкой:

Теперь если выполнить поиск по кластерному индексу, SQL Server будет выполнять индексный поиск. Например:

SELECT * FROM Customers WHERE customerid = ‘ALFKI’

Эта операция показывается в query execution plan следующей иконкой:

Заметьте что стрелочка изогнута что индицирует что SQL Server использует индекс для нахождения соответствующего значения. При выполнении этой операции SQL Server находит root page из таблицы Sysindexes и ищет совпадение значений по индексу. Рис.8 Показывает как выполняется указанный поиск.

Рис. 8

Сначала SQL Server находит root page используя запрос

SELECT id, indid, root FROM sysindexes WHERE id = OBJECT_ID(‘Customers’)</p>

Рассмотрим результат:

Id Indid root
2073058421 1 0x890100000100

Indid равно 1, что указывает на то что это кластерный индекс. В случае heap это значение будет равно 0.

Значение root - входная дверь для индекса. Как только отправная точка будет найдена, SQL Server найдет все значения удовлетворяющей условию WHERE. Это проиллюстрировано на Рис.8 Мы ищем записи, значение которых равно ‘London’. С root страницы мы находим что запись ‘London’ находится между ‘Johannesburg’ и ‘London 2’. Таким образом записи, удовлетворяющие условию начинаются на странице 11, где находится ‘Johannesburg’. Мы помним что записи отсортированы по этому полю. Следовательно SQL Server будет отбирать записи до конца 11й страницы и перейдет на следующую страницу пока будет находить записи ‘London’ (помните что страницы прилинкованы)

В этом конкретном примере, SQL Server сканирует три физические страницы вместо пяти при полном сканировании таблицы.

[В начало]

10. Доступ к данным с использованием не кластерного индекса

При доступе к данным при помощи не кластерного индекса, следует обратить внимание что способ доступа будет зависеть от наличия кластерного индекса в таблице. Вспомним, что существует только два пути доступа к записям таблицы: через row ID и через кластерный индекс.

Если удалить все индексы таблицы Customers и создать некластерный индекс по полю City, план исполнения запроса будет выглядеть следующим образом:

SQL Server использует индекс для нахождения записей, совпадающих с условиями запроса. На leaf level индекса, будут взяты row locator (row ID или cluster key).

Выполним запрос


SELECT id, indid, root

FROM sysindexes

WHERE id = OBJECT_ID(‘Customers’)

Мы получим результат похожий на следующий:

Id Indid root
2073058421 2 0x890100000100

Заметим что значение Indid имеет значение 2, индицирующее что запись ссылается на некластерный индекс. Значение между 2 и 250 зарезервированы для не кластерных индексов.

Рис.9 Иллюстрирует процесс поиска когда таблица не имеет кластерного индекса. Как только SQL Server найдет root страницу, он сканирует root страницу и находит страницу(ы), которые соответствуют искомому результату.

В нашем примере сервер читает 4 страницы, но выполняет доступ к 5 страницам. Поскольку искомое значение появляется дважды на одной физической странице, то будет произведено два логических чтения этой страницы, даже если было произведено только одно физическое чтение этой страницы. Если сравнить поиск с кластерным индексам, заметим что некластерный индекс имеет дополнительный leaf level, роль которого в кластерном выполняют сами данные.

Рис.10 показывает поиск по не кластерному индексу при наличии кластерного. Разница с поиском без кластерного в том что SQL Server находит кластерные ключи на последнем уровне не кластерного индекса. А используя кластерный ключ, находит данные в таблице. Заметим что план выполнения запроса, увы, не показывает разницы.

Рис. 9

Теперь вы имеете полное представление как SQL Server использует индексы при выборке данных. Теперь остается один большой вопрос – как SQL Server решает какие индексы использовать для конкретного запроса.

Рис. 10

[В начало]

11. Статистика и выбор индексов

Посмотрим на приме с книгой: иногда бывает два и более индекса по книге. Например по теме и по ключевым словам. Читатель выбирает индекс, соответствующий его требованиям для конкретного случая. SQL Server поступает тем же самым образом. Но остается вопрос – если таблица имеет один кластерный индекс и четыре не кластерных, как SQL Server узнает какой из индексов использовать. Распределение статистики дает query optimiser возможность выбрать определенный индекс.

[В начало]

12. Распределение статистики

Индексы не выбираются на основании анализа распределенной статистики. Немного истории – в SQL Server 6.5 статистика хранилась на странице в 2 Кб, независимо от размера индекса. Таким образом при увеличении размера индекса, уменьшалась точность описания распределения данных по индексу. В SQL Server 2000 статистика хранится в поле данных для хранения имиджей. То есть – растет пропорционально размеру индекса, это позволяет не терять точность при увеличении размера индексов.

Что бы понять что из себя представляет статистика, рассмотрим следующий пример. Возьмем таблицу Orders из базы данных Northwind. Если мы выполним следующий запрос:

SELECT TOP 24 OrderID, convert(char(11), OrderDate) FROM Orders ORDER BY OrderDate

Вы получите следующий результат:

OrderID OrderDate 10248 Jul 4 1996 10249 Jul 5 1996 10250 Jul 8 1996 10251 Jul 8 1996 10252 Jul 9 1996 10253 Jul 10 1996 10254 Jul 11 1996 10255 Jul 12 1996 10256 Jul 15 1996 10257 Jul 16 1996 10258 Jul 17 1996 10259 Jul 18 1996 10260 Jul 19 1996 10261 Jul 19 1996 10262 Jul 22 1996 10263 Jul 23 1996 10264 Jul 24 1996 10265 Jul 25 1996 10266 Jul 26 1996 10267 Jul 29 1996 10268 Jul 30 1996 10269 Jul 31 1996 10270 Aug 1 1996 10271 Aug 1 1996

В этом примере выбраны записи с 4 июля 1996 по 1 августа 1996. Теперь посчитаем сколько раз встречаются те или иные значения:

SELECT convert(char(11), A.orderdate) as OrderDate , count(*) as '# of OrderDate' FROM (SELECT TOP 24 OrderID, OrderDate FROM Orders ORDER BY OrderDate) as A GROUP BY A.orderdate OrderDate # of OrderDate' Jul 4 1996 1 Jul 5 1996 1 Jul 8 1996 2 Jul 9 1996 1 Jul 10 1996 1 Jul 11 1996 1 Jul 12 1996 1 Jul 15 1996 1 Jul 16 1996 1 Jul 17 1996 1 Jul 18 1996 1 Jul 19 1996 2 Jul 22 1996 1 Jul 23 1996 1 Jul 24 1996 1 Jul 25 1996 1 Jul 26 1996 1 Jul 29 1996 1 Jul 30 1996 1 Jul 31 1996 1 Aug 1 1996 2

Когда SQL Server считает или сортирует данные, он заранее знает как много тех или иных значений он найдет в указанном запросе. Например, выполним следующий запрос:

SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-15’ AND ‘1996-07-20’

SQL Server знает что запрос вернет только 6 записей еще до того как будет произведен доступ к таблице. Назначение статистики базируется на простом алгоритме: для выбора стратегии доступа к данным, SQL Server должен знать как много записей вернет запрос.

В SQL Server статистика не просто учитывает количество записей для того или иного значения. Первое, статистика более точно описывает распределение данных по значениям. Второе, зона описания статистики может не содержать всех значений индексируемого поля. Например, в таблице Orders 830 записей, но статистика хранится только для 186 значений. Как и почему именно 187 – этот алгоритм не рассматривается в данном издании.

Выполним команду

dbcc show_statistics (Orders,OrderDate) Statistics for INDEX 'OrderDate'. Updated Rows Rows Sampled Steps Density Average key length ----------------------------------------------------------------------------- Dec 23 2002 9:30AM 830 830 187 1.6842016E-3 12.0

Этот результат показывает следующее

  • статистика была просчитана в последний раз 23 декабря 2002

  • таблица содержит 830 записей

  • все записи были проанализированы для получения статистики

  • в статистике информация сохранена в дискретности на 187 записей

  • средняя плотность распределения примерно 0.17%

Пожалуй самым интересным здесь будет значение плотности распределения. Если каждое значение в таблице уникальное, то плотность будет 1/830, то есть 0.12%. Но в нашем примере мы имеем 0.17% показывает что некоторые значения встречаются 2 и более раз. Например мы видим что для 8 июля 1996 встречается дважды. Теория говорит, что чем меньше плотность, тем лучше – это увеличивает избирательность, а следовательно и ценность построенного индекса.

Например если колонка содержит только 3 значения, плотность распределения будет равна 33.3%, что показывает бесполезность построения индекса по данному полю. Индексы занимают место на диске и в оперативной памяти и отнимает быстродействие. В идеале самый лучший индекс имеет плотность распределения равную единице, деленной на количество записей в таблице - все записи уникальны. При построении индексов, обращайте внимание на плотность распределения – если она превышает 10%, то индекс можно считать бесполезным. Сканирование по таблице в таком случае будет более эффективным.

Второй результат, возвращаемый командой dbcc show_statistics

All density Average Length Columns ---------------------------------------------------------------- 2.0833334E-3 8.0 OrderDate 1.2048193E-3 12.0 OrderDate, OrderID

Это дает очень интересный результат потому что плотность для одного поля OrderDate 0.2%, а для пары OrderDate, OrderID уже 0.12. Поскольку OrderID является primary key для таблице, то понижение плотности вполне очевидно.

Последний результат может быть наиболее интересен для полного понимания важности статистики.

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS 1996-07-04 0.0 1.0 0 1996-07-15 7.0 1.0 6 1996-07-19 3.0 2.0 3 1996-07-25 3.0 1.0 3 1996-08-01 4.0 2.0 4

Во-первых, заметим что только пять значений в зоне распределения вместо 24 в самой таблице. Тем не менее учитывая значения по всем колонками, система знает сколько записей будет в выборке. Колонка RANGE_HI_KEY дает высшее значение для значения, сохраненного в статистике. Мы знаем что 1996-07-04 является первым значением и следующим за ним идет 1996-07-15. Между этими двумя значениями находится 7 записей. Колонка RANGE_ROWS дает нам эту информацию. Только три значения есть между 1996-07-15 и 1996-07-19, и так далее. Колонка DISTINCT_RANGE_ROWS содержит информацию о том сколько определенных (неповторяющихся) значений в интервале. Например в интервале с 1996-07-04 по 1996-07-15 есть 6 определенных значений из 7 записей в интервале. Это говорит что одно значение в указанном интервале повторяется дважды. Мы не указали еще одну колонку в результате команды dbcc show_statistics - AVG_RANGE_ROWS, которое является результатом простой арифметической операции RANGE_ROWS /DISTINCT_RANGE_ROWS.

Анализируя эту информацию, можно определить как распределены данные в индексе, а так же как много записей вернет запрос.

[В начало]

13. Выбор индексов

Как мы видели в предыдущих примерах, SQL Server достаточно точно знает как данные распределены по таблице при наличии индекса. Каждый раз при выполнении запроса, SQL Server первым делом оценивает статистику. Давайте рассмотрим несколько примеров для более полного понимания данного процесса.

SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’

SQL Server сначала проверяет существования индекса по полю OrderDate или составного индекса, начинающегося с поля OrderDate. В таком случае SQL Server знает как много записей вернется в результате запроса. Query Analyser дает следующую картинку:

Для того что бы посмотреть детали каждого этапа выполнения запроса, просто выберете нужный этап и кликните правой клавишей мыши.

Мы видим что оценочное количество записей – 6, что и реально соответствует действительности.

Теперь выполним этот запрос:

SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’ AND CustomerID = ‘FOLKO’

SQL Server найдет один индекс OrderDate и один CustomerID. Он будет использовать их обоих и результатом будет пересечение по этим двум условиям. Оценка дает 6 значений по OrderDate и только одно для CustomerID. В этом конкретном случае SQL Server вернет только одно значение.

Если заменить условие операции AND на OR, система может сделать объединение индексов или может предпочесть полное сканирование таблицы, если сочтет что такой путь будет более быстрым и имеет меньшую стоимость чем работа с индексами. В нашем конкретном случае в результате запроса:

SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-19' AND '1996-07-25' OR CustomerID = 'FOLKO'

Было произведено полное сканирование таблицы так как стоимость операции OR оказалась выше. Это объясняется тем что размеры таблицы сравнительно малы.

Что же произойдет, если для таблицы нет индексов, следовательно нет статистики? Ответ прост: SQL Server не может работать без статистики! И он создаст статистику без каких либо индексов. Если выполнить запрос:

SELECT * FROM Orders WHERE ShipCity=’Grass’

SQL Server автоматически создаст статистику для этого поля, поскольку нет индекса по нему. Для того что бы просмотреть все индексы, созданные для определенной таблицы, выполним запрос:

SELECT name, first, root FROM sysindexes WHERE id=OBJECT_ID(‘Orders’)

Мы получили результат:

name first root PK_Orders 0xCD0000000100 0xCB0000000100 CustomerID 0x690100000100 0x6C0100000100 CustomersOrders 0x6F0100000100 0x720100000100 EmployeeID 0xDA0000000100 0xDA0000000100 EmployeesOrders 0xDC0000000100 0xDC0000000100 OrderDate 0xDE0000000100 0xDE0000000100 ShippedDate 0xE00000000100 0xE00000000100 ShippersOrders 0xE20000000100 0xE20000000100 ShipPostalCode 0x740100000100 0x770100000100 _WA_Sys_ShipCity_014935CB 0x000000000000 0x000000000000

Заметьте имя “индекса” _WA_Sys_ShipCity_014935CB с root адресам 0х0 – потому что это не индекс. Это статистика для поля ShipCity. Вам не стоит беспокоится о засорении вашей базы данных ненужной информацией – автоматически созданная статистика будет уничтожена так же автоматически когда SQL Server посчитает что она более не нужна. Просто доверьтесь SQL Server. (Хорош оборот, неправда ли!?)

Вы можете так же изучить наличие статистики при помощи команды:

sp_helpstats ‘Orders’ statistics_name statistics_keys _WA_Sys_ShipCity_014935CB ShipCity

А так же при выполнении команды Tools a Manage Statistics в Query Analyser.

Эта статистика была создана автоматически потому что параметр базы данных AUTO_CREATE_STATISTICS установлен в “On”.

Автоматическое обновление статистики является огромной помощью для DBA. Порой крайне сложно определить какая же статистика должна быть создана и эту функцию на себя взял SQL Server. Существует только одна проблема - поддерживать корректную на каждый момент времени статистику.

[В начало]

14. Обслуживание статистики.

Что произойдет если статистика устареет и не будет отражать реальную картину с данными в таблице. Ответ очень прост – выбор индекса может быть неверен. Представим что статистика была собрана когда в таблице было только 1 000 записей, а теперь ее размер 100 000. Что бы быть реально полезной, статистика должна содержать текущие реальные данные.

SQL Server позволяет обновлять статистику автоматически без привлечения дополнительных усилий со стороны DBA. Проверить, установлен ли флаг автоматического обновления статистики, можно командой:

SELECT DATABASEPROPERTYEX(‘dbname’,’IsAutoUpdateStatistics’)

Если результат равен 1, это значит что опция автоматического обновления статистики включена. Установить опцию в этот режим можно командой

ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON

а выключить

ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF

Так же эту операцию можно установить используя хранимую процедуру sp_dboption, но в SQL Server 2000 она оставлена только для обратной совместимости и может исчезнуть в будущих версиях.

Рекомендуется оставлять эту функцию включенной. В этом случает SQL Server будет сам обновлять статистику когда посчитает ее устаревшей. Алгоритм обновления полностью определяется SQL Server и зависит от количества обновлений, удалений и добавлений записей в таблицу. Если таблица имеет один миллион записей и только 100 из них были изменены (0.01%), вряд ли имеет смысл обновлять статистику поскольку такие изменения в таблице вряд ли драматически поменяли общую картину данных.

Кроме того если размер таблицы более 8МБ (1 000 страниц), SQL Server не будет использовать все данные для вычисления статистики. Все эти ограничения разработаны для того что бы работа со обновлением статистики наносила как можно меньший удар на быстродействие сервера.

Так же управлять автоматическим обновлением статистики можно при помощи хранимых процедур типа sp_autostats:

Команда

sp_autostats ‘Orders’

показывает включена ли опция автоматического обновления статистики для конкретных индексов и когда обновление было сделано в последний раз. Так же эта команда позволяет включить или отключить опцию обновление для всех индексов таблицы или для какого-то конкретного индекса. Итого что бы отключить автоматическое обновление статистики у вас есть несколько вариантов:

  • ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS

  • sp_autostats

  • используйте STATISTICS_NORECOMPUTE в команде CREATE INDEX

  • используйте NORECOMPUTE в STATISTICS UPDATES или CREATE STATISTICS

Как только автоматическое обновление будет отключено, вы будете вынуждены обновлять статистику вручную. Эта операция может быть выполнена при сопровождении индексов или операцией UPDATE STATISTICS.

Полное описание UPDATE STATISTICS стоит изучить по BOL.

[В начало]

15. Создание индексов и статистики.

Создание индексов и статистики достаточно простой процесс и выполняется командами CREATE INDEX и CREATE STATISTICS. Давайте посмотрим как же индексы создаются.

[В начало]

16. Индексы.

Как мы изучили ранее, существует два вида индексов: кластерные и не кластерные. В дополнение к этим типам, еще существуют дополнительные опции как: уникальный индекс, сложный индекс и т.д. Все это многообразие выбора необходимо для работы с индексами наиболее подходящими к конкретной ситуации.

[В начало]

16.1. Кластерные и не кластерные индексы

По умолчанию, индекс всегда создается не кластерным, если не указано иное. Таблица может иметь только один кластерный индекс.

Процедура sp_helpindex возвращает полный список всех индексов для таблицы.

Например

sp_helpindex Orders index_name index_description index_keys CustomerID nonclustered located on PRIMARY CustomerID CustomersOrders nonclustered located on PRIMARY CustomerID EmployeeID nonclustered located on PRIMARY EmployeeID EmployeesOrders nonclustered located on PRIMARY EmployeeID OrderDate nonclustered located on PRIMARY OrderDate PK_Orders clustered, unique, primary key located on PRIMARY OrderID ShippedDate nonclustered located on PRIMARY ShippedDate ShippersOrders nonclustered located on PRIMARY ShipVia ShipPostalCode nonclustered located on PRIMARY ShipPostalCode

Вы так же можете получить более удобный результат при выборе команды Tools a Manage indexes в Query Analyser.

Заметим что все индексы находятся в PRIMARY file group. В случае необходимости повышения быстродействия, можно поместить индексы в другую file group, отличную от местонахождения таблицы, для баланса I/O операций между разными физическими дисками.

Команда CREATE INDEX имеет ряд параметров. Давайте рассмотрим некоторые из них.

1) ASC|DESC

    ASC значит что индекс будет построен по возрастанию ключей. DESC соответственно – по убыванию. Эта опция не дает никакой разницы на поиск данных, но оказывает существенное влияние на скорость выполнения ORDER BY опции в запросах.

2) SORT_IN_TEMPDB

    Полезна при создании индексов. Эта опция может увеличить производительность системы во время создания индексов. Индексы создаются в два этапа. На первом создается временный набор данных с отсортированными ключами для не кластерного индекса. На втором – окончательный результат переносится на место его хранения в базе данных. Без указания данной опции временный результат создается в той же file group, где и будет создан индекс. При указанной опции, временный результат будет находиться в базе данных Tempdb. Преимущества очевидны, особенно если поместить базу данных Tempdb на другой физический диск. Выигрыш будет получен на обеих стадиях так как чтение и запись данных будут вестись параллельно на двух разных дисках. Вторая положительная сторона использования указанного параметра – созданный индекс будет менее фрагментирован. Отрицательный момент – в общем требуется больше памяти на дисках и надо уделять дополнительное внимание базе данных Tempdb.

3) IGNORE_DUP_KEY

    Очень хитрая опция. Она рассматривает поведение в случае вставки данных в уникальный столбец (или группу столбцов). Без этой опции если идет попытка вставки дублирующего значения, то вся вставка будет откачена (rolled back).

    Server: Msg 2601, Level 14, State 3, Line 1 Cannot insert duplicate key row in object 'TestTable' with unique index 'id1'. The statement has been terminated.

    С этой опцией вставка записей будет продолжена, а дублирующие записи отброшены с сообщением warning:

    Server: Msg 3604, Level 16, State 1, Line 2 Duplicate key was ignored.

    Пример для проверки:

    create table TestTemp (c1 int, c2 varchar(10)) create table TestTable (c1 int, c2 varchar(10)) create table TestTable_IGNORE_DUP_KEY (c1 int, c2 varchar(10)) create unique index id1 on TestTable(c1) create unique index id2 on TestTable_IGNORE_DUP_KEY(c1) with IGNORE_DUP_KEY insert TestTemp values(1,'test') insert TestTemp values(1,'test') insert TestTable select * from TestTemp insert TestTable_IGNORE_DUP_KEY select * from TestTemp

[В начало]

16.2. Уникальный индекс

По умолчанию индексы позволяют иметь дублирующие значения. Если необходимо что бы значения были уникальны, можно включить опцию UNIQUE в CREATE INDEX. Если при создании индекса будут найдены дублирующие значения, создание индекса будет остановлено с сообщением об ошибке:

Server: Msg 1505, Level 16, State 1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '1'. The statement has been terminated.

Кластерный индекс строится с опцией CLUSTERED:

create CLUSTERED index id3 on TestTemp(c1)

При попытке создания кластерного индекса на таблице, где он уже существует вы получите ошибку:

Server: Msg 1902, Level 16, State 3, Line 1 Cannot create more than one clustered index on table 'TestTemp'. Drop the existing clustered index 'id3' before creating another.

[В начало]

16.3. Композитный индекс

В создании композитного (сложного) индекса участвуют несколько полей таблицы. При создании индекса следует обращать внимание на порядок следования полей в индексе.

Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе где в критериях используются оба этих поля. Так же этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен.

Если в дополнении к индексу по полям Field1, Field2 добавить индекс по полям Field2, Field1, то SQL Server при построении плана запроса будет анализировать какой из них более селективен в применении к ограничениям на условия запроса. Последний момент в построении композитного индекса по полям Field1, Field2 – он не равен сумме индексов по указанным полям. В случае когда в запросе могут быть использованы оба поля как критерий поиска при раздельных индексах по полям будет построено пересечение по индексам, что медленнее чем выборка из компаундного индекса.

[В начало]

16.4. Индекс по вычисляемому полю

SQL Server 7 представил новую возможность – вычисляемые поля в таблице. SQL Server 2000 добавили возможность построения индекса по этому полю. Достоинство нововведения почти очевидна – при отсутствии индекса, значение вычисляется на лету при выполнении запроса. При наличии индекса, результат хранится на leaf level индекса и вычисляется во время добавления/изменения записи. Однако это выполняется только в том случае если этот индекс задействован при выполнении запроса.

Индекс по вычисляемому полю может построен при выполнении ряда ограничений:

  • вычисляемое значение должно быть определенным и прецизионным. Определить выполнение этого правила можно выполнив пару проверок:

select columnproperty(object_id('tablename'),'columnname','IsDeterministic') select columnproperty(object_id('tablename'),'columnname',' IsPrecise')

оба запроса должны дать результат 1

  • опция ANSI_NULLS должна была быть установлена в ON при создании таблицы. Проверяется запросом:

select OBJECTPROPERTY(object_id('tablename'),'IsAnsiNullsOn')

  • вычисляемое поле не должно иметь тип text, ntext или image

Далее, шесть опций должны быть установлены в ON во время создания индекса:

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNING

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER

И одна в OFF:

  • NUMERIC_ROUNDABOUT

Все эти опции кроме ARITHABORT установлены корректно при использовании OLE-DB или ODBC. Это можно поправить следующим скриптом:

declare @val int select @val=(value|64) from master..spt_values join master..sysconfigures on number=config where name = 'user options' exec sp_configure 'user options', @val RECONFIGURE

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

[В начало]

16.5. FillFactor

Fillfactor очень важный параметр для уменьшения фрагментирования. Он указывает на процент заполнения страницы на leaf level. Например если fillfactor=100, это значит при создании индекса на leaf level страницы будут заполнены на 100%. Это обеспечивает наименьшее фрагментирование данных на диске, но при вставке или изменении записи возможно расщепление страницы, что вызывает дополнительные I/O операции. При выставлении fillfactor меньше 100, на каждой вновь создаваемой странице индекса на leaf level будет оставляться свободное место и при вставке данных расщепление производиться не будет. То есть, значение 100 имеет смысл когда идет работа со статическими данными или для таблиц, где производится только добавление данных в конец таблицы(например индекс по identity полю). Опыт показывает что часто обновляемых таблиц оптимальным следует считать значение 80. Стоит так же помнить что SQL Server не придерживается строго указанных цифр – если, например, fillfactor выставлен в 50, а на странице может содержаться информация о 13 записях, то реально SQL Server будет хранить 7 записей, что дает реальный fillfactor 53.8

Помимо параметра fillfactor, регламентирующего заполнение на leaf level, существует параметр PAD_INDEX, который определяет заполнение на более высоких уровнях.

[В начало]

16.6. Статистика

Мы видели что система может сама создавать статистику для полей таблицы когда индекс не существует. Это работает, но отнимает ресурсы при выполнении запроса. Более того – автоматически создаваемая статистика создается только для одного поля, а для оптимизатора может быть необходима статистика по нескольким полям сразу. Для изучения синтаксиса CREATE STATISTICS обратитесь к BOL.

Помните что статистика оказывает воздействие на решения принимаемые оптимизатором при построении плана запроса, но не оказывает воздействие на скорость выполнения запроса. Эта функция лежит на индексах.

[В начало]

17. Фрагментация и сопровождение индексов

Не смотря на то что большое число операций в SQL Server 2000 автоматизированы, сопровождение индексов может быть значительной частью работы администратора БД. Если понизилось быстродействие – первое на что следует обратить внимание, фрагментация БД. Фрагментация БД возникает когда данные в БД меняются.

[В начало]

17.1. Типы фрагментаций

Когда запись удаляется, в файле БД высвобождается место. Когда вставляется новая запись, это может привести к расщеплению страниц, что приводит к появлению пустого пространства на страницах данных. Когда данный обновляются, это может привести к изменению размера записи и к возникновению двух ранее упоминавшихся случаев. Все это приводит к фрагментации. В SQL Server рассматриваются два типа фрагментации: внутренняя и внешняя.

Внутренняя подразумевает пустоты внутри страницы. Внешняя – непоследовательность связей страниц.

Если страницы не полностью заполнены данными, это приводит к дополнительным операциям I/O и переиспользованью оперативной памяти. Помните что страницы в оперативной памяти есть зеркальное отражение страниц на диске.

В идеале страницы должны быть подлинкованы слева направо в порядке хранения данных. Вследствие расщепления страниц этот порядок может быть нарушен. Это приводит как к неполному заполнению страниц, так и к увеличению операций I/O вследствие непоследовательного положения цепочек страниц на диске – это вызывает дополнительные перемещения головок с цилиндра на цилиндр диска. А это одна из наиболее медленных дисковых операций.

Команда DBCC SHOWCONTIG помогает определить как внутреннюю так и внешнюю фрагментацию.

Выполним команду

DBCC SHOWCONTIG('Orders')

Изучим результат:

DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 20 - Extents Scanned..............................: 5 - Extent Switches..............................: 4 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 60.00% [3:5] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 40.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19%

Команда DBCC SHOWCONTIG работает на leaf level поэтому дает ответ только о положении страниц.

Расшифруем результат:

  • Pages Scanned указывает количество страниц в таблице. В нашем примере их 20.

  • Extents Scanned показывает количество экстентов занимаемых таблицей. Это сразу указывает на фрагментированность данных – для сохранения 20 страниц хватает 3х экстентов.

  • Extent Switches говорит о количестве раз переключения с экстента на экстент при последовательном чтении данных. В идеальной ситуации это число равно Extents Scanned – 1

  • Avg. Pages per Extent говорит о среднем количестве страниц на экстент при перемещении по цепочке страниц. Это значение должно быть как можно ближе к 8

  • Scan Density представляет собой значение для внешней фрагментации. Этот результат получается от соотношения идеальной смены экстентов к фактической. Вполне очевидно, это что должно быть близко к 100%

  • Logical Scan Fragmentation дает процент страниц не в логическом порядке. Если страницы находятся в строгой последовательности слева направо, то данный параметр будет иметь значение 0

  • Extent Scan Fragmentation дает процент экстентов не в логическом порядке. Имеет то же логическое значение что и Logical Scan Fragmentation

  • Avg. Bytes Free per Page – должно быть как можно ближе к 0 если fill factor 100. Иное значение требует незначительных расчетов. Если fill factor 80, это обеспечивает примерно 1600 свободных байтов на страницу.

  • Avg. Page Density должно быть как можно ближе к 100%. Avg. Bytes Free per Page и Avg. Page Density дают хорошее представление о внутренней фрагментации.

В нашем примере мы имеем Avg. Page Density 98.19%, что означает что нет внутренней фрагментации (длина записей не всегда совпадает с размером страницы). С другой стороны Scan Density 60% и Extent Scan Fragmentation 40% говорит о внешней фрагментации. Если мы дефрагментируем таблицу а выполним эту команду еще раз, мы получим следующий результат:

DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 20 - Extents Scanned..............................: 3 - Extent Switches..............................: 2 - Avg. Pages per Extent........................: 6.7 - Scan Density [Best Count:Actual Count].......: 100.00% [3:5] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19%

Insert

Вставка может приводить к фрагментации на leaf level. Кластерные индексы особенно чувствительны к вставке данных.

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

Updates

При обновлении SQL Server всегда старается оставить запись на старом месте и избежать ее переноса на новое место. Но это невозможно при увеличении длины записи. В случае переноса записи SQL Server использует указатели на новое место. Это позволяет не перестраивать индексы.

Стремление не обновлять индексы наносит негативный удар по производительности поскольку приводит к фрагментированию базы данных. Данный алгоритм вполне оправдан для OLTP БД.

Deletes

Как мы видели вставки приводят к внутренней и внешней фрагментации, а обновления к внешней. Удаления записи приводит к внутренней фрагментации – делает “дырки” на страницах. При удалении записи, SQL Server не удаляет физически эти записи, а помечает их как удаленные. При вставке или обновлении записей, свободное место от удаленных записей может быть переиспользовано. В противном случае каждые пол часа запускается процесс, который утилизирует удаленные записи.

Удаление записей приводит в внутренней фрагментации и переиспользование места на диске приводит к дополнительным операциям I/O. И не забывайте что страницы на диске копируются в память – нерациональное использование дисковой памяти приводит к переиспользованью оперативной памяти.

Поэтому следует внимательно следить за фрагментацией таблиц для достижения максимального быстродействия системы.

[В начало]

17.2. Что делать дальше

Теперь вы понимаете как работает вставка, обновление и удаление данных и как это приводит к фрагментации. Для дефрагментации в SQL Server существует три пути:

  • DBCC INDEXDEFRAG

  • DBCC DBREINDEX

  • CREATE INDEX WITH DROP_EXISTING

DBCC INDEXDEFRAG производит дефрагментирование leaf level для всех типов индексов и исправляет как внутреннюю так и внешнюю дефрагментацию. Однако эта команда не создает новые страницы, а лишь перетряхивает информацию в уже имеющихся.

Таким образом сильно фрагментированные индексы не получат реальной помощи от этой команды. Эта команда имеет одно из главных достоинств: она накладывает блокировку на очень короткий период в отличии от иных команд.

DBCC DBREINDEX может использована для перестройки индексов и возможно для изменения fillfactor. Если вы хотите перестроить все индексы для таблицы, необходимо выдать команду c пустым вторым параметром:

[В начало]

17.3. DBCC DBREINDEX (Orders,’’)

Опция WITH DROP_EXISTING для команды CREATE INDEX позволяет удалить индексы и перестроить их при помощи одно команды. Эта команда особенно полезна для кластерного индекса поскольку некластерный индекс может быть не перестроен. Посмотрим на пример: вы выполняете команду CREATE INDEX WITH DROP_EXISTING без перестройки ключей. Поскольку кластерный индекс выполняет функцию row locator, то он остается не перестроенным. Следовательно и все остальные индексы останутся нетронутыми. Если будет удален кластерный индекс, это потребует перестройки всех не кластерных индексов (сменился row locator). Таким образом для перестройки всех индексов таблицы достаточно выдать команду для перестройки одного кластерного индекса. Вы можете модифицировать структуру индексов во время использования опции WITH DROP_EXISTING но только с одним ограничением – невозможно преобразование кластерного индекса в некластерный.

Вот пример хранимой процедуры, выполняющей перестройку индексов для всех таблиц базы данных:

CREATE PROCEDURE sp_reindex_all_tables AS DECLARE reindex_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN reindex_cursor DECLARE @tablename sysname FETCH NEXT FROM reindex_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN EXECUTE ('DBCC DBREINDEX ("" + @tablename + "",""")') FETCH NEXT FROM reindex_cursor INTO @tablename END CLOSE reindex_cursor DEALLOCATE reindex_cursor

[Содержание]

Перевод: Геннадий Гречкосий  2003г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013