Как оптимизировать запросы в базе данных - основы
Кажется, что все вопросы относительно этой темы очень конкретны, и в то время как я оцениваю определенные примеры, я интересуюсь основами оптимизации SQL. Я - очень удобная работа в SQL и имею знания в программном обеспечении аппаратных средств/низкого уровня.
То, что я хочу, является инструментами и материальное программное обеспечение и метод для рассмотрения mysql баз данных, на которые я смотрю регулярно и знаю что различие между заказами операторов соединения и где операторы.
Я хочу знать, почему индекс помогает, как, точно почему. Я хочу знать конкретно, что происходит по-другому, и я хочу знать, как я могу на самом деле посмотреть на то, что происходит. Мне не нужен инструмент, который будет разбивка каждый шаг моего SQL, я просто хочу смочь ввести по абсолютному адресу вокруг и если кто-то не может сказать мне, какой столбец индексировать, я смогу вынуть лист бумаги и в течение некоторого промежутка времени смочь придумать ответы.
Базы данных являются сложными, но они не ТО, ЧТО сложный, и должен быть некоторый большой материал там для изучения основ так, чтобы Вы знали, как найти решения проблем оптимизации, с которыми Вы встречаетесь, даже если мог выследить точный ответ на форуме.
Рекомендуйте некоторое чтение, которое кратко, интуитивно, и не боится перейти к низкоуровневым основным деталям. Я предпочитаю бесплатные ресурсы онлайн, но если бы книжная рекомендация уничтожает шляпку гвоздя, она совершает нападки, я рассмотрел бы принятие ее.
5 ответов
Вы должны выполнять поиск для каждого условия where и для каждого соединения ... условия. Оба работают одинаково.
Предположим, мы пишем
select name
from customer
where customerid=37;
Каким-то образом СУБД должна найти запись или записи с customerid = 37. Если индекса нет, единственный способ сделать это - прочитать каждую запись в таблице, сравнивая customrid с 37. Даже когда он находит один, у него нет возможности узнать, что он только один, поэтому он должен продолжать поиск другие.
Если вы создаете индекс по клиенту, у СУБД есть способы для очень быстрого поиска по индексу. Это не последовательный поиск, а, в зависимости от базы данных, бинарный поиск или какой-либо другой эффективный метод. Неважно, как именно, примите тот факт, что это намного быстрее, чем последовательное. Затем индекс направляет его непосредственно к соответствующей записи или записям. Более того, если вы укажете, что индекс «уникальный», тогда база данных знает, что может быть только один, поэтому она не тратит время на поиски секунды. (И СУБД не даст вам добавить вторую.)
Теперь рассмотрим этот запрос:
select name
from customer
where city='Albany' and state='NY';
Теперь у нас есть два условия. Если у вас есть индекс только для одного из этих полей, СУБД будет использовать этот индекс для поиска подмножества записей, а затем последовательно искать их.Например, если у вас есть индекс по состоянию, СУБД быстро найдет первую запись для Нью-Йорка, затем последовательно выполнит поиск по запросу city = 'Albany' и прекратит поиск, когда достигнет последней записи для Нью-Йорка.
Если у вас есть индекс, который включает оба поля, то есть «создать индекс по заказчику (штат, город)», то СУБД может немедленно перейти к нужным записям.
Если у вас есть два отдельных индекса, по одному на каждое поле, СУБД будет иметь различные правила, которые она применяет, чтобы решить, какой индекс использовать. Опять же, то, как именно это делается, зависит от конкретной СУБД, которую вы используете, но в основном она пытается вести статистику по общему количеству записей, количеству различных значений и распределению значений. Затем он будет последовательно искать в этих записях те, которые удовлетворяют другому условию. В этом случае СУБД, вероятно, заметит, что городов намного больше, чем штатов, поэтому, используя индекс города, можно быстро приблизиться к записям «Олбани». Затем он будет последовательно искать их, сравнивая состояние каждого с «NY». Если у вас есть записи по Олбани, Калифорния, они будут пропущены.
Каждое соединение требует определенного поиска.
Допустим, мы пишем
select customer.name
from transaction
join customer on transaction.customerid=customer.customerid
where transaction.transactiondate='2010-07-04' and customer.type='Q';
Теперь СУБД должна решить, какую таблицу читать первой, выбрать оттуда соответствующие записи, а затем найти совпадающие записи в другой таблице.
Если у вас есть индекс для transaction.transactiondate и customer.Customerid, лучший план, вероятно, будет заключаться в том, чтобы найти все транзакции с этой датой, а затем для каждой из них найти клиента с совпадающим клиентом, а затем проверить, что у клиента правильный тип.
Если у вас нет индекса для customer.customerid, то СУБД могла бы быстро найти транзакцию, но тогда для каждой транзакции ей пришлось бы последовательно искать в таблице клиентов в поисках подходящего идентификатора клиента. (Скорее всего, это будет очень медленно.)
Предположим, что у вас есть только индексы для transaction.customerid и customer.type. Тогда СУБД, вероятно, использовала бы совершенно другой план. Вероятно, он просканирует таблицу клиентов для всех клиентов с правильным типом, затем для каждого из них найдет все транзакции для этого клиента и последовательно выполнит поиск нужной даты.
Самый важный ключ к оптимизации - это выяснить, какие индексы действительно помогут, и создать эти индексы. Лишние неиспользуемые индексы - это бремя для базы данных, потому что для их обслуживания требуется работа, а если они никогда не используются, это напрасная трата усилий.
Вы можете определить, какие индексы СУБД будет использовать для любого заданного запроса, с помощью команды EXPLAIN. Я все время использую это, чтобы определить, хорошо ли оптимизируются мои запросы или мне нужно создавать дополнительные индексы. (Прочтите документацию по этой команде для объяснения ее вывода.)
Предостережение: помните, что я сказал, что СУБД хранит статистику по количеству записей, количеству различных значений и так далее в каждой таблице.EXPLAIN может дать вам сегодня совершенно другой план, чем вчера, если данные изменились. Например, если у вас есть запрос, который объединяет две таблицы, и одна из этих таблиц очень мала, а другая большая, он будет смещен в сторону чтения сначала небольшой таблицы, а затем поиска совпадающих записей в большой таблице. Добавление записей в таблицу может изменить ее размер, что приведет к изменению плана СУБД. Таким образом,вам следует попытаться выполнить EXPLAINS для базы данных с реалистичными данными. Запуск с тестовой базой данных с 5 записями в каждой таблице имеет гораздо меньшую ценность, чем запуск с реальной базой данных.
Что ж, можно сказать гораздо больше, но я не хочу писать здесь книгу.
Допустим, вы ищете друга в другом городе. Один из способов - пройти от двери к двери и спросить, не тот ли дом, который вы ищете. Другой способ - посмотреть на карту.
Индекс - это карта таблицы. Он может сказать движку БД, где именно находится то, что вы ищете. Таким образом, вы индексируете каждый столбец, который, по вашему мнению, вам придется искать, и не учитываете столбцы, из которых вы просто читаете данные, и никогда не выполняете поиск.
Хорошее техническое прочтение об индексах и об оптимизации ORDER BY . А если вы хотите увидеть, что именно происходит, вам нужен оператор EXPLAIN .
Не думайте об оптимизации баз данных. Думайте об оптимизации запросов.
Как правило, вы оптимизируете один случай за счет других. Вы просто должны решить, какие случаи вас интересуют.
"Меня особенно интересует, как индексы повлияют на джойны"
В качестве примера я возьму случай equijoin (SELECT FROM A,B WHERE A. x = B.y).
Если индексов нет вообще (что возможно в теории, но, думаю, не в SQL), то практически единственный способ вычислить объединение - это взять всю таблицу A и разбить ее по x, взять всю таблицу y и разбить ее по y, затем сопоставить разделы и, наконец, для каждой пары совпадающих разделов вычислить строки результата. Это дорого (или даже совершенно невозможно из-за ограничений памяти) для всех таблиц, кроме самых маленьких.
Та же история, если существуют индексы на A и/или B, но ни один из них не имеет x или y в качестве первого атрибута.
Если существует индекс на x, но не на y (или наоборот), то открывается другая возможность: просканировать таблицу B, для каждой строки подобрать значение y, найти это значение в индексе и получить соответствующие строки A для вычисления объединения. Обратите внимание, что это все равно не принесет вам большого выигрыша, если не применяются другие ограничения (AND z = ...) - за исключением случая, когда между значениями x и y есть только несколько совпадений.
Если для x и y существуют упорядоченные индексы (хэш-индексы не являются упорядоченными), то открывается третья возможность: выполнить сканирование совпадений по самим индексам (сами индексы, скорее всего, будут меньше, чем таблицы, поэтому сканирование самого индекса займет меньше времени), и для совпадающих значений x/y вычислить объединение соответствующих строк.
Это базовый вариант. Вариации возникают для объединений по x>y и т.д.
Я не знаю об инструментах MySql, но в MS SqlServer есть инструмент, который показывает все операции, выполняемые запросом, и сколько времени занимает обработка всего запроса.
Использование этого инструмента помогло мне понять, как запросы оптимизируются оптимизатором запросов, гораздо лучше, чем, я думаю, могла бы помочь любая книга, потому что то, что делает оптимизатор, часто нелегко понять. Настроив запрос и, возможно, подстроив базу данных, я мог увидеть, как каждое изменение влияет на план запроса. Существуют определенные ключевые моменты в написании запросов, но мне кажется, что вы уже имеете представление о них, поэтому оптимизация в вашем случае в большей степени связана с этим, чем с какими-либо общими правилами. После нескольких лет работы над базами данных я просмотрел несколько книг, посвященных оптимизации баз данных на SQL Server, и нашел очень мало полезной информации.
Быстрое гугление натолкнуло меня на следующее: http://www.mysql.com/products/enterprise/query.html, который звучит как похожий инструмент.
Конечно, это было на уровне запросов, оптимизация на уровне базы данных - это совсем другое дело, но там вы смотрите на такие параметры, как распределение базы данных на жестких дисках и т.д.. По крайней мере, в SqlServer вы можете выбрать разделение таблиц на разные жесткие диски и даже дисковые пластины, и это может дать большой эффект, поскольку диски и головки дисков могут работать параллельно. Другой вопрос - как построить запросы так, чтобы база данных могла выполнять их в нескольких потоках и процессорах параллельно, но оба этих вопроса опять же зависят от движка базы данных и даже версии, которую вы используете.