mysql数据库索引 HASH和B树

什么是索引？

索引是一种特殊的数据结构，可以用来查询数据库表中特定的记录。他由数据库中的一列或者多列组成，可以提高数据库的查询速度。

我们先看一下如果没有索引的情况下数据库是怎么查询数据的？

例如查询

select * from usr where name = '张三';

当执行这句sql语句的的时候，数据库会逐行遍历整张表，对于每一行都会比较name是否等于张三，因为是要查询所有姓名为张三的记录，所以要遍历整张表，做了一个全局检索。

使用索引是怎么查找的呢？这里不同的索引存储类型有不同的查找方式。

索引的两种存储类型 B树索引和hash索引

不同的数据库引擎支持不同的索引存储类型，InnoDB和MyISAm支持btree，memory支持hash和btree。

hash存储

hash存储是一种散列表结构的存储 

hash存储的实质是将数据(key/value类型数据)存储到一个数组中，当数据存储时利用hash函数hash = H(key)得到一个散列值，然后利用散列值再去计算出数据应该存放到数组的某个位置index，将数据存入数组的index位置，数据查找时传入key值，hash = H(key),进而得到index值，直接取出index位置的地址，利用取出的地址去磁盘中直接定位到某一条记录数据，其时间复杂度为O(1)。

在mysql hash存储结构的索引中，key值保存的是列值，也就是添加索引的列的值，value是个指针指向列值对应的行地址。

例如

select * from usr where name = '张三';

通过张三得到hash值进而得到index假如是3，直接利用index=3，到数组的3位置取出地址0x23109，然后根据地址0x23109去磁盘中取出该位置的数据。 这是种理想的没发生hash碰撞情况，那如果key值发生碰撞了怎么办？？？？？？？

hash存储冲突了怎么办？

如果是存在多个为张三的数据如果索引采取的是拉链法处理这种冲突，查询的时候解决起来也很轻松可以根据拉出来的链表依次读取地址并到磁盘中读取数据，如果是其他的解决冲突的方法，例如再hash，开放地址法等，最坏的情况可能是表中只存储了关键字都是相同的key，那么就避免不了需要检索整个索引，当记录数超级多的时候，虽然能提高效率，但是对整个索引表的遍历工作量也是巨大的。

hash 存储结构非等值条件查询怎么办？

我们知道hash表这种存储结构是无序的，可以看出如果是等值查询的话，hash存储结构的索引无疑是最快的一种。但是如果进行非等值条件查询，hash存储结构的索引将显得力不从心。例如

select * from user where age < 50 and age >20;

所以hash存储结构存在缺点

• 结构的无序导致范围查询不能提高查询速度
• hash索引无法通过操作索引来排序，因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序。
• 不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值如果哈希碰撞很多的话，性能也会变得很差。

 B-树结构

B-树的定义

一棵m介的B-树，或为空树，或为满足下列特性的m叉树

1. 树中的结点之多有m棵子树。
2. 若根节点不是叶子结点，则至少有两颗子树
3. 除根以外的的所有非终端结点至少有m/2棵子树
4. 所有的叶结点出现在同一层次上，并且不带信息，通常称为失败结点。
5. 所有的非终端结点最多有m-1个关键字。

B-树为什么会出现？

B-树说了那么多我个人认为都是以二叉排序树为基础的一种改进二叉树定义就是一棵树的左子树小于根节点，根节点小于右子树，二叉排序树极端的情况是如果按照顺序插入例如 2、5、8、23、67就会出现一条链的情况就成了单链表，查找时的平均查找长度最长，因此出现了平衡二叉树，平衡二叉树解决了这种极端的情况，提出了平衡因子（就是左子树的高度减右子树的高度），并且平衡因子必须为1、-1、0才能称之为平衡二叉树，就是为了减少树的高度，减小查找长度。而对于B-树是为了解决磁盘IO速度相对于内存来说速度慢出现的，我们都知道对于平衡二叉树的查找来说也是分为两步1.查找到结点2.将结点读入内存，根据key查找数据（二叉树每个结点只有一个key），平衡二叉树在查找时每次磁盘的IO查询，查出一个结点，结点中只有一个数据，磁盘的IO是非常的慢的，对于此问题就有人想能不能每次很辛苦的磁盘IO得到多条数据，这就出现了B-树这种数据结构，每个结点上存在多个key值，也就是多条数据，这样就能充分利用每次磁盘的io查询。

B-树的查找两步1.（磁盘上进行）查找结点2.（内存中进行）将结点读入内存，使用顺序查找或者二分查找key。

B-树的查找过程 例如这棵B-树

查找 关键字47，首先47>35，查询a的右子树，与43,78比较，43<47<78,就顺着43右边临近的指针向下查找，47与47,53,64比较发现47=47，即查找成功。

查找10 首先根节点比较 10<35,查左子树，与18比较 10<18查左子树，10<11，查左子树，左子树为失败结点，查找结束，查找失败。

对于mysql数据库中的B-树结构的索引的查找亦是如此，每个结点上存储的是一个关键字key和value，也可以说根据关键字查询出 value，而value中存储的也是磁盘中数据库的某一条记录的地址。

可以看出如果是去做等值查询的话其速度是不及hash存储的，但是如果是范围查询的话那将是非常的快，比如上图中的是对年龄设置了索引，我们查找年龄小于35岁的，那么当第一次与根节点比较时，就能确定左子树上的所有的都是满足的，直接去遍历所有节点即可。

在数据库中索引的分类

 1. 普通索引 创建一个普通索引时不需要加任何的限制条件 

CREATE INDEX index1 ON book(id);

如果是将索引设置在char varchar text字段上需要指定长度。

例如再varchar类型的name字段上加索引，查找时比较前10个字节，相同就匹配。

CREATE INDEX index1 ON book(name(10));

2. 唯一索引 通过UNIQUE参数设置的索引为唯一索引

CREATE UNIQUE INDEX index1 ON book(id);

3. 全文索引

使用FULLTEXT参数设置为全文索引，全文索引只能创建在 char varchar text 类型的字段上。

 4. 单列索引  创建在单个字段上的索引

CREATE INDEX index1 ON book(id);

5. 多列索引  创建在多个字段上的索引

CREATE INDEX index1 ON book(id,name(10));

这里注意：对于多列索引来说，查询时只有使用了第一个字段时才会使用索引查询，也就是说

select * from book where id = 1;

不会使用索引查询。

select * from book where id = 1 and name = '张三';

这种情况下才启动索引。

索引的优点：提高查询速度

索引的缺点：1.创建索引时 消耗时间。2. 索引需要物理空间。 3. 增删改操作，需要动态维护索引。