Оптимизация структуры базы данных: исправляем ошибки

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

• Как работать с индексами бд?
• Индексы в бд: о чем еще стоит помнить
• Критические ошибки: запросы
• Оптимизация индексов: о чем еще стоит задуматься?

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

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

Учимся читать план выполнения запроса.

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

Частичный индекс компактнее полного.

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

Кластеризованные индексы идеальны для дат.

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

Функциональный индекс поддерживают все СУБД.

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

Индексы по числам эффективнее, чем по строкам.

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

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

Не все можно эффективно индексировать.

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

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

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

Много индексов на таблице - признак перегруженной структуры.

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

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

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

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

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

Настройка ORM.

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

Рефакторинг бизнес-логики.

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

Контроль ссылочной целостности.

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

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