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