本文参考了极客时间里林晓斌老师的【MySQL实战45讲】
Server层包含MySQL的大多数核心服务,和所有内置函数,所有跨存储引擎功能的实现
存储引擎层负责数据的存储和提取,包括InnoDB、MyISAM、Memory等多个存储引擎,InnoDB从MySQL5.5.5版本开始成为了默认存储引擎,可以自由选择存储引擎:engine=InnoDB
create table `t` {
}engine=InnoDB
连接器负责跟客户端建立连接、获取权限、维持和管理连接
mysql -h 主机名 -P 端口号 -u 用户名 -p密码
例如:mysql 【-h localhost P 3306】 -u root -p564445 ,若是登录本地的,【】内的可以不写。-p和密码中间不能加空格,也可以-p之后直接回车再输入密码,这时候密码是隐藏的,安全性更高
MySQL拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式,被直接缓存在内存中。key是查询的语句,value是查询的结果。如果查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。如果查询命中缓存,MySQL不需要执行后面的复杂操作,就可以直接返回结果,效率更高。
但查询缓存更适合业务上有一张静态表的情况,很长时间才会更新一次,如果更新太频繁,表上的查询缓存会被清空,导致的使用率不高。MySQL8.0版本直接将查询缓存的整块功能删掉了,从8.0版本开始彻底没有这个功能了。
分析器会对语句做“词法分析”,识别里面的各种字符代表什么,语法规则是否正确等等,一般语法错误会提示第一个出现错误的位置
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。不同的执行顺序会导致效率的高低,通常会选择效率更高的情况,但是也会出现选错的情况
建表
create table `T`(
ID int primary key,
c int
)engine=InnoDB;
将ID=2的值加1
update T set c=c+1 where ID=2;
执行流程
更新流程和查询流程大体上的执行顺序相似,但是更新流程还涉及到两个重要的日志模块:redo log(重做日志)和bin log(归档日志)
先分析问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程IO成本、查找成本都很高
这时候就需要redo log了,当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到redo log里,并更新内存,这个时候更新就算完成了。InnoDB引擎会在空闲的时候,将这个操作记录批量更新到磁盘里面,减少对磁盘的IO操作
这其实就是MySQL里经常说到的WAL技术,WAL的全称是Write-Ahead-Logging(预写式日志),它的关键点就是先写日志,再写磁盘
InnoDB的redo log大小固定,比如可以配置一组4个文件,每个文件1GB,总共4GB,从头到尾循环写入,如下图
write pos是当前正在记录的位置,从0号文件到3号文件,然后循环回0号文件,checkpoint是将当前数据更新到磁盘。
有了redo log,InnoDB可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,称为crash-safe
上面的redo log是InnoDB存储引擎层特有的日志,而binlog则属于Server层。因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM,但是MyISAM没有crash-safe的能力,binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的,既然只依靠binlog是没有crash-safe能力的,所以InnoDB使用另外一套日志系统,也就是redo log来实现crash-safe能力
redo log和binlog的三个不同点:
现在再来看看加上和两个日志模块后,update语句在执行器的执行流程
上述最后三步中,将redo log的写入拆成了两个步骤:prepare和commit,即两阶段提交,两阶段提交的目的是:为了让两份日志之间的逻辑一致
先来看看数据恢复过程。binlog会记录所有的逻辑操作,如果设定的是可以恢复一个月,那么备份系统中一定会保存最近一个月的所有binlog,前面讲到binlog可以追加写入,并不会覆盖以前的日志,当要找回数据时:
为什么一定是两阶段提交呢?
可以使用binlog替代redo log进行数据恢复吗?不可以,Innodb利用WAL技术进行数据恢复,write ahead logging技术依赖于物理日志进行数据恢复,binlog不是物理日志是逻辑日志,因此无法使用
可以只使用redo log而不使用binlog吗?不可以,redo log是循环写,写到末尾要回到开头继续写,这样的日志无法保留历史记录,会被覆盖掉,无法进行数据复制
由于redo log和binlog是两个独立的逻辑,如果不用两阶段提交,要么就是先写完redolog再写binlog,或者采用反过来的顺序。若在写完第一个日志后,第二个日志还没有写完期间发生了crash,数据库的状态可能和用日志恢复出来的数据库的状态不一致,下面是不采用两阶段提交的情况:
由上述分析可见,如果不采用两阶段提交,数据库的状态就有可能和用它的日志恢复出来的库的状态不一致
采用两阶段提交后:
在MySQL中,事务是在引擎层实现的,MySQl的原生引擎MyISAM不支持事务,而InnoDB支持。
ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)
问题 | 描述 | 举例 |
---|---|---|
脏读 | 当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个修改了但未提交的数据,并且使用了这个数据 | 张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。与此同时,事务B正在读取张三的工资,读取到张三的工资为8000。随后,事务A发生异常,而回滚了事务。张三的工资又回滚为5000。最后,事务B读取到的张三工资为8000的数据即为脏数据,事务B做了一次脏读 |
不可重复读 | 在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读 | 在事务A中,读取到张三的工资为5000,操作没有完成,事务还没提交。与此同时,事务B把张三的工资改为8000,并提交了事务。随后,在事务A中,再次读取张三的工资,此时工资变为8000。在一个事务中前后两次读取的结果并不致,导致了不可重复读 |
幻读 | 当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入/删除一行新数据。那么,以后就会发生操作第一个事务的用户发现表中多出/丢失的数据行,就好象发生了幻觉一样 | 目前工资为5000的员工有10人,事务A读取所有工资为5000的人数为10人。此时,事务B插入一条工资也为5000的记录。这是,事务A再次读取工资为5000的员工,记录为11人。此时产生了幻读 |
为了解决事务并发问题,就有了“隔离级别”的概念,隔离得越严实,即隔离等级越高,效率越低。Oracle数据库的默认隔离级别是读已提交,MySQL数据库的默认隔离级别是可重复读,可以参考这篇文章:MySQL数据库的默认隔离级别为什么是可重复读?
隔离级别 | 描述 | 出现脏读 | 出现不可重复读 | 出现幻读 | 加锁 |
---|---|---|---|---|---|
读未提交(read uncommitted) | 即能够读取到没有被提交的数据 | 是 | 是 | 是 | 否 |
读已提交(read committed) | 即能够读到那些已经提交的数据 | 否 | 是 | 是 | 否 |
可重复读(repeatable read) | 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的 | 否 | 否 | 是 | 否 |
串行化(serializable) | “写”会加“写锁”,“读”会加“读锁”,不管多少事务,运行完一个事务的所有子事务之后才可以执行下一个事务 | 否 | 否 | 否 | 是 |
举个例子
以可重复读为例,在MySQL中,每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。假设一个值从1按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录
当前值是4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的read-view。如图中看到的,在视图A、B、C里面,这一个记录的值分别是1、2、4,同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制 MVCC(Multi-Version Concurrency Control),对于read-view A,要得到1,就必须将当前值依次执行图中所有的回滚操作得到,即使现在有另外一个事务正在将4改成5,这个事务跟read-view A、B、C对应的事务是不会冲突的
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致占用大量存储空间,这是一个不容忽视的问题。
MySQL的事务启动方式有以下几种
有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务。因此,建议使用set autocommit=1,通过显式语句的方式来启动事务,在autocommit为1的情况下,用begin显式启动的事务,如果执行commit则提交事务,可以避免长事务