如何优化MySQL选择查询？
我有一个包含90,000多行的数据库表。
每一行都包含一个形容词、副词、名词或动词的单词(如Type列所示)。
我需要运行一个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，这意味着它是查询的有效选项，而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;有人对如何优化这个查询以提高运行时间有任何想法吗？
回答 1
Stack Overflow用户
发布于 2019-08-17 01:16:35
综合指数
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更好的方法是将其扩展到覆盖索引中。
INDEX(type, free_writing_prompt, -- in either order
word_length, -- last (for indexing purposes)
word) -- to make "covering"您的第一个SELECT将与此覆盖指数很好地工作。可惜，它并不完美，因为它将构建一个几千个临时表，对其进行排序，然后剥离3行。
https://stackoverflow.com/questions/57532553
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号