Школа backend. PostgreSQL. Лекция 5: Индексы | Часть 1

• Приветствие и ожидание участников.  

• Вопрос о том, слышат ли участники, когда спикер молчит.  

• Начало презентации.  

• Пятая лекция из серии "Пост для разработчика".  

• Индексы как подсказки для баз данных.  

• Пример с библиотекой и шкафчиками с карточками.  

• Индексы помогают базам данных быстро находить нужные данные.  

• Пример с таблицей и автоинкрементным полем.  

• Проблема полного перебора таблицы без индекса.  

• Команда CREATE INDEX и её синтаксис.  

• Индексы применяются только к одной таблице.  

• Возможность присвоения имени индексу.  

• База автоматически использует индексы для оптимизации запросов.  

• Пример с запросом и индексом.  

• Использование индекса для более эффективного доступа к данным.  

• Индекс-он-листик как более эффективный метод доступа.  

• Добавление полей в индекс для улучшения производительности.  

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

• Влияние количества ключевых полей на размер индекса.  

• Пример с тестовыми данными и индексами.  

• Важность минимизации количества индексов для экономии места на носителях.  

• Можно индексировать по любому выражению или функции, но простое выражение нужно заключить в скобки.  

• Пример: выражение "релка равно с" в поле индекса.  

• Оператор равенства возвращает булево значение, что может быть проблемой.  

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

• Пример: индекс для необработанных заказов.  

• Условия индекса позволяют быстро выбирать записи, удовлетворяющие условию.  

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

• Любое выражение можно использовать в индексе, но оно должно быть мутабельным.  

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

• Нестабильные функции, такие как NOW(), не могут использоваться в индексах.  

• Пример: выражение "поле минус функция больше либо равно константа" не может быть проиндексировано.  

• Такие выражения нужно переписывать, чтобы использовать стабильные функции.  

• Конкурентные индексы позволяют избежать блокировок при создании индекса.  

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

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

• Уникальные индексы проверяют значения на уникальность.  

• Пример: уникальный индекс по полю идентификатора.  

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

• Системная вьюшка pg_stat_progress_index показывает процесс создания индексов.  

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

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

• Много индексов, особенно ненужных или дублирующихся, плохо.  

• В PostgreSQL каждый индекс содержит абсолютную адресацию к записи.  

• Это приводит к увеличению количества записей при записи в таблицу.  

• Uber перешел на PostgreSQL, но позже вернулся к MySQL.  

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

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

• Для удаления неиспользуемых индексов можно использовать view pg_stat_user_indexes.  

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

• В продакшне лучше использовать pg_drop_index для предотвращения блокировок.  

• Индексы могут распухать из-за частых изменений данных.  

• Для очистки индексов можно использовать pg_compact_index.  

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

• PostgreSQL позволяет создавать индексы разных типов для решения различных задач.  

• Индексы строятся на логике разбиения данных на кучки и детализации до конкретных записей.  

• Встроенные типы индексов включают B-Tree и другие методы.  

• B-Tree индексы сбалансированы и позволяют решать отношения линейного порядка.  

• Они подходят для числовых типов данных и строк, упорядоченных по алфавиту.  

• B-Tree индексы включают префикс-индексы для полей в начале индекса.  

• B-Tree индексы позволяют решать условия, где все значения полей, кроме последнего, константы.  

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

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

• Префиксные индексы позволяют сортировать и отбирать данные по полям.  

• Если условия для префиксных полей не корректны, индекс не будет использоваться.  

• Неправильное использование префиксных полей может привести к полному перебору данных.  

• Неправильное использование префиксных полей может привести к сканированию таблицы.  

• Важно учитывать порядок полей в индексе и запросе.  

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

• Приведение типов данных в запросах может ухудшить производительность.  

• Лучше приводить константы к типу поля, а не наоборот.  

• Неправильное приведение типов может привести к неиндексированному поиску.  

• Поля с малой кардинальностью лучше ставить ближе к концу индекса.  

• Поля с высокой кардинальностью, такие как даты, лучше ставить в конец индекса.  

• Неправильное расположение полей может увеличить объем индекса.  

• Условия для массивов в индексах не работают корректно.  

• Массивы не индексируются с помощью B-деревьев.  

• Неправильное использование массивов может привести к неэффективному использованию индекса.  

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

• Важно исключать записи с новыми полями из индекса.  

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

• Битмап-энд: сложение двух битовых карт для получения записей.  

• Составной индекс: улучшение производительности за счет использования двух полей.  

• Модель использования: разработчики часто не задумываются о модели выборок.  

• Составной индекс улучшает производительность на 20%.  

• База данных не догадается сама, нужно подумать о структуре индекса.  

• Пример с выбором первого значения по атрибуту.  

• Сортировка по полю в индексе улучшает производительность.  

• Использование метода сортировки топ тен для уменьшения объема данных.  

• Пример с поиском по строкам и использованием оператора лайк.  

• Префиксный лайк-поиск позволяет индексировать строки.  

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

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

• Классы операторов помогают преобразовывать данные при индексации.  

• Пример с использованием класса операторов для префиксного лайк-поиска.  

• Преимущества использования классов операторов для улучшения производительности.  

• Включение де-дупликации в B-Tree индексе позволяет сжимать дерево индекса.  

• Пример с записями по документам и заказам.  

• Де-дупликация уменьшает объем индекса на 10-15% без значительного замедления.  

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

• Нестабильные функции могут давать разные результаты на одном и том же наборе аргументов.  

• База данных знает, какие функции стабильные, а какие нет, и не позволит создать индекс для нестабильных функций.  

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

• Если вопросов нет, лекция продолжается с обсуждения 54-го слоя.  

• Преподаватель завершает лекцию и благодарит слушателей, анонсируя следующую лекцию через неделю.