为“OR”创建索引查询中的运算符
我有一些 MySQL 查询，其条件类似于
where field1=val1 or field2=val2
和 一些类似
where fieldx=valx and fieldy=valy and (field1=val1 or field2=val2)
如何通过创建索引来优化这些查询？我的直觉是为第一个查询的 field1 和 field2 创建单独的索引，因为它是一个 OR，所以复合索引可能不会有太大作用。
对于第二个查询，出于上述原因，我打算再次创建 2 个索引：fieldx、fieldy、field1 和 fieldx、fieldy、field2。
这个解决方案正确吗？这是一个非常大的表，所以我不能仅仅通过应用索引和解释查询来进行实验。
I have some MySQL queries with conditions like
where field1=val1 or field2=val2
and some like
where fieldx=valx and fieldy=valy and (field1=val1 or field2=val2)
How can I optimize these queries by creating indexes? My intuition is to create separate indexes for field1 and field2 for first query as it is an OR, so a composite index probably won't do much good.
For the second query I intend to create 2 indexes: fieldx, fieldy, field1 and fieldx,fieldy,field2 again for the above stated reason.
Is this solution correct? This is a really large table so I can't just experiment by applying indexes and explaining the query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
与所有 DBMS 优化问题一样，这取决于您的执行引擎。
我将从最简单的场景开始，每列有四个单独的索引。
这将确保以您未预料到的方式使用这些列的任何查询仍然可以正常运行（
fieldx/fieldy/field1索引对于仅使用fieldy 的查询来说为零）。任何像样的执行引擎都会首先有效地选择基数最低的索引，以减少结果集，然后基于此执行其他过滤器。
然后，仅如果您遇到性能问题，您可以考虑使用不同的索引来改进它。您应该测试生产类型数据的性能，而不是您自己构建的任何测试数据库（除非它们反映了生产的属性）。
请记住，数据库调优很少是“一劳永逸”的操作。您应该定期重新调整，因为性能取决于架构和您保存的数据。
即使架构从未改变，数据也可能有很大差异。关于您的评论“我只是无法通过应用索引和解释查询来进行实验”，这正是您应该做的。
如果您担心在生产中玩游戏（您应该担心），您应该设置另一个具有类似规格的环境，将生产数据复制到其中，然后在那里摆弄索引。
As with all DBMS optimisation questions, it depends on your execution engine.
I would start with the simplest scenario, four separate indexes on each of the columns.
This will ensure that any queries using those columns in a way you haven't anticipated will still run okay (a
fieldx/fieldy/field1index will be of zero use to a query only usingfieldy).Any decent execution engine will efficiently choose the index with lowest cardinality first so as to reduce the result set and then perform the other filters based on that.
Then, and only if you have a performance problem, you can look into improving it with different indexes. You should test performance on production-type data, not any test databases you have built yourself (unless they mirror the attributes of production anyway).
And keep in mind that database tuning is rarely a set-and-forget operation. You should periodically re-tune because performance depends both on the schema and the data you hold.
Even if the schema never changes, the data may vary wildly. Re your comment "I just cant experiment by applying indexes and explaining the query", that's exactly what you should be doing.
If you're worried about playing in production (and you should be), you should have another environment set up with similar specs, copy the production data across to it, then fiddle around with your indexes there.
这是正确的。
这是一个选项，另一个选项是 fieldx、fieldy、field1 上的索引和 field2 上的索引（与您的第一个查询相同！）。现在您还有 2 个索引，但第二个索引会小得多。您的第二个查询可以使用两个索引，较大的索引用于查询的 AND 部分，较小的索引用于 field2 的 OR 部分。 MySQL 现在应该足够智能了。
解释会帮助你。
That's correct.
That's one option, the other will be an index on fieldx, fieldy, field1 and an index on field2 (same as for you first query!). Now you also have 2 indexes, but the second one will be much smaller. Your second query can use both indexes, the bigger one for the AND-part of your query and the small index for the OR part of field2. MySQL should be smart enough nowadays.
EXPLAIN will help you out.