Эта статья - не просто набор сухих инструкций «что делать», а скорее личное признание в том, как я учусь чувствовать внутреннюю логику SQL-сервера. И вот почему это так важно: именно глубинное понимание механизмов работы позволяет писать действительно оптимальные запросы. Опираться на чужие советы или шаблонные решения вроде «в такой ситуации делай так» - крайне рискованно. Да, оно может сработать! Но только пока данные не вырастут до миллиона записей, когда всё внезапно встанет колом. И, как назло, это произойдет прямо во время важной презентации руководству. Страшно даже представить!
Мне сложно оптимизировать чужие или старые запросы, потому что я не вижу под капотом того, как они функционируют. Чтобы понять их логику, мне приходится фактически переписывать код с нуля. Команда explain select, к сожалению, часто убивает моё воображение вместо того, чтобы его питать. Я категорически не верю в метод «напиши как попало, потом посмотри explain и почини». Причина проста: на малом объёме данных план выполнения может выглядеть идеально, а на большом - превратиться в катастрофу. К тому же, поведение сильно зависит от конкретных значений в запросе.
Мой подход такой: сначала я ярко представляю себе, как должен работать запрос, и лишь потом пишу код. Если не лень, затем сверяюсь с explain. И если реальность расходится с моим воображением - это лучший случай! Значит, я узнал что-то новое и скорректировал своё понимание.
Возьмём простой пример. Допустим, нам нужно найти термины, оканчивающиеся на «тизация», и отсортировать их по алфавиту. Я мысленно листаю книгу: находим термин - записываем на бумажку (да, я так это себе представляю), затем сортируем список и возвращаем результат.
А теперь усложним задачу. Что если нам нужен только самый первый термин по алфавиту? (… ORDER BY termin LIMIT 1). Нам всё равно придётся просмотреть всю книгу, но нужно ли нам сортировать все найденные термины? Нет! Найти первый по порядку в разы быстрее, чем выстраивать весь список. В воображении это видится предельно ясно.
Здесь кроется нюанс: даже выбирая один термин, мы можем знать общее количество совпадений. Например, MySQL умеет возвращать этот счётчик отдельно от основного запроса. Это позволяет показать первый результат и сразу сообщить пользователю, сколько всего найдено за одно «пролистывание».
А теперь перейдём к самому интересному - индексам.
Я представляю индексы как алфавитный указатель в толстом справочнике: отсортированные термины, рядом с которыми стоят номера страниц (идентификаторы записей), причём сами номера тоже упорядочены по возрастанию.
Скажите, поможет ли такой индекс найти слова, оканчивающиеся на «матизация»? Я часто задаю этот вопрос на собеседованиях. Спрашиваю: есть колонка termin, по которой летит много запросов вида termin like "%матизация". Будете ли вы создавать индекс? Ответить сложно, если не представить себе этот самый указатель. Но как только вы его «видите», становится очевидно: искать удобно слова с определённым началом (например, начинающиеся на «авто»), но никак не те, что заканчиваются конкретным суффиксом.
Конечно, можно возразить: даже без учёта сортировки поиск по указателю быстрее, чем чтение всей огромной книги. Отличный вопрос! Он возникает благодаря нашему воображению, и на него нельзя ответить однозначно. Но если вы задали себе этот вопрос - значит, вы уже на верном пути к разработке оптимальной схемы базы данных.
Предположим, мы ищем информацию по терминам, начинающимся на «авто». Мы быстро находим первое такое слово в указателе. У него справа стоит список страниц (первичных ключей). Начинаем перелистывать книгу к каждой из них (не забывая слюнявить палец!) и выписывать данные. Затем переходим к следующему термину.
Листать книгу хаотично быстро, пока не станет иначе. Но если терминов на «авто» очень много и они встречаются на десятках страниц, мы просто устанем перелистывать листы. В этом случае лучше было бы читать книгу всю последовательно: от начала до конца.
К счастью, разработчики СУБД это понимают. Они внедрили автоматическую оптимизацию, которая иногда может ошибиться, выбрав индекс вместо полного сканирования (или наоборот), в самый неподходящий момент.
Очень важно понимать следующий момент: если мне нужна информация, отсортированная по термину, то это произойдёт само собой. Я просматриваю указатель, который уже упорядочен по этому полю. Поэтому добавление условия ORDER BY termin не потребует дополнительных вычислительных затрат.
А вот что будет, если захотеть отсортировать результаты по номеру страницы (первичному ключу)? Представим такой индекс:
- Если выбираем один конкретный термин (
termin='Автоматизация'), то результаты уже будут упорядочены по страницам. - Но если условию удовлетворяет несколько терминов, результаты будут сгруппированы «кусками». Нам придётся собрать их все и отсортировать вручную на нашей воображаемой бумажке.
В качестве упражнения попробуйте понять логику выполнения запросов ColumnA=10 AND ColumnB=15, если обе колонки индексированы. В чём разница с запросом, где порядок условий изменён? Если поймёте ответ - подтвердите его через explain select. Только не делайте наоборот: сначала проверяйте головой!
Эта тема неисчерпаема. Я мог бы продолжать писать про воображение работы INNER/OUTER JOINs, агрегаций, группировок и многого другого, но на этом пока хватит.
Если вам откликается этот подход - оставляйте комментарии! И я продолжу своё «крючкотворство».
Надеюсь, вы уловили суть: представляя себе ход запроса, вы сразу пишете его правильно. Не нужно ничего оптимизировать постфактум. Более того, вы начнёте интуитивно чувствовать производительность при росте данных. Вы сможете вовремя отсечь те «супероптимальные» killer queries, которые, несмотря на свою красоту в плане выполнения, убьют вашу систему под нагрузкой, заменив их принципиально другими подходами или даже отказавшись от реляционной модели в пользу NoSQL. Для понимания работы NoSQL-баз такой подход тоже прекрасно работает!