mysql索引操作篇

索引的定义是存储引擎（存储引擎的相关概念会在下一篇文章中进行具体的说明介绍）用于快速找到记录的一种数据结构。索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。

简单来讲索引的作用就是目录。

索引存在的意义是：
减少查询需要扫描的数据量(加快了查询速度)
减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)
将服务器的随机IO变为顺序IO(加快查询速度).

虽然有这么多的优点，但是还是会有不足的地方：
索引占用磁盘或者内存空间
减慢了插入更新操作的速度

索引的使用场景：
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

最好不要创建索引的情况：
表记录太少
经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
Where条件里用不到的字段不创建索引
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

一. MySQL索引的分类

一. MySQL索引的分类

索引分类：主键索引、唯一索引、普通索引、全文索引、组合索引 **1.主键索引** 是一种特殊的唯一索引，**不允许有空值**。一般是在建表的时候指定了主键，数据库会自动创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

主键索引的几种创建方法
（1）在建表时创建

create table tablename（
id int,
name char,
primary key (id)
）;

（2）单独建立主键索引

alter table tablename add primary key tablename(name);

上述两种时主键索引的创建方法，如果想要对主键索引进行删除操作

alter table tablename drop primary key；

如果想要对主键索引进行修改，先删除原有的索引，在进行建立操作。

2.唯一索引
用来建立唯一索引的列必须是保证唯一的，但是列中的内容允许存在空值。主键会自动进行唯一索引的建立。

唯一索引的创建方法
（1）在建表时创建

create table tablename(
id int,
name char,
unique(id)
);

（2）使用create

create unique index indexname on tablename(name);

（3）使用alter

alter table tablename add unique index index_name(name);

3.普通索引
最普通的索引，无任何的限制

普通索引的创建方法
（1）在建表时创建

create table tablename(
id int,
name char,
index indexname(id)
);

（2）使用create

create index indexname on tablename(name);

（3）使用alter

alter table tablename add index index_name(name);

4.全文索引
全文索引用于对全文的内容进行搜索，只有InnoDB和 MyISAM存储引擎支持 全文索引和仅适用于 CHAR， VARCHAR和 TEXT列。

全文索引的创建方法
（1）在建表时创建

create table tablename(
id int,
name char,
fulltext key indexname(name)
);

（2）使用alter

alter table tablename add fulltext index index_name(name);

5.组合索引
由多个列组合形成的索引，其中不允许有空值。在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)当表的行数远大于索引列的数目时可以使用组合索引。
在创建组合索引的时候，**遵循“最左前缀”**的原则将最常用的那一列放在最左边。在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取前几个字符作为索引。

组合索引的创建方法
（1）在建表时创建

create table tablename(
id int,
name char,
index indexname(id,name)
);

（2）使用create

create index indexname on tablename(id,name);

（3）使用alter

alter table tablename add index index_name(id,name);