关于Mysql获取每个分类下面前几条数据的几种方式

作者: xahy 分类: 笔记 发布时间: 2019-07-20 20:14

说在前面:

wp_term_relationships 表目前有 9145 条数据,

wp_term_relationships 表结构如下:

CREATE TABLE `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_order` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`),
KEY `object_id` (`object_id`) USING BTREE
) ENGINE=InnoDB;

SQL:

  • 分批获取程序组装

SELECT
object_id,
term_taxonomy_id
FROM
wp_term_relationships
WHERE
term_taxonomy_id = 1
ORDER BY
object_id DESC
LIMIT 2;

查询时间:0.027s

分页获取分类id,比如获取10条分类id;然后通过SQL获取最新的前两条数据、拼接结果集返回最终结果。

优点是:分页显示条数较少时耗时是最优的,比如10条;

缺点是:1、分页显示条数较大时,比如100条,耗时也是不能接受的;2、可能存在数据为空的分类;

  • 子查询

SELECT
term_taxonomy_id,
object_id
FROM
wp_term_relationships a
WHERE
2 > (
SELECT
count(*)
FROM
wp_term_relationships b
WHERE
b.term_taxonomy_id = a.term_taxonomy_id
AND b.object_id > a.object_id
)
ORDER BY
a.term_taxonomy_id,
a.object_id DESC
LIMIT 100;

ps:term_taxonomy_id 为分类id;object_id 为文章id 也就是自增id。

limit 100 查询耗时:2.845s

limit 10 查询耗时:2.894s

LIMIT 150,10 查询耗时:2.884s

耗时来说比较稳定,但是如果再加上统计的耗时,整体耗时也是不能接受的。

优点是:一条SQL解决需求;

缺点是:耗时不能接受

  • 左连接方式

SELECT
a.object_id,
a.term_taxonomy_id
FROM
wp_term_relationships a
LEFT JOIN wp_term_relationships b ON a.term_taxonomy_id = b.term_taxonomy_id
AND a.object_id <= b.object_id
GROUP BY
a.term_taxonomy_id,
a.object_id
HAVING
count(b.object_id) <= 2
ORDER BY
a.object_id,a.term_taxonomy_id
DESC
LIMIT 100

limit 100 耗时:3.045s

limit 10 查询耗时:3.036s

limit 150,10 查询耗时:2.950s

这么来看和子查询方式没太大差异,或许数据量大了之后就能显现出来了。

另外有很多MySQL优化相关的博文,建议数据量大的时候最好使用 左连接替代子查询,原因是执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表。

  • 指定分类id

直接在查询的时候指定分类id;

SELECT
a.*
FROM
wp_term_relationships AS a
LEFT JOIN wp_term_relationships AS b ON a.term_taxonomy_id = b.term_taxonomy_id
AND a.object_id < b.object_id
WHERE
a.term_taxonomy_id IN (1, 23)
GROUP BY
a.object_id,
a.term_taxonomy_id
HAVING
COUNT(b.object_id) < 2
ORDER BY
a.object_id DESC;

查询时间:3.674s

a.term_taxonomy_id = 1 查询时间:2.746s

至于为什么限定条数反而比楼上的慢了,通过 EXPLAIN 来看,b 表的扫描行数变大了,分类id存在多少条数据 rows 则为多少条,故查询时间变长了。

  • 总结

单从这几条SQL来看,分批获取程序组装的方式是最快的;

若需要一条SQL跑出来,直接用左连接的方式好了,耗时相差也不太大,数据量大了后就能发挥出优势了。

另外,具体业务具体对待,服务器性能也要考虑在内,毕竟我测试的电脑还是个 win7 系统。

发表评论

电子邮件地址不会被公开。 必填项已用*标注