Mysql-索引数据结构&引擎篇

Mysql可以说是最广泛使用的数据库之一了，体积小，成本低，开源（免费才是王道呀-。-），本文旨在和大家一起探索Mysql的一些相关知识，不仅要会用它来写sql，更要学习它的底层设计和技术延伸。

索引数据结构

Mysql索引是基于B+tree的数据结构来设计的，那么为什么不使用二叉树，Hash（其实是支持的），B-tree等结构来设计索引呢？

二叉树：

树的层数过高，容易退化成链表

平衡二叉树，红黑树：

层数仍然过高，会大大增加系统的IO频率

Hash

Mysql是支持Hash索引的，只不过Hash索引不支持范围查找，而我们在日常工作中需要广泛的运用到范围查询

B-tree：

1.在B-tree和B+tree中，每一个节点叫做一个磁盘页，每一个磁盘页的大小是16K，那么相比较于B-tree是在每个节点上都存储数据，B+tree是只在叶子节点上存储数据，相同层数下，B+tree能存储的数据量要大于B-tree
2.B+tree的叶子节点有双向指针，对于范围查找的效率能大大提升

存储引擎

Mysql中有很多种存储引擎，我们这里主要介绍的是MyISAM和InnoDB。

打开不同引擎的表的存储文件夹，会发现这两种引擎用来保存相关数据的文件不同:
MyISAM

.frm文件：存储表结构
.MYD文件：存储数据
.MYI文件：存储索引
查询时，如果有索引，在MYI文件中根据索引获取数据地址，再去MYD文件中查找到数据

InnoDB

.frm文件:存储表结构
.ibd文件：存储索引和数据
索引和数据都存储在ibd文件

---

除了存储文件上的区别，MyISAM和InnoDB还有以下的区别：

• 前者是非聚集索引，后者是聚集索引
• 前者不支持事务，后者支持
• 前者不支持外键，后者支持
• 前者只支持表锁，后者支持表锁和行锁
• 前者保存表的行数，后者每次仅限count(*)操作需要去扫描全表
• delete表的时候，前者是重新建表，后者是一行行的删
  ···

那么，什么时候使用哪个最好呢？一般来说，系统业务涉及到查询占大部分，对事务需求度低，容忍度高的，可以使用MyISAM引擎，MyISAM查询效率要高于InnoDB。反之，系统涉及并发量大，需要大量的增删改操作，建议使用InnoDB引擎。

tips：MyISAM查询效率更高，是因为：InnoDB要缓存数据块，而MyISAM只要缓存索引块；在select的时候InnoDB需要去维护MVCC（多版本并发控制）；InnoDB查询需要映射到块再到行，而MyISAM直接记录文件的offset，定位更快

聚集索引和非聚集索引

• 对于主键索引和非主键索引来说，MyISAM主键索引的叶子节点和非主键索引的叶子节点都存放的是行数据的磁盘地址。InnoDB非主键索引的叶子节点存储的是主键值，而主键索引的叶子节点里存储的是行数据，当进行非主键索引查询时，先在非主键索引中查找到对应的主键值，然后根据主键值再去主键索引里进行一次树查询，获取主键索引中存储的行数据。（这种第一次树查询定位主键，第二次再进行一次树查询的操作叫做回表）
• 根据索引存储方式的不同，我们把MyISAM的主键索引和非主键索引类型叫做非聚集索引，把InnoDB的主键索引类型叫做聚集索引，非主键索引类型叫做辅助索引（普通索引）。聚集的含义可以理解为索引和数据聚合在一起。

使用InnoDB时的tips：
1.基于上面的设计，InnoDB必须设置主键索引，所以一般建议我们在进行表的设计的时候都要添加主键列，如果不设置主键，mysql会在表中寻找一个唯一列来当做主键索引，如果没有这样的列，它会去维护一个虚拟列，用以建立主键索引
2.主键尽可能的要设置成自增整型类型，因为最终在B+tree中是需要去比较索引大小的，如果是非整型的，或者是无序的主键，还需要先去进行值转换，无疑增加了额外时间开支