LIMIT возвращает не уникальные значения

У меня две таблицы. Сообщения и ответы. Думайте о сообщениях как записи в блоге, а ответы – это комментарии.

Я хочу отобразить X количество сообщений, а затем последние три комментария для каждого из сообщений.

Мои ответы имеют внешний ключ «post_id», который соответствует «id» каждого сообщения.

Я пытаюсь создать главную страницу, которая имеет что-то вроде

Сообщение –Reply –Reply –Reply

Ответить

так и так далее. Я могу выполнить это, используя цикл for в моем шаблоне и отбрасывая ненужные ответы, но я ненавижу захват данных из db, которые я не буду использовать. Есть идеи?

На самом деле это довольно интересный вопрос.

HA HA ОТКАЗЫВАЕТСЯ ЭТО, Я СУКА

При редактировании: этот ответ работает, но в MySQL он становится утомительно медленным, когда число родительских строк составляет всего 100. Однако, см. Ниже для исправления исполнителей.

Очевидно, вы можете запускать этот запрос один раз за сообщение: select * from comments where id = $id limit 3 Это создает много накладных расходов, так как вы в конечном итоге делаете один запрос к базе данных за сообщение, страшные N + 1 запросы .

Если вы хотите получить все сообщения за один раз (или некоторое подмножество с каким-либо), то следующее будет неожиданно работать. Он предполагает, что комментарии имеют монотонно возрастающий id (поскольку datetime не гарантированно является уникальным), но допускает чередование идентификаторов комментариев среди сообщений.

Поскольку столбец id auto_increment монотонно возрастает, если в комментарии есть идентификатор, вы все настроены.

Сначала создайте это представление. В представлении я называю post parent и comment child :

 create view parent_top_3_children as select a.*, (select max(id) from child where parent_id = a.id) as maxid, (select max(id) from child where id < maxid and parent_id = a.id) as maxidm1, (select max(id) from child where id < maxidm1 and parent_id = a.id) as maxidm2 from parent a; 

maxidm1 – это только «max id минус 1»; maxidm2 , «max id минус 2», то есть второй и третий наибольшие дочерние элементы в пределах определенного родительского идентификатора .

Затем присоедините представление к тому, что вам нужно из комментария (я назову этот text ):

 select a.*, b.text as latest_comment, c.text as second_latest_comment, d.text as third_latest_comment from parent_top_3_children a left outer join child b on (b.id = a.maxid) left outer join child c on (c.id = a.maxidm1) left outer join child d on (c.id = a.maxidm2); 

Естественно, вы можете добавить любое предложение where, которое вы хотите, чтобы ограничить должности: where a.category = 'foo' или что-то еще.


Вот как выглядят мои таблицы:

 mysql> select * from parent; +----+------+------+------+ | id | a | b | c | +----+------+------+------+ | 1 | 1 | 1 | NULL | | 2 | 2 | 2 | NULL | | 3 | 3 | 3 | NULL | +----+------+------+------+ 3 rows in set (0.00 sec) 

И часть ребенка. У родителя 1 есть дети noo:

 mysql> select * from child; +----+-----------+------+------+------+------+ | id | parent_id | a | b | c | d | +----+-----------+------+------+------+------+ . . . . | 18 | 3 | NULL | NULL | NULL | NULL | | 19 | 2 | NULL | NULL | NULL | NULL | | 20 | 2 | NULL | NULL | NULL | NULL | | 21 | 3 | NULL | NULL | NULL | NULL | | 22 | 2 | NULL | NULL | NULL | NULL | | 23 | 2 | NULL | NULL | NULL | NULL | | 24 | 3 | NULL | NULL | NULL | NULL | | 25 | 2 | NULL | NULL | NULL | NULL | +----+-----------+------+------+------+------+ 24 rows in set (0.00 sec) 

И точка зрения дает нам следующее:

 mysql> select * from parent_top_3; +----+------+------+------+-------+---------+---------+ | id | a | b | c | maxid | maxidm1 | maxidm2 | +----+------+------+------+-------+---------+---------+ | 1 | 1 | 1 | NULL | NULL | NULL | NULL | | 2 | 2 | 2 | NULL | 25 | 23 | 22 | | 3 | 3 | 3 | NULL | 24 | 21 | 18 | +----+------+------+------+-------+---------+---------+ 3 rows in set (0.21 sec) 

План объяснения для вида лишь слегка волосатый:

 mysql> explain select * from parent_top_3; +----+--------------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | | | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 3 | | | 5 | DEPENDENT SUBQUERY | child | ALL | PRIMARY | NULL | NULL | NULL | 24 | Using where | | 4 | DEPENDENT SUBQUERY | child | ALL | PRIMARY | NULL | NULL | NULL | 24 | Using where | | 3 | DEPENDENT SUBQUERY | child | ALL | NULL | NULL | NULL | NULL | 24 | Using where | +----+--------------------+------------+------+---------------+------+---------+------+------+-------------+ 

Однако, если мы добавим индекс для parent_fks, он станет лучше:

 mysql> create index pid on child(parent_id); mysql> explain select * from parent_top_3; +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | | | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 3 | | | 5 | DEPENDENT SUBQUERY | child | ref | PRIMARY,pid | pid | 5 | util.a.id | 2 | Using where | | 4 | DEPENDENT SUBQUERY | child | ref | PRIMARY,pid | pid | 5 | util.a.id | 2 | Using where | | 3 | DEPENDENT SUBQUERY | child | ref | pid | pid | 5 | util.a.id | 2 | Using where | +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ 5 rows in set (0.04 sec) 

Как отмечено выше, это начинает разваливаться, когда число родительских строк невелико на 100, даже если мы индексируем родительский элемент с использованием его первичного ключа :

 mysql> select * from parent_top_3 where id < 10; +----+------+------+------+-------+---------+---------+ | id | a | b | c | maxid | maxidm1 | maxidm2 | +----+------+------+------+-------+---------+---------+ | 1 | 1 | 1 | NULL | NULL | NULL | NULL | | 2 | 2 | 2 | NULL | 25 | 23 | 22 | | 3 | 3 | 3 | NULL | 24 | 21 | 18 | | 4 | NULL | 1 | NULL | 65 | 64 | 63 | | 5 | NULL | 2 | NULL | 73 | 72 | 71 | | 6 | NULL | 3 | NULL | 113 | 112 | 111 | | 7 | NULL | 1 | NULL | 209 | 208 | 207 | | 8 | NULL | 2 | NULL | 401 | 400 | 399 | | 9 | NULL | 3 | NULL | 785 | 784 | 783 | +----+------+------+------+-------+---------+---------+ 9 rows in set (1 min 3.11 sec) 

(Обратите внимание, что я намеренно тестирую медленную машину, данные сохраняются на медленном флэш-диске.)

Вот объяснение, ища ровно один id (и первый из них):

 mysql> explain select * from parent_top_3 where id = 1; +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 1000 | | | 5 | DEPENDENT SUBQUERY | child | ref | PRIMARY,pid | pid | 5 | util.a.id | 179 | Using where | | 4 | DEPENDENT SUBQUERY | child | ref | PRIMARY,pid | pid | 5 | util.a.id | 179 | Using where | | 3 | DEPENDENT SUBQUERY | child | ref | pid | pid | 5 | util.a.id | 179 | Using where | +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ 5 rows in set (56.01 sec) 

Более 56 секунд для одного ряда, даже на моей медленной машине, на два порядка недопустимы.

Итак, мы можем сохранить этот запрос? Он работает , он слишком медленный.

Вот план объяснения для измененного запроса. Это выглядит так же плохо или хуже:

 mysql> explain select * from parent_top_3a where id = 1; +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | Using where | | 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 100 | | | 4 | DERIVED | <derived6> | ALL | NULL | NULL | NULL | NULL | 100 | | | 6 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 100 | | | 7 | DEPENDENT SUBQUERY | child | ref | pid | pid | 5 | util.a.id | 179 | Using where | | 5 | DEPENDENT SUBQUERY | child | ref | PRIMARY,pid | pid | 5 | a.id | 179 | Using where | | 3 | DEPENDENT SUBQUERY | child | ref | PRIMARY,pid | pid | 5 | a.id | 179 | Using where | +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+ 7 rows in set (0.05 sec) 

Но он на три порядка быстрее, в 1/20 секунды!

Как мы можем перейти к гораздо более быстрому parent_top_3a? Мы создаем три вида, каждый из которых зависит от предыдущего:

 create view parent_top_1 as select a.*, (select max(id) from child where parent_id = a.id) as maxid from parent a; create view parent_top_2 as select a.*, (select max(id) from child where parent_id = a.id and id < a.maxid) as maxidm1 from parent_top_1 a; create view parent_top_3a as select a.*, (select max(id) from child where parent_id = a.id and id < a.maxidm1) as maxidm2 from parent_top_2 a; 

Эта работа не только значительно ускоряет работу, но и работает в RDBMS, кроме MySQL.

Давайте увеличим количество родительских строк до 12800, количество дочерних строк до 1536 (большинство сообщений в блоге не получают комментариев, правильно?;))

 mysql> select * from parent_top_3a where id >= 20 and id < 40; +----+------+------+------+-------+---------+---------+ | id | a | b | c | maxid | maxidm1 | maxidm2 | +----+------+------+------+-------+---------+---------+ | 39 | NULL | 2 | NULL | NULL | NULL | NULL | | 38 | NULL | 1 | NULL | NULL | NULL | NULL | | 37 | NULL | 3 | NULL | NULL | NULL | NULL | | 36 | NULL | 2 | NULL | NULL | NULL | NULL | | 35 | NULL | 1 | NULL | NULL | NULL | NULL | | 34 | NULL | 3 | NULL | NULL | NULL | NULL | | 33 | NULL | 2 | NULL | NULL | NULL | NULL | | 32 | NULL | 1 | NULL | NULL | NULL | NULL | | 31 | NULL | 3 | NULL | NULL | NULL | NULL | | 30 | NULL | 2 | NULL | 1537 | 1536 | 1535 | | 29 | NULL | 1 | NULL | 1529 | 1528 | 1527 | | 28 | NULL | 3 | NULL | 1513 | 1512 | 1511 | | 27 | NULL | 2 | NULL | 1505 | 1504 | 1503 | | 26 | NULL | 1 | NULL | 1481 | 1480 | 1479 | | 25 | NULL | 3 | NULL | 1457 | 1456 | 1455 | | 24 | NULL | 2 | NULL | 1425 | 1424 | 1423 | | 23 | NULL | 1 | NULL | 1377 | 1376 | 1375 | | 22 | NULL | 3 | NULL | 1329 | 1328 | 1327 | | 21 | NULL | 2 | NULL | 1281 | 1280 | 1279 | | 20 | NULL | 1 | NULL | 1225 | 1224 | 1223 | +----+------+------+------+-------+---------+---------+ 20 rows in set (1.01 sec) 

Обратите внимание, что эти тайминги предназначены для таблиц MyIsam; Я оставлю его кому-то еще, чтобы сделать тайминги на Innodb.


Но, используя Postgresql, в подобном, но не идентичном наборе данных, мы получаем аналогичные тайминги, where предикаты с участием столбцов parent :

  postgres=# select (select count(*) from parent) as parent_count, (select count(*) from child) as child_count; parent_count | child_count --------------+------------- 12289 | 1536 postgres=# select * from parent_top_3a where id >= 20 and id < 40; id | a | b | c | maxid | maxidm1 | maxidm2 ----+---+----+---+-------+---------+--------- 20 | | 18 | | 1464 | 1462 | 1461 21 | | 88 | | 1463 | 1460 | 1457 22 | | 72 | | 1488 | 1486 | 1485 23 | | 13 | | 1512 | 1510 | 1509 24 | | 49 | | 1560 | 1558 | 1557 25 | | 92 | | 1559 | 1556 | 1553 26 | | 45 | | 1584 | 1582 | 1581 27 | | 37 | | 1608 | 1606 | 1605 28 | | 96 | | 1607 | 1604 | 1601 29 | | 90 | | 1632 | 1630 | 1629 30 | | 53 | | 1631 | 1628 | 1625 31 | | 57 | | | | 32 | | 64 | | | | 33 | | 79 | | | | 34 | | 37 | | | | 35 | | 60 | | | | 36 | | 75 | | | | 37 | | 34 | | | | 38 | | 87 | | | | 39 | | 43 | | | | (20 rows) Time: 91.139 ms 

Похоже, вам просто нужно предложение LIMIT для SELECT :

 SELECT comment_text, other_stuff FROM comments WHERE post_id = POSTID ORDER BY comment_time DESC LIMIT 3; 

Вы должны будете запустить этот запрос один раз за сообщение, на которое хотите показывать комментарии. Есть несколько способов обойти это, если вы готовы пожертвовать ремонтопригодностью и своим здравомыслием в Quest for Ultimate Performance:

  1. Как и выше, один запрос на сообщение для получения комментариев. Простой, но, вероятно, не все так быстро.

  2. Получите список post_ids , для которого вы хотите показать комментарии, затем получите все комментарии к этим сообщениям и отфильтруйте их на стороне клиента (или вы можете сделать это на стороне сервера, если бы у вас были функции окна, я думаю, хотя это не в MySQL). Простая на стороне сервера, но фильтрация на стороне клиента будет уродливой, и вы по-прежнему перемещаете много данных с сервера на клиент, так что, вероятно, это будет не так быстро.

  3. Как # 1, но используйте unholy UNION ALL из числа запросов, так как у вас есть сообщения для отображения, поэтому вы используете один отвратительный запрос вместо N маленьких. Ужасно, но это будет быстрее, чем варианты 1 или 2. Вам все равно придется немного фильтровать клиентскую сторону, но тщательная запись UNION сделает это намного проще, чем фильтрация, необходимая для # 2, и нет впустую данные будут отправлены по проводу. Тем не менее, это вызовет уродливый запрос.

  4. Присоединяйтесь к таблице сообщений и комментариев, частично отменяя комментарии. Это довольно чисто, если вам нужен только один комментарий, но если вы хотите три, он быстро станет беспорядочным. Отлично работает на стороне клиента, но даже хуже SQL, чем # 3, и, вероятно, сложнее для сервера, для загрузки.

В конце концов, я бы пошел с вариантом 1, простым запросом выше, и не беспокоиться о накладных расходах на выполнение одного раза за сообщение. Если вам нужен только один комментарий, то вариант соединения может быть приемлемым, но вы хотите три, и это исключает его. Если в MySQL добавляются функции оконной обработки (они выпущены в версии 8.4 PostgreSQL), вариант 2 может стать приемлемым или даже предпочтительным. Однако до этого дня просто выберите простой, понятный запрос.

Хотя может быть умный способ получить это в одном запросе без каких-либо изменений схемы, я предполагаю, что это не будет выполнено в любом случае , Edit: Похоже, у tpdi есть умное решение. Это выглядит довольно быстро, но мне было бы любопытно увидеть бенчмарк по конкретным базам данных.

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

Решение без изменений или обслуживания схемы

Первый:

 SELECT * FROM Posts 

Соберите идентификаторы, затем:

 SELECT id FROM Replies WHERE post_id IN (?) ORDER BY id DESC 

Наконец, прокрутите эти идентификаторы, захватив только первые 3 для каждого post_id, а затем выполните:

 SELECT * FROM Replies WHERE post_id IN (?) 

Более эффективное решение, если вы хотите поддерживать несколько столбцов кеша

Второе решение предполагает, что записи гораздо больше, чем записи, вы можете минимизировать поиск, сохраняя последние три идентификатора комментариев в таблице Posts каждый раз, когда вы добавляете ответ. В этом случае вы просто добавите три столбца last_reply_id , second_reply_id , third_reply_id или некоторые из них. Затем вы можете искать два запроса:

 SELECT * FROM Posts 

Соберите идентификаторы из этих полей, затем:

 SELECT * FROM Replies WHERE post_id IN (?) 

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

 SELECT posts.*, r1.title, r2.title ... FROM Posts LEFT JOIN Replies as r1 ON Posts.last_reply_id = Replies.id LEFT JOIN Replies as r2 ON Posts.second_reply_id = Replies.id ... 

Который вы предпочитаете, вероятно, зависит от вашего ORM или языка.