The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

форумы  помощь  поиск  регистрация  майллист  ВХОД  слежка  RSS
"Или оптимизатор MySQL тупит, или я"
Вариант для распечатки Архивированная нить - только для чтения! 
Пред. тема | След. тема 
Форумы Программирование под UNIX (Public)
Изначальное сообщение [Проследить за развитием треда]

"Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 00:43  (MSK)
Люди, разбирающиеся в оптимизации запросов MySQL, помогите, плиз.
Система FreeBSD 4.7, MySQL 3.23.49 и 4.0.16 (проверено на обоих).
Ситуация такая:

есть таблица session:

mysql> desc session;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| session_id     | int(10) unsigned     |      | PRI | 0       |       |
| session_length | smallint(5) unsigned |      | MUL | 0       |       |
+----------------+----------------------+------+-----+---------+-------+
у неё такие индексы (несущественные поля описания я убрал для удобства):
mysql> show index from session;
+---------+-----+----------------+----------------+-----+--------+---------+
| Table   | N_u | Key_name       | Column_name    | Col | Cardin | Index_t |
+---------+-----+----------------+----------------+-----+--------+---------+
| session |   0 | PRIMARY        | session_id     | A   | 606947 | BTREE   |
| session |   1 | session_length | session_length | A   |    350 | BTREE   |
+---------+-----+----------------+----------------+-----+--------+---------+

делаем такой запрос:

mysql> explain select session_length from session where session_length > 0;                  
+---------+-------+----------------+----------------+---------+------+--------+--------------------------+
| table   | type  | possible_keys  | key            | key_len | ref  | rows   | Extra                    |
+---------+-------+----------------+----------------+---------+------+--------+--------------------------+
| session | range | session_length | session_length |       2 | NULL | 309581 | Using where; Using index |
+---------+-------+----------------+----------------+---------+------+--------+--------------------------+

здесь, как видим, всё нормально, индекс используется...
Теперь добавим в выборку поле session_id:

mysql> explain select session_id, session_length from session where session_length > 0;
+---------+------+----------------+------+---------+------+--------+-------------+
| table   | type | possible_keys  | key  | key_len | ref  | rows   | Extra       |
+---------+------+----------------+------+---------+------+--------+-------------+
| session | ALL  | session_length | NULL |    NULL | NULL | 606947 | Using where |
+---------+------+----------------+------+---------+------+--------+-------------+

Опачки! Объясните, какого хрена он вдруг не захотел юзать очевидный индекс, какого хрена он полез делать фулскан? То же самое будет, если поменять в этом тесте поля session_id и session_length местами. Я пробовал воздействовать на таблицу с помощью myisamchk - ничего не помогает.
Поэкспериментировав с вариациями запросов выделил следующие ситуации:

mysql> explain select session_id, session_length from session where session_length > 3;
+---------+-------+----------------+----------------+---------+------+-------+-------------+
| table   | type  | possible_keys  | key            | key_len | ref  | rows  | Extra       |
+---------+-------+----------------+----------------+---------+------+-------+-------------+
| session | range | session_length | session_length |       2 | NULL | 80225 | Using where |
+---------+-------+----------------+----------------+---------+------+-------+-------------+

т.е. когда мы увеличили нижнюю границу поиска до 3, то индекс он юзать стал, причём именно с трёх и выше, 1 и 2 - то же самое (не работает).

mysql> explain select session_id, session_length from session where session_length = 0;
+---------+------+----------------+----------------+---------+-------+--------+-------+
| table   | type | possible_keys  | key            | key_len | ref   | rows   | Extra |
+---------+------+----------------+----------------+---------+-------+--------+-------+
| session | ref  | session_length | session_length |       2 | const | 297366 |       |
+---------+------+----------------+----------------+---------+-------+--------+-------+

т.е. если знак "больше" меняем на знак "равно" к чему угодно, то индекс тоже юзается.

mysql> explain select session_id, session_length from session where session_length != 0;
+---------+------+---------------+------+---------+------+--------+-------------+
| table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+---------+------+---------------+------+---------+------+--------+-------------+
| session | ALL  | NULL          | NULL |    NULL | NULL | 606947 | Using where |
+---------+------+---------------+------+---------+------+--------+-------------+

т.е. для "не равно" поведение такое же, как и для "больше".

Эксперименты также показали, что изменение порядка полей в выборке, или убирании session_length из неё не влияют на поведение.  ведут себя одинаково - проверял.

Помогите, плиз, в реальной задаче у меня таблица на несколько миллионов, причем она ещё и объединяется с ещё более несколькомиллионной, так что фулскан тут совсем не в тему. Как с этим бороться?

Заранее всем спасибо.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

 Оглавление

Индекс форумов | Темы | Пред. тема | След. тема
Сообщения по теме

1. "Или оптимизатор MySQL тупит, или я"
Сообщение от Xela emailИскать по авторуВ закладки on 08-Дек-03, 11:18  (MSK)
man 5 rules of data normalization
  Рекомендовать в FAQ | Cообщить модератору | Наверх

2. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 12:26  (MSK)
>man 5 rules of data normalization

простите, а что вы хотели сказать этой глубокой ёмкой фразой?
где это есть такой ман?
и причём тут вообще нормализация, когда речь идёт об одной таблице, состоящей из двух полей?

  Рекомендовать в FAQ | Cообщить модератору | Наверх

3. "Или оптимизатор MySQL тупит, или я"
Сообщение от Xela emailИскать по авторуВ закладки on 08-Дек-03, 12:38  (MSK)
>>man 5 rules of data normalization
>где это есть такой ман?
в ДНК

>и причём тут вообще нормализация, когда речь идёт об одной таблице, состоящей
>из двух полей?

Хоть из пяти. Если изначально система собрана не правильно, то постичь, а тем паче понять, ее не возможно.

Простите, есди чем-то задел.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

4. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 12:58  (MSK)
>>>man 5 rules of data normalization
>>где это есть такой ман?
>в ДНК
что такое ДНК? я только такую расшифровку знаю - ДизоксирибоНуклеиновая Кислота :)

>Хоть из пяти. Если изначально система собрана не правильно, то постичь, а
>тем паче понять, ее не возможно.
>
>Простите, есди чем-то задел.

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

И ещё - я в курсе, что есть такое понятие, как нормализация, но где найти этот ман всё равно скажите - интересно.
Спасибо.
Вопрос остаётся открытым.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

5. "Или оптимизатор MySQL тупит, или я"
Сообщение от Xela emailИскать по авторуВ закладки on 08-Дек-03, 13:15  (MSK)
Нет. Не так. проблема действительно очевидна. И заключается в том,
что так как сделано --- сделано не правильно.

По этому, ожидать в данной ситуации предсказуемого поведения неразумно.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

6. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 14:19  (MSK)
>Нет. Не так. проблема действительно очевидна. И заключается в том,
>что так как сделано --- сделано не правильно.

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

Что всё-таки такое - ДНК?

З.Ы.: Я не ищу церебрального секса, я ищу помощи знающих людей.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

7. "Или оптимизатор MySQL тупит, или я"
Сообщение от Xela emailИскать по авторуВ закладки on 08-Дек-03, 14:37  (MSK)
дело-то не в том, а в том, что неправльно данные размещены.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

8. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 14:48  (MSK)
>дело-то не в том, а в том, что неправльно данные размещены.

:)) это уже даже забавным становится. Вам сколько лет вообще? Такое ощущение, что вы вчера прочитали что-то про sql, а теперь кидаете фразы из книжки. Вы вообще потрудились прочитать суть проблемы?
Ну хорошо, допустим вышесказанное - моё заблуждение...
Как можно изменить таблицу
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| session_id     | int(10) unsigned     |      | PRI | 0       |       |
| session_length | smallint(5) unsigned |      | MUL | 0       |       |
+----------------+----------------------+------+-----+---------+-------+
чтобы всё работало правильно? а?
объясните! слабо?

з.ы.: повторяю: я - не любитель церебрального секса :)

  Рекомендовать в FAQ | Cообщить модератору | Наверх

9. "Или оптимизатор MySQL тупит, или я"
Сообщение от Xela emailИскать по авторуВ закладки on 08-Дек-03, 15:43  (MSK)
я конечно понимаю, что мерение письками и тому подобное, это ЛОР.
Но никогда я не думал, что здесь встречу такое.

Но. Если Вам уж так интересно, то мне 25.
Работаю я в инвестиционой компании. С SQL-ем знаком не по наслышке.
А вот, если Вы не можете понять, о чем с Вами речь ведут, то либо Вы малограмотны, либо одно из двух.

ЗЫ. Ничего личного

  Рекомендовать в FAQ | Cообщить модератору | Наверх

11. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 15:52  (MSK)
>Но. Если Вам уж так интересно, то мне 25.
>Работаю я в инвестиционой компании. С SQL-ем знаком не по наслышке.
>А вот, если Вы не можете понять, о чем с Вами речь
>ведут, то либо Вы малограмотны, либо одно из двух.

наверное всё-таки малограмотный...

  Рекомендовать в FAQ | Cообщить модератору | Наверх

18. "Или оптимизатор MySQL тупит, или я"
Сообщение от OldOwl Искать по авторуВ закладки on 09-Дек-03, 14:21  (MSK)
>>Но. Если Вам уж так интересно, то мне 25.
>>Работаю я в инвестиционой компании. С SQL-ем знаком не по наслышке.
>>А вот, если Вы не можете понять, о чем с Вами речь
>>ведут, то либо Вы малограмотны, либо одно из двух.
>
>наверное всё-таки малограмотный...

Да нет, не отчаивайтесь, дружище! Как минимум, не менее грамотный, чем Ваш собеседник ;-) Во всяком случае, Вы, в отличие от него, понимаете, что нормализацией тут вовсе и не пахнет. У товарища просто было желание засветиться в форуме да рисануться, пытаясь указанием места работы аргументировать свою многограмотность (за неимением других аргументов).
А пахнет тут на самом деле вот чем. Оптимизатор запросов смотрит не только на схему данных, но и оценивает т.н. "стоимость" запроса, учитывая, в том числе, и сами данные, находящиеся в таблице.  У Вас, видимо, количество строк с session_length = 0 сравнительно небольшое (по отношению к общему кол-ву записей), т.е. большая часть записей удовлетворяет Вашему условию (session_length > 0). В такой ситуации использовать индекс - неоправданная трата времени. Дешевле (читай - быстрее) пробежать всю таблицу БЕЗ индекса, и отсеять те несколько строк, где session_length = 0, чем
скакать по таблице туда-сюда, руководствуясь индексом. Вам-то все-равно не важно, в какой последовательности выводить данные! Можете проверить и дополнить запрос "... ORDER BY session_length", и увидете, что оптимизатор не так глуп, и индекс подхватит.
А вот когда вы ставите session_length > 3, то их количество уже, видимо, _существенно_ меньше, чем общее количество, и ситуация меняется.
Чтобы убедиться во всем вышесказанном, достаточно попробовать добавить в таблицу энное количество строк с session_length = 0, и тот же самый запрос у Вас "вдруг" станет использовать индекс.
Так что все у Вас в порядке.
Удачи!

  Рекомендовать в FAQ | Cообщить модератору | Наверх

19. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 09-Дек-03, 14:30  (MSK)
>А пахнет тут на самом деле вот чем. Оптимизатор запросов смотрит не только на схему данных, но и оценивает т.н. "стоимость" запроса, учитывая, в том числе, и сами данные, находящиеся в таблице.  У Вас, видимо, количество строк с session_length = 0 сравнительно небольшое (по отношению к общему кол-ву записей), т.е. большая часть записей удовлетворяет Вашему условию (session_length > 0). В такой ситуации использовать индекс - неоправданная трата времени. Дешевле (читай - быстрее) пробежать всю таблицу БЕЗ индекса, и отсеять те несколько строк, где session_length = 0, чем
>скакать по таблице туда-сюда, руководствуясь индексом. Вам-то все-равно не важно, в какой
>последовательности выводить данные! Можете проверить и дополнить запрос "... ORDER BY
>session_length", и увидете, что оптимизатор не так глуп, и индекс подхватит.
>
>А вот когда вы ставите session_length > 3, то их количество уже, видимо, _существенно_ меньше, чем общее количество, и ситуация меняется.
>Чтобы убедиться во всем вышесказанном, достаточно попробовать добавить в таблицу энное количество
>строк с session_length = 0, и тот же самый запрос у
>Вас "вдруг" станет использовать индекс.
>Так что все у Вас в порядке.
>Удачи!

Спасибо, именно к этому я и пришёл (см ниже). Вас немного опередил мой товарищ по асе. :) Но всё равно спасибо.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

10. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 15:48  (MSK)
Люди, помогите, а? А то мне тут пытаются мозг ипать.
  Рекомендовать в FAQ | Cообщить модератору | Наверх

12. "Или оптимизатор MySQL тупит, или я"
Сообщение от Soldier Искать по авторуВ закладки on 08-Дек-03, 20:22  (MSK)
Я MySQL практически не пользую, поэтому не знаю как в нем, но например, в Interbase/Firebird существует возможность самому задавать план запроса и если бы все это было в Interbase/Firebird, то можно было переписать запрос примерно так:

select session_id, session_length from session where session_length > 3
PLAN (session index(primary,session_length))

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

select session_id, session_length from session where session_length > 3
PLAN (session index(session_length,primary))

Здесь наоборот, поиск сначала по индексу session_length, затем по первичному ключу


Естественно, вместо primary, нужно было бы указать имя первичного ключа таблицы session

Вобщем посмотрите, может и у MySQL есть аналогичная возможность, самому задавать план запроса

  Рекомендовать в FAQ | Cообщить модератору | Наверх

13. "Или оптимизатор MySQL тупит, или я"
Сообщение от Soldier Искать по авторуВ закладки on 08-Дек-03, 20:39  (MSK)
Что то я просмотрел, что session_id не участвует в условиях выборки,
поэтому план должен быть просто:

PLAN (session index(session_length))

  Рекомендовать в FAQ | Cообщить модератору | Наверх

14. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 08-Дек-03, 20:43  (MSK)
>Что то я просмотрел, что session_id не участвует в условиях выборки,
>поэтому план должен быть просто:
>
>PLAN (session index(session_length))

да, это не суть

есть у MySQL такие штуки как USE INDEX и IGNORE INDEX, но они рассматриваются как подсказки, а не как руководство к действию
я пробовал - не помогает :(

  Рекомендовать в FAQ | Cообщить модератору | Наверх

15. "Или оптимизатор MySQL тупит, или я"
Сообщение от Soldier Искать по авторуВ закладки on 09-Дек-03, 07:14  (MSK)
>>Что то я просмотрел, что session_id не участвует в условиях выборки,
>>поэтому план должен быть просто:
>>
>>PLAN (session index(session_length))
>
>да, это не суть
>
>есть у MySQL такие штуки как USE INDEX и IGNORE INDEX, но
>они рассматриваются как подсказки, а не как руководство к действию
>я пробовал - не помогает :(

А FORCE INDEX не пробовали?
А в Interbase PLAN - это именно руководство к действию.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

17. "Или оптимизатор MySQL тупит, или я"
Сообщение от Davojan emailИскать по авторуВ закладки on 09-Дек-03, 12:01  (MSK)
>А FORCE INDEX не пробовали?
>А в Interbase PLAN - это именно руководство к действию.

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

фишка в другом.
на самом деле оптимизатор был прав...
дело в том, что нулевых и ненулевых значений session_length в таблице примерно поровну. Соответственно mysql решил лучше пробежаться один раз по таблице, чем сканить индекс и хаотически выбирать записи из таблицы по нему. Спасибо знающим людям, которые на это указали.
Это подтвердил и бенчмарк, который я незамедлительно провёл.
Запрос с FORCE INDEX оказался примерно в 4 раза медленнее, чем без него.

Всё. Всем спасибо. Вопрос закрыт.

  Рекомендовать в FAQ | Cообщить модератору | Наверх

16. "Или оптимизатор MySQL тупит, или я"
Сообщение от switch emailИскать по авторуВ закладки on 09-Дек-03, 11:48  (MSK)
Сделай optimize table, есть вариант, что он сам потом правельно выберет вариант подбора индекса при запросе.

Теперь у меня вопрос, вчера я пытался отослать этот же ответ в майл-листе, ушло письмо 2 мегабайта, оказывается thenderbird если в него скопировать кусочек HTML, который содержит ссылку, то он лепит в него всю страницу. А страница получилась - man mysql_toc :-(. Ребята теперб заглючил майл-лист и шлет мне ответные письма размеров 1268Кб. Уже второй день шлет.

Подскажите как выйте на админа рассылки, чтобы прикоатидся этот бред.
А то получается что это грубйший СПАМ, меня с роботы уволят, если так дело пойдет.

МОМОГИТЕ НАЙТИ ЭТОТ АДМИНА, ПОЖАЛЙУСТА!!!!

  Рекомендовать в FAQ | Cообщить модератору | Наверх


Удалить

Индекс форумов | Темы | Пред. тема | След. тема
Пожалуйста, прежде чем написать сообщение, ознакомьтесь с данными рекомендациями.




Спонсоры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2022 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру