浅谈MySQL索引

索引含义

是存储引擎用于快速找到记录的一种数据结构，是创建在表上，对数据库表中一列或者多列的值进行排序的一种结构，它可以提高查询速度(因为它在查询数据时不用将每条信息都比较，只是查询索引列)。通俗点理解，索引就像整篇文章的目录表，你通过目录可以快速找到你想要查看的主题所在页数。（在MySQL中也叫做“键(key)”）。

索引的类型

不同的存储引擎定义了每个表的最大索引数和最大索引长度，所有的存储引擎对每个表至少支持16个索引，总索引的长度至少为256字节。

1. B型树(BTREE)索引
2. 哈希索引(HASH)索引

索引的分类

1.普通索引

在创建普通索引时，不添加任何限制性条件，可以创建在任何数据类型上。

2.唯一性索引

使用UNIQUE参数将索引设置为唯一性索引，对应的索引列的值必须是唯一的。主键就是一种特殊的唯一性索引。

3.单列索引

在表中单个字段上创建索引，即进行索引时只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引，只需要保证索引只对应一个字段就?了。

4.多列索引

为表中的多个字段建立同一个索引，但只有查询中使用了这些字段的第一个字段时，索引才会被用到。(最左匹配原则)。

5.全文索引

使用FULLTEXT参数将索引设置为全文索引，全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。目前只有MyISAM存储引擎支持全文检索?，在默认情况下，全文索引的搜索执行方式不区分大小写，但是索引的列使用二进制排序后，可以执行区分大小写的全文索引。

6.空间索引

使用SPATIAL参数将索引设置为空间索引。空间索引只能建立在空间数据的类型上(GEOMETRY、POINT、LINESTRING和POLYGON等)。目前只有MyISAM存储引擎支持空间检索，并且索引的字段不能为空。

索引的设计原则

1. 选择唯一性索引。
2. 为经常需要排序、分组和联合操作的字段建立索引。
3. 为常作为查询条件的字段建立索引。
4. 限制索引的数目—因为每个索引都需要占用一定的磁盘空间。
5. 尽量为数据量小的列建立索引–例如对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。
6. 尽量使用前缀来索引—在保证检索正确率的情况下，只检索字段的前边若干个字符，以提高检索速度。
7. 删除不再使用或者很少使用的所以。

创建索引

1.创建表的时候创建索引

//方括号表示可选参数
//unique 唯一性索引
//fulltext 全文索引
//sqatial 空间索引
create table 表名( 属性名 数据类型 [完整性约束],
		   属性名 数据类型 [完整性约束],
		   ……
		   属性名 数据类型 [完整性约束]
		   [unique|fulltext|spatial] index|key [别名] (字段名 [(长度)])
		   ）；

例：创建一个student表，在表的id字段上建立名为student_id、长度为6的唯一性索引。SQL语句如下：

create table student (id int unique,
			name varchar(10),
			unique index student_id  (id (6))
			);

在已存在的表上创建索引
1.

//方括号表示可选参数
create [unique|fulltext|spatial] index 索引名 on 表名 (字段名 [(长度)]);

例：为student表中的id和name字段建立 index_id_na的多列索引。

create index index_id_na on student(id, name);

2.用ALTER TABLE语句来创建索引。

alter table 表名 add [unique|fulltext|spatial] index 索引名 on 表名 (字段名 [(长度)]);

删除索引

drop index 索引名 on 表名;

例：删除student表上index_id_na的索引。

drop index index_id_na on student;

索引的优缺点

优点

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机的I/O变为顺序I/O。

缺点

1. 创建和维护索引都需要耗费时间，且耗费时间的大小随着数据量的增加而增加
2. 占用物理空间。故增、删、改数据时，要动态维护索引，造成了数据维护速度降低。

技巧:
向有索引的表中插入数据时，可以删除表中的索引，然后插入索引，插入完成之后，在创建索引。