MYSQL：创建表中的索引关键字以及何时使用它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/561446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL: Index keyword in create table and when to use it
提问by Graviton
What does indexkeyword mean and what function it serves? I understand that it is meant to speed up querying, but I am not very sure how this can be done.
什么index关键字的含义以及它的功能服务？我知道这是为了加快查询速度，但我不太确定如何做到这一点。
When how to choose the column to be indexed?
何时选择要索引的列？
A sample of indexkeyword usage is shown below in create tablequery:
查询中的index关键字使用示例如下所示create table：
CREATE TABLE `blog_comment`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`blog_post_id` INTEGER,
`author` VARCHAR(255),
`email` VARCHAR(255),
`body` TEXT,
`created_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `blog_comment_FI_1` (`blog_post_id`),
CONSTRAINT `blog_comment_FK_1`
FOREIGN KEY (`blog_post_id`)
REFERENCES `blog_post` (`id`)
)Type=MyISAM
;
;
采纳答案by David Grant
I'd recommend reading How MySQL Uses Indexesfrom the MySQL Reference Manual. It states that indexes are used...
我建议阅读MySQL 参考手册中的MySQL如何使用索引。它指出使用索引...
- To find the rows matching a
WHEREclause quickly. - To eliminate rows from consideration.
- To retrieve rows from other tables when performing joins.
- To find the
MIN()orMAX()value for a specific indexed column. - To sort or group a table (under certain conditions).
- To optimize queries using only indexes without consulting the data rows.
WHERE快速查找与子句匹配的行。- 从考虑中排除行。
- 在执行连接时从其他表中检索行。
- 查找特定索引列的
MIN()或MAX()值。 - 对表进行排序或分组（在特定条件下）。
- 仅使用索引优化查询而不查询数据行。
Indexes in a database work like an index in a book. You can find what you're looking for in an book quicker, because the index is listed alphabetically. Instead of an alphabetical list, MySQL uses B-treesto organize its indexes, which is quicker for its purposes (but would take a lot longer for a human).
数据库中的索引就像书中的索引一样工作。您可以更快地在书中找到您要查找的内容，因为索引是按字母顺序列出的。MySQL 使用B 树来组织索引，而不是按字母顺序排列的列表，这对于它的目的来说更快（但对人类来说会花费更长的时间）。
Using more indexes means using up more space (as well as the overhead of maintaining the index), so it's only really worth using indexes on columns that fulfil the above usage criteria.
使用更多的索引意味着使用更多的空间（以及维护索引的开销），因此只有在满足上述使用标准的列上使用索引才真正值得。
In your example, the idand blog_post_idcolumns both uses indexes (PRIMARY KEYis an index too) so that the application can find them quicker. In the case of id, it is likely that this allows users to modify or delete a comment quickly, and in the case of blog_post_id, so the application can quickly find all comments for a given post.
在您的示例中，id和blog_post_id列都使用索引（PRIMARY KEY也是索引），以便应用程序可以更快地找到它们。在 的情况下id，这可能允许用户快速修改或删除评论，而在 的情况下blog_post_id，因此应用程序可以快速找到给定帖子的所有评论。
You'll notice that there is no index for the emailcolumn. This means that searching for all blog posts by a particular e-mail address would probably take quite a long time. If searching for all comments by a particular e-mail address is something you'd want to add, it might make sense to add an index to that too.
您会注意到该email列没有索引。这意味着通过特定电子邮件地址搜索所有博客文章可能需要很长时间。如果您想要添加通过特定电子邮件地址搜索所有评论的内容，那么也可以为其添加索引。
回答by Quassnoi
This keyword means that you are creating an index on column blog_post_idalong with the table.
此关键字意味着您正在blog_post_id与表一起在列上创建索引。
Queries like that:
像这样的查询：
SELECT *
FROM blog_comment
WHERE blog_post_id = @id
will use this index to search on this field and run faster.
将使用此索引在此字段上进行搜索并运行得更快。
Also, there is a foreign keyon this column.
此外，foreign key此列上有一个。
When you decide to delete a blog post, the database will need check against this table to see there are no orphan comments. The index will also speed up this check, so queries like
当您决定删除博客文章时，数据库将需要对照此表检查是否有孤立评论。索引也会加快这个检查，所以查询像
DELETE
FROM blog_post
WHERE ...
will also run faster.
也会跑得更快。
