一.索引简介
1.1 索引的含义和特点
- 索引是创建在表上，是对数据库一列或多列的值进行排序的结构。不同储存引擎定义的最大索引数和长度不一样，所有引擎对每个表至少支持16个索引，总索引长度至少256字节。
- 索引存储类型有两种：1.B树(BTREE) 2.哈希(HASH)
InnoDB与MyISAM支持B树索引；MEMORY两种都支持，但是默认为HASH
- 索引的优缺点：
- 优点：提高数据检索速度、查询速度、分组与排序的效率
- 缺点：创建和维护随着数据量的增多而增加；索引也要占用空间
所以可以在插入大量数据时候，先删除索引，再插入数据，节约时间，最后再插入索引。
1.2 索引的分类
- 普通索引：创建时不附加任何限制条件，可以创建在任何数据类型中。
- 唯一性索引：使用
UIQUE设置该索引的值是唯一的。 - 全文索引：用
FULLTEXT设置，只能创建在CHAR、VARCHAR或TEXT字段上。查询数字量较大的字符串，可以提高查询速度
只有MyISAM支持全文索引；默认情况下，全文索引不分大小写；索引用二进制排序后，可区分大小写
- 单列索引：在单个字段上创建索引(可以是上面任意一个索引)
- 多列索引：在多个字段上创建索引，但只有查询条件使用了这些字段的第一个字段时，索引才会被使用
- 空间索引：用
SPATIAL设置，其只能设置在空间类型的数据上
空间数据类型有：GEOMETRY、POINT、LINESTRING、PLOYGON
只有MyISAM支持空间数据类型
1.3 设计原则
- 具有唯一性数据，比如学号等，可以用唯一性索引
- 经常要
order by，group by，distinct，union，select的字段最好建立索引 - 限制索引数，索引不是越多越好
- 尽量选择数据量少的索引，会加快查询速度
- 尽量用值的前缀来进行索引。
- 删除少用和不用的索引
二.创建索引
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 表名；