mysql索引数据结构的探究

一、索引的介绍

1.1 索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构

 

常见的数据结构：

        二叉树

        红黑树

        Hash表

        B- Tree

        B+Tree（索引默认的数据结构）

 

1.2 为什么要使用索引

索引的好处：查询速度快

 

索引是不是越多越好？

• 索引会增加数据库服务器写入操作的成本（INNODB对这个 做了一个优化：插入缓存 将多次插入合并成一次插入）
• 索引过多的话，会增加mysql查询优化器的选择时间，从而影响查询效率

 

1.3 为什么索引的数据结构使用的是B+树

1.3.1 二叉树

二叉树是n(n>=0)个结点的有限集合，该集合或者为空集（称为空二叉树），或者由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树组成。

由二叉树定义以及图示分析得出二叉树有以下特点：
1）每个结点最多有两颗子树，所以二叉树中不存在度大于2的结点。
2）左子树和右子树是有顺序的，次序不能任意颠倒。
3）即使树中某结点只有一棵子树，也要区分它是左子树还是右子树。

二叉树的性质

1）在二叉树的第i层上最多有2i-1 个节点 。（i>=1）
2）二叉树中如果深度为k,那么最多有2k-1个节点。(k>=1）
3）n0=n2+1 n0表示度数为0的节点数，n2表示度数为2的节点数。
4）在完全二叉树中，具有n个节点的完全二叉树的深度为[log2n]+1，其中[log2n]是向下取整。
5）若对含 n 个结点的完全二叉树从上到下且从左至右进行 1 至 n 的编号，则对完全二叉树中任意一个编号为 i 的结点有如下特性：

(1) 若 i=1，则该结点是二叉树的根，无双亲, 否则，编号为 [i/2] 的结点为其双亲结点;
(2) 若 2i>n，则该结点无左孩子， 否则，编号为 2i 的结点为其左孩子结点；
(3) 若 2i+1>n，则该结点无右孩子结点， 否则，编号为2i+1 的结点为其右孩子结点。

二叉树的数据结构

 

 

从大到小按顺序插入，或者从小到大按顺序插入，这时候会形成一条链表

 

从小到大

由上面可以得出结论，就是在二叉树中，会存在这么一种极端的情况，就是按顺序插入的时候，二叉树会往一边插入，这时候就会形成链表，而链表是不利于查询的。

 

1.3.2 红黑树

红黑树，Red-Black Tree 「RBT」是一个自平衡(不是绝对的平衡)的二叉查找树(BST)，树上的每个节点都遵循下面的规则:

1. 每个节点都有红色或黑色
2. 树的根始终是黑色的 (黑土地孕育黑树根， )
3. 没有两个相邻的红色节点（红色节点不能有红色父节点或红色子节点，并没有说不能出现连续的黑色节点）
4. 从节点（包括根）到其任何后代NULL节点(叶子结点下方挂的两个空节点，并且认为他们是黑色的)的每条路径都具有相同数量的黑色节点

结论：按顺序插入的时候，红黑树会做一个自平衡，所以这也是跟二叉树相对比具有的一个最明显的特点，也是优点，通过自平衡之后，它的查询速度就加快了许多。

 

那么在索引中使用红黑树，有什么缺点呢？

虽然红黑树有一个自平衡的操作，查询速度相对于二叉树提升了许多。但是在一些真实情况中，也就是在表数据量很大的时候，红黑树的深度也是大，一棵树的的深度不可控制，也会影响查询速度。

 

1.3.3 hash表

对于hash表结构来说，它的数据结构是数组+链表，这种结构对于查询速度也是很快的。

那么hash对于用作索引结构，有什么缺点呢？

hash在作某个具体值查询的时候，查询速度就会很快

例如

select * from table where id = 6

但是通常是，我们在实际应用中，可能会涉及查询某个范围的值，这对于hash结构存储的数据，显然是不合适的，hash不适合查询范围

 

1.3.4 B-Tree

B-tree的特点：

1.叶节点具有相同的深度，叶节点的指针为空

2.所有索引元素不重复

3.节点中的数据元素索引从左到右递增排列

 

1.3.5 B+Tree（B-Tree的变种）

B+Tree在B-Tree的基础上作了一些改进

1.非叶子节点不存储data,只存储索引（冗余），可以放更多的索引

2.叶子节点包含所有索引字段

3.叶子节点用指针连接，提高区间访问的性能

 

结论：b+数的深度是可控的，而且非叶子节点的索引也是按顺序递增的，叶子节点索引按顺序递增，并且不同节点之间有指针相连接，这也是跟b-tree的一个区别，这种指针相连接，对于范围查询也提供了条件。

 

二、存储引擎的介绍

2.1 MyISAM存储引擎

MyISAM索引文件和数据文件是分离的（非聚集）

存储引擎是形容表的，数据库的表是存储在磁盘中

结论：非聚集索引，是查询两次，数据跟索引分开存储。相对于聚集索引，查询效率相对慢一点

 

2.2 InnoDB索引（聚集）

1.表数据文件本身就是按B+Tree组织的一个索引结构文件

2.聚集索引-叶节点包含了完整的数据记录

3.为什么innoDB表必须要有主键，而且推荐使用自增的主键？

4.为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

 

为什么主键推荐使用自增呢？

答：因为B+Tree本身也是存在一种自平衡机制，如果不是自增的话，随意插入一个数，那么树就会频繁的做平衡操作，这样是比较耗费资源的。

 

聚集索引跟非聚集索引的区别是什么？

索引跟数据分开存储，是非聚集索引（MyISAM）

索引跟数据一起存储，聚集索引（InnoDB）

 

2.3 InnoDB跟MyISAM的区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； 

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

       MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

       也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

那么为什么InnoDB没有了这个变量呢？

    因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
    如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
 

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

       InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

8、InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

        Innodb：frm是表定义文件，ibd是数据文件

        Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

innodb引擎的4大特性

       插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)