Mysql基础架构概述
Mysql基础架构概述
我对mysql基础架构的学习初衷
在我们以往去使用mysql进行增删改查的时候，我们看到的只是输入一条语句，返回一个结果。希望经过学习之后，我们能够了解mysql的基础零件及其作用，当遇到问题的时候能直戳本质，更为快速地定位并解决问题。
Mysql基础架构的组成以及作用
Mysql的基础架构分为Server 层和存储引擎层两部分。在我们的日常使用中，多数针对mysql的操作需要这两部分互相配合，实现我们需要的操作。
Server 层：涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层：负责数据的存储和读取。它的架构是插件式的，主要有支持 InnoDB、MyISAM、Memory 等多个存储引擎。目前mysql数据库在5.5.5版本之后将InnoDB存储引擎指定为默认的存储引擎。也就是说，执行create table 建表的时候如果不指定存储引擎，那么默认以InnoDB引擎创建。当以engine=memory, 来指定使用内存引擎创建表时就选择了其他的存储引擎。不同的存储引擎，对表的存取方式，支持的功能不同。
由如下图片可以看出，不同的存储引擎共用一个 Server 层。
一条查询语句是如何执行的？
连接器
当我们在命令行键入"mysql -h $ ip -P $ port -u $user -p"时我们就调用了连接器。
【注：】写-p后面写入参数容易造成数据库的密码泄露
连接器负责数据库与客户端获取权限，建立连接，维持和管理连接。
它主要的连接过程是这样的：
- 客户端与数据库进行TCP握手
- 连接器开始认证身份（验证用户名密码）
- 认证通过后连接器会去权限表内查询到该用户对应的所有权限
- 这个链接里的权限判断全依赖于此次查询（即使此时管理员给用户修改了权限，也不影响此链接对应的用户的权限更改，只有创建新链接才有新权限）连接创建完成。
注：查看连接状态命令：show processlist
参数wait_timeout用于控制空闲链接的断开时间，默认8小时
如果连接断开之后再请求将报错"Lost connection to MySQL server during query",如果执行请求需要重连。
长连接与短连接
长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
由于数据库的连接是高cost的，建立连接的过程较为复杂，所以建议使用长连接，可以通过连接池维护一些长连接，可以一直重复用。大部分并发业务不建议使用短连接。
但是全部使用长连接之后暴露问题：MySQL 占用内存涨得特别快
那是因为，mysql执行中一些临时数据绑定在连接对象中，因为长连接长时间不断开导致内存占用太多。针对这个问题有以下两种方案：
- 可以使用定时超时断开/判断执行一个内存占用过大的查询后断开
- 在mysql5.7以上版本可通过重新初始化链接来清空（通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。）。
查询缓存
MySQL 8.0 版本直接将查询缓存的整块功能删掉了,但是8.0以下版本都是有本功能的。
查询缓存的作用
之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
查询缓存往往弊大于利
弊：失效频繁，表修改之后查询缓存全清空，更新压力大的时候，命中率低。
优化：提供了按需使用的方式，将参数 query_cache_type 设置成 DEMAND，对于默认语句不使用查询缓存。
当要使用查询缓存时，用SQL_CACHE 显式指定加入查询缓存。
mysql> select SQL_CACHE * from T where ID=10；
分析器
分析器的作用：1. 解析语句，生成解析树 2. 检查语句中的关键词，表，字段是否存在
具体来说，先做词法分析，识别哪些关键字是表名哪些是列名，
再做语法分析，分析这些词组成的语句是否符合语法规则（如果不对，会报You have an error in your SQL syntax）
优化器
优化器的作用：针对分析器的需要选择一个最优的执行方案
比如，在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
优化器选择一个执行效率最高的方案
执行器
执行器的作用：执行优化器决定的“如何做”的方案
- 判断此人对该表有没有查询权限，如果没权限直接返回
注：权限验证不仅仅在执行器这部分会做，在分析器之后优化器之前，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做precheck。而precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。） - 根据表的引擎定义，调用数据库引擎的API来进行数据操作。（在此步骤才会进入引擎，所以引擎是插件形式的）
具体的查询逻辑是这样的：
调用引擎接口取这个表第一行，判断是否符合条件，不是则取下一行；是就加入结果集，直到取到这个表最后一行，然后将结果集返给客户端。
对于有索引的表，调取逻辑相同，只是调用的是引擎的“取满足条件的第一行”这个接口
注：数据库的慢查询日志中可以看到rows_examined 的字段，代表执行器调用多少次
在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同。
一条更新语句是如何执行的
首先，查询语句的那一套流程，更新语句也是同样会走一遍。
与查询流程不一样的是，更新流程还涉及两个日志模块：redo log（重做日志）和binlog（归档日志）
redo log 重做日志(在引擎层，随机IO变成顺序IO)
redo log 也是在磁盘上，这也是一个写磁盘的过程，但是与更新过程不一样的是，更新过程是在磁盘上随机IO，费时。 而写redo log 是在磁盘上顺序IO。效率要高。
为什么使用redo log
举个例子：
当掌柜有个小酒馆经营，当有顾客赊账的时候，他有一个小黑板用来记录。当有人赊账或者还账的时候，掌柜有两种选择：
- 一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；
- 另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。
如果没有小黑板，那么每次老板都需要翻账本，找到此人的记录，再计算好，将结果写回账本。
这样效率会非常低，还是在黑板上记一下方便。
使用redo log解决的问题
对应的，数据库也会有这个问题。如果每一次更新都需要写进磁盘，磁盘找到那条记录然后更新，它的io成本和查找成本都高。
IO成本就是寻址时间和上线文切换所需要的时间，最主要是用户态和内核态的上下文切换。我们知道用户态是无法直接访问磁盘等硬件上的数据的，只能通过操作系统去调内核态的接口，用内核态的线程去访问。 这里的上下文切换指的是同进程的线程上下文切换，所谓上下文就是线程运行需要的环境信息。 首先，用户态线程需要一些中间计算结果保存CPU寄存器，保存CPU指令的地址到程序计数器（执行顺序保证），还要保存栈的信息等一些线程私有的信息。 然后切换到内核态的线程执行，就需要把线程的私有信息从寄存器，程序计数器里读出来，然后执行读磁盘上的数据。读完后返回，又要把线程的信息写进寄存器和程序计数器。 切换到用户态后，用户态线程又要读之前保存的线程执行的环境信息出来，恢复执行。这个过程主要是消耗时间资源。
–来自《Linux性能优化实战》里的知识 SQL执行前优化器对SQL进行优化，这个过程还需要占用CPU资源
为了解决这个问题，mysql有WAL(Write-Ahead Logging)技术，和账本与黑板的思路相同，先写日志，再写磁盘。
mysql存储引擎innodb采用将数据先写入到redo log，然后更新内存，这样就算完成一条更新语句，然后在适当的时候再讲数据更新到磁盘中。 同时该技术能够结解决crash-safe问题，当系统突然崩溃时，可以通过redo log恢复崩溃前的状态。
- “先写日志” 也是先写磁盘，只是写日志是顺序写盘，速度很快
- 我们比较熟悉的是数据库的写前日志（Write Ahead Log, WAL），也就是说，在实际写数据前，先把修改的数据记到日志文件中，以便故障时进行恢复。不过，Redis的AOF 日志正好相反，它是写后日志，“写后”的意思是 Redis 是先执行命令，把数据写入内存，然后才记录日志。
redo log的构成
InnoDB 的 redo log 是固定大小的,如果写满了则要擦除最开始记录的数据。比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。
redo log的写入方式
- write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
- checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
- 它们之间就是还空着的部分，可以用来记录新的操作。如果write pos追上了 checkpoint，那么redolog满了（redolog 是有刷盘机制的，每次 commit或者每几秒进行一次刷盘，并不会一定等到 redolog 满了才会刷盘），需要先擦掉一些记录，把 checkpoint 推进一下（擦掉记录之前，要把记录更新到数据文件，也就是把记录更新到磁盘文件上，这个事情是在系统比较空闲的时候去做的）
关于刷盘机制：
1.后台线程定期会刷脏页
2.清理LRU链表时会顺带刷脏页
3.redoLog写满会强制刷
4.数据库关闭时会将所有脏页刷回磁盘
5.脏页数量过多（默认占缓冲池75%）时，会强制刷
关于crash-safe:当数据库发生异常重启，数据库还是可以通过redo log 和磁盘记录进行数据的修复
redo log 是循环写的，空间固定会用完； binlog 是可以追加写入的。 “追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
- 停下来擦掉记录，会造成请求阻塞吗？我认为会。所以有时数据库执行某些语句会慢。
- 处理擦掉部分的时候还没有写入，如果这时crash难道不会出问题吗？这时crash，磁盘也没写入，所以整体数据一致。
- 如果光写log没写到库里，那不会导致数据实时性有问题吗？为了解决这个问题，出现两阶段提交机制。
binlog 归档日志(在Server层，二进制日志)
binlog 日志只能用于归档，自带引擎MyISAM没有crash-safe能力，所以InnoDB依靠redo log实现crash-safe。
binlog与redo log区别
- binlog是Server层的，所有引擎都能用；redo log是InnoDB带的
- redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑
- redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
REDO的写盘时间会直接影响系统吞吐，显而易见，REDO的数据量要尽量少。其次，系统崩溃总是发生在始料未及的时候，当重启重放REDO时，系统并不知道哪些REDO对应的Page已经落盘，因此REDO的重放必须可重入，即REDO操作要保证幂等。最后，为了便于通过并发重放的方式加快重启恢复速度，REDO应该是基于Page的，即一个REDO只涉及一个Page的修改。 熟悉的读者会发现，数据量小是Logical Logging的优点，而幂等以及基于Page正是Physical Logging的优点，因此InnoDB采取了一种称为Physiological Logging的方式，来兼得二者的优势。所谓Physiological Logging，就是以Page为单位，但在Page内以逻辑的方式记录。举个例子，MLOG_REC_UPDATE_IN_PLACE类型的REDO中记录了对Page中一个Record的修改，方法如下： （Page ID，Record Offset，(Filed 1, Value 1) … (Filed i, Value i) … ) 其中，PageID指定要操作的Page页，Record Offset记录了Record在Page内的偏移位置，后面的Field数组，记录了需要修改的Field以及修改后的Value。
binlog作用
复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
数据恢复：通过mysqlbinlog工具恢复数据
增量备份
binlog具体的用法
update语句的执行流程
1、执行器先树搜索找到对应的行，如果这行在内存中直接返回给执行器，否则从磁盘读入内存返回
2、调用引擎的更新接口，写入这行新数据（N=N+1）
3、引擎接口将数据更新到内存中，将这个操作写入redo log，此时redo log处于prepared状态，返回告诉执行器随时可以提交
4、执行器生成binlog并将其写入磁盘
5、执行器调用引擎的提交事务接口，把redo log改为commit状态，更新完成
将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。
两阶段提交
为了让两份日志保持逻辑一致，所以出现两阶段提交方式。
通过定期的整库备份加上binlog的操作回放可以保证数据的安全性。因为binlog记录的是数据的逻辑操作（原始sql语句），而redolog是数据的物理操作日志，并且非innodb的引擎没有redolog。因此回放的时候回使用binlog进行回放
1 prepare阶段 2 写binlog 3 commit
- 当在2之前崩溃时 重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。 一致
- 当在3之前崩溃 重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份恢复：有binlog. 一致
如果出现误删表，需要找回数据：恢复误操作前的数据库状态到一个临时库，然后用临时库当时的数据，选择性的恢复到线上。在重放binlog之前，需要将binlog中误删除的那个位置前的操作给删除掉，不然还是会执行误删除操作。等于前面的所有操作都白费了，也可以指定重放的位置，重放到误删除操作之前的position。
数据库的扩容
数据库的扩容，即增加备份库来提高系统读数据库的能力的时候，常采取全量备份+binlog实现。假如binlog和redo log记录的事务的逻辑状态不一致，则会导致严重的主从数据库数据不一致问题。
持久化控制：
关于crash-safe:innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。
关于bingo:sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。
一天一备份，和一周一备份差别
在于备份频率。 那么，我觉得从下面这几个角度来看看吧： 1. 数据是否重要？如果重要，那么最好还是一天一备份。 2. 如果数据量产生的很大，那么还是建议一天一备份，毕竟备份是增量式的，如果一周一备份，但是备份过程就要持续一周。。。那么。。。 备份的时候，会影响数据库哪些指标呢？
- 磁盘io
- 网络io
- “最长恢复时间”更短
当然这个是有成本的，因为更频繁全量备份需要消耗更多存储空间，所以这个 RTO （恢复目标时间）是成本换来的，就需要你根据业务重要性来评估了。
事务隔离
事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
在 MySQL 中，事务支持是在引擎层实现的。InnoDB引擎支持事务，MYISAM引擎不支持事务。
事务隔离级别
数据库为了保证数据正确有效的四大特性ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）其中，事务是用来保证隔离性。
数据库的底层实现，redo日志实现持久性，undo日志实现原子性，通过加锁实现隔离性和一致性。
事务隔离级别解决的问题
当数据库上有多个事务同时执行的时候，会出现以下问题：
- 脏读（dirty read）–读到其他事务未提交的数据；
- 不可重复读（non-repeatable read）–前后读取的记录内容不一致；
- 幻读（phantom read）–前后读取的记录数量不一致。
为解决如上问题，数据库提出了四种事务隔离级别
它们的规律是：隔离得越严实，效率就会越低。
四种事务隔离级别
- 读未提交（read uncommitted）:一个事务还没提交时，它做的变更就能被别的事务看到。
- 读提交（read committed）:一个事务提交之后，它做的变更才会被其他事务看到。
- 可重复读（repeatable read）:一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。未提交变更对其他事务也不可见。
- 串行化（serializable ）:对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。 读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。 可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。 串行：我的事务尚未提交，别人就别想改数据。 这4种隔离级别，并行性能依次降低，安全性依次提高。
以下是一个例子：
当隔离级别是读未提交，V1 = 2，V2 = 2，V3 = 2
当隔离级别是读提交，V1 = 1， V2 = 2，V3 = 2
当隔离级别是可重复读，V1 = 1，V2 = 1，V3 = 2
当隔离级别是串行化，V1 = 1，V2 = 1，V3 = 2
什么场景下会用到可重复读隔离级别呢？
可重复读场景，其实就是事务开始以后不希望被打扰。广泛用在数据校对的场景下。
事务隔离是如何实现的
读未提交：直接返回最新结果
读提交：在每个sql语句执行的时候创建一个MVCC(多版本并发控制)视图
可重复读：在创建事务的时候创建一个视图
串行化：通过加锁来控制避免并行访问
那么利用视图具体是怎么做的事务隔离呢？
其实数据库除了记录变更记录，还会记录一条变更相反的回滚操作记录，前者记录在redo log，后者记录在undo log，只有将每次修改都记录下来，才能方便回滚到指定的每次提交。
假设一个值从 1 被按顺序改成了 2、3、4，回滚日志如下图：
在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。
在可重复读隔离级别中，表中的数据其实已经改变，在前面的视图里，需要查找某条记录时，是通过取当前数据，再取视图对应的回滚段回滚到该视图的值。
这里有行锁保证不会出现一个事务把4改到5,然后另外一个事务回滚的情况。
系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。
如何判断事务是否用到回滚日志：
当没有比回滚日志更早的读视图（读视图在事务开启时创建）的时候，这个数据不会再有谁驱使它回滚了，这个回滚日志也就失去了用武之地，可以删除了。
注：Oracle默认的隔离级别是读提交，所以当做Oracle到Mysql的数据迁移的时候需要设成读未提交
配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。eg:show variables like ‘transaction_isolation’;
为什么建议尽量不要使用长事务
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。长事务还占用锁资源，也可能拖垮整个库。
在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。因为清理指的是逻辑上存储这些文件的位置可以被复用了而已，物理文件并没有删除。
在5.7版本支持单独配置undo log的路径和表空间文件。
事务的启动方式
事务有两种启动方式
误用事务启动方式可能导致长事务问题
- 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
- set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。
多一次begin交互问题
第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。
如果使用第一种方式（显式启动），那么可以使用 commit work and chain 语法。
在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务
这样的好处：
- 从程序开发的角度明确地知道每个语句是否处于事务中。
- 省去了再次执行 begin 语句的开销
查找持续时间超过 60s 的事务
在 information_schema 库的 innodb_trx 这个表中查询长事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
如何避免长事务对业务的影响？
从应用开发端来看
- 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
一、查询日志开启
方法一：mysql>set global general_log_file=’/tmp/general.lg’; #设置路径 mysql>set global general_log=on; # 开启general log模式 mysql>set global general_log=off; # 关闭general log模式 命令行设置即可,无需重启 在general log模式开启过程中，所有对数据库的操作都将被记录 general.log 文件
方法二： 也可以将日志记录在表中 set global log_output=‘table’ 运行后,可以在mysql数据库下查找 general_log表
二、查询日志关闭 查看是否是开启状态： mysql> show global variables like ‘%general%’; 关闭 mysql> set global general_log = off; // 关闭查询日志
三、- slow log可以定位一些有性能问题的sql
– general log会记录所有的SQL
- 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
- 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令（该参数是用来控制select语句的最大执行时间,单位毫秒;如果设置为0的话，则证明不设限制。），来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）
从数据库端来看
- 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
- Percona 的 pt-kill 这个工具不错，推荐使用；
- 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
- 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces （用于设定创建的undo表空间的个数）设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。（在mysql第一次初始化的时候就要指定好undo表空间和信息。启动后不可以再次更改。）
扫一扫
4075
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
