Как правильно составить SQL запрос?

Question

[Дмитрий Масленников]

Привет всем!

Как составить наиболее производительный запрос к БД (MySQL, InnoDB) в следующей ситуации. Имеется таблица USER_POSTS, где хранятся данные о постах пользователей. В таблице порядка 20 млн. записей о постах пользователей, размер 20Gb, у каждого пользователя в среднем 3000 постов (данные собираются из других социальных сетей). Требуется выводить по 20 записей на страницу, отбираемых по OWNER_ID (владелец профиля) и сортируемых по CREATED (дате создания). По OWNER_ID и CREATED выставлены индексы.

Наиболее простое решение в моем случае будет следующим:

SELECT post_id, owner_id, content_text, location_name, provider_id, provider_source_id, provider_source_id_full, provider_source_domain, provider_source_link, provider_source_name, provider_source_photo, provider_post_link, attachment_0_photo, created
FROM user_posts
WHERE owner_id='30'
ORDER BY created DESC
LIMIT 20;

Проблема в том, что до попадания этого запроса в кэш MySQL, время его выполнения для меня неприемлемо. Пробовал такой вариант:

SELECT b.post_id, b.owner_id, b.content_text, b.location_name, b.provider_id, b.provider_source_id, b.provider_source_id_full, b.provider_source_domain, b.provider_source_link, b.provider_source_name, b.provider_source_photo, b.provider_post_link, b.attachment_0_photo, b.created
FROM (
   SELECT post_id
   FROM user_posts
   WHERE owner_id='30'
   ORDER BY created DESC
   LIMIT 20
) a, user_posts b
WHERE b.post_id=a.post_id;

Выдает более производительный результат, но все равно достаточно медленно. Как я понял, узким местом является сортировка по полю CREATED. Без нее запрос идет в 5-7 раз быстрее, но ее нельзя убрать. Посты по индексу POST_ID идут не в хронологическом порядке.

Как можно составить более быстрый запрос на основе этих исходных данных? Что еще можно сделать для более производительного выполнения запросов такого типа?

Answer 1

[lega]

Нужен один составной индекс на эти 2 поля, сейчас видимо у вас 2 индекса (один на поле) и соответственно они одновременно не работают.

Comments

[Дмитрий Масленников]

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

[lega]

Дмитрий Масленников: Составной индекс должен содержать обратную сортировку вторым "параметром" (первый owner_id), тогда запрос будет работать мгновенно, т.к. бд не будет вручную сортировать результат, а будет просто брать элементы из индекса по порядку.

[Дмитрий Масленников]

lega а как выставить у индекса именно обратную сортировку?

[Дмитрий Масленников]

В результате пока пришел к такому решению. Сделал дополнительное поле FLAG_SORT, которое формируется на основе даты создания (FLAG_SORT = 4294967295 - CREATED), далее по нему и OWNER_ID сформировал составной индекс. Конечно, это костыль, но с его использованием скорость обработки значительно возросла.

[lega]

Дмитрий Масленников: По идее правильно, только за вас это должна делать бд. В mysql сходу не на нашел как, а в других бд (конкретно в mongodb) это делается просто.

[Дмитрий Масленников]

Решил использовать решение из своего предыдущего комментария. Если посты генерировали только пользователи сайта, то можно вообще обойтись без сортировки (выводить по POST_ID), но они идут не в хронологическом порядке, дополнительный FLAG_SORT будет оптимальным решением

Answer 2

[Aleksey Ratnikov]

Если у вас mysql версии 5.6+, то вам поможет секционирование по полю owner_id.

Comments

[Дмитрий Масленников]

В том то все и дело, что поле owner_id обрабатывается относительно быстро. Сложность с обратной сортировкой по дате, занимает до 80% времени запроса.

[Aleksey Ratnikov]

Дмитрий Масленников: Я понял, в первом случае table scan, потому что в where у вас некалстерный индекс, а во втором случае вообще CROSS JOIN. Попробуйте переделать второый запрос следующим образом:
SELECT b.post_id, b.owner_id, b.content_text, b.location_name, b.provider_id, b.provider_source_id, b.provider_source_id_full, b.provider_source_domain, b.provider_source_link, b.provider_source_name, b.provider_source_photo, b.provider_post_link, b.attachment_0_photo, b.created
FROM (
SELECT post_id
FROM user_posts
WHERE owner_id='30'
ORDER BY created DESC
LIMIT 20
) as a inner join user_posts as b on b.post_id=a.post_id;

[Дмитрий Масленников]

Да, на высоких нагрузках это решение работает на 30-40% быстрее, чем прямой запрос без JOIN.

Answer 3

[Александр]

Оставте один индекс на owner_id

Comments

[Дмитрий Масленников]

Хм, а как тогда оптимизировать обратную сортировку по дате? Опытным путем понял, что все упирается в нее, если ее убрать, то скорость возрастает в разы. Также скорость сильно зависит от количества выборки сообщений для сортировки (3000 сообщений обрабатываются в 2 раза медленнее, чем 1000).

[Дмитрий Масленников]

Если посты поступали только от пользователей сайта, то можно все отсортировать по POST_ID (они идут в хронологическом порядке), но мои данные идут миксом (часть от пользователей, часть из других соцсетей, где они собираются пакетами через API, по времени записи идут в разном порядке).

[Александр]

Рассмотрим такой запрос:
SELECT * FROM users WHERE age = 29 AND gender = 'male'

Нам следует создать составной индекс на обе колонки:
CREATE INDEX age_gender ON users(age, gender);

Устройство составного индекса

Чтобы правильно использовать составные индексы, необходимо понять структуру их хранения. Все работает точно так же, как и для обычного индекса. Но для значений используются значений всех входящих колонок сразу. Для таблицы с такими данными:

id | name | age | gender
1 | Den | 29 | male
2 | Alyona | 15 | female
3 | Putin | 89 | tsar
4 | Petro | 12 | male

значения составного индекса будут такими:

age_gender
12male
15female
29male
89tsar

Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

[Дмитрий Масленников]

Александр Можно как-то сделать составной индекс с обратной сортировкой по полю?

[Александр]

Создайте составной индекс CREATE INDEX index_posts ON user_posts(owner_id,created).
Удалите все результаты запросов из кэша запросов. И попробуйте выполнить Ваш запрос:

SELECT post_id, owner_id, content_text, location_name, provider_id, provider_source_id, provider_source_id_full, provider_source_domain, provider_source_link, provider_source_name, provider_source_photo, provider_post_link, attachment_0_photo, created
FROM user_posts
WHERE owner_id='30'
ORDER BY created DESC
LIMIT 20;

Результат в студию.

[Дмитрий Масленников]

Пробовал, но обрабатывается также медленно. Временное решение, которое нашел - сделать дополнительное поле FLAG_SORT, которое формируется на основе даты (FLAG_SORT = 4294967295 - CREATED) и по нему сформировать составной индекс. Тогда все работает быстро. Как понимаю, MySQL при создании индексов не использует обратную сортировку, поэтому вторая часть запроса (ORDER BY) выполняется без использования индекса.

[Александр]

Используйте EXPLAIN для анализа индексов.

[Дмитрий Масленников]

Александр По EXPLAIN все хорошо, составной индекс выбирается для запроса. Судя по длине, используются обе части ключа (OWNER_ID и CREATED), но обратная сортировка куда медленнее прямой (по индексу на основе OWNER_ID и FLAG_SORT).