极客时间-MySQL实战45讲
1.基础架构：⼀条SQL查询语句是如何执⾏的？
1.1 SQL语句执⾏过程
Server 层包括连接器、查询缓存、分析器、优化器、执⾏器等，以及所有的内置函数（如⽇期、时间、数学和加密函数等），所有跨存储引擎的功能，⽐如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。InnoDB、MyISAM、Memory 等多个存储引擎。InnoDB 在5.5.5后成为默认存储引擎
1.2 SERVER 层
连接器
负责跟客户端建⽴连接、获取权限、维持和管理连接。连接命令⼀般是这么写的：
mysql -h$ip -P$port -u$user -p
如果⽤户名密码认证通过，连接器会到权限表⾥⾯查出你拥有的权限。之后，这个连接
⾥⾯的权限判断逻辑，都将依赖于此时读到的权限。对这个⽤户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使⽤新的权限设置
processlist 参数查看连接⽤户
wait_timeout 参数设置空闲连接⾃动断开
在使⽤中要尽量减少建⽴连接的动作，多使⽤长连接
长连接导致内存占⽤的解决⽅案：
1. 定期断开长连接。使⽤⼀段时间，或者程序⾥⾯判断执⾏过⼀个占⽤内存的⼤查询后，断开连接，之后要查询再重连。
2. 如果⽤的是 MySQL 5.7 或更新版本，可以在每次执⾏⼀个⽐较⼤的操作后，通过执
⾏ mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权
限验证，但是会将连接恢复到刚刚创建完时的状态。
查询缓存
MySQL 拿到⼀个查询请求后，会先到查询缓存看看，之前是不是执⾏过这条语句。之前执⾏过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。查询缓存的失效⾮常频繁，只要有对⼀个表的更新，这个表上所有的查询缓存都会被清空。
参数 query_cache_type 设置成 DEMAN，对默认SQL语句都不使⽤查询缓存，对于要使⽤查询缓存的语句，使⽤ SQL_CACHE 显⽰指定，例如
SELECT SQL_CACHE * FROM T WHERE ID=10;
MYSQL 8.0 后不⽀持查询缓存
分析器
1.词法分析：从"SELECT" 识别是查询语句，把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”
2.语法分析：根据与语法规则判断SQL语句是否满⾜MySQL语法
⼀般语法错误会提⽰第⼀个出错位置，要关注的是紧接“use near”的内容
优化器
优化器在表⾥有多个索引的时候，决定使⽤哪个索引；或者在⼀个语句有多表关联（join）的时候，决定各个表的连接顺序
执⾏器
1.在执⾏之前，判断时候有执⾏的权限
2.命中查询缓存，在返回结果时做权限验证
3.查询会在优化器之前调⽤precheck验证权限
举例：
SELECT * FROM T WHERE ID=10
执⾏器流程：
1.调⽤ InnoDB 引擎接⼝取这个表的第⼀⾏，判断 ID 值是不是 10，如果不是则跳过，如
果是则将这⾏存在结果集中；
2. 调⽤引擎接⼝取“下⼀⾏”，重复相同的判断逻辑，直到取到这个表的最后⼀⾏。
3. 执⾏器将上述遍历过程中所有满⾜条件的⾏组成的记录集作为结果集返回给客户端。
对有索引的表，第⼀次调⽤的是“取满⾜条件的第⼀⾏”这个接⼝，之后循环取“满⾜条件的下⼀⾏”这个接⼝，这些接⼝都是引擎中已经定义好的。
在数据库慢查询⽇志中看到 rows_examined 字段
⼩结
如果表 T 中没有字段 k，⽽你执⾏了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ inunix操作系统设计pdf下载
‘where clause’” 这个错误是在我们上⾯提到的哪个阶段报出来的呢？
2.⽇志系统：⼀条SQL更新语句是如何执⾏的？
2.1 更新语句的执⾏流程
UPDATE T  SET  c=c+1 WHERE ID=2;
1.先连接器连接
2.分析器通过词法和语法分析是⼀条更新语句
3.优化器决定要是使⽤ID的索引
2.2 ⽇志模块
2.2.1⽇志模块：redo log
类⽐《孔⼄⼰》的掌柜记账 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写⽇志，再写磁盘。
redo log 是InnoDB 引擎特有的，从开头写到末尾⼜回开头循环写，有crash-safe能⼒
write pos 是当前记录的位置，⼀边写⼀边后移，check point 是当前要擦除的位置，也是往后推移并且
循环的，擦除记录前要把记录更新到数据⽂件
2.2.2 重要⽇志模块 binlog
binlog是Server层的归档⽇志，没有crash-safe能⼒
两种⽇志的不同：
2.3 Update语句内部流程
1.执⾏器先引擎去ID=2这⾏，ID是主键，引擎⽤树搜索到这⾏，如果内存存在数据，直接返回给执⾏器，否则需要先从磁盘读⼊内存，再返回
2.执⾏器拿到引擎给的⾏数据，将该值加上1，得到新的⼀⾏数据，再调⽤引擎接⼝写⼊这⾏新数据
3.引擎将这⾏新数据更新到内存，同时将更新操作记录到redo log，redo log处于prepare状态，然后告知执⾏器执⾏完了，随时可以提交事务。
4.执⾏器⽣成这个操作的binlog，并把binlog写⼊磁盘
5.执⾏器调⽤引擎的提交事务接⼝，引擎把刚刚写⼊的redo log改成提交（commit）状态，更新完成
z型檩条檩托板方向
redo log和binlog都可以⽤于表⽰事务的提交状态，⽽两阶段提交就是让这两个状态保持逻辑上的⼀致
2.4 ⼩结
redo log⽤于保证crash-safe能⼒。inoodb_flush_log_at_trx_commit 参数设置成1，表⽰每次事务的redo log都直接持久到磁盘。可以保证Mysql异常重启后数据不丢失
sync_binlog 参数设置成1的时候，表⽰每次事务的binlog都持久化到磁盘，可以保证MySQL异常重启后binlog不丢失ssh框架怎么搭建
2.5问题
在什么场景下，⼀天⼀备会⽐⼀周⼀备更有优势呢？或者说，它影响了这个数据库系统的哪个指标?
好处是“最长恢复时间”更短。
在⼀天⼀备的模式⾥，最坏情况下需要应⽤⼀天的 binlog。⽐如，你每天 0 点做⼀次全量备份，⽽要恢复出⼀个到昨天晚上 23 点的备份。⼀周⼀备最坏情况就要应⽤⼀周的 binlog 了
因为更频繁全量备份需要消耗更多存储空间，所以这个 RTO 是成本换来的，就需要你根据业务重要性来评估了
3.事务隔离：为什么你改了我还看不见？
3.1 隔离性与隔离级别
隔离的越严实，效率就会越低
SQL标准的事务隔离级别：
1.读未提交（read uncommitted）：⼀个事务还没提交，它的变更就能被别的事务看到
2.读提交（read committed）:⼀个事务提交了之后，它的变更才会被其他事务看到
3.可重复读（repeatable read）：⼀个事务执⾏过程中看到的数据，总是跟这个事务在启动时看到的数据是⼀致的，在可重复读隔离级别下，未提交的变更对其他事务也是不可见的
4.串⾏化（serializable）：对于同⼀⾏记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突的时候，后访问的事务必须等前⼀个事务执⾏完成，才能继续执⾏
举例：
create table T(c int) engine=InooDB;
mysql语句的执行顺序insert into T(c) values(1);writeminidump是什么意思
在不同隔离级别下V1、V2、V3的值
隔离级别值返回
读未提交V1=2,V2=2,V3=2
读提交V1=1,V2=2,V3=2
可重复读V1=1,V2=1,V3=2
串⾏化V1=1,V2=1,V3=2
串⾏化：事务B执⾏“将1改成2”的时候，会被锁住，直到事务A提交后，事务B才可以继续执⾏
实现上，数据库会创建视图，访问的时候⼀视图的逻辑结果为准
可重复读：在事务启动时创建，整个事务存在期间都⽤这个视图
读提交：在每个SQL语句开始执⾏时创建
读未提交：直接返回记录上的最新值
纯flutter开发的app串⾏化：直接⽤加锁的⽅式避免并⾏访问
配置⽅式：
将启动参数 transaction-isolation 的值设置成 READ-COMMITTED
3.2 事务隔离的实现
在MySQL中，实际上每条记录在更新状态的时候都会记录⼀条回滚操作，记录上的最新值，通过回滚操作，都可以得到前⼀个状态的值
即使现在有另外⼀个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。