MySql索引那些事
1、什么是索引
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。就像我们以前用的新华字典的目录一样，能帮助我们快速查询到某一个字。
二、索引的分类
分类角度划分如下
数据结构： B+树，Hash索引，R-Tree等
存储层面： 聚簇索引，非聚簇索引
逻辑层面： 主键索引，普通索引，组合索引，唯一索引，全文索引等
index ---- 普通索引,数据可以重复，没有任何限制。
unique ---- 唯一索引,要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。
primary key ---- 主键索引,是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建主键索引。
组合索引 ---- 在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
fulltext ---- 全文索引,是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。
注:全文索引目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引
所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节
Mysql索引主要有两种结构："B+Tree索引和Hash索引"。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。索引如图所示：
最外层浅蓝色磁盘块1里有数据17、35（深蓝色）和指针P1、P2、P3（黄色）。P1指针表示小于17的磁盘块，P2是在17-35之间，P3指向大于35的磁盘块。真实数据存在于子叶节点也就是最底下的一层3、5、9、10、13……非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35。
查找过程：例如搜索28数据项，首先加载磁盘块1到内存中，发生一次I/O，用二分查找确定在P2指针。接着发现28在26和30之间，通过P2指针的地址加载磁盘块3到内存，发生第二次I/O。用同样的方式找到磁盘块8，发生第三次I/O。
真实的情况是，上面3层的B+Tree可以表示上百万的数据，上百万的数据只发生了三次I/O而不是上百万次I/O，时间提升是巨大的。
数据表索引可以提高数据的检索效率，也可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。排序分组操作主要消耗的就是CPU资源和内存，所以能够在排序分组操作中好好的利用索引将会极大地降低CPU资源的消耗。
三、索引实例分析(以InnoDB为例)
3.1 InnoDB下索引的结构
InnoDB下，表都是根据主键顺序以索引的形式存放的，这种数据存储方式也被称为聚簇索引，“聚簇”就是表示数据行和相邻的键值紧凑的存储在一起，也就是数据行实际上是存储在索引的叶子页中。我们创建一张表来实际说明下InnoDB下的索引结构，建表语句如下：
<code>create table person(
id int primary key,
age int not null
index (age)engine=InnoDB;</code>
然后我们插入五条数据分别为(1,15),(2,17),(6,20),(10,18),(19,21)，索引的树结构如下:
上图中展示了两部分内容，第一个图为聚簇索引（主键索引）的内容，可以看到，数据按照Id的大小排序，对应的索引会包含该索引的整行数据。
第二个图展示了用age做索引的索引结构图，也就是非聚簇索引（非主键索引），可以看到索引以年龄排序，但是和主键索引不同的是，年龄索引对应的却是Id，所以我们可以知道非主键索引记录的内容就是主键索引的值。
这里可能有同学会有疑问，如果我建表的时候没有指定主键的话，索引结构又是如何的呢？其实在InnoDB中，如果没有定义主键，那么他会选择一个唯一的非空索引代替。如果没有这样的索引，那么他会隐式的定义一个主键来作为聚簇索引。所以无论你是否设置主键，InnoDB还是会帮你满足以上图的形式来索引数据。接下来我们分析下索引查询的流程。
3.2 索引查询分析
假设我们执行一条查询语句
select * from person where ID = 6
因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。
如果执行的sql语句是非主键索引
select * from person where age = 18
上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。
从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。
3.3 覆盖索引
我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做“覆盖索引”。
那么如何建立一个覆盖索引呢？答案是通过联合索引来实现，通过联合索引的字段来覆盖要查询的字段，从而达到索引覆盖的效果。
我们把上面的建表语句改造下，来分析下如何实现覆盖索引。
<code> CREATE TABLE `person` (
`id` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code>
上面我创建了一个name和age的联合索引，索引结构图表示如下：
我们根据图可以知道，联合索引是和创建索引字段顺序有关的，上面这个例子就是先以name排序，然后name相同再以age为标准排序。那么我们建表后该如何达到覆盖索引的效果呢？相信有些同学已经知道了怎么写sql可以达到覆盖索引效果，sql如下：
select name,age from person where name = "Barry"
因为我们需要查询的字段name和age，都在索引中可以直接查询到了，所以不需要查找到主键ID，然后再回表了。
看到这里，肯定有同学会说，既然这样的话，我把所有需要查询的字段组合都建上联合索引不就行了吗？答案是：不行。因为索引也是需要消耗空间的，而且维护索引也是需要成本的，这一点我会在后面的优缺点中提到。那么有没有别的方式可以尽可能的实现不回表的效果呢？这里我们就要引入MySql的最左前缀原则了。
什么叫最左前缀原则呢？就是在索引的匹配中，可以以索引的最左N个字段,也可以是字符串索引的最左N个字符。比如在上图中，要查询以A开头的名字，查询语句就是
select name from person where name like 'A%'
这个时候就可以满足最左前缀规则来使用索引查询了，这里就会依赖索引查询到第一个首字母是A的名字，然后向后遍历，直到不满足条件为止。
那么最左N个字段是什么意思呢？意思就是索引（name,age),可以直接利用 name来当做单独索引使用，可以只使用联合索引的部分字段，但是必须是顺序一致，比如索引（a,b,c)，如果要想使用最左前缀规则，可以使用索引a,ab。
我们也可以利用该规则来少维护一个或多个索引，比如我们需要 a,ab,abc的查询，那就只需要(a,b,c)联合索引就满足要求了。
3.4 索引下推
在MySql 5.6版本中引入了一个新特性，叫做“索引条件推送（index condition pushdown)”，这也称为索引下推。那么索引下推是这个什么东东呢？其实从“索引条件推送”这个名字就可以表明，这个特性是可以在索引中的字段进行条件判断，然后过滤不满足条件的记录，减少回表的次数。
比如以上图中的数据为准，sql如下：
select * from person where name like 'A%' and age =19;
那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。
在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。
3.5 索引的优点与缺点
说了这么多关于索引的内容，我们来谈谈索引的优缺点。
优点：
减少服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变为顺序IO
缺点
索引会占用额外的存储空间
索引的维护需要一定的成本，插入数据后需要保证原来的索引有序，所以也会影响一定的数据库性能。
四、总结*
索引的定义，索引的分类，索引按照不同的角度可以分为常见的哪几种。然后在InnoDB下索引的索引的数据结构。 主键索引和非主键索引的区别就是查询主键索引可以直接返回数据，非主键索引需要先查询出主键ID，然后再查询出数据，这个过程就叫做回表。我们可以通过覆盖索引减少回表的次数，从而达到提高性能的效果。在mysql5.6以后，InnoDB可以支持索引下推，在使用联合索引的时候，如果可以在索引判断条件，那么就在索引中过滤不满足条件的行，从而减少回表次数。
