mysql的分类有哪些_MySQL索引类型分类有哪些

MySQL索引类型分类有哪些

发布时间：2020-06-04 15:10:27

来源：51CTO

阅读：184

作者：三月

下面讲讲关于MySQL索引类型分类有哪些，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL索引类型分类有哪些这篇文章你一定会有所受益。

一、索引的分类

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)        

-> );

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判断索引是否正在被使用，如下：

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

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=utf8

3、单列索引

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

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> 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);

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);  

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);    

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\G

2、用alter table删除索引mysql> alter table book drop index uniqidx;

注：添加AUTO_INCREMENT 的约束字段的唯一索引不能删除

3、用drop index删除

mysql> drop index spaidx on t7;

对于以上MySQL索引类型分类有哪些相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。