mysql45讲整理---01-08基础篇

@[TOC]

1.一条sql查询语句是怎么执行的

1.1Server层

连接器：

跟客户端建立连接、获取权限、维持和管理连接（连接过程尽量使用长连接）

长连接导致内存占用解决方案：

1.定期断开连接（使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连）

2.mysql 5.7以上，执行比较大的操作后，执行mysql_reset_connection重连自愿，不需要重连和权限校验，可以回复到刚创建的状态

查询缓存：

mysql拿到一个查询请求后，先去查询缓存看看，之前是不是执行过这条语句。执行过的语句和结构以k-v对的形式存放

但是！8.0以后移除了这个功能：查询缓存失效频繁，对一个表更新，表上的查询缓存就会被清空，所以缓存命中率很低！

除非那张表是一张静态表(比如系统配置表)，很长时间更新一次，才适合用查询缓存。

分析器：（你要做什么？）

词法分析：从”select“识别出是查询语句，把字符串T识别成表T，把字符串”id“识别成列id（就是里面的字符串分别代表了什么意思识别出来） 

语法分析：判断sql是否满足语法规定    如：

mysql> elect * from t where ID=1;

少打个s 报错：You have an error in your SQL syntax

优化器：（你要怎么做？）

有多个索引的适合决定走哪个索引，或者join的时候决定各个表的连接顺序

执行器：

调用Innodb引擎接口依次取表中数据并且判断，把满足条件的记录集作为结果集返回给客户端

执行器调用一次，内部引擎可能扫描了多行引擎扫描行数跟rows_examined并不是完全相同的

小结：

问题：如果表T中没有字段k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答：优化器

1.2存储引擎

2.讲日志系统：一条SQL更新语句是如何执行的

日志模块：

redo log（重做日志）

记录的是物理层面的页，偏移量，修改（包括页的合并，分解），通常由四个1gb内存组成

粉板-账本配合（先记粉板打样再记录账本）的过程就是mysql的WAL技术，全称：Write-Ahead Logging 先写日志，再写磁盘的操作。

write pos 记录当前记录的位置，check point 是当前要擦除的位置，擦除后更新到磁盘，两个点都是边写边后移

作用：1. crash-safe （数据库异常重启，提交的记录不会丢失）

redolog的写入机制：

1. 存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；

2. 写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分；

3. 持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。

 InnoDB提供了innodb_flush_log_at_trx_commit参数，设置每次事务提交时，redo log停留在哪一层（三层都可）

然后innodb有个后台线程，每隔1s，把redologbuffer write到 文件系统的pagecache，然后调用fsync持久化到磁盘

除了后台每秒一次的轮询，海投两种会写redo log

1. 一种是，redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘

2. 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘

MySQL的“双1”配置

一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。

binlog（归档日志）

作用：录着数据行的字段值和/或其新旧变化，常用于数据恢复（全量备份+binlog），数据恢复的适用场景：1.误操作，2.搭建备库 3.主从复制

binlog的三种格式对比：

1.statement：binlog里面记录的就是SQL语句的原文（主备库所使用的索引有可能不一致，而所选的数据不一样，导致主从不一致）

2.row：记录的是真实的主键id和event，保证不会出现主从不一致。缺点：占空间

3.mixed：mysql会自己判断这个sql语句会不会导致主备不一致混合选择上面两个格式。

binglog的写入机制：

先把日志写到binlog cache，事务提交的时候再把binlog cache写到binlog文件中

可以看到，每个线程有自己binlog cache（binlog是不能“被打断的”），但是共用同一份binlog文件

 途中的write和fsync的写入是由sync_binlog控制的，sync_binlog的大小表示n次事务才提交一次fsyn