MySQL索引类型

索引类型

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

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

创建表：

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：

ALTER TABLE INDEX_TEST ADD INDEX idx_normal(name);

唯一索引

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

SQL：

ALTER TABLE INDEX_TEST ADD UNIQUE idx_unique(idno);

主键索引

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

SQL：

ALTER TABLE INDEX_TEST ADD PRIMARY KEY idx_pk(id);

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

组合索引

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

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

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

  

• where a and c：索引命中a

  

• where a and b：索引命中a、b

  

• where b and c：索引未命中

  

• where a and b order by c：索引字段全部命中

  

SQL：

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：

ALTER TABLE INDEX_TEST ADD FULLTEXT idx_fulltext(content);

总结

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

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;