MySql索引类型

什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。(百度)

合理的使用数据库索引会大大提高数据库检索的性能，但并不是越多越好。

MySQL的基本索引类型

普通索引(INDEX)    主键索引(PRIMARY KEY)    唯一索引(UNIQUE)    组合索引(INDEX)    全文索引(FULLTEXT)

首先创建MySQL表结构

/** 创建一个tb_user表*/
CREATE TABLE `springbootdb`.`tb_user`(
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键标识',
  `user_name` VARCHAR(255) NOT NULL COMMENT '用户名',
  `pass_word` VARCHAR(255) NOT NULL COMMENT '密码',
  `id_card` VARCHAR(255) COMMENT '身份证',
  `sex` VARCHAR(10) DEFAULT '男'  COMMENT '性别',
  `email` VARCHAR(255) COMMENT '邮箱',
  `create_data` DATETIME COMMENT '修改时间',
  `status` VARCHAR(1) DEFAULT '1'  COMMENT '状态值(1正常,0异常,其他未知)',
  `reserve` VARCHAR(255) COMMENT '预留字段',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UNIQUE_EMAIL` (`email`),
  UNIQUE INDEX `UNIQUE_USER_NAME` (`user_name`)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin
COMMENT='这是一个用户信息表(测试)' ROW_FORMAT=DYNAMIC;

1.普通索引

（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。

因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDER BY column）中的数据列创建索引。

语法

-- 创建表后(修改表结构)创建索引
-- ALTER TABLE `表名` ADD UNIQUE `索引名` ( `列名` )
ALTER TABLE `tb_user` ADD INDEX INDEX_ID_CARD ( `id_card` );

-- 创建表时创建
INDEX INDEX_ID_CARD(name),

2.主键索引(PRIMARY KEY)

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

语法

创建表时创建。

例如上表中的主键id创建方式 (AUTO_INCREMENT 自增序列 , 主键索引PRIMARY KEY (字段))

(
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键标识',
PRIMARY KEY (`id`)
)

创建表后(修改表结构)创建

-- 定义主键(一个表只能拥有一个)
ALTER TABLE tb_user ADD CONSTRAINT PRIMARY KEY (id);

3.唯一索引(UNIQUE)   

-- 修改表结构创建
-- ALTER TABLE `tb_user` ADD UNIQUE `索引名` ( `id_card` )
ALTER TABLE `tb_user` ADD UNIQUE `INDEX_ID_CARD` ( `id_card` )

--创建表时创建
UNIQUE INDEX_ID_CARD(`name`),

4.组合索引(INDEX)   

 (指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。)

语法

-- 创建表后(修改表结构)创建
ALTER TABLE `tb_user` ADD INDEX `INDEX_TB_USER_ID_NAME` (`id`,`name`);

-- 创建表时创建
INDEX INDEX_TB_USER_ID_NAME(`id`,`name`)

5.全文索引(FULLTEXT)

全文索引跟其它索引大不相同，类似于在全文范围搜索txt文件，而不是简单的where语句的参数匹配。目前只有char、varchar，text 列上可以创建全文索引。

语法

-- 修改表结构创建
ALTER TABLE `tb_user` ADD FULLTEXT `INDEX_FULLTEXT_ID_CARD` (`id_card`);

-- 创建表时创建
FULLTEXT (id_card) ,