Информационный сайт

 

Реклама
bulletinsite.net -> Книги на сайте -> Программисту -> Тоу Д. -> "Настройка SQL. Для профессионалов" -> 132

Настройка SQL. Для профессионалов - Тоу Д.

Тоу Д. Настройка SQL. Для профессионалов — СПб.: Питер, 2004. — 333 c.
ISBN 5-94723-959-0
Скачать (прямая ссылка): nastroykasqldlyaprof2004.djvu
Предыдущая << 1 .. 126 127 128 129 130 131 < 132 > 133 134 135 136 137 138 .. 161 >> Следующая


Однако оказывается, что можно добиться лучших результатов, если создать индексы, идеально подходящие для этого запроса. Чтобы сделать задачу более конкретной, предположим, что рис. 9.4 иллюстрирует следующий запрос:

Совмещенные соединения и фильтрующие индексы

PD 0.005 50/\100

О 0.001

s о.з

Рис. 9.4. Простое трехстороннее соединение
Совмещенные соединения и фильтрующие индексы

269

SELECT ... FROM Shipments S. Order_Details OD. Orders 0 WHERE 0.0rder_ID = 0D.0rder_ID AND 0D.Shipment_ID = S.Shipment_ID AND O.Customer_ID = :1 AND OD.Product_ID = :2 AND S.Shipment_Date > :3

Предполагая, что у нас приблизительно 1000 покупателей, 200 продуктов, а дата, обозначенная параметром : 3, указывает точку, находящуюся приблизительно в 30 % от конца периода регистрации поставок, мы получим коэффициенты фильтрации, указанные на диаграмме. Чтобы еще более конкретизировать задачу, предположим, что количество строк в 0rder_Detai I s равно 10 000 000. Согласно имеющемуся детальному коэффициенту соединения Orders с 0rder_Detai I s, количество строк в Orders должно быть 200 000, то есть ожидается считывание 200 строк из Orders, которые соединятся с 10 000 строкам из 0rder_Detai I s. После отбрасывания записей 0rder_Detai I s с неподходящими значениями Product_ID, текущее количество строк станет равным 50. Эти строки соединятся с 50 строками из Shipments, и после того, как старые поставки будут отброшены, останется 15 строк.

Что же в плане выполнения составляет большую часть стоимости? Определенно затраты на Orders и Shi pments и их индексы минимальны, так как из этих таблиц считывается немного строк. Для считывания из индекса 0rder_DetaiIS(Order ID) потребуется сканирование 200 диапазонов индекса, каждый из которых покрывает 50 строк. Каждое сканирование диапазона потребует прохождения по дереву индекса глубиной в три уровня и, вероятно, затронет один листовой блок для каждого сканирования, что потребует примерно три операции логического ввода-вы-вода. В целом это потребует приблизительно 600 достаточно хорошо кэшированных операций логического ввода-вывода для индекса. Только для самой таблицы 0rder_Detai I s потребуется 10 000 операций логического ввода-вывода, и эта таблица достаточно велика, чтобы многие операции также потребовали физического ввода-вывода. Как же можно улучшить ситуацию?

Фокус заключается в том, чтобы использовать условие фильтрации для 0rder_Details еще до обработки этой таблицы, вместе с индексом. Если вы замените индекс по 0rder_Detai I s (0rder_ID) новым индексом по 0rder_Detai I s (0rder_ID. Product_ID), то сканирование 200 диапазонов индекса по 50 строк каждый превратятся в сканирование 200 диапазонов индекса, размером в среднем с половину строки.

ПРИМЕЧАНИЕ----------------------------------------------------------------------------

Противоположный порядок столбцов в этом индексе для данного запроса также подходит. При этом произойдет даже лучшее самокэширование, так как необходимые записи индекса будут собраны вместе в одном Product_ID.

С этим новым индексом вам придется считать только 50 действительно необходимых строк из 0rder_Details, а это 200-кратная экономия на физическом и логическом вводе-выводе для данной таблицы. Так как 0rder_Detai I s — это единственный объект запроса, требующий существенных объемов ввода-вывода, то описанное мной нововведение позволит достигнуть 50-кратного увеличения производительности всего запроса, предполагая намного лучшее кэширование других, небольших объектов.
270

9. Особые случаи

Ho почему же я тянул до главы 9 с описанием такой значительной возможности оптимизации? Практически везде в этой книге я ставил целью поиск лучшего плана выполнения, независимо от того, какие индексы есть в базе данных на текущий момент. Однако позади этой идеальной картинки просвечивает реальность: многие индексы, предназначенные для оптимизации отдельных, редко используемых запросов, будут стоить больше, чем смогут оказать помощи. Хотя индекс, охватывающий внешний ключ и условие фильтрации, ускорит запрос в нашем примере, он замедлит любую вставку и удаление, а также многие обновления, если они будут затрагивать индексированные столбцы. Эффект, который оказывает один новый индекс на любую данную операцию вставки, минимален. Однако, если распространить его на все вставки и добавить эффект множества других пользовательских индексов, то увеличение количества индексов легко может принести больше вреда, чем пользы.

Рассмотрим еще один способ оптимизации того же запроса. Узел S, как и OD, обрабатывается через ключ соединения, и для него есть фильтрующее условие. Что, если создать индекс по Shipments(Shipment_ID, Shipment_Date), чтобы избежать ненужных считываний из таблицы Shipments? Количество считываний из этой таблицы сократится на 70 %, но это всего лишь экономия 35 операций логического ввода-вывода и, возможно, одной или двух операций физического ввода-вывода, что, вероятно, будет недостаточно для получения заметного эффекта. В реальных запросах такие минимальные улучшения с пользовательскими индексами, комбинирующими ключи соединения и условия фильтрации, встречаются намного чаще, чем возможности для существенных улучшений.
Предыдущая << 1 .. 126 127 128 129 130 131 < 132 > 133 134 135 136 137 138 .. 161 >> Следующая
Реклама
Авторские права © 2009 AdsNet. Все права защищены.
Rambler's Top100