Уроки модуля (1/4)
Индексы и B-деревья
Как база данных может найти одну запись среди миллионов строк за доли секунды?
По умолчанию, когда мы запрашиваем SELECT * FROM customers WHERE city = 'Milano', Postgres должен выполнить Последовательное сканирование: он физически считывает всю таблицу с диска построчно, сравнивая каждый город с «Миланом». Когда таблица содержит миллионы записей, этот процесс снижает производительность.
Основным структурным ответом на эти узкие места являются Индексы.
Индексы (B-деревья)
Индексы работают точно так же, как аналитический указатель в конце большой книги. Вместо того, чтобы читать всю книгу в поисках места, где говорится о «Милане», мы просматриваем последние страницы, где слово «Милан» перенаправляет нас на [p. 45, 114, 230].
Базы данных за кулисами создают эту отдельную, очень компактную структуру данных (часто «B-дерево»), упорядоченную по рассматриваемому столбцу или столбцам, содержащую прямые указатели на физическое хранилище совпадающих строк.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Эта единственная операция запускает механизм Postgres, который извлечет и проиндексирует все значения city в фоновом режиме. После завершения каждый последующий SELECT ... WHERE city = '...' будет летать!
Первичные ключи и уникальность
Знаете ли вы, что правила первичного ключа (PRIMARY KEY) и UNIQUE тайно используют индексы? Поскольку БД должна гарантировать, что адрес электронной почты или идентификатор никогда не дублируются при вставке или изменении строк, PostgreSQL автоматически создает поверх них неявный индекс B-дерева: таким образом проверка уникальности занимает микросекунды.
Вот почему соединения по полю ID невероятно производительны.
Удаление индекса
Если вы понимаете, что тяжелые запросы изменились и к индексу никогда не обращаются, разумно уничтожить его и вернуть жизненно важное пространство:
DROP INDEX idx_customers_city;Ваша очередь
В нашей электронной коммерции мы замечаем через информационные панели, что очень часто фильтруем клиентов по национальности (столбец «Страна» в разделе «Клиенты»). \nСоздайте новый базовый индекс для столбца страны и назовите его «idx_customers_country».
Показать подсказку
Используйте CREATE INDEX имя_индекса ON имя_таблицы(имя_столбца);
Решение доступно после 3 попыток
Изучение вторичных уникальных индексов
Индекс B-дерева также можно использовать для имитации ограничения UNIQUE во время создания и ускорения поиска, одновременно делая логическое намерение явным и обязательным.\n\nСоздайте новый индекс, в котором вы объявите ключевое слово UNIQUE. Определите его так, чтобы он блокировал любые нежелательные повторные регистрации от других пользователей, обеспечивая уникальность столбца «электронная почта» для «клиентов», и назовите его «idx_unique_email_customer».
Показать подсказку
Напишите CREATE UNIQUE INDEX index_name ON table (столбец);
Решение доступно после 3 попыток