MySQL查询性能优化

Question

我在我的数据库中有一本字典，它有超过百万条记录和这个简单的选择

select * from Word where languageId = 'en' order by rand() limit 1

随机选择一个单词。

问题是，这个请求持续3-4秒，这是非常长的，因为我不得不重复多次。

有没有办法实现同样的事情，但要快得多呢？

编辑表模式

wordId - integer, auto increment
languageId - varchar (FK), values like cs, en, de, ... 
word - varchar, word itself

数据结构示例

wordId   languageId   word
--------------------------
1        cs           abatyše
...
100000   cs           zip
100001   en           aardvark
...
etc

SQL

CREATE TABLE Language (
  languageId VARCHAR(20)  NOT NULL  ,
  name VARCHAR(255)  NULL    ,
PRIMARY KEY(languageId));

CREATE TABLE Word (
  wordId INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  languageId VARCHAR(20)  NOT NULL  ,
  word VARCHAR(255)  NULL    ,
PRIMARY KEY(wordId)  ,
INDEX Word_FK_Language(languageId),
  FOREIGN KEY(languageId)
    REFERENCES Language(languageId)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);

Answer 1

如果您有一个If列，并且元素之间的差距并不大(删除的元素不多，否则一些元素将被更频繁地选择)，那么尝试这个查询。

SELECT * FROM `table` 
   WHERE id >= 
      (SELECT FLOOR( MAX(id) * RAND()) FROM `table` WHERE languageId = 'en' ) 
   AND languageId = 'en'
   ORDER BY id LIMIT 1;

在这里看不同的例子，http://akinas.com/pages/en/blog/mysql_random_row/

ps:我刚刚意识到，只有在不需要languageId的情况下，它才能正常工作，否则，相同languageId的ID缺口可能会很大。

更新的尝试一下这个，它的速度可能快几倍。我根据您查询的执行时间检查了它。快两倍..。

SELECT d.* FROM
  (SELECT @rn:=0 ) r, 
  (SELECT FLOOR(count(*)*RAND()) as rnd FROM `Word` WHERE languageId = 'en') t,  
  (SELECT @rn:=@rn+1 as rn, `Word`.* FROM `Word` WHERE languageId = 'en' ) d 
WHERE d.rn >= t.rnd LIMIT 1

基本上，它仍然创建某种连续的it，但不按它们进行排序。

最后一次更新这个可能会更快(取决于生成的随机数)

SELECT d.* FROM
  ( SELECT @rn:=@rn+1 as rn, w.*, t.rnd rnd FROM 
     (SELECT @rn:=0 ) r, 
     (SELECT FLOOR(count(*)*RAND()) rnd FROM `Word` WHERE languageId = 'en') t, 
     `Word` w 
   WHERE w.languageId = 'en' AND @rn<t.rnd
  ) d 
WHERE d.rn=d.rnd

Answer 2

首先，确保您的表有正确的索引。它有主键吗？languageId是一个索引吗？确定是的。

其次，您只对单词感兴趣，而不是对languageId或表中的其他字段感兴趣吗？如果你是，你需要这个：

SELECT word_field FROM Word...

通配符选择返回所有内容，但不需要检索永远不会使用的数据。

第三，如果重复多次，那么您只是在循环中运行相同的查询吗？更改LIMIT语句以在一个查询中返回更多单词：

-- for 10 words
... LIMIT 10

您可以存储此结果供以后使用，而不必重新查询数据库。

最后，您可以运行您的查询，但是在查询前面有EXPLAIN，可以全面了解MySQL在运行它时所做的工作。

EXPLAIN SELECT word_field FROM Word...

使用它，您可以确定您的查询在哪里运行缓慢。

Answer 3

您可以根据单词的第一个字母对表进行分区，随机选择一个字母，然后使用现有的排序来选择该分区中的随机单词。在现代服务器上对50,000行进行排序应该是相当快的。我认为大多数数据库排序都是n lg(n)，所以1/26的记录排序速度应该快50倍以上。就性能而言，分区选择应该可以忽略不计。另一方面，fuzzyDunlop关于重复使用同一列表的评论无疑仍将在执行50次左右之后胜出。编辑:我想我把我在windows calc上的日志搞砸了，所以我要说:它应该快26倍以上;)