MySQL实战四十五讲笔记

1.        第一天：SQL查询语句是如何执行

   1. SQL可以划分成：Server层、存储引擎层次
      1. Server层：包括连接器、分析器、优化器、执行器等（涵盖Mysql的大部分核心功能），包含内置函数等，所有跨存储引擎的功能都是在该层实现
         1. 具体实现功能
            1. 连接层：负责与客户端连接，获取权限 、管理和维持连接
               1. 连接完成后，若客户端没有后续操作，数据库处于空闲状态。若客户端长时间未连接会自动将其断开（Wait_timeout控制）默认8小时
               2. 登入的命令行格式：（连接命令）

                   

                  1. 
               3. 数据的连接方式：长连接、短连接
                  1. 长连接：连接成功后，客户端若有持续的请求，则使用同一个连接
                     1. 长链接：优点:减少连接配置，从而减少相应的系统开销
                     2. 缺点：由于长期连接，执行过程中使用的临时内存占用过大，导致容易出现（OOM）的现象
                     3. 解决办法
                        1. 定期断开长连接：在判断是否执行一个较大内存的查询后，断开，等到下次查询需要后再连接
                        2. 在MySQL5.7版本后，在执行比较大的内存后，，使用mysql_reset_connection 俩重新初始化连接资源
                  2. 短连接：客户端执行几句SQL语句后就断开，等到下次查询再重新建立

            2. ---

               查询缓存

               1. 查询缓存是会对之前查询的结果以Key -value的形式保存起来的临时数据区，当数据库执行更新操作（ddl时），缓存则会被全部更新

               2. 执行流程

                  1. 数据库通过语法分析器分析出SQL语句所要表达的含义，若是select等查询语句，则优先进行到缓存中查询是否之前有查询过（速度较快），若缓存中不存在该数据，则会进行到数据库中进行查找

                  2. 特点：查询缓存的命中率低，适合使用数据变化不多的静态表

                  3. 设置参数：query_cache_type 设置成DEMAND （默认的sql语句查询就不会使用查询缓存）

                     ---

            3. 分析器
               1. 含义：将sql语句进行解析，明确该语句的目的和执行要求
               2. 词法分析：会将sql语句的字符串识别出代表什么含义
               3. 若语法错误，则会报出相应的提示：You have an error in your SQL syntadx 
                  1. 重点关注：user near的内容
            4. 优化器：优化器通常是在使用多表查询的时候进行，它决定着使用什么的索引或者是多表查询时，决定各表的连接顺序
               1. 优化器的选择索引以及具体操作后续描述
            5. 执行器：执行相应的语句
               1. 执行流程：
                  1. 判断用户的表是否拥有相应的权限
                  2. 若用户拥有权限，则继续执行，通过表所定义的引擎，使用引擎定义的接口
                  3. 执行流程
                     1. 通过条件进行搜索，若不符合条件则跳过，符合条件加入结果集
                     2. 调用引擎的接口的下一行，重复相同的逻辑，直至执行到最后一行
                     3. 将结果集返回给接口
                  4.   注意在数据库的慢查询日志中存在：row_examined 字段。表示语句执行是扫描了多少行
      2. 存储引擎：负责数据的存储和提取（插件式）
         1. 类似：Mysam、Innodb、Memory        （MySQL的默认存储引擎（5.7版本后）是Innodb

---

1. 第二讲：日志系统：SQL的更新语句是如何实现

   1. 实现背景       
      1. 在执行更新流程时，首先缓存会被全部更新，与此同时，会启动相应的日志模块，用于记录当前的操作步骤。由于IO操作的速度过慢，系统开销大，所以对于操作，优先使用日志进行记录，而后在同步到数据库中。
   2. 日志模块
      1. redo log（重做日志）（引擎层的日志）
         1. Mysql在执行更新操作时，Innodb 引擎会先将记录记录到redo log中，等到更新完成后，Innodb会在合适的时机将记录更新到磁盘
         2. 若更新操作产生的记录过多，Innodb会将最先记录更新到磁盘中，并且清除，用于存储新记录的日志
         3. redo log的大小是固定的，其中 write pos 表示当前记录的位置（一边写入一边移动）checkPoint 表示当前需要清除的位置
         4. crash-safe 由于存储redo-log，即使数据库出现异常重启，之前的记录也不会丢失
      2. binlog（归档日志）（server 层的日志）
         1. 为什么拥有两套日志
            1. Innodb是以插件的形式补充到MySQL中，原本的MySQL使用myisam没有crash-safe的能力，只能依靠binlog进行记录
         2. 两者的区别（binlog、redo log）
            1. binlog是MySQL的server层实现，所有引擎都可以使用，而redo log只能是Innodb使用
            2. redo log 是物理日志，记录在数据页进行说明修改，binlog是逻辑日志，记录的是原始逻辑
            3. redo log 是循环写，大小固定 binlog 是可以追加写，写完一页后会再写一页
         3. 执行逻辑
      3. 两阶段提交
         1. redo log将数据更新到磁盘时，需要将其拆封成两个部分（prepare 和commit），目的是让两份日志之间的逻辑一致（维持数据逻辑一致性的常用方法）
            1. 恢复操作的流程
               1. 通过全量备份获取到上一阶段的备份，将其恢复到数据库
               2. 将数据库从备份的节点开始，从备份的binlog依次取出，回复到误删的表的时刻
            2. 使用场景
               1. 误删除数据时候，需要恢复数据时进行操作
               2. 在需要扩容的时候需要搭建备库来增加读能力，通常使用全量备份+binlog来实现，未使用会导致出现备份库与主库出现“主从数据库不一致”
            3. 使用的流程

                

               1. 

---

1. 第三讲：事务的隔离

   1. 事务的特性
      1. ACID(原子性、一致性、隔离性、持久性)，其中事务的隔离属于I（ISOLATION）
   2. 数据库出现的问题：脏读、可重复读、幻读
   3. SQL标准的隔离级别
      1. 读未提交：事务未提交的时候，出现的变更就可以被其他事务看到
      2. 读已提交：事务在提交之后，才能被其他的事务所看到
      3. 可重复读：一个事务在执行过程中看到的数据和启动时候看到的数据是一致，只有当前的事务提交后，才能看别人更新的数据
      4. 串行化：对同一行记录加锁（写锁和读锁）出现读写锁互斥的时候，后访问的事务必须等前面的事务提交后才能执行
      5. 在实现过程中
         1. 可重复读：在事务创建的时候创建相应的视图，在未提交之前都是使用着这个的视图的逻辑结果为准
         2. 读已提交：在执行SQL语句的时候才创建的
         3. 读未提交：是直接依据数据库上记录的数据来进行返回的
         4. 串行化   ：直接通过加锁的方式避免并行访问
      6. 效率问题上：隔离的越严实，就会导致其效率越低
      7. 可重复读的应用场景：对于更新数据，不影响之前的数据的统计的结果（银行月底结账）
   4. oracle 默认的隔离级别是：读已提交 MySQL的默认隔离级别是可重复读
   5. 事务隔离级别的实现（通过回滚进行实现）
      1. MVCC(多版本并发控制):MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
      2. 作用:主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突(不加锁)

      3. MVCC 带来的好处是？
         多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以 MVCC 可以为数据库解决以下问题

         在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
         同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

      4. 1

      5. 详细学习参考这篇：(46条消息) 【MySQL笔记】正确的理解MySQL的MVCC及实现原理_长路漫漫的歇脚处-CSDN博客_mvcc原理

   6. 事务的启动：

      1. 显示启动  ：begin、start transation 提交事务 ：commit 回滚 rollback

      2. set autocommit = 0 关闭自动提交事务 （在执行select 语句的时候会进行事务的自动开启）（通常建议去开启事务 set autocommit =1 ）

      3. 在set autocommit =1 的条件下，显示的开启事务：begin。若使用commit提交则直接就提交，若使用commit work and chain ，则是自动提交事务并且自动开启下一个事务（减少一次交互）

   7. 长事务的影响

      1. 长事务：

         1. 占用锁资源，导致后面进行处于死锁

         2. 长事务会导致回滚段被 大量的保留(系统中会保存着很老的事务视图)，会占据大量的存储资源

         3. 在事务开启的时候，长连接的出现会导致长事务的出现

      2. 查询长事务的方法

         1. information_schema 库中的innodb _ trx来查询长事务（持续时间再60s）

---

第四讲：索引：