Как создать, использовать и удалить уникальные, составные и другие индексы SQL


Содержание материала:

Создаем индекс в таблице базы данных MS SQL Server

И ндексы — это ключ к повышению производительности вашей информационной системы , основанной на одной из реляционных СУБД. Широкоизвестна аналогия индексов с оглавлением книги , по которому читатель быстро находит нужную страницу. К сожалению , начинающие разработчики информационных систем иногда как-то недооценивают значение индексов при работе с базами данных. Поэтому , для такой категории пользователей имеет смысл рассказать хотя бы в двух словах о том, как работать с индексами в MS SQL Server.

Прежде чем переходить к чисто прикладному вопросу — Как создать индексы в MS SQL Server Express Edition, логично дать для начала кое-какие элементарные сведения.

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

Далее. Есть такое понятие — первичный ключ ( P rimary K ey , PK ), который однозначно идентифицирует запись в таблице. Лучшим кандидатом на первичный ключ, допустим, в справочных таблицах, является код (или идентификатор) справочника. У первичного ключа есть одна полезная особенность — для него индекс создаётся автоматически. По определению, первичный ключ может содержать только уникальные значения и не содержать значение NULL . Структуры поиска по полю, являющимся первичным ключом, работают более эффективно в силу перечисленных выше причин — уникальности и гарантированного индекса.

Приведём ещё несколько примеров информационных систем, в которых индексировать таблицы по уникальному ключу следует как отче наш. Предположим , вы создаёте базу данных клиентов и сделанных ими заказов. В такой системе у вас будут две основные ‘ оперативные ‘ таблицы — таблица Клиенты и таблица Заказы. При занесении нового клиента в базу , ваша система должна автоматически присваивать ему уникальный > Как правило , все современные СУБД имеют тип поля автоинкремент , созданного как раз для автоматической генерации уникального идентификатора записи. Так вот , для данного случая не подлежит сомнению тот факт , что необходимо индексировать таблицу Клиенты по полю >Или другой случай. Вы создаете информационную систему для страховки автомобилей — объектов страхового случая. Вполне естественной в такой базе данных будет присутствие оперативной таблицы застрахованных автомобилей. И в этом случае при занесении нового автомобиля , система должна присваивать ему уникальный > и выстраивать для него соотв. индекс .

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

В общем , я думаю , что логику вы уловили .

Ну вот, после небольшого теоретического вступления, можно и переходить к прикладным вопросам. Представим, что в базе данных MyShop есть таблица заказов Zakazy, содержащая информацию о сделанных потребителями (customers, классика жанра 🙂 заказах:

Наша учебная таблица Zakazy имеет сл. структуру:

ZakazID — уникальный идентификатор (код) заказа

Number — обозначение заказа

Data- дата заказа

Customer >код заказчика , оформившего заказ

Summa — цена вопроса :- ) — сумма заказа

Прим. О том , как создать таблицу в базе данных более подробно можно посмотреть в этой статье

В соответствии с изложенной выше теорией , первичным ключом таблицы Zakazy имеет смысл сделать поле Zakaz >Для этого в SQL Server Management Studio становимся на нужную таблицу и по правой кнопке выбираем в контекстном меню Создать столбец :

Попадаем в настройки структуры таблицы . Становимся на поле Zakaz >правая кнопка мыши и выбираем Задать первичный ключ :

После такой манипуляции структура таблицы примет следующий вид — обратите внимание на значок ключа в ZakazyID:

Первичный ключ готов!

О том, как создать внешний ключ (Foreign Key) можно посмотреть в этой статье

Теперь немного о том , какие поля (столбцы) таблицы следует индексировать — создавать для них индексы. Тут можно дать следующую рекомендацию — прежде всего индексировать следует те поля , которые участвуют в различных ‘ключевых’запросах. Причём начинать надо с запросов, что носят наиболее стабильный характер и реже всего изменяются по логике работы вашей информационной системы. Предположим, для нашего примера, что по логике программы нужно постоянно делать выборки из таблицы Zakazy по коду клиента (заказчика), ну скажем, по такому SQL-запросу:

Select * From Zakazy Where Customer >

В приведённом примере , однозначно надо индексировать нашу учебную таблицу по полю Customer >Делаем это сл. образом. В нужной таблице становимся на Индексы , и по правой кнопке выбираем Создать индекс :

. и попадаем в форму создания индекса :

На форме выше кликнем по кнопке Добавить и в сл. форме выбираем поле (столбец) таблицы для индексации — Customer >для нашего случая :

В форме задания опций индекса выбираем тип индекса (некластеризованный) и задаём имя индекса :

Жмём на форме выше OK, на ветке Индексы в контекстном меню Обновить и видим наш вновь созданный индекс CustID:

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

Еще несколько полезных советов для пользователей о работе с MS SQL Server можно посмотреть здесь

Индексы

В этой статье рассматриваются индексы и их роль в оптимизации времени выполнения запросов. В первой части статьи обсуждаются разные формы индексов и способы их хранения. Далее исследуются три основные инструкции языка 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 соединены два условия, поэтому для обоих столбцов в этих условиях следует создать составной некластеризованный индекс.

Топ-пост этого месяца:  FullCalendar. Окно добавления события

Индексы и оператор соединения

В случае операции соединения рекомендуется создавать индекс для каждого соединяемого столбца. Соединяемые столбцы часто представляют первичный ключ одной из таблицы и соответствующий внешний ключ другой таблицы. Если указываются ограничения для обеспечения целостности 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). (Как упоминалось ранее, индекс для вычисляемого столбца можно создать только в том случае, если его базовые столбцы имеют точный тип данных.)

Индексы в MySQL

Индексы в MySQL (Mysql indexes) — отличный инструмент для оптимизации SQL запросов. Чтобы понять, как они работают, посмотрим на работу с данными без них.

1. Чтение данных с диска

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

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

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

2. Поиск данных в MySQL

Таблицы MySQL — это обычные файлы. Выполним запрос такого вида:

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

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

Итак, есть две проблемы при чтении данных:

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

3. Сортировка данных

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

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

Индекс — это и есть отсортированный набор значений. В MySQL индексы всегда строятся для какой-то конкретной колонки. Например, мы могли бы построить индекс для колонки age из примера.

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

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

Рассмотрим запрос из примера:

Нам необходимо создать индекс на колонку age:

После этой операции MySQL начнет использовать индекс age для выполнения подобных запросов. Индекс будет использоваться и для выборок по диапазонам значений этой колонки:

Сортировка

Для запросов такого вида:

действует такое же правило — создаем индекс на колонку, по которой происходит сортировка:

Внутренности хранения индексов

Представим, что наша таблица выглядит так:

После создания индекса на колонку age, MySQL сохранит все ее значения в отсортированном виде:

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

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

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

На колонку email необходимо создать уникальный индекс:

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

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

MySQL может использовать только один индекс для запроса (кроме случаев, когда MySQL способен объединить результаты выборок по нескольким индексам). Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.

Рассмотрим такой запрос:

Нам следует создать составной индекс на обе колонки:

Устройство составного индекса

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

значения составного индекса будут такими:

Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

Поиск по диапазону

Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:

Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:

Сначала будут отфильтрованы все данные, которые подходят под условие age . Затем, поиск по значению «male» будет произведен без использования индекса.

Сортировка

Составные индексы также можно использовать, если выполняется сортировка:

В этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):

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

Колонок в индексе может быть больше, если требуется:

В этом случае следует создать такой индекс:

6. Использование EXPLAIN для анализа индексов

Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:

Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).

Как видим, в примере не используется ни один индекс. После создания индекса:

Прочитана всего одна запись, т.к. был использован индекс.

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

Explain также поможет определить правильность использования составного индекса. Проверим запрос из примера (с индексом на колонки age и gender):

Значение key_len показывает используемую длину индекса. В нашем случае 24 байта — длинна всего индекса (5 байт age + 19 байт gender).

Если мы выполним изменим точное сравнение на поиск по диапазону, увидим что MySQL использует только часть индекса:

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

В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.

7. Селективность индексов

Вернемся к запросу:

Для такого запроса необходимо создать составной индекс. Но как правильно выбрать последовательность колонок в индексе? Варианта два:

  • age, gender
  • gender, age

Подойдут оба. Но работать они будут с разной эффективностью.

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

Эта информация говорит нам вот о чем:

  1. Любое значение колонки age обычно содержит около 200 записей.
  2. Любое значение колонки gender — около 6000 записей.

Если колонка age будет идти первой в индексе, тогда MySQL после первой части индекса сократит количество записей до 200. Останется сделать выборку по ним. Если же колонка gender будет идти первой, то количество записей будет сокращено до 6000 после первой части индекса. Т.е. на порядок больше, чем в случае age.

Это значит, что индекс age_gender будет работать лучше, чем gender_age.

Селективность колонки определяется количеством записей в таблице с одинаковыми значениями. Когда записей с одинаковым значением мало — селективность высокая. Такие колонки необходимо использовать первыми в составных индексах.

8. Первичные ключи

Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:

При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.

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

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

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

Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.

Overhead

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

Создавайте только необходимые индексы, чтобы не расходовать зря ресурсы сервера. Контролируйте размеры индексов для Ваших таблиц:

Когда создавать индексы?

  • Индексы следует создавать по мере обнаружения медленных запросов. В этом поможет slow log в MySQL. Запросы, которые выполняются более 1 секунды являются первыми кандидатами на оптимизацию.
  • Начинайте создание индексов с самых частых запросов. Запрос, выполняющийся секунду, но 1000 раз в день наносит больше ущерба, чем 10-секундный запрос, который выполняется несколько раз в день.
  • Не создавайте индексы на таблицах, число записей в которых меньше нескольких тысяч. Для таких размеров выигрыш от использования индекса будет почти незаметен.
  • Не создавайте индексы заранее, например, в среде разработки. Индексы должны устанавливаться исключительно под форму и тип нагрузки работающей системы.
  • Удаляйте неиспользуемые индексы.

Самое важное

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

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

Ускорение репликации в Mysql 5.6+

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

Анализ медленных запросов с помощью EXPLAIN

3 примера установки индексов в JOIN запросах


Check-unused-keys для определения неиспользуемых индексов в базе данных

Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL

Сравнение Vertica и Mysql на практике

Правила выбора типов данных для максимальной производительности в Mysql

Повышение скорости работы запросов с MySQL Handlersocket

Как восстановить данные, если MySQL упал и не поднимается

Правильный поиск по тексту в Mysql (full-text search)

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

Синтаксис и оптимизация Mysql LIMIT

Оптимизация постраничного вывода данных

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

Быстрая альтернатива Mysqldump для больших таблиц без блокировок и выключений.

Анализ работы СУБД при помощи pgFouine

Сравнение двух движков и когда стоит использовать каждый из них

Эффективная замена ORDER BY RAND()

Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере

Создание и использование Real-Time индексов в Sphinx’e

Настройки для улучшения производительности Postgres

Устройство колоночных баз данных

Как устроена распределенная база данных на основе blockchain механизма

Основы индексов в Microsoft SQL Server

В данном материале будут рассмотрены такие объекты базы данных Microsoft SQL Server как индексы, Вы узнаете, что такое индексы, какие типы индексов бывают, как их создавать, оптимизировать и удалять.

Что такое индексы в базе данных?

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

Типы индексов в Microsoft SQL Server

В Microsoft SQL Server существуют следующие типы индексов:

  • Кластеризованный (Clustered) – это индекс, который хранит данные таблицы в отсортированном, по значению ключа индекса, виде. У таблицы может быть только один кластеризованный индекс, так как данные могут быть отсортированы только в одном порядке. По возможности каждая таблица должна иметь кластеризованный индекс, если у таблицы нет кластеризованного индекса, такая таблица называется «кучей». Кластеризованный индекс создается автоматически при создании ограничений PRIMARY KEY (первичный ключ) и UNIQUE, если до этого кластеризованный индекс для таблицы еще не был определен. В случае создания кластеризованного индекса для таблицы (кучи), в которой есть некластеризованные индексы, то после создания все их необходимо перестроить.
  • Некластеризованный (Nonclustered) – это индекс, который содержит значение ключа и указатель на строку данных, содержащую значение этого ключа. У таблицы может быть несколько некластеризованных индексов. Создаваться некластеризованные индексы могут как на таблицах с кластеризованным индексом, так и без него. Именно этот тип индекса используется для повышения производительности часто используемых запросов, так как некластеризованные индексы обеспечивают быстрый поиск и доступ к данным по значениям ключа;
  • Фильтруемый (Filtered) – это оптимизированный некластеризованный индекс, который использует предикат фильтра для индексирования части строк в таблице. Если хорошо спроектировать такой тип индекса, то он может повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами;
  • Уникальный (Unique) – это индекс, который обеспечивает отсутствие повторяющихся (одинаковых) значений ключа индекса, гарантируя тем самым уникальность строк по данному ключу. Уникальными могут быть как кластеризованные, так и некластеризованные индексы. Если создавать уникальный индекс по нескольким столбцам, индекс гарантирует уникальность каждой комбинации значений в ключе. При создании ограничений PRIMARY KEY или UNIQUE SQL сервер автоматически создает уникальный индекс для ключевых столбцов. Уникальный индекс может быть создан только в том случае, если у таблицы на текущий момент отсутствуют дублирующие значения по ключевым столбцам;
  • Колоночный (Columnstore) – это индекс, основанный на технологии хранения данных в виде столбцов. Данный тип индекса эффективно использовать для больших хранилищ данных, поскольку он может увеличить производительность запросов к хранилищу до 10 раз и также до 10 раз уменьшить размер данных, так как данные в Columnstore индексе сжимаются. Существуют как кластеризованные колоночные индексы, так и некластеризованные;
  • Полнотекстовый (Full-text) – это специальный тип индекса, который обеспечивает эффективную поддержку сложных операций поиска слов в символьных строковых данных. Процесс создания и обслуживания полнотекстового индекса называется «заполнением». Существует такие типы заполнения как: полное заполнение и заполнение на основе отслеживания изменений. По умолчанию SQL сервер полностью заполняет новый полнотекстовый индекс сразу после его создания, но на это может потребоваться значительный объем ресурсов, в зависимости от размеров таблицы, поэтому есть возможность откладывать полное заполнение. Заполнение на основе отслеживания изменений используется для обслуживания полнотекстового индекса после его первоначального полного заполнения;
  • Пространственный (Spatial) – это индекс, который обеспечивает возможность более эффективного использования конкретных операций на пространственных объектах в столбцах с типом данных geometry или geography. Данный тип индекса может быть создан только для пространственного столбца, также таблица, для которой определяется пространственный индекс, должна содержать первичный ключ (PRIMARY KEY);
  • XML – это еще один специальный тип индекса, который предназначен для столбцов с типом данных XML. Благодаря XML-индексу повышается эффективность обработки запросов к XML столбцам. Существует два вида XML-индекса: первичные и вторичные. Первичный XML-индекс индексирует все теги, значения и пути, хранимые в XML столбце. Он может быть создан, только если у таблицы есть кластеризованный индекс по первичному ключу. Вторичный XML-индекс может быть создан, только если у таблицы есть первичный XML-индекс и используется он для повышения производительности запросов по определенному типу обращения к XML-столбцу, в связи с этим существует несколько типов вторичных индексов: PATH, VALUE и PROPERTY;
  • Также существуют специальные индексы для таблиц, оптимизированных для памяти (In-Memory OLTP) такие как: Хэш (Hash) индексы и некластеризованные индексы, оптимизированные для памяти, которые создаются для сканирования диапазона и упорядоченного сканирования.

Создание и удаление индексов в Microsoft SQL Server

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

Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2020 Express.

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

Для создания индексов в Microsoft SQL Server существует два способа: первый – это с помощью графического интерфейса среды SQL Server Management Studio (SSMS), и второй – это с помощью языка Transact-SQL, мы с Вами разберем оба способа.

Исходные данные для примеров

Давайте представим, что у нас есть таблица с товарами под названием TestTable, в которой есть три столбца:

    Product >Пример создания кластеризованного индекса

Как я уже говорил, кластеризованный индекс создается автоматически, если мы, например, при создании таблицы указываем конкретный столбец в качестве первичного ключа (PRIMARY KEY), но так как мы этого не сделали, давайте рассмотрим пример самостоятельного создания кластеризованного индекса.

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

Для примера давайте просто создадим кластеризованный индекс, без создания первичного ключа. Сначала сделаем это с помощью Management Studio.

Открываем SSMS и в обозревателе объектов находим нужную таблицу и щелкаем правой кнопкой мыши по пункту «Индексы», выбираем «Создать индекс» и тип индекса, в нашем случае «Кластеризованный».

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

После ввода всех необходимых параметров жмем «ОК», в итоге будет создан кластеризованный индекс.

Точно также можно было бы создать кластеризованный индекс, используя инструкцию T-SQL CREATRE INDEX, например, вот так

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

Пример создания некластеризованного индекса с включенными столбцами

Сейчас давайте рассмотрим пример создания некластеризованного индекса, при этом мы укажем столбцы, которые не будет являться ключевыми, но будут включаться в индекс. Это полезно в тех случаях, когда Вы создаете индекс для конкретного запроса, например, для того чтобы индекс полностью покрывал запрос, т.е. содержал все столбцы (это называется «Покрытием запроса»). Благодаря покрытию запроса повышается производительность, так как оптимизатор запросов может найти все значения столбцов в индексе, при этом не обращаясь к данным таблиц, что приводит к меньшему числу дисковых операций ввода-вывода. Но помните, что включение в индекс неключевых столбцов влечет за собой увеличение размера индекса, т.е. для хранения индекса потребуется больше места на диске, а также может повлечь и снижение производительности операций INSERT, UPDATE, DELETE и MERGE на базовой таблице.

Для того чтобы создать некластеризованный индекс с помощью графического интерфейса Management Studio, мы также находим нужную таблицу и пункт индексы, только в данном случае мы выбираем «Создать -> Некластеризованный индекс».

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

Далее переходим на вкладку «Включено столбцы» и с помощью кнопки «Добавить» добавляем столбцы, которые мы хотим включить в индекс, в нашем случае, например, ProductName.

На Transact-SQL это будет выглядеть следующим образом.

Пример удаления индекса в Microsoft SQL Server

Для того чтобы удалить индекс можно щелкнуть правой кнопкой по нужному индексу и нажать «Удалить», затем подтвердить свое действия нажав «ОК».

или также можно использовать инструкцию DROP INDEX, например

Следует отметить, что инструкция DROP INDEX неприменима к индексам, которые были созданы путем создания ограничений PRIMARY KEY и UNIQUE. В данном случае для удаления индекса нужно использовать инструкцию ALTER TABLE с предложением DROP CONSTRAINT.

Оптимизация индексов в Microsoft SQL Server

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

В каких случаях использовать реорганизацию индекса, а в каких перестроение?

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

В данном случае нас интересует столбец avg_fragmentation_in_percent, т.е. процентная доля логической фрагментации.

Так вот, Microsoft рекомендует:

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

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

Реорганизация индексов

Реорганизация индекса – это процесс дефрагментации индекса, который дефрагментирует конечный уровень кластеризованных и некластеризованных индексов по таблицам и представлениям, физически переупорядочивая страницы концевого уровня в соответствии с логическим порядком (слева направо) конечных узлов.

Для реорганизации индекса можно использовать как графический инструмент SSMS, так и инструкцию Transact-SQL.

Реорганизация индекса с помощью Management Studio

Реорганизация индекса с помощью Transact-SQL

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

Перестроение индекса – это процесс, при котором происходит удаление старого индекса и создание нового, в результате чего фрагментация устраняется.

Для перестроения индексов можно использовать два способа.

Первый. Используя инструкцию ALTER INDEX с предложением REBUILD. Эта инструкция заменяет инструкцию DBCC DBREINDEX. Обычно для массового перестроения индексов используется именно этот способ.

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

В Management Studio функционал для перестроения также доступен. Правой кнопкой по нужному индексу «Перестроить».

На этом материал по основам индексов в Microsoft SQL Server закончен, если Вас интересует язык T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL», удачи!

Индексирование таблиц MySQL: создание индекса MySQL, удалить индекс и другие операции

От правильно составленной структуры базы данных очень сильно зависит скорость работы всего проекта. Еще одним инструментом, позволяющим значительно сократить время отклика базы, являются индексы БД MySQL. Перед тем, как рассматривать различные операции с ними, стоит определиться, что же такое индексы СУБД и какие преимущества можно получить при их использовании.

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

Индексы – это специфические объекты базы данных, позволяющие значительно повысить скорость поиска значений из таблиц базы данных. MySQL index представляет из себя структуру, в которой хранятся значения одного (в некоторых случаях — нескольких) столбца таблицы и ссылок на строки, где эти значения расположены. Так как для хранения индексов чаще всего используются бинарные деревья, поиск среди них занимает чрезвычайно мало места.

Принцип работы индексов очень прост. Для примера рассмотрим запрос:
SELECT Name FROM Persons WHERE Points ON ( [(length)]. )
где:

[UNIQUE | FULLTEXT] – определяет, будет ли индекс содержать только уникальные значения (UNIQUE), или в нем будут присутствовать и повторяющиеся значения (FULLTEXT). По умолчанию используется режим FULLTEXT. Length – определяет длину символов поля для индексирования. Если Length оставить пустым, то в индекс попадет поле целиком вне зависимости от длины.

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

В версиях MySQL младше 3.22 эта команда не активна, а в более поздних – в плане создания индексов работает аналогично команде ALTER TABLE. При работе с ALTER TABLE добавление записей происходит при помощи команды ADD INDEX, MySQL при помощи этой команды позволяет создавать индексы PRIMARY KEY (создать индекс такого типа при помощи CREATE INDEX нельзя).

Удаление индекса в MySQL

В MySQL удалить индекс можно при помощи такого оператора:
DROP INDEX ON

Индексирование таблиц MySQL – обширная тема, а умение правильно работать с MySQL index – целое искусство. Администраторы из RigWEB все знают особенности индексирования MySQL на хостинге, VPS и выделенных серверах и умеют применять свои знания на практике, поэтому если у Вас остались вопросы — обращайтесь к нам!

Что такое индекс mysql и как их использовать

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

3 ответа 3

Если в кратце, то индекс, это поле по которому оптимизирован(ускорен) поиск.

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

Допустим есть таблица.

Допустим вам нужен поиск по имени (firstname).

тогда есть смысл добавить индекс по данному полю.

Будет созданна «карта» которая позволет легко находить записи в оригинальном списке.

Для одной небольшой таблицы приемущество не будет очевидно join несколько (3-4 уже достаточно) таблиц по неиндексированным полям. Убивает сервер на раз!

Вкратце, индексы создаются для повышения производительности поиска данных. Таблицы могут иметь огромное количество строк, которые хранятся в произвольном порядке. Без индекса поиск нужных строк идёт по порядку (последовательно), что на больших объемах данных отнимает много времени.

Индекс — обычно один или несколько столбцов таблицы и указателей на соответствующие строки таблицы, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск — например, в MySQL b-дерева. Индекс лучше использовать на тех столбцах таблицы, на которые вы чаще всего будете накладывать условия через where column_name = .

Индекс создаётся по правилу:

Например, у вас таблица называется test , где хранятся данные по городам России с улицами вида Город, Улица, Дом. Понятно, что строк в таблице при таком раскладе будет много. Если вы часто делаете выборку по определенному городу, например:

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

Тогда тот же самый запрос

отработает гораздо быстрее, если столбец city будет проиндексирован.

На пальцах можно объяснить так:

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

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

Это связано с тем, что когда Вы ищите какую-то запись, то просматриваются все записи, пока не дойдут до нужной.

Когда Вам это окончательно надоедает и Вы хотите что-нибудь сделать, то к Вам на помощь приходят индексы.

Индекс создаётся по какому-то определённому полю (можно по нескольким) по которому, обычно, выполняется поиск. Когда Вы создаёте индекс, то MySql (и любая другая БД) обходит все записи в таблице и строит дерево (скорее всего B-дерево или разновидность), в котором ключами выступает выбранное поле, а содержимым ссылки на записи в таблице.

И когда Вы делаете очередной свой select запрос по таблице, по полю для которого создали индекс MySql (и любая другая БД) знает что у неё есть индекс, по которому пройтись будет быстрее, нежели перебирать все записи и Ваш запрос будет направлен этому индексу и записи, удовлетворяющие условию, будут найдены гораздо быстрее, так как поиск по построенному дереву будет гораздо быстрее, нежели простой перебор всех записей.

Топ-пост этого месяца:  Tweetmeme retweet button кнопка retweet для блога

Индексы

У индексов есть две задачи: соблюдать выполнение первичных ключей и уникальных ограничений, и увеличивать производительность. Стратегия по созданию индексов сильно влияет на производительность приложения. Нет четкого ограничения кто ответствене за создание индексов. Когда бизнес-аналитики составляют бизнес-требования к системе которые будут выполнены как создание ограничений – они влияют на индексы. Администратор будет наблюдать за выполнением запросов и давать рекомендации по созданию индексов. Разработчик именно тот кто лучше всех понимает что происходит в коде и природе данных – тоже влияет на стратегию создания индексов.

Почему индексы необходимы

Индексы это часть механизма ограничений (constraint). Если столбец (или группа столбцов) помечены как первичной ключ таблица, то каждый раз когда вставляется строка в таблицу, Oracle необходимо проверить что не существует строки с такими значениями. Если у таблицы нет индекса дял столбцов – единственный способ проверить это это вычитать всю таблицу. Это может быть приемлимо если в таблице всего несколько строк, но дял таблиц, содержащих тысячи миллионов (или миллиардов) строк это займёт очень много времени и неприемлимо. Индекс позволяет практически мгновенно получить доступ к значениям ключа и проверка на существование происходит моментально. Когда определяется первичный ключ Oracle создаст индекс для столбца(ов) ключа если ещё не существует такого индекса.

Ограничение по уникальности (unique constraint) тоже требует создание индекса. Это ограничение отличается от первичного ключа тем что значение в столбцах ограничения по уникальности могут быть NULL в отличие от первичного ключа, но это не влияет на создание и исопльзование индекса. Внешний ключ (foreign key) соблюдается с помощью индексов, но обязательным является индекс только на родительской таблице. Внешний ключ дочерней таблицы зависит от столбца первичного ключа или уникального ключа родительской таблицы. Когда строка добавляется в дочернюю таблицу, Oracle будет использовать индекс родительской таблицы для проверки существует ли такое значение в родительной таблице или нет, перед тем как позволить записать данные. Как бы то ни было желательно всегда создавать индексы для столбцов дочерней таблицы используемых как внешние ключи из соображений производительности: DELETE для родительской таблицы будет гораздо б ыстрее если Oracle сможет использовать индекс для проверки существуют ли ещё строки в дочерней таблице с этим значением или нет.

Индексы критически важны для производительности. Когда выполняется команда SELECT с директивой WHERE, Oracle необходимо определить строки в таблице которые необходимо выбрать. Если не создано индексов для столбцов используемых в директиве WHERE, то единственным способом сделать это – это вычитать всю таблицу (full table scan).Full table scan проверяют все строки по очереди для поиска нужных значений. Если в таблицы хранятся миллиарды строк, это может занять несколько часов. Если существует индекс для использованного в WHERE столбца, Oracle может искать используя индекс. Индекс это отсортированный список ключей значений структурирвоанных таким образом чтобы операция поиска была очень быстрой. Каждая запись это сслыка на строку в таблице. Поиск строк используя индекс гораздо быстрее чем чтение всей таблицы если размер таблицы больше определённого размера и пропорция между данными которые нужны для запроса и всеми данными в таблице ниже определённого значения. Для маленьких таблиц, или где секция WHERE всё равно выберет большую часть строк из таблицы, полное чтение таблицы будет быстрее: вы можете (обычно) доверять Oracle при выборе решения использовать ли индекс. Это решение осуществляется на основании статистической информации собираемой о таблице и строках в ней.

Второй случай когда индексы могут увеличить производительность это сортировка. Команда SELECT c директивой ORDER BY, GROUP BY или ключевым словом UNION (и несколько других) обязана отсортировать строки в определённом порядке – если не создан индекс, который может вернуть строки без необходимости в сортировке (строки уже отсортированы).

И третий случай это объекдинение таблиц, но опять же у Oracle есть выбор: в зависимости от размера таблиц и наличия свободной памяти, может быть быстрее вычитать таблицы в память и объединять их чем использовать индексы. Метод nested loop join читает строки одной таблицы и использует индекс другой таблицы для поиска совпадений (это обычно нагружает диск). Hash join считывает таблицу в память, преобразует в хеш таблицу и использует специальный алгоритм для поиска совпадений — такая операция требует больше оперативной памяти и процессорного времени. Sort merge join сортиует таблицы по значениям столбца для объединения и затем объединяет их вместе – это компромисс между использованием диска, памятии процессора. Если нет индексов –Oracle сильно ограничен в способах объединения.

Indexes assist SELECT statements, and also any UPDATE, DELETE, or MERGE statements that use a WHERE clause—but they will slow down INSERT statements.

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

Oracle поддерживает несколько типов индексов с различными вариациями. Два типа, которые мы рассмотрим это B* Tree индекс, который является типом по умолчанию и bitmap индекс. Основное правило – индексы увеличивают производительность для чтения данных но замедляют при DML операциях. Это происходит потому что индексы нужно обновлять и поддерживать. Каждый раз когда строка записывается в таблицу, новый ключ должен быть вставлен в каждый индекс таблицы, что усиливает нагрузку на БД. Поэтому OLTP системы обычно используют минимальное количество индексов (возможно только необходимые для ограничений) а для OLAP систем создаётся столько индексов сколько нужно для быстроты выполнения.

B* Tree индексы (B*=balanced)

Индекс это древовидная (tree) структура. «Корень» (root) дерева содержит указатели на множество узлов второго уровня, которые в свою очередь могут хранить указатели на узлы третьего уровня и так далее. Глубина дерева определяется длинной ключа и количеством строк в таблице.

The B*Tree structure is very efficient. If the depth is greater than three or four, then either the index keys are very long or the table has billions of rows. If neither if these is the case, then the index is in need of a rebuild.

В листьях (узлы нижнего уровня) индекса хранятся значения столбца строк по порядку и указатель на строку. Также листья хранят ссылки на соседние листья. Таким образом чтобы выбрать строку если условие WHERE использует строгое равенство — Oracle исдёт по дереву в лист содержащий искомое значение и затем использует указатель для считывания строки.Если же используется нестрогое равенство (например LIKE, BETWEEN и т.д.) то вначале находится первая строка удовлетворяющая условию а затем считываются строки по порядку и переход между листьями осуществляется напрямую, без нового обхода по дереву.

Указатель на строку – это rowid. Rowid — это псевдостолбец закрытого формата, который имеет каждая строка в каждой таблице. Внутри значения зашифрован указатель на физический адрес строки. Так как rowid не является частью стандарта SQL то он не видим при написании обычных запросов. Но вы можете выбирать эти значения и использовать их при необходимости. Это отображено на рисунке 7-3.

Rowid для каждой строки полностью уникальный. Каждая строка во всей БД имеет свой уникальный rowid. Расшифровав rowid получаем физический адрес строки, и Oracle может рассчитать в каком файле и где внутри файла находится искомая строка.

B* Tree индексы очень эффективны для вычитки строк число которых невелико относительно всех строк таблицы и таблица достаточно большая. Рассмотрим запрос

select count(*) from employees where last_name between ‘A%’ and ‘Z%’;

При использовании такого условия в WHERE запрос вернёт все строки таблицы. Использование индекса при таком запросе будет значительно медленее чем чтение всей таблицы. И вообще – вся таблица это то что нужно в этом запросе. Другим примером будет настолько маленькая таблица где одна операция чтения считывает её полностью; тогда нет смысла считывать вначале индекс. Обычно говорят что запросы, результат которых предполагает вычитку более чем 2-4% данных в таблице обычно работают быстрее используя полное чтение таблицы. Особым случаем является значение NULL в столбце указанном в секции WHERE. Значение NULL не хранится в B* Tree индексах и запросы типа

select * from employees where last_name is null;

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

Мощность (кратность – количество уникальных значений) столбца велика и

Количество строк в таблице большое и

Столбец используется в директивах WHERE и операциях объединения

Bitmap индексы

Во многих приложения природа данных и запросы таковы что использование B* Tree индексов не сильно помогает. Расммотрим пример. Есть таблица продаж, в которой набор данных о продажах в супермаркетах за год, которые нужно проанализировать в нескольких измерениях. На рисунке 7-4 показана простая диаграмма сущность-связь для четырёх измерений.

Мощность каждого измерения очень низкая. Преположим

Всего два измерения (DATE и PRODUCT) предполагают селективность лучше чем упомянутые 2-4%, т.е. делают использование индексов оправданным. Но если запросы используют предикаты группы (к примеру месяц в году, или группа товаров в которую входит десять товаров) то и эти измерения не подходят к требованиям. Отсюда следует простой факт: B* Tree индексы часто бесполезны в хранилищах данных. Типичным запросов может быть сравнение продаж между двумя магазинами приходящим покупателям определённой группы товаров за месяц. Можно создать B* Tree индесы для этих столбцов но Oracle проигнорирует их так как они недостаточно селективны. Для таких ситуация созданы bitmap индексы. Bitmap индексы хранят все rowid строк как битовую маску для каждого уникального значения ключа. Битовые маски индекса для измерения CHANNEL может быть к примеру

Это значит что первые две строки были приходящими покупателями, затем покупка с доставкой и т.д

Битовые маски индекса столбца SHOP могут быть

Это значит что первые две продажи были в Лондоне, затем одна в Оксфорде, затем четвертая в Рединге и так далее.

Теперь если приходит запрос

select count(*) from sqles where channel=’WALK-IN’ and shop=’OXFORD’

Oracle может выбрать две битовые маски и объединить их с помощью операции И

Результат логического И показывает что только седьмая и шестнадцатая строки удовлетворяют запросу. Операции над битовыми масками очень быстрые и могут использоваться для сложных булевых операций надо многими столбцами со многими сочетаниями И, ИЛИ или НЕ. Также достоинством bitmap индексов является то, что они хранят значения NULL. С точки зрения битовой маски – NULL просто ещё одно уникальное значение со своей битовой маской.

В общем, bitmap индексы полезны когда

Мощность столбца низкая и

Количество строк в таблице большое и

Столбец используется в операциях булевой алгебры

If you knew in advance what the queries would be, then you could build B*Tree indexes that would work, such as a composite index on SHOP and CHANNEL. But usually you don’t know, which is where the dynamic merging of bitmaps gives great flexibility.

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

Всего доступно шесть свойств которые можно применить при создании индекса

  • Уникальность / Unique или nonunique
  • Реверсивность / Reverse key
  • Сжатие / Compessed
  • Составной или нет /Composite
  • Основанный на функции или нет / Function based
  • Сортировка по возрастанию или убыванию / Ascending или descending

Все шесть свойств можно применить к B* Tree индексам и только три последних можно использовать для bitmap индексов.

Уникальный индекс не позволит дублировать значение. По умолчанию значение nonunique. Свойство уникальности индекса не связано с ограниченями уникальности или первичного ключа: если существует уникальный индекс то вствка дубликатов невозможно даже при отстуствии ограничения уникальности.

Реверсивный индекс строится на значениях ключа в которых байты строятся в обратном порядке: вместо индексирования значения к примеру ‘John’ будет использоваться значение ‘nhoJ’. Когда выполнится команда SELECT, Oracle автоматически преобразует строку поиска. Это используется для распределения строк по индексу в мультипользовательских системах. Например если много пользователей добавляют много строк в таблицу с первичным ключом как последовательно-увеличивающийся номер – все строки будут стремиться к концу индекса. Путем реверса ключа строки распределяются по всему индексу. При использовании индекса с реверсированным ключом базы данных не сохраняет ключи индекса друг за другом в лексикографическом порядке. Таким образом, когда в запросе присутствует предикат неравенства, ответ получается медленнее, поскольку база данных вынуждена выполнять полное сканирование таблицы. При индексе с реверсированным ключом база данных не может запустить запрос по диапазону ключа индекса.

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

Составной индекс – это индекс который строится для нескольких столбцов. Нет ограничений на использование столбцов разных типов данных. Если условие WHERE не использует все столбцы, то индекс всё ещё может быть использован, но если не используется самый левый столбец, то Oracle использует skip-scanning метод который гораздо менее эффективный чем если бы левый столбец был включен.

Основанный на функции индекс строится для результата выполнения функции к одному или нескольким столбцам, к примеру upper(last_name или to_char(startdate,’ccyy-mm-dd’). Запросы должны использовать ту же функцию для поиска или Oracle не сможет использовать индекс.

По умолчанию индексы отсортированы по возрастанию (ascending), т.е. значения ключа хранятся от меньшего к большему. Режим по убыванию (descending) меняет это на противоположное. Фактически эта разница не очень важна: записи в индексе хранятся как двойной связный список т.е. можно переходить вверх или вниз с одинаковой скоростью, однако это повлияет на порядок строк в результате.

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

Индексы создаются неявно при создании ограничений первичного ключа или уникальности если индексы на соответствующих столбцах ещё не существуют. Синтаксис для явного создания индекса

CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname

ON [schema.]tablename (column [, column…] ) ;

По умолчанию индекс не уникальный, без сжатия, не-реверсивный типа B* Tree. Невозможно создать уникальный битмап индекс (и не стоит этого поделать если вы подумаете об этом с точки зрения свойства селективности). Индексы это объекты схемы и возможно создать индекс в одной схеме и таблицу в другой, но большинство людей найдут такой способ странным. Составной индекс – это индекс для нескольких столбцов. Составные индексы могут быть созданы для столбцов разных типов и столбцы не обязательно следовать друг за другом.

Many database administrators do not consider it good practice to rely on implicit index creation. If the indexes are created explicitly, the creator has full control over the characteristics of the index, which can make it easier for theDBA to manage subsequently.

Рассмотрим пример создания таблиц, индексов и затем определение ограничений

create table dept(deptno number,dname varchar2(10));

create table emp(empno number, surname varchar2(10),

forename varchar2(10), dob date, deptno number);

create unique index dept_i1 on dept(deptno);

create unique index emp_i1 on emp(empno);

create index emp_i2 on emp(surname,forename);

create bitmap index emp_i3 on emp(deptno);

alter table dept add constraint dept_pk primary key (deptno);

alter table emp add constraint emp_pk primary key (empno);

alter table emp add constraint emp_fk

foreign key (deptno) references dept(deptno);

Первые два индекса помечены как UNIQUE, что значит нельзя добавить дубликат. Это не определяет ограничение, но на самом деле это не что иное. Третий индекс не UNIQUE и позволяет хранить дубликаты и это составной индекс для двух столбцов. Четвертый индекс – это bitmap индекс, так как ожидается что мощность столбца будет низкой.

Когда определяются два ограничения, Oracle определит уже существующие индексы и использует их для ограничений. Обратите внимание что индекс для DEPT.DEPTNO не даст выигрыш с точки зрения происзводительности, но он всё равно необходим для обеспечения ограничения первичного ключа.

После создания индексы работают абсолютно невидимо и автоматически. Перед выполнением SQL запроса, сервер Oracle оценит возможные пути выполнения. Некоторые способы будут использовать индексы, некоторые нет. Далее Oracle использует информацию которую он собирает автоматически о таблица и окружении для принятия решения какой способ предпочтителен.

The Oracle server should make the best decision about index use, but if it is getting it wrong, it is possible for a programmer to embed instructions, known as optimizer hints, in code that will force the use (or not) of certain indexes

Изменение и удаление индексов

Команда ALTER INDEX не может менять свойства индексов интересных с точки зрения программиста: тип, столбцы и всё иное. ALTER INDEX создана для администратора БД и обычно будет использоваться для управления физическими свойствами индекса. Если необходимо изменить логические свойства – то единственным способом будет удаление старого индекса и создание нового. К примеру чтобы изменить индекс EMP_I2 можно выполнить следующие команды

drop index emp_i2;

create index emp_i2 on emp(surname,forename,dob);

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

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

Индекс создается командой create index и непосредственно недоступен пользователю. Индексы используются оптимизатором запросов для доступа к данным в базовых таблицах и представлениях.

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

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

Именно в силу упорядоченности данных индекс предоставляет более эффективный доступ по сравнению со сканированием таблицы к требуемым данным.

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

Индексы, как правило, имеют структуру B-Tree — древовидная иерарархическая структура — которая позволяет, наряду со сканированием индекса (index scan), использовать прямой доступ к данным — поиск по индексу (index seek). Эта структура используется как для кластеризованных, так и некластеризованных индексов. Различием между ними, повторю, является то, что на листовом уровне дерева у кластеризованного индекса находятся сами табличные данные, а у некластеризованного — указатели на данные в таблице.

Если сказанное выше вам не вполне понятно, могу порекомендовать хорошую статью Гейла Шоу (Gail Shaw. Introduction to Indexes).

Кластерный индекс создается автоматически на первичном ключе (который и является ключом индекса), если вы не укажите обратного.

Возьмем для примера таблицу utV (база данных «Окраска»), содержащую всего три столбца — v_id (идентификатор баллончика — первичный ключ), v_name (название баллончика) и v_color (цвет краски в баллончике). Как уже говорилось, на первичном ключе автоматически создается кластеризованный индекс, есть он и у нашей таблицы.

Рис.1 Кластеризованный индекс

Выполним три следующих запроса и посмотрим на планы их выполнения.

select v_id from utv;
select * from utv;
select v_name from utv;

Рис.2 Сканирование кластеризованного индекса

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

Давайте теперь заменим кластеризованный индекс некластеризованным, удалив сначала кластеризованный первичный ключ, и создав затем некластеризованный. Предварительно нам потребуется удалить внешний ключ из таблицы utB, который ссылается на первичный ключ таблицы utV:

alter table utB
drop constraint FK_utB_utV; —удаляем внешний ключ
alter table utV
drop constraint PK_utV; — удаляем первичный ключ (кластеризованный индекс)
go
alter table utV
add constraint PK_utVn primary key nonclustered (v_id asc);

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

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

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

Рассмотрим теперь запросы на получение конкретной строки:

select v_ > select v_name from utv where v_ >

Для выполнения первого запроса оптимизатором теперь выбирается поиск по индексу (index seek) – наиболее эффективная операция, поскольку это прямой доступ к данным с использованием структуры B-Tree. План для второго запроса помимо поиска по индексу содержит еще две операции. Это связано с тем, что мы в запросе хотим получить имя баллончика, а не его ИД, а в индексе содержится только v_ >

Рис. 4 Поиск по индексу

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

alter table utV
drop constraint PK_utVn; — удаляем индекс
/* создаем уникальный индекс (не первичный ключ) с включенным столбцом */
create unique nonclustered index IX_utVi on utV(v_id asc) include(v_name);

Посмотрим план выполнения второго запроса.

Рис. 5 Поиск по индексу с включенным столбцом

Как видим, теперь план не отличается от плана выполнения первого запроса.

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

Рассмотрим, наконец, самый плохой вариант – отсутствие индексов.

drop index IX_utVi on utV; — удаляем индекс
go
select v_ > select v_name from utv where v_ >

Рис.6 Сканирование таблицы при отсутствии индексов

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

Для сравнения планов выполнения давайте вернем индекс по столбцу v_id

alter table utV
add constraint PK_utVn primary key nonclustered (v_id asc);
и выполним следующие запросы:
select v_ > select v_ ;

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

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

Давайте так и поступим, и выполним предыдущие запросы.

create index IX_utVname on utV(v_name);

Рис. 8 Игнорирование неуникального индекса

Неожиданно? Мы ожидали, что будет использован поиск по индексу, а затем поиск закладки для нахождения значения v_id. Однако оптимизатор не использовал индекс на столбце v_name. Почему?

Причина, как я думаю, заключается в том, что индекс на столбце v_name не является уникальным. Т.е. оптимизатор полагает, что значений, отвечающих предикату v_name= ‘Balloon # 15’ может быть несколько. Тогда для каждого такого значения потребуется поиск закладки. Поскольку данных в таблице немного, оптимизатор решает не оценивать план с использованием индекса на основе имеющейся статистики о распределении значений в столбце v_name, а пойти по простому пути, сэкономив на оценке плана. Давайте проверим это предположение, создав уникальный индекс, полагая, что одинаковых названий нет и быть не должно.

drop index IX_utVname on utV;
create unique index IX_utVname on utV(v_name);

Рис.9 Использование уникального индекса

Теперь результат согласуется с нашими ожиданиями.

Как создать, использовать и удалить уникальные, составные и другие индексы SQL

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse

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

Простые примеры:

Основные сценарии:

  • В SQL Server 2020 и базы данных SQL Azure используйте некластеризованный индекс в индексе для повышения производительности запросов хранилища данных. В разделе индексы Columnstore — хранилище данных

Необходимо создать другой тип индекса?

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

Компонент Компонент Database Engine не позволяет создать уникальный индекс по столбцам, уже содержащим повторяющиеся значения, даже если параметру IGNORE_DUP_KEY присвоено значение ON. При попытке создания такого индекса компонент Компонент Database Engine выдает сообщение об ошибке. Прежде чем создавать уникальный индекс по такому столбцу или столбцам, необходимо удалить все повторяющиеся значения. Столбцы, используемые в уникальном индексе, должны иметь свойство NOT NULL, т. к. при создании индекса значения NULL рассматриваются как повторяющиеся.

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

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

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

Если аргумент CLUSTERED не указан, создается некластеризованный индекс.

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

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

НЕКЛАСТЕРИЗОВАННЫЙ
Создание индекса, задающего логическое упорядочение для таблицы. Логический порядок строк в некластеризованном индексе не влияет на их физический порядок.

Каждая таблица может содержать до 999 некластеризованных индексов независимо от способа их создания: неявно с помощью ограничений PRIMARY KEY и UNIQUE или явно с помощью инструкции CREATE INDEX.

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

По умолчанию, используется значение NONCLUSTERED.

index_name
Имя индекса. Имена индексов должны быть уникальными в пределах таблицы или представления, но необязательно должны быть уникальными в пределах базы данных. Имена индексов должны соответствовать правилам идентификаторы.

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

До 32 столбцов могут быть объединены в один составной ключ индекса. Все столбцы ключа составного индекса должны находиться в одной таблице или одном и том же представлении. Максимальный общий размер значений составного индекса равен 900 байт для кластеризованного индекса и 1700 для некластеризованного индекса. Ограничены 16 столбцами и 900 байт для версий до База данных SQL версии 12 и SQL Server 2020.

Столбцы, имеющие типы данных больших объектов (LOB) ntext, текст, varchar(max), nvarchar(max), varbinary(max), xml, или изображение нельзя указывать в качестве ключевых столбцов индекса. Кроме того, определение представления не может содержать ntext, текст, или изображение столбцы, даже если они не указаны в инструкции CREATE INDEX.

Можно создавать индексы на столбцах с определяемым пользователем типом данных CLR, если этот тип поддерживает двоичное упорядочение. Можно также создавать индексы на вычисляемых столбцах, определенных как вызовы методов для столбцов с определяемыми пользователем типами данных, если эти методы помечены как детерминированные и не выполняют операции доступа к данным. Дополнительные сведения об индексировании столбцов определяемого пользователем типа данных CLR см. в разделе CLR определяемые пользователем типы.

[ ASC | DESC]
Определяет сортировку значений заданного столбца индекса: по возрастанию или по убыванию. Значение по умолчанию — ASC.

INCLUDE (column [ ,. n ] )
Указывает неключевые столбцы, добавляемые на конечный уровень некластеризованного индекса. Некластеризованный индекс может быть уникальным или неуникальным.


Имена столбцов в списке INCLUDE не могут повторяться и не могут использоваться одновременно как ключевые и неключевые. Некластеризованные индексы всегда содержат столбцы кластеризованного индекса, если для таблицы определен кластеризованный индекс. Дополнительные сведения см. в статье Create Indexes with Included Columns.

Допускаются данные всех типов, за исключением text, ntextи image. Индекс должен создаваться или перестраиваться в автономном режиме (ONLINE = OFF) Если любой из заданных неключевых столбцов имеет varchar(max), nvarchar(max), или varbinary(max) типов данных.

Вычисляемые столбцы, являющиеся детерминированными и точными или неточными, могут быть включенными столбцами. Вычисляемые столбцы, производные от изображение, ntext, текст, varchar(max), nvarchar(max), varbinary(max), и xml типов данных может быть включено в неключевых столбцов, при условии, что тип данных вычисляемого столбца является допустимым в качестве включенного столбца. Дополнительные сведения см. в разделе Indexes on Computed Columns.

Сведения о создании XML-индекса см. в разделе CREATE XML INDEX (Transact-SQL).

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

Предикат фильтра использует простую логику сравнения и не может ссылаться на вычисляемый столбец, столбец определяемого пользователем типа, столбец типа пространственных данных или столбец типа hierarchyID. Сравнения с помощью литералов NULL с операторами сравнения недопустимы. Вместо этого используются операторы IS NULL и IS NOT NULL.

Далее приведено несколько примеров использования предикатов фильтра для таблицы Production.BillOfMaterials :

WHERE StartDate > ‘20000101’ AND EndDate

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN (‘20000404’, ‘20000905’) AND EndDate IS NOT NULL

Отфильтрованные индексы не применяются к XML-индексам и полнотекстовым индексам. Для индексов UNIQUE только выбранные строки должны иметь уникальные значения индексов. Отфильтрованные индексы не поддерживают параметр IGNORE_DUP_KEY.

Д partition_scheme_name(column_name)

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Задает схему секционирования, которая определяет файловые группы соответствующие секциям секционированного индекса. Схема секционирования должна существовать в базе данных путем выполнения инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. column_name указывает столбец, по которому будет секционирован секционированного индекса. Этот столбец должен соответствовать типу данных, длине и точности аргумента секции функции, partition_scheme_name используется. column_name не ограничивается столбцы в определении индекса. Любой столбец базовой таблицы можно указать, за исключением случая секционирования индекса UNIQUE, когда column_name должен быть выбран из используемых в уникальном ключе. Это ограничение дает возможность компоненту Компонент Database Engine проверять уникальность значений ключа только в одной секции.

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

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

Примечание

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

Дополнительные сведения о секционировании индексов секционированных таблиц и индексов.

Примечание
Область применения: начиная с SQL Server 2008 до SQL Server 2020.

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

ON «default«

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Создает заданный индекс в файловой группе, используемой по умолчанию.

Слово «default» в этом контексте не является ключевым. Идентификатор файловой группы по умолчанию она должен иметь разделители, как в выражениях ON »по умолчанию» или ON [по умолчанию]. Если указано значение «default» (по умолчанию), параметр QUOTED_IDENTIFIER должен иметь значение ON для текущего сеанса. Это параметр по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).

[FILESTREAM_ON < filestream_filegroup_name | partition_scheme_name | «NULL»>]

Область применения: начиная с SQL Server 2008 до SQL Server 2020.

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

filestream_filegroup_name имя файловой группы FILESTREAM. В файловой группе должен быть определен один файл для файловой группы с помощью CREATE DATABASE или ALTER DATABASE инструкции; в противном случае произойдет ошибка.

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

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

Предложение FILESTREAM_ON NULL может быть указано в инструкции CREATE INDEX, если создается кластеризованный индекс и таблица не содержит столбец FILESTREAM.

Дополнительные сведения см. в разделе FILESTREAM (SQL Server).

Полное или неполное имя индексируемого объекта.

имябазыданных
Имя базы данных.

schema_name
Имя схемы, которой принадлежит таблица или представление.

представления table_or_view_name
Имя индексируемой таблицы или представления.

Чтобы создать индекса для представления, это представление оно должно быть определено с параметром SCHEMABINDING. Прежде чем создавать любой некластеризованный индекс для представления, необходимо создать уникальный кластеризованный индекс. Дополнительные сведения об индексированных представлениях см. в разделе «Примечания».

Начиная с версии SQL Server 2020, объект может быть таблица, которая сохраняется в кластеризованный индекс.

База данных SQL Azureподдерживает формат трехкомпонентного имени имябазыданных. [schema_name]. object_name при имябазыданных — текущая база данных или имябазыданных база данных tempdb и object_name начинается с #.

Указывает параметры, которые должны использоваться при создании индекса.

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Определяет разреженность индекса. Значение по умолчанию — OFF.

ON
Процент свободного места, определяемый fillfactor применяется к страницам индекса промежуточного уровня.

ОТКЛЮЧЕНИЕ или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью, при этом остается достаточно места по крайней мере для одной строки максимального размера, возможного в этом индексе при заданном наборе ключей на промежуточных страницах.

Параметр PAD_INDEX имеет смысл только в случае, если указан параметр FILLFACTOR, так как использует процентное значение, указанное в нем. Если процент, заданный аргументом FILLFACTOR, недостаточно велик для размещения одной строки, компонент Компонент Database Engine внутренне переопределит это значение, чтобы обеспечить минимум. Число строк на странице индекса промежуточного уровня никогда не будет меньше двух, независимо от того, насколько мало значение fillfactor.

Для обратной совместимости синтаксиса аргумент WITH PAD_INDEX эквивалентен аргументу WITH PAD_INDEX = ON.

Аргумент FILLFACTOR ** = ** fillfactor

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Определяет величину в процентах, показывающую, насколько компонент Компонент Database Engine должен заполнять конечный уровень каждой страницы индекса во время его создания или перестроения. Аргумент FILLFACTOR должно быть целым числом от 1 до 100. Если fillfactor равен 100, Компонент Database Engine создает индексы с заполненными страницами конечного.

Аргумент FILLFACTOR действует только при создании или перестройке индекса. Компонент Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Для просмотра коэффициента заполнения, используйте sys.indexes представления каталога.

Создание кластеризованного индекса с аргументом FILLFACTOR меньше 100 влияет на объем пространства хранения, занимаемого данными, т. к. компонент Компонент Database Engine перераспределяет данные, когда создает кластеризованный индекс.

Важно
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, следует ли хранить временные результаты сортировки в tempdb. Значение по умолчанию — OFF.

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

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

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

Для обратной совместимости синтаксиса аргумент WITH SORT_IN_TEMPDB эквивалентен аргументу WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = OFF >
Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Параметр не оказывает влияния при выполнении CREATE INDEX, ALTER INDEX, или обновление. Значение по умолчанию — OFF.

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

OFF
Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Будет выполнен откат всей операции INSERT.

IGNORE_DUP_KEY нельзя установить в значение ON для индексов, создаваемых для представлений, неуникальных индексов, XML-индексов, пространственных индексов и фильтруемых индексов.

Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

Для обратной совместимости синтаксиса аргумент WITH IGNORE_DUP_KEY эквивалентен аргументу WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = OFF>
Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

ON
Устаревшие статистики не пересчитываются автоматически.

OFF
Автоматическое обновление статистических данных включено.

Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE.

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

Для обратной совместимости синтаксиса аргумент WITH STATISTICS_NORECOMPUTE эквивалентен аргументу WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = OFF >
Когда ON, являются статистики создаются как статистики отдельно по секциям. Когда OFF, дерево статистик удаляется и SQL Server повторно вычисляет статистики. Значение по умолчанию — OFF.

Если статистики по секциям не поддерживаются, параметр пропускается и выводится предупреждение. Добавочные статистики не поддерживаются для следующих типов статистических данных.

Статистики, созданные с индексами, не выровненными по секциям для базовой таблицы.

Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.

Статистики, созданные в базах данных, доступных только для чтения.

Статистики, созданные по фильтрованным индексам.

Статистика, созданная по представлениям.

Статистики, созданные по внутренним таблицам.

Статистики, созданные с пространственными индексами или XML-индексами.

ПРЕДЛОЖЕНИЕ DROP_EXISTING = OFF >
Есть возможность удалить перестроения существующего кластеризованного или некластеризованного индекса со спецификациями изменяемого столбца и именем для индекса. Значение по умолчанию — OFF.

ON
Указывает, удалите и заново создайте существующий индекс, который должен иметь имя, совпадающее с именем параметра index_name.

OFF
Указано, удалите и заново создайте существующий индекс. SQL Server выводится сообщение об ошибке, если индекс с указанным именем уже существует.

С помощью инструкции DROP_EXISTING можно изменить.

  • Rowstore некластеризованный индекс в кластеризованный rowstore.

Предложение DROP_EXISTING нельзя изменить:

Rowstore кластеризованный индекс в некластеризованный rowstore.

Кластеризованный индекс для любого типа индекса rowstore.

Для обратной совместимости синтаксиса аргумент WITH DROP_EXISTING эквивалентен аргументу WITH DROP_EXISTING = ON.

ONLINE = OFF >
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

Важно

Операции с индексами в сети доступны не во всех выпусках MicrosoftSQL Server. Список функций, поддерживаемых различными выпусками SQL Server, в разделе выпусков и компонентов поддерживается для SQL Server 2020.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это включает запросы или обновления применительно к обрабатываемой базовой таблице и индексам. В начале операции совмещаемая блокировка (S) удерживается на исходном объекте в течение очень короткого времени. В конце операции на источнике на короткое время удерживается совмещаемая блокировка (S), если создается некластеризованный индекс. Если в режиме в сети создается или удаляется кластеризованный индекс и, если перестраивается кластеризованный или некластеризованный индекс, удерживается блокировка SCH-M (изменения схемы). При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

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

Индекс локальной временной таблицы.

Исходные уникальные кластеризованные индексы представлений.

Отключенные кластеризованные индексы.

Кластеризованные индексы, если базовая таблица содержит типы данных LOB: изображение, ntext, тексти Пространственные типы.

varchar(max) и varbinary(max) столбцы не могут быть частью индекса. В SQL Server (начиная с версии SQL Server 2012) и в База данных SQL, если таблица содержит varchar(max) или varbinary(max) , кластеризованный индекс, содержащий другие столбцы, можно построить или перестроить с использованием ONLINE параметр. База данных SQLне разрешает ONLINE вариант, если базовая таблица содержит varchar(max) или varbinary(max) столбцов.

Дополнительные сведения см. в статье Perform Index Operations Online.

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

ON
Блокировки строк допустимы при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки строки.

OFF
Блокировки строк не используются.

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

ON
Блокировки страниц возможны при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки страниц.

OFF
Блокировки страниц не используются.

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Переопределяет Настройка max degree of parallelism параметр конфигурации сервера параметр конфигурации в течение операции с индексами. MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.

max_degree_of_parallelism может быть:

1
Подавляет формирование параллельных планов.

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

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

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

DATA_COMPRESSION
Задает режим сжатия данных для указанного индекса, номера секции или диапазона секций. Существуют следующие параметры выбора.

NONE
Индекс или заданные секции не сжимаются.

ROW
Для индекса или заданных секций производится сжатие строк.

PAGE
Для индекса или заданных секций производится сжатие страниц.

Дополнительные сведения о сжатии см. в разделе сжатие данных.

В РАЗДЕЛАХ ( <

| <>> > [ ,. n ] )

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если индекс не секционирован, аргумент ON PARTITIONS создаст ошибку. Если не указано предложение ON PARTITIONS, то параметр DATA_COMPRESSION применяется ко всем секциям секционированного индекса.

можно указать одним из следующих способов.

Указать номер секции, например ON PARTITIONS (2).

Указать номера нескольких секций через запятые, например ON PARTITIONS (1, 5).

Указать диапазоны и отдельные секции, например: ON PARTITIONS (2, 4, 6 TO 8).

<>>можно указать как номера секций, разделенными ключевым словом, например: ON PARTITIONS (6-8).

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

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

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

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

Индексы поддерживают расширенные свойства.

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

Начиная с версии SQL Server 2020 и База данных SQL Azure, можно создать некластеризованный индекс в таблице хранится как кластеризованный индекс. Если сначала создать некластеризованный индекс в таблице хранятся как кучу или кластеризованный индекс, индекс будет сохраняться после преобразования таблицы в кластеризованный индекс. Это также не нужно удалить некластеризованный индекс, если перестроить кластеризованный индекс.

Ограничения и ограничения:

  • Параметр FILESTREAM_ON не является действительным, если создается некластеризованный индекс в таблице хранится как кластеризованный индекс.

Если существует уникальный индекс, то каждый раз при добавлении данных с помощью операции вставки компонент Компонент Database Engine делает проверку на появление повторяющихся значений. Производится откат операций вставки, которые могли бы создать повторяющиеся значения ключей, и компонент Компонент Database Engine выдает сообщение об ошибке. Это происходит даже в случае, если операция вставки изменяет несколько строк, а повторяющееся значение может появиться всего одно. Если делается попытка ввести данные, для которых существует уникальный индекс, и предложение IGNORE_DUP_KEY имеет значение ON, сбоем завершаются операции только с теми строками, где нарушается свойство уникальности индекса.

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

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

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

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

Индексированные представления могут создаваться для секционированных таблиц таким же образом, как и индексы для таблиц. Дополнительные сведения о секционированных индексах см. в разделе Partitioned Tables and Indexes.

Статистические данные в SQL Server 2020 не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

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

Обязательные параметры SET для отфильтрованных индексов

Параметры SET в столбце Required Value необходимы при возникновении любого из следующих условий.

Создание отфильтрованного индекса.

Операция INSERT, UPDATE, DELETE или MERGE изменяет данные в отфильтрованном индексе.

Отфильтрованный индекс используется оптимизатором запросов для создания плана запроса.

Предупреждение
Параметры SET Обязательное значение Значение сервера по умолчанию По умолчанию

Значение OLE DB и ODBC

По умолчанию

Значение DB-Library

ANSI_NULLS ON ON ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS* ON ON ON OFF
ARITHABORT ON ON OFF OFF
CONCAT_NULL_YIELDS_NULL ON ON ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON ON ON OFF

*Если уровень совместимости базы данных равен 90 или более, при установке параметра ANSI_WARNINGS в состояние ON параметр ARITHABORT также устанавливается в состояние ON. Если уровень совместимости базы данных установлен в состояние 80 или более раннее, то параметр ARITHABORT необходимо явным образом установить в состояние ON.

Если параметры SET неверны, может произойти следующее.

Отфильтрованный индекс не будет создан.

Компонент Компонент Database Engine сформирует ошибку и выполнит откат любой инструкции INSERT, UPDATE, DELETE или MERGE, которая изменила значения данных в индексе.

Оптимизатор запросов не учтет индекс в плане выполнения любой инструкции Transact-SQL.

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

Максимальный размер ключа индекса составляет 900 байт для кластеризованного индекса и 1700 байт для некластеризованного индекса. (Перед База данных SQL версии 12 и SQL Server 2020 ограничение всегда была 900 байт.) Индексы на varchar , превышающих ограничение байтов может быть создан, если существующие данные в столбцах не превышает предел, во время создания индекса, однако последующие операции вставки или обновления на столбцы, для которых общий размер превышает предел не будет работать. Ключ кластеризованного индекса не может включать в себя столбцы varchar , для которых существуют данные в единице размещения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается для столбца типа varchar и существующие данные располагаются в единице размещения IN_ROW_DATA, то все последующие операции вставки или обновления для данного столбца, выталкивающие данные за пределы строки, будут завершаться ошибкой.

Некластеризованные индексы могут включать неключевые столбцы на конечном уровне индекса. Эти столбцы не рассматривает Компонент Database Engine при вычислении размера ключа индекса. Дополнительные сведения см. в статье Create Indexes with Included Columns.

Если ключевые столбцы секционирования не представлены в неуникальном кластеризованном индексе при секционировании таблиц, то они добавляются в индекс службами Компонент Database Engine. Объединенный размер индексированных столбцов (без учета включенных столбцов) и любых добавленных столбцов секционирования в неуникальном кластеризованном индексе не может превышать 1800 байт.

Индексы могут создаваться на вычисляемых столбцах. Кроме того, вычисляемые столбцы могут иметь свойство PERSISTED. Это значит, что компонент Компонент Database Engine хранит вычисляемые значения в таблице и обновляет их при изменении любого столбца, от которого зависит вычисляемый столбец. Компонент Компонент Database Engine использует эти сохраненные значения при создании индекса столбца и при появлении ссылки на этот столбец в запросе.

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

Вычисляемые столбцы, основанные на выражениях языка Transact-SQL, функциях CLR и методах определяемых пользователем типов данных CLR, помеченных пользователем как детерминированные.

Вычисляемые столбцы, основанные на выражениях, которые определены компонентом Компонент Database Engine как детерминированные, но не являются точными.

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

Ограничения UNIQUE или PRIMARY KEY могут содержать вычисляемый столбец, если он удовлетворяет всем условиям для индексирования. Вычисляемый столбец должен быть детерминированным и точным или детерминированным и сохраняемым. Дополнительные сведения о детерминизме см. в разделе функций.

Вычисляемые столбцы, производные от изображение, ntext, текст, varchar(max), nvarchar(max), varbinary(max), и xml типов данных могут индексироваться как ключевые или включенные неключевые столбца до тех пор, пока тип данных вычисляемого столбца приемлем как ключевой столбец индекса или неключевого столбца. Например, нельзя создать первичный XML-индекс для вычисляемого xml столбца. Если размер ключа индекса превышает 900 байт, выдается предупреждение.

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

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

Дополнительные сведения см. в разделе Indexes on Computed Columns.

Неключевые столбцы, называемые «включенными столбцами», могут добавляться на конечный уровень некластеризованного индекса для повышения производительности запроса благодаря тому, что индекс включает все необходимые данные для запроса. Т. е. все столбцы, указанные в запросе, включаются в индекс в качестве ключевых или неключевых столбцов. Таким образом оптимизатор запросов может найти все необходимые данные путем просмотра индекса, не обращаясь к данным таблицы или кластеризованного индекса. Дополнительные сведения см. в статье Create Indexes with Included Columns.

На сервере SQL Server 2005 представлены новые параметры индексов и изменен способ установки параметров. Обратной совместимости синтаксиса WITH option_name эквивалентен аргументу WITH ( ** = ** ON ). Устанавливая параметры индекса, необходимо соблюдать следующие правила.

Новые параметры индекса можно указать только с помощью аргумента WITH (option_name ** = ** ON | OFF).

Нельзя задавать параметры с помощью нового синтаксиса и совместимого старого в одной и той же инструкции. Например, указание WITH (DROP_EXISTING, ONLINE ** = ** ON) приводит к ошибке инструкции.

При создании XML-индекса параметры должны указываться с помощью аргумента WITH (option_name ** = ** ON | OFF).

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

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

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

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

Предложение DROP_EXISTING не сортирует данные заново, если те же ключевые столбцы индекса используются в том же порядке с тем же порядком сортировки по возрастанию или убыванию, за исключением случаев, когда инструкция индекса задает некластеризованный индекс и параметр ONLINE равен OFF. Если кластеризованный индекс отключен, операция CREATE INDEX WITH DROP_EXISTING должна выполняться с параметром ONLINE в значении OFF. Если некластеризованный индекс отключен и не связан с отключенным кластеризованным индексом, операция CREATE INDEX WITH DROP_EXISTING может выполняться с параметром ONLINE в значении OFF или ON.

Если удаляются или перестраиваются индексы со 128 или более экстентами, компонент Компонент Database Engine откладывает фактическое освобождение страниц и связанных с ними блокировок до фиксации транзакции.

Следующие правила применяются к операциям с индексами в режиме в сети.

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

Для операций с индексами требуется дополнительное временное место на диске.

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

Дополнительные сведения см. в статье Perform Index Operations Online.

Если заданы аргументы ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при обращении к индексу разрешены блокировки на уровне строк, страниц и таблиц. Компонент Компонент Database Engine выбирает соответствующую блокировку и может повышать уровень с блокировки строки или страницы до блокировки таблицы.

Если заданы аргументы ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при обращении к индексу разрешены только блокировки на уровне таблиц.

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

Сжатие данных описывается в разделе сжатие данных. Необходимо учесть следующие основные моменты.

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

Неконечные страницы индекса не сжаты на уровне страниц, но могут быть сжаты на уровне строк.

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

При создании кластеризованного индекса в куче кластеризованный индекс наследует состояние сжатия кучи, если не указано другое состояние сжатия.

На секционированные индексы налагаются следующие ограничения.

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

Инструкции ALTER INDEX <> > . Инструкция REBUILD PARTITION . производит перестроение указанной секции индекса.

Инструкции ALTER INDEX <> > . Инструкция REBUILD WITH . производит перестроение всех секций индекса.

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры sp_estimate_data_compression_savings .

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner .

Хранилище данных SQLи Параллельное хранилище данных, невозможно создать:

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

Не удается создать индекс для представления.

Чтобы просмотреть сведения о существующих индексов, можно выполнить запрос sys.indexes (Transact-SQL) представления каталога.

База данных SQL не поддерживает параметры файловой группы и filestream.

A. Создание rowstore простого некластеризованного индекса

В следующем примере создается некластеризованный индекс на VendorID столбец Purchasing.ProductVendor таблицы.

Б. Создание rowstore простого некластеризованного составного индекса

В следующем примере создается некластеризованный составной индекс на SalesQuota и SalesYTD столбцы Sales.SalesPerson таблицы.

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

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

Г. Добавление столбца индекса

В следующем примере создается индекс IX_FF с двумя столбцами из таблицы dbo. Таблица FactFinance. Следующий оператор перестраивает индекс в один столбец и сохраняет существующее имя.

Д. Создание уникального некластеризованного индекса

В следующем примере создается уникальный некластеризованный индекс на столбце Name таблицы Production.UnitMeasure в базе данных AdventureWorks2012. Индекс требует уникальности данных, вставляемых в столбец Name .

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

В результате выдается сообщение об ошибке:

Е. Использование параметра IGNORE_DUP_KEY

В следующем примере демонстрируется влияние параметра IGNORE_DUP_KEY со значениями ON и OFF на операцию вставки нескольких строк во временную таблицу. В таблицу #Test вставляется одна строка, которая намеренно приведет к появлению повторяющихся значений при выполнении второй многострочной операции вставки INSERT . Счетчик строк таблицы возвращает количество вставленных строк.

Ниже приведены результаты второй инструкции INSERT .

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

Те же инструкции выполняются вновь, но теперь с аргументом IGNORE_DUP_KEY , равным OFF .

Ниже приведены результаты второй инструкции INSERT .

Обратите внимание, что ни одна из строк таблицы Production.UnitMeasure не была вставлена, хотя ограничение индекса UNIQUE было нарушено только одной строкой.

Ж. Использование предложения DROP_EXISTING для удаления и повторного создания индекса

В следующем примере удаляется и создается повторно существующий индекс на столбце ProductID таблицы Production.WorkOrder в базе данных AdventureWorks2012 с использованием параметра DROP_EXISTING . Указываются также аргументы FILLFACTOR и PAD_INDEX .

З. Создать индекс для представления

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

И. Создание индекса с включенными (неключевыми) столбцами

В следующем примере создается некластеризованный индекс с одним ключевым столбцом ( PostalCode ) и четырьмя неключевыми столбцами ( AddressLine1 , AddressLine2 , City , StateProvinceID ). Далее следует запрос, все данные для которого есть в индексе. Выводить индекс, выбранный оптимизатором запросов, на запроса в меню SQL Server Management Studioвыберите Показать действительный план выполнения перед выполнением запроса.

К. Создание секционированного индекса

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

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Л. Создание отфильтрованного индекса

В следующем примере создается фильтрованный индекс на таблице Production.BillOfMaterials в базе данных AdventureWorks2012. Предикат фильтра может включать столбцы, не являющиеся ключевыми в отфильтрованном индексе. Предикат в примере выбирает только те строки, где EndDate не равно NULL.

М. Создание сжатого индекса

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

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

Следующий пример демонстрирует создание индекса для секционированной таблицы с помощью сжатия страниц для секции 1 индекса и сжатия строк для секций индекса со 2 по 4 .

Н. Базовый синтаксис

О. Создать некластеризованный индекс для таблицы в текущей базе данных

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

П. Создать кластеризованный индекс для таблицы в другой базе данных

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

Т. Добавление столбца индекса

В следующем примере создается индекс IX_FF с двумя столбцами из таблицы dbo. Таблица FactFinance. Следующий оператор демонстрирует перестроение индекса с тем же именем и один столбец.

Как создать, использовать и удалить уникальные, составные и другие индексы SQL

MS SQL Server
Для удаления обычных индексов в СУБД MS SQL используется оператор DROP INDEX:

Однако оператор DROP INDEX неприменим к первичным ключам (индекс PRIMARY KEY) и уникальным индексам (UNIQUE). Для удаления первичных ключей и уникальных индексов используется оператор ALTER TABLE с предложением DROP CONSTRAINT:

ALTER TABLE DROP CONSTRAINT

MySQL Server
В СУБД MySQL удаление индексов производится операторами DROP INDEX или ALTER TABLE. Удаление первичных ключей (индексов PRIMARY KEY) осуществляется только с помощью оператора ALTER TABLE. Операторы удаления индексов имеют следующий синтаксис:

ALTER TABLE DROP INDEX

Оператор для удаления индекса PRIMARY KEY имеет синтаксис:

ALTER TABLE DROP PRIMARY KEY

Если такой индекс с именем PRIMARY KEY создан не был, а таблица имеет один или несколько индексов UNIQUE, будет удален первый из них.

Удаление столбцов из таблицы влияет на индексы. Удаляя столбец из таблицы, вы тем самым удаляете этот столбец из индекса. Удаляя все индексируемые столбцы из таблицы, вы удаляете весь индекс.

Удалить уникальный индекс client_phone в таблице tbl_clients

MS SQL: *
ALTER TABLE tbl_clients DROP CONSTRAINT client_phone

MySQL:
DROP INDEX client_phone ON tbl_clients
или
ALTER TABLE tbl_clients DROP INDEX client_phone

Добавить комментарий