在mysql中使用INDEXES的最大好处是什么? [英] What are the biggest benefits of using INDEXES in mysql?
问题描述
我知道我需要设置一个主键，并将任何应该唯一的东西设置为唯一键，但什么是索引，我该如何使用它们?
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.
通常最好有索引，因为它们加快 select 的速度比减慢 insert 的速度要快.
Usually it's better to have indexes, because they speed up select more than they slow down insert.
在 UPDATE 上，如果在 WHERE 子句中使用索引字段，索引可以向上加快速度，如果你update 索引字段之一.
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 也可以告诉你是不是 drop 和 index 比较好
Explain can also tell you if it is better to drop and index
EXPLAIN SELECT * FROM table1
WHERE indexedfield1 > indexedfield2
ORDER BY indexedfield3
如果选择的行很少，或者 MySQL 决定忽略索引(它不时这样做)，那么您最好删除索引，因为它减慢了insert的速度，但没有加快select的速度.
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中使用INDEXES的最大好处是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
