值得在列上添加索引只是为了使它们成为外键吗？
我的数据库中有一个 invitations 表，其中有一个 from 和 to 列，它们都是 userId 的外键我的 列。users 表中的
例如，如果用户 # 1 邀请用户 # 2 做某事，则在 invitations 表中，from 将为 1，to 将为2.
我会在where语句中使用to列，例如：
SELECT * FROM invitations WHERE `to` = $currentUserId
但是from列永远不会在WHERE语句中使用。
根据我所读到的内容，您应该只在 WHERE 语句中使用的列上添加索引，但是我还读到您应该始终创建外键。
我在 from 上创建索引只是为了添加外键是否有意义？
I have an invitations table in my database, which has got a from and to columns, which are both foreign keys for the userId column in my users table.
For example, if User # 1 invited User # 2 to do something, then in the invitations table, from will be 1, and to will be 2.
I will use the to column in where statements, such as:
SELECT * FROM invitations WHERE `to` = $currentUserId
But the from column will never be used in WHERE statements.
From what I've read, you should only add an index on the columns which will be used in WHERE statements, however I've also read that you should always create Foreign keys.
Does it make sense for me to create an index on from just to add a foreign key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
对于外键，您应该根据您对用户执行的操作添加一个外键。
在内部，您的 fkey 将在任何插入/更新/删除邀请以及任何更新/删除用户（id）时触发操作。
第一个将使用 users(id) 上的索引来检查用户是否存在。
另一个将本质上运行一个查询：
执行您定义的在邀请更新/删除时执行的任何操作，其中 to/from = :id。因此，如果您更改用户的 id（不太可能），您可以使用索引。如果您偶尔删除用户（可能性稍大一些），则可以利用索引。
正如另一个答案中指出的那样，索引对于带有 order by/limit 子句的查询也很有用。
In the case of foreign keys, you should add one depending on what you do with users.
Internally, your fkeys will fire an action on any insert/update/delete of invitations, but also on any update/delete of users(id).
The first of these will use the index on users(id) to check if the user exists.
The other will essentially run a query that goes:
do whatever you defined to do on update/delete from invitations where to/from = :id.So, if you change the id of users (very unlikely) you can make use of an index. And if you occasionally delete users (only slightly more likely), you can make use of the index.
And as point out in the other answer, indexes are also useful for queries with an order by/limit clause.
这并不是那么简单。这是一个很好的经验法则，但它的意义远不止于此。
您还应该对经常和始终排序的列进行索引，并始终在更改之前和之后进行测试，以查看性能是否有所提高。
一般来说，只要外键不是 where/order by 子句的一部分，外键就不会受益于在其上定义的索引。如果是，您应该将它们编入索引。 （再次强调经验法则）。
这看起来是优化 MySQL 的好资源- 查看底部，其中解释了索引。
It is not so cut and dry. It is a good rule of thumb, but there is more to it than just this.
You should also index columns that you order by often and always, always test before and after a change to see if you have improved performance.
Foreign keys in general will not benefit from having an index defined on them, so long as they are not part of where/order by clauses. If they are, you should index them. (again, rules of thumb).
This looks like a good resource for optimizing MySQL - look towards the bottom where it explains about indexing.