英文:
Understand multiple-column index for mysql
问题
如果我需要查询：
select * from table where f1='f1' and f2='f2' and f3='f3'
而且我有f1_f2和f2_f3的索引，那么我的查询会利用这两个索引吗？还是我需要创建一个新的索引f1_f2_f3？
谢谢
英文:
Suppose I need to query:
select * from table where f1='f1' and f2='f2' and f3='f3'
And I have indexes for f1_f2 and f2_f3, then will my query take advantage of these two indexes? Or I need to create a new index f1_f2_f3?
Thanks
答案1
得分: 2
如果您想要为三列最佳方式进行搜索优化的索引，最好创建一个关于 f1_f2_f3 的三列索引。
MySQL 确实具有一个索引合并算法，但它并不像您想象的那样经常触发。通常，优化器只选择每个表引用的一个索引。此外，两个索引查找的索引合并效率低于使用一个索引进行单一搜索。
类似地，假设您使用书后面的索引来搜索一本书。您可以查找匹配一个单词“Earth”的页面，这将给您一个要检查的页面列表。然后，您查找另一个单词“history”的索引条目，这将给您一个不同的页面列表。然后，您需要比较这两个列表以找到同时出现在两个列表中的页面。
如果您有一个关于“Earth history”的索引条目，它只会提供匹配两个单词的页面，那将更为优化。
英文:
If you want an index to optimize the search for all three columns in the best way, you'd be better off creating a three-column index on f1_f2_f3.
MySQL does have an index merge algorithm, but it doesn't kick in as often as you'd think. Usually the optimizer chooses just one index per table reference. Besides, an index-merge of two index lookups would be less efficient than a single search using one index.
By analogy, suppose you are searching a book using the index at the back of the book. You could look up pages that match one word like "Earth" and that would give you one list of pages to check. Then you look up another index entry for pages that match another word "history" and that would give you a different list of pages. Then you'd have to compare the two lists to find the pages that appear in both lists.
If you had one index entry for "Earth history" that gives only pages that match both words, that would be more optimal.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论