Добрый день, уважаемые!

Там подправляю один проект, идут запросы типа:



Привожу в виду:


Я не эксперт в MySQL, но догадываюсь что в любом случае вначале будет клепаться JOIN-таблица, и уже после всех JOINов (я, кстати, еще слышал что они могут выполняться в произвольном порядке, как компилятор посчитает оптимальней, это правда?) к ним будет применяться WHERE. Так зачем на собирать огромную таблицу, если заведомо ведомо что часть строк по ней потом все равно отбросит WHERE?

По t2.id есть индекс, я сделал еще один, составной индекс (t2.id, t2.status). Но когда смотрю через EXPLAIN, в колонке key идет все равно только t2.id! Почему так? Составной индекс в possible_keys есть, но оптимизатор его не выбирает. Собственно, основной вопрос вот в чем: я слышал что у СУБД есть какие-то хитрые анализаторы, которые ведут статистику по реальным запросам к реальным данным, и уже на основе ее (а не просто анализируя структуру самой БД), принимают решение какой индекс выгодней использовать. Это правда? Если да, то можно как-то посмотреть, какой индекс СУБД предпочтет на "голой" системе?

Сперва ответьте на вопрос - зачем какой-то [censored] залепил в текст запроса звёздочку вместо того, чтобы перечислить реально необходимые поля?


Поскольку связывание внутреннее - то НИЧЕГО не изменилось, кроме внешнего вида запроса. После обработки препарсером оба запроса поступят к оптимизатору в абсолютно одинаковом виде.

Нет.


Изучайте:
http://dev.mysql.com/doc/refman/5.5/en/whe...imizations.html
http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html


  а что есть "голая система"?

На которой не крутилась конечная БД.

Для теста сдампил базу на другой сервер. Там выбирается составной ключ. Версии 5.1.61 и 5.1.40 соответственно, не думаю что в этом делом. Пока что пошел читать ссылки, благодарю!

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

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


Т.е. ответ на этот вопрос, я так понял, положительный?

Я так понял человек начитался про CBO (cost based optimization) и влияние на планы запроса качества статистики, которая может собираться не на автомате.

Помому это просто не про майсл. Хотя он тоже какую-то статистику вроде как хранит и на нее опирается. Но, наверное, автоматом, ибо рекомендации "собрать статистику", когда оптимизатор чудит, про майскул я ни разу не слышал, в отличии от ПГ, Оракл, где такая рекомендация идет первой.

И правильно - нафига он ей? для запроса он ничего не даёт, а по размеру больше.


Поработает - тоже перестанет использоваться. А на старом сервере - сделай ANALYZE TABLE...

Добавлено через 1 минуту и 52 секунды

http://dev.mysql.com/doc/refman/5.5/en/exe...nformation.html

О... Услышал   
Больше так умничать не буду   

А напрасно... его статистика - вовсе не то же самое, что статистика у Оракла или там постгресса... Некое CBO там, конечно, имеется - но навылет ненастраиваемое. Не нравится план - force/ignore index в руки, а все грабли - за свой счёт.

Есть понятие селективность предиката. Если 99% записей у нас имеют значение 'ok', то уточнение предиката доступа по индексу этим значением статуса практически не повлияет на стоимость доступа, возможно даже негативно скажется.

В случае, же если у нас лишь 1% записей имеет значение 'ok', то, наверное, было бы предпочтительнее иметь обособленный индекс по статусу  и в качестве лидирующей таблицы для соединения выбирать t2

Это сообщение отредактировал(а) Zloxa - 4.6.2013, 14:33

Т.е. прасер сам в любом случае приведет запрос к единому виду, по возможности максимально ограничив JOIN? Ведь JOIN, насколько я понимаю, в любом случае будет приводить с созданию временной таблицы? А выгодней сделать таблицу меньше - это и быстрей, и поиск по ней проще. Т.е. на такие вещи можно не заморачиваться, парсер сам позаботится?


По размеру больше. Но как это ничего не дает? Если идет



Это ж не OR. В случае, когда для t2 используется индекс только по id, для status придется пройтись по таблице из всех удовлетворяющих нас id. Разве (id, status) тут не дает выгоды? Ничего не понимаю...  Если это имеет значение, stats - ENUM, т.е. фактически целочисленный тип.

Ссылочки читаю потихоньку, давно в англ не практиковался. 

Добавлено через 8 минут и 17 секунд
Просто эта каша вообще из-за чего заварилась.

Я всегда стараюсь использовать максимально простые запросы. Но недавно дорабатывалась одна функция, вышел запрос на 4 таблицы. Жутко тормозил - 25 секунд выполнялся. Начал его ковырять... И совершенно неожиданно для себя обнаружил, что запретив использование одного из индексов по таблице (это был одиночный индекс INT поля), type сменился с eq_ref на ALL. НО! Скорость выросла В 5 РАЗ!  До сих пор в шоке. Как использование простого индекса может к такому привести? Пока еще пиляю запрос, уже удалось добиться скорости порядка 0,45 сек, но все равно еще крайне плохо, нужно не выше 0,1. Но пока уперся, и дальше никак. Вот и решил разобраться в этом всем деле чуть основательней. 

maxipub, 
Я вам посоветую вот что
1) Определиться, какой из предикатов отсекает бОльшую часть набора "t1.balance>100" или же "t2.status='ok'"
2a) Если первый предикат более селективен, индекс по t1.balance и паре (t2.id,t2.status)
2б) Если второй предикат более селективен, индекс по t2.status и паре (t1.id,t1.balance)

если индекс не используется - форсить.

Если по каждому из предикатов отсекается < 80% данных, возможно затея с индексами вобще тухла и фулсканы будут предпочтительнее.


Это сообщение отредактировал(а) Zloxa - 4.6.2013, 14:52

Первое, что делает парсер - преобразует JOIN во WHERE.


Где ты это вычитал?

Доступ по индексу = сканирование индекса + сканирование таблицы. Если селективность предиката слишком низка, фуллсканить может оказаться выгднее.

Но чтобы в пять раз... думаю просто план стал совсем другой, другой индекс стал испольоваться.

Это сообщение отредактировал(а) Zloxa - 4.6.2013, 14:55

Ну это как-то логически выходит. Хотя, если...


То мой мир вообще перевернулся. Я всегда считал что JOIN создает временную таблицу, в которой всё перечисленное из таблиц склеивается, и к этой таблице уже применяется WHERE.

Только что пересмотрел профилирование простого запроса с JOIN, действительно, не создается. Черт.

Zloxa, t1.balance>100 отсекает почти все, t2.status='ok' почти ничего. Т.е. берем индекс по t1.balance и паре (t2.id,t2.status) - как я и делаю. Но почему тогда Akina пишет:

?

- поспешил, потерто - 

Это сообщение отредактировал(а) maxipub - 4.6.2013, 15:28