时间戳

首页 > 解决方案 > 快速选择具有 Where 条件的随机行

mysql - 快速选择具有 Where 条件的随机行

问题描述

是否可以从表中快速选择随机行，同时还使用 where 条件？

例子：

SELECT * FROM geo WHERE placeRef = 1 ORDER BY RAND() LIMIT 1

这可能需要 10 多秒。

我发现了这个，有时很快，有时很慢：

(SELECT *
FROM geo
INNER JOIN ( SELECT RAND() * ( SELECT MAX( nameRef ) FROM geo ) AS ID ) AS t ON geo.nameRef >= t.ID
WHERE geo.placeRef = 1
ORDER BY geo.nameRef
LIMIT 1)

这提供了一个快速的结果，只有在没有额外的 where 条件的情况下。

这是创建表：

CREATE TABLE `geo` (
 `nameRef` int(8) DEFAULT NULL,
 `placeRef` mediumint(7) unsigned DEFAULT NULL,
 `category` enum('continent','country','region','subregion') COLLATE utf8_bin DEFAULT NULL,
 `parentRef` mediumint(7) DEFAULT NULL,
 `incidence` int(9) unsigned NOT NULL,
 `percent` decimal(11,9) unsigned DEFAULT NULL,
 `ratio` int(11) NOT NULL,
 `rank` mediumint(7) unsigned DEFAULT NULL,
 KEY `placeRef_rank` (`placeRef`,`rank`),
 KEY `nameRef_category` (`nameRef`,`category`),
 KEY `nameRef_parentRef` (`nameRef`,`parentRef`),
 KEY `nameRef_placeRef` (`nameRef`,`placeRef`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

注意这个表有大约 5.5 亿行。

所需查询：查询placeRef = x的表；然后快速返回一排。

问题：一个类似的查询SELECT * FROM geo WHERE placeRef = 1最多可以提供大约 1500 万个结果。因此选择单个随机行很慢。

标签： mysqlrandomsql-order-bywhere-clause

解决方案

该技术是可变的，因为它取决于匹配行在表中的位置。

快速修复可能是添加此索引，假设这nameRef是PRIMARY KEY表：

INDEX(placeRef, nameRef)

让我们稍后再讨论这个

（当前）有 3 个索引使这个子查询非常快（因为前导nameRef）：

( SELECT MAX( nameRef ) FROM geo )

在那之后，我的建议(placeRef, nameRef)将适用于这些：

WHERE geo.placeRef = 1
geo.nameRef >= t.ID

我认为生成的查询应该始终如一地快速。

推荐阅读