MySQL的InnoDB引擎及其索引详解

一、索引简介

1.什么是索引

索引（index）是帮助数据库高效获取数据的数据结构。由此可知，索引的本质是一种数据结构，而索引的目地在于提高查询效率，可以类比于字典目录。
在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

2.优点与缺点

优点：

1. 可以大大加快数据的检索速度，提高数据检索效率。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
3. 降低数据库的IO成本，将随机IO变为顺序IO（顺序IO不需要多次磁盘寻道，所以比随机IO快很多），当对于group by查询时无需再做额外的排序处理。

缺点：

1. 除了数据表占用数据空间以外，每一个索引也要占用一定的物理空间。
2. 创建索引和维护索引都要耗费时间，这种时间会随着数据量的增大而增大。创建索引后，虽然索引大大提高了查询速度，却会降低更新表的速度。如对表进行INSERT、UPDATE和DELETE操作时，索引也要进行动态维护，降低了数据的维护速度。

3. 聚簇索引和非聚簇索引

聚簇索引：表中某行数据的索引和数据是合并在一起进行存储的。
非聚簇索引：表中某行数据的索引和数据是分开存储的。

4. 什么是回表

通常情况下，当使用非聚簇索引进行查找时，非聚簇索引的叶子节点储存的是主键值，通过查找到的主键值回到聚簇索引中查找实际元素的操作称之为回表。

二、InnoDB存储引擎

1.简介

InnoDB存储引擎使用的是 B+树 的索引结构。由于InnoDB 使用了 B+树 索引模型，所以数据都是根据主键顺序以索引的形式存储在一棵B+ 树中的。
每一个索引在 InnoDB 里面都对应一棵 B+树。其中主键索引树采用的是聚簇索引，而普通索引树采用的是叶子节点为主键值的非聚簇索引（所以使用普通索引进行查找时，需要去主键索引树上进行回表查询）。

由于基于主键的查询和排序性能很高。所以，InnoDB存储引擎会自动为我们的主键创建聚簇索引。聚簇索引就是按照表中的主键构造的一颗B+树，其叶子节点储存的是整张表的完整行记录数据。当查询使用聚簇索引时，在对应的叶子节点中，可以获取到整行数据，因此不用再次进行回表查询。

2.优势

1. 遵循ACID模式设计，具有事务和回滚的能力。
2. InnoDB提供行级锁，可以不加锁读取，增加了多用户时的并发性能。
3. 基于主键的查询和排序性能很高。会使用聚簇索引，在对应的叶子节点中即可获取到整行数据，因此不用再次进行回表查询。

三、InnoDB索引详解

1.InnoDB索引介绍

假设，我们有一个 主键列为ID的表，表中有字段 k，并且k上有索引。表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)。则两棵索引树的示例图如下。

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存储的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。
根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？
· 如果语句是 select * from T where ID = 500，即主键查询方式，则只需要搜索ID这棵B+树；
· 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

2.建议使用自增id的原因

首先，InnoDB会默认为主键创建聚集索引（与其说是为主键创建的索引，更形象的来说，应该是InnoDB的数据直接保存在一颗以主键为索引的索引树上）。
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。用户在插入新记录的时候可以不指定ID的值，系统会获取当前ID的最大值加1 ，作为下一条记录的ID值。如果使用自增ID作为主键，那么在每次插入新数据时，都会顺序的将记录添加到索引树的末尾处，不需要调整树中其他节点位置。
也就是说，自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而如果使用业务逻辑字段或者使用UUID等随机ID做主键的话，由于每次插入的ID都是随机的，就不容易保证有序插入。当新纪录被插入到树的中间位置时，就需要移动索引树，增加性能消耗。同时，由于大量的移动操作，会造成索引树中产生大量的空间碎片，为了得到更加紧凑的索引结构，就不得不去重建表来优化页面，这样写数据的成本相对较高。
除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？由于每个非主键索引树的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间两方面考量，自增主键往往是更合理的选择。

3.索引的创建原则：

适合创建：

1. 主键字段应该创建索引。
2. 频繁作为查询条件的字段，在where条件中经常使用的字段应该创建索引 。
3. 会进行排序的字段（会用group by和order by进行排序的字段）建议创建索引，若通过索引去访问将大大提高访问速度。

不适合创建：

1. 表中记录的数据较少时，因为创建索引这件事本身就需要时间。
2. Where查询条件里用不到的字段，不应该创建索引。
3. 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段。
4. 更新频繁的字段不适合创建索引，比如经常执行增删改操作。因为每次不仅要更新数据记录，还要更新索引。

4.查询SQL的书写原则

1. 全值匹配我最爱，在索引列上全部依次使用常量值进行查找。
2. 最佳左前缀法则（带头大哥不能死，中间兄弟不能断）。
3. 不在索引列上做任何操作，比如计算、函数、(自动或手动得)类型转换等，否则会导致索引失效而转向全表扫描。
4. 尽量使用覆盖索引，只访问索引的查询(索引列和查询列一致)，减少select * 的操作。
5. 少使用不等于(!=或者<>)，因为使用不等于的时候无法使用索引。is null ,is not null也无法使用索引，会导致全表扫描。
6. Like以通配符开头(‘%晴…’)时，mysql索引失效会变成全表扫描。
7. 字符串不加单引号，会导致索引失效。
8. 少用or,用它来连接时，会导致索引失效。