MYSQL 45 讲 学习笔记

MYSQL 45讲学习笔记

一、一条sql是怎么执行的

• mysql的架构：

  客户端

  连接器

  查询缓存

  分析器

  优化器

  执行器

  存储引擎

二、一条SQL更新是如何执行的

重要的日志模块redolog、binlog

• redolog

  这里举了一个孔乙己老板赊账记账的故事。

  一条记录需要更新的时候，innoDB引擎会先把记录写到redo日志中，然后更新内存，这时候算是完成了，等到系统比较空闲的时候，才把redo日志更新到磁盘中。

  relog的特点：只有在innodb中才有，是几个文件，循环写入（也就是如果满了，那么就把记录更新一下，然后擦除，然后补上去）

• binlog

  （1）binglog是Server层的日志，其实也就是执行器记录的日志。

  （2）这里先说一下更新一行记录做的过程：重点🚨

  • 执行器找引擎要id=2的那一行记录，拿到以后，给他+1，
  • 之后调用引擎的接口，引擎更新把这条 记录更新到内存中，并记录把这操作记录到redolog中，redolog处于prepare状态，告诉执行器我执行完了，随时可以提交事务。
  • 执行器生成这个操作的binlog日志，然后调用引擎的提交事务接口，引擎也把redolog改为提交commit状态，更新完成。

  （3）根据log的写入顺序我们可以知道1个事情：

  • 如果没有redolog，在引擎执行命令的时候发生异常，是没法恢复的，因为binlog还没有记 录擦操作。cash-save

  （4）那么为什么redolog有两个状态？

  • 结合两个日志的写入顺序，假设在引擎处redolog已经写了，然后系统挂掉了，那么binglog还没记录，这个时候如果系统重启时会读redolog的东西的，但是你binlog你没有这个记录呀，当你加入要创建一个从库，使用binlog创建，就会少一条操作记录，主从数据库内容就不一样了。
  • 上面一点讲到的从库，备库是经常做的事情，他们一般使用全量备份+binlog归档来实现，这个时候binglog如果少了一条操作记录，主从就会不一致的。

  （5）说一说为什么DBA能够将数据库恢复到任何时间

  • 其实就是第一个有一个备份库，第二个就是依靠binlog

三、隔离问题

读未提交 读已提交 可重复度 串行化（表锁）

那么其实本质上是数据库创建了个视图，这样就能做到可重复度和读已提交

在MYSQL中实际上，每条记录在更新的时候都会记录一条回滚操作。因此也会存在一个问题，这记录回滚操作一定不能太多，当你是一个长事务的时候就会让这些回滚记录非常大，要避免这种情况，其实就是当你开启事务的时候，最后一定要提交或者回滚。重点🚨

四、深入浅出索引（一）

• 分为主键索引和普通索引，在innodb中，表都是根据主键顺序存放的。假设一个表中有主键，有普通索引，如果用普通索引去查数据，实际过程中，他是先用普通索引找到对应的主键，然后根据主键的索引去找到应对的记录。其实原因就是主键索引存放的是整行数据，普通索引存放的是对应主键的信息。这个过程也叫做重点：回表。**因此为了提高效率，我们应该用主键来查询数据。**也正是因为有回表，所以innodb一定要有主键，这是他的B+树结构决定的，而MYisam就不需要了，也是因为他的B+树存的是地址导致的，这个后面我们会讲到。🚨
• 看了上面，我们知道普通索引存的其实是主键信息，那么其实在某种情况下，主键的确定也很重要，比如一个表要记录身份证，你会把身份中作为主键吗？应该不可以的，这样普通索引每个占用的空间就很大，应该使用一般的数字自增就行了。
• mysql的innodb使用B+树，其实也就是N叉树，为什么呢？其实就是二叉树的存放数据太分散了，太分散的数据寻址一定是耗时的，使用N叉树就能避免多次的分区寻址。
• B+树的页分裂。页分裂就是你的新id在索引二叉树中超过了N，这个时候就要分裂，经常的分裂会的导致效率贬低，所以建议使用自增长的主键作为索引，反例就是身份证做主键，每次添加的身份证id太随机了，就经常需要页分裂。在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
• 页合并：如果删除了某个索引的一些值，如果不处理的话，我们要经过的节点会多一点，如果把二叉树重新整顿一下的话，经过的会更少。一般我们会设置，如果节点的数量小于N/2 就和附近的节点合并一下。

五、深入浅出索引（二）

• 覆盖索引：我们知道了有回表这一现象的存在，本质是普通索引存的是主键信息，如果我们要的东西就是主键呢？，这样就不用回表了，这样就提高速度了。

• 最左前缀原则，假如我有了a,b两者的联合主键，请问我是否要单独创建一个a的主键呢?答案是不用，因为B+树的索引机构，可以利用他的最左前缀来定位。比如你要找到张三的信息，（老王，1），（老豆，2）（张三，10），（张三，20），根据联合主键他会迅速找到（张三，10），然后依次向后找符合张三条件的。**就是只要满足最左前缀，那么就可以使用索引，重要🚨。**所以我们在创建联合索引的时候，前后顺序要考虑一下，原则就是可以少维护一个索引，就是不错的。

• 索引下推：比如我们要搜索姓氏是张，年龄为10的男孩，索引是姓名和年龄的联合索引。在mysql5.6之前它会先找到张开头的，然后回表，去对比年龄为10的，5.6以后，能够做到，找到张开头以后，直接对比年龄是否符合要求，然后再回表获取其他信息。这样极大减少了回表次数。

• 我们再仔细研究一下索引的使用情况：也就是在有联合索引的时候，不同语句情况下，到底所有有生效吗？基本基于最左前缀原则

  我们假设有个表：employees.titles表，他的联合主键索引是<emp_no, title, from_date> 我们分别看一下不同sql使用索引的情况：

  • select * from employees.titles where emp_no=‘1’ and titile=‘1’ and from_date=‘2020’;
    • 这个是全列匹配，联合索引的字段都用上了
  • select * from employees.titles where emp_no=‘1’
    • 最左前缀匹配，这样用到了emp_no一个索引
  • SELECT * FROM employees.titles WHERE emp_no=‘10001’ AND from_date=‘1986-06-26’;
    • 这里和上面一样，他缺失了title这个字段，最后只用到了emp_no一个索引。
  • SELECT * FROM employees.titles WHERE from_date=‘1986-06-26’;
    • 这中就不会用到索引 — 索引失效🚨
  • SELECT * FROM employees.titles WHERE emp_no=‘10001’ AND title LIKE ‘Senior%’;
    • 这个会用到索引，但是有要求，%不能在开头，否则失效，只用到一个索引。
  • SELECT * FROM employees.titles WHERE emp_no < ‘10010’ and title=‘Senior Engineer’;
    • 只有第一列的范围才能用到索引，而且只是第一个索引，后面的title都不会走走索引。
  • SELECT * FROM employees.titles WHERE emp_no=‘10001’ AND left(title, 6)=‘Senior’;
    • 对于有函数的是不会用索引的，也就是这一句只用到了emp_no这个索引。

六、B树、B+树的知识

这个博客讲的特别好：http://blog.codinglabs.org/articles/theory-of-mysql-index.html 2011年的，太厉害了，主要的图片也是来自于这篇博客，总之写得很精彩。

• 索引是磁盘上的东西，一般来说很大（我们讲那种索引大的情况），读索引的过程是把索引文件读到内存中，对于索引很大的文件，就会需要分部分来读取。

• 对于平衡二叉树或者是红黑树，物理实现上是数组，相关联的数据有时候是距离很远的，这就会导致你读的索引部分会很多，这样效率会很低的。

• 为了索引而出现的结构就是B树了。

• 有个概念叫做局部性原理，就是磁盘预读，读取磁盘的时候，会把这个地址后面的东西也读取到内存中，所以我们应该把相近的数据放到一起，这样能够一起读取出来，减少io次数。这个原理是属于计算机原理的知识。

• 那么对于B树，我们知道他是N叉书，也就是一个节点存了N个数据，这样就能充分利用这个局部性原理和磁盘预读。

• B树的结构图：他的特点是：

  • 每个非叶子节点（非？）由key和指针组成，key数量是指针数量+1。
  • key从左到右升序
  • 如图我们可以看到比15大，比56小的，依靠他们中间的指针指向下一个节点，这个节点的key一定是小于56，大于15
    

  // 他的搜索伪代码
  BTree_Search(node, key) {
      if(node == null) return null;
      foreach(node.key)
      {
          if(node.key[i] == key) return node.data[i];
              if(node.key[i] > key) return BTree_Search(point[i]->node);
      }
      return BTree_Search(point[i+1]->node);
  }
  data = BTree_Search(root, my_key);
  

• B+树的结构是：一些特点：

  • 他的内节点不存data数据，只有key
  • 他的叶子节点没有指针

• B+树真正使用会进一步改进，如下图：改进点：

  • 带有顺序访问指针，提高了区间访问能力，当我们查询15到30的数的时候，定位到15以后，直接完后找就完事了。这也是为什么说B树找范围能力没有B+树厉害

总结一下：两个问题：

1. 平衡二叉树/红黑树 与 B树的区别是什么？为什么索引要用B树而不用那两个呢？

• 平衡二叉树是二叉树，每个节点只有两个叶子节点，关联的数据可能在不同的位置，读取次数多，效率低下。
• B树就是为了解决这问题而诞生的，他的多叉树特性保证了他读取io的次数会降低，而却他的节点数据是有顺序的，利用了磁盘预读的特点。

2. B树和B+树的区别
   • B+树叶子节点不存放指针，只存放着数据，这样只需要遍历叶子节点以及下一个叶子节点即可。
   • B+树的结构是有序数组链表+平衡多叉树，B树是有序数组+平衡多叉树。也就是B+树的叶子节点是个链表，每个数据指向下一个相邻的数据。
   • B+树的特点决定了他很擅长范围查找，而b树没法和他比较。

• MyISAM和innoDB的索引结构区别

  • myisam索引最后一个叶子节点存放的是地址，普通索引和主键索引是一样的，也是存放地址，地址指向对应的数据库记录。也叫做非聚集索引，索引和数据是分离的。
    

  • innoDB

    对于主键，他存的不是地址，而是整个记录，就是innoDB的数据文件本身就是索引文件，myisam的索引和数据是分离的。普通索引的节点存的内容是主键。
    

• InnoDB和MyISAM分别是怎么存储数据的，优缺点是什么— 后面总结一下，不单是结构问题，还有事务，锁等问题考虑对比。

  MyIsam不支持事务。

七、全局锁和表锁

锁分为全局锁、表锁、行锁

• 全局锁：

  锁住全表，只能进行select查询操作，增删改，ddl都不行，一般用于我们需要备份主库的时候。语法是Flush table with read lock，开启时候就会锁住全部的表，但是如果有用户在我们备份的时候操作怎么办了？只能等我们结束吗？其实不然，结合之前我们讲隔离级别的时候，我们有提到数据库会有回滚记录，或者说是MVCC版本控制，他给你一个视图，你可以对视图操作，实际操作上就是你在锁住全表之前，开启一个事务，就行了，官方自带的逻辑备份工具mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务。

• 表锁：

  表锁分为两种：表锁，元数据锁MDL

  • 表锁：语法是lock tables … read/write，来锁中某个表。假设我们有两个会话AB，如果会话A 执行lock t1 read，那么会话A就只能查询t1表，查询其他表会报read lock没有释放的错误。而会话B能查t1表，也可以查其他表，但是insert这个表会一直被阻塞，直到会话A执行unlock tables。

    也就是在某个会话中lock 了一些表特定的read或者write，那么首先他无法访问其他表了，其次对于他lock的那些表只能进行lock的操作。🚨

    明显这种限制太大了，Myisam还会用这样的方式，但是innoDB有颗粒度更细致的行锁，几乎是不会使用表锁的。

  • 元数据锁metadata lock：是表层面的锁

    这个是数据库自动给我们加的锁，当你有增删改查的时候自动加上读锁。当你有DDL语句的时候，就会自动加上写锁。写锁之间是互斥的，两个DDL写的操作要按照顺序执行。读写锁之间也是互斥的，另外加上MDL不会主动释放锁，只有在事务结束以后才会释放锁，当我们给一个表加字段的时候，就容易导致数据库挂掉。

    解决办法就是，先去看看select * from information_schema.innodb_trx;这表里面的事务，考虑把它停掉，或者是我们的DDL语句设置等待时间。

  这一部分呢，1、只要就是要知道MDL锁对我们新增一个字段的影响，已经我们该怎么做去避免影响。2、就是全局锁主要应用在备份中，一般我们开启事务后，在进行备份比较合适。🚨

八、行锁的功过

行锁就是锁住每一行记录的，是在存储引擎中实现，比如MyISAM就没有行锁，只能用表锁，也就是这个表同 一时刻只能有一个更新操作。这也是他被取代的原因。

• 一个事务掌握一个行锁之后，其他事务要对这行记录操作，会被阻塞，只能等待那个行锁释放。

• 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

  假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这
  个业务需要涉及到以下操作：

  1. 从顾客 A 账户余额中扣除电影票价；

  2. 给影院 B 的账户余额增加这张电影票价；

  3. 记录一条交易日志。

  也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保
  证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务
  中的顺序呢？
  试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。
  因为它们要更新同一个影院账户的余额，需要修改同一行数据。
  根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才
  释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余
  额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

• 死锁问题

  innodb是会自动检测死锁的，也就是如果发生了锁等待，引擎会扫一遍所有锁，看看是不是因为自己的加入而导致的死锁，如果是就会回滚事务，这样别人就能走下去了。

  但是每次扫描太费CPU了，解决方向是减少并发度，可以在中间件里控制范围数据库的并发量，或者是mysql修改源码设置等待队列。

总结一下锁：🚨

1. 全局锁用于数据库的备份，建议开始事务以后锁库然后备份，业务会友好点。
2. 表锁主要是MYISAM在用，lock xx read / write，就只能对这个表进行相应操作。几乎没啥意义。
3. 表锁的MDL是表层面的，所以写指的是DDL，读指的是增删改查。读写锁之间互斥，写锁之间也互斥。MDL在事务提交后释放锁。
4. 行锁也是事务提交以后才会释放，读操作不会有互斥，更新操作就会有互斥情况。

九、事务到底是隔离的还是不隔离的

首先清楚innodb默认是可重复度PR，我们知道这可能会导致幻读。下面我们探讨的就是这么做到的可重复度和读已提交。

前面我们说到了每行数据的多版本控制，MVCC，本质是创建了一个视图，这个视图不是数据库的那个视图，其实是一个快照，或者说是一个up_limit_id，它的意义是：表示我只读取比这个up_limit_id小于或者等于的数据版本。

那么up_limit_id这是个什么东西呢？是个版本号，每一次的事务操作都会生成一个row trx_id，那么每次事务启动的时候会获取最大的那个row trx_id，也就是up_limit_id

还有一种情况会再一次获取up_limit_id版本，就是更新操作，更新操作的执行顺序是：先读取，再更新。（因此我们看pdf里面的Q1的最后结果是3。注意了他的事务C是自动提交的，但是AB都是begin，最后才提交，还有就是你测试的时候，要开多个窗口，才是模拟多个事务）这个比较重要，再写一遍：更新操作的执行顺序是：先读取，再更新。🚨

所以你能知道为什么他可以重复读了吧。开启事务记录up_limit_id------>update就重新获取一次，select就还是原来的id--------->获取正确的版本 。 在可重复读隔离级别下，只需要在事务开始的时候找到那个 up_limit_id，之后事务里的其他查询都共用这个 up_limit_id；

读已提交隔离级别的时候就是：每一次的查询都会更新一下 up_limit_id。

重点再说一遍：更新操作的执行顺序是：先读取，再更新🚨

所以你能知道为什么他可以重复读了吧。开启事务记录up_limit_id------>update就重新获取一次，select就还是原来的id--------->获取正确的版本 。 在可重复读隔离级别下，只需要在事务开始的时候找到那个 up_limit_id，之后事务里的其他查询都共用这个 up_limit_id；

读已提交隔离级别的时候就是：每一次的查询都会更新一下 up_limit_id。

十、MYSQL是怎么保证主备一致的呢？

假设A是主库，B是备库。AB之间会有一个连接的，A内部有个专门的线程专门来通讯备库。A库接受到B库从缓过来的位置，从本地读取binlog，发给B，B拿到以后写道自己本地叫做relay log，B中有专门的线程读取中转日志，来执行。

实际情况AB两个库会设置成互为主库或者互为辅库。但B拿到A的binlog后执行，也会记录binglog，A又拿到B的binlog，这不就循环复制了吗？那么binglog里面有一个server_id，他会看是不是自己这台机生成的binlog，如果是，那么就不会执行。

十一、MYSQL怎么保证高可用

• 上面我们说的是一致性，这里讲的高可用值的是，主从之间的延迟情况。

• 延迟指的是同一个事务，在A库的执行完成的时间和在B库执行完成的时间的差值。那么真正耗时的不是binlog的传递，而是B库读取relaylog执行的快慢。可能导致这个速度的原因有：

  • 有些人会因为备库很少用，而把备库的机器性能配置的很低。当然现在这种情况很少了，因为主备之间经常切换。
  • 备库压力太大了。因为有些人很故意生产的数据库，所以尽可能的优先使用备库去查数据，反而导致备库压力很大。解决办法：
    • 一主多从
    • 把binlog给外部系统，比如hadoop，让他们来做查询的工作
  • 大事务。比如一次删除很多数据
  • 辅库的并行能力

• 那么MYSQL主备切换是个什么过程呢？

  • 确认B库的延时时间是否低于规定（防止高延迟，否则后面同步的时候很慢）
  • 把A库改为readonly
  • 同步A库剩下的binlog，直到B库的延迟时间为0.
  • 把B库改为可读写
  • 把业务请求切换到B库来

  这个叫做可靠性优先策略。

十二、为什么备库延迟几个小时

对于主库压力，使用频率非常高的情况，从库如果延迟了，也许再也追不上主库了。下面我们学习从库的并行复制能力。

其实是什么呢？就是从库的读取relay log是单线程的，并发度不够，在MYSQL5.6以后就改为多线程读取了。

十三、主库出现问题了，从库怎么办

首先看一下一主多从的结构图：主库主要做写操作以及一般小部分读操作。从库主要分到读的操作。AA`互为主备。

当主库A挂掉以后，BCD会指向A`，他会成为写库。

那么有个问题，但A挂掉了，B去连接A`,但是不能保证A 和 A\在那个时刻是一样的呀？当然你可以等到A主从库之间完成同步，但是也可以打开GTID这个东西，在这个模式下一主多从切换就会方便很多。

十四、主从读写分离

一主多从架构的应用场景：读写分离。

读写分离在哪实现分开查询不同数据库。可能是在客户端直连，也就是目前我这个项目的情况，直接选择特定的数据源。第二个就是在客户端和数据库之间搞个代理，代理根据上下文来判断应该去那个数据库。

但是都会导致一个问题，如果我刚刚更新的主库数据，我马上要读取，这个时候从库还没有同步呢，怎么办？这个问题不想主备库之间延迟问题可以避免。这个不能100%避免。有以下几种办法：

• 强制走主库：区分我们的查询，如果对于比如我买完东西，返回以后我一定要看到我的订单，那么就强制他到主库去查。对于比如上新一个产品，晚一点看到也没关系，那么就放在从库去查。看起来这个办法是逃避现实的办法，但是实际用的比较多。但是如果你的需求都是要求实时性的话，这个办法就不太好了，因为相当于没有从库了。
• sleep方案，就是我在从库查询的时候，select sleep(1) 一下，这样很大概率主从完成了同步，但是明显者不太精确，可能本来只要0.5s的同步，硬生生被你拖到了1s。
• 判断主从是否无延迟。直接看延迟时间是否为0，或者是GTID主从库日志的记录
• 更多的不做展开学习…

这几种方案中，有的方案看上去是做了妥协，有的方案看上去不那么靠谱儿，但都是有实际应用场景的，你需要根据业务需求选择。

即使是最后等待位点和等待 GTID 这两个方案，虽然看上去比较靠谱儿，但仍然存在需要权衡的情况。如果所有的从库都延迟，那么请求就会全部落到主库上，这时候会不会由于压力突然增大，把主库打挂了呢？

其实，在实际应用中，这几个方案是可以混合使用的。比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。

总结一下：

上面我们讨论了

• 主备/从之间是怎么保持一致的，binlog，连接，relaylog，循环复制。
• 怎么实现高可用，就是主从之间的延迟情况。一主多从，主从对称配置，辅库少一点大事务，辅库并行复制。
• 主库怎么切换到备库的，延迟时间限制----->主库改为只读----->延迟时间为0----->备库改为写。
• 一主多从模式下，主库挂了，从机怎么连接到备机。GTID 全局事务id。具体没深入研究。
• 读写分离导致的“过期读”解决办法。

十五、到底能不能用join

select * from t1 straight_join t2 on t1.a = t2.a;这个过程是这样的：

• 对驱动表t1拿取一行记录。全部搞下来就是全表扫描
• 每一行记录获取a，然后跟据a去t2表中查，这个查询是树搜索。
• 找到t2中符合条件的行，和T1取出的数据组成一行
• 循环第二第三步，直到走到t1表末尾

明显的驱动表走全表扫描，被驱动表是走的树查找，所有尽可能的驱动表要小一点（更仔细的说是，用各自的条件去看涉及的数据量多还是少）。前提是能够使用到被驱动表的索引。

十六、MYISAM和InnoDB

My：没有行锁，没有MMVC多版本控制，查询会相对快一点

innoDB:有行锁，索引是记录有一定的缓存，有MVCC。

十七、索引失效的情况

• or 让本来有索引的反而不走索引了，除非你每个条件都有索引
• 联合索引如果不是第一个就不会使用到
• 立刻以%开头的，不会用到索引 N%表示N开头，这个会用到索引
• 如果列是字符串的，条件中一定要用引号标注
• 如果mysql优化器判断全表查找比用索引更加快，那么就用全表查找
• <> \ not \ in \ exists \
• 语句包含函数

十八、幻读学习

什么是幻读：第一次读取数据库，根据返回的结果后面的insert语句是没有问题的，但是执行insert以后报错主键冲突，这就是幻读。

解决办法：在一开始select的时候就加上一把行锁。而serialize这个级别就是自动查询的时候自动加行锁，他也是唯一的不会幻读的隔离级别。

这个深入学习要看一下原文了，next-key lock，间隙锁+行锁配合可以解决幻读问题。

十九、锁的分类

• 乐观锁：首先先查出数据（连带着版本），然后进行update的时候再查一次，如果这个时候的version和一开始的version一样，那么就update；java代码应该是一个自旋。

  update table set a='a',version = version + 1 where version = #{version} and id = #{id}

• 悲观锁：
  • 共享锁：读锁。A事务lock in share mode 锁住结果的每一行，A事务可以更新，但是其他事务不能再加读锁，更不能更新。其他会话可以读取。也就是我读取数据的时候，别的事务不允许改变数据。
  • 排他锁：写锁，for update 一个会话中使用这个这个语句，那么其他会话只能查不能改，只有前面那个会话可以改。目的就是我更新的时候别的会话别动。
  • 对于insert、update、delete等操作。会自动给涉及的数据加排他锁；

欢迎大家关注我新建的微信公众号，我会持续分享和更新技术文章！