文章/答案/技术大牛

发布

社区首页 >问答首页 >获取基于类别的随机条目

问获取基于类别的随机条目
EN

Stack Overflow用户

提问于 2016-04-01 15:26:45

回答 1查看 53关注 0票数 3

我有一个名为“文章”的表格。此表的结构如下: id (唯一)、类别(文章的类别，即娱乐)、标题(文章的标题)、图像(文章的图像网址)、链接(文章的网址)、计数器(文章的浏览量)、dateStamp (文章的发布日期)。

假设我想随机打印6篇娱乐文章。一种简单但效率较低的方法是这样做

$result = $db->query("SELECT * FROM Articles WHERE category = 'entertainment' ORDER BY RAND() LIMIT 6);

相反，我如何才能更有效率地去做呢？我知道有多个网站解释了ORDER BY RAND()的替代方案，但我就是不理解这些替代方案。我想根据我的结构来理解。我尝试了多种方法，从混洗关联数组到创建随机生成器，但都以失败告终，因为我无法让它正常工作。如何在不使用ORDER BY RAND()的情况下打印出6篇随机的娱乐文章？

php

mysql

mysqli

回答 1

Stack Overflow用户

发布于 2016-04-01 20:26:21

没有通用的方法来做到这一点，有效的选择之一是根据给定的(可选)标准选择数据的随机子集，在您的情况下为category。注意在此列上添加的索引。

SELECT
    r1.*
FROM
    articles AS r1
    INNER JOIN (SELECT(RAND() * (SELECT MAX(id) FROM articles)) AS id) AS r2
WHERE
    r1.id >= r2.id
    AND r1.category = 'entertainment'
LIMIT 6;

以下是示例数据(320万行)和执行计划的详细信息：

mysql> SELECT COUNT(*) FROM articles;
+----------+
| COUNT(*) |
+----------+
|  3200000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT
    r1.*
FROM
    articles AS r1
    INNER JOIN (SELECT(RAND() * (SELECT MAX(id) FROM articles)) AS id) AS r2
WHERE
    r1.id >= r2.id
    AND r1.category = 'entertainment'
LIMIT 6;
+---------+-------------+-----------------------------------------------------------+---------------+
| id      | topic       | message                                                   | category      |
+---------+-------------+-----------------------------------------------------------+---------------+
| 3153910 | JAX68VVH3FZ | Sed eu eros. Nam consequat dolor                          | entertainment |
| 3153911 | NIY23HWV0VM | tortor. Nunc commodo auctor velit. Aliquam nisl. Nulla eu | entertainment |
| 3153912 | LKQ42FRB7LA | mus. Proin vel nisl. Quisque                              | entertainment |
| 3153913 | PFL39VHI9RM | gravida                                                   | entertainment |
| 3153914 | FGV59TUN9TQ | elit, pellentesque a, facilisis non, bibendum sed,        | entertainment |
| 3153915 | OWH73EBZ1GW | ligula. Nullam enim. Sed nulla ante, iaculis              | entertainment |
+---------+-------------+-----------------------------------------------------------+---------------+
6 rows in set (0.473 sec)

mysql> explain extended 
SELECT
    r1.*
FROM
    articles AS r1
    INNER JOIN (SELECT(RAND() * (SELECT MAX(id) FROM articles)) AS id) AS r2
WHERE
    r1.id >= r2.id
    AND r1.category = 'entertainment'
LIMIT 6;
+----+-------------+------------+--------+-----------------+---------+---------+-------+---------+----------+------------------------------+
| id | select_type | table      | type   | possible_keys   | key     | key_len | ref   | rows    | filtered | Extra                        |
+----+-------------+------------+--------+-----------------+---------+---------+-------+---------+----------+------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL            | NULL    | NULL    | NULL  |       1 |      100 | NULL                         |
|  1 | PRIMARY     | r1         | ref    | PRIMARY,cat_IDX | cat_IDX | 768     | const | 1560229 |      100 | Using index condition        |
|  2 | DERIVED     | NULL       | NULL   | NULL            | NULL    | NULL    | NULL  | NULL    | NULL     | No tables used               |
|  3 | SUBQUERY    | NULL       | NULL   | NULL            | NULL    | NULL    | NULL  | NULL    | NULL     | Select tables optimized away |
+----+-------------+------------+--------+-----------------+---------+---------+-------+---------+----------+------------------------------+
4 rows in set (0.00 sec)

在相同数据量的情况下，性能差异比通常情况下显著(超过10倍)：

mysql> SELECT * FROM articles WHERE category = 'entertainment' ORDER BY RAND() LIMIT 6;
+---------+-------------+---------------------------------------------------------------------------+---------------+
| id      | topic       | message                                                                   | category      |
+---------+-------------+---------------------------------------------------------------------------+---------------+
| 2374491 | PZC33VGM0ML | Duis cursus, diam at pretium aliquet, metus urna convallis erat,          | entertainment |
|  382306 | RFN88EPE4MI | malesuada fames ac turpis egestas. Aliquam fringilla cursus purus. Nullam | entertainment |
| 1867986 | KWX30ULB1FR | pede.                                                                     | entertainment |
| 1528863 | ADX52RRJ3MQ | lacus. Mauris non                                                         | entertainment |
| 2188208 | AOD82PXQ6FS | diam luctus lobortis. Class aptent taciti sociosqu ad litora              | entertainment |
|  878426 | ABV08HTB2PG | eu eros. Nam consequat dolor vitae dolor. Donec fringilla. Donec          | entertainment |
+---------+-------------+---------------------------------------------------------------------------+---------------+
6 rows in set (5.726 sec)

希望这对你有帮助。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/36350396

复制

相似问题

问获取基于类别的随机条目
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取基于类别的随机条目EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取基于类别的随机条目
EN