Даже если вы сделали все по инструкции, после того, как ваша система заработала и накопила определенный объем данных, с ними могут возникнуть проблемы, например, медленное выполнение запросов. Это самое распространенное явление, с которым обычно приходится сталкиваться при работе с данными. И это нормально, ведь на этапе проектирования структуры вы не всегда сможете предсказать, как будет развиваться ваша база данных. Предположим, вы запускаете проект MVP, но впоследствии тестовый продукт может не приобрести тот функционал, который планировался. Трудно все предусмотреть с самого начала, поэтому не всегда есть смысл заниматься оптимизацией, сначала нужно увидеть, во что выльется проект. Все оптимизации, которые необходимо сделать на этом этапе, обычно связаны с двумя вещами: во-первых, вы начинаете индексировать все, что вы индексировали до этого, а во-вторых, вы смотрите, как вообще выполняются запросы, возможно, они выполняются неэффективно.

• Как работать с индексами

Учимся читать план выполнения запроса. Разработчики часто плохо понимают, как работают базы данных. Вы должны быть в состоянии понять, как структурировать и проектировать их. Также большинство разработчиков не понимают, что такое план выполнения запроса в СУБД. Но это несложно: достаточно изучить документацию и понять, где тратится время на запросы. Чаще всего помогает «подвешивание» некоторых индексов.

Частичный индекс компактнее полного. По умолчанию индекс, который вы навешиваете на таблицу, индексирует вообще все значения. Но в большинстве случаев вам не нужны NULL-значения, если они есть базе. Так выкиньте их и постройте такой индекс (любая СУБД это позволяет), который NULL-значения не учитывает. Размер индекса в этом случае у вас сильно уменьшится и будет содержать в себе только то, что нужно. То же самое касается ситуации, когда      значения, которые вы индексируете, тоже вам не нужны. В поле могут быть значения от 1 до 100, но вы чаще всего делаете запрос по значениям 1, 2, 3, поэтому можете легко проиндексировать только эти значения. В этом случае индекс у вас будет небольшого размера, и работать он будет быстро. Разницу между полным индексом и частичным можно даже увидеть на глаз в вашей системе мониторинга: время отклика резко упадет.

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

Функциональный индекс поддерживается всеми СУБД. С его помощью вы можете индексировать данные по любой функции. Бывают случаи, когда нужно найти всех людей, чей день рождения 19 июня (независимо от того, в каком году). И эффективен в данном случае функциональный показатель.

Индексы для чисел более эффективны, чем индексы для строк. Все, что является числом, храните как число (номера телефонов, ИНН и так далее), ведь указатель по номерам телефонов и ИНН (его часто приходится вешать и на то, и на другое) будет занимать меньше места, а если что занимает меньше места в СУБД, быстрее находится, читается, оптимально кэшируется и выдается. Все это влияет на производительность. Пока у вас небольшая база данных, это незаметно, но когда она разрастается до гигабайт и количество запросов в секунду превышает несколько десятков, вы это сразу чувствуете.

• Индексы: о чем стоит помнить?

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

Поиск по нескольким индексированным полям — лотерея. Надо помнить, что если в одной таблице есть несколько индексированных полей и вы не по всем из них ищите данные, то результат будет непредсказуемым, как в лотерее. Ситуация может отличаться от сервера к серверу и от машины к машине. Почему? Разные условия, нагрузка, количество памяти, скорость работы с диском. Поэтому индексировать данные надо, изучая план запроса на том самом «боевом» сервере, на котором у вас возникли проблемы.

За индексами нужно ухаживать. Индексы со временем «разбухают», становятся большими, и их нужно постоянно чистить, сжимать, дефрагментировать — только тогда они будут работать эффективно. 

• Критические ошибки: запросы

Нижеприведенные рекомендации — это классика жанра, об этом написано в любом руководстве по тому, как правильно работать с SQL-базами данных. Никогда нельзя запрашивать все разом      (SELECT *), запрашивайте только то, что вы ищите, это, если хотите, закон. Скорость работы СУБД зависит в том числе и от того, сколько вы из нее запрашиваете. Если вы запрашиваете много ненужных вещей, которые потом выкинете, значит вы делаете работу вхолостую. Такой запрос —      зло, особенно в ситуации, когда таблица большая.

За SELECT COUNT (*) и LIMIT/OFFSET надо винить разработчиков таких интерфейсов, где есть списки с постраничным выводом данных. Это, может, выглядит красиво, но даже по опыту использования поисковиков мы знаем, что пользователи дальше второй страницы заходят редко. А если список длинный, то они и первую страницу просматривают не до конца. Нужно давать в интерфейсах возможность делать такие фильтры, чтобы данные можно было получить быстро и именно столько, сколько нужно.

Арифметика в фильтрах, функции и полнотекстовый поиск убивают индексы. Если в условиях поиска вы используете арифметику, функции или полнотекстовый поиск, то чаще всего это убивает любые индексы. Запросы не будут быстрыми: вам нужно будет или упрощать их, или использовать какие-то специфические решения (например, для полнотекстового поиска есть плагин либо функциональный индекс, о котором шла речь ранее). 

• Оптимизация: о чем еще стоит задуматься?

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

Рефакторинг бизнес-логики. Иногда проще сделать два маленьких запроса, чем один большой — он будет медленнее. Но здесь каждый случай индивидуален, нужно уметь экспериментировать. Поэтому чтобы ускорить работу с базами, стоит и настроить ORM, и отрефакторить запросы к ним.

Контроль ссылочной целостности. Речь идет о ситуациях, когда настроены каскадные удаления и обновления. В то время, как вы пытаетесь что-то удалить, СУБД ищет все связанные сущности и пытается понять, стоит их удалять или нет. В этом случае можно не удалять, а помечать как удаленную. Либо пытаться ссылочную целостность как-то порвать, но это может привести к несогласованности, когда вы случайно забудете удалить вместе с основной записью и все связанные с ней. В этом случае в вашей базе будут накапливаться «потерянные» записи. Они, как минимум, будут зря занимать место. Но в перспективе это может привести к неправильной работе бизнес-логики, что гораздо хуже. Так что, если вы очень часто удаляете что-то из БД, то рвать ссылочную целостность надо очень аккуратно, семь раз проверив, что вы удалили действительно всё.