MySQL索引简介

索引可以理解为表的目录，它实质上是一种满足特定查找算法的数据结构，它是在数据库的存储引擎层实现的，而不是在服务层，所以不同的存储引擎的索引也不一定相同。

索引用于优化查询，快速找出某一列中有特定值的行，如果没有索引，会对表中数据进行顺序查询，表越大，花费时间越多。但如果要访问表中大部分的行，则顺序读取要快得多，因为避免了对索引的磁盘搜索。

索引的好处：

1、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

2、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组group by和排序order by子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

索引的不利方面：

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2、索引需要占物理空间，建立的索引越多，那么需要的空间就会越大。

3、当对表中的数据进行增加、删除和修改的时候，索引也要随之改变，进行动态的维护，这样就降低了数据的维护速度。

所以在创建索引的时候，要考虑清楚在哪个列上创建，创建哪种索引

创建索引的一般原则：

1、在经常需要查询的列上创建索引；

2、经常需要范围查询的列，因为索引已经排序，这样利用其加快查询速度

3、出现在where子句中的列，而不是出现在select后的列

4、使用唯一索引，索引列的基数越大，索引的效果越好。例如出生年月列比性别列更适合做索引，性别列只有两种结果，不管搜索哪一行，都会得到大约一半的行，而出生年月列具有多种不同的值，很容易区分出各行。

3、使用短索引，在对字符串字段使用前缀索引时，只要所有行的前n个字符是唯一的，也就是能够将各行区分开来的，就对前n个字符进行索引。这样不但能节省索引空间，还能使得查询更快

4、不要过度索引，每一个索引都要占用额外的磁盘空间，并降低写操作的性能，并且在修改表时，索引需要进行更新或重构，因此索引越多，所花的时间就越长，反而降低了查询效率

5、对于InnoDB存储引擎的表，它是按照一定的顺序来保存的，首先是主键，若没有主键，按唯一索引，如果也没有唯一索引，按照该表自动生成的一个内部列的顺序进行保存。所以，尽量自己指定主键，并且要尽可能选用较短的数据类型的列进行索引，因为InnoDB的普通索引都会保存主键的键值，选用较短的数据类型，可以有效的减少索引的磁盘占用空间，提高索引的缓存效果

MySQL目前支持4种索引：

1、B-tree索引（Blanced tree 平衡树索引），大部分存储引擎都支持，并且应用广泛

当使用>,<,>=,<=,!=,between这种范围查询，或者like ''等操作符时，一般选择Btree

2、HASH索引，至于MEMORY支持

3、R-tree（空间索引），是MyISAM的一个特殊索引

适用于=，>=，<=这样的等式比较，也是能使用整个关键词来搜索

4、Full-text(全文索引)，MyISAM支持

创建索引：

create index|unique|fulltext [index_name] using [index_type]  on [table_name](col_name[(length)]  [ASC|DESC])

length用于前缀索引时指定索引几个字节

index_type指索引类型

也可以在create table和alter table语句中创建索引

create table emp (

    ->id int not null auto_increment primary  key,

    ->name varchar(11) not null,

    ->salary int not null,

    ->index index_name(name)

);

alter table emp add index index_name(name);

索引的分类：

1、普通索引  alter table emp add index index_name(name);

2、唯一索引  alter table emp add unique index_name(id);

3、多列索引：alter table emp add index index_name(id,name,info(10));

4、全文索引：alter table emp add fulltext index index_name(name);

5、空间索引：alter table emp add spatial index index_name(name);

删除索引：

drop index index_name on table_name;

查看索引：

show index from ts

---

扩展：

在创建索引时，可以指定在更新或删除表父表时，对子表的相应操作，指定参数包括：restrict、no action、cascade、set null（父表、子表是外键（foreign key）中的概念，父表即含被参考字段的表，子表就是参考了另外一张表中字段的表）

---restrict和no action相同，表示在父表中，当遇到与子表有关联的字段时，不允许更新或删除

---cascade表示父表在更新或操作时，字表对应字段也进行相应的更新和删除

---set null表示父表在更新或删除时，字表的对应字段设置为null

例：create table emp (id int not null primary key auto_increment,

                      name varchar(11) not null,

                      salary  int not null

                      foreign  key (id) references emp2 (id) on delete restrict on update casacde) engine-innodb default charset=utf8;

on delete restrict on update casacde表示当父表进行删除操作时，如果子表中有对应（参照）的字段时，该删除操作不能进行，当父表有更新操作时，如果子表对应字段也进行相应的更新

 

如有错误，敬请指正~~