wordpress网站管理员插件,潍坊最新通知,接私活做预算的网站,接送车服务网站怎么做SQL底层执行原理 MySQL的内部组件结构#xff1a;大体来说#xff0c;MySQL 可以分为 Server 层和存储引擎层store两部分 Server层:主要包括连接器、查询缓存、分析器、优化器、执行器等#xff0c;涵盖 MySQL 的大多数核心服务功能#xff0c;以及所有的内置函数#xf…SQL底层执行原理 MySQL的内部组件结构大体来说MySQL 可以分为 Server 层和存储引擎层store两部分 Server层:主要包括连接器、查询缓存、分析器、优化器、执行器等涵盖 MySQL 的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。 Store层:存储引擎层负责数据的存储和提取。其架构模式是插件式的支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。 Server层中连接器、查询缓存、分析器、优化器、执行器分别主要干了哪些事情。
连接器连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接的时候先校验用户名和密码然后才获取权限。 a.一个用户成功建立连接后会创建一个会话空间这个会话空间存储了用户名和密码及相关的权限相关权限存储在user表中。所以即使你用管理员账号对这个用户的权限做了修改只要连接没断开也不会影响已经存在连接的权限修改完成后只有再新建的连接才会使用新的权限设置。用户的权限表在系统表空间的mysql的user表中。 b.数据库里面连接分长连接和短连接。长连接是指连接成功后如果客户端持续有请求则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接下次查询再重新建立一个。 c.连接占用的内存在连接断开的时候才会释放。长连接有时会导致内存涨的非常快如果长连接累积下来可能导致内存占用太大被系统强行杀掉OOM从现象看就是 MySQL 异常重启了。 怎么解决这类问题呢1、定期断开长连接。使用一段时间或者程序里面判断执行过一个占用内存的大查询后断开连接之后要查询再重连。2、如果你用的是 MySQL 5.7 或更新版本可以在每次执行一个比较大的操作后通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态。
查询缓存mysql8.0已经移除了查询缓存功能。
分析器如果缓存没命中下一步就会由分析器处理。 词法分析器分成6个主要步骤完成对sql语句的分析1、词法分析2、语法分析3、语义分析4、构造执行树5、生成执行计划6、计划的执行。 也就是说词法分析器会将sql拆分构建成一颗语法树。 词源在分布式开发(比如分库分表在第三期分库分表中讲到还有分布式事务也用到)中很有用。 词法结构分析插件antlr v4
优化器经过了分析器MySQL 就知道你要做什么了。在开始执行之前还要先经过优化器的处理。优化器是在表里面有多个索引的时候决定使用哪个索引或者在一个语句有多表关联join的时候决定各个表的连接顺序。
执行器 开始执行的时候要先判断一下你对这个表T有没有执行查询的权限如果没有就会返回没有权限的错误。如果命中查询缓存会在查询缓存返回结果的时候做权限验证。查询也会在优化器之前调用 precheck 验证权限。如果有权限就打开表继续执行。打开表的时候执行器就会根据表的引擎定义去使用这个引擎提供的接口。
事务 mysql设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制来解决多事务并发问题。
ACID 事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。 1.原子性(Atomicity) 操作层面所有操作都要执行。事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。 2.一致性(Consistent) 数据层面所有数据都应正确更新。在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。 3.隔离性(Isolation) 每个事务之间的数据是不互相影响的类似局部变量。数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。 4.持久性(Durable) 数据是持久化的。事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态也就是说一个事务执行之前和执行之后都必须处于一致性状态。 还是转账来说假设用户A和用户B两者的钱加起来一共是1000那么不管A和B之间如何转账转几次账事务结束后两个用户的钱相加起来应该还得是1000这就是事务的一致性。
并发事务处理带来的问题 1.更新丢失(Lost Update)或脏写 最后的更新覆盖了由其他事务所做的更新 2.脏读(读到事务B修改的数据) 事务A读取到了事务B已经修改但尚未提交的数据 3.不可重读 事务A内部的相同查询语句在不同时刻读出的结果不一致不符合隔离性 4.幻读读到事务B新增的数据 一个事务按相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足其查询条件的新数 据这种现象就称为“幻读”。 即事务A读取到了事务B提交的新增数据不符合隔离性
事务隔离级别 分读未提交、读已提交、可重复读、可串行化 读未提交 read-uncommitted表示可以读到未提交的数据
锁 锁和事务、事务隔离级别是关联的,事务是通过锁来实现 1.锁分类 a.从性能上分为乐观锁(用版本对比来实现)和悲观锁 b.从对数据库操作的类型分分为读锁和写锁(都属于悲观锁) 读锁共享锁S锁(Shared)针对同一份数据多个读操作可以同时进行而不会互相影响 写锁排它锁X锁(eXclusive)当前写操作没有完成前它会阻断其他写锁和读锁 c.从对数据操作的粒度分分为表锁和行锁。可以给表或行加读锁、写锁。
事务中的悲观锁与乐观锁 悲观锁修改数据时直接加锁其他用户不能访问 乐观锁用版本号来控制,版本号不改变的情况下可以进行其它事务操作
事务中的乐观锁和并发的乐观锁(比如CAS操作)类似都是无锁没有阻塞的。 悲观锁会进行阻塞。乐观锁不会阻塞(不会涉及锁等待)在没有拿到锁的时候会回滚或重试。
读锁是共享锁写锁是排他锁。
表锁一般用在整表数据迁移的场景 每次操作锁住整张表。开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高并发度最低一般用在整表数据迁移的场景。myisam,innodb支持表锁。 加了读锁无法进行写操作。所有session都可以进行读。 加了写锁当前session可以进行增删改查其它session无法对该表进行增删改查
行锁 每次操作锁住一行数据。开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度最高。 InnoDB与MYISAM的最大不同有两点 a.InnoDB支持事务TRANSACTION,myisam不支持 b.InnoDB支持行级锁,myisam不支持 打交道最多的是行锁
2.锁总结 MyISAM在执行查询语句SELECT前会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。InnoDB在执行查询语句SELECT时(非串行隔离级别)不会加锁。但是update、insert、delete操作会加行锁。 简而言之就是读锁会阻塞写但是不会阻塞读。而写锁则会把读和写都阻塞。 3.锁优化建议 1.尽可能让所有数据检索都通过索引来完成避免无索引行锁升级为表锁 2.合理设计索引尽量缩小锁的范围 3.尽可能减少检索条件范围避免间隙锁。 4.尽量控制事务大小减少锁定资源量和时间长度涉及事务加锁的sql尽量放在事务最后执行 5.尽可能低级别事务隔离
间隙锁概述 间隙锁用于防止幻读现象它锁住的是索引中记录之间的“间隙”而不是记录本身。具体来说当一个事务需要对某个范围的记录进行修改时InnoDB 会锁住这个范围内的所有间隙以防止其他事务插入符合条件的新记录。 幻读一个事务按相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足其查询条件的新数 据这种现象就称为“幻读”。
行锁与事务隔离级别案例 略。
InnoDB引擎底层存储和缓存原理 当我们想从表中获取某些记录时InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么 InnoDB 采取的方式是将数据划分为若干个页以页作为磁盘和内存之间交互的基本单位InnoDB 中页的大小一般为 16 KB。也就是在一般情况下一次最少从磁盘中读取 16KB 的内容到内存中一次最少把内存中的 16KB 内容刷新到磁盘中。
InnoDB记录存储结构和索引页结构微观角度查看记录和页面存储方式 1.记录存储结构1条记录1行数据 2.索引页结构索引页中包含了记录User Records
InnoDB的体系结构宏观角度查看InnoDB 的内存结构和磁盘存储结构 1.内存结构 包含Buffer Pool、Redo Log Buffer 简化版 Buffer Pool用于缓存磁盘中的页 a.InnoDB 为了缓存磁盘中的页在 MySQL 服务器启动的时候就向操作系统申请了一片连续的内存他们给这片内存起了个名叫做 Buffer Pool中文名是缓冲池。那它有多大呢这个其实看我们机器的配置默认情况下 Buffer Pool 只有128M 大小。它是可以配置的。 b.Buffer Pool 的缺省值其实是偏小的一个比较合理的设置方法是按比例设置一般的网上惯例是给 buffer pool 设置的机器内存的 60%-80%左右。以比较权衡的值是 70%~75%之间。具体值可以看服务器使用情况。 c.Buffer Pool 内部组成 1.Buffer Pool中默认的缓存页大小和在磁盘上默认的页大小是一样的都是16KB。innodb会为每一个缓存页创建一些控制信息控制信息包括该页所属的表空间编号、页号、缓存页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息当然还有一些别的控制信息。 每个缓存页对应的控制信息占用的内存大小是相同的我们称为控制块。控制块和缓存页是一一对应的它们都被存放到 Buffer Pool 中其中控制块被存放到 Buffer Pool 的前边缓存页被存放到 Buffer Pool 后边 2.free 链表的管理用于标识哪些缓存页是空闲的方便判断缓存存放。 3.LRU 链表的管理
2.磁盘结构 A.包含系统表空间、Redo Log、独立表空间、其他表空间、Undo空间 a.系统表空间存储系统相关表文件数据如innodb数据字典、双写缓冲区等 1),整个MySQL进程只有一个系统表空间 2)在系统表空间中会额外记录一些有关整个系统信息的页面所以会比独立表空间多出一些记录这些信息的页面相当于是表空间之首所以它的表空间 IDSpace ID是 0。 系统表空间有extent 1和extent两个区也就是页号从64~191这128个页面被称为Doublewrite buffer也就是双写缓冲区。 b.独立表空间存储每个业务表数据 1).表空间中的页可以达到2的32次方个页实在是太多了为了更好的管理这些页面 InnoDB中还有一个区英文名extent的概念。对于16KB的页来说连续的64个页就是一个区也就是说一个区默认占用1MB空间大小。不论是系统表空间还是独立表空间都可以看成是由若干个区组成的每256个区又被划分成一个组。 所以结构由小到大为页-区-组表空间 2)如果链表中相邻的两个页物理位置离得非常远就是所谓的随机I/O。如果两个页是相邻的则为顺序I/O。随机I/O是非常慢。 引入区的主要目的是什么按区分为空间的话分配的页大多是相邻的 因为一个区是物理位置上连续的64个页如果按区分为空间的话分配的页大多是相邻的从性能角度看可以消除很多的随机I/O可以更多的使用顺序I/O。 3).段segment可以将叶子节点和非叶子节点进行分类放在不同段提升查询效率。它是一个逻辑概念。 B.表空间(tablespaces)是一个抽象的概念对于系统表空间来说对应着文件系统中一个或多个实际文件一般是ibdata1 C.对于每个独立表空间也就是File-Per-Table Tablespaces来说对应着文件系统中一个名为表名.ibd的实际文件。 D.可以把表空间想象成被切分为许许多多个页的池子当我们想为某个表插入一条记录的时候就从池子中捞出一个对应的页来把数据写进去。 E.表空间中的每一个页都对应着一个页号这个页号由4个字节组成也就是32个比特位所以一个表空间最多可以拥有2的32次方个页如果按照页的默认大小16KB来算一个表空间最多支持64TB的数据。
3.双写缓冲区和buffer pool的区别 作用不一样。双写缓冲区用于保证数据可靠性用于写磁盘数据的恢复。当数据写入失败时可以通过它来恢复。而Buffer pool是为了缓存数据页Page信息。 双写缓冲区是InnoDB存储引擎的一个特性它用于保证数据的一致性和可靠性。当InnoDB引擎写入数据时会先将数据写入到双写缓冲区然后再写入到磁盘上的数据文件。如果在写入数据文件时发生了故障可以通过双写缓冲区中的数据进行恢复从而保证数据的一致性和可靠性。 Buffer pool是InnoDB存储引擎的另一个重要缓存区它用于缓存数据页。当InnoDB引擎需要读取数据时会先在buffer pool中查找数据页如果找到了就直接返回如果没有找到就从磁盘上读取数据并将数据页存储到buffer pool中。当InnoDB引擎需要写入数据时也是先将数据写入到buffer pool中然后再异步地将数据写入到磁盘上的数据文件中。 因此双写缓冲区和buffer pool的区别在于它们的作用不同双写缓冲区用于保证数据的一致性和可靠性而buffer pool用于缓存数据页提高数据的读取和写入性能。 缓冲区不仅在内存中有更多的是属于MySQL 的系统表空间属于磁盘文件的一部分。
4.innodb doublewrite buffer和redo log区别 都属于系统表空间。redo log用于事务失败的恢复而doubewrite buffer用于写磁盘数据的恢复 innodb doublewrite buffer和redo log都是InnoDB存储引擎中用于保证数据完整性的机制但它们的作用不同。 redo log是用于崩溃恢复的机制记录了事务在执行过程中对数据所做的修改此时还未存库以便在崩溃后进行恢复。 doublewrite buffer是用于保证数据页写入磁盘的完整性的机制它会在数据页写入磁盘之前先将数据页的副本写入doublewrite buffer中然后再将数据页写入磁盘。这样即使在写入磁盘的过程中发生了崩溃也可以通过doublewrite buffer中的数据来恢复数据页保证数据的完整性。 因此redo log和doublewrite buffer都是InnoDB存储引擎中非常重要的机制但它们的作用不同各自都有其独特的作用。
innodb引擎底层事务实现机制 MVCC与BufferPool缓存机制 A.MVCC机制(用于保证事务隔离) 同样的sql查询语句在一个事务里多次执行查询结果相同就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。这里就用到mvcc机制。mvcc机制全称多版本并发控制隔离机制(Multi-Version Concurrency Control)。 undo日志版本链与read view机制详解 1.undo即回滚用于事务回滚。redo即重做用于事务的崩溃恢复比如机器宕机。 2.undo日志版本链包含了日志信息和指针数据。 3.undo日志版本链的回滚指针是指向上一条日志。即它是较新的数据指向的是旧的数据。 4.read-view即查询一致性视图 5.事务内查询是不会生成事务id的更新的时候才会生成事务id。 6.read- view在事务开始后的第一次查询时会生成一次直到结束都不会变其作用范围是本次事务。而undo日志版本链是会一直变的只要有更新就会变其作用范围是针对所有事务。
mvcc如何保证在其它事务修改了数据之后保证可重复读的 答案是readview视图undo日志版本链比对规则
B.BufferPool机制流程提高读写性能 1.mysql undo日志、redo日志、binlog的区别 a.MySQL中有三种不同的日志文件类型undo log、redo log和binlog。它们各自有不同的用途和特点。 undo log用于回滚事务。当一个事务需要回滚时MySQL会利用undo log中的信息将数据恢复到事务开始之前的状态。undo log是InnoDB存储引擎层的日志。 redo log用于恢复事务。当MySQL崩溃并重新启动时它会利用redo log中的信息将数据恢复到崩溃之前的状态。redo log是InnoDB存储引擎层的日志。 binlog用于复制和恢复。binlog记录了所有对数据库的修改操作包括对表结构的修改。它可以用于数据备份、恢复和复制。binlog是MySQL Server层记录的日志。 它们的区别可以总结如下 用途undo log用于回滚事务redo log用于恢复事务binlog用于复制和恢复。 存储位置undo log和redo log都是InnoDB存储引擎层的日志而binlog是MySQL Server层记录的日志。 记录内容undo log记录了事务执行前的数据用于回滚事务redo log记录了事务执行后的数据用于恢复事务binlog记录了所有对数据库的修改操作包括对表结构的修改。 格式undo log和redo log的格式相同都是物理日志binlog的格式是逻辑日志。 日志生成undo log和redo log是在事务执行时生成的binlog是在事务提交时生成的。 删除策略undo log和redo log的删除策略是循环利用binlog的删除策略是根据过期时间删除。
物理日志和逻辑日志的区别 物理日志 和 逻辑日志 主要在记录方式和用途上有所区别 物理日志记录数据页的实际变化。它们关注数据的具体物理状态或位置更侧重于如何恢复数据的一致性和可靠性。 逻辑日志记录数据的操作或事件如 sql。它关注数据的变化内容和操作步骤适用于主从复制、数据审计和恢复操作。
2.总结即undo log和redo log和事务相关属于引擎层的日志。undo log用于事务回滚redo log用于由于系统宕机后为“事务提交成功但未来得及写磁盘”的事务进行事务恢复。而binlog为server层日志记录的所有数据库的sql操作用于数据备份、恢复、复制。
Redo 日志和 Undo 日志的关系 数据库崩溃重启后需要先从 redo log 中把未落盘的脏页数据恢复回来重新写入磁盘保证用户的数据不丢失。当然在崩溃恢复中还需要把未提交的事务进行回滚操作。由于回滚操作需要 undo log 日志支持undo log 日志的完整性和可靠性需要 redo log 日志来保证所以数据库崩溃需要先做 redo log 数据恢复然后做 undo log 回滚。 在事务执行过程中除了记录 redo 一些记录还会记录 undo log 日志。Undo log 记录了数据每个操作前的状态如果事务执行过程中需要回滚就可以根据undo log 进行回滚操作。 因为 redo log 是物理日志记录的是数据库页的物理修改操作。所以 undo log可以看成数据库的数据的写入也会伴随着 redo log 的产生因为undo log也是存储在数据库页中的所以对undo log的操作也是对数据库页的物理操作这是因为 undo log也需要持久化的保护。也就是说掉电重启的时候会从redo log中将redo log里的undo log数据恢复到undo log中。然后才会根据需要读取undo log进行回滚。 即对undo log的操作记录也会记录到redo log中同时写 Redo 和 Binlog 。怎么保持一致使用2PC 略。
总的来说MySQL 中事务的原子性是通过 undo log 来实现的事务的持久性是通过 redo log 来实现的事务的隔离性是通过读写锁MVCC 来实现的。事务的一致性通过原子性、隔离性、持久性来保证。也就是说 ACID 四大特性之中C(一致性)是目的A(原子性)、I(隔离性)、D(持久性)是手段是为了保证一致性数据库提供的手段。数据库必须要实现 AID 三大特性才有可能实现一致性。同时一致性也需要应用程序的支持应用程序在事务里故意写出违反约束的代码一致性还是无法保证的例如转账代码里从 A 账户扣钱而不给 B账户加钱那一致性还是无法保证。
在事务的具体实现机制上MySQL 采用的是 WALWrite-ahead logging预写式日志机制来实现的。这也是是当今的主流方案。(即所有修改先写日志再写磁盘) 1.在使用 WAL 的系统中所有的修改都预先被写入到日志中然后再被应用到系统中。通常包含redo 和 undo 两部分信息。 2.redo 日志准确的说是修改日志而不是失败重做日志。
MySQL 事务执行流程 MySQL 的事务主要主要是通过 Redo Log 和 Undo Log 实现的。 MySQL 在事务执行的过程中会记录相应 SQL 语句的 UndoLog 和Redo Log然后在内存中更新数据并形成数据脏页。接下来 RedoLog 会根据一定规则触发刷盘操作Undo Log 和数据脏页则通过刷盘机制刷盘。事务提交时会将当前事务相关的所有 Redo Log 刷盘只有当前事务相关的所有 Redo Log 刷盘成功事务才算提交成功。
MySQL事务恢复流程 如果一切正常则 MySQL 事务会按照上图中的顺序执行。如果 MySQL 由于某种原因崩溃或者宕机当然进行数据的恢复或者回滚操作。 如果事务在执行第 8 步,即事务提交之前MySQL 崩溃或者宕机,此时会先使用Redo Log 恢复数据,然后使用 Undo Log 回滚数据。 如果在执行第8步之后MySQL崩溃或者宕机此时会使用Redo Log恢复数据大体流程如下图所示。 MySQL 崩溃恢复后首先会获取日志检查点信息随后根据日志检查点信息使用 Redo Log 进行恢复。MySQL 崩溃或者宕机时事务未提交则接下来使用 Undo Log 回滚数据。如果在 MySQL 崩溃或者宕机时事务已经提交则用Redo Log 恢复数据即可。
恢复机制 在服务器不挂的情况下redo 日志简直就是个大累赘不仅没用反而让性能变得更差。但是万一数据库挂了就可以在重启时根据 redo 日志中的记录就可以将页面恢复到系统崩溃前的状态。 MySQL 可以根据 redo 日志中的各种 LSN 值来确定恢复的起点和终点。然后将 redo 日志中的数据以哈希表的形式将一个页面下的放到哈希表的一个槽中。之后就可以遍历哈希表因为对同一个页面进行修改的 redo 日志都放在了一个槽里所以可以一次性将一个页面修复好避免了很多读取页面的随机 IO。 并且通过各种机制避免无谓的页面修复比如已经刷新的页面进而提升崩溃恢复的速度。
事务崩溃后的恢复为什么不用 binlog 1、这两者使用方式不一样 binlog 会记录表所有更改操作包括更新删除数据更改表结构等等主要用于人工恢复数据而 redo log 对于我们是不可见的它是 InnoDB 用于保证crash-safe 能力的也就是在事务提交后 MySQL 崩溃的话可以保证事务的持久性即事务提交后其更改是永久性的。 一句话概括binlog 是用作人工恢复数据redo log 是 MySQL 自己使用用于保证在数据库崩溃时的事务持久性。 2、redo log 是 InnoDB 引擎特有的binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。 3、redo log 是物理日志记录的是“在某个数据页上做了什么修改”恢复的速度更快binlog 是逻辑日志记录的是这个语句的原始逻辑比如“给 ID2这的 c 字段加 1 ” 4、redo log 是“循环写”的日志文件redo log 只会记录未刷盘的日志已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志保存的是全量的日志。 5、最重要的是当数据库 crash 后想要恢复未刷盘但已经写入 redo log 和binlog 的数据到内存时binlog 是无法恢复的。虽然 binlog 拥有全量的日志但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)哪些数据还没有。 比如binlog 记录了两条日志 给 ID2 这一行的 c 字段加 1 给 ID2 这一行的 c 字段加 1 在记录 1 入表后记录 2 未入表时数据库 crash。重启后只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘哪条没有写入磁盘不管是两条都恢复至内存还是都不恢复对 ID2 这行数据来说都不对。 但 redo log 不一样只要刷入磁盘的数据都会从 redo log 中抹掉数据库重启后直接把 redo log中的数据都恢复至内存就可以了。
从架构师层面做mysql调优 1.mysql优化大方向来讲从下到上主要分为3个方面架构调优、mysql调优、硬件和os调优。 越往上走难度越来越高收益却是越来越小。 2.从MySQL执行全流程考虑性能优化
