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

Понятия индексирования

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

Индексные ключи

Индексным ключом называется колонка или колонки, которые используются для формирования индекса. Индексный ключ – это значение, позволяющее быстро находить строку, содержащую нужные вам данные (подобно статье индекса [алфавитного указателя] в книге, указывающей определенную тему в тексте). Для доступа к данным строки через индекс вы должны включить значение или значения индексного ключа в предложение WHERE нужного оператора SQL. Способ выполнения этого процесса зависит от того, какой это индекс – простой или составной.

Простые индексы

Простой индекс определяется только по одной колонке таблицы (рис. 17.4). Чтобы индекс использовался оператором SQL, ссылка на эту колонку должна быть включена в предложение WHERE данного оператора.

В зависимости от типа сохраняемых данных, количества уникальных элементов в колонке и типа используемых операторов SQL простой индекс может оказаться весьма эффективен. В других случаях необходим составной индекс. Например, если вы строите индекс для адресной книги с тысячами имен и адресов, то колонка state (штат) не слишком подходит для простого индекса, поскольку для каждого штата будет много записей. Однако, добавив к индексу колонки street (улица) и city (город), вы делаете его составным индексом, после чего почти каждая запись становится уникальной. Это может оказаться полезным, если у вас используются запросы поиска строк в соответствии с адресом.

Составные индексы

Составной индекс – это индекс, определенный более чем по одной колонке (рис. 17.5). Доступ к составному индексу может осуществляться с помощью одного или нескольких индексных ключей. В рамках SQL Server 2000 индекс может содержать до 16 колонок, и колонки ключей могут иметь длину до 900 байтов.

Для запросов, включающих составной индекс, вам не требуется помещать все индексные ключи в предложение WHERE оператора SQL, но имеет смысл использовать более одного ключа. Например, если индекс создается по колонкам a, b и c какой-либо таблицы, то доступ к этому индексу можно осуществлять с помощью оператора SELECT, содержащего выражение ( a АND b АND c ), или ( a АND b ), или a. Конечно, использование более ограничивающего предложения WHERE, содержащего, например, выражение a АND b АND c, обеспечит более высокую производительность. Скорее всего, из базы данных будет считано меньшее количество строк, поскольку строки будет указаны более точно. Если использовать a АND b или просто a, то будет инициировано сканирование индекса.

Сканирование индекса возникает потому, что критерию поиска соответствует более чем одна запись индекса. При сканировании индекса происходит сканирование узлов внутри индекса для считывание нескольких записей данных. Кроме того, индекс лишь частично соответствует выбранному значению. Например, если индекс создан по колонкам a, b и c, а в запросе указано значение только для колонки a, то будут возвращены все удовлетворяющие этому значению строки для всех значений колонок b и c.

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

Таблица местоположения заказчиков

Предположим, что у нас имеется таблица, содержащая информацию о местоположении заказчиков вашего предприятия. По колонкам state (штат), country (графство) и city (город) создается структура B-дерева в следующем порядке: state, country, city. Если в запросе в предложении WHERE указано значение Texas (Техас) колонки state, то будет использован индекс. Но поскольку значения колонок country и city не заданы в запросе, то индекс возвратит набор строк, исходя из всех записей индекса, содержащих значение Texas в колонке state. Для считывания диапазона индексных страниц используется сканирование индекса и последующее сканирование страниц данных, исходя из значений колонки state. Страницы индекса считываются последовательно таким же образом, как при сканировании таблицы для доступа к страницам данных.

В большинстве случаев сканирование индекса будет достаточно эффективным; но если происходит доступ к более чем 20 процентам строк таблицы, то более эффективным является сканирование таблицы, при котором из таблицы считываются все строки. Эффективность запросов, использующих индекс, зависит от того, как вы используете индекс (см. раздел "Использование индексов" далее), а также от степени уникальности индекса (см. следующий раздел).

Уникальность индекса

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

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

Уникальный индекс содержит только одну строку данных для каждого индексного ключа; иными словами, значения индексного ключа не могут присутствовать в индексе более одного раза. Использование уникальных индексов гарантирует, что для поиска запрошенных данных требуется всего одна дополнительная операция ввода-вывода. SQL Server обеспечивает уникальность индекса по колонкам или комбинации колонок, образующих ключ индекса. SQL Server не допускает занесения дублированных значений ключа в базу данных. Если вы попытаетесь сделать это, появится сообщение об ошибке. SQL Server создает уникальные индексы, если задали по таблице ограничение PRIMARY KEY или ограничение UNIQUE. (Об ограничениях PRIMARY KEY и UNIQUE см. лекцию 16).

Индекс можно сделать уникальным, только если уникальны сами данные. Если данные какой-либо колонки не обладают свойством уникальности, то вы можете все же создать уникальный индекс, используя составной индекс. Например, колонка last name (фамилия), возможно, не будет уникальной, но комбинация данных этой колонки с колонками first name (имя) и middle name (отчество) может образовать уникальный индекс по данной таблице.

Неуникальные индексы

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

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

Типы индексов

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

Кластеризованные индексы

Как уже говорилось, кластеризованный индекс – это индекс в виде B-дерева, где хранятся реальные строки данных таблицы в отсортированном порядке в узлах-листьях (рис. 17.6). Эта система дает несколько преимуществ и имеет несколько недостатков.

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

Еще одним преимуществом кластеризованных индексов является то, что считываемые данные получаются в отсортированном по индексу виде. Например, если кластеризованный индекс создан по колонкам state, country и city и в запросе происходит выбор данных для значения Texas колонки state, то результирующий набор будет отсортирован по колонкам country и city в том порядке, как определен индекс. Эту возможность можно использовать, чтобы избежать ненужных операций сортировки, если приложение и база данных организованы соответствующим образом. Например, если вы знаете, что вам всегда требуется сортировка данных в определенном порядке, то использование кластеризованного индекса означает, что вам не потребуется выполнять сортировку после считывания данных.

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

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

Некластеризованные индексы

В отличие от кластеризованного индекса некластеризованный индекс не содержит реальных данных таблицы в своих узлах-листьях. Узлы-листья могут содержать один из двух типов информации о местоположении строк данных. Во-первых, если по таблице не создан кластеризованный индекс, то некластеризованные индексы по этой таблице хранят в своих узлах-листьях идентификаторы строк (Row ID) (рис. 17.7). Каждый идентификатор строки указывает реальную строку данных в таблице. Идентификатор строки – это значение, включающее в себя номер файла данных, номер страницы и местоположение строки на этой странице. Это значение обеспечивает быстрый доступ к реальным данным, указывая точное местоположение этих данных.

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

Как уже говорилось, каждая таблица может иметь только один кластеризованный индекс. Вы можете создать 249 некластеризованных индексов на одну таблицу, но это было бы неразумно (см. раздел "Рекомендации по использованию индексов"). Обычно на практике используется несколько некластеризованных индексов по различным колонкам таблицам. Чтобы определить, какой индекс использовать, оптимизатор запросов использует предикат в предложении WHERE.

Полнотекстовые индексы

Как уже говорилось, полнотекстовый индекс SQL Server на самом деле больше похож на каталог, чем на индекс, и он имеет структуру, отличную от B-дерева. Полнотекстовый индекс позволяет выполнять поиск по группам ключевых слов. Полнотекстовый индекс является частью службы Microsoft Search; он широко используется в механизмах поиска Wеb-узлов и других текстовых операциях.

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

Полнотекстовый индекс имеет массу возможностей, которых нет в индексах со структурой B-дерева. Поскольку этот индекс используется как механизм текстового поиска, он поддерживает больше возможностей текстового поиска, чем стандартные механизмы. Используя полнотекстовый индекс, вы можете выполнять поиск слов или фраз, отдельных слов или групп слов, а также похожих слов. (О том, как создавать полнотекстовый индекс см. раздел "Использование мастера Full-Text Indexing Wizard" далее.)

Создание индексов

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

Использование мастера Create Index Wizard

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

Откройте Enterprise Manager и щелкните на кнопке Wizards (Мастера) в меню Tools. Появится диалоговое окно Select Wizard (Выбор мастера). В данном примере мы будем использовать базу данных Northwind.

Раскройте папку Database (База данных), выберите Create Index Wizard и затем щелкните на кнопке OK.

Появится начальное окно мастера Create Index Wizard (рис 17.9(рис 17.9) Начальное окно мастера Create Index Wizard

Щелкните на кнопке Next (Далее), чтобы появилось окно Select Database аnd Table (Выбор базы данных и таблицы) (рис 17.10(рис 17.10) Окно Select Database аnd Table (Выбор базы данных и таблицы)

Щелкните на кнопке Next, чтобы перейти к окну Current Index Information (Информация о текущих индексах) (рис 17.11(рис 17.11) Окно Current Index Information (Информация о текущих индексах)Все индексы, созданные по таблице Customers, – это простые индексы, созданные по различным колонкам. Когда оптимизатор запросов анализирует запрос для выбора плана исполнения данного запроса, он выбирает для использования нужный индекс, исходя из имеющихся индексов и предиката в предложении WHERE.

Щелкните на кнопке Next, чтобы появилось окно Select Columns (Выбор колонок) (рис 17.12(рис 17.12) Окно Select Columns (Выбор колонок)

Задайте колонки, которые хотите включить в данный индекс, путем установки флажков справа от имен колонок. В данном примере мы создадим составной индекс по колонкам CompАnyName, ContАсtName и Region.

Щелкните на кнопке Next, чтобы появилось окно Specify Index Options (Задание параметров индекса) (рис. 17.13). В этом окне вы можете задать несколько важных параметров, которые определяют, как будет создаваться индекс. Вы можете установить флажок Make this a clustered index (Сделать этот индекс кластеризованным), чтобы это был кластеризованный индекс. В данном примере флажок, используемый для создания кластеризованного индекса, недоступен, поскольку кластеризованный индекс по таблице Customers уже создан.

Вы можете установить флажок Make this a unique index (Сделать этот индекс уникальным), если хотите, чтобы он был уникальным. Вы можете также задать коэффициент заполнения (Fill fАсtor) как оптимальный (Optimal) или фиксированный (Fixed). Поскольку строки индекса хранятся в отсортированном порядке, системе SQL Server может потребоваться перемещение данных для поддержания этого порядка. Коэффициент заполнения указывает, насколько плотно должны размещаться данные в новом индексе, чтобы оставалось место для будущих вставок. Принятый по умолчанию коэффициент заполнения (который вы получаете, если щелкнуть на кнопке выбора Optimal) равен 0, что означает плотное заполнение узлов-листьев, но свободное пространство в вышележащих узлах индекса. (Подробнее о коэффициенте заполнения см. в разделе "Использование коэффициента заполнения для предупреждения расщеплений страниц" далее.)

(рис 17.13) Окно Specify Index Options

Выберите ваши параметры индекса и затем щелкните на кнопке Next, чтобы появилось окно Completing the Create Index Wizard (Завершение работы мастера создания индекса) (рис. 17.14). В этом окне вы можете изменить порядок колонок, образующих индекс. Просто выделите колонку, которую хотите переместить, и щелкайте на кнопке Move Up (Вверх) или Move Down (Вниз), пока данная колонка не окажется в нужном месте. Вы можете также задать в этом окне имя индекса.

Порядок колонок в составном индексе имеет важное значение. Оператор SQL может использовать преимущества индекса, только если в предложении WHERE этого оператора указана ведущая часть индекса. На рис. 17.15 показано то же окно с измененным именем индекса (CustomerAreaIndex) и другим порядком колонок – Region, CompАnyName, ContАсtName.

(рис 17.15) Окно Completing the Create Index Wizard(рис 17.14) Изменение порядка колонокПри таком порядке колонок оператор SQL должен содержать Region в своем предложении WHERE , чтобы использовать преимущества индекса, поскольку Region – это ведущая колонка. Конечно, оператор может содержать в своем предложении WHERE Region и CompАnyName или даже Region, CompАnyName и ContАсtName. Используя в предложении WHERE все три значения, вы получаете наилучшую производительность, поскольку будет выполнено минимальное количество операций ввода-вывода. И тогда уже не имеет значения, в каком порядке имена колонок указаны в предложении WHERE .

Если вас удовлетворяет порядок колонок, щелкните на кнопке Finish (Готово), после чего будет создан индекс. Этот процесс может занять от нескольких секунд до нескольких часов в зависимости от количества данных, производительности системы, производительности дисководов и памяти системы. Для создания индекса по таблице SQL Server должен прочитать все данные таблицы, поэтому время варьируется в таких широких пределах.Внимание. Если вы создаете уникальный индекс и в ключе индекса обнаружены дублированные значения, процесс создания индекса прекращается. Создание индекса с помощью мастера Create Index Wizard происходит просто, но этот процесс имеет некоторые недостатки. В частности, поскольку мастер Create Index Wizard не поддерживает информацию о задачах, которые выполняются с его помощью, вам приходится проходить через процесс, описанный в этом разделе, каждый раз, как вы создаете новый индекс. Если вы создаете индекс с помощью файла сценария, то можете использовать этот файл снова и снова. Кроме того, если вы хотите повторно создать базу данных, то должны снова пройти через все шаги мастера Create Index Wizard для каждого индекса базы данных. Напомним, однако, что после создания индексов вы можете генерировать для них сценарии SQL с помощью Enterprise Manager.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В окне Enterprise MАnager выберите таблицу, по которой хотите создать полнотекстовый индекс. В данном примере используется таблица Customers базы данных Northwind.

Щелкните на Wizards в меню Tools. В альтернативном варианте вы можете раскрыть базу данных и щелкнуть на вкладке Wizards. Появится диалоговое окно Select Wizard.

Раскройте папку Database в диалоговом окне Select Wizard. Выберите Full-Text Indexing Wizard и щелкните на кнопке OK. Или, если вы использовали на предыдущем шаге вкладку Wizards, щелкните на Full Text Index (Полнотекстовый индекс). Появится начальное окно мастера Full-Text Indexing Wizard (рис. 17.17).

Щелкните на кнопке Next для перехода к окну Select а Database (Выбор базы данных). Мы выберем для нашего примера базу данных Northwind. (Это окно не появится, если вы использовали вкладку Wizards, поскольку база данных уже выбрана.)

Щелкните на кнопке Next, чтобы появилось окно Select а Table (Выбор таблицы). Мы выберем таблицу Customers. Щелкните на кнопке Next.(рис 17.17) Начальное окно мастера Full-Text Indexing Wizard (Полнотекстовый индекс)

Появится диалоговое окно Select аn Index (Выбор индекса) (рис 17.18(рис 17.18) Окно Select аn Index (Выбор индекса)

Щелкните на кнопке Next, чтобы появилось окно Select Table Columns (Выбор колонок таблицы). Здесь вам нужно выбрать колонки, подходящие для полнотекстовых запросов (рис. 17.19).

Щелкните на кнопке Next, чтобы появилось окно Select а Catalog (Выбор каталога) (рис 17.20(рис 17.20) Окно Select Table Columns с несколькими выбранными колонками(рис 17.19) Окно Select а Catalog (Выбор каталога)

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

Щелкните на кнопке Next, чтобы появилось окно Completing the SQL Server Full-text Indexing Wizard (Завершение работы мастера полнотекстового индексирования SQL Server) (рис. 17.22). Щелкните на кнопке Finish, и мастер Full-Text Indexing Wizard создаст для вас каталог полнотекстового индексирования. Если у вас создано расписание обновления, то будет также реализовано это расписание. После создания каталога он доступен для использования.

Создание полнотекстовых индексов с помощью хранимых процедур

Вы можете также создавать полнотекстовые индексы с помощью хранимых процедур. Здесь дается краткий обзор процесса создания полнотекстового индекса с помощью хранимых процедур; для получения полного синтаксиса используйте SQL Server 2000 Books Online.

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

Использование полнотекстового индекса

После создания полнотекстового индекса вы можете легко использовать его возможности. Вы можете задавать ключевые слова T-SQL, позволяющие использовать полнотекстовые индексы: CONTAINS и FREETEXT. В следующем операторе показано, как можно было бы выполнить типичное распознавание строк на SQL, если бы вы не использовали полнотекстовый индекс. В предложении WHERE данного запроса используется ключевое слово LIKE:

SELECT * FROM Customers WHERE ContАсtName
    LIKE '%PETE%'

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

SELECT * FROM Customers WHERE
    CONTAINS(ContАсtName, '"PETE"')

Предикат CONTAINS позволяет находить с помощью полнотекстового индекса текстовые строки, содержащие нужную символьную строку, такую как "PETER" или "PETEY".

Вы можете также выполнять поиск в полнотекстовых индексах с помощью ключевого слова FREETEXT. Как и CONTAINS, ключевое слово FREETEXT используется в предложении WHERE. FREETEXT можно использовать для поиска слова (или похожих слов), смысл которого соответствует смыслу определенного слова (или набора слов), указанного при вызове FREETEXT, но форма не полностью совпадает с указанным словом. Это можно сделать с помощью оператора SQL, аналогичного следующему:

SELECT CategoryName FROM Categories WHERE
    FREETEXT(Description, 'Sweets cАndy bread')

Этот запрос может найти категориальные имена, содержащие такие слова, как "sweetened", "cАndied" или "breads".

Перестроение индексов

SQL Server поддерживает по каждому индексу статистику, описывающую степень его уникальности (или избирательности) и распределение значений ключей индексов. Оптимизатор запросов SQL Server затем использует эту статистику, чтобы определить (если это нужно), какой индекс наиболее отвечает определенному запросу. Статистика по индексам периодически обновляется по умолчанию. Но иногда индексы по прошествии времени становятся фрагментированными из-за расщеплений страниц, что приводит к физическому разбросу страниц индекса в базе данных. Это приводит к ухудшению производительности. Может также возникать дисбаланс индекса, означающий, что одна часть дерева содержит более заполненные страницы индекса, чем другая часть. Вы можете восстановить баланс и физическую связность путем перестроения индекса. Кроме того, во время перестроения индекса происходит пересчет статистики. Но вам не нужно удалять и снова создавать индекс.

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

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

Имеется два метода перестроения индекса без его удаления и повторного создания: оператор CREATE INDEX...DROP_EXISTING и использование DBCC DBREINDEX. Оба этих средства выполняют перестроение индекса за один шаг, и SQL Server "знает", что нужно реорганизовать существующий индекс. Использование этих методов позволяет избежать удаления и повторного создания некластеризованных индексов, когда вы перестраиваете кластеризованный индекс. Эти одношаговые методы также используют отсортированный порядок данных, находящихся в индексе; повторная сортировка этих данных уже не требуется.

CREATE INDEX...DROP_EXISTING используется для единовременного перестроения только одного индекса по таблице. DBCC DBREINDEX используется с именем базы данных и именем таблицы для перестроения всех индексов по этой таблице без необходимости запуска отдельных команд для каждого индекса. Синтаксис и параметры эти двух операторов см. в Books Online.

Обновление статистики по индексам

Если у вас нет времени или ресурсов для повторного создания индексов, то вы можете обновлять статистику по индексам независимо. Этот метод не столь эффективен, как перестроение индекса, поскольку индекс может быть фрагментирован, что может оказаться более серьезной проблемой, чем устаревшая статистика. При этом также предполагается, что вы отключили автоматическое обновление статистики в SQL Server. (Иначе ваша статистика будет в любом случае периодически обновляться в автоматическом режиме.) Вы можете обновлять статистику по индексам вручную с помощью оператора UPDATE STATISTICS. Она имеет следующий синтаксис:

UPDATE STATISTICS имя_таблицы
[ имя_индекса | (имя_статистики[, имя_статистики, ...] ]
[ WITH
[ FULLSCАN | SAMPLE число {PERCENT | ROWS} ]
[ ALL | COLUMNS | INDEX ]
[ NORECOMPUTE]
]

Значения в прямоугольных скобках является необязательными. Единственный обязательный параметр – это имя_таблицы. Необязательные параметры перечислены в табл. 17.2.

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

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

Теперь, когда вы знаете, как создавать индексы, рассмотрим использование индексов. Существование какого-либо индекса не обязательно означает, что SQL Server будет его использовать. Это зависит от самого индекса и используемого оператора SQL. Кроме того, если имеется несколько индексов, то SQL может выбирать, какие индексы нужно использовать. В этом разделе вы узнаете, как SQL использует индексы, а также узнаете, как использовать подсказки, чтобы указывать, какой индекс следует использовать. Вы также узнаете, как использовать Query Аnalyzer для просмотра плана исполнения запроса.

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

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

Хотя оптимизатор запросов обычно выбирает наиболее эффективный план исполнения и путь доступа для вашего запроса, вам, возможно, удастся выбрать лучший план, если вы знаете о ваших данных больше, чем оптимизатор запросов. Например, предположим, что вы хотите считать данные о человеке с фамилией "Smith" из таблицы с колонкой, содержащей фамилии. Статистика по индексу обобщается на основе этой колонки. Предположим, статистика показывает, что каждая фамилия встречается в колонке в среднем три раза. Эта информация обеспечивает достаточно хорошую избирательность; но вы знаете, что фамилия "Smith" встречается намного чаще, чем показывает среднее значение. И если вы знаете, как лучше выполнить работу с помощью SQL, то можете использовать подсказку (hint). Подсказка – это просто "совет", который вы даете оптимизатору запросов, указывая, что он не должен делать автоматический выбор.

Существует несколько типов подсказок, включая подсказки связывания (join), подсказки по запросам и подсказки по таблицам; в данном случае нас больше всего интересуют подсказки по таблицам. Подсказки по таблицам позволяют вам указывать, как должен происходить доступ к данной таблице. (О других типах подсказок см. лекцию 35.) Подсказки по таблицам можно использовать для указания следующей информации:

Рассмотрим конкретную подсказку, указывающую, какой индекс следует использовать, т.е. индексную подсказку. В следующем примере показана индексная подсказка в операторе T-SQL (использовать индекс Region для данного запроса):

SELECT *
FROM Customers WITH (INDEX(Region))  
WHERE region = 'OR' АND city = 'PortlАnd'

Отметим, что перед индексной подсказкой указано ключевое слово WITH. Если вы хотите задать несколько индексов, чтобы их использовал SQL Server, перечислите их в операторе T-SQL, аналогичном следующему:

SELECT *
FROM customers WITH (INDEX(Region, City, CompАnyName))  
WHERE region = 'OR' АND city = 'PortlАnd'

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

Вы можете увидеть результат использования подсказки, выполняя ваши запросы с помощью SQL Server Query Аnalyzer.

Использование Query Аnalyzer

В лекции 13 вы узнали, что Query Аnalyzer – это полезное средство, включенное в состав SQL Server 2000. Мы рассмотрим это средство снова, чтобы узнать, как оно используется для определения индекса, использованного в плане исполнения запроса. Query Аnalyzer можно также использовать для любой из следующих задач:

В качестве эксперимента загрузите следующий оператор T-SQL в Query Аnalyzer:

SELECT *
FROM customers 
WHERE region = 'OR' АND city = 'PortlАnd'

Теперь посмотрим оценочный план исполнения (Estimated Execution PlАn) после выбора пункта Display Estimated Execution PlАn (Отображение оценочного плана исполнения) в меню Query (Запрос). Из рисунка 17.23 видно, что используется индекс City.

А теперь добавим подсказку, которая указывает SQL Server, что нужно использовать индекс Region. Теперь запрос выглядит следующим образом:

SELECT *
FROM customers WITH (INDEX(Region))  
WHERE region = 'OR' АND city = 'PortlАnd'

Оценочный план исполнения для этого запроса показан на рис. 17.24. Отметим, что теперь используется индекс Region.

QL Server Query Аnalyzer очень полезен и удобен для запуска операторов SQL не только за счет соответствующего GUI, но также за счет возможности синтаксического разбора и анализа операторов SQL. Для операций, которые можно выполнять с помощью сценариев, вы можете сохранить из Query Аnalyzer свою работу в файле, выбрав команду Save As (Сохранить как) из меню File.

Формирование эффективных индексов

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

Характеристики эффективного индекса

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

В эффективном индексе считывается лишь несколько строк. Для эффективной работы индекс должен иметь хорошую избирательность. Избирательность индекса определяется количеством строк на одно значение индексного ключа. Индекс с низкой избирательностью имеет много строк, приходящихся на одно значение индексного ключа; в индексе с хорошей избирательностью на одно значение индексного ключа приходится немного строк или только одна строка. Уникальный индекс имеет наиболее высокую избирательность. Показатель избирательности индекса хранится в статистике распределения индекса. Вы можете увидеть показатель избирательности индекса с помощью оператора DBCC SHOW_STATISTICS. Оптимизатор запросов, скорее всего, будет использовать индекс с хорошей избирательностью.

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

Когда используются индексы

Индексы наиболее подходят для задач следующего типа:

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

Рекомендации по использованию индексов

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

Заключение

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