|
Задавайте вопросы, мы ответим
Вы не зашли.
Собственно вопрос простой: существую разные подходы к оптимизации запросов оптимизатором. Основанные на синтаксисе, основанные на стоимости, может ещё какие-то? Можете подсказать как работает в этом плане MySQL? или где об этом можно почитать?
Заранее спасибо!
Неактивен
Вы довольно широку тему затронули. Попробую ответить просто, а дальше спрашивайте.
Оптимизатор MySQL принимает следующие решения:
1. в каком порядке выполнять JOIN.
2. какие индексы использовать для WHERE и JOIN и каким способом (включая экзотические алгоритмы, как index merge)
3. использовать ли индекс или отдавать предпочтение full table scan
В итоге создается план исполнения запроса, который можно приблизительно увидеть, выполнив EXPLAIN.
Влиять на работу оптимизатора можно, используя STRAIGHT_JOIN, USE INDEX, IGNORE INDEX, FORCE INDEX, но фактически этим возможности прямого влияния ограничены.
Неактивен
Попробую уточнить вопрос!
К примеру Oracle использует систему рангов для выбора оптимального плана выполнения... есть ещё подход на основе стоимости выполнения плана... как работает тут mysql?
Неактивен
MySQL использует относительно простые алгоритмы для оценки сложности исполнения запроса. Эта сложность исполнения минимизируется.
Неактивен
Оптимизатор запросов строит множество планов выполнения запроса, а потом на основе чего-то принимает решение о выполнении одного из множества. Вот меня и интересует, на основе каких критериев оптимизатор запросов в MySQL принимает это решение?
Неактивен
Точный алгоритм нужно смотреть в исходниках, но одним из основных критериев является оцениваемое количество строк для перебора.
Неактивен
Значит речь идёт о оценочном подходе.
Неактивен
А как работает Оракл (просто в целях просвещения)?
Неактивен
Если верить Карповой, то используется метод оптимизации основанный на синтаксисе:
http://www.rema44.ru/resurs/study/optimize/optimize.htm написал:
При использовании этого метода план составляется на основании существующих путей доступа и их рангов. Ранжирование путей доступа выполняется на основании знаний о правилах и последовательности осуществления этих путей.
Ранг пути доступа определяется на основании знаний о последовательности реализации этого пути. Например, одна строка по хеш-кластеру с уникальным или первичным ключом (ранг 3) будет выбираться в среднем быстрее, чем одна строка по уникальному или первичному ключу (ранг 4), потому что для получения ROWID строки из хеш-кластера достаточно применить к значению ключа хеш-функцию, а для получения ROWID по индексу требуется лишнее обращение к индексу (который может в данный момент отсутствовать в оперативной памяти, что вызовет обращение к диску).
Метод оптимизации, основанный на синтаксисе, учитывает иерархическое старшинство операций. Если для какой-либо операции существует более одного пути ее выполнения, то выбирается тот путь, чей ранг выше, т.к. в большинстве случаев он выполняется быстрее, чем путь с более низким рангом. План выполнения запроса формируется из выбранных путей доступа с максимальными рангами
Неактивен
Относительно MySQL, запрос обрабатывается в несколько фаз:
Я написал:
На первой фазе осуществляется лексический и синтаксический анализ запроса, формируется представление запроса, отражающее его структуру и содержащее информацию, которая характеризует объекты базы данных, упомянутые в запросе (отношения, поля и константы).
На второй фазе, полученное в результате анализа на первой фазе представление запроса, подвергается логической оптимизации. На этом этапе представление подвергается различным преобразованиям, делающим представление более удобным с точки зрения системы, хотя и являющимся эквивалентным представлению полученному на первой фазе.
На третей фазе обработки строится набор альтернативных процедурных планов выполнения запроса в соответствии с его внутреннем представлением, полученным на второй фазе. Также, для каждого плана оценивается стоимость его выполнения на основе статистической информации о состоянии базы данных. И в результате выбирается среди альтернативных планов тот, который имеет наименьшую стоимость выполнения.
На четвертой фазе формируется выполняемое представление плана.
И на пятой фазе происходит реальное выполнение запроса в соответствии с выполняемым планом запроса.
Собственно из третей фазы видно, что используется метод основанный на стоимости. А конкретные критерии оценки стоимости зависят в том числе и от настроек сервера. Вот, сам у себя нашёл ответ на свой вопрос))) Спасибо огромное за помощь и ещё большее спасибо, если Вы можете опровергнуть или дополнить написанное тут мною!)
Неактивен
Мне пока непонятно одно - чем ранг отличается от стоимости. Если в описании про Оракл заменить слово ранг на слово стоимость оно на мой взгляд останется корректным. Не покидает ощущение, что описан в общем один и тот же подход, с точностью до все равно неизвестной реализации.
Вы научный труд пишете? Наверное вам недостатки оптимизатора тоже нужны. Основной недостаток вытекает из основного преимущества MySQL - возможности работать с подключаемыми хранилищами, которые могут в том числе добавляться пользователями и обладать определенными свойствами с точки зрения физического хранения данных. В моем докладе на РИТ есть архитектура MySQL - в ней показано место оптимизатора и подключаемых хранилищ. Видно, что хранилища - внешний элемент и оптимизатор об их свойствах или не знает или знает немного. Отсюда недостаточная информированность о "стоимости" операции.
Неактивен
Да, дипломчик тут у меня...
Я так понимаю, что различие в ранге и стоимости в том, что ранг строится на основе пути доступа, для определения которого проводится анализ синтаксиса запроса. А стоимость учитывает большее количество параметров, вес которых также зависит от многих факторов и для вывода стоимости запроса используется информация о состоянии базы данных. Например распределение значений в столбце.
Опять же, если верить Карповой, то это два принципиально разных подхода... ну, а я пока только разбираюсь что да как)
Оу! просмотрел Вашу презентацию крайне полезная вещь! - огромное спасибо)
Неактивен
Насчет стоимости и MySQL вы правы, для того, чтобы оценить сколько строк потребуется используется внутренняя статистика индексов. Если вы напишете EXPLAIN запроса, он покажет сколько строк выбирается при присоединении каждой таблице - число оценочное, оно не совпадет с итоговым, но в большинстве случаев близкое. Также, если MySQL поймет, что результат - пустое множество, запрос может не выполняться (называется optimized out). Например условие WHERE b>10, но на b есть индекс и MySQL видит, что максимальное значение 5. Кстати, этот метод приводит к тому, что порядок выполнения запроса может измениться по мере наполнения таблиц.
Про Оракл я не знаю, если он действительно не учитывает статистику данных, то это принципиально другой подход, преимущество которого в детерминистичности пути исполнения запроса на основании структуры таблиц.
Неактивен
Ну вот) мне главное было разобраться с механизмами MySQL) спасибо за помощь!
Неактивен
