在mysql中使用INDEXES有什么好处？ [英] What are the biggest benefits of using INDEXES in mysql?

问题描述

我知道我需要设置一个主键，并设置任何唯一的键作为唯一键，但什么是INDEX以及如何使用它们？

I know I need to have a primary key set, and to set anything that should be unique as a unique key, but what is an INDEX and how do I use them?

有什么好处？优点&缺点？我注意到我可以使用它们，我应该什么时候使用它们？

What are the benefits? Pros & Cons? I notice I can either use them or not, when should I?

推荐答案

简答：

索引加速 SELECT 并减慢 INSERT 的速度。

Short answer:
Indexes speed up SELECT's and slow down INSERT's.

通常最好有索引，因为它们加速选择比减速 insert 。

Usually it's better to have indexes, because they speed up select more than they slow down insert.

在 UPDATE 上，索引可以加速方式如果在 WHERE 子句中使用了索引字段，那么如果更新其中一个，则会减慢速度索引字段。

On an UPDATE the index can speed things way up if an indexed field is used in the WHERE clause and slow things down if you update one of the indexed fields.

您如何知道何时使用索引

在 SELECT 语句前添加 EXPLAIN 。

如下所示：

Add EXPLAIN in front of your SELECT statement.
Like so:

EXPLAIN SELECT * FROM table1 
WHERE unindexfield1 > unindexedfield2 
ORDER BY unindexedfield3

将告诉你MySQL将在每个工作上做多少工作未编入索引的字段。

使用该信息可以决定是否值得添加索引。

Will show you how much work MySQL will have to do on each of the unindexed fields.
Using that info you can decide if it is worthwhile to add indexes or not.

解释也可以告诉你是否最好放弃和索引

Explain can also tell you if it is better to drop and index

EXPLAIN SELECT * FROM table1 
WHERE indexedfield1 > indexedfield2 
ORDER BY indexedfield3

如果选择的行很少，或者MySQL决定忽略索引（它会不时地执行此操作）然后您也可以放弃索引，因为 会减慢您的插入但不加快选择。

If very little rows are selected, or MySQL decided to ignore the index (it does that from time to time) then you might as well drop the index, because it is slowing down your inserts but not speeding up your select's.

然后再次也可能是你的select语句不够聪明。

（对不起，答案的复杂性，我试图保持简单，但失败了。）

Then again it might also be that your select statement is not clever enough.
(Sorry for the complexity in the answer, I was trying to keep it simple, but failed).

链接：

MySQL索引 - 什么是最佳实践？

这篇关于在mysql中使用INDEXES有什么好处？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！