Оптимизация SELECT с ORDER BY и LIMITКак избавиться от using where и using filesort
Текущий рейтинг темы: Нет
4X_Pro
Руководитель Проекта
Настоящий Компьютерщик
Откуда: Москва Всего сообщений: 2994 Рейтинг пользователя: 79 Дата регистрации на форуме: 29 сен. 2001
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 25 октября 2006 21:58
Недавно столкнулся со следующей ситуацией: выполнял выборку из нескольких таблиц с использованием сортировки и ограничением числа извлеченных записей (т.е. оператор имел вид SELECT ... ORDER BY ... LIMIT ...) и запрос получался слишком медленным.
Когда я рассмотрел запрос с помощью EXPLAIN, то обнаружилось, что в нем возникает using where и using filesort.
Я долго не мог понять, как с этим бороться, а потом обнаружил статью на эту тему: http://hackmysql.com/case5. (Кстати, на сайте есть еще несколько интересных статей и утилит.)
Кратко поясню ее суть: столбец, по которому производится сортировка, должен быть отиндексирован, а сама таблица — быть самой первой в порядке выборки. (Наилучший вариант — это когда к ней другие таблицы присоединяются по LEFT JOIN.)
--- Каждый человек всегда может найти, чем он может быть полезен окружающим. Проблема только в одном: слишком многие не хотят это искать.
| | |
wsx
Модератор раздела
Юниксойд, сетевик
Откуда: Казань Всего сообщений: 1074 Рейтинг пользователя: 28 Репутация пользователя: 1Дата регистрации на форуме: 14 янв. 2005
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 26 октября 2006 10:08
Афигеть. Это кстати по "псевдо-стандарту" SQL или примудрости MySQL ? Тонкостей то дох и больше... | | |
4X_Pro
Руководитель Проекта
Настоящий Компьютерщик
Откуда: Москва Всего сообщений: 2994 Рейтинг пользователя: 79 Дата регистрации на форуме: 29 сен. 2001
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 26 октября 2006 13:53
Стандарт здесь не причем. Заморочки чисто в реализиации в MySQL.
Хотя, думаю, и в PostgreSQL есть какие-нибудь подобные заморочки.
--- Каждый человек всегда может найти, чем он может быть полезен окружающим. Проблема только в одном: слишком многие не хотят это искать.
| | |
4X_Pro
Руководитель Проекта
Настоящий Компьютерщик
Откуда: Москва Всего сообщений: 2994 Рейтинг пользователя: 79 Дата регистрации на форуме: 29 сен. 2001
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 4 февраля 2007 17:10
Вот еще один важный момент: если выбирается запись только одной записи из нескольких таблиц со связью по JOIN, имеет смысл явно указать LIMIT 1, чтобы поиск велся до первой найденной записи (особенно в тех случаях, когда индексы для связи — неуникальные).
--- Каждый человек всегда может найти, чем он может быть полезен окружающим. Проблема только в одном: слишком многие не хотят это искать.
| | |
4X_Pro
Руководитель Проекта
Настоящий Компьютерщик
Откуда: Москва Всего сообщений: 2994 Рейтинг пользователя: 79 Дата регистрации на форуме: 29 сен. 2001
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 8 мая 2007 23:01
И еще два важных уточнения:
1) в индексе столбец, по которому идет сортировка, должен быть первым
2) не следует забывать, что если условие выборки недостаточно жесткое (т.е. количество записей, выбираемых по индексу не превышает 30%), индексы не учитываются, и EXPLAIN все равно пишет using filesort, using where. Оффтопик: Я полдня потратил на это, пока понял, в чем тут дело.
Ну и последнее, о чем не стоит забывать — это порядок подключения таблиц с LEFT JOIN. Если какая-то таблица является достаточно "тяжелой", и подключается только с информационными целями (т.е. мы выводим из нее какие-то поля, но она никак не участвует в выборке записей, кроме связывания по ключу) то ее лучше подключать одной из последних.
--- Каждый человек всегда может найти, чем он может быть полезен окружающим. Проблема только в одном: слишком многие не хотят это искать.
| | |
4X_Pro
Руководитель Проекта
Настоящий Компьютерщик
Откуда: Москва Всего сообщений: 2994 Рейтинг пользователя: 79 Дата регистрации на форуме: 29 сен. 2001
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 14 мая 2007 13:56
Удалось выявить еще одну рекомендацию: при создании индекса по нескольким столбцам, в одном из которых значения различаются сильно (например, вообще являются уникальными), а в другом — чередуется несколько значений (например, 0 или 1) первым нужно делать тот столбец, по которому значения различаются сильно, в противном случае возможна ситуация, когда индекс не будет использоваться вообще из-за ограничения "если в индексе более 30% значений удовлетворяют условию, индекс отбрасывается".
--- Каждый человек всегда может найти, чем он может быть полезен окружающим. Проблема только в одном: слишком многие не хотят это искать.
| | |
4X_Pro
Руководитель Проекта
Настоящий Компьютерщик
Откуда: Москва Всего сообщений: 2994 Рейтинг пользователя: 79 Дата регистрации на форуме: 29 сен. 2001
|
Профиль | Сообщить модератору | ИгнорироватьNEW! Сообщение отправлено: 15 ноября 2007 21:47
Еще одна интересная особенность MySQL: если первичный ключ является сложным (т.е. состоит из нескольких столбцов), то при выборе единственной записи по первому ключу в столбце (обычно это уникальный номер с auto_increment) тоже имеет смысл указывать явно LIMIT 1.
--- Каждый человек всегда может найти, чем он может быть полезен окружающим. Проблема только в одном: слишком многие не хотят это искать.
| | |
Время выполнения скрипта: 0.0293. Количество выполненных запросов: 18, время выполнения запросов 0.0195
|