Индексы.

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

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

CREATE INDEX idx_email ON Users (email);

Практические примеры:

-- Создание индекса на один столбец
CREATE INDEX idx_username ON users (username);

-- Создание индекса на несколько столбцов
CREATE INDEX idx_name_email ON users (last_name, first_name, email);

-- Создание уникального индекса
CREATE UNIQUE INDEX idx_unique_email ON users (email);

-- Создание индекса с указанием типа
CREATE INDEX idx_created_at ON orders (created_at DESC);

-- Создание частичного индекса (PostgreSQL)
CREATE INDEX idx_active_users ON users (username) WHERE status = 'active';

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

SHOW INDEX FROM Users;

Практические примеры:

-- Показать все индексы таблицы (MySQL)
SHOW INDEX FROM users;

-- Показать индексы с полным именем
SHOW INDEX FROM database_name.users;

-- Показать индексы (PostgreSQL)
SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'users';

-- Показать индексы (SQL Server)
SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('users');

-- Показать индексы (Oracle)
SELECT index_name, table_name FROM user_indexes WHERE table_name = 'USERS';

Удаление индекса.

DROP INDEX idx_email ON Users;

Практические примеры:

-- Удаление индекса по имени
DROP INDEX idx_username ON users;

-- Удаление индекса с проверкой существования (MySQL)
DROP INDEX IF EXISTS idx_temp_index ON products;

-- Удаление индекса с полным именем
DROP INDEX idx_name_email ON database_name.users;

-- Удаление индекса (PostgreSQL)
DROP INDEX idx_username;

-- Удаление индекса (SQL Server)
DROP INDEX idx_username ON users;

Уникальные (UNIQUE) индексы.

Уникальный индекс выполняет две функции:

• Обеспечивает все преимущества стандартного индекса.
• Предотвращает дублирование значений в индексируемом столбце.

CREATE UNIQUE INDEX idx_email ON Users (email);

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

Многостолбцовые индексы.

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

CREATE INDEX idx_full_name
ON Student (last_name, first_name);

Практические примеры:

-- Составной индекс для поиска по имени и фамилии
CREATE INDEX idx_full_name ON users (last_name, first_name);

-- Составной индекс для заказов
CREATE INDEX idx_order_user_date ON orders (user_id, order_date DESC);

-- Составной индекс для продуктов
CREATE INDEX idx_category_price ON products (category, price ASC);

-- Составной индекс для поиска по нескольким полям
CREATE INDEX idx_user_status_date ON users (status, created_at, last_login);

-- Составной индекс с включением дополнительных полей (SQL Server)
CREATE INDEX idx_user_name ON users (last_name, first_name)
INCLUDE (email, phone);

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

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

Обратная сторона индексов.

Если индексы столь эффективны, возникает вопрос: почему бы просто не индексировать всё подряд?

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

Следовательно, чем больше индексов, тем больше должна работать СУБД, чтобы поддерживать все объекты схемы в актуальном состоянии — что приводит к замедлению работы.

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

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

Сниппеты запросов в SQL.

Представляют собой примеры, шаблоны или краткие фрагменты кода, которые показывают, как выполнять определенные операции с базами данных с использованием языка SQL (Structured Query Language). Они могут включать в себя команды для выборки данных, обновления, вставки, удаления и другие операции, которые могут пригодиться при работе с реляционными базами данных.