I just found out that in order to prevent full table scans during joins, I need to add indexes to my foreign keys in my rails app. But I'm unsure exactly what adding indexes to foreign keys means, how it works, and how it boosts performance.
我刚刚发现，为了防止在连接期间进行全表扫描，我需要在我的rails应用程序中向外键添加索引。但我不确定向外键添加索引的确切含义，它是如何工作的，以及如何提高性能。
2 个解决方案
#1
2
I think wikipedia has a good summary of what indexes are and do. See here: http://en.wikipedia.org/wiki/Index_%28database%29
我认为*很好地总结了什么是索引。在这里看到的:http://en.wikipedia.org/wiki/Index_%28database%29
Indexes can/do increase the size of the database. They effectivily cache an order for your data. When you index with respect to a foreign key relationship you are going to cache default ordering for your table which can reduce the size of the items that need to be searched in your table or can speed up joins.
索引可以/确实增加数据库的大小。它们有效地缓存数据的订单。当对外键关系进行索引时，您将缓存表的默认排序，它可以减少需要在表中搜索的项的大小，或者可以加速连接。
Consider this contrived example:
考虑下面的例子:
I have a table called employee:
我有一张桌子叫员工:
Employee ID | Employee code | Employee Name
员工ID |员工代码|员工姓名
1 | 0003 | Richard
2 | 0002 | Bob
3 | 0008 | Tim
1 | 0003 | Richard 2 | 0002 | Bob 3 | 0008 | Tim
I want to join to a sorted list of employee codes:
我想加入员工代码的排序列表:
0003 0008
0003 0008
This will mean without indexes you have efficiency of search of O(n2). With the table ordered by Employee Code, you can see a search efficency given by a binary search tree: http://en.wikipedia.org/wiki/Binary_search_algorithm . Effectively it can find 0003 by guessing its location and finding it getting forever closer, as opposed to searching every single row.
这意味着如果没有索引，就会有搜索O(n2)的效率。通过按员工代码排序的表，您可以看到二叉搜索树提供的搜索效率:http://en.wikipedia.org/wiki/Binary_search_algorithm。通过猜测它的位置，并发现它变得越来越近，而不是搜索每一行，它可以有效地找到0003。
Im not saying that your database is using any particular algorithm, but there are algorithms that require ordering of data by keys in order to do more efficient searches.
我不是说你的数据库正在使用任何特定的算法，但是有一些算法需要按键排序，以便进行更有效的搜索。
You probably want to consider removing indexes when you no longer require them too, since it impacts database size.
您可能希望在不再需要索引时考虑删除索引，因为它影响数据库大小。
#2
0
It's no different than an index on any other field. It potentially helps the optimizer find specific rows in those tables. FKs are often used as join fields, so they are natural candidates for indexing in these cases.
它和其他字段上的索引没有什么不同。它可能帮助优化器找到这些表中的特定行。FKs通常用作联接字段，因此在这些情况下它们是索引的自然候选。
#1
2
I think wikipedia has a good summary of what indexes are and do. See here: http://en.wikipedia.org/wiki/Index_%28database%29
我认为*很好地总结了什么是索引。在这里看到的:http://en.wikipedia.org/wiki/Index_%28database%29
Indexes can/do increase the size of the database. They effectivily cache an order for your data. When you index with respect to a foreign key relationship you are going to cache default ordering for your table which can reduce the size of the items that need to be searched in your table or can speed up joins.
索引可以/确实增加数据库的大小。它们有效地缓存数据的订单。当对外键关系进行索引时，您将缓存表的默认排序，它可以减少需要在表中搜索的项的大小，或者可以加速连接。
Consider this contrived example:
考虑下面的例子:
I have a table called employee:
我有一张桌子叫员工:
Employee ID | Employee code | Employee Name
员工ID |员工代码|员工姓名
1 | 0003 | Richard
2 | 0002 | Bob
3 | 0008 | Tim
1 | 0003 | Richard 2 | 0002 | Bob 3 | 0008 | Tim
I want to join to a sorted list of employee codes:
我想加入员工代码的排序列表:
0003 0008
0003 0008
This will mean without indexes you have efficiency of search of O(n2). With the table ordered by Employee Code, you can see a search efficency given by a binary search tree: http://en.wikipedia.org/wiki/Binary_search_algorithm . Effectively it can find 0003 by guessing its location and finding it getting forever closer, as opposed to searching every single row.
这意味着如果没有索引，就会有搜索O(n2)的效率。通过按员工代码排序的表，您可以看到二叉搜索树提供的搜索效率:http://en.wikipedia.org/wiki/Binary_search_algorithm。通过猜测它的位置，并发现它变得越来越近，而不是搜索每一行，它可以有效地找到0003。
Im not saying that your database is using any particular algorithm, but there are algorithms that require ordering of data by keys in order to do more efficient searches.
我不是说你的数据库正在使用任何特定的算法，但是有一些算法需要按键排序，以便进行更有效的搜索。
You probably want to consider removing indexes when you no longer require them too, since it impacts database size.
您可能希望在不再需要索引时考虑删除索引，因为它影响数据库大小。
#2
0
It's no different than an index on any other field. It potentially helps the optimizer find specific rows in those tables. FKs are often used as join fields, so they are natural candidates for indexing in these cases.
它和其他字段上的索引没有什么不同。它可能帮助优化器找到这些表中的特定行。FKs通常用作联接字段，因此在这些情况下它们是索引的自然候选。