Проблема с сабжем в том, что mysql сортирует сначала все выбранные данные, а потом отдает кусочек, который ты указал в limit. Соответстветственно, даже при наличии индекса по полю сортировки он выпадает в filesort если в таблице есть крупные поля.
Обычный подход состоит в том, чтобы сделать задуманое двумя запросами: первым (легким) выгрести только id отсортированных записей и избежать filesort, а вторым уже получить полные записи для этих id.
Ну, например, вот так:
class FoobarManager(models.Manager): def latest(self, limit=10): """ more effecient version of query to avoid filesort """ ids = list(self.values_list('id', flat=True).order_by('-modified')[:limit]) return self.filter(id__in=ids).extra( select={'manual': 'FIELD(id,%s)' % ','.join(map(str, ids))}, order_by=['manual'])
Идея насчет реализации order_by(‘manual’) взята отсюда, а сама необходимость в нем описана вот тут.
Можно не делать order_by(‘manual’), а сделать повторный order_by(‘-modified’) при желании. По идее, производительность не пострадает.
Второй способ решения этой проблемы — вынос больших текстовых полей в отдельную таблицу, выбирать из которой можно по связке one2one.
В связи с этим возникает идея использовать Django MultiTable Inheritance и класть в таблицу предка мелкие поля, в наследника — крупные, а уж запрос Django ORM должен и сам сгенерировать довольно оптимальный. Как–то оно попрозрачнее получится. Надо попробовать, короче
16.11.2010 в 16:48
IMO cамый правильный способ решения этой (и не только этой) проблемы – сортировать по id всегда, когда возможно. Например, зачем сортировать по created (которое растет вместе с auto increment primary key), если можно сортировать по id и получить тот же эффект без дополнительных индексов и filesort:
Foobar.objects.order_by(‘-id’)[:limit]
Если все-таки надо сортировать по полю изменяющемося незавиcимо от id, то для простых запросов типа:
Foobar.objects.order_by(‘-last_modified’)[:limit]
MySQL должен использовать индекс по last_modified и возвращать данные из начала этого индекса (без сортировки всего result set и независимо от наличия крупных полей в таблице). Если EXPLAIN всеже показывает filesort, надо смотреть EXPLAIN, индексы и настройки сервера. Например при малом числе записей или одинаковых значениях в столбце планировщик запросов может решить использовать не тот индекс, что надо. Надо смотреть конкретные SQL-запросы и убирать filesort.
Использование Django MultiTable Inheritance вместе с MySQL мне представляется неоправданым. Django MultiTable Inheritance очевидно разработана с прицелом на PgSQL, который поддерживает наследование таблиц и является основным СУБД для django. В MySQL наследование таблиц будет только эмулироваться со всеми вытекающими последствиями в виде необходимости использования транзакций при сохранении одной модели в двух таблицах, дополнительного one2one join и усложения слоя хранения данных.
16.11.2010 в 17:47
Сортировка по pk и по индексированому полю в принципе не сильно отличается (хотя и появляется оверхед в виде дополнительного индекса).
Насчет created, конечно, я погорячился, пример не совсем корректный, с другой стороны, если это будет поле modified, то сортировка по id уже не спасет (а у меня, похоже, так и будет). Кстати, Артем подобное использует в md, для оптимизации сортировок.
Сейчас еще поэкспериментирую.
Насчет MultiTable inheritance ты прав в том, что в целом это не идеальный инструмент. Но моя идея заключается в том, чтобы использовать его для прозрачных запросов к сущностям, которые разнесены по двум таблицам (в первой поля для лукапа и сортировки, во второй — тяжелые blob и text). По сути, это тоже самое, что я делаю в менеджере модели, только прозрачнее.