从来没有思考过MySql的架构,了解了一下看来,MySql的架构是挺简洁的。
下图已经非常清楚,比较值得注意的就是存储引擎的选择,不通过存储引擎的设计不同,导致影响数据表存取的并发性能、存取方式。
此外,该架构中的典型设计。
- 连接池,管理连接,由于这类连接通常是资源消耗较大的,而单一实例又完全不够用,所以一般是有一个连接池,可以看成空闲队列,资源池
- 缓存,内存管理中典型的cache架构,适用于速度不匹配的设备之间的加速
- 抽象与分层
索引
2020年11月9日
14:09
索引是一种优化查找速度的经典思想。索引本质上是一个精心设计的数据结构的目录,甚至可以说本质上就是一种数据结构。那么,问题是,索引这种数据结构为什么能够让查找速度变快呢?
我们来考虑一个日常的例子,索引无处不在,例如书和目录。
有没有想过,为什么目录能够快速查找?明明同样对应整本书,原因是目录压缩了信息,原本每一章的信息都是不一样长度,而在目录看来,每个章节都是一个相同的节点。
以上其实是索引的朴素思想,信息整理并压缩到一个目录中。我们会期望内存能够加载进一个目录,使我们能够快速定位所有数据。但事实上,当数据量变得非常大的时候,我们索引的节点数量也最终会大到同样尴尬的地步——只能放在硬盘上,查找索引本身太过耗时。
不难想到,线性结构组织索引的时间消耗为O(N),如果是利用树,那么我们可以期待O(logN)的时间消耗,考虑数据有序,那么平衡树可以做到,结合二分查找,两种方法可以加快搜索过程。
总结来说,索引之所以有用:
- 信息压缩
- 信息组织
B-Tree和B+Tree
2020年11月9日
14:06
B-Tree和B+Tree的特点是叶子节点非常大,而索引节点很小,这种特性被广泛用于IO相关的数据索引中——因为索引节点集中,方便加载进内存。
B-Tree
B-Tree是平衡二叉树的一种变种,它是一种多路平衡树。如下图,它看起来是非常好理解的,主要需要关注它的特点,以及插入删除的自平衡过程。
特点:
- 平衡二叉树节点最多有两个子树,而 B 树每个节点可以有多个子树,M 借 B 树表示该树每个节点最多有 M 个子树
- 平衡二叉树每个节点只有一个数据和两个指向孩子的指针,而 B 树每个中间节点有 k-1 个关键字(可以理解为数据)和 k 个子树( **k 介于阶数 M 和 M/2 之间,M/2 向上取整)
- B 树的所有叶子节点都在同一层,并且叶子节点只有关键字,指向孩子的指针为 null
来自 <
https://juejin.im/entry/6844903613915987975>
插入
无法插入gif比较可惜,自行去这个网站选择M=5,手动插入1-30的数据,应该就能非常好地获得intuition。
https://www.cs.usfca.edu/~galles/visualization/BTree.html
总体来说,B-Tree的插入的基本方式就是,上溢则分裂,递归执行。
ref: https://www.xuetangx.com/learn/THU08091002048/
删除
同上,还是去可视化网站上删除一个序列的节点,获得直观感受。事实上,平衡树的删除都比较复杂(操作细节多),但是并不难(理解后很简单)。本质上,所有平衡树的插入删除的核心操作都是:分裂,合并,协调兄弟父母节点。
删除操作是指,根据key删除记录,如果B树中的记录中不存对应key的记录,则删除失败。
1)如果当前需要删除的key位于非叶子结点上,则用后继key(这里的后继key均指后继记录的意思)覆盖要删除的key,然后在后继key所在的子支中删除该后继key。此时后继key一定位于叶子结点上,这个过程和二叉搜索树删除结点的方式类似。删除这个记录后执行第2步
2)该结点key个数大于等于Math.ceil(m/2)-1,结束删除操作,否则执行第3步。
3)如果兄弟结点key个数大于Math.ceil(m/2)-1,则父结点中的key下移到该结点,兄弟结点中的一个key上移,删除操作结束。
否则,将父结点中的key下移与当前结点及它的兄弟结点中的key合并,形成一个新的结点。原父结点中的key的两个孩子指针就变成了一个孩子指针,指向这个新结点。然后当前结点的指针指向父结点,重复上第2步。
有些结点它可能既有左兄弟,又有右兄弟,那么我们任意选择一个兄弟结点进行操作即可。
来自 <
https://www.cnblogs.com/nullzx/p/8729425.html>
B+Tree
B-Tree有一个显而易见的缺点:索引节点中直接包含键值(数据),在数据量很多的情况下,会让索引节点变得更大,IO次数变多。于是B+Tree改进了B-Tree,让所有数据放到叶节点上。索引节点只做索引,不再保存数据。如图,同样非常直观。
B-Tree的插入和删除和B+树非常像,后者的删除要稍微复杂一些。同理。
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
B+Tree直观的理解就是,将最后一行看作硬盘,而其他节点全在内存中——这就是索引的朴素形态。当然,事实是,索引节点无需一次加载。
Sql Explain性能分析工具
2020年11月9日
14:32
用法: Explain+SQL语句。
用于分析建立索引后SQL的性能分析。
select_type属性
select_type属性 | 含义 |
SIMPLE | 简单的 select 查询,查询中不包含子查询或者UNION |
PRIMARY | 查询中若包含任何复杂的子部分,最外层查询则被标记为Primary |
DERIVED | 在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。 |
SUBQUERY | 在SELECT或WHERE列表中包含了子查询 |
DEPEDENT SUBQUERY | 在SELECT或WHERE列表中包含了子查询,子查询基于外层 |
UNCACHEABLE SUBQUERY | 无法使用缓存的子查询 |
UNION | 若第二个SELECT出现在UNION之后,则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED |
UNION RESULT | 从UNION表获取结果的SELECT |
type
查询访问的类型,重要指标,指示出索引是否发挥作用。
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
其中const是常数性能,eq_ref是索引后唯一,ref是索引+遍历混合,一般需要这个等级的type才比较高效。后面的,range是指定了范围,例如between,ALL是原始遍历。
possible_keys,key,key, key_len, rows
- possible_keys,指示出相关的所有索引,但只会使用最快的,所以需要从后面几个关键属性中分析。
- key,实际使用的索引
- key_len,实际使用索引长度
- rows,实际查询行数
extra
其他重要信息,例如使用了where,group by,join等等。
Sql索引匹配策略
2020年11月9日
14:50
由于只在部分属性上建立索引,随之而来的问题就是,多个属性的索引的匹配顺序,类型转换,通配符匹配等等情况下的索引匹配策略。
不适合建立索引的情况
- 记录太少,没有必要
- 增删多,查询少,维护索引每个操作都需要logN
- where条件里用不到的属性不必要索引
- 过滤性不好的属性不需要索引,例如男女,数据无区分度
索引匹配策略
开头提到的诸多比较复杂的场景,MySql中其实是一刀切,选了非常简单的策略。
- 按建立索引时的属性顺序进行匹配,分层索引。建立索引时的顺序很重要,因为MySql中的匹配顺序就是按照该顺序进行最大匹配的,若是中间有属性没匹配到,则后面的索引不会生效
- 对建立索引的属性的所有操作,都会导致索引失效,例如类型转换,函数调用
- 对某个索引的属性进行范围操作,将使得后边的索引失效
- 不等于,not in将使得索引失效
- 通配符无法使用索引,索引尽量把通配符写在字符串后面,索引会匹配到通配符为止。
- or无法使用索引,应该使用union或union all代替,ps,union all是无去重的版本,但是速度快很多,如果知道不会有重复,应该使用后者
- 尽量覆盖索引,不要select *