Главная цель этого поста - показать, как я учусь разбираться в работе SQL-сервера, а не просто выдавать готовые решения.

Понимание того, как именно работает запрос, позволяет писать оптимальный код. Если же полагаться только на шаблоны, подсказки или догадки вроде «в таком случае сделай так», то иногда всё срабатывает, но обычно это лишь временное решение. Когда в таблицу попадает миллион строк, ваш запрос может упасть ровно во время презентации начальству.

Поэтому мне трудно доработать уже написанный запрос без понимания его внутренней логики. Чтобы представить, как он работает, я фактически вынужден переписать его с нуля. Explain не всегда помогает – он показывает план на небольшом объёме данных, а при большем наборе может отличаться.

Именно поэтому я сначала мысленно моделирую работу запроса и пишу его, затем проверяю (если не ленюсь) через explain, чтобы убедиться, совпадает ли воображаемый сценарий с реальностью. Если нет – это сигнал о том, что нужно пересмотреть свои догадки.

Представим задачу найти термины, оканчивающиеся на «тизация», и отсортировать их по алфавиту. Мы просматриваем «книгу» терминов, записывая найденные слова, а потом сортируем список.

Если же нам нужен только первый термин в алфавитном порядке (ORDER BY termin LIMIT 1), то читать всю книгу всё равно нужно, но сортировать весь результат нет необходимости. Это гораздо быстрее – как найти первую букву в словаре.

Оптимизация здесь заключается не только в выборе одного термина, но и в том, что мы одновременно узнаём общее количество найденных терминов. MySQL умеет выдавать это число параллельно с первым результатом без дополнительного прохода.

Индексы

Я представляю индексы как алфавитный указатель в книге. В нём упорядочены термины, а рядом находятся номера страниц (или строк), где они встречаются.

Скажите, поможет ли такой индекс найти слова, оканчивающиеся на «матизация»? Я задаю этот вопрос на собеседованиях. Если представить указатель, становится ясно, что поиск по началу слова эффективен, но искать по окончанию почти невозможно.

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

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

Разработчики СУБД понимают это и включают автоматическую оптимизацию, которая иногда может ошибаться в критический момент.

Важно понять следующее: если запрос требует сортировку по термину, то он уже идёт через алфавитный указатель, поэтому дополнительное ORDER BY termin не добавляет затрат. А если нужно упорядочить по номеру страницы, ситуация меняется.

Представьте такой индекс:

При условии termin = 'Автоматизация' результаты уже будут отсортированы по страницам. Если же несколько терминов удовлетворяют условию, они попадают в «кусочки», и их придётся сортировать отдельно.

Упражнение

Попробуйте понять, как будет исполняться запрос ColumnA = 10 AND ColumnB = 15, если обе колонки индексированы, и в чем разница с запросом ColumnA = 10 AND ColumnB = 15 (без индексов). Если вы это уловите, explain подтвердит ваш вывод – правильно ли вы понимаете план.

Эта тема почти бесконечна. Я могу дальше писать о том, как визуализировать inner/outer joins, агрегаты, группировку и т.д., но пока хватит. Если вам понравилось, пишите комментарии – я продолжу делиться мыслями.

Надеюсь, вы разобрались в моём подходе: представляйте ход запроса, сразу напишите его правильно, не нужно потом оптимизировать. Вы будете чувствовать, как запрос будет работать при росте данных, сможете отсеивать «killer queries», которые с увеличением объёма убивают систему, и заменять их более надёжными решениями или даже переходом на NoSQL.