mysql索引类型与数据存储

myisam与innodb索引与数据存储

一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node(叶子节点) ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引。当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息（增加了顺序访问指针），这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

一、myisam索引实现方式

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。

其主键索引与普通索引没有本质差异：

有连续聚集的区域单独存储行记录,主键索引的叶子节点，存储主键，与对应行记录的指针,普通索引的叶子结点，存储索引列，与对应行记录的指针；

举个例子：
有用户表如下：id为主键，name字段为普通索引


主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

行记录单独存储,id为PK，有一棵id的索引树，叶子指向行记录,name为KEY，有一棵name的索引树，叶子也指向行记录。

二、innodb索引实现方式

InnoDB的主键索引与行记录是储存在一起的，故叫做聚集索引（Clustered index）;

没有单独区域储存行记录，主键索引的叶子节点，储存主键，与对应行记录（而不是指针）

因为这个特性，InnoDB的表必须要有聚集索引；

1. 如果表定义了PK，则PK就是聚集索引；
2. 如果表没有定义PK，则第一个非空unique列是聚集索引；
3. 否则，InnoDB会创建一个隐蔽的row-id作为聚集索引；

聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集储存。

InnoDB的普通索引可以有多个，它与聚集索引是不同的：普通索引的叶子节点，储存主键（也不是指针）

对于InnoDB表，这里的启示是：

1. 不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会储存主键，会导致普通索引过于庞大；
2. 建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；

举个例子：
有用户表如下：id为主键，name字段为普通索引


其B+树索引构造如上图：id为PK，行记录和id索引树储存在一起，name为key，有一颗name的索引树，叶子储存id

当查询 select * from user where name = 'ls';

会先通过name辅助索引定位到B+树的叶子节点得到id=5，再通过聚集索引定位到行记录。

三、InnoDB的回表问题

如粉红色路径，需要扫码两遍索引树：

1. 先通过普通索引定位到主键值id=5;
2. 在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

什么是覆盖索引？

mysql官网表达意思：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

概念：索引覆盖 =》 通过普通索引查询的时候，不需要回表查询，直接可以获取到对应的数据

如下示例：

该怎么做可以实现覆盖索引，提高效率？

创建联合索引 (name,sex) 可以避免回表查询提高效率

索引的使用跟查询的 and 的前后顺序没有关系：

创建 (name,sex,flag) 索引，以下这两个查询都走索引

select name,id,sex from table where name = "zhangsan" and sex = 1;

select name,id,sex from table where sex = 1 and name = "zhangsan";

以下查询不走索引：

select name,id,sex from table where sex = 1;

select name,id,sex from table where sex = 1 and flag = "xxx";

建立一个索引 idx(A,B,C) 要使用A，AB，ABC 这样的顺序查询，应遵守最左匹配原则；而使用B，BC 这样是使用不到索引的。

四、hash 索引

在MySQL的储存引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是储存引擎根据B-Tree索引自建的。

hash 索引的特点

1. hash 索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。
2. 对于hash索引中的所有列，储存引擎都会为每一行计算一个hash码，hash索引中储存的就是hash码。
3. hash索引包括键值、hash码和指针。

因为hash索引本身只需要储存对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash 索引的限制

1. hash索引必须进行二次查找，使用hash索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
2. hash索引不能用于外排序，hash索引储存的是hash码而不是键值，所以无法用于外排序。
3. hash索引不支持部分索引查找，也不支持范围查找，只能用到等值查询，更不能使用模糊查询。
4. hash索引中的hash码的计算可能存在hash冲突(概率小)。

当出现hash冲突的时候，储存引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上(重复值很多)。

上面所到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上再创建一个hash索引，这样就让B-Tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

B Tree 索引和 hash 索引的区别

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像 B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，索引hash索引的查询效率要远高于B-Tree索引。

可能很多人又有疑问了，既然hash索引的效率要比B-Tree高很多，为什么大家不都hash索引而还要使用B-Tree索引呢？任何事物都是有两面性的，hash索引也一样，虽然hash索引效率高，但是hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

1. hash索引仅仅能满足"=",“in"和”< = >"查询，而不能使用范围性的查询。
   
   hash索引只支持等值比较查询，包括 = in <=>（注意 <> 和 <=> 是不同的操作）。也不支持任何范围查询，例如 where price > 100 。由于hash索引比较的是进行hash运算之后的hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的hash算法处理之后的hash值的大小关系，并不能保证和hash运算前完全一样。
   
   

2. hash索引无法被用来避免数据的排序操作。
   
   由于hash索引中存放的是经过hash计算之后的hash值，而且hash值的大小关系并不一定和hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
   
   

3. hash索引不能利用部分索引键查询。
   
   对于组合索引，hash索引在计算hash值的时候是组合索引键合并后再一起计算hash值，而不是单独计算hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，hash索引也无法被利用。
   
   

4. hash索引在任何时候都不能避免表扫描。
   
   前面已经知道，hash索引是将索引通过hash运算之后，将hash运算结果的hash值和所对应的行指针信息存放于一个hash表中，由于不同索引键存在相同hash值，所以即使取满足某个hash键值的数据的记录条数，也无法从hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
   
   

5. hash索引遇到大量hash值相等的情况后，性能并不一定就会比B-Tree索引高。
   
   对于选择性比较低的索引键，如果创建hash索引，那么将会存在大量记录指针信息存于同一个hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。