MySQL中INDEX、PRIMARY、UNIQUE、FULLTEXT 的区别

在 MySQL 中，我们经常需要对表进行索引操作，以提高数据的查询效率，然而在索引操作中，常用的有四种索引类型，即 INDEX、PRIMARY、UNIQUE、FULLTEXT。那么这四种索引到底有何不同呢？本文将详细介绍它们的区别。

阅读更多：MySQL 教程

INDEX

索引的作用是优化数据的查询速度。在MySQL中，通常使用B+Tree索引和Hash索引。而INDEX就是指B+Tree索引。它可以让数据库更快地完成对数据的查找和筛选工作，因为它会按照一定规则对数据进行排序，形成一个树形的数据结构，方便快速查找。我们可以在一个表上创建多个INDEX并且可以在多列上创建INDEX，其语法如下：

CREATE INDEX idx_name ON table_name (col1, col2, ...);

其中，idx_name为索引的名称，table_name为表名，col1, col2, ...为该索引包含的列名。

需要注意的是，虽然索引可以加快查询速度，但它同时也会占用一定的存储空间和增加写入数据的时间开销。

PRIMARY

PRIMARY关键词是用于指明一个表中的主键，一个表只能有一个主键，而且它的值不能为NULL。当我们使用PRIMARY时，会自动创建一个名为PRIMARY的B+Tree索引，用于对主键列进行快速查找。另外，该关键字也可以用于修改表的主键属性，其语法如下：

ALTER TABLE table_name MODIFY id int PRIMARY KEY;

其中，table_name为表名，id为表中的某一列名，int表示该列的数据类型，PRIMARY KEY表示该列为主键。

UNIQUE

UNIQUE关键字用于指定一个列或多个列的唯一性，它与PRIMARY类似，但可以允许NULL值。在创建UNIQUE索引时，如果表中存在重复的索引值，会引发错误。其语法如下：

CREATE UNIQUE INDEX uni_name ON table_name (col1, col2, ...);

其中，uni_name为索引名称，table_name为表名，col1, col2, ...为该索引包含的列名。

需要说明的是，虽然UNIQUE和PRIMARY都可以保证列的唯一性，但它们还有一些区别。主要差异如下：

1. 一个表只能有一个PRIMARY键，而一个表可以有多个UNIQUE 键

2. 其他表都引用PRIMARY键，而外键并不可以引用UNIQUE键

3. PRIMARY键不能有NULL值，但是UNIQUE键可以拥有NULL值

FULLTEXT

FULLTEXT关键字用于指定一个列或多个列的FULLTEXT索引，它是指针对文本类型的数据创建的索引类型。与前面的索引类型基于B+Tree或Hash不同，它使用的是全文索引算法，即对关键字进行词法分析，分解成不同的项，并将这些项存储下来。其语法如下：

CREATE FULLTEXT INDEX ft_name ON table_name (col1, col2, ...);

其中，ft_name为索引名称，table_name为表名，col1, col2, ...为该索引包含的列名。

需要说明的是，FULLTEXT索引适用于英文等单词比较规范的语言，对于汉语等象形文字较多的语言效果并不如人意。

总结

在MySQL中，索引是提高数据库查询速度的有效手段，而INDEX、PRIMARY、UNIQUE、FULLTEXT则是常用的四种索引类型。它们的使用和区别如下：

1. INDEX：速度快，可以多列索引，但会增加存储空间和写入数据的时间开销。

2. PRIMARY：主键，唯一，不能为NULL，自动创建B+Tree索引。

3. UNIQUE：唯一，可以为NULL，存在多个唯一索引，不能作为外键。

4. FULLTEXT：适用于英文等单词比较规范的语言，全文索引，结果显示类似于搜索引擎。

在实际使用中，我们选择何种索引类型需要根据具体场景进行分析和选择。比如在对重要信息进行增删改时，主键索引比较安全；在需要去重或者唯一性判断时可以使用UNIQUE；在需要进行全文检索时，FULLTEXT就是首选。