(4)索引

一.索引简介

1.1 索引的含义和特点

1. 索引是创建在表上，是对数据库一列或多列的值进行排序的结构。不同储存引擎定义的最大索引数和长度不一样，所有引擎对每个表至少支持16个索引，总索引长度至少256字节。
2. 索引存储类型有两种：1.B树(BTREE) 2.哈希(HASH)

InnoDB与MyISAM支持B树索引；MEMORY两种都支持，但是默认为HASH

3. 索引的优缺点：

• 优点：提高数据检索速度、查询速度、分组与排序的效率
• 缺点：创建和维护随着数据量的增多而增加；索引也要占用空间

所以可以在插入大量数据时候，先删除索引，再插入数据，节约时间，最后再插入索引。

1.2 索引的分类

1. 普通索引：创建时不附加任何限制条件，可以创建在任何数据类型中。
2. 唯一性索引：使用UIQUE设置该索引的值是唯一的。
3. 全文索引：用FULLTEXT设置，只能创建在CHAR、VARCHAR或TEXT字段上。查询数字量较大的字符串，可以提高查询速度

只有MyISAM支持全文索引；默认情况下，全文索引不分大小写；索引用二进制排序后，可区分大小写

4. 单列索引：在单个字段上创建索引(可以是上面任意一个索引)
5. 多列索引：在多个字段上创建索引，但只有查询条件使用了这些字段的第一个字段时，索引才会被使用
6. 空间索引：用SPATIAL设置，其只能设置在空间类型的数据上

空间数据类型有：GEOMETRY、POINT、LINESTRING、PLOYGON
只有MyISAM支持空间数据类型

1.3 设计原则

1. 具有唯一性数据，比如学号等，可以用唯一性索引
2. 经常要order by，group by，distinct，union，select的字段最好建立索引
3. 限制索引数，索引不是越多越好
4. 尽量选择数据量少的索引，会加快查询速度
5. 尽量用值的前缀来进行索引。
6. 删除少用和不用的索引

二.创建索引

2.1 创建表的时候直接创建索引

create table 表名(
属性名 数据类型[完整的约束条件],
……
属性名 数据类型[完整的约束条件],
属性名 数据类型
[ unique|fulltext|spatial ] index|key [别名] (属性1 [(长度)] [asc|desc])
);

• index与key作用一样，指定索引，只要选择其一就够了
• [别名]：给创建的索引取的新名称
• 属性1：指定索引对应的字段的名称
• [(长度)]：表示索引长度，必须字符串类型使用
• asc：升序排列
• desc：降序排列

2.2 在已存在的表上创建索引

create  [unique|fulltext|spatial]  index  索引名  on  表名 (属性1 [(长度)]  [asc|desc]);

2.3 使用ALTER TABLE语句创建索引

alter table 表名 add [unique|fulltext|spatial]  index  索引名(属性1 [(长度)]  [asc|desc]);

三.删除索引

drop index 索引名 on 表名；