数据库存储引擎
概述
MySQL中的数据用各种不同的技术存储在文件中
每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力
存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
MySQL数据库中的组件，负责执行实际的数据/O操作
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储
MyISAM
MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的
访问速度快，对事务完整性没有要求
MyISAM适合查询、插入为主的应用
MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为
.frm文件存储表结构的定义
数据文件的扩展名为.MYD (MYData)
索引文件的扩展名是.MYI (MYIndex)
表级锁定形式，数据在更新时锁定整个表
数据库在读写过程中相互阻塞
会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
数据单独写入或读取，速度过程较快且占用资源相对少
MyISAM适用的生产场景举例
无事务
单独读或者写数据量比较多
并发访问相对较低
修改较少
数据一致性要求较低
服务硬件资源差
InnoDB
支持事务，支持4个事务隔离级别
MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
读写阻塞与事务隔离级别相关
能非常高效的缓存索引和数据
表与主键以簇的方式存储
支持分区、表空间，类似oracle数据库
支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
对硬件资源要求还是比较高的场合
行级锁定，但是全表扫描仍然会是表级锁定
InnoDB适用生产场景分析
业务需要事务的支持
行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
业务数据更新较为频繁的场景
业务数据一致性要求较高
硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘I0的压力
InnoDB的底层存储结构为B+树
B+树的每个节点都对应InnoDB的一个page
page的大小是固定的,一般设置为16kb
其中非叶子节点只有键值,叶子结点包含完整的数据
Mysql 索引原理
基本原理
索引是一种数据结构。以协助快速查询、 更新数据库表中数据
把创建索引列的内容进行排序
对排序的结果生成倒排表
在倒排表内容上拼接上数据行地址
查询数据时，先拿到倒排表内容，在取出数据行地址，从而拿到具体的数据
索引类型
逻辑维度
主键索引(primay key)
普通(Normal) 非唯一索引
联合索引 多个字段创建的索引
唯一 (Unique) 索引列中的值必须是唯一的
空间索引
数据结构维度
B+树索引
所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询
R-Tree索引
用来对GIS数据类型创建SPATIAL索引
物理存储维度
聚集索引 B+树结构
聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据
优点
查询通过聚集索引可以直接获取数据，相比非聚集索引需要第二次查询（非覆盖索引情况下）效率高
对范围查询效率很高，因为数据是按照大小排列的
适合排序的场景，非聚集索引不适合
缺点
维护索引代价很高，特别插入新行或者更新主键导致导致页的分裂
如果主键是随机ID（比如UUID）,导致存储稀疏（磁盘碎片），可能比全表扫描还慢
或者主键比较大，导致辅助索引变的很大（节点占用更多的物理空间），这也是建议自增id作为主键的根本原因
非聚集索引 B+树结构
非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列
推演索引
二分查找
有序数组 适合存储静态 index变动
哈希结构 等值查询 无法范围
二叉查找树
流程
每个结点最多两个子树，分别称为左子树和右子树
左子节点的值小于当前节点的值，当前节点值小于右子节点值
顶端的节点称为根节点，没有子节点的节点值称为叶子节点
查找耗时是和这棵树的深度相关,可能会造成倾斜
平衡二叉树
任何节点的两个子树高度最大差为1
不会出现特殊化一个链表
平衡二叉树插入或者更新时，需要左旋右旋维持平衡，维护代价大
如果数量多的话，树的高度会很高。因为数据是存在磁盘的，以它作为索引结构
每次从磁盘读取一个节点，操作IO的次数就多，消耗的时间就越多
多路平衡查找树 (B树)
B树在枝节点和叶子节点存储键值、数据地址、节点引用
分叉数（路数）永远比关键字数多1
B Tree在做检索时，检索效率非常高
但是在做数据插入和删除时，会破坏B Three本身的平衡
以为了保持B Tree的平衡，需要对节点进行分裂、合并、转移等操作
节点的分裂和合并，其实就是InnoDB页的分裂和合并
如果索引键值有序，写满一页接着开辟一个新的页
如果索引键值无序，存储过程造成大量磁盘碎片,带来频繁的page分裂和合并
而这个操作在节点数量较多的情况下性能影响较大
B树相对于平衡二叉树，就可以存储更多的数据，高度更低
B+Tree
B+树非叶子节点上是不存储数据的,仅存储键值
而B树节点中不仅存储键值，也会存储数据
innodb中页的默认大小是16KB,如果不存储数据，那么就会存储更多的键值
相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖
如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快
B+树索引的所有数据均存储在叶子节点
而且数据是按照顺序排列的，链表连着的
那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单
总结,InnoDB中的B+Tree索引带来的优势
它是BTree的变种，BTree能解决的问题，它都能解决
B Tree解决的两大问题 ：每个节点存储更多关键字；路数更多
扫库、扫表能力更强
如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵B+Tree拿到所有的数据
B+Tree的磁盘读写能力相对于B Tree来说更强
根节点和枝节点不保存数据区, 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多
排序能力更强
因为叶子节点上有下一个数据区的指针，数据形成了链表
效率更加稳定
永远是在叶子节点拿到数据，所以I0次数是稳定的
同样索引带来的问题或者缺点
会降低插入、删除、更新表的速度，因为执行写操作时，还有操作索引文件
索引需要占用物理空间，除了数据表占数据空间之外,每个索引还要占一定的物理空间
如何要建立聚集索引，需要的空间更大，如何非聚集索引很多
一旦聚集索引改变，所有的非聚集索引都会跟着变动。所以建议不要随便改变主键值
B+Tree作为索引的数据结构带来的好处
在B+ Tree中，每个节点不存存储数据区，只需要存储键值+指针，使得B+ Tree在每个节点存储的路数更多
一个Page页（一个节点）可以存储大量键值+指针
深度是3的时候,两个叶子结点 共32个字节
InnoDB 索引存储(.frm和.ibd)
聚集索引（聚簇索引）
索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的
InnoDB组织数据的方式就是（聚集）索引组织表
如果说一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序
主键索引就是聚集索引,其他的索引统一叫做"二级索引”二级索引存储的是二级索引的键值
在二级索引的叶子节点找到，然后拿到主键值,然后再到主键索引的叶子节点拿到数据
主键索引比二级索引少扫描了一棵B+Tree （避免了回表） 它的速度相对会快一些
覆盖索引
覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果
覆盖索引
覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果
索引下推
可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
创建索引的原则
选择唯一索引
唯一性索引一般基于哈希算法实现,可以快速,唯一定位某条数据
为经常需要排序,分组和联合操作的字段建立索引
为经常作为查询条件的字段建立索引
限制索引数量
索引越多,数据更新表越慢
因为在进行数据更新时会不断计算和添加索引
尽量使用数据量少的索引
如果索引的值很长,则占用的磁盘较大,查询速度会受到影响
尽量使用前缀来索引
如果索引字段的值很长,则不但影响索引的大小.而且会降低索引的执行效率
这时需要使用字段的部分前缀来作为索引
创建前缀索引 prefix_length 判断区分度值,越高越好
删除不再使用或者很少使用的索引
尽量选择区分度高的列作为索引
区分度表示字段值不重复的比例
数据库三大范式
每列都是不可再分的最小数据单位,确保每列的原子性
表中非主键列不存在对主键的部分依赖,每个表都只描述一件事情
表中的列不存在对非主键列的传递依赖
存储过程
存储过程指一组用于完成特定功能的SQL语句集,它被存在数据库中
经过第一次编译后再次调用时不需要被再次编译
用户通过指定存储过程的名称并给出参数来执行它
存储过程是数据库中的一个重要对象
我们通过基于存储过程快速完成复杂的计算操作
常见优化思路
及来那个利用SQL语句代替一些小循环,如聚合函数,求平均函数等\
中间结果被存放于临时表,并加索引
少使用游标
事务越短越好
try catch处理异常
尽量不要将查询语句放入循环中,防止过度消耗系统资源
数据库的并发操作和锁
数据库的并发策略
乐观锁
乐观锁在读数据时,认为其他用户不会去写起所读的数据
悲观锁
在修改某条数据时,不允许其他用户读取改数据,知道整个事务都提交,才可访问该数据
悲观锁又可分为排它锁(写锁)和共享锁(读锁)
时间戳
数据库表中额外加一个时间戳TimeStamp
每次读数据时都把时间戳也读出来,再更新数据时就+1
更新提交之前跟数据库的该字段比较一次,如果比数据库的值大就允许保存,否则不允许保存
数据库锁
表级锁
对当前操作的整张表加锁
MyISAM与InnoDB都支持表级锁
页级锁
粒度介于行级锁也表级锁之间
表级锁加锁速度快,但冲突多,行级锁冲突少,但加锁速度慢,页级锁在二者之间做了平衡
数据库的分库分表
垂直切分
将表按照功能模块,关系密切的程度划分到不同的库中
水平切分
将该表的数据按照某种规则进行划分,然后存储到多个具有相同结构的数据库上
数据库事务
原子性
事务是一个完整的操作,要么全部执行,要么都不执行
持久性
在事务操作完成后,对数据的修改都将被持久化都永久存储中
隔离性
对数据进行修改的所有并发事务都是彼此隔离的,不存在任何方式依赖或影响其他事务
本地事务
本地事务基于数据库资源实现
事务串行的在JDBC连接上执行
本地事务将事务处理局限在当前事务资源内
其特性是使用灵活,但无法支持多数据源的事务操作
分布式事务
提供了跨数据库的分布式事务操作的数据一致性
数据库的一致性包含同一类型的多个数据库实例服务的一致性,和多个不同类型数据库的数据一致性
Java事务编程接口 JTA和java事务服务为J2EE平台提供了分布式事务服务
分布式事务
资源管理器
一个或多个支持XA协议(分布式事务的规范,主要定义事务管理器和资源管理器的接口)
负责不同数据库的具体事务执行操作
CAP
原理
一致性 Consistency
分布式系统中所有数据备份中,同一时刻是否有同样的值
等同于所有节点都访问同一最新的数据副本
可用性 Availability
在集群中一部分节点发生故障后，集群整体能否响应客户端的读写请求
对数据更具备高可用性
分区容错性 Partition tolerance
系统如果不能在时限内达成数据的一致性,就意味着发生了分区
必须就当前操作在C和A之间做出选择
就实际效果而言,分区相当于对通信实现要求
分区
因为网络故障或者其他原因导致分布式系统中的部分节点与其他节点失去连接,形成独立分区
在集群出现分区时,整个系统也能对外提供服务
模式
AP模式
各自事务分别执行和提交,允许出现结果不一样,然后采取你补措施恢复数据即可,实现最终一致性
CP模式
各自事务执行后互相等待,同时提交,同时回滚,达成强一致,但是事务等待过程中处于弱一致性状态
两阶段提交
分布式事务涉及操作多个数据库的事务
在分布式系统中,各个节点之间在物理上相互独立,通过网络进行沟通和协调
两段提交指在计算机网络及数据库领域内,为了使分布式数据库的所有节点在进行事务提交时都保持一致性而设计的一种算法
分布式系统中,每个节点虽然都可以知道自己操作是否成功,但是无法知道其他节点是否成功
在一个事务中跨越多个节点时,为了保持事务的ACID特性,需要引入一个作为协调者的组件来统一掌握所有节点的操作结果
并最终确定这些节点是否真正提交操作结果
因此两段提交的算法思路可以包括
参与者将操作成败通知协调者
再由协调者根据所有参与者的反馈决定各参与者时提交操作还是中止操作
具体阶段
两段提交流程
Prepare(准备阶段)
事务协调者(事务管理器)给每个参与者(源管理器)都发送Prepare消息
每个参与者要么直接返回失败,要么在本地执行事务
写本地的redo和undo日志但不提交,是一种万事俱备只欠东风的状态
Commit(提交阶段)
如果协调者接收到了参与者的失败消息或者等待超时
则直接给每个参与者都发送回滚消息,否则发送提交消息
参与者根据协调者的指令执行提交或者回滚操作
释放在所有事务处理过程中使用的锁资源
两段提交的缺点
同步阻塞问题
在执行过程中,所有参与者的任务都是阻塞执行的
数据不一致
在两阶段提交的第二阶段.在协调者向参与者发送Commit请求后发生了局部网络异常
或者在发送commit请求过程中协调者发生了故障
导致只有一部分参与者接收到了Commit请求
于是整个分布式系统出现了数据不一致的现象也称为脑裂
在协调者宕机后事务状态丢失
协调者在发出Commit消息之后宕机
唯一接收到这条消息的参与者也宕机
即使协调者通过选举协议产生了新的协调者,这条事务的状态也是不确定的
没有人知道事务是否已被提交
三段提交
原理
引入超时机制
在协调者与参与者中引入超时机制
如果协调者长时间接收不到参与者的反馈,则认为参与者执行失败
在第一阶段和第二阶段都加入一个预准备阶段
以保证最后的任务提交之前各节点的状态是一致的
分为Cancommit,PrecCommit.Docommit三个阶段
流程
准备提交 Cancommit
协调者向参与者发送Commit请求,参与者如果可以提交,就返回YES响应,否则NO
预提交Precommit
协调者根据参与者的反馈来决定是否继续进行,如果一下两种可能
都是Yes响应,就预执行事务
任意No响应,或者超时,则执行事务中断
提交DoCommit
在该阶进行真正的事务提交
协调者发送提交请求,参与者提交事务,参与者响应反馈
柔性事务
基于CAP理论及BASE理论,提出了柔性事务的概念
基本可用(Basically Available)
分布式系统在出现故障时,允许损失部分可用性,即保证核心可以
柔性状态(SoftState)
在一段时间内,允许出现中间状态,比如临时不一致状态
最终一致性(Eventual Consistency)
虽然不能保证强一致性,但是柔性状态结束后,最终达到数据一致
BASE理论是CAP理论的延伸
柔性事务通常分为
两阶段型
指分布式事务的两段提交,对应技术上XA和JTA/JTS
是分布式环境下事务处理的典型模式
补偿型
基于补偿的事务处理模型,通过补偿操作对事务做一致性处理
TCC事务模型,牺牲了一定的隔离性和一致性,但提高了事务的可用性
异步确保型
将一系列同步的事务操作修改为基于消息队列异步执行的操作
来避免分布式事务中同步阻塞导致的数据操作性能下降
最大努力通知型
通过消息中间件实现的
与异步不同的是
在消息由消息服务器发送到消费者之后
允许在达到最大重试次数之后正常结束事务
因此无法保障数据的最终一致性
Seata分布式事务中间件
组件
TC 事务协调管理器
维护全局和分支事务的状态,协调全局事务提交或者回滚
TM事务管理器
定义全局事务的范围,开始全局事务,提交或回滚事务
RM资源管理器
管理分支事务处理的资源,与TC交谈以注册分支事务和报告分支事务的状态，并驱动分支事务提交或回滚
模式
XA模式
强一致性分阶段事务模式，牺牲了一定的可用性，无业务侵入
AT模式
最终一致的分阶段事务模式，无业务侵入，也是Seata的默认模式
SQL优化
通过查询慢日志定位那些执行效率较低的SQL语句
explain分析SQL的执行计划
type 由上至下,效率越来越高
ALL:全表扫描
index索引全扫描
range索引范围扫描,常用语<、<=、>=、between、in等操作
ref
非使用唯一索引扫描或唯一索引前缀扫描
返回单条记录,常出现在关联查询中
eq_ref
类似于ref,区别在于使用的是唯一索引,使用主键的关联查询
const/system
单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询
null
Mysql不访问任何表和索引,直接返回结果
Extra
Using filesort
MySQL需要额外的一次传递,以找出如何按排序顺序检索行
通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序
然后关键字被排序，并按排序顺序检索行
Using temporary
使用了临时表保存中间结果，性能特别差，需要重点优化
Using index
表示相应的 select 操作中使用了覆盖索引（Coveing Index）
避免访问了表的数据行，效率不错！如果同时出现 using where
意味着无法直接通过索引查找来查询到符合条件的数据
Using index condition
MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推）
在存储引擎层进行数据过滤，而不是在服务层过滤
利用索引现有的数据减少回表的数据
show profile 分析
了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”
trace
trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划
确定问题并采用相应的措施
优化索引
优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
改用其他实现方式：ES、数仓等
数据碎片处理
场景分析
最左匹配
查询索引从左往右匹配,调换查询字段,或者调换索引位置
隐式转换
相当于在索引上做运算,会让索引失效
字符类型,使用了数字,则会做隐式替换,导致索引失效
大分页
把上一次的最后一条数据，也即上面的c传过来，然后做“c < xxx”处理
采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果
范围查询索引失效
范围查询阻断，后续字段不能走索引
把需要范围查询的字段放在最后
避免使用非快速索引
不等于、不包含不能用到索引的快速搜索
优化器选择索引失效
如果要求访问的数据量很小，则优化器还是会选择辅助索引
但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右）
优化器会选择通过聚集索引来查找数据
复杂查询
如果是统计某些数据，可能改用数仓进行解决
如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了
而是采用其他的方式进行解决，比如使用ES等进行解决
asc和desc混用
desc 和asc混用时会导致索引失效
大数据
对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存
那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理
MySQL原理
客户端
可以是我们常用的MySQL命令行窗口，或者是Java的客户端程序等
Server层
连接器、查询缓存、分析器、优化器和执行器等
大部分MySQL对用户提供的功能都在这一层实现，包括了内置函数的实现，存储过程、触发器、视图等
存储层
存储引擎层负责数据的存储和提取，存储引擎的实现是插件式的
也就是说用户可以选择自己所需要的存储引擎，如InnoDB、MyISAM等
连接器
连接器是MySQL服务端对外的门户
当我们使用命令行黑窗口或者JDBC的Connection.connect()，连接到MySQL Server端时
会校验用户名和密码；然后会查询用户对应的权限列表
当连接建立后，后续的权限范围就在此时确定了
如果连接没有断开的情况下,更改了用户的权限，此时对于该连接也不生效
查询缓存
当连接建立完成后，执行select 语句的时候，就会来到查询缓存
MySQL会将Select 语句为 KEY，将查询结果为VALUE 的形式保存在内存中
如果匹配到对应的 KEY 就会直接从内存中返回结果
但是常我们不会使用MySQL自身的查询缓存，因为当有一条Update 或 Insert 的改表语句时
就会清空对该表的所有查询缓存。缓存的粒度比较大
可以考虑类似 Redis 的分布式缓存做业务数据的缓存
在MySQL 8.0 中，查询缓存直接被移除了
分析器
如果在查询缓存中没有查到数据，就要真正的开始执行SQL语句了
分析器首先会做“词法分析
词法分析就是识别上面字符串，id、name 是表的字段名，T 是表的名称
之后就是语法分析，如果SQL有语法错误，在此时就会报错
优化器
当分析器处理过之后，MySQL就知道SQL 要干什么了，但是此时还需要优化器对待执行的SQL 进行优化
当然MySQL 提供的优化器，相比其他几款商用收费的数据库来说还是比较弱的
当然MySQL 的优化器还是可以对 join 操作，表达式计算等等进行优化
执行器
执行阶段，首先会检查当前用户有没有权限操作该 SQL 语句。如果有，则继续执行后续的操作
流程总结
询语句的执行流程如下
权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎
更新语句执行流程如下
分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit 状态)
biglog
用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中
可以简单理解为记录的就是sql语句
binlog 是 mysql 的逻辑日志，并且由 Server层进行记录
使用任何存储引擎的 mysql 数据库都会记录 binlog 日志
主要使用场景
用于主从复制，在主从结构中，binlog 作为操作记录从 master 被发送到 slave，slave服务器从 master 接收到的日志保存到 relay log 中
用于数据备份，在数据库备份文件生成后，binlog保存了数据库备份后的详细信息，以便下一次备份能从备份点开始
