Индексы и скорость запросов: как ускорить MySQL и PostgreSQL

Индекс — это структура данных, которая помогает базе находить нужные строки без перебора всей таблицы. И в MySQL, и в PostgreSQL индексы работают похоже по смыслу, но по-разному устроены изнутри, поддерживают разные типы данных и требуют разного подхода к настройке — от этого и зависит, насколько быстро будут выполняться запросы.

Зачем вообще нужен индекс, если данные уже лежат в таблице?

Представьте телефонную книгу без алфавитного порядка — чтобы найти нужного человека, пришлось бы читать её от начала до конца. Индекс — это как раз алфавитный порядок: специальная структура, которая заранее «сортирует» значения одного или нескольких столбцов и хранит ссылки на строки таблицы. Когда база видит запрос с условием WHERE или JOIN по индексированному полю, она обращается не ко всей таблице, а сразу к нужному участку индекса.

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

Чем отличаются индексы в MySQL и PostgreSQL по своей структуре?

По умолчанию обе СУБД используют B-дерево (B-tree) — сбалансированную древовидную структуру, которая хорошо подходит для сравнений «равно», «больше», «меньше» и для сортировки. Но дальше начинаются различия.

PostgreSQL поддерживает больше типов индексов «из коробки»: помимо B-tree есть GiST и SP-GiST для геометрических данных и полнотекстового поиска, GIN — отличный вариант для индексации JSON-полей и массивов, BRIN — компактный индекс для очень больших таблиц с упорядоченными по времени данными. MySQL исторически делает ставку в основном на B-tree и хеш-индексы (в движке Memory), а полнотекстовый поиск и пространственные данные поддерживает более ограниченно, хотя в актуальных версиях возможности постепенно расширяются.

• MySQL — простая и предсказуемая модель, минимум настроек, подходит для типовых OLTP-нагрузок.
• PostgreSQL — больше гибкости для нестандартных задач: поиск по тексту, работа с географией, сложная аналитика.

Как понять, что запросу не хватает индекса?

Главный инструмент — план выполнения запроса. В MySQL это команда EXPLAIN, в PostgreSQL — EXPLAIN и EXPLAIN ANALYZE (последняя ещё и реально выполняет запрос, показывая фактическое время). План показывает, использует ли СУБД индекс или делает полное сканирование таблицы, сколько строк обрабатывает на каждом шаге и где узкое место.

Тревожный сигнал — операция вроде «Seq Scan» в PostgreSQL или «Full table scan» в MySQL на большой таблице там, где ожидался быстрый точечный поиск. Это почти всегда значит, что нужного индекса нет или он не используется по какой-то причине — например, из-за несовпадения типов данных в условии сравнения.

Правда ли, что чем больше индексов — тем быстрее работает база?

Это одно из самых частых заблуждений. Индексы ускоряют чтение, но замедляют запись: при каждой вставке, обновлении или удалении строки СУБД вынуждена обновлять все связанные с таблицей индексы, а не только сами данные. На таблице с десятком индексов операция INSERT может выполняться заметно медленнее, чем на таблице с одним-двумя.

Кроме того, лишние индексы занимают место на диске и в памяти, а оптимизатор запросов иногда тратит время на то, чтобы выбрать между несколькими похожими индексами. Разумный подход — индексировать те столбцы, по которым реально идёт поиск, фильтрация или соединение таблиц, и периодически проверять, какие индексы вообще используются в реальных запросах.

Что такое составной индекс и когда он нужен?

Составной (многоколоночный) индекс строится сразу по нескольким полям, например по паре «город» и «дата регистрации». Он эффективен, когда запросы часто фильтруют данные именно по этой комбинации условий вместе. Важный нюанс — порядок столбцов в индексе имеет значение: индекс по паре (город, дата) хорошо ускорит поиск по городу и по городу с датой, но почти не поможет запросу, где фильтруется только дата.

Это правило работает одинаково и в MySQL, и в PostgreSQL, потому что оно вытекает из самой природы B-дерева: значения сортируются по первому столбцу, затем внутри него — по второму, и так далее по цепочке.

Как индексы связаны с уникальностью и ссылочной целостностью?

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

Интересный исторический факт: сама идея B-дерева как основы индексов в реляционных базах данных появилась ещё в начале 1970-х годов и с тех пор остаётся фундаментом индексирования почти во всех популярных СУБД — настолько удачной оказалась эта структура для баланса между скоростью чтения и записи.

Что делать, если своих знаний не хватает для настройки индексов?

Оптимизация индексов — тонкая работа, которая требует понимания реальных запросов конкретного проекта, объёма данных и характера нагрузки: где больше чтения, а где записи. Ошибиться легко в обе стороны — оставить таблицу вообще без индексов или, наоборот, захламить её лишними.

Если самостоятельно разбираться в планах запросов и подбирать индексы некогда, разумно опереться на готовую инфраструктуру и поддержку специалистов. Например, сервис «Клауд АйСи» предоставляет управляемые базы данных на MySQL и PostgreSQL с мониторингом производительности и помощью в настройке — это снимает часть рутины по анализу медленных запросов и позволяет сосредоточиться на самом проекте, а не на администрировании СУБД.

С чего начать, если проект уже работает медленно?

Первый шаг — не гадать, а измерять. Нужно собрать список самых медленных и самых частых запросов (в обеих СУБД есть журналы медленных запросов), прогнать их через EXPLAIN и посмотреть, где база делает полное сканирование вместо использования индекса. Дальше — точечно добавлять индексы под конкретные условия WHERE, JOIN и сортировки, а не индексировать таблицу «на всякий случай».

Полезно также периодически проверять статистику использования уже существующих индексов — и в MySQL, и в PostgreSQL есть системные представления, которые показывают, какие индексы реально задействуются запросами, а какие просто занимают место и тормозят запись. Удаление неиспользуемых индексов часто даёт заметный прирост производительности без единой строчки нового кода.