索引的类型分类、区别、优缺点

导读：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

mysql的索引我们分为三大类来讲单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引。

 

一、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1：普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2：唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3：主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

 

主键索引与唯一索引的区别：

1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

3. 唯一性索引列允许空值，而主键列不允许为空值。

4. 主键索引在创建时，已经默认为非空值+ 唯一索引了。

5. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引。

6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

7. 主键可以被其他表引用为外键，而唯一索引不能。

 

二、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。

 

 

三、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

 

创建索引的语句：

1：直接创建索引：

CREATE INDEX index_name ON table(column(length)); 创建普通索引
CREATE UNIQUE INDEX indexName ON table(column(length)); 创建唯一索引
CREATE FULLTEXT INDEX index_content ON article(content); 全文索引

2：修改表结构的方式添加索引：

ALTER TABLE table_name ADD INDEX index_name ON (column(length)); 创建普通索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)); 创建唯一索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 组合索引
ALTER TABLE article ADD FULLTEXT index_content(content); 全文索引

3：创建表的时候创建索引：

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
); 普通索引

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
UNIQUE indexName (title(length))
); 唯一索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
); 主键索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
); 全文索引

4：删除索引：

DROP INDEX index_name ON table;
5：查看索引：

show index from table_name;

索引的优缺点：

1、优点：创建索引可以大大提高系统的性能。
第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

 

2、缺点：

增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这是因为，增加索引也有许多不利的一个方面:

第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么 需要的空间就会更大。

第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

哪些字段适合作为索引（不适合作为索引）：

 

一般来说，应该在这些列上创建索引，例如：

第一、在经常需要搜索的列上，可以加快搜索的速度；

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

第六、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

什么样的字段不适合创建索引:

 

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，

并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，

在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。

增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

因此，当修改性能远远大于检索性能时，不应该创建索引。
————————————————
版权声明：本文为CSDN博主「战之殇」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_38852289/article/details/80817156