Mysql
2021-02-25 18:22:49 4 举报
AI智能生成
登录查看完整内容
mysql基本原理总结
作者其他创作
大纲/内容
Mysql
基本架构
查询缓存
相对一般业务来说弊大于利,每个更新操作都会将整个表的缓存清除
5.6之后默认关闭,8.0之后完全移除
基本流程
select
1.连接器收到客户端请求建立连接,并检查是否有执行权限
2.分析器对语法分析
3font color=\"#c41230\
4.执行器根据优化器结果,调用引擎接口,获取第一条引擎认为符合条件的,执行器判断,继续取下一行
update
查询步骤同上,执行器拿到符合条件的行后,进行更新,调用引擎写接口
order
如果要求对当前使用的索引列进行排序,从引擎拿到的就是有序的
可以直接返回给客户端(一种优化手段:合理建立索引)
如果引擎返回到执行器的数据无序
排序类型
快速排序
1. 从表中获取满足WHERE条件的记录
4. 循环执行上述过程,直到所有满足条件的记录全部参与排序
优化排序
font color=\"#c41230\
max_length_for_sort_data只有当排序元组小于max_length_for_sort_data时,才能利用优化排序方式,否则只能用常规排序方式
优化队列排序
limit场景使用(堆排序算法)
可以适当调大sort buffer,优化排序性能
group
如果从引擎拿到的有序,遍历一遍结果即可得到结果
所以可建立适当索引进行优化
如果从引擎拿到的无序,需要用到临时表(小则内存表,大则磁盘表),将group by的列当作唯一键,并对结果进行排序返回,如果不需要排序,可以order by null 免于排序直接返回
count
由于MVCC机制,同时存在的每个session可能返回不同的结果,所以不能像Myisam那样直接记录整表数量直接返回
count(*)
innodb对此进行了优化,不取值,肯定不为null,按行累加
优先使用len最小的索引累加
count(1)
会被自动转换为count(*)
count(主键)
count(字段)
会取出字段列进行非空比较
如果不允许为null,直接按行累加
如果允许为null,需要先判断是否为null,对非null行累加
性能
count(*) > count(1) > count(主键) > count(字段)
join
join字段有索引
Index Nested-Loop Join(NLJ)
从驱动表取出一行数据,直接利用匹配表的索引树font color=\"#8a8a8a\
Batched Key Access(BKA)
依赖于Multi Range Read(MRR),回表的时候先做排序,通过将随机读,转换为尽量顺序读
对NLJ方案进行的优化,默认开启,从驱动表取出一批数据,放到join buffer,到匹配表进行MRR连接
join字段无索引
Block Nested-Loop Join(BNL)
从驱动表取出符合条件的行,放到join buffer,取出join列,到被驱动表进行全表匹配,如果buffer放不下,需要分多次进行(Block名字由来,分块)
优化
构建适当索引,尽量走BKA方案
如果为冷查询,并且可以过滤掉很多数据,建索引有点浪费,可以考虑使用临时表,将数据插入到临时表中,建立索引进行连接
原则
尽量选取小表作驱动表
可以适当调节join buffer大小优化速度
索引
优缺点
优点
1. 减少服务器对数据的大量扫描
2. 将随机IO转换为顺序IO
3. 避免服务器反复进行排序操作以及使用临时表
缺点
2. 过多的索引会生成过多的索引文件占用磁盘
数据结构
为什么不用平衡二叉树
太高了
树高太高了,取一次数据需要访问磁盘次数太多
太小了
硬盘一页为4k,平衡二叉树不能很好利用磁盘特性
B树
一个磁盘块可以放多个度,树高也降下来了,可以有效利用磁盘特性
数据和索引在同一个节点上
innodb使用B+树
相比B树,数据都存放在叶子节点上,一个磁盘块上可以存放更多的度,可以有效降低树高,减少磁盘访问次数
与mysiam索引区别
mysiam索引也使用B+树,但其叶子节点上存放数据的指针,辅助索引同样存放数据指针
innodb主键索引叶子节点存放具体的数据,辅助索引叶子节点存放对应的主键
常见规则
建立索引规则
每个表不超过5个索引
索引太多,会导致插入更新操作变慢
每个联合索引不超过5个字段
联合索引字段太多,区分度就不是很高了,反而会浪费空间
修改频繁的列不要建立索引
更新数据会更新索引,会导致数据库性能下降
区分度低的列不要建立索引
建立联合索引时
经常用的优先
区分度高的优先
长度短的优先
主键索引最好使用递增整数
整数:由于innodb辅助索引叶子节点存放的是主键,主键长度越小,辅助索引查找越快
递增:由于innodb主键索引上数据按主键递增排序,如果插入顺序不是递增,会导致页分裂,降低插入数据效率
使用索引规则
范围查询右边的列将不能使用索引
like的%在前面,不会使用索引(最左匹配原则)
查询的时候,对列的那一边使用函数,会导致不走索引(mysql认为使用函数会改变索引顺序)
隐式转换
比如查询条件为字符串列=数值,a=1,mysql会将字符串转换为数值进行比较,导致索引列使用了函数
字符集转换
比如a、b两列分别使用utf8和utf8mb4字符集,当查询条件是a=b的时候,会将utf8强制转换为utf8mb4,导致索引列使用了函数
覆盖索引优化
当索引中包含了查询条件的所有列,将直接返回,不去回表
索引下推优化
当使用范围查询,对于索引中该列后面的列,mysql会优化直接使用索引判断是否符合条件
事务
事务是font color=\"#c41230\
特性
1. 原子性
undo log属于逻辑日志,它记录的是sql执行相关的信息.
2. 持久性
由于数据库的的读写会font color=\"#0076b3\
redo log采用的是WAL(Write-ahead logging,预写式日志),所有修改先写入日志,再更新到Buffer Pool,保证了数据不会因MySQL宕机而丢失,从而满足了持久性要求
3. 隔离性
保证事务执行尽可能不受其他事务影响;InnoDB默认的隔离级别是RR,RR的实现主要基于锁机制(保证只能同时存在一个写操作)、数据的隐藏列(记录当前行数据的版本号、删除时间、指向undo log)、undo log、类next-key lock机制font color=\"#c41230\
脏读
不可重复读(数据变化)
幻读(数据增多)
4. 一致性
MVCC(多版本并发控制)
假设同一份数据,既有读事务访问,又有写事务操作,实际上,写事务会新建一个新的数据版本,而读事务访问的是旧的数据版本,直到写事务提交,读事务才会访问到这个新的数据版本
实现方式
数据记录的多个版本同时保存在数据库
使用undo_log动态构造(mysql的innodb使用该实现)
实现原理
在每一行有隐藏列,当前行的创建事务id,font color=\"#c41230\
读取每一行的时候
判断记录是否被修改
判断记录是否被删除
日志
redo log
redo log存放的是数据页的改动
bin log
格式
statement
直接记录执行的语句,有可能造成主从的数据不一致,比如delete limit 1,主从如果选择了不同的索引,将删除不同的数据
row
记录中有执行前的值和执行后的值,对于数据恢复提供了极大的便利,但是会急剧增大bin log大小,比如delete 10w行数据,就需要存10w语句
mixed
前两种模式的混合,对于mysql认为不会导致数据不一致的语句,使用statement,否则使用row格式
每个线程都有一个bin log buffer,因为事务是不能被打断的
使用sync_binlog参数控制flush
0表示每次只写到os_cache中就返回
1表示每次都执行flush
2-N表示集齐N个事务一起flush
使用组提交优化flush次数,将bin log的flush放到flush redo log之后
undo log
用来存放数据的历史版本,用于MVCC回退版本时使用
高可用
结构
主从
双主
冷备
延迟备份
防止误操作,可以设置为延迟1小时,这样可以保证1小时内发现的误操作及时修正
判活机制
select 1
缺点:当增删改查堵住的时候,select 1也会正常返回
select 表
缺点:当磁盘空间满的时候,select表可以正常返回
update 表
缺点:响应慢的情况,不容易检测到
开启innodb performance
缺点:开启会损失一部分性能
可以用update表+部分innodb performance
读写分离遇到问题
主从延迟导致刚写入的直接查从库查不到
直接查主库
sleep
先判断主从延迟,如果有延迟走主库
数据安全
crash safe
通过bin log和redo log
数据完整性
数据一致性
explain
id
id大的先执行,如果id一样,从上往下执行
select_type
SIMPLE等
table
涉及的表
type
system、const
可以转换为常数查询
eq_ref
主键或者唯一索引等值查询
ref
普通索引等值查询
range
范围查询
index
对索引进行全索引扫描
all
全表扫描
从上到下性能逐渐下降,最要求应该在range
possible_keys
可能用到的索引
key
实际用到的索引
key_len
索引的长度
rows
预估读取的行数
extra
using where
using index
覆盖索引
using filesort
需要排序
...
扩展
explain后使用show warings可以看到优化后的语句
hash算法
复杂度O(1)
二叉树查找树
定义
1. 左子树所有节点的值均小于他的根节点的值
2. 右子树所有节点的值均大于他的根节点的值
3. 子树也符合以上规则
数据不平衡
平衡二叉树
1. 二叉查找树的定义
2. 左右两个子树的高度差的绝对值不超过1(子高平衡)
几乎每次插入/删除节点都会影响二叉树的平衡
红黑树
1. 每个结点要么是红的,要么是黑的
2. 根结点是黑的
3. 每个叶结点,即空结点(NIL)是黑的
4. 如果一个结点是红的,那么它的俩个儿子都是黑的
5. 对每个结点,从该结点到其子孙结点的所有路径上包含相同数目的黑结点(黑高平衡)
B树(平衡多路查找树)
m阶的B树定义
1. 根节点至少有2个节点
font color=\"#8a8a8a\
4. 所有叶子节点在同一层上
5. 每个节点的元素从小到大排列
特点
B+树
m阶的B+树定义
1. 根节点至少有2个子女
2. 每个中间节点都至少包含ceil(m / 2)个孩子,最多有m个孩子
3. 每一个叶子节点都包含k-1个元素,其中 m/2 <= k <= m
4. 所有的叶子结点都位于同一层
5. 每个节点中的元素从小到大排列,节点当中k-1个元素正好是k个孩子包含的元素的值域分划
2. 每个中间节点至少包含ceil(m/2)个子节点
3. 每个叶子节点都有左右2个指针,指向左右的下一个数据数据,形成一个有序的双向链表
4. 只有叶子节点才会有data,其他都是主键索引
0 条评论
回复 删除
下一页