减少网站跳出率,百度投广告怎么收费,做vue用哪个网站,wordpress on line 66什么是MVCC#xff1f; 说说MySQL实现MVCC的原理#xff1f;
MVCC#xff0c;全称Multi-Version Concurrency Control#xff0c;即多版本并发控制。MVCC是一种并发控制的方法#xff0c;一般在数据库管理系统中#xff0c;实现对数据库的并发访问。 对于「读已提交」和…什么是MVCC 说说MySQL实现MVCC的原理
MVCC全称Multi-Version Concurrency Control即多版本并发控制。MVCC是一种并发控制的方法一般在数据库管理系统中实现对数据库的并发访问。 对于「读已提交」和「可重复读」隔离级别的事务来说它们是通过 Read View 来实现的它们的区别在于创建 Read View 的时机不同大家可以把 Read View 理解成一个数据快照就像相机拍照那样定格某一时刻的风景。「读已提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View而「可重复读」隔离级别是「启动事务时」生成一个Read View然后整个事务期间都在用这个 Read View。 基础知识 creator_trx_id创建该Read View的事务的事务id m_ids创建Read View时当前数据库中 活跃且未提交的事务的id列表。活跃事务”指的就是启动了但还没提交的事务。 min_trx_id创建Read View时当时数据库中活跃且未提交的事务中最小的事务的id也就是 m_ids 的最小值。 max_trx_id创建Read View时当前数据库中应该给下一个事务的id值也就是全局事务中最大的事务 id 值 1 聚簇索引记录中的两个隐藏列trx_id和roll_pointer [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8MLAi8bT-1677727928848)(en-resource://database/2289:1)]
trx_id当一个事务对某条聚簇索引记录进行改动时就会把该事务的事务 id 记录在 trx_id 隐藏列里roll_pointer每次对某条聚簇索引记录进行改动时都会把旧版本的记录写入到 undo 日志中然后这个隐藏列是个指针指向每一个旧版本记录于是就可以通过它找到修改前的记录。 规则如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值表示这个版本的记录是在创建 Read View 前已经提交的事务生成的所以该版本的记录对当前事务可见。如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值表示这个版本的记录是在创建 Read View 后才启动的事务生成的所以该版本的记录对当前事务不可见。如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间需要判断 trx_id 是否在 m_ids 列表中 如果记录的 trx_id 在 m_ids 列表中表示生成该版本记录的活跃事务依然活跃着还没提交事务所以该版本的记录对当前事务不可见。如果记录的 trx_id 不在 m_ids列表中表示生成该版本记录的活跃事务已经被提交所以该版本的记录对当前事务可见。
Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系
Buffer Pool 是 MySQL 的一个非常重要的组件因为针对数据库的增删改操作都是在 Buffer Pool 中完成的 Undo log 记录的是数据操作前的样子 redo log 记录的是数据被操作后的样子redo log 是 Innodb 存储引擎特有 bin log 记录的是整个操作记录这个对于主从复制具有非常重要的意义
MyISam和InnoDB区别区别
InnoDB支持事务MyISAM不支持对于InnoDB每一条SQL语言都默认封装成事务自动提交这样会影响速度所以最好把多条SQL语言放在begin和commit之间组成一个事务 2. InnoDB支持外键而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败InnoDB是聚集索引使用BTree作为索引结构数据文件是和主键索引绑在一起的表数据文件本身就是按BTree组织的一个索引结构必须要有主键通过主键索引效率很高。但是辅助索引需要两次查询先查询到主键然后再通过主键查询到数据。因此主键不应该过大因为主键太大其他索引也都会很大。MyISAM是非聚集索引也是使用BTree作为索引结构索引和数据文件是分离的索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说InnoDB的B树主键索引的叶子节点就是数据文件辅助索引的叶子节点是主键的值而MyISAM的B树主键索引和辅助索引的叶子节点都是数据文件的地址指针。 4.InnoDB不保存表的具体行数执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快注意不能加有任何WHERE条件那么为什么InnoDB没有了这个变量呢 因为InnoDB的事务特性在同一时刻表中的行数对于不同的事务而言是不一样的因此count统计会计算对于当前事务而言可以统计到的行数而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在InnoDB还会尝试去遍历其他聚簇索引。 如果索引并没有完全处于InnoDB维护的缓冲区Buffer Pool中count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUSInnodb不支持全文索引而MyISAM支持全文索引在涉及全文索引领域的查询效率上MyISAM速度更快高PS5.7以后的InnoDB支持全文索引了MyISAM表格可以被压缩后进行查询操作InnoDB支持表、行(默认)级锁而MyISAM支持表级锁 InnoDB的行锁是实现在索引上的而不是锁在物理行记录上。潜台词是如果访问没有命中索引也无法使用行锁将要退化为表锁。 8、InnoDB表必须有唯一索引如主键用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键而Myisam可以没有 9、Innodb存储文件有frm、ibd而Myisam是frm、MYD、MYI。 Innodbfrm是表定义文件ibd是数据文件Myisamfrm是表定义文件myd是数据文件myi是索引文件
说下MySQL的索引有哪些吧
按数据结构分类可分为Btree索引、Hash索引、Full-text索引。 按物理存储分类可分为聚簇索引、二级索引辅助索引|非聚簇索引。 按字段特性分类可分为主键索引、普通索引、前缀索引。 按字段个数分类可分为单列索引、联合索引复合索引、组合索引。
什么是B树为什么B树成为主要的SQL数据库的索引实现
什么是BTree B-Tree又叫做B树和平衡二叉树不同的地方在于B树是多叉树(平衡多路查找树)BTree是B树的变种基于BTree 和叶子节点顺序访问指针进行实现它具有BTree 的平衡性并且通过顺序访问指针来提高区间查询的性能。在 BTree 中一个节点中的 key 从左到右非递减排列。 为什么B树成为主要的SQL数据库的索引实现 二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点右子树的所有节点都大于这个节点。当每次插入的元素都是二叉查找树中最大的元素二叉查找树就会退化成了一条链表查找数据的时间复杂度变成了 O(n)为了解决二叉查找树会在极端情况下退化成链表的问题后面就有人提出平衡二叉查找树AVL 树 主要是在二叉查找树的基础上增加了一些条件约束每个节点的左子树和右子树的高度差不能超过 1自平衡然而不管平衡二叉查找树还是红黑树都会随着插入的元素增多而导致树的高度变高这就意味着磁盘 I/O 操作次数多会影响整体数据查询的效率根本原因是因为它们都是二叉树也就是每个节点只能保存 2 个子节点。 为了解决降低树的高度的问题后面就出来了B树它不再限制一个节点就只能有 2 个子节点而是允许 M 个子节点 (M2)从而降低树的高度。因为节点数据量的问题B树过渡到了B树。 索引的核心是提高每次io的有效数据因为io相对于cpu、内存来讲速度差着数量级。BTree是B TREE的变种B TREE能解决的问题BTREE也能够解决降低树的高度增大节点存储数据量
B树的中间节点不存储卫星数据所以同样大小的磁盘页可以容纳更多的节点元素如此一来相同数量的数据下B树就相对来说要更加矮胖些磁盘IO的次数更少由于只有叶子节点才保存卫星数据B树每次查询都要到叶子节点而B树每次查询则不一样最好的情况是根节点最坏的情况是叶子节点没有B树稳定B树天然具备排序功能由于B树所有的叶子节点数据构成了一个有序链表在查询范围区间数据的时候会更加方便数据紧密性很好高B树全节点遍历更快B树遍历整棵树只需要遍历所有的叶子节点即可而B树需要对每一层进行遍历所以B树更有利于全表扫描 公众号
事务的ACID属性为保证数据库的一致性在事务处理之前和之后都要遵循某些属性也就是大家耳熟能详的ACID属性
原子性Atomicity:即不可分割性事务中的操作要么全不做要么全做通过 undo log 重做日志来保证的一致性Consistency:一个事务在执行前后数据库必须满足正确的状态满足完整性约束通过其余三个特点实现隔离性Isolation:多个事务并发执行时一个事务的执行不应该影响其它事务的执行通过 MVCC多版本并发控制 或锁机制来保证的持久性Durability:事务处理完成后对数据的修改是永久性的即便系统故障也不会丢失通过 redo log重做日志来保证的
并发事务处理带来的问题
脏读一个事务正在对一条记录做修改在这个事务完成并提交前这条记录的数据就处于不一致的状态这时另一个事务也来读取同一条记录如果不加控制第二个事务读取了这些“脏”数据并据此作进一步的处理就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。概括为事务A读取到了事务B已经修改但尚未提交的数据还在这个数据基础上做了操作。此时如果B事务回滚A读取的数据无效不符合一致性要求不可重复读 一个事务在读取某些数据后的某个时间再次读取以前读过的数据却发现 其读出的数据已经发生了改变、或某些记录已经被删除了这种现象就叫做“不可重复读”。概括为事务A读取到了事务B已经提交的修改的数据。(核心思想是读到了已经提交的数据但是和第一次读到的不一样)幻读一个事务按相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足其查询条件的新数据这种现象就称为“幻读”。概括为事务A读取到了事务B提交的新增数据不符合隔离性
数据库隔离级别
读未提交read uncommitted指一个事务还没提交时它做的变更就能被其他事务看到读提交read committed指一个事务提交之后它做的变更才能被其他事务看到可重复读repeatable read指一个事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的MySQL InnoDB 引擎的默认隔离级别串行化serializable 会对记录加上读写锁在多个事务对这条记录进行读写操作时如果发生了读写冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行
隔离级别脏读不可重复读幻读读未提交(Read Uncommitted)可能可能可能读已提交(Read Committed)不可能可能可能可重复读Repeatable Read不可能不可能可能串行读Serializable不可能不可能不可能
MySQL 锁的类型有哪些呢
MySQL锁可以按模式分类为乐观锁与悲观锁。 按粒度分可以分为全局锁、表级锁、页级锁、行级锁。 按属性可以分为共享锁、排它锁。 按状态分为意向共享锁、意向排它锁。 按算法分为间隙锁、临键锁、记录锁。 按粒度 全局锁对整个数据库实例加锁应用在全库逻辑备份 表锁一种是表锁一种是元数据锁meta data lockMDL)。MDL 不需要显式使用在访问一个表的时候会被自动加上在 MySQL 5.5 版本中引入了 MDL当对一个表做增删改查操作的时候加 MDL读锁当要对表做结构变更操作的时候加 MDL 写锁。风险点在于加了写DML之后的读DML全部被阻塞 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。因此采取了折衷的页级锁一次锁定相邻的一组记录。BDB 引擎支持页级锁。 行级锁级锁是粒度最低的锁发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大容易发生死锁现象。MySQL中只有InnoDB支持行级锁行级锁分为共享锁和排他锁。记录锁锁的是索引如果不是索引加的排它锁就不是rl、间隙锁范围内的没有索引的空间与 Next-Key Lock前面的合体锁住行和之间的空隙-左开右闭。锁的是索引索引分为主键索引和非主键索引两种如果一条sql语句操作了主键索引MySQL就会锁定这条主键索引如果一条语句操作了非主键索引MySQL会先锁定该非主键索引再锁定相关的主键索引。 深入了解mysql–gap locks,Next-Key Locks 按照模式 乐观锁乐观锁是相对悲观锁而言的乐观锁假设数据一般情况下不会造成冲突所以在数据进行提交更新的时候才会正式对数据的冲突与否进行检测如果发现冲突了则返回给用户错误的信息让用户决定如何去做。 适用于读多写少。
#通过加了一个版本号
update set status支付成功,versionversion1 where id#{id} and version#{ version};悲观锁具有强烈的独占和排他特性每次去拿数据的时候都认为别人会修改在整个数据处理过程中将数据处于锁定状态。 适用于并发量不大、写入操作比较频繁、数据一致性比较高的场景。 在MySQL中使用悲观锁必须关闭MySQL的自动提交set autocommit0因为MySQL默认使用autocommit模式也就是说当你执行一个更新操作后MySQL会立刻将结果进行提交这样多个更新不是一块锁了。 共享锁和排它锁是悲观锁的不同的实现它俩都属于悲观锁的范畴 悲观锁 按照属性 共享锁又称之为读锁简称S锁。如果事务T对数据A加上排它锁后就只允许事务T对数据A的读取、修改其他事务对数据A不能再加任何锁也不能读取和修改数据A直到事务T释放A上的排它锁。通过普通读和共享读都可以读出来数据。
select …lock in share mode排它锁又称之为写锁简称X锁当事务对数据加上写锁后其他事务既不能对该数据添加读写也不能对该数据添加写锁写锁与其他锁都是互斥的。只有当前数据写锁被释放后其他事务才能对其添加写锁或者是读锁。 MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁select语句默认不会加任何锁类型。提交事务或回滚事务就会释放锁。
select …for update按状态分 意向共享锁、意向排它锁。意向锁是表锁为了协调行锁和表锁的关系支持多粒度表锁与行锁的锁并存。 当有事务A有行锁时MySQL会自动为该表添加意向锁事务B如果想申请整个表的写锁那么不需要遍历每一行判断是否存在行锁而直接判断是否存在意向锁增强性能。 注意普通查询没有任何的锁机制 MySQL锁
MySQL主从复制
主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。
binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。I/O 线程 : 负责从主服务器上读取二进制日志并写入从服务器的中继日志中。SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句 全同步复制主库写入binlog后强制同步日志到从库所有的从库都执行完成后才返回给客户端但是很显然这个方式的话性能会受到严重影响。 半同步复制和全同步不同的是半同步复制的逻辑是这样从库写入日志成功后返回ACK确认给主库主库收到至少一个从库的确认就认为写操作完成
MySQL读写分离方案?
主服务器处理写操作以及实时性要求比较高的读操作而从服务器处理读操作。 读写分离能提高性能的原因在于
主从服务器负责各自的读和写极大程度缓解了锁的争用从服务器可以使用 MyISAM提升查询性能以及节约系统开销增加冗余提高可用性。 读写分离常用代理方式来实现代理服务器接收应用层传来的读写请求然后决定转发到哪个服务器
分库分表怎么做的
确定userId 作为sharding key通过基因法id构成最后3位决定取模84位取模16保证其它非sharding key 。hash一致性分库分表、 直接32库逻辑库32表1024张表固然可以一步到位但是对于小公司来说前期根本用不上浪费机器且增加系统复杂度所以还是循序渐进按照一致性hash算法的思想先确定总的节点数为32 基因法分库基因假如通过 uid 分库分为 8 个库采用 uid%8 的方式进行路由此时是由 uid 的最后 3bit 来决定这行 User 数据具体落到哪个库上那么这 3bit 可以看为分库基因。 一致性hash
