Как понять работу sql-запроса?

Question

[DrunkMaster]

SELECT `group_id` FROM `vkgroups` WHERE `group_id` 
IN (SELECT * FROM (SELECT `group_id` FROM `vkmembers` ORDER BY `members` DESC LIMIT 0,20) temp_tab)

Первый запрос выполняется 3 секунды, если его повторить то 0,0001 Потом меняем LIMIT например на 20.20 или 40,20 всё повторяется - первый запрос 2-3 секунды, остальные очень быстрые.
EXPLAIN:


Результаты профилирования:

Comments

[AlikDex]

это связано с кешем мускуля. Второй запрос просто кешируется, потому и быстро. На этапе разработки полезно пользоваться флагом SQL_NO_CACHE:

SELECT SQL_NO_CACHE `group_id` FROM `vkgroups` WHERE `group_id` 
IN (SELECT * FROM (SELECT `group_id` FROM `vkmembers` ORDER BY `members` DESC LIMIT 0,20) temp_tab)

[DrunkMaster]

AlikDex: Я вас понял, добавил в вопрос результаты профилирования с отключенным кешем мне интересно на что тратится время в таком несложном запросе и можно ли ускорить freeing items

Answer 1

[Алексей Ярков]

Запросы кэшируются

mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 10485760 |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 52428800 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.20 sec)

Comments

[DrunkMaster]

Отключил кеш, время уходит на freeing items

Answer 2

[Артём Каретников]

Попробуйте так. Результат одинаковый будет, но оптимизатор не станет выполнять лишний select
SELECT `group_id` FROM `vkgroups` WHERE `group_id`
IN (SELECT `group_id` FROM `vkmembers` ORDER BY `members` DESC LIMIT 0,20)

Т.е. что вижу я тут странное. Вот это подзапрос, который в скобках. Он отбирает нечто, обратно это сортирует и берет первые 20 записей из результата.
Условно, значения у вас идут от 10 до 100 - берутся записи от 100 и до 80. Фактически - возьми последние 20 записей
SELECT `group_id` FROM `vkmembers` ORDER BY `members` DESC LIMIT 0,20

Зачем делать из этого потом еще один запрос select * - не ведаю.

И совершенно непонятно, зачем брать абсолютно все данные и их потом переворачивать. Добавьте, например, условие сюда же по дате, или брать не все данные, а половину только и скорость увеличится.

Comments

[Артём Каретников]

и id_group можно еще раз в vkgroups не искать. Они есть уже, если требуется их уникальность, то можно с этими данными работать, а не лезть в другую таблицу. В общем, странное решение задачи.

[DrunkMaster]

Артем Каретников: Ваше первое предложение не прокатит, об этом был предыдущий вопрос ))) Можно ли обойти ограничение в mysql?

И совершенно непонятно, зачем брать абсолютно все данные и их потом переворачивать

Вы не поняли смысл запроса. Берутся не все данные, а 20 самых больших значений, кладутся во временную таблицу что бы обойти ограничение на LIMIT в подзапросе а потом из временной выбираются все

[Артём Каретников]

DrunkMaster: Нет, это вы не поняли. Берутся 20 последних, а не максимальных.

Давайте просто попробуем, что я предложил. Найдите в таблице vkmembers среднее значение.
Допустим, сто записей, тогда это значение будет 50. Если тысяча - это будет 500 и так далее.

SELECT `group_id` FROM `vkmembers` where group_id > 50 ORDER BY `members` DESC LIMIT 0,20

Попробуйте этот запрос, если он чудесным образом станет работать значительно быстрей - приходите, поговорим дальше. :)

[DrunkMaster]

Артем Каретников: в БД нет понятия "последний" - на практике это либо максимальный по дате / id / ещё чему-то, либо минимальный.

Я сделал то что вы просили:

SELECT SQL_NO_CACHE `group_id`,`members` FROM `vkmembers` ORDER BY `members` DESC LIMIT 0,10
Запрос занял 0.0003 сек.

SELECT SQL_NO_CACHE `group_id`,`members` FROM `vkmembers` WHERE `group_id` > 50 ORDER BY `members` DESC LIMIT 0,10
Запрос занял 0.0005 сек.

Я выложил профилировку, там больше всего времени тратится на freeing items т.е. судя по всему на удаление временной таблицы, которая была создана. А мы с вами обсуждаем вложенный запрос на который тратится мизер времени.

[ThunderCat]

DrunkMaster: у вас действительно 100 записей в таблице???

[DrunkMaster]

Артем Каретников: да сделал EXPLAIN дополнительно этих 2 запросов и увидел что у вас possible_keys PRIMARY а у меня NULL но как быть если я не знаю какое значение может вgroup_id для условия `group_id` > 50 Может быть 10 млн., а может и 41...

[Артём Каретников]

DrunkMaster:

Окей. Зайду с другой стороны.

Подзапрос выбрал информацию по группам. Зачем вам лезть в еще одну таблицу, зачем создавать временную, зачем все эти чудеса, если у вас уже есть информация по группам?

Надо ее уникальной сделать?

[DrunkMaster]

Артем Каретников: Ну у меня данные в 2 таблицах в каждой из которых есть group_id сейчас это не принципиально, я могу переделать в 1 таблицу. Теперь мне всё же принципиально захотелось разобраться чисто с подзапросом и понять как же максимально правильно и быстро вынуть 20 наибольших значений из базы если голый LIMIT не катит... В ответе ThunderCat так же вам ответил

[Артём Каретников]

Дорогой мой человек. Вот я вам в самом первом ответе смотрите, что написал:

"И совершенно непонятно, зачем брать абсолютно все данные и их потом переворачивать. Добавьте, например, условие сюда же по дате, или брать не все данные, а половину только и скорость увеличится".

Никакого иного чуда не случится. Ускорить выборку подстановкой правильного условия - это и есть ваше решение. Моё, вернее. Не надо пытаться объяснять, как вы написали запрос. Это и так очевидно.

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

[DrunkMaster]

Есть 3 поля - id, members, date. id и members как мы выяснили с ThunderCat неуникальные, не подчиняются никакой закономерности. Дата тоже одна на всех, но на следующий день будет другая дата так обеспечивается уникальность составного primary из id+date. Мне надо выбрать N самых больших, потом N-20 поменьше потом N-40 поменьше и т.д. я не могу в WHERE задать никакого условия потому что не имею представления ни какой id у этого members ни какой величины members будет на следующем шаге. Может меньше на 5 тыс. а может меньше на 2 миллиона

[Артём Каретников]

Кто конкретно и с какой целью мешает добавить автоинкрементное поле, и назвать его rid? Поймайте его и разберитесь. Дальше сделаете индекс по дате.

Потом делаете один раз выборку по минимальному rid за текущую дату. И все. Полет ракеты в космос.

[DrunkMaster]

Я не очень понимаю как мне тут поможет автоинкрементное поле rid? Ну вот добавил я его предположим, что дальше, как его использовать?

[Артём Каретников]

Ох, грехи мои тяжкие...

Надо мне отобрать определенные записи из таблицы. Я могу херачать выборку из таблицы всей. Это глупо, да?
Хотя сейчас именно так и сделано.

Что еще я могу? Создать индекс на дату. Это будет быстрый, хороший индекс. Тогда решение задачи сведется к:
0. Отобрать минимальный id за текущую дату. Скажем, это 14597889
1. Сказать серверу: отбери мне все данные, у которых rid больше, чем 14597889

Будет это быстрей? Будет. Все, на этом вашу задачу с подзапросом вообще можно считать решенной.

[Артём Каретников]

Теперь, дети, давайте подумаем, нужен ли нам вообще подзапрос. И мы тут же поймем, что он не нужен, что лазить во вторую таблицу вообще не надо, все данные можно достать только этим самым подзапросом и оперировать только с ними.

[DrunkMaster]

Артем Каретников: Я слили всё в 1 таблицу, теперь подзапрос не нужен. Да с id + датой понял идею, там чем больше дат, тем больше выгода получится...

Answer 3

[ThunderCat]

Возможно у вас есть непонимание основ работы бд. Например

SELECT `group_id` FROM `vkmembers` ORDER BY `members` DESC LIMIT 0,20

возможно по вашему мнению быстренько выбирает 20 последних записей, что на самом деле не так.

PS: попробуй уменьшить query_cache хотя бы до 16 мб, а то и вовсе отключить.

Comments

[Артём Каретников]

Ну да, именно поэтому я предлагаю урезать объем отбираемых данных, вставив условие where id > половины Да?

[DrunkMaster]

Да почитал

1. В первую очередь нужно избавится от limit, если записи растут с единицы, и выборка проводится последовательно по записях то применяйте не лимит а where выражение, но это только возможно если счетчик идет непрерывно
2. если записи идут как попало и невозможно применить п. 1, то следует использовать кеширование, напр. в простейшем виде - при запросе от и до обращаться к табл. где будут указаны id выбранных записей, если таковой нет - то создать, конечно с понятных причин первый такой запрос будет выполнятся долго, либо насильно генерировать такие запросы для пагинации

У меня как раз второй пункт записи идут как попало и в `group_id` и в `members` закономерности нет. Единственное - я не понял что конкретно он предлагает, к какой обращаться таблице с id записей? Это же у меня и есть эта таблица...

[DrunkMaster]

@ThunderCat

у вас действительно 100 записей в таблице???

В таблице сейчас около 1 млн. записей
@art_karetnikov

именно поэтому я предлагаю урезать объем отбираемых данных

Теперь да я полностью понял вашу идею, другое дело что я не имею представления где половина по group_id но могу такое условие ориентировочно поставить на members надеясь что вручную никто не пролистает больше половины пагинации ))) (больше 100 тыс. страниц)

[ThunderCat]

DrunkMaster: Count же!

[DrunkMaster]

ThunderCat: Извините я не понял идею SELECT COUNT чего именно? Какое условие к Count должно быть?

[ThunderCat]

DrunkMaster: если я правильно понимаю структуру таблицы(не уверен, не видел) count по members даст вам точное количество, его останется только поделить на 2(а можно и более раз).

[ThunderCat]

DrunkMaster: кроме того - таки попробуйте отключить query_cache в настройках базы, для теста.

[DrunkMaster]

ThunderCat: id и members - это значения почти случайные, ни одно из них не autouncremet и если я разделю пополам таблицу я профита от этого никакого не получу. Вы меня спросите как же тогда primary обеспечивается если они могут быть не уник. Primary - это составной ключ из id + столбца data

[ThunderCat]

DrunkMaster: если members не уникальный, тогда да, смысл теряется, с другой стороны - тогда скорее всего "что-то пошло не так" при проектировании структуры бд. Слишком сложное построение для, в сущности, не очень сложной связки структур. Либо, что вероятнее, попытки упростить структуру за счет потери связей "многие ко многим". Чистое имхо, бо так и не видел ни таблиц, ни связей, ни структуры. PS: Что с кешем? Не пробовали или не помогает?

[DrunkMaster]

ThunderCat: не пробовал ещё, переделываю структуру, сливаю всё в одну таблицу но там тоже будет неуникальный members и id случайные а не autoincrement так что вопрос выбора N самых больших members останется ))