目录
1:mysql数据引擎
mysql引擎名字 | 特点 | 级别 | 引用范围 |
Innodb | 5.5版本之后的默认引擎,支持事务,行级锁,外键,支持索引 | 5.5之后默认 | 常见的应用,MVCC,多线程版本控制器 |
MYisam | 5.5版本之前默认引擎,不支持事务,行级锁,有表级锁,支持索引 | 5.5之前默认 | 查询速度快查询很快,修改锁表 |
NDB | 集群引擎,数据存储加载之后存储在内存中,访问速度快,支持索引 | 之间查找忒别快,耗费内存 | |
Memory | 存储引擎,表中的数据存储在内存中,断电重启,表里边的数据会消失,只能指出表级锁 | 并发性能差 | |
Archive | 存储引擎,只能支持insert和select操作。但是数据压缩比很高,大量数据不空间 | 适用于归档数据 |
2:mysql索引和算法
在分析mysql索引的前提下,我们假设现在存在一个用户表,主键是id递增,里边有10条数据,便于我们逐步分析
2.1:顺序查找和二分法查找(二分法查询有序的数据)
顺序查找:当我们我们查找某一个数字,假设1-10的数据,我们按照顺序查找概率是(n+1)/2;复杂度是0(n),有10条记录的,时间是5.5次平均值。
二分法查找:当我们查找数字6的时候,首先数中位数5,6比5大二分法查找后半部分,然后后半部分二分法查询第8位,6比8小,然后查询到6。速度想比较二分法有优势,有10条数据的情况下,查找平均次数是2.9
2.2:哈希索引
基于哈希表实现,只有精确匹配索引所有列的查询才有效,对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),并且Hash索引将所有的哈希码存储在索引中,同时在索引表中保存指向每个数据行的指针。
哈希数据结构索引
1:只有MEMORY存储引擎显示支持哈希索引。
2:哈希码在哈希表中是连续的,但是数据不一定连续。
3:哈希索引不支持部分索引列查找,因为哈希索引始终是使用索引列的全部内容来计算哈希码。如,在数据列(A,B)上建立哈希索引,如果查询只有数据列A,则无法使用该哈希索引
4:哈希只能支持到等值查询,包括=、IN()、<=>,不支持范围查询,如where price > 100
5:哈希还存在哈希冲突,(不同索引列会用相同的哈希码)会影响查询速度,此时需遍历索引中的行指针,逐行进行比较。
总结:哈希索引限制很多,只适用于一定的场合。而一旦适合哈希索引,它带来的性能提升将非常显著。
2.4:二叉树
二叉树具有以下性质:左子树的键值小于根的键值,右子树的键值大于根的键值。
首先我们看二叉树,将数据按照一下结构存储,
二叉查找树可以任意地构造,同样是2,3,5,6,7,8这六个数字,也可以按照下图的方式来构造:
但是这棵二叉树的查询效率就低了,这样的二叉树会退化为链表,导致性能退化,。因此若想二叉树的查询效率尽可能高,需要这棵二叉树是平衡的,从而引出新的定义——平衡二叉树,或称AVL树。
2.4:avl树
平衡二叉树满足二叉树的所有特点,但是满足任何节点的子树高度相差1.下面的两张图片,左边是AVL树,它的任何节点的两个子树的高度差<=1;右边的不是AVL树,其根节点的左子树高度为3,而右子树高度为1;
想要保证二叉树始终平衡,需要耗费性能,在二叉数上旋转,这样会耗费性能,并且二叉树的每一个节点只能存储一个数据,如果有一千万条数据,将索引维护成二叉树会导致树的层级高,并且查询连续的数据,会导致数据查询效率很低。
二叉树的旋转实例如下:
二叉树作为索引的缺点只要有四个:
1:维护二叉树的平衡性需要耗费性能
2:数据量很大,导致二叉树的层级变的很高,图例仅有7条数据
3:二叉树每个节点只能存储一个数据,图例只能存储7条数据
4:查询区间A-C的时候,还要多次遍历整个数据结构,导致性能变差
2.5:BTree
针对二叉树的缺点,我们想优化一个节点本身能多存储数据,这样就能将树变矮,并且根节点不单单只有两个节点,可有不止两条腿,可以有多条腿指向叶子节点。以此我们引进bTree
为了表述Btree,我们引进一条记录为一个二元数组[key, data] ,key记录键值,对应表中的主键,data对应主键外的实际的数据。
B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个3阶的B-Tree:
每个节点占用一个磁盘空间(电脑单次固定读取磁盘的大小为16KB,此处是计算机基础,非常关键),每个节点含有两个升序的排序的关键字和三个执行跟节点的指针,data存储id=17和35的数据。
假如我们查找29的过程:
1:读入磁盘1,17<29<35,通过P2指针指向磁盘3
2:读取磁盘3,26<29<30,通过p2指针指向磁盘8
3:读取磁盘8,在磁盘8的关键字中读取29
分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
为什么能够实现一个磁盘块(节点)有大量的数据呢?不是二叉树的一个数据,是因为,硬盘每次读取都是16Kb,这是计算机的硬件决定的,存储引擎的叫法是按叶读取,所以,每次的读取的一个叶就是一个节点,可以包含大量的数据。
Btree的缺点有两个:
1:虽然一次读取16Kb作为一节点,数据量很大,但是data部分包含的实际的数据,会导致16kb剩下的空间作为键key部分占用空间变小,导致数据量很大的时候,树的深度也不好控制
2:我们查找连续数据的时候,也要多次遍历整个树结构,比如查找3,5数据的时候,好要两次遍历树结构
2.6:B+Tree
通过上边的Btree我们感觉不错了呀,性能很牛逼了,但是Btree还有缺点的,此时我们引入B+Tree。
B+Tree相对于B-Tree有几点不同:
1:非叶子节点只存储键值信息(有效减少大数据量的树深度)。
2:所有叶子节点之间都有一个链指针(横向查询,减少反复遍历数据结构)。
3:数据记录都存放在叶子节点中(优化数据结构)。
将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:
B+tree的优点:
可能上面例子中只有22条数据记录,看不出B+Tree的优点,InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),那么一个节点(磁盘快)可以存储的(16*1024)/(8=8)=1024个键值。约1000的键值有500个指针,那么三层的结构就有500的三次方,大约1.25亿条数据的索引,却只有三层的数据构,是不是很厉害呢,效率也很好,足够用了。查询只需要三次IO操作。
问:为什么索引结构默认使用B-Tree,而不是hash,二叉树,红黑树?
hash:虽然可以快速定位,但是没有顺序,IO复杂度高。
二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。
红黑树:树的高度随着数据量增加而增加,IO代价高。
问:为什么官方建议使用自增长主键作为索引。
结合B+Tree的特点,自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。
插入连续的数据:
插入非连续的数据