mysql 索引太多？

mysql too many indexes?

提问by Hailwood

I am spending some time optimizing our current database.

我正在花一些时间优化我们当前的数据库。

I am looking at indexes specifically.

我正在专门查看索引。

There are a few questions:

有几个问题：

• Is there such a thing as too many indexes?
• What will indexes speed up?
• What will indexes slow down?
• When is it a good idea to add an index?
• When is it a bad idea to add an index?
• Pro's and Con's of multiple indexes vs multi-column indexes?

• 有没有索引太多这样的事情？
• 索引会加速什么？
• 索引会减慢什么？
• 什么时候添加索引是个好主意？
• 什么时候添加索引是个坏主意？
• 多索引与多列索引的优缺点？

回答by OMG Ponies

What will indexes speed up?

索引会加速什么？

Data retrieval -- SELECT statements.

数据检索——SELECT 语句。

What will indexes slow down?

索引会减慢什么？

Data manipulation -- INSERT, UPDATE, DELETE statements.

数据操作——INSERT、UPDATE、DELETE 语句。

When is it a good idea to add an index?

什么时候添加索引是个好主意？

If you feel you want to get better data retrieval performance.

如果您想获得更好的数据检索性能。

When is it a bad idea to add an index?

什么时候添加索引是个坏主意？

On tables that will see heavy data manipulation -- insertion, updating...

在将看到大量数据操作的表上——插入、更新......

Pro's and Con's of multiple indexes vs multi-column indexes?

多索引与多列索引的优缺点？

Queries need to address the order of columns when dealing with a covering index (an index on more than one column), from left to right in index column definition. The column order in the statement doesn't matter, only that of columns 1, 2 and 3 - a statement needs have a reference to column 1 before the index can be used. If there's only a reference to column 2 or 3, the covering index for 1/2/3 could not be used.

在处理覆盖索引（多列上的索引）时，查询需要在索引列定义中从左到右处理列的顺序。语句中的列顺序无关紧要，只有第 1、2 和 3 列的顺序 - 在使用索引之前，语句需要引用第 1 列。如果只有第 2 或 3 列的引用，则不能使用 1/2/3 的覆盖索引。

In MySQL, only one index can be used per SELECT/statement in the query (subqueries/etc are seen as a separate statement). And there's a limit to the amount of space per table that MySQL allows. Additionally, running a function on an indexed column renders the index useless - IE:

在 MySQL 中，查询中的每个 SELECT/语句只能使用一个索引（子查询/等被视为单独的语句）。并且 MySQL 允许的每个表的空间量是有限制的。此外，在索引列上运行函数会使索引无用 - IE：

WHERE DATE(datetime_column) = ...

回答by Bill Karwin

I disagree with some of the answers on this question.

我不同意这个问题的一些答案。

Is there such a thing as too many indexes?

有没有索引太多这样的事情？

Of course. Don't create indexes that aren't used by any of your queries. Don't create redundant indexes. Use tools like pt-duplicate-key-checkerand pt-index-usageto help you discover the indexes you don't need.

当然。不要创建任何查询都不使用的索引。不要创建冗余索引。使用pt-duplicate-key-checker和pt-index-usage 等工具来帮助您发现不需要的索引。

What will indexes speed up?

索引会加速什么？

• Search conditions in the WHERE clause.
• Join conditions.
• Some cases of ORDER BY.
• Some cases of GROUP BY.
• UNIQUE constraints.
• FOREIGN KEY constraints.
• FULLTEXT search.

• WHERE 子句中的搜索条件。
• 加盟条件。
• ORDER BY 的一些情况。
• GROUP BY 的一些情况。
• 唯一约束。
• 外键约束。
• 全文搜索。

Other answers have advised that INSERT/UPDATE/DELETE are slower the more indexes you have. That's true, but consider that many uses of UPDATE and DELETE also have WHERE clauses and in MySQL, UPDATE and DELETE support JOINs too. Indexes may benefit these queries more than making up for the overhead of updating indexes.

其他答案建议 INSERT/UPDATE/DELETE 速度越慢，索引越多。这是真的，但考虑到 UPDATE 和 DELETE 的许多用途也有 WHERE 子句，在 MySQL 中，UPDATE 和 DELETE 也支持 JOIN。索引可能比弥补更新索引的开销更有益于这些查询。

Also, InnoDB locks rows affected by an UPDATE or DELETE. They call this row-level locking, but it's really index-level locking. If there's no index to narrow down the search, InnoDB has to lock a lot more rows than the specific row you're changing. It can even lock allthe rows in the table. These locks block changes made by other clients, even if they don't logically conflict.

此外，InnoDB 锁定受 UPDATE 或 DELETE 影响的行。他们称之为行级锁定，但它实际上是索引级锁定。如果没有索引来缩小搜索范围，InnoDB 必须锁定比您正在更改的特定行多得多的行。它甚至可以锁定表中的所有行。这些锁会阻止其他客户端所做的更改，即使它们在逻辑上没有冲突。

When is it a good idea to add an index?

什么时候添加索引是个好主意？

If you know you need to run a query that would benefit from an index in one of the above cases.

如果您知道在上述情况之一中您需要运行可从索引中受益的查询。

When is it a bad idea to add an index?

什么时候添加索引是个坏主意？

If the index is a left-prefix of another existing index, or the index doesn't help any of the queries you need to run.

如果索引是另一个现有索引的左前缀，或者该索引对您需要运行的任何查询没有帮助。

Pro's and Con's of multiple indexes vs multi-column indexes?

多索引与多列索引的优缺点？

In some cases, MySQL can perform index-merge optimization, and either union or intersect the results from independent index searches. But it gives better performance to define a single index so the index-merge doesn't need to be done.

在某些情况下，MySQL 可以执行索引合并优化，并联合或交叉独立索引搜索的结果。但是定义单个索引可以提供更好的性能，因此不需要进行索引合并。

For one of my consulting customers, I defined a multi-column index on a many-to-many table where there was no index, and improved their join query by a factor of 94 million!

对于我的一位咨询客户，我在没有索引的多对多表上定义了多列索引，并将他们的连接查询提高了 9400 万倍！

Designing the right indexes is a complex process, based on the queries you need to optimize. You shouldn't make broad rules like "index everything" or "index nothing to avoid slowing down updates."

根据您需要优化的查询，设计正确的索引是一个复杂的过程。您不应该制定宽泛的规则，例如“索引所有内容”或“不索引任何内容以避免减慢更新速度”。

See also my presentation How to Design Indexes, Really.

另请参阅我的演示文稿如何设计索引，真的。

回答by duffymo

Is there such a thing as too many indexes?

有没有索引太多这样的事情？

Indexes should be informed by the problem at hand: the tables, the queries your application will run, etc.

手头的问题应该告知索引：表、应用程序将运行的查询等。

What will indexes speed up?

索引会加速什么？

SELECTs.

选择。

What will indexes slow down?

索引会减慢什么？

INSERTs will be slower, because you have to update the index.

INSERT 会更慢，因为您必须更新索引。

When is it a good idea to add an index?

什么时候添加索引是个好主意？

When your application needs another WHERE clause.

当您的应用程序需要另一个 WHERE 子句时。

When is it a bad idea to add an index?

什么时候添加索引是个坏主意？

When you don't need it to query or enforce uniqueness constraints.

当您不需要它来查询或强制执行唯一性约束时。

Pros and Cons of multiple indexes vs multi-column indexes?

多索引与多列索引的优缺点？

I don't understand the question. If you have a uniqueness constraint that includes multiple columns, by all means model it as such.

我不明白这个问题。如果您有一个包含多列的唯一性约束，请务必对其进行建模。

回答by RPM1984

Is there such a thing as too many indexes?

有没有索引太多这样的事情？

Yes. Don't go outlooking to create indexes, create them as necessary.

是的。不要出去寻找创建索引，根据需要创建它们。

What will indexes speed up?

索引会加速什么？

Any queries against the indexes table/view.

对索引表/视图的任何查询。

What will indexes slow down?

索引会减慢什么？

Any INSERT statements against the indexed table will be slowed down, because each new record will need to be indexed.

任何针对索引表的 INSERT 语句都会变慢，因为每个新记录都需要被索引。

When is it a good idea to add an index?

什么时候添加索引是个好主意？

When a query is not running at an acceptable speed. You may be filtering on records that are not part of the clustered PK, in which case you should add indexes based on the filters you are searching upon (if the performance deems fit).

当查询未以可接受的速度运行时。您可能正在过滤不属于集群 PK 的记录，在这种情况下，您应该根据您正在搜索的过滤器添加索引（如果性能认为合适）。

When is it a bad idea to add an index?

什么时候添加索引是个坏主意？

When you do it for the sake of it- i.e over-optimization.

当你为了它而做的时候- 即过度优化。

Pro's and Con's of multiple indexes vs multi-column indexes?

多索引与多列索引的优缺点？

Depends on the queries you are trying to improve.

取决于您要改进的查询。

回答by Daniel Fath

Is there such a thing as too many indexes?

有没有索引太多这样的事情？

Yup, like all things, too many indexes will slow down data manipulation.

是的，就像所有事情一样，太多的索引会减慢数据操作的速度。

When is it a good idea to add an index?

什么时候添加索引是个好主意？

A good idea to add an index is when your queries are too slow (i.e. you have too many joins in your queries). You should use this optimization only after you built a solid model, to tweak the performance.

添加索引的一个好主意是当您的查询太慢时（即您的查询中有太多连接）。您应该仅在构建实体模型后使用此优化来调整性能。