MYSQL: 在创建表时使用索引关键字以及何时使用它

Question

index 关键字是什么意思以及它的作用是什么？我知道它是为了加速查询，但我不太确定它是如何实现的。

什么时候该选择哪些列进行索引？

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

;

- Ngu Soon Hui

Answer 1

我建议阅读MySQL参考手册中的How MySQL Uses Indexes。它指出索引用于以下目的：

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

数据库中的索引类似于书籍中的索引。您可以更快地找到您要查找的内容，因为索引按字母顺序列出。MySQL使用B-trees来组织其索引，这对其目的来说更快（但对人类来说需要更长时间）。

使用更多的索引意味着使用更多的空间（以及维护索引的开销），因此只有在满足上述使用标准的列上才值得使用索引。

在你的例子中，id和blog_post_id列都使用了索引（PRIMARY KEY也是一个索引），以便应用程序可以更快地找到它们。对于id，很可能这使用户可以快速修改或删除评论，在blog_post_id的情况下，应用程序可以快速找到给定帖子的所有评论。

你会注意到email列没有索引。这意味着按特定电子邮件地址搜索所有博客文章可能需要相当长的时间。如果要添加按特定电子邮件地址搜索所有评论的功能，将该列添加到索引中可能是有意义的。

- Mr Potato Head

Answer 2

这个关键字的意思是在表中同时创建一个基于列 blog_post_id 的索引。

像这样的查询：

SELECT *
FROM blog_comment
WHERE blog_post_id = @id

将使用此索引在该字段上进行搜索以提高速度。

此列上还有一个外键。

当您决定删除博客文章时，数据库将需要检查此表以查看是否存在孤立的评论。索引也将加快此检查的速度，因此查询如下：

DELETE
FROM blog_post
WHERE ...

将会更快地运行。

- Quassnoi