mysql - 如何优化我的 MySQL 选择查询？

Question

我有一个包含超过 90,000 行的数据库表。

每行包含一个词，它可以是形容词、副词、名词或动词(如类型列所示)。

我需要运行一个 MySQL 查询，该查询返回少量(例如 3 - 10)随机选择的名词，其中单词的长度介于两个提供的数字(例如 4 - 8)之间。

我试过的查询的性能不是很好。

这是表结构:

CREATE TABLE `words` (
  `id` int(11) NOT NULL,
  `type` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `word` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `variations` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `free_writing_prompt` smallint(1) NOT NULL DEFAULT '0',
  `word_length` smallint(5) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `words`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `word_type` (`type`,`word`),
  ADD KEY `type` (`type`),
  ADD KEY `Word Length` (`word_length`);

ALTER TABLE `words`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

free_writing_prompt 列设置为 0 或 1，1 表示它是查询的有效选项，0 表示忽略它。目前，所有名词都将此列设置为 1，但计划将许多单词更改为 0，因为它们不是应返回的名词。

这是行数，按类型:

形容词:21,499
副词:4,475
名词:58,670
动词:8,978

这是我尝试的第一个查询:

SELECT  word
    FROM  words
    WHERE  type='n'
      AND  free_writing_prompt=1
      AND  CHAR_LENGTH(word)>=4
      AND  CHAR_LENGTH(word)<=8
    ORDER BY  RAND()
    LIMIT  3;

在此之后，我决定添加 word_length 列，它最初不是表定义的一部分，因为当我可以预先计算并存储该值时，对所有这些行使用 CHAR_LENGTH 函数似乎效率不高。

我还认为在 word_length 列上构建一个键索引是值得的，但我不相信它真的有帮助。

然后我尝试了以下三个查询，但它们仍然需要大约 16 秒才能运行:

SELECT  word
    FROM  words
    WHERE  type='n'
      AND  free_writing_prompt=1
      AND  word_length>=4
      AND  word_length<=8
    ORDER BY  RAND()
    LIMIT  3;
SELECT  word
    FROM  words
    WHERE  type='n'
      AND  free_writing_prompt=1
      AND  word_length IN (
        SELECT  word_length
            FROM  words
            WHERE  word_length>=4
              AND  word_length<=8
                          )
    ORDER BY  RAND()
    LIMIT  3;
SELECT  word
    FROM  words
    WHERE  word_length IN (
        SELECT  word_length
            FROM  words
            WHERE  type='n'
              AND  free_writing_prompt=1
              AND  word_length>=4
              AND  word_length<=8
                          )
    ORDER BY  RAND()
    LIMIT  3;

然后我尝试了这个 Union 查询，它稍微快了一点(但可能不足以达到统计显着性):

SELECT * FROM (
    SELECT word FROM words WHERE type='n' AND free_writing_prompt=1 AND word_length=4
    UNION
    SELECT word FROM words WHERE type='n' AND free_writing_prompt=1 AND word_length=5
    UNION
    SELECT word FROM words WHERE type='n' AND free_writing_prompt=1 AND word_length=6
    UNION
    SELECT word FROM words WHERE type='n' AND free_writing_prompt=1 AND word_length=7
    UNION
    SELECT word FROM words WHERE type='n' AND free_writing_prompt=1 AND word_length=8
) a
ORDER BY RAND() LIMIT 3;

有人对如何优化此查询以提高运行时间有任何想法吗？

Answer 1

综合指数:

    WHERE  type='n'
      AND  free_writing_prompt=1
      AND  word_length>=4
      AND  word_length<=8

乞求

INDEX(type, free_writing_prompt,   -- in either order
      word_length)                 -- last

更好的是，将其拉伸(stretch)成一个覆盖索引:

INDEX(type, free_writing_prompt,   -- in either order
      word_length,                 -- last (for indexing purposes)
      word)                        -- to make "covering"

您的第一个 SELECT 可以很好地使用这个覆盖索引。唉，它不会是完美的，因为它会建立一个几千个临时表，对其进行排序，然后剥离 3 行。