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

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



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


Я не эксперт в 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

Посмотрите http://dev.mysql.com/doc/refman/5.5/en/ind...timization.html

потому что если статус у вас не отекает почти ничего (селективность предиката высока) - преимущества использования этого индекса против индекса по id - не очевидны. Два индекса вам точно не нужны. Нужен ли индекс по паре - сомнительно. Вполне есть смысл задуматься окупятся ли расходы на его содержание профитом от его использования. И если  врезультате этих раздумий таки решите что - да, имеет смысл дропнуть индекс по id, ибо индекс по паре (id,status) может быть использван для тех же целей, для каких может быть использован индекс по id

Добавлено @ 16:10

хотя....

Akina, мася умеет такую хрень:

?

Добавлено @ 16:15

Это же совсем не о том... здесь нет индекс мержа.   
Здесь реньжскан индекса по balanse>100 + нестед луп, с уник/реньжскану  по индексу t2.id

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

Парни, очень признателен за помощь! За эти сутки узнал много нового, по-другому начал смотреть на это дело. Утро вечера мудренее, тот мутный запрос уже удалось ускорить с 25 до <0.05сек  ну еще раза в два ускорить, и я буду совсем доволен.  

Такой еще вопрос по теме: а можно при JOIN как-то явно указать лидирующую таблицу? Просто я уверен что сейчас она не оптимальная, хотя бы попробовать что будет. Запихнул все индексы нового лидера в IGNORE, выполняет полное сканирование, но все равно выбирает эту таблицу! Можно как-то явно указать приоритетную?

И если у кого есть что интересного по теме, желательно на русском, буду рад ссылочкам. И инглыш тоже. МАН хорош, но местами суховат, описание, пример-другой, а особенностей применения не особо.

узнал про STRAIGHT_JOIN 

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


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

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

Т.е. не уникальный индекс по (id,status), а ограничение ПК по (id) использует этот индекс

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

Может... но и в этих сценариях нет индексмержинга ))

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

В оракле, кстате, очень не популярный метод жойна.

Составной первичный индекс? Да пжалста. Причём по текстовым полям можно не всё поле в индекс, а только необходимый префикс. 


ааа... не, вот чего нет того нет.

оракля в этом отношении вобще умняшка... может даже сам определить, что для поддержания ограничения нового индекса не надо

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

да нет косячка, нормуль все.

Структура уникального индекса ничем не отличается от структуры не уникального индекса. Если уж на то пошло, уникальный индекс сам по себе - пережиток старины. Для контроля целостности должны использоваться ограничения, а не индексы. Для своей работы ограничения могут требовать наличия индекса(и даже строить его самостоятельно, при создании). В общем-то индекс это просто некая вспомогательная структура данных, к логической организации данных отношения никоим местом иметь не должная. Но т.к. у нас есть богатое историческое наследие использования индексов для контроля целостности...

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

Не... я всего лишь о голом факте, что данный индекс (ну или комбинация полей) по факту является уникальной, в то время как согласно выводу числится неуникальной на том основании, что таковое требование не наложено в составе ограничения при создании либо изменении индекса.

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

В том то все и дело. Повторюсь. Уникальный и не уникальный индекс имеют одинаковую структуру. Если бы уникальный имел более оптимальную какуюнить струтуру хранения, логику построения, строить его по заведомо уникальным полям имело бы какой-то смысл.


Сведения о требовании уникальности сохранены в структуре данных ограничением первичного ключа. Обеспечиватеся это требование уникальным ли индексом - не уникальным ли - какая разница?




с уникальным индексом - то же самое.

Надо заметить, что в ошибке сказано про ограничение, хотя ограничения, как такового не прописано и оно нигде не числится.


Однако, надо отдать должное, что исторически исключение ORA-00001 в PL/SQL имеет мнемонику dup_val_on_index

Это сообщение отредактировал(а) Zloxa - 5.6.2013, 15:17

То есть он правильно именно в констрейнт носом тычет. Гуд. 


Да понятно это - условие реально отделено от индекса.