Комментарии 19
Никто не пишет, поэтому выскажусь. Несколько раз подходил к этому тексту, но не смог его осилить. Вроде, по отдельности каждое предложение более-менее понятно, но в общую картинку не складывается. То ли я такой тупой, то ли это очередное творение ИИ. Рассуждая логически, тут скорее все-таки первое. Но чисто психологически, конечно, хочется чтобы второе.
Подскажите, пожалуйста, на ваш взгляд было тяжело читать из-за языка, плохой структурированности или чего-то еще?
Скорее второе. Почему наводит на ассоциации с ИИ — статья выглядит, как одноранговый набор определений. Статья прямо-таки набита определениями, но вот логических связок между ними гораздо меньше. Ну или это чисто моя проблема. Хотя отсутствие комментариев наводит на мысль, что многие прочитавшие тоже не нашли, за что зацепиться.
Мне тоже тяжело заходит, хотя тема очень интересная. Кажется, что если бы было больше оформленных в псевдокод императивных примеров, то дело бы пошло веселее
Не могли бы вы ответить здесь или написать другую статью об использовании "SELECTIVITY 0" в запросах? Когда это нужно делать, когда не нужно?
Честно говоря, никогда не сталкивался с SELECTIVITY 0 в работе, поверхностное гугление тоже ничего не дало :(
Могу предположить, что вопрос про низкоселективные индексы, если нет, можете, пожалуйста, дать конкретики, может какие-нибудь примеры?
Я сам пока работал с MS SQL не сталкивался. Недолго работаю на DB2 OS:IBM i 7.4.
Столкнулся с тем, что запрос который должен быстро выполняться - уверенно на часы зависает. Посоветовался с более опытными - сказали добавь SELECTIVITY 0.
Добавил - запрос стал выполняться за секунды.
В TABLE_A было 15 записей с уникальными идентификаторами.
TABLE_B - большая партицированная таблица из полутора миллиардов записей.
Поле - ABC - индексировано, уникально.
SELECT
A.ABC, B.*
FROM TABLE_A A
JOIN TABLE_B B ON A.ABC=B.ABS SELECTIVITY 0
Точного ответа на этот вопрос дать не могу, т.к. с DB2 никогда не работал
Но могу выдвинуть предположение
В документации к DB2 написано:The SELECTIVITY clause can only be used with basic predicates (as defined in the SQL reference), not predicates such as LIKE or BETWEEN. A lower selectivity value (very small number) will tell DB2 that the predicate will qualify fewer rows (and encourage use of indexes defined on that column). A higher selectivity value (close to 1) will mean the opposite.То есть, если операция выборки из 10000 строк вернет 2, то использовать полный обход заведомо неразумно - слишком много операций впустую, поэтому в таких случаях можно заставить оптимизатор использовать индекс, даже если оценка с ним почему-то хуже, чем без него (например мало статистических данных, чтобы нормально кардинальность посчитать)
Скорее всего, в примере оптимизатор решил, что без индекса лучше, а применение SELECTIVITY 0 заставило его передумать :)
Вообще, как я понял, параметр SELECTIVITY можно задавать любой от 0 до 1, и это будет как-то влиять на решения оптимизатора, но 0 - это что-то вроде насильственного использования индекса
Так и я работал с dbase, foxpro, ms sql. И всегда считал, что нужные индексы всегда сами подключаются для ускорения запроса. Старшие товарищи говорят, что влияет ещё статистика работы с конкретной таблицей. А тормоза проявляются чаще всего на редко используемых архивных базах данных. Как будто оптимизатор не обучен.
Работал плотно только с mssql. Его здравый смысл потребовал бы, чтобы B.ABS тоже было индексировано (надеюсь это так? ), а соединение крайне желательно было левым.
В свое время, помню, Oracle CBO часто ошибался при объединении двух таблиц с where. Выборка по первой таблице была огромной, а по второй - давала лишь несколько строчек. По какой-то причине в определенный момент он предпочитал сначала делать выборку именно по первой таблице, что увеличивало время запроса с 0.1 сек до 5-6. Не помогали ни переписывания запросов (Oracle индифферентен к форме запроса), ни прогоны анализатора статистики. Лечилось только явными хинтами, поставленными в запросе. Не знаю, баг ли это был или что-то ещё, но факт в том, что CBO не гарантирует оптимальное выполнение.
В оптимизаторе PostgreSQL используется генетический алгоритм. Что можете про него рассказать? когда стоит его использовать и когда стоит его отключать?
Как оптимизируются JOIN-ны с 5-ю и более таблицами? С большой и малой селективностью?
Чем отличаются оптимизаторы PostgreSQL и MySQL?
Начал разбирать вопрос, понял, что скорее потянет не на комментарий, а на отдельную статью. Если Вы не против, так и сделаю :)
Могу пока посоветовать прочитать публикацию Севидова П.Н. https://pandia.ru/text/81/114/5463.php, там вполне неплохо описывается генетический алгоритм в задаче оптимизации, и есть даже про JOIN с 5-ю таблицами
Что касается, разницы PostgreSQL и MySQL, могу сказать следующее: оба используют подход с левосторонними деревьями (снизу вверх), но в PostgreSQL оптимизатор работает немного лучше, так как MySQL использует подход П.Г.Сэлинджер напрямую, а PostgreSQL применяет модифицированный алгоритм Г.Моеркотта и Т.Ньюмана. Там суть в оптимизации построения деревьев, что позволяет быстрее выполнять вычисления и сразу отбрасывать часть неэффективных планов
Подробнее можете почитать в работе со страшным названием A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration
В статье есть определение дерева запросов, но нет описания, что такое проекция. Т.е. если давать детальное описание, то мне кажется нужно описывать все термины, с примерами. Да и после теории, лучше всего давать пример на пальцах, чтобы меньше абстракции.
Статья писалась не совсем для новичков, все-таки тема не самая простая, поэтому предполагается, что базовые вещи, вроде определения выборки и проекции читатель уже знает
А вот про деревья запросов далеко не в каждой книжке по БД пишут, по личному опыту знаю
Со вторым соглашусь, постараюсь в дальнейшем давать больше примеров
Было бы круто, если бы был приведен пример к Второй подход: сверху вниз. На практическом примере увидеть принцип работы. В целом статья очень понравилась. Спасибо за такую интересную статью! И да, куда бы вы могли сослаться на информацию о "деревьях запросов" ?
Спасибо за отзыв!
Про второй подход могу посоветовать цикл статей от Querify Labs - https://www.querifylabs.com/blog/memoization-in-cost-based-optimizers
Что касается деревьев запросов, если не изменяет память, брал информацию отсюда: Yongwen Xu: Efficiency In The Columbia Database Query Optimizer| Portland State University, 1998
Спасибо за статью. Всегда интересно читать материал на подобные темы. Но, все таки, отмечу некоторые недочеты, которые делают материал сложным и не системным для восприятия. Об этом уже выше упоминали.
Деревом запроса называется древовидная структура, в узлах которой расположены логические операторы, соответствующие отдельным операциям запроса.
Слово "логические" здесь требует расшифровки или уточнения. Ниже вы употребляете связку "физические" / "логические" операции, связывая с трансформациями, но в данном случае это сбивает с толку. Может просто "отдельные элементарные операции"?! А ниже уже расшифровывать на логические и физические, иначе возникает путаница с реальными логическими выражениями в условиях.
Более того, повествование к середине статьи уходит корнями только в JOIN-ы и их связь с теорией множеств и логикой. И слово "логический" в Вашем тексте становится крайне перегруженным, и каждый раз нужно себя заставлять понять, что же конкретно автор имеет в виду.
Разобравшись с тем, что такое дерево поиска, введем ещё два определения: логическая эквивалентность и область поиска.
В каком-то смысле план каждого SELECT-запрос действительно можно сравнить с неким деревом поиска. Но, честно говоря, - это двоякое понятие, особенно в контексте баз данных. Может все таки использовать "дерево разбора" или "дерево запроса", чтобы не было путаницы с реальными деревьями поиска. Тем более далее Вы снова употребляете фразу "...деревья запросов..."
Чтобы сократить количество логически эквивалентных выражений, их можно преобразовывать в так называемые групповые выражения. В каждом узле дерева такого выражения находится логический оператор, принимающий на вход группы эквивалентных запросов, а не просто отдельные запросы.
Вот это наверно самое сложное место статьи. Змея сама себя съела с хвоста. Намешано в кучу и логические выражения и запросы и все это завернутое само в себя на вход другому узлу. По контексту, конечно, можно догадаться, что Вы хотели сказать. Вы имели здесь в виду по большей части всякие JOIN-ы, что тесно связано и с теорией множеств и с логикой (SQL JOINS on Euler/Venn Diagramms).
Но слова выбраны, на мой взгляд, неподходящие. Очень сложно читать. Как улучшить, не знаю. Наверно только переписать заново, точно поняв набор каких понятий (введенных выше) использовать, заранее обозначить, что все повествование статьи идет в контексте JOIN.
Вероятно Вы это взяли из какой-то книжки. Тогда лучше процитировать автора повествования теории в таком виде, чтобы все таки разобраться в понятиях.
Очевидно, что при таком подходе выбор оптимального плана выродится в полный перебор всех вариантов, поэтому для использования на практике его нужно модифицировать.
Превратиться?! ))
for a in A:
if (contains(B, a)):
return a x b
return надо бы подвинуть. Ну и если вы хотите вернуть пару (кортеж), а здесь именно это и предполагается, то это лучше как-то обозначить без применения оператора декартова произведения по отношению к элементам множества.
Тоже самое съехали отступы ниже.
Магия оптимизации SQL запросов