mysql
2024-07-05 21:26:16 20 举报AI智能生成
mysql
作者其他创作
大纲/内容
mysiam和innoDB区别
mysiam不支持事务,行锁，外键,崩溃后无法安全恢复
innoDB对比mysiam写的效率差一些，并且占用更多的磁盘空间保存数据和索引,mysiam只缓存索引
针对数据统计有额外的数据存储 故而针对count(*)效率更高
mysql的几种日志
binlog
二进制日志，基本记录所有数据的修改，主要主从同步
row日志格式 清楚记录每一行数据被修改的情况 不会导致由于函数比如now()导致的时间不一致问题， 缺点批量操作会产生大量的日志
statment日志格式记录每一条修改数据的sql语句，批量修改时记录的不是单条sql语句而是批量修改的sql语句事件，主从复制时候会解析 master端执行过的sql,优点日志量小，减少磁盘io提升存储和恢复速度，缺点比如执行now()函数等会导致主从不一致
基于sqlserver
mixd日志格式以上两种模式的混合使用
redolog
mysql做了二阶段提交保证数据一致
记录数据修改后的记录顺序记录保证数据的持久性，记录的是数据页的变化物理变化是事务级别的操作方式
顺序io redolog就2个文件 如果满了，checkpoint如果没有持久化的就赶紧持久化
基于innodb的
undolog
mysql二阶段提交
开始事务更新数据写入redolog(prepare阶段)提交事务写入binlog和设置redoglog为commit标志
如果在写入redolog（prepare）时崩溃 此时是满足数据一致性的此时拿着事务id去binlog中去找没有找到则回滚，如果在binlog中找到则设置redolog为commit提交数据
当事务提交时先把 redolog写到操作系统的缓存区，并不会对redolog持久化，这种情况如果数据挂了，操作系统并没有挂，那么事务的持久新还是可以保证的
mysql的执行流程
1.客户端与服务端进行连接
2 查询缓存，查询到数据则返回记录（mysql 8.0已经删除）
3查询不到则解析器进行词法分析和语法分析生成语法树
4 预处理器进行校验表名和字段名
5，优化器来计算执行sql语句的成本,选择执行成本最低的
6 执行器则调用api来到存储引擎来检索相应的数据
什么是bufferpool
如何判断一个页是否在bufferpool缓存中 ，mysql 中有一个哈希表的数据结构，，使用表空间号和数据页作为key,缓冲页对应的控制快（新加载的数据页）作为value,如果查询不到则从free 链表中选取一个空闲的缓冲页，，然后把磁盘数据加载到对应的缓冲页
LRU链表
mysql改进了算法升级版的lru列表，将链表分为new和old两部分，加入元素时并不是从表头插入，而是从中间midpoint插入(就是从磁盘中新读出的数据会放在冷数据区的头部，如果数据很快被访问到)那么page 则会向new列表的头部移动,如果没有被访问则向old区尾部移动,等待淘汰
flush链表
对于有修改的加到脏页加到flush链表中mysql需要持久化就从flush链表中找到持久化到磁盘
mysql分库分表
为什么需要分库分表
分库
分库主要解决并发量的问题,因为并发量一旦上来,数据库就有可能成为瓶颈，因为数据库的连接是有限的，，通过增加数据库的实例来提高数据库的连接然后提交并发度
分表
主要是解决数据量大的问题，如果你的单表数据量比较大，，因为并发度不高那么数据连接可能还够，但是存储和查询遇到性能遇到瓶颈，需要分表
横向拆分（水平拆分）
把一张表的记录放到不同表中这就是横向拆分
纵向拆分（垂直拆分）
把一张表的多个字段拆分到多张表中则是纵向拆分
分库分表开源框架
jdbc直连层的
shardingsphere
jdbc应用层分片
分片规则分片逻辑 在内存中性能更高
proxy代理层的
mycat
分片规则和分片逻辑跨进程的
优点是跨语言
联表查询的优化点
注意关联字段使用索引
查询字段使用索引 不要select * 不然内存装不下
小表驱动大表
where ,on和having的使用场景区别
where子句在group by分组和聚合函数之前对数据进行过滤
having子句在 group by分组和聚合函数之后进行数据过滤
对于内连接 where子句和on 后面and条件查询一致
对于外连接 where是对连接后过滤 而on后面and条件还是返回全部左表 只对右表限制没有置为null
mysql数据如何在磁盘存储
mysql中数据存放在磁盘中是以数据页存储的一页默认大小为16kb
数据之间是通过双向链表进行连接,页之间页是通过链表进行链接的
mysql中四大特性
原子性
持久性
隔离性
一致性
mysql中索引的分类
聚簇索引
主键索引（就算没有主键id也会生成隐藏的字段）维护索引树,叶子节点挂着所有数据
普通索引
联合索引
唯一索引
索引中的值唯一的,可以允许为空值
索引原理与sql优化
什么是索引覆盖
查询的列在一颗索引树上就可以获取 无须进行回表操作
什么是回表
当使用非主键索引进行查询时，我们查询的列不在索引树上时，，根据索引树上查询到的主键值去主键索引树上获取完整的数据
什么是索引下推
mysql5.6 引入了索引下推,可以在索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录，减少回表次数，，在引擎层面可以解决的不交给server层，，尽量减少回表io
什么是索引跳跃
mysql8.0引入的优化，某种程度上可以无视最左前缀原则，，不过限制条件如下
select选择字段不能包含非索引字段
sql中不能带group by 和distinct语法
仅仅支持单表查询不支持多表查询
索引失效的场景
使用like关键字时以%开头会导致索引失效
数据类型转换失效
索引字段使用函数转换索引失效
索引列使用加减乘除
不符合最左前缀原则
如果数据库觉的全表扫描比使用索引更快也不会使用索引
or关键字使用必须都是索引列，否则有一个不是索引就会失效
慢sql优化
架构层面
单库有瓶颈则分库分表
主从读写分离
利用缓存
硬件方面
cpu,内存和磁盘的升级
表调优
建立索引，联合索引满足最左前缀原则
能使用覆盖索引的时候尽量使用覆盖索引
根据explain执行计划查看是否使用索引
严格约束字段长度
对于多插入使用批量插入
长的字段建立索引可以使用前缀索引
小表驱动大表
大量数据查询时可以使用分页查询
数据量大时进行表拆分
水平拆分
垂直拆分
开启慢查询日志 show variables like 'slow_query_log' 查看是否开启; 开启慢查询
set GLOBAL slow_query_log = on; 设置慢查询时间set long_query_time=0;
set GLOBAL slow_query_log = on; 设置慢查询时间set long_query_time=0;
explain关键字使用
type列
System
constant
利用主键和唯一键查看一行数据时
eq_ref
基于主键或唯一索引链接两个表，对于索引键值只有一条匹配，被驱动表的类型为 eq_ref
ref
关联查询时使用非唯一索引匹配，，简单查询时使用二级索引匹配返回所有匹配某个单行值
range
当使用范围扫描> between 时
index
扫描二级索引 如果 extra有using index表示使用的覆盖索引
all
全表扫描
id列
如果id序号相同,从上往下执行
如果id序号不同,序号大的先执行
如果两种都存在,先执行序号大的,然后再同级往下执行
如果为null,最后执行,表示结果集，，并且不需要使用它来查询
extra列
using index
使用非主键索引树就可以查询到所需要的数据，一般是覆盖索引，，不需要回表查询
using where
不通过索引查询所需要的数据
using index condition
表示查询列不被索引覆盖,where条件中是一个索引范围查找，过滤完需要回表找到所有符合的数据
using temporary
表示需要使用临时表来查询数据
using filesort
当查询中包含order by 操作而且无法利用索引完成排序操作
事务的隔离级别
读未提交(RU)
读已提交(RC)
rr 只是完全解决了快照读下的幻读
可重复读（RR）
序列化
MVCC（多版本并发控制）
读已提交每次执行sql查询时都会生成新的readview
可重复读执行第一条查询 sql时生成的readview并且事务结束前都不会发生变化
undolog版本链是指一行数据被多个事务依次修改后，在每个事务修改完成后,mysql 都会保留修改前的数据到undolog中，并且利用事务id和回滚指针两个隐藏字段把这些undolog进行串联起来形成一个历史版本链
readview的机制4个标志位
m_id 当前活跃事务id
如果行数据的最新undolog在当前活跃事务列表中那么一定读不到，因为活跃事务id都时readview生成时还没提交的事务id
min_trx_id m_id 里面的最小值
如果行数据的最新undolog事务id比当前活跃的最小的还要小表明该事务肯定提交了，一定读
max_trx_id 最大值 是创建readview时当前事务的最大id+1
如果行数据的最新undolog事务id比最大的事务id大说明这条事务在生成readview时还没开启肯定不读
creator_trx_id 当前事务id 每开启一个事务都会生成一个
如果行数据的最新undolog的事务id和当前事务id相等，说明时当前事务修改的肯定读
如果最终判断最新的undolog事务id读取不到数据，那么就根据版本链往上找，，然后对比过滤直到找到符合的数据
mysql锁
粒度上区分
全局锁
表锁
行锁
当我们对主键和唯一索引加锁时默认锁定的时一行也就是记录锁
模式分类
乐观锁
悲观锁
属性分类
共享锁
读锁
允许数据同时读阻止其它数据写
排它锁
写锁
阻止其它用户读取和更新数据
状态分类
意向共享锁
事务在给一行记录加共享锁时，必须先获取该表的意向共享锁
意向排它锁
事务在给一行记录加排它时，必须先获取该表的意向排它锁
算法分类
间隙锁
在普通索引或者唯一索引上锁定的是一个区间默认锁住的是对应一个索引的左右开范围
记录锁
记录锁锁的是表中某一条记录，记录锁出现的条件必须中精准命中，并且索引是唯一索引
临键锁
行锁加间隙锁 既包括了范围也包括了当前记录 当使用非唯一索引进行精准匹配时默认加了一个临键锁 左开右闭区间
回滚日志
0 条评论
下一页
