文章转载自公众号:江南一点雨,作者:muggle
导读:本文从MySQL架构、MySQL日志、MySQL的MVCC、MySQL索引、MySQL语法分析及优化、执行计划和慢查询日志、主从备份、分布式事务等方面进行了体系化的讲述。
MySQL架构
MySQL分为 server 层和存储引擎
server层
- 连接器:管理连接权限验证
- 查询缓存:命中缓存直接换回查询结果
- 分析器:分析语法
- 优化器:生成执行计划,选择索引
- 执行器:操作索引返回结果
存储引擎
存储引擎负责数据的存储和提取;其架构是插件式的。innodb 在 mysql5.5.5 版本开始成为 mysql 默认存储引擎。
各存储引擎比对:
- InnoDB:支持事务,支持外键,InnoDB 是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据,不支持全文索引。
- MyISAM:不支持事物,不支持外键,MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的,查询效率上 MyISAM 要高于 InnnDB ,因此做读写分离的时候一般选择用 InnoDB 做主机,MyISAM 做从机
- Memory:有比较大的缺陷使用场景很少;文件数据都存储在内存中,如果 mysqld 进程发生异常,重启或关闭机器这些数据都会消失。
sql 的执行过程
第一步客户端连接上 mysql 数据库的连接器,连接器获取权限,维持管理连接;连接完成后如果你没有后续的指令这个连接就会处于空闲状态,如果太长时间不使用这个连接这个连接就会断开,这个空闲时长默认是 8 小时,由 wait_timeout 参数控制。
第二步你往 mysql 数据库发送了一条 sql ,这个时候查询缓存开始工作,看看之前有没有执行过这个 sql ,如果有则直接返回缓存数据到客户端,只要对表执行过更新操作缓存都会失效,因此一些很少更新的数据表可考虑使用数据库缓存,对频繁更新的表使用缓存反而弊大于利。使用缓存的方法如以下 sql ,通过 SQL_CACHE 来指定:
select SQL_CACHE * from table where xxx=xxx
第三步当未命中缓存的时候,分析器开始工作;分析器判断你是 select 还是 update 还是 insert ,分析你的语法是否正确。
第四步优化器根据你的表的索引和 sql 语句决定用哪个索引,决定 join 的顺序。
第五步执行器执行 sql ,调用存储引擎的接口,扫描遍历表或者插入更新数据。
MySQL日志
MySQL日志介绍
mysql 有两个重要日志—— redolog 和 binlog ,redolog 是独属于 innodb 的日志,binlog 则是属于 server 层的日志。下面介绍这两个日志有什么用:当我们更新数据库数据的时候,这两个日志文件也会被更新,记录数据库更新操作。
redolog 又称作重做日志,用于记录事务操作的变化,记录的是数据修改之后的值,不管事务是否提交都会记录下来。它在数据库重启恢复的时候被使用,innodb 利用这个日志恢复到数据库宕机前的状态,以此来保证数据的完整性。redolog 是物理日志,记录的是某个表的数据做了哪些修改,redolog 是固定大小的,也就是说后面的日志会覆盖前面的日志。
binlog 又称作归档日志,它记录了对 MySQL 数据库执行更改的所有操作,但是不包括 SELECT 和 SHOW 这类操作。binlog 是逻辑日志,记录的是某个表执行了哪些操作。binlog 是追加形式的写入日志,后面的日志不会被前面的覆盖。
数据更新过程
我们执行一个更新操作是这样的:读取对应的数据到内存—>更新数据—>写 redolog 日志—> redolog 状态为 prepare —>写 binlog 日志—>提交事务—> redolog 状态为 commit ,数据正式写入日志文件。我们发现 redolog 的提交方式为“两段式提交”,这样做的目的是为了数据恢复的时候确保数据恢复的准确性,因为数据恢复是通过备份的 binlog 来完成的,所以要确保 redolog 要和 binlog 一致。
MySQL的MVCC
事务隔离级别在此略过,相信大部分小伙伴都知道相关的知识了,在这里单单只介绍 mysql 实现事务隔离的原理—— mvcc(多版本并发控制)。在学习 mvcc 之前我需要先介绍快照读和当前读。
快照读和当前读
快照读就是一个 select 语句,形如:
select * from table
在 Repeatableread 事务隔离级别下,快照读的特点是获取当前数据库的快照数据,对于所有未 commit 的数据都不可见,快照读不会对数据上锁。
当前读是对所读数据上悲观锁使其他当前读无法操作数据。当前读 sql 包括:
select ... lock in share modeselect ... for updateinsertupdatedelete
其中后面三个 sql 都是给数据库上排他锁(X锁),而第一个 sql 是给数据库上共享锁(S锁)。X 锁是一旦某个当前读到这个锁,其他当前读则没有对这个事务读写的权利,其他当前读会被阻塞住。而 S 锁是当一个当前读对某条数据上 S 锁,其他当前读可以对该数据也上 S 锁但不能上 X 锁,拿到 S 锁的当前读可以读数据不能改数据。(关于数据库悲观锁乐观锁并发章节会介绍)。
MVCC 原理
innodb 实现快照读和当前读悲观锁的技术就是 mvcc 。innodb 在插入一条数据的时候会在后面跟上两个隐藏的列,这两个列,一个保存了这个行的创建时系统版本号,一个保存的是行的删除的系统版本号。每开始一个新的事务ÿ