英文:
Why does Mysql change query execution plan based on query parameters?
问题
以下是您要翻译的内容：
我有以下查询。
SELECT *
FROM user u
LEFT JOIN operator o ON o.id = u.id
WHERE u.user_type_id IN (2,4) AND u.is_enabled = 1 AND u.office_id = 225
如果我在上面的查询上运行explain，它会显示它使用了表user的索引IX_user_type。
如果我只是改变了office_id的比较值，如下所示，执行计划会发生变化。
SELECT *
FROM user u
LEFT JOIN operator o ON o.id = u.id
WHERE u.user_type_id IN (2,4) AND u.is_enabled = 1 AND u.office_id = 32365487
在这种情况下，explain显示用于表user的索引是fk_user_office,IX_user_is_enabled。
我进行了一些测试，从性能角度来看，第一个执行计划比第二个执行计划要好得多。现在，我知道我可以强制MySQL使用我想要的索引，但我想了解为什么会发生这种情况。为什么MySQL会根据查询参数选择一个索引而不是另一个？
英文:
I have the following query.
SELECT *
FROM user u
LEFT JOIN operator o ON o.id = u.id
WHERE u.user_type_id IN (2,4) AND u.is_enabled = 1 AND u.office_id = 225
If I run explain on the query above, it shows that it uses the index IX_user_type for the table user.
If I just change the office_id comparison value like the following, the execution plan changes.
SELECT *
FROM user u
LEFT JOIN operator o ON o.id = u.id
WHERE u.user_type_id IN (2,4) AND u.is_enabled = 1 AND u.office_id = 32365487
In this case, the explain shows that the indexes used for the table user are fk_user_office,IX_user_is_enabled.
I made some tests and would say, performance wise, the first execution plan is much better than the second one. Now, I know I can force Mysql to use the index I want but, I would like to understand why this happens. Why would Mysql pick an index instead of another based on a query parameter?
答案1
得分: 3
MySQL可能决定不使用office_id上的索引，如果您搜索的值太常见。
类似地，为什么书籍的索引不包括像“the”这样的常见词？因为这些常见词出现在大多数页面上。在索引后面列出这些页面在相应单词下是不必要的，因为告诉读者在没有索引查找的情况下阅读整本书更容易。
同样，如果MySQL估计您搜索的特定值在大部分页面上都出现，它会查找其他索引（如果有其他条件），如果找不到索引，则会采用表扫描。
在这种情况下，我想问一下，您能确认这个表中的office_id 225非常常见吗？
还有一个想法：对于您展示的查询，最好的索引将是在(office_id, is_enabled, user_type)上的复合索引。然后，它将能够使用该索引同时缩小三列的搜索。
您可能会喜欢我的演示文稿《如何设计索引，真的》或视频。我还在我的书《SQL反模式，第1卷：避免数据库编程的陷阱》中有一个关于索引设计的章节。
英文:
MySQL may decide not to use the index on office_id if the value you are searching for is too common.
By analogy, why doesn't a book include common words like "the" in the index at the back of the book? Because such common words occur on a majority of pages in the book. It's unnecessary to keep a list of those pages under the respective word in the index, because it's easier to tell the reader to read all the pages in the book, without the index lookup.
Similarly, if MySQL estimates that a given value you are searching for occurs on a high enough portion of the pages, it looks for another index if you have other conditions, and if none are found, then it resorts to a table-scan.
In this case, I'd ask if you can confirm that office_id 225 is very common in this table.
One more thought: The best index of all for the query you show would be a compound index on (office_id, is_enabled, user_type). Then it would be able to use that index to narrow down the search by all three columns at once.
You might like my presentation How to Design Indexes, Really or the video. I also have a chapter on index design in my book SQL Antipatterns, Volume 1:
Avoiding the Pitfalls of Database Programming.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论