mysql - 在 MySQL 中使用组合索引

Question

我有一个单词转换表 (MySQL MyISAM)(现在是空的，但一旦插入单词就会变得很大)。

• id_word
• lang_original(来自原始单词的语言)VARCHAR(2)
• lang_target(来自翻译词的语言)VARCHAR(2)
• word(单词本身)VARCHAR(50)
• translation(翻译)VARCHAR(50)

只有两种 SELECT 请求该表的信息:

通过定义原始语言

SELECT translation FROM table WHERE lang_original='en' AND lang_target='de' AND word = 'house'
//this will return 'Haus'

让脚本自动检测原始语言

SELECT lang_original,translation FROM table WHERE lang_target='de' AND word = 'house'
//this will return 'en','Haus'

通常我会让这些字段具有单独的索引:

• id_word
• lang_original
• lang_target
• 单词

但是，我刚刚在另一个 thread 中阅读了有关组合索引的信息, 但我仍然对如何使用它们有一些疑问。

1. 我应该像这样只有 3 个索引吗？

   • id_word(主要/自动递增)
   • 组合:lang_target + word
   • 组合:lang_original + lang_target + word

2. 这些组合索引应该是 UNIQUE 还是 INDEX 类型？为什么？

3. 我是否还需要为 lang_original、lang_target 和 word 设置单独的索引，尽管它们永远不会被自己过滤？

更新 1 修复了 = 而不是 LIKE

Answer 1

正如我上面提到的，使用 EXPLAIN将向您展示如何执行查询以及正在使用或未使用哪些索引。

1) Should I only have 3 indexes, like this?

• id_word (primary / auto increment)
• combined: lang_target + word
• combined: lang_original + lang_target + word

拥有那些复合索引 就可以了。这两个复合索引之间有很多重叠，所以也许您可以找到一种方法将它们组合起来，如下所示:

(lang_target, word(10), lang_original)

我建议为 word 列使用合理的前缀大小，以便它们具有固定长度(比如 10)。绝大多数单词在前 10 个字符内是不同的，因此由于它们在 10 个字符内相同而必须从磁盘读取的情况很少见。前缀长度是索引大小和执行速度之间的平衡。更大的前缀会遇到更少的前缀冲突，但它会是一个更耗内存的索引。另一方面，较小的前缀将节省内存，但可能导致更多的前缀冲突(因此必须转到磁盘以在几条记录之间做出最终决定)。玩适合你的东西。也许是 5，也许是 15。

Should those combined indexes be UNIQUE or INDEX type? Why?

如果 (lang_target, word, lang_original) 将是一个唯一的组合(看起来是这样)，继续使用 UNIQUE 约束，但前提是您不使用前缀 分割word(在这种情况下，可能存在具有相同部分word 值的多个记录)。作为索引，我认为它们在功能上仍然是等效的。

Do I also need to set separated indexes for lang_original, lang_target and word despite the fact that they won't ever be filtered by their own?

复合索引中的列是从左到右使用的。在 (lang_target, word(10), lang_original) 中，如果您只在 lang_target 上查询条件，则可以使用索引，但如果您有条件lang_original，除非您同时查询 lang_target 和 word，否则无法使用它。因此，如果您需要在 没有 lang_target 条件的情况下进行查询，则只为 word 和/或 lang_original 创建其他索引.参见 this doc page on multiple column indexes以获得更多解释。

另一个想法:如果您的 lang_ 列总是只有两个字符，请将它们设为 CHAR 而不是 VARCHAR。对于这样大小的列，VARCHAR 使用每个值的额外字节来指定数据长度，而 CHAR 是固定长度。对于数百万条记录，每个字节都对大小和速度很重要。