目录
1、为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
2、为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
前言
什么是索引?
通俗的说就是为了提高查询效率专门设计的一种排好序的数据结构。
上边是数据,假设有个sql
select * from col2 where id = "5"
如果没有索引的话,需要进行全表扫描,走磁盘IO…
加上一个合适的索引,比如二叉树
Pre1 索引的数据结构选型
二叉树 ?
在线演示地址:
Binary Search Tree Visualization
Mysql 一般有主键自增、id之类的字段,演示二叉树存储自增数据时:
当进行查询时,
select * from t where id = 7
自增主键时,二叉树已经变为一个链表了。。。
想下,如果百万条数据,一个一个的进行对比,可想IO效率而知,(数据保存在磁盘中,从磁盘中捞呀!!)。
二叉树 pass,试试带有平衡功能的树。二叉平衡树(红黑树)
红黑树 ?
这样就不是一个链表了。
select * from t where id = 7
三次磁盘I/O即可找到, 比刚才二叉树的七次是少了些哈 ,自然查找效率也比二叉树高了
但是问题来了,如果百万条数据,那么这棵树异常庞大。
数据量大, 树高问题 - pass
那既然树高不好, 是不是如果可以控制树的高度(比如 3 到4层的高度,这样查询起来还能接受),让每一层能存储更多的数据,然后再分裂,这样的话数据量相乘起来,也是不少了对吧,这样就能存储更多的数据,这样会不会好一点? ----> B-Tree
B-Tree ?
- 叶节点具有相同的深度, 叶节点之间指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
叶子节点之间的没有指针,区别于B+树。
data存储的是数据对应的磁盘地址, k-v结构。
我们来看下B-Tree的插入 (Max.Degree 设置为3 即 元素到了3个就分裂 )
查找一下
3次
MySQL也没有使用B-Tree , 因为
除了存储索引以外,还存储了data(数据对应的磁盘地址) , 为了更多的存储数据,MySQL对B-Tree进行了很多改造
由此演进出了 B+Tree ,将data部分仅保留在叶子节点上,这样的话同等的页可以存储更多而索引数据。
B+Tree
- 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接,提高区间访问的性能
数据仅存储在叶子节点, data可能是磁盘地址也可能是其他的列数据,这个和存储引擎有关系。
叶子节点之间有指针相连。
我们来算下 3层高的B+Tree能存储多少数据结构
假设是BigInt类型的数据
BigInt 占 8个字节 ,同时还是用6个字节存储了它指向的数据的物理地址
MySQL在使用innodb引擎的时候页大小默认是16K ,查询如下
mysql> SHOW GLOBAL STATUS like 'Innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
假设 树高为3 , 这样的话,第一层即可以存储 16KB * 1024 / (8B + 6B) = 1170
同样的第二层也是1170 (第二层不是叶子结点,不存储数据)
第三层,存储数据,一般情况下一行数据的大小肯定不会超过1KB,那我们就按照1KB算吧
3层高的B+Tree , 存储BitInt可以存储 1170 * 1170 * 16 = 2千1 百万。。。。这效率还是可以的哈
想一想 如果是4层高的数 1170 * 1170 * 1170 * 16 = 250多亿数据。.。。。
当然了 都是估算, 如果换成其他类型的数据,每个表的行数据的大小都是相关的,这也就是我们通常说的 MySQL的表到千万级别就要分库分表的理论依据了。
我们看下B+Tree的插入和查找
Hash表
- 对索引的key进行一次hash计算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+ 树索引更高效
- 仅能满足 “=”,“IN”,不支持范围查询
- hash冲突问题
对索引字段进行hash以后, 还存储了数据对应的磁盘地址。
一般情况下,hash 比 b+tree的效率要高 ,但工作中绝大部分还是使用的B+Tree , 因为hash对范围查找不是很友好,还要全表扫描。
为啥B+Tree 支持范围查找?
我们知道B+Tree的叶子节点 有指针相连,从根节点找到对应的叶子节点后, 加上节点本身就是排好序的,所以范围查找就恨轻松了。
B-Tree 没有指针相连,所以要想范围查找,还得从根节点重新找,效率肯定比B+树低 。
Pre2 MySQL不同存储引擎下索引的实现
主要讨论InnoDB和MyISAM两个存储引擎的索引实现方式。
一、MyISAM索引实现
我们建立一个myIsam存储引擎的表,看磁盘上的文件存储如下
我这个是8.0的MYSQL, 5.7版本 不是sdi结尾的文件,而是frm (framework)
可以看到MyISAM存储引擎的索引文件 MYI 和数据文件 MYD 是分离的(非聚集)
这就是非聚簇索引的含义, MYI 和 MYD 分开存储 ,同样的 InnoDB都存在.idb文件中,所以InnoDB存储引擎的索引就是聚簇索引。
索引原理图
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
上图就是 MyISAM索引的原理图。
上图一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址
在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。
如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:
同样也是一颗B+Tree,data域保存数据记录的地址。
因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,去另外一个文件中MYD读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。
二、InnoDB索引实现
建立一个innodb存储引擎的表,看磁盘上的数据文件如下
这个ibd就是 数据和索引,这两个存储在一个文件中。
第一个重大区别是InnoDB的数据文件本身就是索引文件 ,因为就只有一个ibd文件啊。
-
MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
-
InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM 不同。
索引原理图
上图就是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域
上图为 定义在Col3上的一个辅助索引 观察叶子节点 : data域存储相应记录主键的值而不是地址
Col3字段上的索引,以英文字符的ASCII码作为比较准则。
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
Final:常见面试题
1、为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
至于是整型,主要是构建B+Tree的时候,从左到右递增的属性,你如果用过UUID,不仅占用空间,还要转换成assic码进行比较,效率自然不行。
2、为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大,占用空间。
再比如用非单调(可重复)的字段作为主键在InnoDB中是不推荐的,因为InnoDB数据文件本身是一颗B+Tree,可重复的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,所以推荐使用自增主键。
这是我看过的最清晰易懂的文章了,赶紧记录保存一下。