MySQL索引类型

索引类型

MySQL中我们常用的索引类型有五种:

  1. 普通索引
  2. 唯一索引
  3. 主键索引
  4. 组合索引
  5. 全文索引

创建表:

1
2
3
4
5
6
7
8
CREATE TABLE `INDEX_TEST` (
`id` int(11) DEFAULT NULL,
`name` varchar(11) DEFAULT NULL,
`idno` varchar(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(11) DEFAULT NULL,
`content` varchar(500) DEFAULT NULL
) ENGINE=InnoDB;

普通索引

最基本的一种索引,没有任何限制,可以为null,可以重复,普通索引的目的就是为了加快对数据的访问速度,为那些常被用来作查询条件的字段创建一个索引

SQL:

1
ALTER TABLE INDEX_TEST ADD INDEX idx_normal(name);

唯一索引

与普通索引类似,但是不同点在于被索引的字段的值不能重复,但是可以为null,比如身份证、员工编号等必须唯一的信息,被设置了唯一索引的字段在整张表中都不能重复

SQL:

1
ALTER TABLE INDEX_TEST ADD UNIQUE idx_unique(idno);

主键索引

与唯一索引类似,但是不同点是字段值不能为null,一般一张表只能有一个主键。实际上每张表都要有一个主键字段,通常情况下我们都是在创建表的时候手动添加,如果一张InnoDB引擎的表未明确主键字段,那么InnoDB引擎会自动为表创建一个隐式的自增主键,所以最优的情况下,我们最好手动创建一个自增主键。

SQL:

1
ALTER TABLE INDEX_TEST ADD PRIMARY KEY idx_pk(id);

创建索引的时候,索引名称可以不写。

组合索引

组合索引是创建一个包含表中多个字段的索引,组合索引的使用遵循"断桥原则",也叫"最左前缀原则",我们在使用组合索引的时候,会从第一个往后匹配,使用顺序影响不大,因为MySQL的优化器会进行再次优化,如果中间有断桥(漏掉了中间一个或多个字段),则断桥左侧的字段才会被用到索引。

比如索引字段(a, b, c),有以下情况:

  • where a and c and b:索引字段全部命中

    image-20200409172431883
  • where a and c:索引命中a

    image-20200409172638836
  • where a and b:索引命中a、b

    image-20200409172717610
  • where b and c:索引未命中

    image-20200409172802182
  • where a and b order by c:索引字段全部命中

    image-20200409172431883

SQL:

1
ALTER TABLE INDEX_TEST ADD INDEX idx_cb(age, sex);

全文索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多,因为建表的时候表中没有数据,而已经有了数据再创建的话,要将已存在的数据都先fulltext一下,所以慢。

SQL:

1
ALTER TABLE INDEX_TEST ADD FULLTEXT idx_fulltext(content);

总结

最终我们的表创建语句变成了:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `index_test` (
`id` int(11) NOT NULL,
`name` varchar(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(11) DEFAULT NULL,
`content` varchar(500) DEFAULT NULL,
`idno` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),# 主键索引
UNIQUE KEY `idx_unique` (`idno`),# 唯一索引
KEY `normal_index` (`name`),# 普通索引
KEY `idx_cb` (`age`,`sex`),# 组合索引
FULLTEXT KEY `idx_fulltext` (`content`)# 全文索引
) ENGINE=InnoDB;