读书笔记：Mysql实战45讲 （1-10讲）

总结一下：这本书都讲解了那些知识点：

 增、删、改、查 底层实现和优化
 索引:底层数据结构实现、聚簇索引（主键索引）、二级索引（索引、联合索引、前缀索引、唯一索引）使
    用方法和底层实现
 Mysql锁的使用:表锁、行锁、全局锁
          事务
 MySQL上线后的快速优化（饮鸩止渴）的办法
 MySQL主从一致和高可用优化解决办法
 MySQL的语法：count,join,order by 
 MySQL常见问题 

说一下关系型数据库和非关系型数据库的区别 

非关系型数据库的优势：

性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过像关系型数据库需要进行多表查询,仅仅需要根据key来取出对应的value值即可，性能比较高

可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

使用场景：对这种一致性的要求不是那么的严格，允许有一定的时间间隔,日志、埋点、论坛、博客等

缺点：但是由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询。因此适合存储较为简单的数据。有一些不能够持久化数据，所以需要和关系型数据库结合。

关系型数据库的优势：

复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询

事务支持:使得对于安全性能很高的数据访问要求得以实现。

使用场景：所有有逻辑关系的数据存储

瓶颈：(1 )海量数据的读写效率,对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/o是一个很大的挑战。(2)高扩展性和可用性，在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

关系型数据库的最大优点就是事务的一致性，这个特性，使得关系型数据库中可以适用于一切要求一致性比较高的系统中。比如：银行系统。

 

1.一条SQL查询语句如何执行     

     

        Mysql可以分为Server层和存储引擎层

Server：连接器、查询缓存、分析器、优化器、执行器

存储引擎：支持innodb、MyISAM等多个引擎   engine=memory

 不同的存储引擎公用一个server层

连接器：

  负责跟客户端建立连接、获取权限、维持和管理连接，连接命令中的mysql是客户端工具，用来跟服务端建立连接，在完成经典的TCP握手后，连接器就要开始认证你的身份，然后输入账号密码，通过后会到权限表里面查看你拥有的权限。

注意：在数据库里面，长连接是指连接成功，如果客户端持续有请求，则一直使用同一个连接，短连接是指每次执行很少的几次查询就断开连接，尽量使用长连接

使用长连接会发现Mysql占用内存涨的特别快，这是因为MySQL在执行过程中临时用的内存是管理在连接对象里面，这些资源会在连接断开时候释放，如果长连接累积下来，导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MYSQL异常重启

解决方案： 

   1>定期断开长连接或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连

   2>如果使用5.7或更新版本，可以在每次执行一个比较大操作通过执行mysql_reset_connection重新初始化连接资源，这个过程不需要重连和重新做权限认证，但是会将连接回复到刚刚创建完成状态

查询缓存：

   之前执行过的语句及结果会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询结果，如果没有命中直接继续后面的执行阶段

   但是大多数情况不建议使用查询缓存，因为往往弊大于利 ：因为查询缓存失效非常频繁，只要对一个表的更新，这个表中所有的查询缓存都会被清空，对于更新压力大的数据库来说，查询缓存的命中率特别低。除非是一张静态表，很长时间更新。（关闭：query_cache_type：demand），当确定使用查询缓存的语句，可以用SQL_CAHCE显示指定： select SQL_CACHE * from T where ID=10                  但是在MYSQL 8.0版本直接将查询缓存整块功能删除掉了

分析器：

  先做词法分析，比如输入由多个字符串和空格组成的一条SQL语句，MYSQL需要识别里面字符串分别是什么，代表什么,从输入的关键字如select，识别出来这是一个查询语句，再把字符串 “T” 识别表名 id识别为列ID

再做语法分析:语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

   分析器负责词法分析和语法分析，构造一颗解析树，整棵树只确保没有语法错误（语法分析），比如检查标识符是否有效，语句是否闭合等等

优化器：

  优化器在表里面如果多个索引的时候，觉得执行哪个索引等等为sql选择执行计划

执行器： 

   首先判断有没有执行查询的权限，（在工程实现上，如果命中查询缓存，会在查询缓存返回结果时候做权限认证，查询也会在优化器之前调用precheck验证权限），如果有权限打开表，执行器根据表的引擎定义去使用这个引擎提供的接口，然后执行语句，在语句执行过程中扫描多少行获取数据时，就在慢查询日志中rows_examined字段累加的

   但是有些场景，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同

 2.一条SQL更新语句是如何执行的

       在一个表中有更新的时候，跟这个表相关的查询缓存会失效，所以这条语句就会把表上所有缓存结果清空，这是不建议使用查询缓存的原因。   接下来，分析器会通过词法和语法分析出来这是一条更新语句。优化器再决定使用哪个索引给出执行计划。然后，执行器负责具体执行，知道这一行，然后更新

      在更新流程设计两个重要日志模块 redo log (重做日志)  binlog (归档日志)

redo log :

   在Mysql中，如果每一次的更新操作都需要写入磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高，为了解决这个问题，MYSQL利用了WAL技术，全程 write-Ahead Logging,它的关键点就是先写日志，再写磁盘

    具体来说当一条记录需要更新的时候，innodb引擎会把记录写到redo log里面，并更新内存，同时innodb引擎会在适当的时候，将这个操作记录更新到磁盘。 redo log 是固定大小的，如图，从头开始写，写到末尾又回到开头循环写：

               

 write pos 是当前记录的位置，一边写一边后移，写到第三个文件末就回到0号文件开头。checkpoint是当前要擦除的位置

 有了redo log,innodb就可以保证即使数据库发送异常重启，之前提交的记录都不会丢失，因为redo log记录了已经成功提交事务的修改信息，并且把redo log持久化到磁盘，系统重启后读取redo log 恢复最新数据,这个能力称为 crash-safe(redo log是用来恢复数据的 用于保障，并且承担已提交事务的持久化特性)

 日志模块： binlog

    在mysql的两层架构 server层，主要做的是MYSQL功能层面的事情 二、引擎层，负责存储相关的具体事宜。 redo log是innodb引擎特有的日志，而server层也有自己的日志成为bin log (归档日志)

    因为一开始mysql里面并没有innodb，但是myisam没有crash-safe功能，binlog只能用于归档，而Innodb是另一个公司以插件形式引入mysql，所以innodb使用另一套日志系统实现crash-safe能力

    区别： 

     1>redo log 是innodb特有的；binlog是mysql的server层实现的，所有引擎都可以用

     2>redo log 是物理日志，记录的“在某个数据页做了什么修改”，是循环写的，空间固定会用完；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给id=2”这一行的c字段加1，binlog是可以追加的，并不会覆盖以前的日志

     执行器和innodb引擎在执行update内部流程：

  执行器和innodb引擎在执行update语句的内部过程：
   1>执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果这一行所在数据本来就在内存中就直接返回给执行器，否则需要从磁盘读入内存，然后再返回
   2>执行器拿到引擎给的行数据，修改后再调用引擎接口写入这行新数据
   3>引擎把这行新数据值更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态，然后告知执行器执行完成，随时可以提交事务
   4>执行器生成这个操作的binlog,并写入磁盘
   5>执行器调用引擎的提交事务接口，引擎把刚才写入的redo log 改成提交(commit)状态，更新完成

  必须要两阶段提交，这是为了两份日志之间的逻辑一致：
  1>如果先redo log 再binlog
     如果在redo log 写完，binlog还没写完mysql进程异常重启，然后恢复之后，redo log仍然可以数据恢复，但是由于binlog还没写完就creash了，这个时候binlog里面没有记录这个语句。之后的备份日志，也是没有这个语句，如果需要binlog进行恢复临时表的话，就会因为缺少这次更新和原库的值不同
  2>如果先写binlog后写redolog
    如果在binlog写完之后crash，由于redolog还没写完，崩溃恢复以后这个事务无效，所以不会更新。但是再之后用binlog来恢复的时候就多了一个事务出来，与原库的值不同
   其实不只是误操作需要这个过程恢复数据，当扩容的时候用到全量备份加上应用Binlog实现
  
 两阶段提交时跨系统维持数据逻辑一致性常用的一个方案

  物理日志redo log 和逻辑日志 bin log 前者用于保证crash-safe能力
  sync_binlog这个参数设置为1，表示每次事务的binlog持久化磁盘，保证mysql异常重启之后binlog数据不丢失
   一般设置参数 innodb_flush_log_at_trx_conmmit=1 表示每次事务redo log直接持久化到磁盘，保证mysql异常重启之后数据不丢失

扩展：

           1、 为了最大程度避免数据写入时io瓶颈带来的性能问题，MySQL采用了这样一种缓存机制：当query修改数据库内数据时，InnoDB先将该数据从磁盘读取到内存中，修改内存中的数据拷贝，并将该修改行为持久化到磁盘上的事务日志（先写redo log buffer，再定期批量写入），而不是每次都直接将修改过的数据记录到硬盘内，等事务日志持久化完成之后，内存中的脏数据可以慢慢刷回磁盘，称之为Write-Ahead Logging。事务日志采用的是追加写入，顺序io会带来更好的性能优势。

为了避免脏数据刷回磁盘过程中，掉电或系统故障带来的数据丢失问题，InnoDB采用事务日志（redo log）来解决该问题。

2、正是由于binlog有归档的作用，所以binlog主要用作主从同步和数据库基于时间点的还原。redo log 能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失。

参考地址：https://zhuanlan.zhihu.com/p/142491549

3、事务隔离： 为什么你改了我看不见

  事务时保证一组数据库操作，要么全部成功，要么全部失败，在MySql中，事务支持是在引擎层实现的。

  隔离性与隔离级别

  ACID：原子性、一致性、隔离性、持久性
  当数据库有多个事务同时执行的时候，就可能出现脏读、不可重复读、幻度的问题，为了解决这些问题，就有了隔离级别的概念
  SQL标准的事务隔离级别包括：读未提交（RU）、读提交(RC)、可重复读(RR)、串行读(SZ)
  隔离级别越高，效率越低
  读未提交：一个事务还没提交时，它所做的变更就被别的事务看到
  读提交：一个事务提交以后，它的变更才会被其他事务看到
  可重复读：一个事务执行过程看到的数据࿰