SQL
Индексы

Вступление

замечания

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

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

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

SELECT * FROM Cars WHERE EmployeeId = 1

Индекс может содержать более 1 столбца, как в следующем;

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

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

Например, в следующем случае будет использоваться второй индекс;

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Однако, если порядок отличается, индекс не имеет таких же преимуществ, как в следующем;

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

Индекс не так полезен, потому что база данных должна извлекать весь индекс по всем значениям EmployeeId и CarID, чтобы определить, какие элементы имеют OwnerId = 17 .

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

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

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

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

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

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

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

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

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

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

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

Вставка с уникальным индексом

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

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

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: индекс падения

Эта команда уменьшит индекс в таблице. Он работает на сервере SAP ASE .

Синтаксис:

DROP INDEX [table name].[index name]

Пример:

DROP INDEX Cars.index_1

Сортированный указатель

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

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Когда вы выполняете запрос

SELECT * FROM scoreboard ORDER BY score DESC;

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

Удаление индекса, или отключение и восстановление его

DROP INDEX ix_cars_employee_id ON Cars;  

Мы можем использовать команду DROP для удаления нашего индекса. В этом примере мы будем DROP индексом ix_cars_employee_id на столе Cars .

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

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

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

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

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Уникальный индекс, который позволяет NULLS

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Эта схема допускает отношения 0..1 - люди могут иметь ноль или одну водительскую лицензию, и каждая лицензия может принадлежать только одному человеку

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

С течением времени индексы B-Tree могут стать фрагментированными из-за обновления / удаления / вставки данных. В терминологии SQLServer у нас может быть внутренняя (индексная страница, наполовину пустая) и внешняя (логический порядок страниц не соответствует физическому порядку). Индекс перестройки очень похож на падение и воссоздание.

Мы можем перестроить индекс с помощью

ALTER INDEX index_name REBUILD; 

По умолчанию индекс перестройки - это автономная операция, которая блокирует таблицу и предотвращает DML, но многие RDBMS позволяют осуществлять онлайн-перестройку. Кроме того, некоторые поставщики БД предлагают альтернативы перестройке индекса, такие как REORGANIZE (SQLServer) или COALESCE / SHRINK SPACE (Oracle).

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

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

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

Пример создания кластерного индекса в таблице Сотрудники в столбце Employee_Surname:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Некомпилированный индекс

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

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

Пример создания некластеризованного индекса в таблице Сотрудники и столбец Employee_Surname:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

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

Частичный или отфильтрованный указатель

SQL Server и SQLite позволяют создавать индексы, которые содержат не только подмножество столбцов, но и подмножество строк.

Рассмотрим постоянное возрастающее количество заказов с order_state_id равным завершенному (2), и стабильное количество заказов с order_state_id equal (1).

Если ваш бизнес использует такие запросы:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

Частичная индексация позволяет вам ограничить индекс, включая только незавершенные заказы:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Этот индекс будет меньше, чем нефильтрованный индекс, что экономит место и снижает стоимость обновления индекса.