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

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

Поэтому мне сложно оптимизировать написанный ранее запрос - я не представляю то, как он работает. Чтобы представить запрос, мне фактически надо переписать его заново. И explain select не помогает моему воображению, а наоборот, его полностью убивает. Я не верю в технику: «напишите запрос как-нибудь, а потом посмотрите explain и оптимизируйте». Хотя бы потому, что explain, на небольшом количестве данных, может выдать совсем не то же самое, что на большом, к тому же, будет зависеть от конкретных значений в запросе.

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

Представим, что мы хотим найти термины, оканчивающиеся на «тизация» и отсортировать их по алфавиту. Что мы будем делать? Мы также будем листать книгу, но, как только нашли термин, мы не можем его вернуть, мы должны записать его на бумажку (я так себе это и представляю), а потом начать сортировать все термины, и вернуть бумажку с упорядоченными терминами.
Пока все просто, но, что если мы хотим не все термины, а только самый первый, по алфавиту (… ORDER BY termin LIMIT 1)? Нам же все равно придется прочесть всю книгу, но нужно ли нам сортировать все термины, чтобы найти самый первый? Нет. Найти первый по алфавиту гораздо быстрее, чем сортировать всё найденное, в воображении это ясно видно.
А оптимизация здесь при том, что мы, не смотря на то, что выберем один термин, знаем, сколько всего терминов было найдено. И действительно, например, mysql умеет выдавать это количество, отдельно от запроса, что позволяет выдать первый результат поиска, и при этом сказать: сколько всего было найдено, за одно пролистывание книги.

А теперь к интересному - индексам.

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

Скажите, поможет ли индекс найти термины оканчивающиеся на «матизация»? Я такой вопрос на собеседовании задаю. Спрашиваю: есть колонка termin, по которой делается много запросов вида termin like "%матизация", будете ли вы делать по ней индекс? Трудно ответить не представляя себе алфавитный указатель. Если же представить, становится понятно, что искать в нем удобно по словам с определенным началом, например, слова, начинающиеся на «авто», но никак не те, что заканчиваются на «матизация».

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

Ладно, а что же будет, если я ищу информацию, относящуюся к терминам, начинающимся на «авто». Тогда я листаю указатель, пока не найду первый термин на «авто», причем я чувствую, что сделаю это быстро, даже если указатель огромный. Представил, как нашел первый термин, у него справа список страниц (первичных ключей в СУБД), на которых он находится. И начинаю листать до каждой из указанных страниц (не забываю слюнявить палец) и выписывать информацию с нее. Потом перехожу к следующему термину и листаю книгу дальше. Вроде, листать книгу в хаотическом порядке быстро, но если большинство терминов начинается на «авто» и они встречаются на большом количестве страниц, то я устану ее листать, уж лучше бы я читал ее всю последовательно: от начала до конца.
К счастью, разработчики СУБД это тоже поняли, и сделали автоматическую оптимизацию, которая может взять и ошибиться, в самый нужный момент.

Очень важно понимать следующее: в этом примере, если мне требуется информация, отсортированная по термину, то это получится само собой, потому что я просматриваю алфавитный указатель (если я конечно его использую), упорядоченный по термину. Поэтому добавление дополнительного условия ORDER BY termin, не приведет к дополнительным затратам. А вот что будет, если я захочу отсортировать по номеру страницы (первичному ключу)? Представляю такой индекс:

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

В качестве упражнения, попробуйте понять, как будет исполняться запрос ColumnA=10 and ColumnB=15, если обе колонки будут индексированы, и в чем будет большая разница с запросом: ColumnA10 and ColumnB15. А если поймете, тогда explain select подтвердит - правильно или нет Вы поняли. Только не делайте наоборот.

Вся эта тема поистине неисчерпаема, я могу еще писать и писать про то, как воображать себе ту или иную SQL конструкцию: innerouter joins, aggregation,grouping и т.д, но, для начала, хватит. Если Вам понравилось, пишите комменты, и я продолжу свое крючкотворство.

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