Цель этого поста рассказать про то, как я учусь понимать работу sql сервера, а не дать набор готовых инструкций.
Потому что именно через понимание того, как же это работает, можно написать оптимальный запрос. Когда же мы полагаемся на инструкции, советы, спец. средства или даже догадки, которые говорят нам: «в таком-то случае сделай так», но без понимания «почему и как это будет работать?» — это страшно, потому что это может сработать, да, сработать! Но, сработать только поначалу, а потом, когда в базу добавится миллион записей, все встанет колом, и, как на зло, во время презентации начальству.
Удивительно, но понимать такю сложную штуку, как SQL сервер, мне помогает… воображение.
Я представляю себе запрос, вернее то, как он будет работать. Как?
Как поиск по книге, ну или нескольким книгам, если таблиц несколько. Еще я представляю себе алфавитные указатели (индексы) и листочки с бумагой (для буферов и временных таблиц).
Поэтому мне сложно оптимизировать написанный ранее запрос — я не представляю то, как он работает. Чтобы представить запрос, мне фактически надо переписать его заново. И explain select не помогает моему воображению, а наоборот, его полностью убивает. Я не верю в технику: «напишите запрос как-нибудь, а потом посмотрите explain и оптимизируйте». Хотя бы потому, что explain, на небольшом количестве данных, может выдать совсем не то же самое, что на большом, к тому же, будет зависеть от конкретных значений в запросе.
Поэтому я сначала представляю то, как запрос должен работать и пишу его, а потом сверяю, если не лень, с explain, и проверяю, сходится ли мое воображение с реальностью или нет. И если не сходится — это самое лучшее, тогда я узнаю что-то новое, и корректирую свое понимание.
Теперь к практике.
Представим, что мы хотим найти места, где встречается определенный термин в книге, и проанализировать его контекст — слова, которые перед термином и после него.
Если в книге нет алфавитного указателя для терминов, то что я буду делать? Правильно, читать всю книгу, страницу за страницей. Сложность такой задачи легко оценить в воображении — она пропорциональна размеру книги.
Но что я буду делать, когда я встретил термин? Я могу начать сразу анализировать его «контекст», т.е. в терминах СУБД — вернуть ряд клиенту. Хорошо, и тут должно возникнуть понимание, что клиент, получив первый ряд, удовлетворяющий условию поиска, не знает: сколько всего результатов еще будет, а иногда так хочется знать!
А можно ли вернуться к предыдущему термину? Нет! Попробуйте ка читать книгу задом наперед — не получится, в лучшем случае, вы сможете сделать запрос заново.
Эврика, ведь есть другой способ, можно читать всю книгу целиком, выписывать найденные термины вместе с контекстом на бумажку, а потом всю эту бумажку отдать клиенту, пусть подавится. Тогда мы знаем количество результатов, и можем перебирать их вперед и назад, но тогда мы ждем, пока прочитаем всю книгу целиком (а может одного-двух результатов бы хватило?), и переводим бумагу (память).
Отлично, это заставляет задуматься, а каков размер бумажки (если он ограничен, то вместит ли он в себя все результаты, и что будет если не вместит), а не подавится ли клиент бумажкой такого размера?
Это дает понимание, которое спасает от ошибок и позволяет выбрать оптимальный вариант для конкретной цели, а главное узнать, что этот вариант есть.
А знаете ли вы, как по-умолчанию это будет работать в Вашем случае?
Следующий пример.
Представим, что мы хотим найти термины, оканчивающиеся на «тизация» и отсортировать их по алфавиту. Что мы будем делать? Мы также будем листать книгу, но, как только нашли термин, мы не можем его вернуть, мы должны записать его на бумажку (я так себе это и представляю), а потом начать сортировать все термины, и вернуть бумажку с упорядоченными терминами.
Пока все просто, но, что если мы хотим не все термины, а только самый первый, по алфавиту (… ORDER BY termin LIMIT 1)? Нам же все равно придется прочесть всю книгу, но нужно ли нам сортировать все термины, чтобы найти самый первый? Нет. Найти первый по алфавиту гораздо быстрее, чем сортировать всё найденное, в воображении это ясно видно.
А оптимизация здесь при том, что мы, не смотря на то, что выберем один термин, знаем, сколько всего терминов было найдено. И действительно, например, mysql умеет выдавать это количество, отдельно от запроса, что позволяет выдать первый результат поиска, и при этом сказать: сколько всего было найдено, за одно пролистывание книги.
А теперь к интересному — индексам.
Индексы я представляю, как алфавитный указатель в книге: сортированные по алфавиту термины, рядом с которыми номера страниц (записей), где этот термин встречается, кстати номера тоже упорядочены по возрастанию.
Скажите, поможет ли индекс найти термины оканчивающиеся на «матизация»? Я такой вопрос на собеседовании задаю. Спрашиваю: есть колонка termin, по которой делается много запросов вида termin like "%матизация", будете ли вы делать по ней индекс? Трудно ответить не представляя себе алфавитный указатель. Если же представить, становится понятно, что искать в нем удобно по словам с определенным началом, например, слова, начинающиеся на «авто», но никак не те, что заканчиваются на «матизация».
Однако, можно возразить, что искать по алфавитному указателю, даже без учета того, что он отсортирован, быстрее, чем читать всю огромную книгу целиком. Отличный вопрос, возникший благодаря нашему воображению, потому что на него нельзя одназначно ответить, но если Вы его себе задали, то Вы на пути к разработке оптимальной схемы БД.
Ладно, а что же будет, если я ищу информацию, относящуюся к терминам, начинающимся на «авто». Тогда я листаю указатель, пока не найду первый термин на «авто», причем я чувствую, что сделаю это быстро, даже если указатель огромный. Представил, как нашел первый термин, у него справа список страниц (первичных ключей в СУБД), на которых он находится. И начинаю листать до каждой из указанных страниц (не забываю слюнявить палец) и выписывать информацию с нее. Потом перехожу к следующему термину и листаю книгу дальше. Вроде, листать книгу в хаотическом порядке быстро, но если большинство терминов начинается на «авто» и они встречаются на большом количестве страниц, то я устану ее листать, уж лучше бы я читал ее всю последовательно: от начала до конца.
К счастью, разработчики СУБД это тоже поняли, и сделали автоматическую оптимизацию, которая может взять и ошибиться, в самый нужный момент.
Очень важно понимать следующее: в этом примере, если мне требуется информация, отсортированная по термину, то это получится само собой, потому что я просматриваю алфавитный указатель (если я конечно его использую), упорядоченный по термину. Поэтому добавление дополнительного условия ORDER BY termin, не приведет к дополнительным затратам. А вот что будет, если я захочу отсортировать по номеру страницы (первичному ключу)? Представляю такой индекс:
Автоматизация: 10,30,117,486
Авторизация: 15,24,97,248
Если выбирать один термин, по условию termin=«Автоматизация», тогда результаты будут уже выбраны по порядку страниц, а если условию удовлетворяет несколько терминов, то результаты будут упорядочены «кусками», и нам придется их на бумажке сортировать.
В качестве упражнения, попробуйте понять, как будет исполняться запрос ColumnA=10 and ColumnB=15, если обе колонки будут индексированы, и в чем будет большая разница с запросом: ColumnA<10 and ColumnB>15. А если поймете, тогда explain select подтвердит — правильно или нет Вы поняли. Только не делайте наоборот.
Вся эта тема поистине неисчерпаема, я могу еще писать и писать про то, как воображать себе ту или иную SQL конструкцию: inner&outer joins, aggregation,&grouping и т.д, но, для начала, хватит. Если Вам понравилось, пишите комменты, и я продолжу свое крючкотворство.
Надеюсь, Вы поняли мой подход, представляйте себе ход запроса, тогда Вы сразу напишете его правильно, и не придется ничего оптимизировать. Более того, Вы будете чувствовать, сколько этот запрос будет выполняться при росте количества данных, сможете отсекать супероптимальные killer queries, которые с ростом объема данных убъют Вашу систему, не смотря на свою «оптимальность», заменяя такие killer queries принципиально другими подходами или NoSQL СУБД. Для понимания которой, кстати, такой подход тоже работает!
Воображение Вам в помощь.
Автор: susliks
