Mysql

Mysql

行锁

MySQL 表锁和行锁机制 分析

在执行DML操作时,会对涉及的行加锁,同时也会对该表加上意向锁。(意向共享锁:与表共享兼容,表拍他互斥。意向排它锁:都互斥。但是意向拍他之间,不会互斥,行锁可以保证的。)

后面对这张表加表锁的时候,会根据该表上的意向锁来判定是否可以加锁成功,而不用逐行在判断行锁。

总结:InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁

当你需要更新一张较大表的大部分甚至全表的数据时。而你又傻乎乎地用索引作为检索条件。一不小心开启了行锁(没毛病啊!保证数据的一致性!)。可MySQL却认为大量对一张表使用行锁,会导致事务执行效率低,从而可能造成其他事务长时间锁等待和更多的锁冲突问题,性能严重下降。所以MySQL会将行锁升级为表锁,即实际上并没有使用索引。

行锁的劣势:开销大;加锁慢;会出现死锁
行锁的优势:锁的粒度小,发生锁冲突的概率低;处理并发的能力强
加锁的方式:自动加锁。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁;对于普通SELECT语句,InnoDB不会加任何锁;当然我们也可以显示的加锁:
共享锁:select * from tableName where … + lock in share more
排他锁:select * from tableName where … + for update
InnoDB和MyISAM的最大不同点有两个:一,InnoDB支持事务(transaction);二,默认采用行级锁。加锁可以保证事务的一致性,可谓是有人(锁)的地方,就有江湖(事务);我们先简单了解一下事务知识。

行锁

间隙锁:防止其他事务在间隙insert,防止了了RR隔离级别的幻读。

临间锁:上俩综合。

mysql是单线程还是多线程?

mysql是多线程的。mysql是一个单进程多线程的数据库,在innodb中大概3种线程为:1、主线程Master Thread;2、IO Thread线程,用于异步处理写请求;3、purge Thread线程,用于删除undo日志。

主从复制的时候,从节点将从主节点read到的二进制日志通过IOThread写入到中继日志。

HAVING和WHERE的异同点

执行时机不同,一个在分组前,不满足where不参与分组,having对分组之后的结果进行过滤。

判断条件不同,where不能对聚合函数进行判断。

顺序:where > 聚合函数 > having。

语法上:where 用表中列名,having 用 select 结果别名。

影响结果范围:where 从表读出数据的行数,having 返回客户端的行数。

where 可以使用索引,having 不能使用索引,只能在临时结果集操作。

where后面不能使用聚集函数,having是专门使用聚集函数的。

SQL10 获取所有非manager的员工emp_no

MySQL官方文档有说明,in关键字适合确定数量的情况,一般效率较低,不推荐使用。能用in关键字的语句都可以转化为使用join的语句,推荐使用join关键字。

MYSQL的隔离级别是基于锁和MVCC共同实现的。

binlog、redolog、undolog

ifpRJK5vzec4jh6

redolog用于主机的重启恢复,会有一个二阶段提交过程(将redo log的写入拆成了两个步骤preparecommit,这就是两阶段提交),先会有redolog的准备节点,在binlog写入,再redolog的commit

binlog主要用于备份

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性,使用 undo log(回滚日志) 来保证事务的原子性

MySQL数据库的数据备份、主备、主主、主从都离不开binlog,需要依靠binlog来同步数据,保证数据一致性。

ThdnPEwA2Su7vki

InnoDB存储引擎对MVCC的实现

InnoDB 存储引擎中,多版本控制 (multi versioning) 就是对非锁定读的实现。如果读取的行正在执行 DELETEUPDATE 操作,这时读取操作不会去等待行上锁的释放。相反地,InnoDB 存储引擎会去读取行的一个快照数据,对于这种读取历史数据的方式,我们叫它快照读 (snapshot read)

所以说,insert是在提交事务后,undolog就能删除的,但是delete和update是需要mvcc的。

MVCC 的实现依赖于:隐藏字段、Read View、undo log

IMG_2075.jpg

索引

用B+,而不是B,而不是HashMap(哈希冲突,顺序和范围查找)

而不是平衡二叉树,平衡二叉树创建删除增加更麻烦。而不是普通二叉树(深度太深)。

dvIjm4FeM7VJXpG

Author: Jcwang

Permalink: http://example.com/2022/06/06/mysql/