这篇文章给大家分享的是有关MySQL索引有哪些作用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

一、索引简介
(1)索引的含义和特定
(2)索引的分类
(3)索引的设计原则

二、创建索引
(1)创建表的时候创建索引
(2)在已经存在的表上创建索引
(3)删除索引

一、索引简介

索引用于快速找出在某列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始读完整个表，知道找出相关的行。表越大，查询数据所花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。

含义：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。用于快速找出在某个或多个列中有一特定值的行。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAL存储引擎可以支持HASH和BTREE索引。

索引的优点：
1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的查询速度。(创建索引的最主要原因)
3.在实现数据的参考完整性方面，可以加速表和表之间的连接。
4.在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

增加索引的缺点：
1.创建索引和维护索引耗费时间，随着数据量的增加所耗费的时间也会增加。
2.索引占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3.当对表中的数据进行增加、删除和修改时，索引也要动态维护，降低了数据的维护速度。

1.普通索引和唯一索引(unique)

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

2.单列索引和组合索引

单列所以你即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。

使用组合索引时遵循最左前缀集合。

3.全文索引(fulltext)

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在char、varchar或text类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

4.空间索引(spatial)

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别为geometry，point，linestring和polygon。MySQL使用spatial关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为not null，空间索引只能在存储引擎为MySQL的表中创建。

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要，设计索引时，应该考虑以下准则：

1.索引并非越多越好。

2.避免对经常更新的表进行过多的索引，并且索引的列尽可能少。

3.数据量小的表最好不要使用索引。

4.在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。

5.当唯一性是某种数据本身的特征时，指定唯一索引。

6.在频繁进行排序或分组(进行group by 或 order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

二、创建索引

语法格式：

createtabletable_name[col_namedate_type][unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

unique，fulltext和spatial为可选参数，分别表示唯一索引，全文索引和空间索引。

index与key为同义词，两者作用相同，用来指定创建索引。

col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择。

index_name指定索引的名称，为可选参数，若不指定，MySQL默认col_name为索引值。

length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。

asc或desc指定升序或者降序的索引值存储。

①创建普通索引

普通索引是 最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

【例1】在book表中的year_publication字段上建立普通索引，SQL语句如下：

mysql>createtablebook->(->bookidintnotnull,->booknamevarchar(255)notnull,->authorsvarchar(255)notnull,->infovarchar(255)null,->commentvarchar(255)null,->year_publicationyearnotnull,->index(year_publication)->);QueryOK,0rowsaffected(0.21sec)mysql>showcreatetablebook\G***************************1.row***************************Table:bookCreateTable:CREATETABLE`book`(`bookid`int(11)NOTNULL,`bookname`varchar(255)NOTNULL,`authors`varchar(255)NOTNULL,`info`varchar(255)DEFAULTNULL,`comment`varchar(255)DEFAULTNULL,`year_publication`year(4)NOTNULL,KEY`year_publication`(`year_publication`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.06sec)mysql>explainselect*frombookwhereyear_publication=1990\G***************************1.row***************************id:1select_type:SIMPLEtable:bookpartitions:NULLtype:refpossible_keys:year_publicationkey:year_publicationkey_len:1ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)

explan语句输出结果的各行解释如下：

select_type行指定所使用的select查询类型，这里值为simple，表示简单的select，不使用union或子查询。其他可能的取值有primary、union、subquery等。

table行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。

type行指定了本数据库表与其他数据库表之间的关联关系，可能的取值有system、const、eq_ref、ref、range、index和all。

possible_keys行.给出了MySQL在搜索数据记录时可选用的各个索引。

key行是MySQL实际选用的索引。

key_len行给出索引按字节计算的长度，key_len数值越小，表示越快。

ref行给出了关联关系中另一个数据表里数据列的名字。

rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。

extra行提供了与关联操作有关的信息。

可以看到，possible_key和key的值都为year_publication，查询时使用了索引。

②创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

【例2】创建一个表t1，在表中的id字段上使用unique关键字创建唯一索引。

mysql>createtablet1->(->idintnotnull->,namechar(30)notnull,->uniqueindexuniqidx(id)->);QueryOK,0rowsaffected(0.27sec)mysql>showcreatetablet1\G***************************1.row***************************Table:t1CreateTable:CREATETABLE`t1`(`id`int(11)NOTNULL,`name`char(30)NOTNULL,UNIQUEKEY`uniqidx`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.06sec)

③创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。

【例3】创建一个表t2，在表中的name字段上创建单列索引。

mysql>createtablet2->(->idintnotnull,->namechar(50)null,->indexsingleidx(name(20))->);QueryOK,0rowsaffected(0.06sec)mysql>showcreatetablet2\G***************************1.row***************************Table:t2CreateTable:CREATETABLE`t2`(`id`int(11)NOTNULL,`name`char(50)DEFAULTNULL,KEY`singleidx`(`name`(20)))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.01sec)

由结果可以看到，id字段上已经成功建立了一个名为SingleIdx的单列索引，索引长度为20。

④创建组合索引
组合索引就是在多个字段上创建一个索引。

【例4】创建表t3，在表中的id、name和age字段上建立组合索引，SQL语句如下:

mysql>createtablet3->(->idintnotnull,->namechar(30)notnull,->ageintnotnull,->infovarchar(255),->indexmulitiidx(id,name,age)->);QueryOK,0rowsaffected(0.07sec)mysql>showcreatetablet3\G***************************1.row***************************Table:t3CreateTable:CREATETABLE`t3`(`id`int(11)NOTNULL,`name`char(30)NOTNULL,`age`int(11)NOTNULL,`info`varchar(255)DEFAULTNULL,KEY`mulitiidx`(`id`,`name`,`age`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.06sec)mysql>explainselect*fromt3whereid=1andname='joe'\G***************************1.row***************************id:1select_type:SIMPLEtable:t3partitions:NULLtype:refpossible_keys:mulitiidxkey:mulitiidxkey_len:124ref:const,constrows:1filtered:100.00Extra:Usingindexcondition1rowinset,1warning(0.06sec)

组合索引起到几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵循从"最左前缀":利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。

例如，这里由id，name，age三个字段构成的索引，索引行中按id/nam/age的顺序粗放，索引可以搜索字段组合：(id，name，age)、(id、name)或者id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如(age)或(name，age)组合则不能使用索引查询。查询id和name字段时，使用了multiidx的索引，如果查询(name，age)组合或者单独查询name和age字段，索引为null。

⑤创建全文索引

fulltext全文索引可以用于全文搜索。只有MyISAM存储引擎支持fulltext索引，并且只为char、varchar和text列创建索引。索引总是对整列进行，不支持局部(前缀)索引。

【例5】创建表t4，在表中的info字段上建立全文索引，SQL语句如下：

mysql>createtablet4->(->idintnotnull,->namechar(30)notnull,->ageintnotnull,->infovarchar(255),->fulltextindexfulltxtidx(info)->)engine=MyISAM;QueryOK,0rowsaffected(0.08sec)mysql>showcreatetablet4\G***************************1.row***************************Table:t4CreateTable:CREATETABLE`t4`(`id`int(11)NOTNULL,`name`char(30)NOTNULL,`age`int(11)NOTNULL,`info`varchar(255)DEFAULTNULL,FULLTEXTKEY`fulltxtidx`(`info`))ENGINE=MyISAMDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.06sec)

全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。

6.创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。

【例6】创建表t5，在空间类型为geometry的字段上创建空间索引，SQL语句如下：

mysql>createtablet5->(ggeometrynotnull,spatialindexspatidx(g))ENGINE=MyISAM;QueryOK,0rowsaffected,1warning(0.07sec)mysql>showcreatetablet5\G***************************1.row***************************Table:t5CreateTable:CREATETABLE`t5`(`g`geometryNOTNULL,SPATIALKEY`spatidx`(`g`))ENGINE=MyISAMDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.05sec)

可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。

在已经存在的表中创建索引，可以使用alter table语句或者create index 语句。

1.使用alter table语句创建索引

基本语法：

altertabletable_nameadd[unique|fulltext|spatial][index|key][index_name](col_name[length],...)[asc|dec]

【例7】在book表中的bookname字段上建立名为BkNameIdx的普通索引。

添加索引之前，show index语句查看指定表中创建的索引：

mysql>showindexfrombook\G***************************1.row***************************Table:bookNon_unique:1Key_name:year_publicationSeq_in_index:1Column_name:year_publicationCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL1rowinset(0.10sec)

其中，各个主要参数的含义为;

table表示创建索引的表。

Non_unique表示索引非唯一，1表示非唯一，0表示唯一。

Key_name表示索引的名称。

Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。

Column_name表示定义索引的列字段。

Sub_part表示索引的长度。

Null表示该字段是否能为空值。

Index_type表示索引类型。

可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下：

mysql>altertablebookaddindexbknameidx(bookname(30));QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0mysql>showindexfrombook\G***************************1.row***************************Table:bookNon_unique:1Key_name:year_publicationSeq_in_index:1Column_name:year_publicationCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL***************************2.row***************************Table:bookNon_unique:1Key_name:bknameidxSeq_in_index:1Column_name:booknameCollation:ACardinality:0Sub_part:30Packed:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL2rowsinset(0.05sec)

可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。

【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下：

mysql>altertablebookadduniqueindexuniqididx(bookid);QueryOK,0rowsaffected(0.17sec)Records:0Duplicates:0Warnings:0mysql>showindexfrombook\G1...2...***************************3.row***************************Table:bookNon_unique:1Key_name:bknameidxSeq_in_index:1Column_name:booknameCollation:ACardinality:0Sub_part:30Packed:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL3rowsinset(0.01sec)

可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。

【例9】在book表的comment字段上建立单列索引，SQL语句如下：

mysql>altertablebookaddindexbkcmtidx(comment(50));QueryOK,0rowsaffected(0.11sec)Records:0Duplicates:0Warnings:0mysql>showindexfrombook\G1...2...3...***************************4.row***************************Table:bookNon_unique:1Key_name:bkcmtidxSeq_in_index:1Column_name:commentCollation:ACardinality:0Sub_part:50Packed:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:NULL4rowsinset(0.01sec)

可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。

【例10】在book表的authors和info字段上建立组合索引，SQL语句如下：

mysql>altertablebookaddindexbkauandinfoidx(authors(30),info(50));QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0mysql>showindexfrombook\G1...2...3...4...***************************5.row***************************Table:bookNon_unique:1Key_name:bkauandinfoidxSeq_in_index:1Column_name:authorsCollation:ACardinality:0Sub_part:30Packed:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL***************************6.row***************************Table:bookNon_unique:1Key_name:bkauandinfoidxSeq_in_index:2Column_name:infoCollation:ACardinality:0Sub_part:50Packed:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:NULL6rowsinset(0.06sec)

可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。

【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下：

mysql>createtablet6->(->idintnotnull,->infochar(255)->)ENGINE=MyISAM;QueryOK,0rowsaffected(0.07sec)mysql>altertablet6addfulltextindexinfofiidx(info);QueryOK,0rowsaffected(0.13sec)Records:0Duplicates:0Warnings:0mysql>showindexfromt6\G***************************1.row***************************Table:t6Non_unique:1Key_name:infofiidxSeq_in_index:1Column_name:infoCollation:NULLCardinality:NULLSub_part:NULLPacked:NULLNull:YESIndex_type:FULLTEXTComment:Index_comment:Visible:YESExpression:NULL1rowinset(0.05sec)

可以看到，t6表中已经创建了名称为infoftidx的索引，该索引在info字段上创建，类型为fulltext，允许空值。

【例12】创建表t7，t7的空间类型字段g上创建名称为spatidx的空间索引，SQL语句如下：

mysql>createtablet7(ggeometrynotnull)ENGINE=MyISAM;QueryOK,0rowsaffected(0.07sec)mysql>altertablet7addspatialindexspatidx(g);QueryOK,0rowsaffected,1warning(0.06sec)Records:0Duplicates:0Warnings:1mysql>showindexfromt7\G***************************1.row***************************Table:t7Non_unique:1Key_name:spatidxSeq_in_index:1Column_name:gCollation:ACardinality:NULLSub_part:32Packed:NULLNull:Index_type:SPATIALComment:Index_comment:Visible:YESExpression:NULL1rowinset(0.01sec)

可以看到，t7表的g字段上创建了名为spatidx的空间索引。

2.使用create index 创建索引

create index 语句可以在已经存在的表上添加索引，MySQL中create index被映射到一个alter table语句上，基本语法为：

create[unique|fulltext|spatial]indexindex_nameontable_name(col_name[length],...)[asc|desc]

可以看到create index语句和alter index语句的语法基本一样，只是关键字不同，使用相同的表book，假设该表中没有任何索引值，创建book表语句如下：

createtablebook(bookidintnotnull,booknamevarchar(255)notnull,authorsvarchar(255)notnull,infovarchar(255)null,commentvarchar(255)null,year_publicationyearnotnull);

【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

mysql>createindexBkNameOdxonbook(bookname);QueryOK,0rowsaffected(0.10sec)Records:0Duplicates:0Warnings:0

【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引，SQL语句如下：

mysql>createuniqueindexUniqiiIdxonbook(bookid);QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0

【例15】在book表的comment字段上建立单列索引，SQL语句如下：

mysql>createindexBkcmtIdxonbook(bookid);QueryOK,0rowsaffected(0.08sec)Records:0Duplicates:0Warnings:0

【例16】在book表的authors和info字段上建立组合索引，SQL语句如下：

mysql>createindexBkAuAndInfoIdxonbook(authors(20),info(50));QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0

【例17】删除表t6，重新建立表t6，在t6表中使用create index 语句，在char类型的info字段上创建全文索引。

mysql>droptablet6;QueryOK,0rowsaffected(0.02sec)mysql>createtablet6->(->idintnotnull,->infochar(255)->)ENGINE=MyISAM;QueryOK,0rowsaffected(0.06sec)mysql>createfulltextindexinfoftidxont6(info);QueryOK,0rowsaffected(0.07sec)Records:0Duplicates:0Warnings:0

【例18】删除表t7，重新创建表t7，在t7表中使用create index语句，在空间数据类型字段g上创建名称为spatIdx的空间索引。

mysql>droptablet7;QueryOK,0rowsaffected(0.06sec)mysql>createtablet7(ggeometrynotnull)ENGINE=MyISAM;QueryOK,0rowsaffected(0.06sec)mysql>createspatialindexspatIdxont7(g);QueryOK,0rowsaffected,1warning(0.07sec)Records:0Duplicates:0Warnings:1(3)删除索引

MySQL中删除索引使用alter table或者drop index 语句，两者可实现相同的功能，drop index 语句在内部被映射到一个alter table语句中。

1.使用alter table删除索引

alter table 删除索引的基本语法格式:

altertabletable_namedropindexindex_name

【例1】删除book表中的名称为UniqidIdx的唯一索引。

mysql>showcreatetablebook\G***************************1.row***************************Table:bookCreateTable:CREATETABLE`book`(`bookid`int(11)NOTNULL,`bookname`varchar(255)NOTNULL,`authors`varchar(255)NOTNULL,`info`varchar(255)DEFAULTNULL,`comment`varchar(255)DEFAULTNULL,`year_publication`year(4)NOTNULL,UNIQUEKEY`UniqiIdx`(`bookid`),KEY`BkNameOdx`(`bookname`),KEY`BkcmtIdx`(`bookid`),KEY`BkAuAndInfoIdx`(`authors`(20),`info`(50)))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)mysql>altertablebookdropindexUniqiIdx;QueryOK,0rowsaffected(0.19sec)Records:0Duplicates:0Warnings:0mysql>showcreatetablebook\G***************************1.row***************************Table:bookCreateTable:CREATETABLE`book`(`bookid`int(11)NOTNULL,`bookname`varchar(255)NOTNULL,`authors`varchar(255)NOTNULL,`info`varchar(255)DEFAULTNULL,`comment`varchar(255)DEFAULTNULL,`year_publication`year(4)NOTNULL,KEY`BkNameOdx`(`bookname`),KEY`BkcmtIdx`(`bookid`),KEY`BkAuAndInfoIdx`(`authors`(20),`info`(50)))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)

可以看到，book表中已经没有名称为UniqidIdx的唯一索引，删除索引成功。

注意：添加auto_increment约束字段的唯一索引不能被删除。

2.使用drop index 语句删除索引

drop index语句删除索引的基本语法格式：

dropindexinde_nameontable_name

【例2】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：

mysql>dropindexBkAuAndInfoIdxonbook;QueryOK,0rowsaffected(0.08sec)Records:0Duplicates:0Warnings:0mysql>showcreatetablebook\G***************************1.row***************************Table:bookCreateTable:CREATETABLE`book`(`bookid`int(11)NOTNULL,`bookname`varchar(255)NOTNULL,`authors`varchar(255)NOTNULL,`info`varchar(255)DEFAULTNULL,`comment`varchar(255)DEFAULTNULL,`year_publication`year(4)NOTNULL,KEY`BkNameOdx`(`bookname`),KEY`BkcmtIdx`(`bookid`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)

可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。

注意：删除表中的列时，如果要删除的列为索引的组成部分，则该部分也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。

感谢各位的阅读！关于“MySQL索引有哪些作用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！