# Indexes
Indexes are a data structure that contains pointers to the contents of a table arranged in a specific order, to help the database optimize queries. They are similar to the index of book, where the pages (rows of the table) are indexed by their page number.
Several types of indexes exist, and can be created on a table. When an index exists on the columns used in a query’s WHERE clause, JOIN clause, or ORDER BY clause, it can substantially improve query performance.
# Creating an Index
This will create an index for the column EmployeeId in the table Cars. This index will improve the speed of queries asking the server to sort or select by values in EmployeeId, such as the following:
The index can contain more than 1 column, as in the following;
In this case, the index would be useful for queries asking to sort or select by all included columns, if the set of conditions is ordered in the same way. That means that when retrieving the data, it can find the rows to retrieve using the index, instead of looking through the full table.
For example, the following case would utilize the second index;
If the order differs, however, the index does not have the same advantages, as in the following;
The index is not as helpful because the database must retrieve the entire index, across all values of EmployeeId and CarID, in order to find which items have OwnerId = 17 .
(The index may still be used; it may be the case that the query optimizer finds that retrieving the index and filtering on the OwnerId , then retrieving only the needed rows is faster than retrieving the full table, especially if the table is large.)
# Clustered, Unique, and Sorted Indexes
Indexes can have several characteristics that can be set either at creation, or by altering existing indexes.
The above SQL statement creates a new clustered index on Employees. Clustered indexes are indexes that dictate the actual structure of the table; the table itself is sorted to match the structure of the index. That means there can be at most one clustered index on a table. If a clustered index already exists on the table, the above statement will fail. (Tables with no clustered indexes are also called heaps.)
This will create an unique index for the column Email in the table Customers. This index, along with speeding up queries like a normal index, will also force every email address in that column to be unique. If a row is inserted or updated with a non-unique Email value, the insertion or update will, by default, fail.
This creates an index on Customers which also creates a table constraint that the EmployeeID must be unique. (This will fail if the column is not currently unique — in this case, if there are employees who share an ID.)
This creates an index that is sorted in descending order. By default, indexes (in MSSQL server, at least) are ascending, but that can be changed.
# Sorted Index
If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional sorting when in retrieval.
When you execute the query
The database system would not do additional sorting, since it can do an index-lookup in that order.
# Dropping an Index, or Disabling and Rebuilding it
We can use command DROP to delete our index. In this example we will DROP the index called ix_cars_employee_id on the table Cars.
This deletes the index entirely, and if the index is clustered, will remove any clustering. It cannot be rebuilt without recreating the index, which can be slow and computationally expensive. As an alternative, the index can be disabled:
This allows the table to retain the structure, along with the metadata about the index.
Critically, this retains the index statistics, so that it is possible to easily evaluate the change. If warranted, the index can then later be rebuilt, instead of being recreated completely;
# Partial or Filtered Index
SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows.
Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1).
If your business make use of queries like this:
Partial indexing allows you to limit the index, including only the unfinished orders:
This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index.
# Inserting with a Unique Index
This will fail if an unique index is set on the Email column of Customers. However, alternate behavior can be defined for this case:
# Rebuild index
Over the course of time B-Tree indexes may become fragmented because of updating/deleting/inserting data. In SQLServer terminology we can have internal (index page which is half empty ) and external (logical page order doesn’t correspond physical order). Rebuilding index is very similar to dropping and re-creating it.
We can re-build an index with
By default rebuilding index is offline operation which locks the table and prevents DML against it , but many RDBMS allow online rebuilding. Also, some DB vendors offer alternatives to index rebuilding such as REORGANIZE (SQLServer) or COALESCE / SHRINK SPACE (Oracle).
# SAP ASE: Drop index
This command will drop index in the table. It works on SAP ASE server.
Syntax:
Example:
# Unique Index that Allows NULLS
This schema allows for a 0..1 relationship — people can have zero or one driving licenses and each license can only belong to one person
# Clustered index
When using clustered index, the rows of the table are sorted by the column to which the clustered index is applied. Therefore, there can be only one clustered index on the table because you can’t order the table by two different columns.
Generally, it is best to use clustered index when performing reads on big data tables. The donwside of clustered index is when writing to table and data need to be reorganized (resorted).
An example of creating a clustered index on a table Employees on column Employee_Surname:
# Non clustered index
Nonclustered indexes are stored separately from the table. Each index in this structure contains a pointer to the row in the table which it represents.
This pointers are called a row locators. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
An example of creating a non clustered index on table Employees and column Employee_Surname:
There can be multiple nonclustered indexes on the table. The read operations are generally slower with non clustered indexes than with clustered indexes as you have to go first to index and than to the table. There are no restrictions in write operations however.
# Remarks
Indexes are a way of speeding up read queries by sorting the rows of a table according to a column.
The effect of an index is not noticeable for small databases like the example, but if there are a large number of rows, it can greatly improve performance. Instead of checking every row of the table, the server can do a binary search on the index.
The tradeoff for creating an index is write speed and database size. Storing the index takes space. Also, every time an INSERT is done or the column is updated, the index must be updated. This is not as expensive an operation as scanning the entire table on a SELECT query, but it is still something to keep in mind.
Создание кластерных и некластеризованных индексов в SQL Server
В SQL Server существует два типа индексов; Кластерные и некластеризованные индексы. И кластерные, и некластеризованные индексы имеют одинаковую физическую структуру. Более того, оба они хранятся в SQL Server как структура B-Tree.
Кластерный индекс:
Кластеризованный список – это особый тип индекса, который перестраивает физическое хранение записей в таблице. В SQL Server индексы используются для ускорения операций с базой данных, что обеспечивает высокую производительность. Таким образом, таблица может иметь только один кластеризованный индекс, что обычно выполняется по первичному ключу. Концевые узлы кластерного индекса содержат «страницы данных». Таблица может иметь только один кластерный индекс.
Давайте создадим кластерный индекс, чтобы иметь лучший понимание. Прежде всего, нам нужно создать базу данных.
Создание базы данных
Для создания базы данных. Щелкните правой кнопкой мыши «Базы данных» в проводнике объектов и выберите вариант «Новая база данных» . Введите имя базы данных и нажмите ОК. База данных была создана, как показано на рисунке ниже.
Теперь мы создадим таблицу с именем «Сотрудник» с первичным ключом с помощью представления дизайна. Как видно на рисунке ниже, мы назначили в первую очередь поле с именем «ID», и мы не создали никакого индекса для таблицы.
Вы также можете создать таблицу, выполнив следующий код.
Результат будет следующим.
Приведенный выше код создал таблицу с именем «Сотрудник» с полем идентификатора, уникальным идентификатором в качестве первичного ключа. Теперь в этой таблице кластеризованный индекс будет автоматически создан по идентификатору столбца из-за ограничений первичного ключа. Если вы хотите увидеть все индексы в таблице, запустите хранимую процедуру «sp_helpindex». Выполните следующий код, чтобы увидеть все индексы в таблице с именем «Employee». Эта процедура хранения принимает имя таблицы в качестве входного параметра.
Результат будет следующим.
Другой способ просмотреть индексы таблиц – перейти к «таблицам» в проводнике объектов . Выберите стол и расходуйте его. В папке indexes вы можете увидеть все индексы, относящиеся к этой конкретной таблице, как показано на рисунке ниже.
Поскольку это кластерный индекс, логический и физический порядок индекса будет таким же. Это означает, что если запись имеет идентификатор 3, то она будет сохранена в третьей строке таблицы. Точно так же, если пятая запись имеет идентификатор 6, она будет сохранена в 5 th месте таблицы. Чтобы понять порядок записей, вам необходимо выполнить следующий скрипт.
Althoug h записей хранятся в столбце «Id» в случайном порядке значений. Но из-за кластерного индекса в столбце id. Записи физически хранятся в порядке возрастания значений в столбце id. Чтобы проверить это, нам нужно выполнить следующий код.
Результат будет следующим.
Как видно на приведенном выше рисунке, записи были получены в порядке возрастания значений в столбце id.
Настраиваемый кластерный индекс
Вы также можете создать настраиваемый кластерный индекс. Поскольку мы можем создать только один кластерный индекс, нам нужно удалить предыдущий. Чтобы удалить индекс, выполните следующий код.
Результат будет следующим.
Теперь, чтобы создать индекс, выполните следующий код в окне запроса. Этот индекс был создан для нескольких столбцов, поэтому он называется составным индексом.
Результат будет следующим.
Некластеризованный индекс:
Некластеризованный индекс — это особый тип индекса, в котором логический порядок индекса не соответствует физическому порядку строк, хранящихся на диске. Конечный узел некластеризованного индекса не содержит страниц данных, а содержит информацию о строках индекса. Таблица может содержать до 249 индексов. По умолчанию ограничение уникального ключа создает некластеризованный индекс. В операции чтения некластеризованные индексы работают медленнее, чем кластеризованные индексы. Некластеризованный индекс содержит копию данных из индексированных столбцов, хранящуюся в порядке вместе со ссылками на фактические строки данных; указатели на кластерный список, если таковые имеются. Поэтому рекомендуется выбирать только те столбцы, которые используются в индексе, вместо использования *. Таким образом, данные могут быть получены непосредственно из дублирующего индекса. В противном случае кластерный индекс также используется для выбора оставшихся столбцов, если он создается.
Синтаксис, используемый для создания некластеризованного индекса, аналогичен кластеризованному индексу. Однако ключевое слово «НЕКЛАСТЕРНЫЙ» используется вместо «CLUSTERED» в случае некластеризованного индекса. Выполните следующий сценарий, чтобы создать некластеризованный индекс.
Будет следующим образом.
Записи таблицы сортируются по кластеризованному индексу, если он был создан. Этот новый некластеризованный индекс будет сортировать таблицу в соответствии с ее определением и будет храниться по отдельному физическому адресу. Приведенный выше сценарий будет создайте индекс в столбце «ИМЯ» таблицы сотрудников. Этот индекс будет отсортировать таблицу в порядке возрастания столбца «Имя». Данные таблицы и индекс будут храниться в разных местах, как мы говорили ранее. Теперь выполните следующее скрипт, чтобы просмотреть влияние нового некластеризованного индекса.
Результат будет следующим.
На рисунке выше видно, что столбец Name таблицы Employee показан в порядке возрастания столбца name, хотя мы не упомянули предложение «Порядок по ASC» с предложением select. Это связано с некластеризованным индексом в столбце «Имя», созданным в таблице сотрудников. Теперь, если запрос записывается в получить имя, адрес электронной почты, город и адрес конкретного человека. База данных сначала будет искать это конкретное имя внутри индекса, а затем извлекать соответствующие данные, что уменьшит время выборки запроса, особенно когда данные огромны.
Заключение
Из приведенного выше обсуждения мы узнали, что кластеризованный индекс может быть только одним, тогда как некластеризованный индекс может быть многие. Кластеризованный индекс быстрее по сравнению с некластеризованным индексом. Кластеризованный индекс не требует дополнительного места для хранения, тогда как некластеризованный индекс требует дополнительной памяти для их хранения. Если мы применяем ограничение первичного ключа к таблице, на ней автоматически создается кластерный индекс. Более того, если мы применяем ограничение уникального ключа к любому столбцу, для него автоматически создается некластеризованный индекс. Некластеризованный индекс работает быстрее, чем кластеризованный, для операций вставки и обновления. Таблица может не иметь некластеризованного индекса.
SQL Server Index Architecture
Hello everyone! In this article, we are going to lead in the index architecture of the SQL server and talk about index types, design, operations, and options, respectively.
An index is database objects that are defined on database tables and access data faster with fewer actions. A classic example of indexes is a phone book. In case the entries in the phone book are not indexed, that is to say, if each entry is kept random in the phone book, we will have to browse around the entire phone book for a name for which we search. But, if the entries in our phone book were indexed, we could see whether the name we were looking for was ahead of or behind the name in the middle of the phone book. In this way, we could reach the desired result in a few steps by adding the data we are looking for. The objects that allow for keeping the data in order, such as in this example, are called indexes.
Index Principle
In this section, we will touch upon how indexes work. But first, let’s look at how data is stored in the SQL server.
When we create a new database, we indicate the files where our database is. SQL Server splits these files into 8 KB blocks logically, not physically. These blocks are called pages. Hereby, the first 8 KB of the file becomes a page0, and the following 8 KB becomes a page1, and so on. Inside the pages, there are structures that are like the rows in tables and we call them rows. SQL Server makes another logical batching on the pages; a data structure of 64 KB formed by the aggregation of 8 consecutive pages is called extent. When an extent is fully loaded, the subsequent entry is made into a new extent at the size of the entry.
The number of rows on each page is not the same. Pages have rows varying according to data size, and a row can only be on one page. Indeed, SQL Server doesn’t read the rows and instead accesses the data by reading the pages.
When an index is designated to a table in SQL Server, it organizes the data in that table according to a tree structure as follows.
The top node of this tree structure is called the Root level. In fact, it starts from there and tries to find the entry by branching it to the right or left, as in the phone book example. There are Intermediate levels below the root level. While there should be one Root level, there can be several Intermediate levels depending on the amount of data in the table. At the bottom, there are Leaf Nodes, that’s to say, the structures that store the data. The search starts from the top and comes to the bottom level. The data stored in the leaf nodes will vary according to the index type.
As seen in the picture above, we can find the data we are searching for in three steps. But, if an index was not used, in other words, the data was not organized in the tree structure as above, the data could be accessed by browsing around all records. It is always required to go down to the leaf level to access the data.
Heap Table
There is no concept called heap table in SQL Server. Indeed, designating a table as a heap depends on whether an index has been defined on it. When adding data to a non-indexed table, the SQL Server does not store it on the disk as indexed, and the data is written to random data pages. Such tables are also called the heaps. In other words, we can say that tables without clustered indexes are heap tables. When data is searched on the heap table, the SQL Server accesses the table entries in sequence and matches them with the entry we are searching for. Even if an entry is found, it compares all entries to see if there is any other entry to match. This operation done by SQL Server is called Table Scan. This operation will take a very long time based on the number of entries in the table. It also has advantages over the tables defined with Clustered Index. These tables do not need extra index maintenance cost and extra space for the clustered index tree structure.
Clustered Table
Tables defined with clustered index are called clustered tables. If the query uses index-defined columns, the data is accessed very rapidly. Data pages are inter-connected for rapid access to the data. There is an extra index maintenance cost for INSERT, UPDATE, and DELETE operations compared to heap tables.
Index Types
In principle, the indexes in SQL Server are divided into two as clustered and non-clustered indexes. The data stored in the leaf nodes are designated as clustered while the pointer indicating, on which page the data is stored, is called a non-clustered index.
Clustered Index
As we mentioned above, clustered indexes sort the data in the table physically. Since a table is sorted physically, only one clustered index can be defined on the table. The column(s) to be selected for the clustered index should be the most used columns in the queries. As the data will be physically sorted according to these columns, it is accessed very rapidly. Furthermore, the selected column should be a field that has not been changed much. This is because the change of the column concerning the index means reorganizing the entire index, that’s to say, resorting to it physically. Indeed, the SQL server determines the need for the index itself. It makes its decision whether or not to use the indexes that we will define.
CREATE CLUSTERED INDEX IX_IndexName ON TableName (Column1);
Non-Clustered Index
Non-Clustered Index sorts the data logically, not physically. The leaf nodes of these indexes store the information of where the data is, not the data itself. A maximum of 999 non-clustered indexes can be defined in the table. Non-clustered indexes cannot access data directly. It can be accessed over the heap or a clustered index. This index should be created from the columns we frequently use in the condition section of our query.
CREATE NONCLUSTERED INDEX IX_IndexName ON TableName (Column1);
We have said that there can be a maximum of one clustered index and 999 non-clustered indexes. An index in SQL Server can contain a maximum of 16 columns, and its total size should not exceed 900 bytes. Moreover, any index cannot be defined on large-size fields, namely the columns having varchar(max), nvarchar(max), xml, text, and image types. We have always mentioned the advantages of the index, but its costs are very high. Each time you create an index, a space from your database is occupied. Indexes are required to be reorganized in insert, update, and delete operations, and this adversely affects the table performance. When an index is started to be created on a table, SQL Server locks the table and blocks off access. The index creation process may take a short or long time based on the amount of data in the table. Thus, we should think about it a lot and make decisions carefully in the selection of indexes.
Unique Index
It is used to ensure the deduplication of the data. It prevents duplication of the data and accelerates data retrieval according to the column we define. When we define a primary key or unique constraint for our table, a unique index is automatically defined. When we define this index to several columns, the deduplication occurs not over a single column, but over the columns where it is defined. A null value can be added to the column where it is defined, only once. Both clustered and non-clustered indexes can be defined as unique.
CREATE UNIQUE INDEX AK_IndexName ON TableName (Column1);
Filtered Index
In this index type, instead of defining an index for the entire table, an index is defined for the data that fulfills the specified requirement. It both increases performance and reduces the index maintenance costs. It occupies less space compared to a usual non-clustered index.
CREATE NONCLUSTERED INDEX IX_IndexName ON IndexName (Column1, Column2) WHERE …
Composite Index
If the index has been defined on the table over several columns, not over a single column, this type of index is called composite index. As we mentioned in the index constraints, a composite index is defined up to a maximum of 16 columns in a table, and it should not exceed 900 bytes. Both clustered and non-clustered indexes can be defined as composite. In this index definition, the order in which the columns are written is also very significant. To increase the index performance, the column whose diversity is higher should be written first. In other words, the column having more individual data than the data in the table is written first.
CREATE NONCLUSTERED INDEX IX_IndexName ON IndexName (Column1, Column2)
Covered Index
First, let’s explain why we need this index type. Normally, if the fields we want to access in a query are available in the index definition, we can access the data directly from the leaf nodes with this index. But when we want to retrieve a column or columns different from the index definition, first, the data meeting the index condition is retrieved, and the key value is determined. Then, the values of the columns that are not defined in the index are accessed over this key value. This process is called key lookup.
SQL Server makes an extra key lookup to access data, and this adversely affects I/O performance. You may ask why we don’t define the composite index. We cannot add as many columns as we want, as there is a limit of 16 columns and 900 bytes in the composite index. Even if there is no constraint, having too many columns in our index definition will increase the index size and give rise to performance loss since the index will be reorganized in operations such as updates and inserts.
We can use covered indexes to solve this problem.
CREATE NONCLUSTERED INDEX IX_IndexName ON TableName (Column1) INCLUDE (Column2, Column3);
With the INCLUDE option specified in the index definition, we can add the columns that stay out of the index and are available in our query. While SQL Server is accessing this data, it does not make an extra key lookup and accesses it directly. The INCLUDE option also removes some constraints. If you remember, we cannot add the fields storing big-size data such as varchar(max), nvarchar(max), xml, text, and image to the index definition. They can be added with the INCLUDE option. Moreover, the constraints of 16 column and 900-byte are thereby removed. The INCLUDE option can be only defined with non-clustered indexes.
We should be attentive to select as few columns as possible in our queries and avoid the statement select *. Before filling the include section of the indexes with columns, we should calculate the cost and define the index accordingly. Otherwise, the performance of our queries may decrease further.
Column Store Index
In the index types we have explained so far, indexes are kept on a row basis. In this index type, column-based indexing is done. Storage cost is low compared to other indexes. It is generally used in systems where data is written less but read more and in applications containing big data such as data warehouses where complex filtering and grouping operations are conducted. It compresses up to 15 times more than a non-clustered index and increases the performance up to 10 times in queries.
CREATE COLUMNSTORE INDEX IX_IndexName_ColumnStore ON TableName (Column1, Column2);
Full-text Index
In text-based searches in SQL Server, the performance of the like operator decreases, especially as the text size increases. Apart from that, the 900-byte limit constrains us to create an index, as we have mentioned in the index constraints. We can use this index type to make a fast search, especially in fields containing big data (varchar(max), nvarchar(max), xml, text). That is a service offered by SQL Server. I won’t go into more detail as it is the subject of an article in itself.
Index Design
Up to this section, we have touched upon how to define index types. In summary, these are as follows:
- We should choose as few columns as possible in tables with intensive data updating for the index definition.
- We can define more indexes in tables with less data updating.
- We should define the clustered index in as few columns as possible. Ideally, our clustered index should be defined in a unique column and not contain a null value.
- The more repetitive data we have in the column where we define the index, the lower our index performance will be.
- We should be attentive to the order of the columns in composite indexes.
- An index can be also defined on computed columns as long as they meet the requirements. That’s to say, the computed value must be deterministic.
- Columns should be chosen carefully in index definitions due to storage and sorting effects.
- The number of columns in the index definition will directly affect the performance of insert, update and delete operations.
Apart from these clauses, you can find more detailed information at this link.
Index Operations
Indexes do not always make a positive contribution to performance, and they may affect it negatively. When we notice that the indexes are not used, we may need to either delete or disable them so that they do not affect performance negatively.
- Making indexes passive; does not delete the index cookies from system catalogs but deletes the actual data that the index contains. Making a non-clustered index passive only prevents access to the index, but for clustered indexes, this prevents access to the table data unless it is dropped or rebuilt. We can use this option if we need to delete the index for a short time and then create it again.
ALTER INDEX INDEX IX_IndexName ON TableName DISABLE
- Deleting indexes; When clustered indexes are deleted, the data stored in leaf nodes starts to be stored in unsorted heap tables. Both cookie and index data are deleted from the disk. The clustered index defined as the primary key cannot be deleted. First, this constraint should be removed from the table.
DROP INDEX INDEX IX_IndexName ON TableName
Index Options
We use some options when creating indexes. These are as follows;
FILLFACTOR: It is used to adjust the density of the data pages in the leaf nodes where the data is stored. While the incoming new entries are written to the pages, the fill factor is checked, and if there is a space, it is written to the relevant place, but if there is no space, the page is divided into two and organized for the incoming new data. We adjust the fill factor of these pages with FILLFACTOR. The default value is zero and all data pages are filled. Keeping the Fillfactor high will increase the number of pages. When reading the data, we reach more data by reading fewer pages. Keeping this value low, in other words, leaving the pages blank, will raise our performance in the insert process. This value should be well calculated as both cases have positive and negative aspects.
PAD_INDEX: We have stated that the Fillfactor value is implemented in leaf nodes. If we want to apply this value to the nodes at the Intermediate level, we should also use the pad_index option together with the fill factor option.
SORT_IN_TEMPDB: Activating this option means that our index operations will be in the Tempdb system database, not in the database.
IGNORE_DUP_KEY: When we add the same entry to the unique index-defined table again, this reduces the level of the error message and provides a warning with this option. An entry is not added, but if the processing is in a transaction, the transaction is not terminated, other processings are continued.
DROP_EXISTING: It is used when a new index is desired to be created with the identical name to the index name to be created. It deletes the old index and creates a new one.
ONLINE: We have stated that the table was locked while creating the index. With this option, the table is not locked when the index is being created and the data is accessed.
MAXDOP: With this option, we can indicate how many processors on our server will be used for index creation. This value indicates the processor value which will be used most.
DATA_COMPRESSION: This option provides compression of the data of our index, especially when creating large-size indexes.
These are frequently used index options. I have not given a place to all options here. Those who are interested in these can check this link.
There are many more things to mention about indexes. I have tried to explain what I used from a developer’s point of view. I hope I can achieve to come in useful. I will always be glad to get your comments about the article. In any case, you can reach me on Linkedin and Twitter.
Индексы
В этой статье рассматриваются индексы и их роль в оптимизации времени выполнения запросов. В первой части статьи обсуждаются разные формы индексов и способы их хранения. Далее исследуются три основные инструкции языка Transact-SQL, применяемые для работы с индексами: CREATE INDEX, ALTER INDEX и DROP INDEX. Потом рассматривается фрагментация индексов ее влияния на производительность системы. После этого дается несколько общих рекомендаций по созданию индексов и описывается несколько специальных типов индексов.
Общие сведения
Системы баз данных обычно используют индексы для обеспечения быстрого доступа к реляционным данным. Индекс представляет собой отдельную физическую структуру данных, которая позволяет получать быстрый доступ к одной или нескольким строкам данных. Таким образом, правильная настройка индексов является ключевым аспектом улучшения производительности запросов.
Индекс базы данных во многом сходен с индексом (алфавитным указателем) книги. Когда нам нужно быстро найти какую-либо тему в книге, мы сначала смотрим в индексе, на каких страницах книги эта тема рассматривается, а потом сразу же открываем нужную страницу. Подобным образом, при поиске определенной строки таблицы компонент Database Engine обращается к индексу, чтобы узнать ее физическое местонахождение.
Но между индексом книги и индексом базы данных есть две существенные разницы:
Читатель книги имеет возможность самому решать, использовать ли индекс в каждом конкретном случае или нет. Пользователь базы данных такой возможности не имеет, и за него это решение принимает компонент системы, называемый оптимизатором запросов. (Пользователь может манипулировать использованием индексов посредством подсказок индексов, но эти подсказки рекомендуется применять только в ограниченном числе специальных случаев.)
Индекс для определенной книги создается вместе с книгой, после чего он больше не изменяется. Это означает, что индекс для определенной темы всегда будет указывать на один и тот же номер страницы. В противоположность, индекс базы данных может меняться при каждом изменении соответствующих данных.
Если для таблицы отсутствует подходящий индекс, для выборки строк система использует метод сканирования таблицы. Выражение сканирование таблицы означает, что система последовательно извлекает и исследует каждую строку таблицы (от первой до последней), и помещает строку в результирующий набор, если для нее удовлетворяется условие поиска в предложении WHERE. Таким образом, все строки извлекаются в соответствии с их физическим расположением в памяти. Этот метод менее эффективен, чем доступ с использованием индексов, как объясняется далее.
Индексы сохраняются в дополнительных структурах базы данных, называющихся страницами индексов. Для каждой индексируемой строки имеется элемент индекса (index entry), который сохраняется на странице индексов. Каждый элемент индекса состоит из ключа индекса и указателя. Вот поэтому элемент индекса значительно короче, чем строка таблицы, на которую он указывает. По этой причине количество элементов индекса на каждой странице индексов намного больше, чем количество строк в странице данных.
Это свойство индексов играет очень важную роль, поскольку количество операций ввода/вывода, требуемых для прохода по страницам индексов, значительно меньше, чем количество операций ввода/вывода, требуемых для прохода по соответствующим страницам данных. Иными словами, для сканирования таблицы, скорей всего, потребовалось бы намного больше операций ввода/вывода, чем для сканирования индекса этой таблицы.
Индексы компонента Database Engine создаются, используя структуру данных сбалансированного дерева B+. B+-дерево имеет древовидную структуру, в которой все самые нижние узлы находятся на расстоянии одинакового количества уровней от вершины (корневого узла) дерева. Это свойство поддерживается даже тогда, когда в индексированный столбец добавляются или удаляются данные.
На рисунке ниже показана структура B+-дерева для таблицы Employee и прямой доступ к строке в этой таблице со значением 25348 для столбца Id. (Предполагается, что таблица Employee проиндексирована по столбцу Id.) На этом рисунке можно также видеть, что B+-дерево состоит из корневого узла, узлов дерева и промежуточных узлов, количество которых может быть от нуля и больше:
Поиск в этом дереве значения 25348 можно выполнить следующим образом. Начиная с корня дерева, выполняется поиск наименьшего значения ключа, большего или равного требуемому значению. Таким образом, в корневом узле таким значением будет 29346, поэтому делается переход на промежуточный узел, связанный с этим значением. В этом узле заданным требованиям отвечает значение 28559, вследствие чего выполняется переход на узел дерева, связанный с этим значением. Этот узел и содержит искомое значение 25348. Определив требуемый индекс, мы можем извлечь его строку из таблицы данных с помощью соответствующих указателей. (Альтернативным эквивалентным подходом будет поиск меньшего или равного значения индекса.)
Индексированный поиск обычно является предпочтительным методом поиска в таблицах с большим количеством строк по причине его очевидного преимущества. Используя индексированный поиск, мы можем найти любую строку в таблице за очень короткое время, применив лишь несколько операций ввода/вывода. А последовательный поиск (т.е. сканирование таблицы от первой строки до последней) требует тем больше времени, чем дальше находится требуемая строка.
В следующих разделах мы рассмотрим два существующих типа индексов, кластеризованные и некластеризованные, а также научимся создавать индексы.
Кластеризованные индексы
определяет физический порядок данных в таблице. Компонент Database Engine позволяет создавать для таблицы лишь один кластеризованный индекс, т.к. строки таблицы нельзя упорядочить физически более чем одним способом. Поиск с использованием кластеризованного индекса выполняется от корневого узла B+-дерева по направлению к узлам дерева, которые связаны между собой в двунаправленный связанный список (doubly linked list), называющийся цепочкой страниц (page chain).
Важным свойством кластеризованного индекса является та особенность, что его узлы дерева содержат страницы данных. (Узлы кластеризованного индекса всех других уровней содержат страницы индекса.) Таблица, для которой определен кластеризованный индекс (явно или неявно), называется кластеризованной таблицей. Структура B+-дерева кластеризованного индекса показана на рисунке ниже:
Кластеризованный индекс создается по умолчанию для каждой таблицы, для которой с помощью ограничения первичного ключа определен первичный ключ. Кроме этого, каждый кластеризованный индекс однозначен по умолчанию, т.е. в столбце, для которого определен кластеризованный индекс, каждое значение данных может встречаться только один раз. Если кластеризованный индекс создается для столбца, содержащего повторяющиеся значения, система баз данных принудительно обеспечивает однозначность, добавляя четырехбайтовый идентификатор к строкам, содержащим дубликаты значений.
Кластеризованные индексы обеспечивают очень быстрый доступ к данным, когда запрос осуществляет поиск в диапазоне значений.
Некластеризованные индексы
Структура некластеризованного индекса точно такая же, как и кластеризованного, но с двумя важными отличиями:
некластеризованный индекс не изменяет физическое упорядочивание строк таблицы;
страницы узлов некластеризованного индекса состоят из ключей индекса и закладок.
Если для таблицы определить один или более некластеризованных индексов, физический порядок строк этой таблицы не будет изменен. Для каждого некластеризованного индекса компонент Database Engine создает дополнительную индексную структуру, которая сохраняется в индексных страницах. Структура B+-дерева некластеризованного индекса показана на рисунке ниже:
Закладка в некластеризованном индексе указывает, где находится строка, соответствующая ключу индекса. Составляющая закладки ключа индекса может быть двух видов, в зависимости от того, является ли таблица кластеризованной таблицей или кучей (heap). (Согласно терминологии SQL Server, кучей называется таблица без кластеризованного индекса.) Если существует кластеризованный индекс, то закладка некластеризованного индекса показывает B+-дерево кластеризованного индекса таблицы. Если таблица не имеет кластеризованного индекса, закладка идентична идентификатору строки (RID — Row Identifier), состоящего из трех частей: адреса файла, в котором хранится таблица, адреса физического блока (страницы), в котором хранится строка, и смещения строки в странице.
Как уже упоминалось ранее, поиск данных с использованием некластеризованного индекса можно осуществлять двумя разными способами, в зависимости от типа таблицы:
куча — прохождение при поиске по структуре некластеризованного индекса, после чего строка извлекается, используя идентификатор строки;
кластеризованная таблица — прохождение при поиске по структуре некластеризованного индекса, после чего следует прохождение по соответствующему кластеризованному индексу.
В обоих случаях количество операций ввода/вывода довольно велико, поэтому следует подходить к проектированию некластеризованного индекса с осторожностью, и применять его только в том случае, если есть уверенность, что его использование существенно повысит производительность.
Язык Transact-SQL и индексы
Теперь, когда мы познакомились с физической структурой индексов, в этом разделе рассмотрим, как их создавать, изменять и удалять, а также как получать информацию о фрагментации индексов и редактировать информацию об индексах. Все это подготовит нас к последующему обсуждению использования индексов для улучшения производительности системы.
Создание индексов
Индекс для таблицы создается с помощью инструкции CREATE INDEX. Эта инструкция имеет следующий синтаксис:
Параметр index_name задает имя создаваемого индекса. Индекс можно создать для одного или больше столбцов одной таблицы, обозначаемой параметром table_name. Столбец, для которого создается индекс, указывается параметром column1. Числовой суффикс этого параметра указывает на то, что индекс можно создать для нескольких столбцов таблицы. Компонент Database Engine также поддерживает создание индексов для представлений.
Можно проиндексировать любой столбец таблицы. Это означает, что столбцы, содержащие значения типа данных VARBINARY(max), BIGINT и SQL_VARIANT, также могут быть индексированы.
Индекс может быть простым или составным. Простой индекс создается по одному столбцу, а составной индекс — по нескольким столбцам. Для составного индекса существуют определенные ограничения, связанные с его размером и количеством столбцов. Индекс может иметь максимум 900 байтов и не более 16 столбцов.
Параметр UNIQUE указывает, что проиндексированный столбец может содержать только однозначные (т.е. неповторяющиеся) значения. В однозначном составном индексе однозначной должна быть комбинация значений всех столбцов каждой строки. Если ключевое слово UNIQUE не указывается, то повторяющиеся значения в проиндексированном столбце (столбцах) разрешаются.
Параметр CLUSTERED задает кластеризованный индекс, а параметр NONCLUSTERED (применяется по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Компонент Database Engine разрешает для таблицы максимум 249 некластеризованных индексов.
Возможности компонента Database Engine были расширены, позволяя создать поддержку индексов с убывающим порядком значений столбцов. Параметр ASC после имени столбца указывает, что индекс создается с возрастающим порядком значений столбца, а параметр DESC означает убывающий порядок значений столбца индекса. Таким образом, в использовании индекса предоставляется большая гибкость. С убывающим порядком следует создавать составные индексы на столбцах, значения которых упорядочены в противоположных направлениях.
Параметр INCLUDE позволяет указать неключевые столбцы, которые добавляются к страницам узлов некластеризованного индекса. Имена столбцов в списке INCLUDE не должны повторяться, и столбец нельзя использовать одновременно как ключевой и неключевой.
Чтобы по-настоящему понять полезность параметра INCLUDE, нужно понимать, что собой представляет покрывающий индекс (covering index). Если все столбцы запроса включены в индекс, то можно получить значительное повышение производительности, т.к. оптимизатор запросов может определить местонахождение всех значений столбцов по страницам индекса, не обращаясь к данным в таблице. Такая возможность называется покрывающим индексом или покрывающим запросом. Поэтому включение в страницы узлов некластеризованного индекса дополнительных неключевых столбцов позволит получить больше покрывающих запросов, при этом их производительность будет значительно повышена.
Параметр FILLFACTOR задает заполнение в процентах каждой страницы индекса во время его создания. Значение параметра FILLFACTOR можно установить в диапазоне от 1 до 100. При значении n=100 каждая страница индекса заполняется на 100%, т.е. существующая страница узла так же, как страница, не относящаяся к узлу, не будет иметь свободного места для вставки новых строк. Поэтому это значение рекомендуется применять только для статических таблиц. (Значение по умолчанию, n=0, означает, что страницы узлов индекса заполняются полностью, а каждая из промежуточных страниц содержит свободное место для одной записи.)
При значении параметра FILLFACTOR между 1 и 99 страницы узлов создаваемой структуры индекса будут содержать свободное место. Чем больше значение n, тем меньше свободного места в страницах узлов индекса. Например, при значении n=60 каждая страница узлов индекса будет иметь 40% свободного места для вставки строк индекса в дальнейшем. (Строки индекса вставляются посредством инструкции INSERT или UPDATE.) Таким образом, значение n=60 будет разумным для таблиц, данные которых подвергаются довольно частым изменениям. При значениях параметра FILLFACTOR между 1 и 99 промежуточные страницы индекса содержат свободное место для одной записи каждая.
После создания индекса в процессе его использования значение FILLFACTOR не поддерживается. Иными словами, оно только указывает объем зарезервированного места с имеющимися данными при задании процентного соотношения для свободного места. Для восстановления исходного значения параметра FILLFACTOR применяется инструкция ALTER INDEX.
Параметр PAD_INDEX тесно связан с параметром FILLFACTOR. Параметр FILLFACTOR в основном задает объем свободного пространства в процентах от общего объема страниц узлов индекса. А параметр PAD_INDEX указывает, что значение параметра FILLFACTOR применяется как к страницам индекса, так и к страницам данных в индексе.
Параметр DROP_EXISTING позволяет повысить производительность при воспроизведении кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Более подробную информацию смотрите далее в разделе «Пересоздание индекса».
Параметр SORT_IN_TEMPDB применяется для помещения в системную базу данных tempdb данных промежуточных операций сортировки, применяющихся при создании индекса. Это может повысить производительность, если база данных tempdb размещена на другом диске, чем данные.
Параметр IGNORE_DUP_KEY разрешает системе игнорировать попытку вставки повторяющихся значений в индексированные столбцы. Этот параметр следует применять только для того, чтобы избежать прекращения выполнения длительной транзакции, когда инструкция INSERT вставляет дубликат данных в индексированный столбец. Если этот параметр активирован, то при попытке инструкции INSERT вставить в таблицу строки, нарушающие однозначность индекса, система базы данных вместо аварийного завершения выполнения всей инструкции просто выдает предупреждение. При этом компонент Database Engine не вставляет строки с дубликатами значений ключа, а просто игнорирует их и добавляет правильные строки. Если же этот параметр не установлен, то выполнение всей инструкции будет аварийно завершено.
Когда параметр ALLOW_ROW_LOCKS активирован (имеет значение on), система применяет блокировку строк. Подобным образом, когда активирован параметр ALLOW_PAGE_LOCKS, система применяет блокировку страниц при параллельном доступе. Параметр STATISTICS_NORECOMPUTE определяет состояние автоматического перерасчета статистики указанного индекса.
Активированный параметр ONLINE позволяет создавать, пересоздавать и удалять индекс в диалоговом режиме. Данный параметр позволяет в процессе изменения индекса одновременно изменять данные основной таблицы или кластеризованного индекса и любых связанных индексов. Например, в процессе пересоздания кластеризованного индекса можно продолжать обновлять его данные и выполнять запросы по этим данным.
Параметр ON создает указанный индекс или на файловой группе по умолчанию (значение default), или на указанной файловой группе (значение file_group).
В примере ниже показано создание некластеризованного индекса для столбца Id таблицы Employee:
Создание однозначного составного индекса показано в примере ниже:
В этом примере значения в каждом столбце должны быть однозначными. При создании индекса заполняется 80% пространства каждой страницы узлов индекса.
Создание однозначного индекса для столбца невозможно, если этот столбец содержит повторяющиеся значения. Такой индекс можно создать лишь в том случае, если каждое значение (включая значение NULL) встречается в столбце только один раз. Кроме этого, любая попытка вставить или изменить существующее значение данных в столбец, включенный в существующий уникальный индекс, будет отвергнута системой в случае дублирования значения.
Получение информации о фрагментации индекса
В течение жизненного цикла индекса он может подвергнуться фрагментации, вследствие чего процесс хранения данных в страницах индекса станет неэффективным. Существует два типа фрагментации индекса: внутренняя фрагментация и внешняя фрагментация. Внутренняя фрагментация определяет объем данных, хранящихся в каждой странице, а внешняя фрагментация возникает при нарушении логического порядка страниц.
Для получения информации о внутренней фрагментации индекса применяется динамическое административное представление DMV, называемое sys.dm_db_index_physical_stats. Это DMV возвращает информацию об объеме и фрагментации данных и индексов указанной страницы. Для каждой страницы возвращается одна строка для каждого уровня B+-дерева. С помощью этого DMV можно получить информацию о степени фрагментации строк в страницах данных, на основе которой можно принять решение о необходимости реорганизации данных.
Использование представления sys.dm_db_index_physical_stats показано в примере ниже. (Прежде чем запускать пакет в примере на выполнение, необходимо удалить все существующие индексы таблицы Works_on. Для удаления индексов используется инструкция DROP INDEX, применение которой показано позже.)
Как видно из примера, представление sys.dm_db_index_physical_stats имеет пять параметров. Первые три параметра определяют идентификаторы текущей базы данных, таблицы и индекса соответственно. Четвертый параметр задает идентификатор раздела, а последний определяет уровень сканирования, применяемый для получения статистической информации. (Значение по умолчанию для определенного параметра можно указать посредством значения NULL.)
Наиболее важными из столбцов этого представления являются столбцы avg_fragmentation_in_percent и avg_page_space_used_in_percent. В первом указывается средний уровень фрагментации в процентах, а во втором определяется объем занятого пространства в процентах.
Редактирование информации индекса
После ознакомления с информацией о фрагментации индекса, как было рассмотрено в предыдущем разделе, эту и другую информацию индекса можно редактировать с помощью следующих системных средств:
представления каталога sys.indexes;
представления каталога sys.index_columns;
системной процедуры sp_helpindex;
функции свойств objectproperty;
среды управления Management Studio сервера SQL Server;
динамического административного представления DMV sys.dm_db_index_usage_stats;
динамического административного представления DMV sys.dm_db_missing_index_details.
Представление каталога sys.indexes содержит строку для каждого индекса и строку для каждой таблицы без кластеризованного индекса. Наиболее важными столбцами этого представления каталога являются столбцы object_id, name и index_id. Столбец object_id содержит имя объекта базы данных, которой принадлежит индекс, а столбцы name и index_id содержат имя и идентификатор этого индекса соответственно.
Представление каталога sys.index_columns содержит строку для каждого столбца, являющегося частью индекса или кучи. Эту информацию можно использовать совместно с информацией, полученной посредством представления каталога sys.indexes, для получения дополнительных сведений о свойствах указанного индекса.
Системная процедура sp_helpindex возвращает данные об индексах таблицы, а также статистическую информацию для столбцов. Эта процедура имеет следующий синтаксис:
Здесь переменная @db_object представляет имя таблицы.
Применительно к индексам, функция свойств objectproperty имеет два свойства: IsIndexed и IsIndexable. Первое свойство предоставляет сведения о наличии индекса у таблицы или представления, а второе указывает, поддается ли таблица или представление индексированию.
Для редактирования информации существующего индекса с помощью среды SQL Server Management Studio выберите требуемую базу данных в папке Databases, разверните узел Tables, в этом узле разверните требуемую таблицу и ее папку Indexes. В папке таблицы Indexes отобразится список всех существующих индексов для данной таблицы. Двойной щелчок мышью по индексу откроет диалоговое окно Index Properties со свойствами этого индекса. (Создать новый индекс или удалить существующий можно также с помощью среды Management Studio.)
Представление sys.dm_db_index_usage_stats возвращает подсчет разных типов операций с индексами и время последнего выполнения каждого типа операции. Каждая отдельная операция поиска, просмотра или обновления по указанному индексу при исполнении одного запроса считается использованием индекса и увеличивает на единицу значение соответствующего счетчика в этом DMV. Таким образом можно получить общую информацию о частоте использования индекса, чтобы на ее основе определить, какие индексы используются больше, а какие меньше.
Представление sys.dm_db_missing_index_details возвращает подробную информацию о столбцах таблицы, для которых отсутствуют индексы. Наиболее важными столбцами этого DMV являются столбцы index_handle и object_id. Значение в первом столбце определяет конкретный отсутствующий индекс, а во втором — таблицу, в которой отсутствует индекс.
Изменение индексов
Компонент Database Engine является одной из немногих систем баз данных, которые поддерживают инструкцию ALTER INDEX. Эту инструкцию можно использовать для выполнения операций по обслуживанию индекса. Синтаксис инструкции ALTER INDEX очень сходен с синтаксисом инструкции CREATE INDEX. Иными словами, эта инструкция позволяет изменять значения параметров ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY и STATISTICS_NORECOMPUTE, которые были описаны ранее при рассмотрении инструкции CREATE INDEX.
Кроме вышеперечисленных параметров, инструкция ALTER INDEX поддерживает три другие параметра:
параметр REBUILD, используемый для пересоздания индекса;
параметр REORGANIZE, используемый для реорганизации страниц узлов индекса;
параметр DISABLE, используемый для отключения индекса. Эти три параметра рассматриваются в следующих подразделах.
Пересоздание индекса
При любом изменении данных, используя инструкции INSERT, UPDATE или DELETE, возможна фрагментация данных. Если эти данные проиндексированы, то также возможна фрагментация индекса, когда информация индекса оказывается разбросанной по разным физическим страницам. В результате фрагментации данных индекса компонент Database Engine может быть вынужден выполнять дополнительные операции чтения данных, что понижает общую производительность системы. В таком случае требуется пересоздать (REBUILD) все фрагментированные индексы.
Это можно сделать двумя способами:
посредством параметра REBUILD инструкции ALTER INDEX;
посредством параметра DROP_EXISTING инструкции CREATE INDEX.
Параметр REBUILD применяется для пересоздания индексов. Если для этого параметра вместо имени индекса указать ALL, будут вновь созданы все индексы таблицы. (Разрешив динамическое пересоздание индексов, вам не нужно будет удалять и создавать их заново.)
Параметр DROP_EXISTING инструкции CREATE INDEX позволяет повысить производительность при пересоздании кластеризованного индекса таблицы, которая также имеет некластеризованные индексы. Он указывает, что существующий кластеризованный или некластеризованный индекс нужно удалить и создать заново указанный индекс. Как упоминалось ранее, каждый некластеризованный индекс в кластеризованной таблице содержит в своих узлах дерева соответствующие значения кластеризованного индекса таблицы. По этой причине при удалении кластеризованного индекса таблицы требуется создать вновь все ее некластеризованные индексы. Использование параметра DROP_EXISTING позволяет избежать повторного пересоздания некластеризованных индексов.
Параметр DROP_EXISTING более мощный, чем параметр REBUILD, поскольку он более гибкий и предоставляет несколько опций, таких как изменение столбцов, составляющих индекс, и изменение некластеризованного индекса в кластеризованный.
Реорганизация страниц узлов индекса
Параметр REORGANIZE инструкции ALTER INDEX задает реорганизацию страниц узлов указанного индекса, чтобы физический порядок страниц совпадал с их логическим порядком — слева направо. Это удаляет определенный объем фрагментации индекса, повышая его производительность.
Отключение индекса
Параметр DISABLE отключает указанный индекс. Отключенный индекс недоступен для применения, пока он не будет снова включен. Обратите внимание, что отключенный индекс не изменяется при внесении изменений в соответствующие данные. По этой причине, чтобы снова использовать отключенный индекс, его нужно полностью создать вновь. Для включения отключенного индекса применяется параметр REBUILD инструкции ALTER TABLE.
При отключенном кластеризованном индексе таблицы данные этой таблицы будут недоступны, так как все страницы данных таблицы с кластеризованным индексом хранятся в его узлах дерева.
Удаление и переименование индексов
Для удаления индексов в текущей базе данных применяется инструкция DROP INDEX. Обратите внимание, что удаление кластеризованного индекса таблицы может быть очень ресурсоемкой операцией, т.к. потребуется пересоздать все некластеризованные индексы. (Все некластеризованные индексы используют ключ индекса кластеризованного индекса, как указатель в своих страницах узлов.) Использование инструкции DROP INDEX для удаления индекса показано в примере ниже:
Инструкция DROP INDEX имеет дополнительный параметр MOVE TO, значение которого аналогично параметру ON инструкции CREATE INDEX. Иными словами, с помощью этого параметра можно указать, куда переместить строки данных, находящиеся в страницах узлов кластеризованного индекса. Данные перемещаются в новое место в виде кучи. Для нового места хранения данных можно указать или файловую группу по умолчанию, или именованную файловую группу.
Инструкцию DROP INDEX нельзя использовать для удаления индексов, которые создаются неявно системой для ограничений целостности, таких индексов, как PRIMARY KEY и UNIQUE. Чтобы удалить такие индексы, нужно удалить соответствующее ограничение.
Индексы можно переименовывать с помощью системной процедуры sp_rename.
Индексы можно также создавать, изменять и удалять в среде Management Studio с помощью диаграмм баз данных или обозревателя объектов. Но самым простым способом будет использовать папку Indexes требуемой таблицы. Управление индексами в среде Management Studio аналогично управлению таблицами в этой среде.
Рекомендации по созданию и использованию индексов
Хотя компонент Database Engine не накладывает никаких практических ограничений на количество индексов, по паре причин это количество следует ограничивать. Во-первых, каждый индекс занимает определенный объем дискового пространства, следовательно, существует вероятность того, что общее количество страниц индекса базы данных может превысить количество страниц данных в базе. Во-вторых, в отличие от получения выгоды при использовании индекса для выборки данных, вставка и удаление данных такой выгоды не предоставляют по причине необходимости обслуживания индекса. Чем больше индексов имеет таблица, тем больший требуется объем работы по их реорганизации. Общим правилом будет разумно выбирать индексы для частых запросов, а затем оценивать их использование.
Некоторые рекомендации по созданию и использованию индексов предоставляются в этом разделе. Последующие рекомендации являются всего лишь общими правилами. В конечном итоге их эффективность будет зависеть от способа использования базы данных на практике и типа наиболее часто выполняемых запросов. Индексирование столбца, который никогда не будет использоваться, не принесет никакой пользы.
Индексы и условия предложения WHERE
Если предложение WHERE инструкции SELECT содержит условие поиска с одним столбцом, то для этого столбца следует создать индекс. Это особенно рекомендуется при высокой селективности условия. Под селективностью (selectivity) условия имеется в виду соотношение количества строк, удовлетворяющих условию, к общему количеству строк в таблице. Высокой селективности соответствует меньшему значению этого соотношения. Обработка поиска с использованием индексированного столбца будет наиболее успешной при селективности условия, не превышающей 5%.
Столбец не следует индексировать при постоянном уровне селективности условия 80% или более. В таком случае для страниц индекса потребуются дополнительные операции ввода/вывода, которые уменьшат любую экономию времени, достигаемую за счет использования индексов. В этом случае быстрее выполнять поиск сканированием таблицы, что и будет обычно выбрано оптимизатором запросов, делая индекс бесполезным.
Если условие поиска часто используемого запроса содержит операторы AND, лучше всего будет создать составной индекс по всем столбцам таблицы, указанным в предложении WHERE инструкции SELECT. Создание такого составного индекса показано в примере ниже:
В этом примере происходит создание составного индекса по всем столбцам предложения WHERE. В этом запросе оператором AND соединены два условия, поэтому для обоих столбцов в этих условиях следует создать составной некластеризованный индекс.
Индексы и оператор соединения
В случае операции соединения рекомендуется создавать индекс для каждого соединяемого столбца. Соединяемые столбцы часто представляют первичный ключ одной из таблицы и соответствующий внешний ключ другой таблицы. Если указываются ограничения для обеспечения целостности PRIMARY KEY и FOREIGN KEY для соответствующих соединяемых столбцов, следует создать только некластеризованный индекс для столбца внешнего ключа, т.к. система неявно создаст кластеризованный индекс для столбца первичного ключа.
В примере ниже показано создание индексов, которые будут использованы, если у вас есть запрос с операцией соединения и дополнительным фильтром:
Для запроса в примере рекомендуется создать два отдельных индекса для столбца Id в таблице Employee, и столбца EmpId в таблице Works_on. Кроме этого, следует создать дополнительный индекс для столбца EnterDate.
Покрывающий индекс
Как уже упоминалось ранее, включение всех столбцов запроса в индекс может значительно повысить производительность запроса. Создание такого индекса, называемого покрывающим (covering), показано в примере ниже:
В этом примере в первую очередь из таблицы Address удаляется индекс IX_Address_StateProvinceID. Затем создается новый индекс, который помимо столбца PostalCode включает два дополнительных столбца. Наконец, инструкция SELECT в конце примера показывает запрос, покрываемый индексом. Для этого запроса системе нет необходимости выполнять поиск данных в страницах данных, поскольку оптимизатор запросов может найти все значения столбцов в страницах узлов некластеризованного индекса.
Покрывающие индексы рекомендуется применять по той причине, что страницы индексов обычно содержат намного больше записей, чем соответствующие страницы данных. Кроме этого, для того чтобы использовать этот метод, фильтруемые столбцы должны быть первыми ключевыми столбцами в индексе.
Индексы для вычисляемых столбцов
Компонент Database Engine позволяет создавать следующие специальные типы индексов:
индексы для вычисляемых столбцов;
индексы сохранения столбца;
В этом разделе рассматриваются вычисляемые столбцы и связанные с ними индексы.
называется столбец таблицы, в котором сохраняются результаты вычислений данных таблицы. Такой столбец может быть виртуальным или постоянным. Эти два типа столбцов рассмотрены в следующих далее подразделах.
Виртуальные вычисляемые столбцы
Вычисляемый столбец, который не имеет соответствующего кластеризованного индекса, является логическим, т.е. он физически на жестком диске не хранится. Таким образом, он вычисляется при каждом обращении к строке. Использование виртуальных вычисляемых столбцов показано в примере ниже:
Таблица Orders в этом примере имеет два виртуальных вычисляемых столбца: total и shippeddate. Столбец total вычисляется с использованием двух других столбцов, price и quantity, а столбец shippeddate вычисляется при использовании функции DATEADD и столбца orderdate.
Постоянные вычисляемые столбцы
Компонент Database Engine позволяет создавать индексы для детерминированных вычисляемых столбцов, где базовые столбцы имеют точные типы данных. (Вычисляемый столбец называется детерминированным, если всегда возвращаются одни и те же значения для одних и тех же данных таблицы.)
Индексированный вычисляемый столбец может быть создан только в том случае, если следующим параметрам инструкции SET присвоено значение ON (эти параметры обеспечивают детерминированность столбца):
Кроме этого, параметру NUMERIC_ROUNDABORT нужно присвоить значение off.
Если для вычисляемого столбца создать кластеризованный индекс, то значения столбца будут существовать физически в соответствующих строках таблицы, поскольку страницы узлов кластеризованного индекса содержат строки данных. В примере ниже показано создание кластеризованного индекса для вычисляемого столбца total из таблицы Orders:
После выполнения инструкции CREATE INDEX вычисляемый столбец total будет присутствовать в таблице физически. Это означает, что все обновления базовых столбцов вычисляемого столбца будут вызывать его обновление.
Столбец можно сделать постоянным и другим способом, используя параметр PERSISTED. Этот параметр позволяет задать физическое наличие вычисляемого столбца, даже не создавая соответствующего кластеризованного индекса. Эта возможность требуется для создания физических вычисляемых столбцов, которые создаются на столбцах с приблизительным типом данных (float или real). (Как упоминалось ранее, индекс для вычисляемого столбца можно создать только в том случае, если его базовые столбцы имеют точный тип данных.)