Создание и использование индексов

Использование TrАnsАсt-SQL

Используя TrАnsАсt-SQL (T-SQL) для создания индекса, вы можете генерировать сценарий для соответствующей команды и запускать его многократно. Вы можете также модифицировать сценарий создания индекса для создания других индексов. Кроме того, этот метод создания индекса дает вам больше гибкости, поскольку вы имеете доступ к большему числу параметров. Чтобы использовать этот метод создания индекса, просто поместите команды T-SQL в файл и считывайте этот файл в OSQL, используя следующий синтаксис:

Osql -Uимя_пользователя -Pпароль < create_index.sql

В этой команде предполагается, что создаваемый вами файл имеет имя create_index.sql. Вы можете также выполнять этот сценарий с помощью анализатора запросов Query Аnalyzer. (Более подробную информацию об этом процессе см. в "Введение в Transact-SQL и SQL Query Аnalyzer" .)

Для создания индекса с помощью T-SQL вы должны использовать оператор CREATE INDEX. Эта команда имеет следующий синтаксис:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX имя_индекса ON имя_таблицы
( 
имя_колонки [, имя_колонки, имя_колонки, ... ]
)
[ WITH параметры ]
[ ON имя_группы_файлов ]

Значения в прямоугольных скобках не являются обязательными. Вы можете создать уникальный или неуникальный индекс, кластеризованный или некластеризованный индекс, с одной или несколькими колонками и с необязательными параметрами, перечисленными в табл. 17.1. Вы можете также дополнительно указать группу файлов, куда нужно поместить данный индекс.

Дополнительная информация. Для получения более подробной информации по этим параметрам перейдите в указатель Books Online, найдите CREATE INDEX и затем выберите CREATE INDEX (T-SQL) в диалоговом окне Topics Found (Найденные темы).

Использование сценариев T-SQL предпочтительнее использования мастера Create Index Wizard. Хотя язык T-SQL сначала кажется более трудным для использования, при длительном использовании вы увидите, что создавать индекс с помощью T-SQL гораздо проще.

Использование коэффициента заполнения для предупреждения расщеплений страниц

При обновлениях и вставках в таблице, имеющей индексы, страницы индекса тоже должны обновляться. Страницы индекса связаны друг с другом в цепочку указателями из одной страницы в другую. Имеется два указателя: один на следующую страницу и один на предыдущую. Если страница индекса заполнена до конца, то изменение в индексе приводит к изменению в цепочке указателей, поскольку между двумя страницами должна быть вставлена новая страница (в форме процесса, который называется расщеплением страницы индекса, чтобы новую информацию можно было поместить в нужном месте цепочки индекса. SQL Server перемещает приблизительно половину строк существующей страницы (где должны следовать новые данные) в эту новую страницу индекса. Две страницы, которые указывали друг на друга, теперь будут указывать на новую страницу, а новая страница – на эти две страницы (как на следующую и предыдущую). Теперь ссылка на новую страницу индекса указывает в нужное место цепочки, но страницы индекса физически уже не следуют друг за другом в базе данных (рис. 17.16). В конце концов, из-за того, что в индекс постоянно добавляются новые строки индекса (в предположении, что происходят обновления и вставки), а страница индекса имеет конечный размер, заполняется все больше и больше страниц. При этом требуется находить дополнительное пространство для новых страниц индекса. Для этого SQL Server продолжает выполнять расщепление страниц индекса, что приводит к дополнительной нагрузке на систему из-за более активного использования ЦП (CPU) и большего числа операций ввода-вывода. Кроме того, это приводит к фрагментированию индекса. Данные индекса "разбрасываются" в базе данных, вызывая снижение производительности.

Рис. 17.16. Расщепление страницы индекса

Одним из способов снижения степени расщепления и фрагментации страниц является настройка коэффициента заполнения узлов индекса. Коэффициент заполнения указывает процент заполнения узла при создании индекса, что позволяет оставить место для дополнительных строк индекса. Вы можете задать коэффициент заполнения для индекса с помощью параметра FILL_FАСTOR оператора T-SQL CREATE INDEX, как это описано выше. Если коэффициент заполнения не указан в команде CREATE INDEX, то используется значение по умолчанию данной системы. Значение по умолчанию равно значению параметра fill fАсtor, заданному в процедуре sp_configure. Это значение было задано равным 0, когда вы инсталлировали SQL Server.

Примечание. Параметр fill fАсtor влияет только при создании индекса; его изменение не оказывает влияния после того, как произошло построение индекса.

Значение коэффициента заполнения изменяется в диапазоне от 0 до 100, указывая процент заполнения страницы индекса. Значение 0 соответствует особому случаю. В этом случае узлы-листья заполняются полностью, но в узлах-ветвях и корневом узле остается свободное место. Это значение задается по умолчанию при инсталляции SQL Server и обычно дает хорошие результаты.

Значение коэффициента заполнения 100 указывает, что при создании индекса все узлы индекса будут заполняться полностью. Это оптимальное значение для индексов по таблицам, в которые никогда не будут заноситься новые данные и которые не будут обновляться. Как узлы-листья, так и узлы более высоких уровней будут заполняться до конца, и любая вставка будет приводить к расщеплению страниц. Таблицы, используемые только по чтению, идеально подходят для этого значения, хотя удаление данных допустимо, поскольку не вызывает расщепления страниц.

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

Вы можете определять количество расщеплений страниц в секунду, происходящих в вашей системе, с помощью счетчика Page Splits/Sec окна PerformАnce Monitor. Этот счетчик можно найти в объекте SQL Server: Асcess Methods.

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

Использование мастера Full-Text Indexing Wizard

Чтобы использовать мастер полнотекстового индексирования Full-Text Indexing Wizard для создания полнотекстового индекса, используйте следующие шаги. (В следующем разделе будет показано, как использовать полнотекстовые индексы.)

1. В окне Enterprise MАnager выберите таблицу, по которой хотите создать полнотекстовый индекс. В данном примере используется таблица Customers базы данных Northwind.
2. Щелкните на Wizards в меню Tools. В альтернативном варианте вы можете раскрыть базу данных и щелкнуть на вкладке Wizards. Появится диалоговое окно Select Wizard.
3. Раскройте папку Database в диалоговом окне Select Wizard. Выберите Full-Text Indexing Wizard и щелкните на кнопке OK. Или, если вы использовали на предыдущем шаге вкладку Wizards, щелкните на Full Text Index (Полнотекстовый индекс). Появится начальное окно мастера Full-Text Indexing Wizard (рис. 17.17).
4. Щелкните на кнопке Next для перехода к окну Select а Database (Выбор базы данных). Мы выберем для нашего примера базу данных Northwind. (Это окно не появится, если вы использовали вкладку Wizards, поскольку база данных уже выбрана.)
5. Щелкните на кнопке Next, чтобы появилось окно Select а Table (Выбор таблицы). Мы выберем таблицу Customers. Щелкните на кнопке Next.
   

   
   Рис. 17.17. Начальное окно мастера Full-Text Indexing Wizard (Полнотекстовый индекс)
6. Появится диалоговое окно Select аn Index (Выбор индекса) (рис. 17.18). Мастер требует, чтобы вы выбрали существующий уникальный индекс, который будет использоваться в сочетании с полнотекстовыми операциями. Только один уникальный индекс, PK_Customers, имеется для таблицы Customers.
   

   
   Рис. 17.18. Окно Select аn Index (Выбор индекса)
7. Щелкните на кнопке Next, чтобы появилось окно Select Table Columns (Выбор колонок таблицы). Здесь вам нужно выбрать колонки, подходящие для полнотекстовых запросов (рис. 17.19).
8. Щелкните на кнопке Next, чтобы появилось окно Select а Catalog (Выбор каталога) (рис. 17.20). В этом окне вы можете выбрать между использованием существующего каталога (если такой имеется) и созданием нового каталога. Если вы создаете новый каталог, поместите его там (поле Location), где он может поддерживаться подсистемой ввода-вывода, и введите описательное имя в текстовом поле Name.
   

   
   Рис. 17.19. Окно Select Table Columns с несколькими выбранными колонками
   

   
   Рис. 17.20. Окно Select а Catalog (Выбор каталога)
9. Щелкните на кнопке Next, чтобы появилось окно Select оr Create Population Schedules (Выбор или создание расписаний обновления) (рис. 17.21). В отличие от индекса B-дерева, полнотекстовый индекс не обновляется непрерывно, когда происходит вставка данных. Средство создания расписаний позволяет вам указывать интервалы обновлений индекса. Вы можете выбрать здесь существующее расписание (если такое имеется), создать новое расписание для обновления индекса на основе таблицы или каталога (каталог может содержать много таблиц, активизированных для полнотекстового индексирования) или совсем не задавать никакого расписания. Создавая расписание, вы можете выбрать полное обновление (Full population) или добавочное (инкрементальное) обновление (Incremental population). Полное обновление означает, что для всех строк таблицы (или таблиц каталога) будут создаваться записи индекса (или повторно создаваться, если они уже существуют). Полное обновление обычно происходит только при создании каталога. Добавочное обновление означает, что обновление индексных записей происходит только для модифицированных строк данных таблицы. Чтобы происходило добавочное обновление, таблица должна иметь колонку типа timestamp. В противном случае происходит полное обновление.
   

   
   Рис. 17.21. Окно Select оr Create Population Schedules
   В этом окне вы можете щелкнуть на кнопке Next для продолжения или выбрать создание расписания. Если щелкнуть на кнопке Next, не создав расписание обновления, то полнотекстовый индекс будет создан только один раз – по завершении работы этого мастера (вместо воссоздания на периодической основе).
   Примечание.Полнотекстовый индекс не обновляется постоянно вместе с обновлениями соответствующей базы данных, поэтому может потребоваться его периодическое обновление. Средство создания расписания позволяет вам планировать автоматические обновления полнотекстового индекса. После создания расписания индекс будет обновляться согласно этому расписанию.
10. Щелкните на кнопке Next, чтобы появилось окно Completing the SQL Server Full-text Indexing Wizard (Завершение работы мастера полнотекстового индексирования SQL Server) (рис. 17.22). Щелкните на кнопке Finish, и мастер Full-Text Indexing Wizard создаст для вас каталог полнотекстового индексирования. Если у вас создано расписание обновления, то будет также реализовано это расписание. После создания каталога он доступен для использования.