Как оптимизировать запросы в базе данных — основы

Вы должны искать каждое условие where и каждое соединение... при условии. Оба работают одинаково.

Предположим, мы пишем

select name
from customer
where customerid=37;

Каким-то образом СУБД должна найти запись или записи с идентификатором клиента=37. Если индекса нет, единственный способ сделать это — прочитать каждую запись в таблице, сравнивая идентификатор клиента с 37. Даже когда он находит один, он не может знать, что он только один, поэтому он должен продолжать поиск. другие.

Если вы создаете индекс по идентификатору клиента, у СУБД есть способы очень быстрого поиска по индексу. Это не последовательный поиск, а, в зависимости от базы данных, бинарный поиск или какой-либо другой эффективный метод. Как именно, не имеет значения, примите во внимание, что это намного быстрее, чем последовательное. Затем индекс переносит его непосредственно к соответствующей записи или записям. Кроме того, если вы укажете, что индекс является «уникальным», то база данных знает, что может быть только один, поэтому она не тратит время на поиски второго. (И СУБД не позволит вам добавить второй.)

Теперь рассмотрим этот запрос:

select name
from customer
where city='Albany' and state='NY';

Теперь у нас есть два условия. Если у вас есть индекс только для одного из этих полей, СУБД будет использовать этот индекс для поиска подмножества записей, а затем последовательно искать их. Например, если у вас есть индекс по штату, СУБД быстро найдет первую запись для NY, затем последовательно ищет city='Albany' и прекратит поиск, когда дойдет до последней записи для NY.

Если у вас есть индекс, который включает оба поля, т.е. «создать индекс по клиенту (штат, город)», то СУБД может сразу перейти к нужным записям.

Если у вас есть два отдельных индекса, по одному для каждого поля, СУБД будет иметь различные правила, которые она применяет, чтобы решить, какой индекс использовать. Опять же, как именно это делается, зависит от конкретной используемой СУБД, но в основном она пытается вести статистику по общему количеству записей, количеству различных значений и распределению значений. Затем он будет последовательно искать в этих записях те, которые удовлетворяют другому условию. В этом случае СУБД, вероятно, обнаружит, что городов гораздо больше, чем штатов, поэтому, используя индекс города, она может быстро приблизиться к записям «Олбани». Затем он будет последовательно искать их, проверяя состояние каждого на соответствие «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< /a> утверждение.

Не думайте об оптимизации баз данных. Подумайте об оптимизации запросов.

Как правило, вы оптимизируете один случай за счет других. Вам просто нужно решить, какие дела вас интересуют.

«Меня особенно интересует, как индексы повлияют на соединения»

В качестве примера я возьму случай эквисоединения (SELECT FROM A,B WHERE A.x = B.y).

Если индексов вообще нет (что возможно в теории, но я думаю, что не в SQL), то в основном единственный способ вычислить соединение — взять всю таблицу A и разбить ее по x, взять всю таблицу y и разбить это по y, затем сопоставьте разделы и, наконец, для каждой пары совпадающих разделов вычислите строки результатов. Это дорого (или даже совершенно невозможно из-за ограничений памяти) для всех таблиц, кроме самых маленьких.

Та же история, если существуют индексы для A и/или B, но ни один из них не имеет x соответственно. y в качестве его первого атрибута.

Если существует индекс по x, но не по y (или наоборот), то открывается другая возможность: просмотреть таблицу B, для каждой строки выбрать значение y, найти это значение в индексе и выбрать соответствующие строки A для вычисления присоединиться. Обратите внимание, что это все равно не принесет вам много пользы, если не применяются другие дополнительные ограничения (И z = ...) - за исключением случая, когда между значениями x и y есть только несколько совпадений.

Если упорядоченные индексы (индексы на основе хэшей не упорядочены) существуют и по x, и по y, то открывается третья возможность: выполнить сканирование на соответствие самим индексам (сами индексы, вероятно, будут меньше, чем сами таблицы, поэтому сканирование сам индекс займет меньше времени), а для совпадающих значений x/y вычислите соединение соответствующих строк.

Это базовый уровень. Вариации возникают для соединений по x> y и т. Д.

Я не знаю об инструментах MySql, но в MS SqlServer у вас есть инструмент, который показывает все операции, которые потребует запрос, и сколько времени займет обработка всего запроса.

Использование этого инструмента помогло мне понять, как оптимизатор запросов оптимизирует запросы, гораздо больше, чем, я думаю, могла бы помочь любая книга, потому что то, что делает оптимизатор, часто нелегко понять. Настроив запрос и, возможно, подчеркнутую базу данных, я смог увидеть, как каждое изменение повлияло на план запроса. Есть определенные ключевые моменты в написании запросов, но мне кажется, что вы уже имеете представление о них, поэтому оптимизация в вашем случае гораздо больше связана с этим, чем с какими-либо общими правилами. После нескольких лет разработки БД я просмотрел несколько книг, специально посвященных оптимизации баз данных на SQL Server, и нашел очень мало полезной информации.

Быстрый поиск в Google дал следующее: http://www.mysql.com/products/enterprise/query.html, что похоже на аналогичный инструмент.

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