Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以查找相关的行。
Says our beloved MySQL manual.
我们钟爱的MySQL手册。
In that case, why don't just index every column?
在这种情况下，为什么不索引每一列呢?
And since I have this feeling that it would be a bigger hit to performance, when should we use indexes/best practices for indexes?
既然我觉得这会对性能造成更大的冲击，我们什么时候应该对索引使用索引/最佳实践?
Thanks in advance!
提前谢谢!
3 个解决方案
#1
13
Creating an index always comes at a cost: The more indices you have on a table, the more expensive it is to modify that table (i.e. inserts, updates and deletes take longer).
创建索引总是要付出代价的:一个表上的索引越多，修改该表的成本就越高(例如插入、更新和删除都需要更长的时间)。
In turn, queries that can use the indices will be faster. It's a classical tradeoff. On most tables a small number of commonly used indices is worth the cost, because queries happen often enough (or their performance is much more important than the modification performance).
反过来，可以使用索引的查询将会更快。这是一个经典的权衡。在大多数表中，少量常用索引是值得的，因为查询经常发生(或者它们的性能比修改性能重要得多)。
On the other hand, if you have some kind of log table that is updated very often, but queried only very rarely (for example in case of a catastrophic failure), then adding an index would add a big cost and provide very little advantage.
另一方面，如果您有某种日志表，它经常更新，但查询的频率非常低(例如，在发生灾难性故障时)，那么添加索引将增加很大的成本，并提供很少的优势。
Also: whether or not an index is useful depends a lot on the exact query to be executed. It's possible that you have indices spanning each column, but the query can't use it because the indices are in the wrong order, have the wrong information or the wrong format. So not all indices help all queries.
同样:索引是否有用在很大程度上取决于要执行的确切查询。可能您拥有跨越每个列的索引，但是查询不能使用它，因为索引的顺序错误，信息错误或格式错误。所以并不是所有的索引都能帮助查询。
#2
1
By your logic, you wouldn't index just every column, but every permutation of every column. The overhead involved in storing this information, and in keeping it up to date, would be utterly vast.
按照你的逻辑，你不会只索引每一列，而是索引每一列的所有排列。存储这些信息并使其及时更新所涉及的开销将是巨大的。
#3
1
Generally index is helpful if it has a good selectivity, i.e. when the query selects a little portion of data based on the value (or range) of indexed attribute.
如果索引具有良好的选择性，即当查询根据索引属性的值(或范围)选择一小部分数据时，通常索引是有用的。
Also indice are good for merge joins, when sorting rows by a joining attribute in both joined tables allows to match rows and retrieve data in one pass.
当通过两个已连接表中的连接属性对行进行排序时，indice还可以在一次传递中匹配行并检索数据。
As it was already mentioned, indexes slow down updates and take up some memory (which, by itself, slows down performance as well)
正如前面提到的，索引会降低更新速度并占用一些内存(这本身也会降低性能)
#1
13
Creating an index always comes at a cost: The more indices you have on a table, the more expensive it is to modify that table (i.e. inserts, updates and deletes take longer).
创建索引总是要付出代价的:一个表上的索引越多，修改该表的成本就越高(例如插入、更新和删除都需要更长的时间)。
In turn, queries that can use the indices will be faster. It's a classical tradeoff. On most tables a small number of commonly used indices is worth the cost, because queries happen often enough (or their performance is much more important than the modification performance).
反过来，可以使用索引的查询将会更快。这是一个经典的权衡。在大多数表中，少量常用索引是值得的，因为查询经常发生(或者它们的性能比修改性能重要得多)。
On the other hand, if you have some kind of log table that is updated very often, but queried only very rarely (for example in case of a catastrophic failure), then adding an index would add a big cost and provide very little advantage.
另一方面，如果您有某种日志表，它经常更新，但查询的频率非常低(例如，在发生灾难性故障时)，那么添加索引将增加很大的成本，并提供很少的优势。
Also: whether or not an index is useful depends a lot on the exact query to be executed. It's possible that you have indices spanning each column, but the query can't use it because the indices are in the wrong order, have the wrong information or the wrong format. So not all indices help all queries.
同样:索引是否有用在很大程度上取决于要执行的确切查询。可能您拥有跨越每个列的索引，但是查询不能使用它，因为索引的顺序错误，信息错误或格式错误。所以并不是所有的索引都能帮助查询。
#2
1
By your logic, you wouldn't index just every column, but every permutation of every column. The overhead involved in storing this information, and in keeping it up to date, would be utterly vast.
按照你的逻辑，你不会只索引每一列，而是索引每一列的所有排列。存储这些信息并使其及时更新所涉及的开销将是巨大的。
#3
1
Generally index is helpful if it has a good selectivity, i.e. when the query selects a little portion of data based on the value (or range) of indexed attribute.
如果索引具有良好的选择性，即当查询根据索引属性的值(或范围)选择一小部分数据时，通常索引是有用的。
Also indice are good for merge joins, when sorting rows by a joining attribute in both joined tables allows to match rows and retrieve data in one pass.
当通过两个已连接表中的连接属性对行进行排序时，indice还可以在一次传递中匹配行并检索数据。
As it was already mentioned, indexes slow down updates and take up some memory (which, by itself, slows down performance as well)
正如前面提到的，索引会降低更新速度并占用一些内存(这本身也会降低性能)