MySQL索引学习笔记

1、什么是MySQL索引？

定义：索引是一种帮助提高查询效率的数据结构，将无序的数据转化为有序的查询。

优点：大大地提高了查询效率

缺点：1、索引占据磁盘空间 。 2、维护索引需要消耗资源 。

2、索引分类

1、主键索引

设定主键后创建数据库会自动建立索引，innoDB为聚簇索引

2、普通索引（单列索引或者单值索引）

即一个索引只包含单个列，一个表可以有多个单列索引

3、唯一索引

索引列的值必须唯一，允许有空值

4、复合索引

即一个索引包含多个列

3、如何创建索引

创建索引主要有两种方式，分为建表时创建索引和建表后创建索引。(主键自动创建索引)

建表时创建索引：

create table user(id int primary key,name varchar(20),key(name));

建表后创建索引：

create index 索引名 on 表名(字段);

查看索引：

show index from 表名;

删除索引：

drop index 索引名 on 表名;

4、复合索引（最左前缀原则）

创建了复合索引，在查询过程中，会存在是否使用到了复合索引的问题。在满足一下2个条件时，才会使用到复合索引。

1、最左前缀原则

2、mysql引擎在查询更好的利用索引，在查询过程中会动态的调整查询字段顺序以便利用索引。

举个例子:创建了一个组合索引 name  age bir ,以下情况是否使用到索引

 5、聚簇索引和非聚簇索引

聚簇索引：将数据存储和索引放到一块，索引的叶子节点保存了行数据。

非聚簇索引：将数据和索引分开存储，索引的叶子节点指向数据对应得位置。

注意：在innoDB中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是要二次查找。 

1、innoDB使用的是聚簇索引，将主键组织到一颗B+树中，行数据存储到叶子节点上，若使用“where id=1”这样的条件查询，按照B+树的检索算法即可查找到对应得叶子结点，从而获取到数据。

2、若对name列进行条件查询，则需要2个步骤（回表查询）：

（1）在辅助索引B+树中检索Name，得到其叶子结点对应得主键

（2）使用主键在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取数据

6、适合创建索引的情况

（1）字段的数值有唯一性的限制

（2）频繁作为where查询条件的字段

（3）经常group by 、 order by 的列

（4）统计字段适合建立索引，如count()、max()

7、什么情况不适合建立索引

（1）频繁更新的字段不适合建立索引

（2）where条件中用不到的字段不适合建立索引

（3）表数据可以确定比较少的不需要建索引

（4）数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

（5） 参与列计算的列不适合建索引

8、innoDB是如何实现事务的？ 

利用回滚日志（undo log） 和 重做日志（redo log） 两种表实现事务，并实现 MVCC (多版本并发控制)；

在执行事务的每条SQL时，会先将数据原值写入undo log 中， 然后执行SQL对数据进行修改，最后将修改后的值写入redo log中。

redo log 重做日志包括两部分：1 是内存中的重做日志缓冲 ；2 是重做日志文件。在事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务commit操作完成才算完成。

当一个事务中的所有SQL都执行成功后，会将redo log 缓存中的数据刷入磁盘，然后提交。

如果发生回滚，会根据undo log 恢复数据。

特征	INNODB实现方式
原子性（A）	回滚日志（undo log）：用于记录数据修改前的状态；
一致性（C）	重做日志（redo log） ：用于记录市局修改后的状态；
隔离性（I）	锁  ：用于资源隔离，分为共享锁和排它锁；
持久性（D）	重做日志（redo log） + 回滚日志（undo log）；