服务化-mysql-04 mysql索引的数据结构

1 mysql索引概述

索引是帮助mysql高效获取数据的排列好的数据结构。在Mysql中，索引是在存储引擎层实现的。三种常见的索引数据结构：哈希表、有序数组、搜索树。

2 数据结构

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 南佛罗里达大学展示数据结构示意的网站

2.1 二叉树

二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。

特点：每个叶子节点左侧小于右侧。

但将二叉树结果作为mysql的索引时有个缺点，既当数据单项增长时，二叉树将退化为链表。此时利用索引来查找数据时，等于遍历整个链表，查找复杂度过高。

                                                               图1 二叉树的链表形态

2.2 二叉平衡树（红黑树）

二叉平衡树又称红黑树，mysql依旧没有使用它来作为索引的数据结构，归根结底还是查找复杂度h过高(最大值为树的高度+1)。弊端：数据量大时，树的高度也过高。

查询复杂度：假设有n个数据，并且每层节点铺满，2^x=n（x相当于二叉树的高度），则查询复杂度不大于O(log n)

插入复杂度：同样的，插入数值需要进行h次判断大小，也是logn 

                                                                图2 红黑树的结构                

2.3 B-Tree B树

B-Tree结构可以显性的控制数据结构的高度，从而降低查找的复杂度。同时每个元素同样存储着对应value的磁盘地址。这样通过3次查找即可查询到对应value地址。

                                                                图3 B-Tree的结构

2.4 B+Tree 

B+树区别于B-Tree，可见最底层节点中，存储着所有索引的值，并且高层的树节点中不存储value数据，只用于大小的判断。

特性：

1、另外非底层的几点都是二叉树

2、数据从左到右一次递增

                                                                图4 B+Tree的数据结构

2.5 哈希表

是一种键-值（key-value）存储数据的结构，我们只要输入key，就可以找到对应的value。

Hash类似于Jdk 1.7 的HashMap，存储的结构是数组+链表

对索引的key进行hash后计算下标，找到目标桶，然后将数据插入到链表。

优点：插入和查询效率都很高。

缺点：无法根据索引的大小范围查询。

结论：所以hash表更适合做等值查询，如查询ID_CARD_01该人员的相关信息。

2.6 有序数组

有序数组在等值查询和范围查询场景中性能都非常优秀。

优点：等值查询和范围查询均采用二分法就可以快速得到结果。

缺点：插值成本太高。如上图，当你需要在User1和User4之间插入User5时，需要将User4后面所有数据挪动一步。

适用场景：只适用于静态存储引擎，历史已经事实的数据。某城市历年人口信息、2016年A股每天上市公司成交信息等。斯人已逝，既成事实。

3 B+tree索引结构

mysql中表最常用的索引类型是B+Tree，表的结构是以索引的组织形成的主干-枝-叶的形式。B+Tree的非叶子节点都是冗余索引，用于快速查找叶子节点的位置。innodb的数据都在B+tree的叶子节点中，myisam的data数据则另外存放在其他磁盘文件（.MYD）中。

在mysql中B-tree与B+tree在数据结构上的区别主要是：

1、B+tree的非叶子节点中只存放索引数据，而B-tree中的非叶子节点包含了索引所在行的data数据。这样B+tree的每个数据页所能存放的索引数据量更多，存放相同数据量的数据所需要的树高更小。

2、B+tree的叶子节点中，相邻数据页存在双向指针，更好的支持了范围查询。

3.1 结构示意

3.2 存储容量

非叶子节点的索引是存储在数据页（page）中的，而数据页的大小是有默认限制的。

 value = 16384 Byte = 16*1024  = 16K

假设索引是double或long类型数据，那么每条索引占8Byte，两个索引间的空白处存放的是类似上图0020索引的磁盘地址，大概占用6Byte,每个数据页大概可以存：16384/14 ≈ 1174个索引。

不同存储引擎的data数据存储的数据不同，以data数据占有1Kb容量计算，那么叶子节点存放的数据量大概有16个。

那么整张表可以存的数据量是 1174 *1174*16 ≈ 2200w条。

3.3 查找效率

由2.2节可见，B+Tree的树高只有3行，查找复杂度是 3。

并且为提高查询效率，mysql会将数据页的索引数据加载到内存中。索引占用的内存大小约为 16K*1174 ≈ 18784Byte = 18MB 

再看其中2次经过内存查找数据页中索引的位置（二分法查找？）

只经过1次磁盘I/O，

可见，查询的效率是相当高的。

4 InnoDB

假设有张存储引擎为InnoDB的表，可以发现其文件都内容都存在出.idb文件中（MySql-5.6版本）

并且innodb索引总结起来有以下几个特点：

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

2、聚簇索引（聚集索引）-叶节点中包含了完整的数据

3、为什么innoDB必须建主键，并且官方推荐使用整型的自增主键？

        1）innoDB的表数据文件是用B+树组织的索引结构文件，如果用户不创建主键，mysql需要额外维护一张索引列

        2）整数型数据在做数据对比时，相比其他类型数据的对比，更高效

        3）B+Tree的叶子节点是按照主键增续排列的双向链表，如果使用自增的主键，新增数据不会影响已写入的数据，不会出现叶子节点分裂、树再平衡等问题。

4、

CREATE TABLE `test_innodb` (
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5 MyISAM

假设有张存储引擎为MyIsam的表，我们来查看它的在磁盘中存储的位置。

CREATE TABLE `test_myisam` (
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  UNIQUE KEY `a` (`a`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

磁盘中对应数据库表的MYI（MyISAM_index）文件存放索引的B+树，MyISAM_data存数据

 select * from test_myisam where col1 = 49 ,那么其中实际的查找过程是从MYI中查找索引所在行的磁盘地址，进而在MYD中查找数据。

6 索引的其他知识

6.1 主键索引和普通索引

 主键索引（聚簇索引）

CREATE TABLE `test_innodb_primary_key` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 普通索引（非主键索引、二级索引），下表中使用普通联合索引

CREATE TABLE `test_innodb` (
  `Id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `score` bigint(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `compound_index` (`name`,`age`,`score`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

可见下图，左侧为主键索引，右图为普通联合索引。

主键索引data存储整行数据，普通索引存储的是主键的ID，需要二次回表（先查到数据的主键key，再根据主键获取其他字段信息） 

6.2 索引最左前缀原则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

CREATE TABLE `test_innodb` (
  `Id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `score` bigint(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `compound_index` (`name`,`age`,`score`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其原理是：索引是排序好的数据结构，根据联合索引查询时需要根据索引的排序规则进行查找，联合索引的前列索引必须保留。

举例分析：

 使用name、age作为搜索条件，进行EXPLAIN，发现执行结果type是ref。先参考type级别：

• system：系统表，少量数据，往往不需要进行磁盘IO
• const：常量连接
• eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
• ref：非主键非唯一索引等值扫描
• range：范围扫描
• index：索引树扫描
• ALL：全表扫描(full table scan)

对照阿里巴巴泰山版SQL性能优化的目标：（type）至少要达到 range 级别，要求是ref级别，如果可以是consts最好。

实际上，案例中的第一行的普通联合索引查找，是使用非主键索引查找，性能尚可。 

6.3 B-Tree和B+Tree的区别

1、双向指针

2、B-Tree每个索引都存放着data数据，以data数据为1kb为了，则每页数据只能存储16个索引数据，那么树的高度 16^x = n，此时查询复杂度（树高度）会比红黑树低一些，但相比B+Tree还是太复杂了。