Wicked
Новичок
Оптимизация запроса
Требуется: выводить пользователю аналог френдленты с постами, отсортированными по дате.
Упрощенный вариант запроса, который это делает (limit - для второй страницы):
[sql]select
`id`,
`title`
from
`ext_feeder_item`
where
`ext_feeder_item`.`feed_id` in (31,1291, ... /* всего 346 id-шников фидов */ )
order by ext_feeder_item.pub_date DESC
limit 10, 10;[/sql]
Как видно, делает он свою работу неприемлемо с точки зрения производительности (у рассматриваемого юзера всего около 32K постов во френдленте - не сильно много).
Насколько я понимаю структуру индексов MySQL (в частности, деревьев B-tree), они не предназначена для выборок типа
where key_part_1 in (много разных значений) order by key_part_2, поэтому индекс (`feed_id`, `pub_date`) игнорируется.
То же самое можно сказать про where key_part_2 in (...) order by key_part_1.
Поэтому у меня напрашивается решение - для каждого юзера кэшировать некоторую дату, отделяющую первые 100 сообщений по всем его френдам-фидам. Таким образом можно будет использовать запрос типа
[sql]select
`id`,
`title`
from
`ext_feeder_item`
where
`ext_feeder_item`.`feed_id` in ( 31, 1291, . . ./* всего 346 id-шников фидов */ )
and
ext_feeder_item.pub_date > '2008-01-22 00:00:00'
order by ext_feeder_item.pub_date DESC
limit 10, 10;[/sql]который дает куда более хорошие результаты для первых десяти страниц (без лимита этот запрос выгребает 100 записей):
Но меня смущает несколько моментов...
1) эту дату нужно обновлять когда происходит одно из событий:
1.1) добавление нового поста в любом из фидов юзера - не обязательно делать сразу, т.к. если эта дата будет меньше актуального, то скоростные характеристики упадут совсем чуть-чуть.
1.2) добавление нового фида - как в 1.1.
1.3) удаление фида - надо делать сразу, т.к. сохраненная дата будет больше, чем нужно, и будет, например, обрезать кол-во постов до 90, а не до 100.
1.4) ...?
2) файлсорт будет реально ссыпаться на диск, т.к. в оригинальном запросе достаются поля типа TEXT.
3) эта дата дает хороший прирост скорости только на первых 10 страницах. Дальше - опа
3.1) чтобы сделать это работающим на всем множестве запросов, придется таких дат запоминать побольше (для данного юзера - 32k/100 = 320 штук), причем их нужно будет обновлять уже синхронно. И когда будет добавляться пост в какой-нибудь популярный фид, это может выливаться каждый раз в кол-во обновлений = 320 дат на юзера * 1000 юзеров = 320к... А это слишком дофига, чтобы делать это на каждый чих.
Есть какие-нибудь мысли, как это можно сделать более-менее красиво и быстро с точки зрения производительности как вставок-обновлений, так и выборок?
Требуется: выводить пользователю аналог френдленты с постами, отсортированными по дате.
Упрощенный вариант запроса, который это делает (limit - для второй страницы):
[sql]select
`id`,
`title`
from
`ext_feeder_item`
where
`ext_feeder_item`.`feed_id` in (31,1291, ... /* всего 346 id-шников фидов */ )
order by ext_feeder_item.pub_date DESC
limit 10, 10;[/sql]
Код:
+----+-------------+-----------------+-------+---------------+-------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | ext_feeder_item | range | feed_id_idx | feed_id_idx | 8 | NULL | 32165 | Using where; Using filesort |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+-------+-----------------------------+Насколько я понимаю структуру индексов MySQL (в частности, деревьев B-tree), они не предназначена для выборок типа
where key_part_1 in (много разных значений) order by key_part_2, поэтому индекс (`feed_id`, `pub_date`) игнорируется.
То же самое можно сказать про where key_part_2 in (...) order by key_part_1.
Поэтому у меня напрашивается решение - для каждого юзера кэшировать некоторую дату, отделяющую первые 100 сообщений по всем его френдам-фидам. Таким образом можно будет использовать запрос типа
[sql]select
`id`,
`title`
from
`ext_feeder_item`
where
`ext_feeder_item`.`feed_id` in ( 31, 1291, . . ./* всего 346 id-шников фидов */ )
and
ext_feeder_item.pub_date > '2008-01-22 00:00:00'
order by ext_feeder_item.pub_date DESC
limit 10, 10;[/sql]который дает куда более хорошие результаты для первых десяти страниц (без лимита этот запрос выгребает 100 записей):
Код:
+----+-------------+-----------------+-------+------------------------+-------------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+------------------------+-------------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | ext_feeder_item | range | ..., feed_id__pub_date | feed_id__pub_date | 17 | NULL | 259 | Using where; Using filesort |
+----+-------------+-----------------+-------+------------------------+-------------------+---------+------+------+-----------------------------+1) эту дату нужно обновлять когда происходит одно из событий:
1.1) добавление нового поста в любом из фидов юзера - не обязательно делать сразу, т.к. если эта дата будет меньше актуального, то скоростные характеристики упадут совсем чуть-чуть.
1.2) добавление нового фида - как в 1.1.
1.3) удаление фида - надо делать сразу, т.к. сохраненная дата будет больше, чем нужно, и будет, например, обрезать кол-во постов до 90, а не до 100.
1.4) ...?
2) файлсорт будет реально ссыпаться на диск, т.к. в оригинальном запросе достаются поля типа TEXT.
3) эта дата дает хороший прирост скорости только на первых 10 страницах. Дальше - опа
3.1) чтобы сделать это работающим на всем множестве запросов, придется таких дат запоминать побольше (для данного юзера - 32k/100 = 320 штук), причем их нужно будет обновлять уже синхронно. И когда будет добавляться пост в какой-нибудь популярный фид, это может выливаться каждый раз в кол-во обновлений = 320 дат на юзера * 1000 юзеров = 320к... А это слишком дофига, чтобы делать это на каждый чих.
Есть какие-нибудь мысли, как это можно сделать более-менее красиво и быстро с точки зрения производительности как вставок-обновлений, так и выборок?
