MYSQL:创建表中的索引关键字以及何时使用它 [英] MYSQL: Index keyword in create table and when to use it

问题描述

index关键字是什么意思，其作用是什么?我了解这是为了加快查询速度，但是我不太确定如何做到这一点.

What does index keyword 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.

何时选择要索引的列?

下面的create table查询中显示了index关键字用法的示例:

A sample of index keyword usage is shown below in create table query:

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

;

推荐答案

我建议阅读 MySQL参考手册.它指出使用了索引...

I'd recommend reading How MySQL Uses Indexes from the MySQL Reference Manual. It states that indexes are used...

• 要快速找到与WHERE子句匹配的行.
• 从考虑中消除行.
• 在执行联接时从其他表中检索行.
• 查找特定索引列的MIN()或MAX()值.
• 对表进行排序或分组(在某些条件下).
• 仅使用索引而不查询数据行来优化查询.

• To find the rows matching a WHERE clause quickly.
• To eliminate rows from consideration.
• To retrieve rows from other tables when performing joins.
• To find the MIN() or MAX() 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.

数据库中的索引的作用就像一本书中的索引.您可以更快地在书中找到所需内容，因为索引按字母顺序列出. MySQL使用 B树而不是字母列表来组织其索引，这更快出于其目的(但对于人类来说则需要更长的时间).

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-trees to organize its indexes, which is quicker for its purposes (but would take a lot longer for a human).

使用更多索引意味着要占用更多空间(以及维护索引的开销)，因此只有在满足上述使用条件的列上使用索引才真正值得.

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.

在您的示例中，id和blog_post_id列都使用索引(PRIMARY KEY也是索引)，以便应用程序可以更快地找到它们.对于id，很可能允许用户快速修改或删除评论，对于blog_post_id，则应用程序可以快速找到给定帖子的所有评论.

In your example, the id and blog_post_id columns both uses indexes (PRIMARY KEY is 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.

您会注意到，email列没有索引.这意味着通过特定的电子邮件地址搜索所有博客文章可能会花费很长时间.如果您想添加特定电子邮件地址中的所有注释，那么也可以在其中添加索引.

You'll notice that there is no index for the email column. 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.

这篇关于MYSQL:创建表中的索引关键字以及何时使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！