共计 8156 个字符，预计需要花费 21 分钟才能阅读完成。
这篇文章给大家介绍 MySQL 中索引类型的作用是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
前言：
MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。
打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
建立索引会占用磁盘空间的索引文件。
每种搜索引擎支持的索引是不同的，具体如下：
MyISAM，InnoDB 支持 btree 索引；
Memory 支持 btree 和 hash 索引。
索引的优势如下：
加快查询速度
创建唯一索引来保证数据表中数据的唯一性
实现数据的完整性，加速表和表之间的连接
减少分组和排序的时间
增加索引也有很多不利，主要表现在以下几个方面：
创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
一、索引的分类 1、唯一索引和普通索引
普通索引：是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，不允许有空值。
2、单列索引和组合索引
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引；
组合索引：指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
3、全文索引（fulltext）
全文索引类型为 FULLTEXT，在定义索引的列上支持值得全文查找，允许在这些索引列
中插入重复值和空值。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。MySQL 5.7.xx 之前只有 MyISAM 存储引擎支持全文索引。
4、空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有 4 中，分别是：
geometry、point、linstring 和 polygon。MySQL 使用 SPATIAL 关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为 NOT NULL，同样，在 MySQL 5.7.xx 之前，空间索引只能在存储引擎为 MyISAM 的表中创建。
5、创建索引的规则
创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响
insert、delete、update 等语句的性能。因为当表中的数据更改时，索引也会进行调整和更新；
数据量小得表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要长；
避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引；
在条件表达式中经常用到的不同值较多的列创建索引；
当唯一性是某种数据本身的特征时，我们创建唯一性索引；
在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建组合索引；
二、创建表的同时创建索引 1、创建普通索引
mysql create table book
- (
- bookid int,
- bookname varchar(255),
- authors varchar(255),
- info varchar(255),
- comment varchar(255),
- year_publication year,
- index(year_publication) !-- 创建 year_publication 列为索引列 --
- );
mysql show create table book\G !-- 查看索引 --
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` ( `bookid` int(11) DEFAULT NULL,
`bookname` varchar(255) DEFAULT NULL,
`authors` varchar(255) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) DEFAULT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)使用 explain 判断索引是否正在被使用，如下：
!-- 在查询命令前加“explain”关键字即可 --
mysql explain select * from book where year_publication=1999\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: book
partitions: NULL
type: ref
possible_keys: year_publication
!--possible_keys：后面表示使用的索引名称，没有定义名称会使用字段名为索引名 --
key: year_publication
key_len: 2
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)2、唯一索引
唯一索引主要原因是减少查询索引列操作的执行时间。尤其是对比比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
!-- 创建带唯一索引的表 --
mysql create table t1(
- id int not null,
- name char(30),
- unique index Uniqidx(id)
- );
!-- 查看 --
mysql show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL,
`name` char(30) DEFAULT NULL,
UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf83、单列索引
单列索引：是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。
!-- 创建单列索引 --
mysql create table t2
- (
- id int not null,
- name char(50) null,
- index singleidx(name)
- );
mysql show create table t2\G !-- 查看 --
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL,
`name` char(50) DEFAULT NULL,
KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)4、组合索引
组合索引：是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行。
!-- 创建组合索引 --
mysql create table t3
- (
- id int not null,
- name char(30) not null,
- age int not null,
- info varchar(255),
- index multiidx(id,name,age)
- );
!-- 查看组合索引 --
mysql show create table t3\G
*************************** 1. row ***************************
Table: t3
Create 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 `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)注：组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5、全文索引
全文索引：FULLTEXT，可以用于全文搜索，支持为 CHAR\VARCHAR 和 TEXT 列。索引总是对整个列进行，不支持局部索引，适合大型数据的表创建。
!-- 创建带有全文索引的表 --
mysql create table test8 ( id int not null, title varchar(255), body varchar(255),fulltext index(body) ) ENGINE=MyISAM; !--MySQL 版本在 5.7 以后的话，可以不用指定引擎为 myisam--
!-- 查看 --
mysql show create table t4\G
*************************** 1. row ***************************
Table: t4
Create 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 `fullidx` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
!-- 全文索引的使用方法和其他索引不一样，具体使用方法如下（先插入测试数据）：--
mysql insert into test8 values(2, 数据库 , WORDS OF WISDOM: Like they say in Asia, nobody should use a fork. Tradition evven dictates to “chop” all your forks and “stick” to the origiinal.
Query OK, 1 row affected (0.00 sec)
mysql insert into test8 values(1, 数据库 , In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query
Query OK, 1 row affected (0.00 sec)
!-- 测试是否用到了索引，并且可以搜索到数据 --
mysql explain select * from test8 where match(body) against(MySQL)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test8
partitions: NULL
type: fulltext
possible_keys: body
key: body
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)查看到的数据如下：
注：在上面创建全文索引的方式中，可以实现英文的全文索引，每个单词以空格分隔来匹配，若想实现中文的全文索引，那么需要在创建表的同时，加上“with parser ngram”来带上中文解析器。
中文的全文索引创建如下：
mysql create table test10
- (
- id int not null,
- title varchar(50),
- info text,
- fulltext index(title,info) with parser ngram);
!-- 创建中文索引时，指定“with parser ngram”，以便支持中文分析器，否则无法使用索引搜索 --
!-- 插入测试数据 --
mysql insert into test10 values(1, 测试 , 主办方介绍，本次大展将更加突出互动性强，大众参与度高的特点，让观展者不仅享受视 觉盛宴，更能体验到冰雪运动的快乐。Query OK, 1 row affected (0.00 sec)
mysql insert into test10 values(2, 测试 2 , 中国摄影家协会分 党组书记、驻会副主席郑更生，中国摄影家协会副主席线云强，中国 摄影家协会顾问王玉文
Query OK, 1 row affected (0.00 sec)
!-- 测试是否可以使用到全文索引 --
mysql explain select * from test10 where match(title,info) against(中国)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test10
partitions: NULL
type: fulltext
possible_keys: title !-- 表示使用到了 --
key: title
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)上述查到的结果如下：
6、空间索引
空间索引：必须在 MyISAM 类型的表中创建，且空间类型的字段必须为非空。
!-- 创建空间索引 --
mysql create table t5
- (
- g geometry not null,
- spatial index spaidx(g)
- )engine=myisam;
!-- 查看 --
mysql show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`g` geometry NOT NULL,
SPATIAL KEY `spaidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)三、在已经存在的表上创建索引 1、添加唯一索引
mysql alter table book add unique index uniqidx(bookid); !--uniqidx 为索引名，bookid 为列名 --2、添加单列索引
mysql alter table book add index bkidex(comment(50));3、添加全文索引
mysql alter table t6 add fulltext index infofulidx(info);4、添加组合索引
mysql alter table book add index abc(authors(20),info);5、添加空间索引
mysql alter table t5 add spatial index spatidx(g);四、在已存在的表上创建索引
!-- 创建一个表用于测试 --
mysql CREATE TABLE book1 ( 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 );
Query OK, 0 rows affected (0.02 sec)1、创建普通索引
mysql create index bknameidex on book1(bookname); !--book1 为表明，bookname 为列名 --2、创建单列索引
mysql create index bkcmtidex on book1(comment(50));3、创建组合索引
mysql create index bkauthandinfoidex on book1(authors,info);4、创建全文索引
mysql create fulltext index fullidx on book(info);5、创建唯一性索引
mysql create unique index uniqidx on book1(bookid);6、创建空间索引
mysql create table t7 (g geometry not null);
mysql create spatial index spaidx on t7(g);五、删除索引
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。
1、查看某些表上有哪些索引
mysql show index from book\G2、用 alter table 删除索引
mysql alter table book drop index uniqidx;注：添加 AUTO_INCREMENT 的约束字段的唯一索引不能删除
3、用 drop index 删除
mysql drop index spaidx on t7;关于 MySQL 中索引类型的作用是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
