mysql的索引类型以及使用优缺点

1.mysql的索引类型大致分为5类

1.1.普通索引（NORMAL)
是最基本的索引，它没有任何限制,其创建方式有
(1).直接创建通过create关键字

create index `索引名` on `表`(`字段名`（长度可不指定，指定的话要不大于字段长度 ）)

(2).通过alter修改表结构

ALTER TABLE `表` ADD INDEX `索引名` ( `字段名` (长度可不指定，指定的话要不大于字段长度 ) )

(3).创建表的时候直接指定

CREATE TABLE `user3` (
  `id` int(1) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `name` (`name`(5))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

其中KEY 也可以写成INDEX
(4).删除普通索引

drop index `索引名` on `表名`

注：只能删除表中已存在的索引，否则报错。

1.2.唯一索引（UNIQUE ）

与前面的普通索引不同的就是：索引列的值必须唯一，但允许有空值，唯一索引不能作用于数字类型。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
(1).直接创建通过create关键字

create UNIQUE index `索引名` on `表`(`字段名`（长度可不指定，指定的话要不大于字段长度）)

(2).通过alter修改表结构

ALTER TABLE `表` ADD UNIQUE INDEX `索引名` ( `字段名` (长度可不指定，指定的话要不大于字段长度 ) )

(3).创建表的时候直接指定

CREATE TABLE `user5` (
  `id` int(1) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(16) DEFAULT NULL,
  `age` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

其中KEY 也可以写成INDEX
(4).删除唯一索引

drop index `索引名` on `表名`

1.3.主键索引

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

CREATE TABLE `user4` (
  `id` int(1) NOT NULL AUTO_INCREMENT COMMENT '主键',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

注:主键索引有特殊的两种情况，联合主键和复合主键，具体参考联合主键与复合主键的区别
1.4.组合索引（复合索引）
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合，创建方式有
(1).直接创建通过create关键字

create UNIQUE index `索引名` on `表`(`字段名1`（长度可不指定，指定的话要不大于字段长度）,(`字段名2`（长度可不指定，指定的话要不大于字段长度）)

(2).通过alter修改表结构

ALTER TABLE `表` ADD UNIQUE INDEX `索引名` ( `字段名` (长度可不指定，指定的话要不大于字段长度 ),(`字段名2`（长度可不指定，指定的话要不大于字段长度） )

(3).创建表的时候直接指定

CREATE TABLE `user5` (
  `id` int(1) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(16) DEFAULT NULL,
  `age` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

其中KEY 也可以写成INDEX
(4).删除组合索引

drop index `索引名` on `表名`

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

CREATE TABLE `user8` (
  `id` int(1) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100)  NULL,
  `age` text  NULL,
  PRIMARY KEY (`id`) USING BTREE,
	FULLTEXT (name,age)	
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO user8 (name,age) VALUES
   ('MySQL Tutorial','DBMS stands for DataBase ...'),
   ('How To Use MySQL Well','After you went through a ...'),
   ('Optimizing MySQL','In this tutorial we will show ...'),
   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
   ('MySQL vs. YourSQL','In the following database comparison ...'),
   ('MySQL Security','When configured properly, MySQL ...');

然后通过

SELECT
	* 
FROM
	user8 
WHERE
	MATCH ( name,age) AGAINST ( 'mysql' IN NATURAL LANGUAGE MODE );

注：上面只能整个单词匹配到，m是匹配不到mysql的,具体可参考mysql基础语法之（全文索引）

2.索引的优缺点

2.1.优点：提高了查询效率
2.2.缺点：降低更新、新增、删除表的速度，因为操作表时，不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快
总结：索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。