mysql - 良好实践：具有多个索引的表？
抱歉，
我确实有这个包含 3 列的迷你表，但是我将按一列或另一列列出值。
1）其中两列带有索引可以吗？
2）每个表应该只有一个索引吗？
3）在极限情况下，如果我们有一个包含 100 列的表，并且其中 50 列带有索引，这样可以吗？
谢谢， MEM
Sorry all,
I do have this mini table with 3 columns but, I will list the values either by one or other column.
1) Is it ok to have two of those columns with indexes?
2) Should we have only one index per table?
3) At the limit, if we have a table with 100 columns for example, and we have 50 of them with indexes, is this ok?
Thanks,
MEM
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
拥有多个索引，甚至一张表上有多个索引，都没有问题，只要您使用它们。
对您的查询运行 EXPLAIN 并检查正在使用哪些索引。如果存在任何查询未使用的索引，那么它们不会给您带来任何好处，只会减慢对表的修改速度。应删除这些未使用的索引。
如果您还没有这样做，您可能还想考虑多列索引。
It's fine to have many indexes, even multiple indexes on one table, as long as you are using them.
Run EXPLAIN on your queries and check which indexes are being used. If there are indexes that are not being used by any queries then they aren't giving you any benefit and are just slowing down modifications to the table. These unused indexes should be removed.
You may also want to consider multiple-column indexes if you have not already done so.
为了快速回答你的问题，我认为：
您的索引创建应该由您的查询驱动。查看您的系统上将有哪些查询并相应地创建索引。
To quickly answer your questions, I think:
Your index creation should be driven by your queries. See what queries are you going to have on your system and create indexes accordingly.
每个表拥有多个索引没有问题，而且每个表一个索引并不是真正的指导原则。
拥有太多索引效率低下，因为
编辑：根据 Pablo 和 Mark，您需要了解如何按顺序访问数据为您建立有效的指数。然后您可以细化和减少索引。
There is no problem with having more than one index per table, and no, one index per table isn't really a guideline.
Having too many indexes is inefficient because
Edit : As per Pablo and Mark, you need to understand how your data is being accessed in order for you to build effective indices. You can then refine and reduce the indexes.