Mysql索引

数据结构

1.Hash表
2.B+Tree(数据只存放在叶子结点)

实际情况中一般都是使用B+Tree，因为Hash表只能用于定值查找:

slect * from table where value = 10;

无法用于范围查找：

slect * from table where value > 10;

这种情况下，需要对每个value大于10的进行Hash，然后逐个查找

MyISAM

MyISAM在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。

• .frm：用于存储表的定义
• .MYD：用于存放数据
• .MYI：用于存放表索引

主键索引

MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址

MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行

树中叶子保存的是对应行的物理位置。通过该值，存储引擎能顺利地进行回表查询，得到一行完整记录。同时，每个叶子页也保存了指向下一个叶子页的指针。从而方便叶子节点的范围遍历。

辅助索引

在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复

Innodb

Innodb有两种存储方式，共享表空间存储和多表空间存储。

Innodb只有表结构文件和数据文件。

表结构文件和MyISAM一样，以表名开头，扩展名是.frm。

数据文件与存储方式有关：

• 如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。
• 如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。

主键索引

Innodb主键索引中，既存储了主键值，又存储了行数据。

辅助索引

对于辅助索引，InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表（上图）查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询，效率肯定是没有按照主键检索高的。

为什么InnoDB的表必须要有主键，并且推荐使用整形的自增主键？

由于索引信息和数据都存放在.ibd文件中，这个文件中的数据必须要有索引维护，所以用InnoDB的表必须要有主键(索引)，如果建表时没有指定主键，Mysql会生成一个默认的索引。
因为索引是树的结构，整形作为主键(索引)方便比较查找且占用存储空间较少。
之所以用递增主键，因为B+Tree的结构，如果新插入的数据的索引比现存的所有都大，只需要向右添加节点，否则，就很有可能要分裂现有的节点，进行树的平衡操作。

为什么非主键索引结构叶子节点存储的是主键值？

和数据表保持一致，并节约存储空间

复合索引

以表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：

name	id
a	6
c	4
c	5
h	1
z	9

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

那什么时候才能用到第二个索引?
那就是cid字段的索引数据也是有序的情况下，什么时候才是有序的呢？观察可知，当name字段是等值匹配的情况下，cid是有序的。观察两个name名字为 c 的cid字段，从上往下分别是4 5，有序。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）

EXPLAIN SELECT * FROM student WHERE   cid=5 AND name='c';

对于这条Sql语句，复合索引中的两个索引字段都利用到了。因为语句中最左面的name字段进行了等值匹配，所以cid是有序的，也可以利用到索引了。

但是，此表建立的索引是(name,cid)，而查询的语句是 cid=5 AND name='c'; 是先查询cid，再查询name的，不是从最左开始查的，为什么也能用到全部索引？

首先可以肯定的是把条件判断反过来变成这样 name='c' and cid=5; 最后所查询的结果是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？

此时就是该mysql查询优化器登场了，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，最终还是以name='c' and cid=5;的方式查询。