SQL
Индексы
Индексы представляют собой структуру данных, содержащую указатели на содержимое таблицы, упорядоченной в определенном порядке, чтобы помочь оптимизировать запросы базы данных. Они похожи на индекс книги, где страницы (строки таблицы) индексируются по их номеру страницы.
Существует несколько типов индексов и могут быть созданы на столе. Когда индекс существует в столбцах, используемых в предложении WHERE запроса, в предложении JOIN или в предложении ORDER BY, он может существенно повысить производительность запросов.
замечания
Индексы являются способом ускорения запросов на чтение путем сортировки строк таблицы в соответствии с столбцом.
Эффект индекса не заметен для небольших баз данных, таких как пример, но если имеется большое количество строк, это может значительно повысить производительность. Вместо проверки каждой строки таблицы сервер может выполнять двоичный поиск по индексу.
Компромисс для создания индекса — скорость записи и размер базы данных. Хранение индекса занимает пробел. Кроме того, каждый раз, когда выполняется INSERT или обновляется столбец, индекс должен обновляться. Это не такая дорогостоящая операция, как сканирование всей таблицы в запросе SELECT, но это все еще нужно иметь в виду.
Создание индекса
Это создаст индекс для столбца EmployeeId в таблице Cars . Этот индекс улучшит скорость запросов, запрашивающих сервер для сортировки или выбора значений в EmployeeId , например:
Индекс может содержать более 1 столбца, как в следующем;
В этом случае индекс был бы полезен для запросов, запрашивающих сортировку или выбор всех включенных столбцов, если набор условий упорядочен таким же образом. Это означает, что при извлечении данных он может найти строки, которые будут извлекаться с использованием индекса, а не просматривать всю таблицу.
Например, в следующем случае будет использоваться второй индекс;
Однако, если порядок отличается, индекс не имеет таких же преимуществ, как в следующем;
Индекс не так полезен, потому что база данных должна извлекать весь индекс по всем значениям EmployeeId и CarID, чтобы определить, какие элементы имеют OwnerId = 17 .
(Индекс все еще может быть использован, возможно, оптимизатор запросов обнаруживает, что извлечение индекса и фильтрация на OwnerId , а затем получение только необходимых строк выполняется быстрее, чем извлечение полной таблицы, особенно если таблица большая.)
Кластерные, уникальные и отсортированные индексы
Индексы могут иметь несколько характеристик, которые могут быть установлены либо при создании, либо путем изменения существующих индексов.
Вышеупомянутый оператор SQL создает новый кластерный индекс для Employees. Кластеризованные индексы — это индексы, которые определяют фактическую структуру таблицы; сама таблица сортируется в соответствии со структурой индекса. Это означает, что на таблице может быть не более одного кластеризованного индекса. Если кластерный индекс уже существует в таблице, вышеуказанный оператор не будет выполнен. (Таблицы без кластеризованных индексов также называются кучами.)
Это создаст уникальный индекс для столбца Email в таблице Customers . Этот индекс, наряду с ускорением запросов, таких как нормальный индекс, также заставит каждый адрес электронной почты в этом столбце быть уникальным. Если строка вставлена или обновлена с нестандартным значением электронной почты , вставка или обновление по умолчанию будут неудачными.
Это создает индекс для клиентов, который также создает ограничение таблицы, в котором EmployeeID должен быть уникальным. (Это не удастся, если столбец в настоящее время не уникален — в этом случае, если есть сотрудники, которые имеют идентификатор).
Это создает индекс, который сортируется в порядке убывания. По умолчанию индексы (по крайней мере, на сервере MSSQL) возрастают, но их можно изменить.
Вставка с уникальным индексом
Это приведет к сбою, если в столбце Электронная почта Клиентов установлен уникальный индекс. Однако для этого случая можно определить альтернативное поведение:
SAP ASE: индекс падения
Эта команда уменьшит индекс в таблице. Он работает на сервере SAP ASE .
Синтаксис:
Пример:
Сортированный указатель
Если вы используете индекс, который сортируется так, как вы его извлекли, SELECT не будет выполнять дополнительную сортировку при поиске.
Когда вы выполняете запрос
Система базы данных не будет выполнять дополнительную сортировку, поскольку она может выполнять поиск по индексу в этом порядке.
Удаление индекса, или отключение и восстановление его
Мы можем использовать команду DROP для удаления нашего индекса. В этом примере мы будем DROP индексом ix_cars_employee_id на столе Cars .
Это полностью исключает индекс, и если индекс кластеризуется, удаляется любая кластеризация. Он не может быть перестроен без воссоздания индекса, который может быть медленным и дорогостоящим. В качестве альтернативы индекс можно отключить:
Это позволяет таблице сохранить структуру вместе с метаданными об индексе.
Критически это сохраняет статистику индекса, так что можно легко оценить изменение. Если это оправдано, индекс затем может быть перестроен, а не полностью восстановлен;
Уникальный индекс, который позволяет NULLS
Эта схема допускает отношения 0..1 — люди могут иметь ноль или одну водительскую лицензию, и каждая лицензия может принадлежать только одному человеку
Перестроить индекс
С течением времени индексы B-Tree могут стать фрагментированными из-за обновления / удаления / вставки данных. В терминологии SQLServer у нас может быть внутренняя (индексная страница, наполовину пустая) и внешняя (логический порядок страниц не соответствует физическому порядку). Индекс перестройки очень похож на падение и воссоздание.
Мы можем перестроить индекс с помощью
По умолчанию индекс перестройки — это автономная операция, которая блокирует таблицу и предотвращает DML, но многие RDBMS позволяют осуществлять онлайн-перестройку. Кроме того, некоторые поставщики БД предлагают альтернативы перестройке индекса, такие как REORGANIZE (SQLServer) или COALESCE / SHRINK SPACE (Oracle).
Кластерный индекс
При использовании кластерного индекса строки таблицы сортируются по столбцу, к которому применяется кластерный индекс. Поэтому в таблице может быть только один кластерный индекс, потому что вы не можете упорядочить таблицу двумя разными столбцами.
Как правило, лучше использовать кластерный индекс при выполнении чтения в больших таблицах данных. Домен кластеризованного индекса заключается в том, чтобы писать в таблицу, и данные необходимо реорганизовать (прибегать).
Пример создания кластерного индекса в таблице Сотрудники в столбце Employee_Surname:
Некомпилированный индекс
Некластеризованные индексы хранятся отдельно от таблицы. Каждый индекс в этой структуре содержит указатель на строку в таблице, которую он представляет.
Эти указатели называются локаторами строк. Структура локатора строк зависит от того, хранятся ли страницы данных в куче или кластеризованной таблице. Для кучи указатель строки является указателем на строку. Для кластерной таблицы локатор строк представляет собой кластерный индексный ключ.
Пример создания некластеризованного индекса в таблице Сотрудники и столбец Employee_Surname:
В таблице может быть несколько некластеризованных индексов. Операции чтения, как правило, медленнее с некластеризованными индексами, чем с кластеризованными индексами, так как вам нужно сначала индексировать, а не таблицу. Однако никаких ограничений в операциях записи нет.
Частичный или отфильтрованный указатель
SQL Server и SQLite позволяют создавать индексы, которые содержат не только подмножество столбцов, но и подмножество строк.
Рассмотрим постоянное возрастающее количество заказов с order_state_id равным завершенному (2), и стабильное количество заказов с order_state_id equal (1).
Если ваш бизнес использует такие запросы:
Частичная индексация позволяет вам ограничить индекс, включая только незавершенные заказы:
Этот индекс будет меньше, чем нефильтрованный индекс, что экономит место и снижает стоимость обновления индекса.
Все, что необходимо знать про индексы MS SQL
Предлагаем расширить знания об индексах в MS SQL Server. Получите полное представление о них, преимуществах использования, структуре. Узнаете, как создавать индексы, оптимизировать и удалять. Все самое полезное читайте в одной статье.
Что такое индексы в sql server
Разберемся в понятии индексов (indexes) – это особые таблицы, используемые поисковыми системами для поиска данных. Их активное использование играет важнейшую роль в повышении производительности sql серверов.
Словно указатель в грамотно составленной книге, индекс помогает быстро получить доступ к строкам требуемых данных в таблице, соответствующих запросу. Таким образом, их использование позволяет ускорить выполнение требуемого запроса.
К примеру, для получения всех страниц в книге, касающихся выбранной тематики, сначала нужно обратиться к перечню тем, а затем выбрать нужные страницы. Для этого следует создать индекс по выбранной теме. На ее основе и будут выбираться ссылки на страницы книги по затронутой теме. Используя значения, заданные первичным ключом, sql server найдет нужный индекс и с его помощью быстро выберет все строки с необходимыми данными. Если не использовать индекс, то для поиска информации будет произведено сканирование каждой строки таблицы. Это значительно понизит производительность и увеличит время поиска.
Благодаря индексу процесс поиска данных сокращается за счет их упорядочивания как физического, так и логического. Таким образом, он выглядит как набор ссылок на данные, которые упорядочены по выбранному столбцу таблицы. Такой столбец называется индексированным. Индексы находятся в таблице и по сути выступают полезными внутренними механизмами системы sql-сервера, которые помогают сделать доступ к данным наиболее оптимальным.
Создать стандартный индекс можно на всех столбцах данных, кроме:
- столбцов, которые используются для хранения данных объектов, имеющих большие размеры, (LOB): TEXT, IMAGE, VARCHAR (MAX);
- представленных в XML. Для работы с данными, представлены в таком формате используются xml-index, которые отличаются от стандартных. О них рассказано ниже.
Об индексах и кучах
Как только таблица создана и в ней еще нет индексов, она выглядит как куча данных (Heap). В ней все записи хранятся хаотично, без определенного порядка. Потому их и называют «кучами».
Если в таблице необходимо найти определенные данные, sql server просканирует ее (Table scan). Пока в таблице не заданы индексы, поддерживающие ограничения (UNIQUE CONSTRAINT, UNIQUE INDEX или PRIMARY KEY), сервер прочитает все табличные записи (с первой до последней) и выберет те, которые удовлетворяют условиям поиска.
Это демонстрирует базовые функции indexes:
- повышение скорости поиска информации и производительности запросов;
- сохранение целостности данных через обеспечение уникальности строк таблицы.
Но не всегда индекс помогает ускорить поиск информации. Для таблиц небольших размеров обычный перебор данных может оказаться намного эффективнее выборки данных по индексам.
Indexes имеют и недостатки:
- требуется много места на дисковом пространстве и в оперативной памяти. Чем длиннее ключ, тем большего размера индекс и место для его хранения;
- замедляется производительность системы (медленнее выполняются операции вставок, обновления либо удаления записей).
Но современные методы их создания позволяют не только снижать негативный эффект для вышеперечисленных операций, но и увеличивать скорость выполнения.
Структура
Все индексы имеют одинаковую структуру (structure). Они состоят из:
- наборов страниц;
- узлов, имеющих древовидную структуру, иерархическую по природе.
Все они хранятся в виде сбалансированных B-деревьев (B-tree). Начало такого дерева расположено в корневом узле (находящимся на вершине иерархии) и по сути является «входной дверью». Этот узел имеет одну страницу, в которой содержатся указатели на ключи последующих уровней.
В нижней части иерархии расположены листья дерева (являющиеся конечными узлами). Длины веток одинаковы.
В таком дереве сбалансирована каждая ветка. Благодаря внутреннему механизму при любых изменениях в таблице дерево снова становится сбалансированным.
При формировании запроса к индексированному столбцу подсистема начинает процесс поиска с верхнего узла к нижним, проходя промежуточные и обрабатывая их. На каждом уровне располагается все более развернутая информация о запрашиваемых данных. Как только достигается нижний уровень листьев (leaf level) поиск прекращается, т.к. подсистема запросов находит необходимое значение.
Типы индексов
В Microsoft SQL Server используются следующие индексы: кластерные и некластерные. Рассмотрим их подробнее.
Кластерный индекс
Основная его задача — сохранение табличных данных в виде, отсортированном по значению ключа. Таблице или представлению может быть присущ лишь единственный кластеризованный индекс (Clustered index), потому что табличные данные могут отсортировываться в едином возможном порядке – либо возрастания, либо убывания. По возможности, у каждой таблицы должен быть Clustered index.
Табличные данные будут храниться отсортированными лишь в том случае, когда таблица имеет кластеризованный индекс. Строки табличных данных Clustered index хранит в уровнях листьев.
Если у таблицы нет Clustered index, в момент формирования ограничений PRIMARY KEY и UNIQUE, он формируется автоматически. Когда для таблиц/ куч созданы Nonclustered indexes, то в процессе создания Clustered index все некластеризованные должны быть перестроены.
Содержание листьев зависит от того, индекс кластерный или некластерный. Они могут содержать как табличные данные, так и ссылки, указывающие на строки с ними.
Некластерный индекс
Некластеризованными (Nonclustered) называют такие индексы, которые содержат:
- значения ключей – ключевые столбцы, по которым они определены;
- указатели на строки в таблице, содержащие реальные данные (значения ключа).
Чтобы обнаружить и получить запрашиваемые данные, для системы подзапросов потребуется совершение дополнительных операций. Содержимое указателей на запрашиваемые данные полностью зависит от того, как они хранятся.
Он может указывать на:
- кучу и тем самым приводить к идентификатору строки с искомыми данными;
- таблицу с Clustered index, указывая, что именно он используется что для поиска действительных данных.
Nonclustered indexes могут быть расширены дополнительными столбцами (included column). А значит, листья будут сохранять значения индексированных и дополнительных неиндексированных столбцов. Это свойство дает возможность обойти определенные ограничения, возложенные на индекс. Данный подход позволяет включать неиндексируемые столбцы либо обходить ограничения на длину индекса.
Главные свойства Nonclustered indexes:
- их нельзя отсортировать;
- на таблицу или представление можно сформировать свыше одного (до 999) некластеризованных индексов. Но не стоит создавать максимальное количество Nonclustered indexes. Нужно помнить, что они способны как повысить, так и понизить производительность.
Nonclustered indexes могут создаваться на любых таблицах, в том числе и имеющих кластерный индекс.
Специальные типы индексов
Существует большое число специальных индексов, которые могут быть как кластерными, так и некластерными. Рассмотрим некоторые из них.
Фильтруемый
Фильтруемым (Filtered) индексом называют оптимизированный Nonclustered index, в котором задействован предикат фильтра для индексации части строк в таблице.
Тщательно спроектированный Filtered index способен:
- увеличить производительность;
- уменьшить затраты на обслуживание и хранение индексов.
Составной
Составным называют индекс, который:
- может включать более одного (до 16) столбцов, выступающих ключевыми значениями;
- ограничивается общей длиной (не превышающей 900 байт);
- содержит поля, которые принадлежат единой таблице.
Простые индексы, в отличие от составных, создаются лишь по единственному столбцу.
Создание составных индексов целесообразно, когда:
- для поискового запроса ключами выступают два и более столбцов;
- в поисковом запросе используются все поля составного индекса. Поисковый запрос, в котором не задействованы все поля, вероятнее всего, использоваться не будет.
Отличным примером может служить телефонный справочник. Он сформирован по фамилии и имени, т.к. много людей имеют одинаковую фамилию. Следовательно, логично будет создать индекс одновременно и по фамилии, и по имени.
Отметим, что наивысший приоритет в процессе сортировки принадлежит первым колонкам, описываемым в CREATE INDEX. Потому, в числе первых должны указываться колонки уникальные. Чтобы индекс был задействован при выборке данных в таблице, сам запрос обязательно должен ссылаться именно на колонку, указанную первой.
Использование составных индексов поможет увеличить производительность за счет того, что для выполнения поиска данных сервер будет сканировать только его, что поможет снизить в таблице число индексов.
Query Optimizer использует их в зависимости от структуры запроса.
Уникальный
Уникальным (Unique) называют индекс, обеспечивающий уникальное значение всех строк по определенному ключу и гарантирующий, что в ключе индекса не будет значений одинаковых, повторяющихся. Для составного ключа понятие уникальности касается всех index columns, но не распространяется на каждый столбец в отдельности.
Если в таблице формируется Unique index одновременно по ряду столбцов, это означает, что абсолютно каждая вариация значений в ключе будет уникальной.
SQL сервером создается автоматически Unique index для ключевых столбцов при формировании ограничений UNIQUE либо PRIMARY KEY. Но он формируется лишь при выполнении условия отсутствия дублей в ключевых столбцах таблицы.
Уникальный индекс создается автоматом при определении ограничений столбца:
- первичным ключом (на один столбец либо сразу на несколько), при условии, что кластерный индекс ранее не создавался. В том случае, когда он все-таки уже создан, сервер создаст уникальный некластерный индекс по первичному ключу;
- ограничением на уникальность значений – сервером создается Unique Nonclustered index. Когда кластерный индекс не был сформирован заранее, есть возможность создания именно Unique Clustered index.
Колоночный
Колоночным (Columnstore) называют индекс, в котором данные хранятся в столбцах. Использование Columnstore indexes наиболее целесообразно применять для крупных хранилищ, т.к. они помогут:
- производительность запросов увеличить в несколько раз;
- размеры данных уменьшить (благодаря их сжатию).
Пространственный
Пространственным (Spatial) называют тип расширенного индекса, позволяющего индексировать столбцы с пространственными данными (представленные в типах Geography или Geometry). Spatial index позволяет наилучшим образом использовать определенные операции запросов относительно пространственных столбцов и может создаваться только для них.
Основное условие создания пространственного индекса – наличие PRIMARY KEY для таблиц.
Полнотекстовый
Полнотекстовые (Full-text) индексы применяются для повышения эффективности поиска определенных слов в строках, где данные представлены в символах.
Действия по созданию и обслуживанию Full-text indexes называются «заполнениями». Встречаются заполнения:
- полное – осуществляется SQL сервером после создания нового Full-text index. Размер таблицы влияет на затребованный объем ресурсов. При увеличении размера на операцию требуются ресурсы большего размера. Потому предусмотрена возможность откладывания этого процесса;
- основанное на отслеживании изменений – применяется для того, чтобы обслуживать Full-text index после полного заполнения (первоначального).
Покрывающий
Покрывающим (Covering) называют индекс, позволяющий на конкретный запрос получать запрашиваемую информацию в полном объеме с листьев индекса, не обращаясь к записям таблицы. А значит, в Covering index хранится достаточный объем данных для полноценного ответа на запрос. Потому нет необходимости обращаться к таблице.
Благодаря тому, что ответ можно получить без использования таблицы, покрывающие индексы быстрее остальных. Однако, они становятся достаточно большими, потому злоупотреблять ими не стоит.
XML-индекс
XML – специфический тип индекса, предназначенный для работы с данными в столбцах таблицы, представленными в соответствующем формате. Он делает более эффективной обработку поисковых запросов к ним.
- первичные – индексируют, хранят в столбцах XML теги, пути, значения. Целесообразно создавать, когда таблица по первичному ключу имеет кластерный индекс;
- вторичные – создаются лишь для таблиц с первичным XML-index. Применяются для увеличения производительности системы по определенному типу обращения к XML-столбцам. Встречаются типы XML-indexes: PATH, VALUE, PROPERTY.
Индексы, используемые в оптимизированных таблицах
Активно используются специальные индексы для таблиц данных:
- оптимизированные для памяти (In-Memory OLTP). К таковым относятся Хэш индексы (Hash);
- Nonclustered indexes, которые специально создаются для сканирования (как упорядоченного, так и диапазонного) и оптимизируются для памяти.
Создание и проектирование индексов в ms sql server
Польза индексов очевидна, потому и проектироваться они должны крайне аккуратно. Созданные тщательным образом способны улучшить производительность, а непрофессионально – понизить.
Индексы занимают достаточно много дискового места, потому не имеет смысла создавать их больше, чем нужно. Более того, при каждом обновлении строк, автоматически обновляются и индексы. Это в свою очередь может потребовать увеличения ресурсов и грозить снижением производительности.
Очень важно при проектировании соблюдать ряд требований как к базам данных, так и к запросам направленным к ним.
Базы данных
Как сказано выше, производительность системы напрямую зависит от индексов. При поступлении запроса они могут увеличивать ее, обеспечивая быстрый поиск данных либо снижать, т.к. при каждой операции с данными будут изменяться и они, дабы отражать действия, производимые над данными. И не важно, что происходит с ними – добавление, удаление или обновление.
Потому, при разработке плана стратегии по индексированию, необходимо придерживаться советов специалистов:
- Если предполагается частое обновление данных в таблице, то для нее нужно применять минимум индексов.
- Для таблицы со значительным количеством данных, которые предположительно будут редко изменяться, можно использовать то число индексов, которое улучшит производительность запросов. Но для таблиц небольшого объема не всегда целесообразно вообще их использовать. Такой поиск может выполняться дольше, чем обычное сканирование таблицы.
- Для Clustered indexes используйте самые короткие поля, которые только допустимы. Лучше всего их применять на столбцах с уникальными значениями и в которых не допускается использование NULL. По этой причине чаще всего PRIMARY KEY выступает в роли Clustered index.
- Производительность индекса напрямую зависит от того, насколько уникальны значения в столбце. Она снижается с увеличением дублей если в столбце и растет с уменьшением. Потому, при каждой возможности следует использовать уникальный индекс.
- Если используется составной индекс, то в нем нужно учитывать порядок столбцов. Первыми идут те, в которых в выражениях используется WHERE. За ними – столбцы с наивысшими показателями уникальных значений. Остальные выстраиваются по мере понижения этого показателя.
- Допускается использование индекса на вычисляемых столбцах таблицы, но лишь при условии соблюдения определенных требований (для вычисления значений такого столбца могут использоваться только детерминистические выражения, т.е. результат для определенного набора входящих параметров всегда должен быть одинаковым).
Запросы к базе данных
При проектировании вторым важным пунктом является понимание и учет того, какие выполняются запросы к базе данных. Необходимо учитывать частоту изменения данных, а также требуется соблюдение определенных принципов:
- Предпочтительнее, чтобы один запрос содержал наибольшее число строк, нежели разбивать их на соответствующее число отдельных запросов.
- На столбцах, используемых в запросах с WHERE чаще всего, предпочтительнее создавать Nonclustered index в качестве условия поиска и соединения в JOIN.
- Следует воспользоваться возможностями индексирования столбцов, используемых в поисковых запросах на соответствие конкретным значениям.
Способы создания индексов
Предусмотрено создание индексов ms sql server с помощью двух инструментов. В этом помогут:
- SSMS (MSSQL Management Studio);
- специальный язык Transact-SQL (T-SQL, поддерживающий Paging Queries).
Как создать кластеризованный индекс
Как отмечалось выше, создание кластеризованного индекса sql сервером происходит автоматически, когда определенный столбец выбирается в качестве первичного ключа (PRIMARY KEY). Когда такого не происходит, следует создать кластерный индекс своими руками.
Чтобы создать Clustered index воспользуемся Management Studio. Для этого следует:
- Открыть SSMS.
- Воспользовавшись обозревателем выбрать соответствующую таблицу.
- Остановившись на пункте «Индексы» кликнуть мышкой.
- Выбрать «Создать индекс» и соответствующий тип (выбираем «Кластеризованный»).
- В новом окне появится форма «Новый индекс». Здесь потребуется вписать наименование нового создаваемого индекса (в рамках одной таблицы требуется, чтобы оно было уникальным). Поставить галочку, что он уникальный.
- Выбрать столбец, который будет являться ключом индекса. Он ляжет в основу создаваемого Clustered index. Провести сортировку строк табличных данных кнопкой «Добавить».
- После ввода всех необходимых параметров кликнуть «ОК».
Результатом действий станет кластерный индекс.
Он может быть создан и с помощью инструкций Transact-SQL CREATRE INDEX.
Как создать некластеризованный индекс
Для создания Nonclustered index можно воспользоваться Management Studio либо инструкциями T-SQL.
Создание Nonclustered index с включенными столбцами
Коснемся вопроса, как создать Nonclustered index с условием, что в индекс включены столбцы, которые не являются ключевыми. Такой индекс принято использовать в тех случаях, когда индекс создается под конкретный запрос. К примеру, чтобы индексом покрывался запрос полностью, т.е. включал все столбцы. Вследствие того, что запрос покрыт, увеличивается производительность. Это становится возможным благодаря тому, что оптимизатор запросов может получить все значения столбцов в индексе без обращения к табличным данным. Это ведет к уменьшению числа операций ввода-вывода на диске.
Однако стоит учитывать, что с включением в индекс неключевых столбцов размер его увеличивается. А значит, для его хранения понадобится больше дискового пространства. Это также может снизить производительность операций INSERT, UPDATE, DELETE и MERGE в базовой таблице данных.
Для его создания также воспользуемся Management Studio:
- Открыть SSMS.
- Воспользовавшись обозревателем выбрать требуемую таблицу и щелкнуть мышкой по пункту «Индексы».
- Выбрать «Создать индекс», а затем «Некластеризованный» (не ставить галочку на уникальности).
- В открывшейся форме «Новый индекс» вписать наименование нового индекса, добавить один или несколько ключевых столбцов, воспользовавшись кнопкой «Добавить».
- Перейти во вкладку «Включено столбцы». Добавить все столбцы, которые должны быть включены в индекс, воспользовавшись кнопкой «Добавить».
- Когда введены все нужные параметры кликнуть «ОК».
При необходимости, можно легко создать фильтруемый Nonclustered index. Для этого следует воспользоваться T-SQL и в операторе CREATE NONCLUSTERED INDEX в WHERE указать условие фильтрации. Так можно отфильтровать практически любые данные, не важные в запросах.
Удаление индекса
Пришло время узнать о том, какими способами могут удаляться индексы. Для начала воспользуемся Management Studio. Для этого необходимо:
- Открыть SSMS.
- Выбрать индекс, подлежащий удалению.
- Щелкнуть мышкой по нему и из списка выбрать «Удалить».
- Выполненное действие подтвердить нажатием «ОК».
Удаление индексов выполняется и с помощью инструкций T-SQL DROP INDEX (DROP INDEX IX_NonClustered ON TestTable). Однако ею нельзя воспользоваться для удаления тех индексов, которые создавались через формирование ограничений PRIMARY KEY и UNIQUE. Чтобы удалить их, следует воспользоваться инструкцией ALTER TABLE с предложением DROP CONSTRAINT.
Как выполнить изменение значений коэффициента, который установлен по умолчанию
Чтобы внести изменения в значения коэффициента, которые установлены по умолчанию, следует воспользоваться:
- SSMS;
- инструкцией T-SQL, выполнив запуск системной сохраненной процедуры;
- sp configure.
Особенности индексов и условий предложения WHERE
Если предложение WHERE инструкции SELECT содержит условие поиска данных с одним столбцом, то необходимо для него создать индекс. Это условие очень важно при высокой селективности (selectivity) условия.
Но он будет абсолютно бесполезным при постоянном уровне селективности от 80% и выше. Простое сканирование табличных данных потребует меньше времени.
Если в часто применяемом запросе условие поиска включает оператор AND, то лучше всего – создать составной индекс, включив в него сразу все табличные столбцы, которые указывались в предложении WHERE инструкции SELECT.
Оптимизация индексов
После выполнения любых действий с табличными данными sql сервером в тот же момент производятся соответствующие правки в индексах. Спустя некоторое время все подобные исправления могут спровоцировать фрагментацию данных. В результате, их может разбросать по всей базе.
Подобная фрагментация данных может стать причиной понижения производительности. Потому крайне важно время от времени проводить дефрагментацию. К подобным операциям по обслуживанию индексов относят реорганизацию и перестроение индексов.
Чтобы понять, какую именно операцию требуется провести – реорганизацию или перестроение, следует выяснить степень фрагментации данных. Она поможет понять, какой способ дефрагментации будет наиболее эффективным и что выбрать.
Чтобы выяснить уровень фрагментации следует воспользоваться системной табличной функцией sys.dm_db_index_physical_stats. Для определения уровня фрагментации всего перечня таблиц для выбранной базы, можете воспользоваться следующим запросом:
SELECT OBJECT_NAME(T1.object_id) AS NameTable,
T1.index_id AS IndexId,
T2.name AS IndexName,
T1.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS T1
LEFT JOIN sys.indexes AS T2 ON T1.object_id = T2.object_id AND T1.index_id = T2.index_id
Согласно рекомендациям Microsoft, последующие действия будут зависеть от уровня фрагментации:
- меньше 5% – о дефрагментации следует пока забыть;
- от 5 до 30% – требуется выполнить реорганизацию индекса. Это потребует минимального количества ресурсов системы и ее можно провести без долговременной блокировки;
- свыше 30% – следует выполнить перестроение индекса. При значительном уровне фрагментации это наиболее эффективно.
Реорганизация индекса
Реорганизацией называют процесс устранения фрагментации индекса. В его ходе происходит дефрагментация конечного уровня кластерных и некластерных индексов по таблицам и представлениям. Говоря простым языком – выполняется простое переупорядочивание страниц. В основе переупорядочивания лежит логический порядок конечных узлов (выполняете слева направо).
Если хотите провести реорганизацию – воспользуйтесь:
- MSSQL Management Studio. На выбранном индексе следует щелкнуть мышкой, из списка выбрать и нажать «Реорганизовать»;
- соответствующими инструкциями T-SQL.
Перестроение индекса
Перестроением называется операция по устранению фрагментации индекса. Он заключается в устранении старого и формировании нового.
Перестроение индекс выполняется несколькими способами. В этом поможет:
- Management Studio. Для этого необходимо выбрать нужный индекс, мышкой кликнуть по нему и выбрать «Перестроить»;
- инструкция ALTER INDEX ix с предложением REBUILD, которая по сути является заменой инструкции DBCC DBREINDEX. Ею пользуются, когда возникла потребность в масштабной операции;
- инструкция CREATE NONCLUSTERED INDEX (CREATE INDEX) с предложением DROP_EXISTING. Подходит, чтобы перестроить индекс и изменить его определения (удалить либо добавить ключевые столбцы).
Это вся полезная информация по индексам в Microsoft SQL Server. Изучайте их, а если возникнут вопросы – задавайте. Удачи в изучении и применении indexes ms sql.
1.2.7. Индексы в SQL Server
Для ограничений UNIQUE и PRIMARY KEY автоматически создается индекс, который упрощает поиск необходимых данных. Что такое индекс? Если говорить простыми словами, то это способ отсортировать данные по определенной колонке. Когда список отсортирован, намного проще производить поиск необходимых данных.
Понимание того, как хранятся данные – является основой понимание того, как получить к ним доступ. Для начала нам нужно разобраться с таким понятием как куча – это коллекция страниц данных, содержащих строки для таблицы:
- Каждая страница данных содержит 8 килобайт информации. Группа и 8-и рядом стоящих страниц называется пространством.
- Строки данных не хранятся в каком-либо определенном порядке, и нет определенного порядка для последовательности страниц.
- Страницы данных не связаны в связанные списки.
- Когда строка вставляется в страницу и страница переполнена, страница разделяется.
Сервер SQL получает доступ к данным одним из следующих способов:
- Сканирует все страницы таблицы – сканирование таблицы. Когда SQL Server выполняет сканирование таблицы он:
- Начинает с начала таблицы;
- Сканирует от страницы к странице через все строки таблицы;
- Выделяет строку, которая соответствует запросу.
- Пересекает структуру дерева индексов для поиска строк, соответствующих запросу;
- Выделяет только необходимые строки, соответствующие критериям запроса.
Первым делом, SQL Server определяет, какие индексы существуют. Оптимизатор запроса (компонент, предназначенный для генерирования оптимального плана для запроса) определяет что использовать – сканировать таблицу или индексы. Индексы более предпочтительны.
Когда вы рассматриваете, нужно ли создавать индексы, рассчитайте два фактора, для гарантирования, что индексы будут более эффективны, чем сканирование таблицы: природа данных и природа запросов к таблице.
Индексы ускоряют доступ к данным. Для примера, без индекса, без индексы вам понадобится перелистать постранично всю книгу для определения содержания. По содержанию легче найти интересующую информацию.
Сервер SQL использует индексы для указания на расположение строки в странице данных вместо просматривания всех страниц таблицы. Рассматривайте следующие факты и рекомендации об индексах:
- Индексы обычно увеличивают скорость выполнения запросов связанных таблиц и выполнение сортировки и группировки;
- Индексы принуждают делать строки уникальными, если включена уникальность.
- Индексы создаются в порядке возрастания или уменьшения.
Индексы достаточно полезны, но они занимают место на диске и берут на себя дополнительные накладные расходы и расходы на эксплуатацию. Индексы могут создать и проблемы:
- когда вы изменяете данные в индексной колонке, сервер SQL обновляет связанные индексы.
- накладные расходы на поддержку индексов требуют времени и ресурсов. Поэтому не создавайте индексы, которые не будете часто использовать.
- индексы на колонки, содержащие большое количество доблирующих данных могут иметь несколько преимуществ.
Индексы бывают кластерными (CLUSTERED) и не кластерными (NONCLUSTERED). В кластерном индексе строки физически сортируются на диске в соответствии с индексируемым полем. Я думаю, что не надо объяснять, почему кластерный индекс может быть только один на таблицу? Нельзя же одновременно физически отсортировать данные по двум ключам.
При не кластерном индексе строки могут на диске храниться в любом порядке, а сортировка осуществляется с помощью определенной таблицы или дерева индекса. В SQL сервере используется принцип дерева.
Конечно, это все из области администрирования SQL сервера, но вы должны понимать, что такое индекс и для чего он нужен.
Все первичные ключи, начиная с SQL Server 2000, по умолчанию создаются кластерными. Ограничения UNIQUE по умолчанию создаются не кластерными.
Чтобы лучше понимать индексы и разницу между кластерным и не кластерным индексом, посмотрим на рисунок 1.6, где показана примерная схема индекса. Каждый прямоугольник – это страница данных из 8 кб. Вверху находится корень дерева, а внизу листья дерева.
Допустим, что нам нужно найти имя Анатолий. Из корня мы узнаем, что для поиска информации об имени нужно спуститься влево вниз. Здесь также будет ссылка о том, что нужно спуститься еще влево вниз и тогда мы окажемся в листовом блоке.
В не кластерном индексе в листовом блоке находится ссылка на строку с данными, где можно найти имя Анатолий, а в кластерном индексе данные находятся непосредственно в листовом блоке.
Кластерный индекс иногда работает быстрее, а иногда удобнее. У меня был случай, когда я разрабатывал программу на Delphi + MS SQL Server, которая состояла из двух частей. Серверная часть программы сохраняла в базе данных строки, полученные с производственного оборудования, а клиентская их читала. Когда сервер сохранял данные в базе, программа направляла программе клиенту по сети сообщение. Получив это сообщение, клиент должен был прочитать сохраненную строку и вывести пользователю на экран.
Чтобы упростить чтение на клиентской стороне, без выполнения лишних запросов на выборку данных, я использовал готовый метод компонентов доступа к базам данных Delphi – переход на последнюю строку. Этот метод очень удобен тем, что быстро позволяет получить последнюю строку. Все прекрасно работало, но почему-то в определенные моменты переход блокировался на определенной строке, и какое-то время новее строки не отображались.
Проблема крылась как раз в не кластерном индексе. Когда базе данных не хватало места, она выделяла блок памяти на диске в произвольном месте. Если это место оказывалось в конце базы, то переходы происходили удачно. Если находилось свободное место в уже существующих блоках памяти, но не заполненных до конца (в любом месте базы данных, но не в конце), то переход не осуществлялся, потому что физически строка оказывалась не в конце. Проблему решило использование кластерного индекса.
Следующий пример, показывает, как можно создать не кластерный индекс:
Если нужно, чтобы первичный ключ был кластерным, то для MS SQL Server 2000 это по умолчанию и явное указание оператора CLUSTERED нужно только для MS SQL Server 7. Учитывайте эту разницу при обработке первичного ключа разными версиями MS SQL Server во время разработке собственных приложений. А лучше ни на кого не надеяться, а всегда явно указывать тип необходимого ключа.
В таблице может быть создано 249 не кластерных индексов и только один индекс может быть кластерным. Такое ограничение количества кластерных индексов связано с тем, что физически можно упорядочить только по одному полю, а не из-за прихоти разработчиков MS SQL Server.
Как мы уже знаем, кластерным может быть и ограничение уникальности. Для ограничения уникальности создается индекс, а свойство кластерный/не кластерный относится как раз к индексу. Например:
В данном примере создается только индекс для полей, с ограничением уникальности. При этом главного ключа нет. А что если его создать:
В данном случае создается первичный ключ и по умолчанию он должен быть кластерным. Но так как кластерным создается ограничение уникальности cn_unique, то первичный ключ автоматически получит не кластерный индекс.
А если явно указать, что мы хотим первичный ключ и ограничение уникальности сделать кластерными? В этом случае произойдет ошибка: «Cannot add more than one clustered index for constraints on table ‘Names’ » (не могу добавлять более чем один кластерный индекс для ограничения на таблицу Names). Я же говорил, что кластерным может быть только один индекс.
Чтобы вам было удобно видеть, что делает сервер после создания таблицы, лучше всего выполнить команду:
Команда sp_help (точнее это процедура SQL сервера) отображает подробную информацию о указанной таблице, в данном случае это таблица Names. Ее команду лучше всего выполнять в программе Query Analyzer, которая поставляется вместе с MS SQL Server, потому что ее результат состоит из нескольких таблиц, а Query Analyzer умеет их отображать в удобочитаемом виде.
Выполните команду и посмотрите на результат выполнения sp_help для таблицы Names. Обратите внимание на последние две строки. Здесь отображается список ограничений и их имена. Чуть выше показаны две строки индексов для этих ограничений. Первая строка соответствует ограничению cn_unique и во второй колонке видно, что этот индекс кластерный (clustered). Вторая строка – это индекс pk_idNames и во второй колонке указано, что индекс не кластерный (nonclustered).
Ваше бизнес окружение, характеристики данных и использование данных определяют колонки, которые должны быть проиндексированы. Полезность индекса напрямую зависит от процента возвращаемых запросом строк. Маленький или большой процент более эффективен.
Создавайте индексы на следующие поля:
- первичный ключ, такой индекс создается автоматически;
- внешний ключ или поле, которое часто используется для связи таблиц. На внешние ключи индексы автоматически не создаются, но если в связанных таблицах находится много строк, то индекс реально может повысить производительность. Если в основной таблице много строк, а в связанной не более 100, можно обойтись и без индекса;
- поле, используемое для поиска ряда значений;
- поле, по которому сортируются данные;
- поля, которые группируются во время агрегации (оператор GROUP BY);
- поле, которое часто используется в запросах SELECT.
- редко используемые в запросе;
- содержащие несколько уникальных значений, например колонки, содержащие только значения мужской или женский пол. Такой индекс будет только тормозить систему;
- объявленные как text, ntext или image типы данных. Колонки с этими типами данных не могут быть проиндексированы.
Вы должны создавать только самые необходимые индексы, потому что каждый лишний индекс может серьезно ударить по производительности во время добавления новых записей. Это особенно становится заметным, при массовой загрузке данных.
Попробуем разобраться, какой индекс нужно создавать – кластерный или нет. Для того чтобы сделать правильный выбор нужно понимать, как будет использоваться ваша таблица.
Когда вы оптимизируете производительность для вставки данных для часто используемой таблицы, рассмотрите создание кластерного индекса на первичный ключ уникальной колонки. Для увеличения скорости вставки в маленькие группы страниц в конец таблицы. Частый доступ помещает эти страницы в память.Таблицы, которые часто используются для отчетов, группировки для агрегации или поиска ряда значений могут принести пользу при кластерном индексе на сортируемую колонку.
Сервер SQL использует значение кластерного индекса в качестве идентификатора строки внутри каждого не кластерного индекса. Кластерный индекс может повторяться много раз в структуре вашей таблицы.
Рекомендуется делать кластерный индекс минимальным по размеру. Для предотвращения больших кластерных индексов:
- ограничьте количество колонок в кластерном индексе;
- уменьшите среднее значение символов с помощью использования типа данных varchar вместо char, а лучше использовать числовые типы данных или уникальный идентификатор guid;
- старайтесь использовать максимально маленький тип данных.
Когда вы определяете плотность ваших данных, помните, что плотность связана с определенными элементами данных. Плотность может изменяться. Что значит плотность? Рассмотрим ее на примере таблицы работников, которая содержит даты рождения. Допустим, что у вас на фирме работает 100 человек в возрасте от 23 до 30 и 10 человек в возрасте старше 30. В диапазоне от 23 до 30 получается высокая плотность, потому что здесь находиться очень много записей.
Так как данные распределяются не равномерно, оптимизатор запросов может использовать или не использовать индексы. Оптимизатор может:
- Выполнить сканирование таблицы для поля с большой плотностью или для поля, значение которого может вызвать возврат большого количества строк.
- Если в проиндексированном поле с именами много имен «Вася», то по этому имени может быть использовано сканирование. При этом, для редкого значения, например, имени «Аврора», будет использоваться индекс.
Разброс данных связан с плотностью. Когда вы определяете плотность данных, вы должны также рассматривать и разброс.
Разброс данных определяет количество данных в определенных рамках значений и как много строк попадает в эти пределы. Если индексированная колонка имеет мало уникальных значений, получение данных может быть медленным. Например, если у вас есть таблица с полем отсортированным по фамилии, то данные могут быть неравномерно разбросаны по алфавиту. На некоторые буквы фамилий больше.
Сервер сам по себе не может знать о разбросе данных. Для этого ему необходимо собрать определенную статистику о полях и содержащихся в таблице значениях, чтобы можно было принять эффективное решение. Имея статистику, сервер сможет принять более эффективное решение о том, надо ли использовать индекс, или сканирование таблицы будет выполняться быстрее. О статистике мы еще не раз будем говорить в главе 4.
Создание индексов в SQL Server
Теперь посмотрим, как создавать индексы вручную. До этого момента мы использовали индексы, которые сервер создавал автоматически для первичного ключа и уникального поля. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE, но бывает необходимость создать индекс на поле без этих ограничений.
Для создания индекса на произвольное поле используется оператор CREATE INDEX, а для удаления используется DROP INDEX. Вы должны быть владельцем базы данных или администратором, чтобы выполнять эти операторы.
Информация об индексах храниться в системной таблице sysindexes. В главе 2 мы научимся работать с таблицами и просматривать их содержимое. Просто ради интереса попробуйте просмотреть системную таблицу sysindexes. Только не вздумайте ее изменять вручную, системные таблицы можно только просматривать.
Лучше всего, если индекс создается на поле с маленьким типом данных, такой индекс будет более эффективным. Когда вы создаете кластерный индекс, все существующие не кластерные индексы перестраиваются, поэтому желательно в первую очередь создавать кластерный индекс.
В общем виде команда создания индекса выглядит следующим образом:
Чтобы удобнее было понять команду, я разбиваю ее на строчки. В первой строке указывается ключевые слова CREATE и INDEX, между которыми можно указать UNIQUE, чтобы индекс был уникальным и CLUSTERED или NONCLUSTERED, чтобы сделать индекс кластерным или не кластерным соответственно. После INDEX указывается имя индекса.
Имя должно быть понятным, должно отображать, что это индекс и желательно, чтобы отражалось имя поля. Я рекомендую использовать для этого формат: «I_CL_Имя». Первая буква I, указывает на то, что это индекс. Затем я ставлю CL или UCL, что будет показывать кластерный или не кластерный индекс. И в самом конце перечисляются имена полей, которые индексируются. В данном случае только одно поле «vcName».
Во вторую строку я пишу ключевое слово ON, за которым идет имя таблицы и в скобках имена индексируемых полей.
Следующий пример создает кластерный индекс на колонку vcName:
После имени колонки нужно указать направление сортировки индекса. Направление задается ключевыми словами ASC (возрастание) или DESC (убывание). Следующий пример создает не кластерный индекс по убыванию:
Теперь поговорим о удалении индексов. Можно удалять только созданные вами индексы. Для этого используется оператор DROP INDEX. Вы не можете использовать этот оператор для удаления индекса, который был автоматически создан на ограничения PRIMARY KEY или UNIQUE. Вы должны удалить ограничение, прежде чем удалять индекс. Нельзя удалять индексы системных таблиц.
Если удалить кластерный индекс, то все не кластерные индексы будут автоматически перестроены.
В общем виде команда удаления индекса выглядит следующим образом:
В следующем примере удаляется созданный нами ранее индекс:
Ранее мы уже создавали индекс уникальности, но делали мы это только на этапе создания таблицы. Если она уже существует, то индекс уникальности можно добавить с помощью оператора CREATE UNIQUE INDEX.
Уникальный индекс гарантирует, что все данные в колонке с таким индексом – уникальны, и не содержат повторяющихся значений. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE.
Сервер SQL проверяет дубликаты каждый раз, когда вы выполняете операторы INSERT или UPDATE. Если дубликат существует, то сервер отклоняет ваши операторы и возвращает сообщение об ошибке.
Если повторяющиеся значения существуют, когда вы создаете уникальный индекс, операция CREATE INDEX отклоняется. Сервер возвращает сообщение об ошибке с первым дубликатом, но могут существовать и еще дубликаты. Используйте следующий простой сценарий для любых таблиц, чтобы найти дублирующие значения в колонке.
Снова мы забегаем вперед, потому что запросы SELECT это тема следующей главы. Если вы не работали с SQL, то этот запрос еще не понятен для вас, но вернитесь к нему после прочтения второй главы, и все встанет на свои места.
Составные индексы
Составные индексы используют более одной колонки в качестве ключевого значения. Создавайте составные индексы, когда две или более полей чаще всего используются для поиска в качестве ключа и если запрос ссылается только на все поля в составном индексе. Если запрос будет использовать не все поля, то индекс, скорей всего использоваться не будет.
Для примера, телефонный справочник является хорошим примером. Справочник организован по фамилии. Вместе с фамилией для поиска регулярно используется имя, потому что часто существует много записей для одной фамилии с разными именами и вполне логично создать индекс из фамилии и имени одновремнно.
Вы можете объединять до 16 колонок в составной индекс. Сумма длины всех колонок составного индекса должна быть менее 900 байт. При этом, все поля должны быть из одной таблицы.
Объявляйте сначала уникальные колонки. Первые колонки, описанные в операторе CREATE INDEX, имеют высший приоритет при сортировке. При поиске данных в таблице, ваш запрос должен будет обязательно ссылаться на первую колонку индекса, иначе индекс точно использоваться не будет.
Индекс на поля «Фамилия» и «Имя» это не то же самое, что индекс на поля «Фамилия» и «Имя». Эти индексы имеют разный порядок полей. Например, для первого случая сортировка будет следующей:
Те же самые поля, но с индексом «Имя» и «Фамилия» будут отсортированы следующим образом:
В данном случае главным является имя, и именно оно сортируется первым.
Составной индекс позволяет повысить производительность запросов и уменьшить количество индексов на таблицу. Производительность повышается за счет того, что сервер для поиска необходимых данных сканирует только один индекс.
Следующий пример создает не кластерный составной индекс для таблицы телефонного справочника. Обратите внимание, что поле «Фамилия» описывается первой, потому что она чаще всего является основой при выборке данных из таблицы:
Так как индекс уникальный, в таблицу нельзя будет записать двух людей с фамилией и именем Иванов Андрей.
Сервер SQL предлагает опции, которые могут ускорить создание индекса, а также увеличить производительность индексов.
# Indexes
Indexes are a data structure that contains pointers to the contents of a table arranged in a specific order, to help the database optimize queries. They are similar to the index of book, where the pages (rows of the table) are indexed by their page number.
Several types of indexes exist, and can be created on a table. When an index exists on the columns used in a query’s WHERE clause, JOIN clause, or ORDER BY clause, it can substantially improve query performance.
# Creating an Index
This will create an index for the column EmployeeId in the table Cars. This index will improve the speed of queries asking the server to sort or select by values in EmployeeId, such as the following:
The index can contain more than 1 column, as in the following;
In this case, the index would be useful for queries asking to sort or select by all included columns, if the set of conditions is ordered in the same way. That means that when retrieving the data, it can find the rows to retrieve using the index, instead of looking through the full table.
For example, the following case would utilize the second index;
If the order differs, however, the index does not have the same advantages, as in the following;
The index is not as helpful because the database must retrieve the entire index, across all values of EmployeeId and CarID, in order to find which items have OwnerId = 17 .
(The index may still be used; it may be the case that the query optimizer finds that retrieving the index and filtering on the OwnerId , then retrieving only the needed rows is faster than retrieving the full table, especially if the table is large.)
# Clustered, Unique, and Sorted Indexes
Indexes can have several characteristics that can be set either at creation, or by altering existing indexes.
The above SQL statement creates a new clustered index on Employees. Clustered indexes are indexes that dictate the actual structure of the table; the table itself is sorted to match the structure of the index. That means there can be at most one clustered index on a table. If a clustered index already exists on the table, the above statement will fail. (Tables with no clustered indexes are also called heaps.)
This will create an unique index for the column Email in the table Customers. This index, along with speeding up queries like a normal index, will also force every email address in that column to be unique. If a row is inserted or updated with a non-unique Email value, the insertion or update will, by default, fail.
This creates an index on Customers which also creates a table constraint that the EmployeeID must be unique. (This will fail if the column is not currently unique — in this case, if there are employees who share an ID.)
This creates an index that is sorted in descending order. By default, indexes (in MSSQL server, at least) are ascending, but that can be changed.
# Sorted Index
If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional sorting when in retrieval.
When you execute the query
The database system would not do additional sorting, since it can do an index-lookup in that order.
# Dropping an Index, or Disabling and Rebuilding it
We can use command DROP to delete our index. In this example we will DROP the index called ix_cars_employee_id on the table Cars.
This deletes the index entirely, and if the index is clustered, will remove any clustering. It cannot be rebuilt without recreating the index, which can be slow and computationally expensive. As an alternative, the index can be disabled:
This allows the table to retain the structure, along with the metadata about the index.
Critically, this retains the index statistics, so that it is possible to easily evaluate the change. If warranted, the index can then later be rebuilt, instead of being recreated completely;
# Partial or Filtered Index
SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows.
Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1).
If your business make use of queries like this:
Partial indexing allows you to limit the index, including only the unfinished orders:
This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index.
# Inserting with a Unique Index
This will fail if an unique index is set on the Email column of Customers. However, alternate behavior can be defined for this case:
# Rebuild index
Over the course of time B-Tree indexes may become fragmented because of updating/deleting/inserting data. In SQLServer terminology we can have internal (index page which is half empty ) and external (logical page order doesn’t correspond physical order). Rebuilding index is very similar to dropping and re-creating it.
We can re-build an index with
By default rebuilding index is offline operation which locks the table and prevents DML against it , but many RDBMS allow online rebuilding. Also, some DB vendors offer alternatives to index rebuilding such as REORGANIZE (SQLServer) or COALESCE / SHRINK SPACE (Oracle).
# SAP ASE: Drop index
This command will drop index in the table. It works on SAP ASE server.
Syntax:
Example:
# Unique Index that Allows NULLS
This schema allows for a 0..1 relationship — people can have zero or one driving licenses and each license can only belong to one person
# Clustered index
When using clustered index, the rows of the table are sorted by the column to which the clustered index is applied. Therefore, there can be only one clustered index on the table because you can’t order the table by two different columns.
Generally, it is best to use clustered index when performing reads on big data tables. The donwside of clustered index is when writing to table and data need to be reorganized (resorted).
An example of creating a clustered index on a table Employees on column Employee_Surname:
# Non clustered index
Nonclustered indexes are stored separately from the table. Each index in this structure contains a pointer to the row in the table which it represents.
This pointers are called a row locators. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
An example of creating a non clustered index on table Employees and column Employee_Surname:
There can be multiple nonclustered indexes on the table. The read operations are generally slower with non clustered indexes than with clustered indexes as you have to go first to index and than to the table. There are no restrictions in write operations however.
# Remarks
Indexes are a way of speeding up read queries by sorting the rows of a table according to a column.
The effect of an index is not noticeable for small databases like the example, but if there are a large number of rows, it can greatly improve performance. Instead of checking every row of the table, the server can do a binary search on the index.
The tradeoff for creating an index is write speed and database size. Storing the index takes space. Also, every time an INSERT is done or the column is updated, the index must be updated. This is not as expensive an operation as scanning the entire table on a SELECT query, but it is still something to keep in mind.