mysql - 优化 SQL 以连接随机单词

Question

我有一个表(“j_un2”)，其中有 6,318 个随机单词，其中的总计数永远不会改变。表中的 ID 是无缝的。

我需要生成一个由 2 个单词组成的 5 个随机连接字符串的列表，其中字符串的总长度为 8 个字符。

几天前我已经通过以下方式获得了一些非常有用的帮助: Selecting random words from table

我的基本方法是从表中选择两次，然后将随机选择的单词连接起来。

我有一个“fld_len”列，它是单词的长度。

表结构:

CREATE TABLE `j_un2` (
  `fld_id` int(11) NOT NULL AUTO_INCREMENT,
  `fld_un` varchar(255) DEFAULT NULL,
  `fld_cat_id` int(11) DEFAULT NULL,
  `fld_len` int(2) NOT NULL,
  PRIMARY KEY (`fld_id`),
  KEY `cat` (`fld_cat_id`),
  KEY `bob` (`fld_len`,`fld_un`)
);

该表具有以下索引:

Keyname     Type    Field
PRIMARY     PRIMARY fld_id
bob         INDEX   fld_len, fld_un

如果我执行 ORDER BY RAND()，我发现了主要的性能问题。阅读 StackOverflow 和此处: http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

我将此查询缩短到大约 3.7 秒:

   SELECT CONCAT(w1.fld_un, w2.fld_un) bbb
        , FLOOR(1 + RAND() * 6318) 'rand_ind'
     FROM j_un2 w1
        , j_un2 w2 
    WHERE w1.fld_len = 8 - w2.fld_len
      AND w2.fld_len < 8
      AND RAND()<(((1/6318)*10)) 
 ORDER BY rand_ind
    LIMIT 20;

这是查询的解释计划:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          w2      range   bob             bob     4           NULL    5886    Using where; Using index; Using temporary; Using filesort
1   SIMPLE          w1      ref     bob             bob     4           func    63      Using where; Using index

我想使用此查询的页面每月获得大约 50 万页面浏览量，因此非常繁忙(无论如何对我来说)，如果用户必须等待每次页面刷新大约 4 秒，他们可能会对此感到恼火。

我也尝试在选择单词后执行 CONCAT，但这需要 10 秒才能运行:

SELECT CONCAT(word1, word2) joined
FROM
   (SELECT w1.fld_un word1, w2.fld_un word2
        , FLOOR(1 + RAND() * 6318) 'rand_ind'
     FROM j_un2 w1
        , j_un2 w2 
    WHERE w1.fld_len = 10 - w2.fld_len
      AND w2.fld_len < 10
      AND RAND()<(((1/6318)*10)) 
 ORDER BY rand_ind
    LIMIT 20) bob;

考虑到我正在尝试通过一种相当组合的连接方法来连接两个表，我想知道这个查询是否运行得像以前一样快，或者是否有任何范围可以加快它的速度？

<小时/>

更新1

实际上，我认为性能下降取决于表连接机制，如下所示:

   SELECT CONCAT(w1.fld_un, w2.fld_un) bbb
     FROM j_un2 w1
        , j_un2 w2 
    WHERE w1.fld_len = 8 - w2.fld_len
      AND w2.fld_len < 8
      AND RAND()<(((1/6318)*10)) 
 ORDER BY rand()
    LIMIT 20;

同时运行 - 例如通过 rand() 排序没有任何区别

Answer 1

可在此处找到仅 SQL 的解决方案:http://mysql.rjweb.org/doc.php/random#case_consecutive_auto_increment 。总之，假设 id 中没有间隙，它会随机获取一行。这样做两次以获得两行。不需要表扫描。如果需要避免两次获得相同的单词，仍然需要代码来重试。

如何使两行的总长度等于8个字符？这是一个有趣的转折。对于第二个查询:添加 INDEX(len, id)，然后在剩余长度内使用 MIN 和 MAX id。然后调整该博客中的下一个“案例”(带间隙的自动增量)以找到第二行。 (好吧，这会变得困惑，但希望我已经给了你一些需要思考的事情。)

该链接有 8 个“高效”替代 ORDER BY RAND() LIMIT n。 (没有一个是“完美的”。)