Учимся оптимизировать SQL запросы. Книга оптимизация запросов sql
Оптимизация SQL-запросов
- 1. Оптимизация SQL-запросов Мастер-класс в компании «ТиЭс Софт»
Как я могу научиться оптимизировать SQL-запросы Безопасный SQL
Я знаю, как писать SQL запросы и получать результаты, которые мне нужны. Но иногда мои запросы медленны, и я не совсем понимаю, почему.
Каковы хорошие ресурсы для обучения написанию эффективных запросов и оптимизации запросов, которые я уже написал?
Я бы сказал, что основные вещи:
- Понимать основанный на наборах характер SQL , читая книги с нейтральной платформой, такие как Celko ; это поможет вам избежать ошибок новичка, например, использовать курсоры (или другие итеративные подходы), где они не нужны.
- Изучите основы нормализации и когда денормализовать; эффективные запросы начинаются с хорошо организованных данных.
- Понять, где индексы могут быть полезными и где они не могут; например, понять, как мощность ваших данных влияет на эффективность индекса, какие запросы SARGable и когда использовать многоколоночные индексы.
- Узнайте, как использовать EXPLAIN PLAN или его эквивалент для вашей платформы; наблюдая, как компилируется ваш запрос, и ресурсы, которые он потребляет, вы лучше поймете узкие места.
- Изучите специфичные для платформы методы, такие как индексированные / материализованные представления, полнотекстовое индексирование и методы подкачки и обработки иерархических данных.
Прошло некоторое время с тех пор, как я должен был использовать его, но я нашел SQL Tuning весьма полезным на работе, которую я имел несколько лет назад.
Отличная книга по теме: Внутри Microsoft SQL Server 2005: настройка и оптимизация запросов
Для конкретной информации MySQL глава 7 справочного руководства посвящена оптимизации. В § 7.3, в частности, рассматривается оптимизация операторов MySQL . Вики Wiki PostgreSQL аналогично оптимизируют документы для этой СУБД. Microsoft « Как оптимизировать SQL-запросы » удалена; Я не уверен, что есть более свежий документ. В Oracle есть несколько документов по оптимизации запросов для различных версий.
использовать индексы, где таблицы могут использовать их, а также запускать советник настройки базы данных по запросу, если у вас есть SQL Server.
sql.fliplinux.com
Учимся оптимизировать 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
www.pvsm.ru
