Люди, разбирающиеся в оптимизации запросов 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 из неё не влияют на поведение. ведут себя одинаково - проверял.
Помогите, плиз, в реальной задаче у меня таблица на несколько миллионов, причем она ещё и объединяется с ещё более несколькомиллионной, так что фулскан тут совсем не в тему. Как с этим бороться?
Заранее всем спасибо.
man 5 rules of data normalization
>man 5 rules of data normalizationпростите, а что вы хотели сказать этой глубокой ёмкой фразой?
где это есть такой ман?
и причём тут вообще нормализация, когда речь идёт об одной таблице, состоящей из двух полей?
>>man 5 rules of data normalization
>где это есть такой ман?
в ДНК>и причём тут вообще нормализация, когда речь идёт об одной таблице, состоящей
>из двух полей?Хоть из пяти. Если изначально система собрана не правильно, то постичь, а тем паче понять, ее не возможно.
Простите, есди чем-то задел.
>>>man 5 rules of data normalization
>>где это есть такой ман?
>в ДНК
что такое ДНК? я только такую расшифровку знаю - ДизоксирибоНуклеиновая Кислота :)>Хоть из пяти. Если изначально система собрана не правильно, то постичь, а
>тем паче понять, ее не возможно.
>
>Простите, есди чем-то задел.Ничем, просто проблема локальна, ясна и очевидна.
Речь идёт не о глобальной концепции системы, а о конкретном запросе к конкретной таблице, меня интересует - почему он не работает так, как я ожидаю и как заставить его работать правильно.И ещё - я в курсе, что есть такое понятие, как нормализация, но где найти этот ман всё равно скажите - интересно.
Спасибо.
Вопрос остаётся открытым.
Нет. Не так. проблема действительно очевидна. И заключается в том,
что так как сделано --- сделано не правильно.По этому, ожидать в данной ситуации предсказуемого поведения неразумно.
>Нет. Не так. проблема действительно очевидна. И заключается в том,
>что так как сделано --- сделано не правильно.я для этого сюда и пришёл, чтобы спросить - что сделано неправильно? Будьте поконкретнее, плиз, иначе ваши заявления - не более чем пустышки.
Что всё-таки такое - ДНК?
З.Ы.: Я не ищу церебрального секса, я ищу помощи знающих людей.
дело-то не в том, а в том, что неправльно данные размещены.
>дело-то не в том, а в том, что неправльно данные размещены.:)) это уже даже забавным становится. Вам сколько лет вообще? Такое ощущение, что вы вчера прочитали что-то про sql, а теперь кидаете фразы из книжки. Вы вообще потрудились прочитать суть проблемы?
Ну хорошо, допустим вышесказанное - моё заблуждение...
Как можно изменить таблицу
+----------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| session_id | int(10) unsigned | | PRI | 0 | |
| session_length | smallint(5) unsigned | | MUL | 0 | |
+----------------+----------------------+------+-----+---------+-------+
чтобы всё работало правильно? а?
объясните! слабо?з.ы.: повторяю: я - не любитель церебрального секса :)
я конечно понимаю, что мерение письками и тому подобное, это ЛОР.
Но никогда я не думал, что здесь встречу такое.Но. Если Вам уж так интересно, то мне 25.
Работаю я в инвестиционой компании. С SQL-ем знаком не по наслышке.
А вот, если Вы не можете понять, о чем с Вами речь ведут, то либо Вы малограмотны, либо одно из двух.ЗЫ. Ничего личного
>Но. Если Вам уж так интересно, то мне 25.
>Работаю я в инвестиционой компании. С SQL-ем знаком не по наслышке.
>А вот, если Вы не можете понять, о чем с Вами речь
>ведут, то либо Вы малограмотны, либо одно из двух.наверное всё-таки малограмотный...
>>Но. Если Вам уж так интересно, то мне 25.
>>Работаю я в инвестиционой компании. С SQL-ем знаком не по наслышке.
>>А вот, если Вы не можете понять, о чем с Вами речь
>>ведут, то либо Вы малограмотны, либо одно из двух.
>
>наверное всё-таки малограмотный...Да нет, не отчаивайтесь, дружище! Как минимум, не менее грамотный, чем Ваш собеседник ;-) Во всяком случае, Вы, в отличие от него, понимаете, что нормализацией тут вовсе и не пахнет. У товарища просто было желание засветиться в форуме да рисануться, пытаясь указанием места работы аргументировать свою многограмотность (за неимением других аргументов).
А пахнет тут на самом деле вот чем. Оптимизатор запросов смотрит не только на схему данных, но и оценивает т.н. "стоимость" запроса, учитывая, в том числе, и сами данные, находящиеся в таблице. У Вас, видимо, количество строк с session_length = 0 сравнительно небольшое (по отношению к общему кол-ву записей), т.е. большая часть записей удовлетворяет Вашему условию (session_length > 0). В такой ситуации использовать индекс - неоправданная трата времени. Дешевле (читай - быстрее) пробежать всю таблицу БЕЗ индекса, и отсеять те несколько строк, где session_length = 0, чем
скакать по таблице туда-сюда, руководствуясь индексом. Вам-то все-равно не важно, в какой последовательности выводить данные! Можете проверить и дополнить запрос "... ORDER BY session_length", и увидете, что оптимизатор не так глуп, и индекс подхватит.
А вот когда вы ставите session_length > 3, то их количество уже, видимо, _существенно_ меньше, чем общее количество, и ситуация меняется.
Чтобы убедиться во всем вышесказанном, достаточно попробовать добавить в таблицу энное количество строк с session_length = 0, и тот же самый запрос у Вас "вдруг" станет использовать индекс.
Так что все у Вас в порядке.
Удачи!
>А пахнет тут на самом деле вот чем. Оптимизатор запросов смотрит не только на схему данных, но и оценивает т.н. "стоимость" запроса, учитывая, в том числе, и сами данные, находящиеся в таблице. У Вас, видимо, количество строк с session_length = 0 сравнительно небольшое (по отношению к общему кол-ву записей), т.е. большая часть записей удовлетворяет Вашему условию (session_length > 0). В такой ситуации использовать индекс - неоправданная трата времени. Дешевле (читай - быстрее) пробежать всю таблицу БЕЗ индекса, и отсеять те несколько строк, где session_length = 0, чем
>скакать по таблице туда-сюда, руководствуясь индексом. Вам-то все-равно не важно, в какой
>последовательности выводить данные! Можете проверить и дополнить запрос "... ORDER BY
>session_length", и увидете, что оптимизатор не так глуп, и индекс подхватит.
>
>А вот когда вы ставите session_length > 3, то их количество уже, видимо, _существенно_ меньше, чем общее количество, и ситуация меняется.
>Чтобы убедиться во всем вышесказанном, достаточно попробовать добавить в таблицу энное количество
>строк с session_length = 0, и тот же самый запрос у
>Вас "вдруг" станет использовать индекс.
>Так что все у Вас в порядке.
>Удачи!Спасибо, именно к этому я и пришёл (см ниже). Вас немного опередил мой товарищ по асе. :) Но всё равно спасибо.
Люди, помогите, а? А то мне тут пытаются мозг ипать.
Я 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 есть аналогичная возможность, самому задавать план запроса
Что то я просмотрел, что session_id не участвует в условиях выборки,
поэтому план должен быть просто:PLAN (session index(session_length))
>Что то я просмотрел, что session_id не участвует в условиях выборки,
>поэтому план должен быть просто:
>
>PLAN (session index(session_length))да, это не суть
есть у MySQL такие штуки как USE INDEX и IGNORE INDEX, но они рассматриваются как подсказки, а не как руководство к действию
я пробовал - не помогает :(
>>Что то я просмотрел, что session_id не участвует в условиях выборки,
>>поэтому план должен быть просто:
>>
>>PLAN (session index(session_length))
>
>да, это не суть
>
>есть у MySQL такие штуки как USE INDEX и IGNORE INDEX, но
>они рассматриваются как подсказки, а не как руководство к действию
>я пробовал - не помогает :(А FORCE INDEX не пробовали?
А в Interbase PLAN - это именно руководство к действию.
>А FORCE INDEX не пробовали?
>А в Interbase PLAN - это именно руководство к действию.спасибо, попробовал - помогло
не знал, что такая штука есть у mysql - нигде в манах не встречал
но...фишка в другом.
на самом деле оптимизатор был прав...
дело в том, что нулевых и ненулевых значений session_length в таблице примерно поровну. Соответственно mysql решил лучше пробежаться один раз по таблице, чем сканить индекс и хаотически выбирать записи из таблицы по нему. Спасибо знающим людям, которые на это указали.
Это подтвердил и бенчмарк, который я незамедлительно провёл.
Запрос с FORCE INDEX оказался примерно в 4 раза медленнее, чем без него.Всё. Всем спасибо. Вопрос закрыт.
Сделай optimize table, есть вариант, что он сам потом правельно выберет вариант подбора индекса при запросе.Теперь у меня вопрос, вчера я пытался отослать этот же ответ в майл-листе, ушло письмо 2 мегабайта, оказывается thenderbird если в него скопировать кусочек HTML, который содержит ссылку, то он лепит в него всю страницу. А страница получилась - man mysql_toc :-(. Ребята теперб заглючил майл-лист и шлет мне ответные письма размеров 1268Кб. Уже второй день шлет.
Подскажите как выйте на админа рассылки, чтобы прикоатидся этот бред.
А то получается что это грубйший СПАМ, меня с роботы уволят, если так дело пойдет.МОМОГИТЕ НАЙТИ ЭТОТ АДМИНА, ПОЖАЛЙУСТА!!!!