这篇文章给大家分享的是有关MySQL索引有哪些作用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。
网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、小程序定制开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了象山免费建站欢迎大家使用！
一、索引简介
(1)索引的含义和特定
(2)索引的分类
(3)索引的设计原则
二、创建索引
(1)创建表的时候创建索引
(2)在已经存在的表上创建索引
(3)删除索引
一、索引简介
索引用于快速找出在某列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始读完整个表，知道找出相关的行。表越大，查询数据所花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。
(1)索引的含义和特定含义：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。用于快速找出在某个或多个列中有一特定值的行。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。
MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAL存储引擎可以支持HASH和BTREE索引。
索引的优点：
1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的查询速度。(创建索引的最主要原因)
3.在实现数据的参考完整性方面，可以加速表和表之间的连接。
4.在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。
增加索引的缺点：
1.创建索引和维护索引耗费时间，随着数据量的增加所耗费的时间也会增加。
2.索引占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3.当对表中的数据进行增加、删除和修改时，索引也要动态维护，降低了数据的维护速度。
(2)索引的分类1.普通索引和唯一索引(unique)
2.单列索引和组合索引
3.全文索引(fulltext)
4.空间索引(spatial)
(3)索引的设计原则索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要，设计索引时，应该考虑以下准则：
1.索引并非越多越好。
2.避免对经常更新的表进行过多的索引，并且索引的列尽可能少。
3.数据量小的表最好不要使用索引。
4.在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。
5.当唯一性是某种数据本身的特征时，指定唯一索引。
6.在频繁进行排序或分组(进行group by 或 order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
二、创建索引
语法格式：
create table table_name [col_name date_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)创建表的时候创建索引①创建普通索引
普通索引是 最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。
【例1】在book表中的year_publication字段上建立普通索引，SQL语句如下：
mysql> create table book -> (
-> bookid int not null,
-> bookname varchar(255) not null,
-> authors varchar(255) not null,
-> info varchar(255) null,
-> comment varchar(255) null,
-> year_publication year not null,
-> index(year_publication)
-> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book \G*************************** 1. row ***************************
Table: bookCreate Table: CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 \G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: book
partitions: NULL
type: ref
possible_keys: year_publication key: year_publication
key_len: 1
ref: const rows: 1
filtered: 100.00
Extra: NULL1 row in set, 1 warning (0.00 sec)
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> create table t1 -> (
-> id int not null
-> ,name char(30) not null,
-> unique index uniqidx(id)
-> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 \G*************************** 1. row ***************************
Table: t1Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
③创建单列索引
单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。
【例3】创建一个表t2，在表中的name字段上创建单列索引。
mysql> create table t2 -> (
-> id int not null,
-> name char(50) null,
-> index singleidx(name(20))
-> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 \G*************************** 1. row ***************************
Table: t2Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` char(50) DEFAULT NULL,
KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)
由结果可以看到，id字段上已经成功建立了一个名为SingleIdx的单列索引，索引长度为20。
④创建组合索引
组合索引就是在多个字段上创建一个索引。
【例4】创建表t3，在表中的id、name和age字段上建立组合索引，SQL语句如下:
mysql> create table t3 -> (
-> id int not null,
-> name char(30) not null,
-> age int not null,
-> info varchar(255),
-> index mulitiidx(id,name,age)
-> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 \G*************************** 1. row ***************************
Table: t3Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' \G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: mulitiidx key: mulitiidx
key_len: 124
ref: const,const rows: 1
filtered: 100.00
Extra: Using index condition1 row in set, 1 warning (0.06 sec)
组合索引起到几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵循从"最左前缀":利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。
例如，这里由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> create table t4 -> (
-> id int not null,
-> name char(30) not null,
-> age int not null,
-> info varchar(255),
-> fulltext index fulltxtidx(info)
-> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 \G*************************** 1. row ***************************
Table: t4Create Table: CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。
6.创建空间索引
空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。
【例6】创建表t5，在空间类型为geometry的字段上创建空间索引，SQL语句如下：
mysql> create table t5 -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 \G*************************** 1. row ***************************
Table: t5Create Table: CREATE TABLE `t5` (
`g` geometry NOT NULL,
SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec)
可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。
(2)在已经存在的表上创建索引在已经存在的表中创建索引，可以使用alter table语句或者create index 语句。
1.使用alter table语句创建索引
基本语法：
alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec]
【例7】在book表中的bookname字段上建立名为BkNameIdx的普通索引。
添加索引之前，show index语句查看指定表中创建的索引：
mysql> show index from book \G*************************** 1. row ***************************
Table: book
Non_unique: 1
Key_name: year_publication
Seq_in_index: 1
Column_name: year_publication
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL1 row in set (0.10 sec)
其中，各个主要参数的含义为;
可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下：
mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G*************************** 1. row ***************************
Table: book
Non_unique: 1
Key_name: year_publication
Seq_in_index: 1
Column_name: year_publication
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL*************************** 2. row ***************************
Table: book
Non_unique: 1
Key_name: bknameidx
Seq_in_index: 1
Column_name: bookname
Collation: A
Cardinality: 0
Sub_part: 30
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL2 rows in set (0.05 sec)
可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。
【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下：
mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...*************************** 3. row ***************************
Table: book
Non_unique: 1
Key_name: bknameidx
Seq_in_index: 1
Column_name: bookname
Collation: A
Cardinality: 0
Sub_part: 30
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL3 rows in set (0.01 sec)
可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。
【例9】在book表的comment字段上建立单列索引，SQL语句如下：
mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...*************************** 4. row ***************************
Table: book
Non_unique: 1
Key_name: bkcmtidx
Seq_in_index: 1
Column_name: comment
Collation: A
Cardinality: 0
Sub_part: 50
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL4 rows in set (0.01 sec)
可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。
【例10】在book表的authors和info字段上建立组合索引，SQL语句如下：
mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...4...*************************** 5. row ***************************
Table: book
Non_unique: 1
Key_name: bkauandinfoidx
Seq_in_index: 1
Column_name: authors
Collation: A
Cardinality: 0
Sub_part: 30
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL*************************** 6. row ***************************
Table: book
Non_unique: 1
Key_name: bkauandinfoidx
Seq_in_index: 2
Column_name: info
Collation: A
Cardinality: 0
Sub_part: 50
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL6 rows in set (0.06 sec)
可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。
【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下：
mysql> create table t6 -> (
-> id int not null,
-> info char(255)
-> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from t6 \G*************************** 1. row ***************************
Table: t6
Non_unique: 1
Key_name: infofiidx
Seq_in_index: 1
Column_name: info
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT Comment:
Index_comment:
Visible: YES
Expression: NULL1 row in set (0.05 sec)
可以看到，t6表中已经创建了名称为infoftidx的索引，该索引在info字段上创建，类型为fulltext，允许空值。
【例12】创建表t7，t7的空间类型字段g上创建名称为spatidx的空间索引，SQL语句如下：
mysql> create table t7(g geometry not null)ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t7 add spatial index spatidx(g);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show index from t7 \G*************************** 1. row ***************************
Table: t7
Non_unique: 1
Key_name: spatidx
Seq_in_index: 1
Column_name: g
Collation: A
Cardinality: NULL
Sub_part: 32
Packed: NULL
Null:
Index_type: SPATIAL Comment:
Index_comment:
Visible: YES
Expression: NULL1 row in set (0.01 sec)
可以看到，t7表的g字段上创建了名为spatidx的空间索引。
2.使用create index 创建索引
create index 语句可以在已经存在的表上添加索引，MySQL中create index被映射到一个alter table语句上，基本语法为：
create [unique|fulltext|spatial] index index_nameon table_name (col_name[length],...) [asc|desc]
可以看到create index语句和alter index语句的语法基本一样，只是关键字不同，使用相同的表book，假设该表中没有任何索引值，创建book表语句如下：
create table book(bookid int not null,bookname varchar(255) not null,authors varchar(255) not null,info varchar(255) null,comment varchar(255) null,year_publication year not null);
【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：
mysql> create index BkNameOdx on book(bookname);Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0
【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引，SQL语句如下：
mysql> create unique index UniqiiIdx on book(bookid);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
【例15】在book表的comment字段上建立单列索引，SQL语句如下：
mysql> create index BkcmtIdx on book(bookid);Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0
【例16】在book表的authors和info字段上建立组合索引，SQL语句如下：
mysql> create index BkAuAndInfoIdx on book (authors(20),info(50));Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0
【例17】删除表t6，重新建立表t6，在t6表中使用create index 语句，在char类型的info字段上创建全文索引。
mysql> drop table t6;Query OK, 0 rows affected (0.02 sec)mysql> create table t6 -> (
-> id int not null,
-> info char(255)
-> )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create fulltext index infoftidx on t6(info);Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0
【例18】删除表t7，重新创建表t7，在t7表中使用create index语句，在空间数据类型字段g上创建名称为spatIdx的空间索引。
mysql> drop table t7;Query OK, 0 rows affected (0.06 sec)mysql> create table t7 (g geometry not null )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create spatial index spatIdx on t7 (g);Query OK, 0 rows affected, 1 warning (0.07 sec)Records: 0 Duplicates: 0 Warnings: 1
(3)删除索引MySQL中删除索引使用alter table或者drop index 语句，两者可实现相同的功能，drop index 语句在内部被映射到一个alter table语句中。
1.使用alter table删除索引
alter table 删除索引的基本语法格式:
alter table table_name drop index index_name
【例1】删除book表中的名称为UniqidIdx的唯一索引。
mysql> show create table book \G*************************** 1. row ***************************
Table: bookCreate Table: CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
UNIQUE KEY `UniqiIdx` (`bookid`),
KEY `BkNameOdx` (`bookname`),
KEY `BkcmtIdx` (`bookid`),
KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> alter table book drop index UniqiIdx;Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row ***************************
Table: bookCreate Table: CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `BkNameOdx` (`bookname`),
KEY `BkcmtIdx` (`bookid`),
KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到，book表中已经没有名称为UniqidIdx的唯一索引，删除索引成功。
注意：添加auto_increment约束字段的唯一索引不能被删除。
2.使用drop index 语句删除索引
drop index语句删除索引的基本语法格式：
drop index inde _name on table_name
【例2】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：
mysql> drop index BkAuAndInfoIdx on book;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row ***************************
Table: bookCreate Table: CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `BkNameOdx` (`bookname`),
KEY `BkcmtIdx` (`bookid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。
注意：删除表中的列时，如果要删除的列为索引的组成部分，则该部分也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。
感谢各位的阅读！关于“MySQL索引有哪些作用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
网页标题：MySQL索引有哪些作用
标题URL：
http://cdkjz.cn/article/gocehc.html
