mysql45讲笔记

mysql45讲笔记：

• mysql结构：
  

• 长连接和短连接

  • 长连接：如果客户端持续有请求，则一直使用同一个连接。

  • 短连接：指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

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

  • 如何解决：

    1. 定期断开长连接，或者程序里判断执行过一个占用内存的大查询后，断开连接，之后再重连。
    2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验 证，但是会将连接恢复到刚刚创建完时的状态。

• 不建议使用查询缓存：因为查询缓存失效很频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，对于更新操作多的数据库来说，命中率会很低。除非是静态表，才推荐使用。8.0版本后该功能被取消

• 分析器：分析你的语法是否正确并且弄清楚你要查什么（即是什么操作，用到了哪些表的哪些字段等等）

• 优化器：判断执行的先后顺序已经使用哪些索引

• WAL：WAL技术（Write-Ahead Logging）是当有一条记录需要更新时，InnoDB会先把记录写到redo log里面，并更新内存，这个时候更新就算完成。同时，InnoDB会在适当的时候（空闲的时候），把这个操作记录更新到磁盘里面。

• redo log和binlog：

  • binlog没有crash-safe能力，而innodb中的redo log是有crash-safe能力的（原因：将innodb_flush_log_at_trx_commit的值设为1，可以使得每次事务的redo log都直接持久化到磁盘，保证crash-safe）
  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

• 两阶段提交：如上图

  • 过程：redo log写入后会先进入prepare阶段，还未commit，等binlog写入后，再提交事务，处于commit状态。
  • 目的：为了保证两份日志之间的逻辑一致性，如果先提交redolog和先提交binlog都可能导致数据库的状态和用它的日志恢复出来的状态不一致。

• 事务隔离级别的实现

  • 读未提交：直接返回记录上最新的值，没有视图的概念
  • 读提交：视图是在每个SQL语句开始执行的时候创建（即一个sql语句，创建一个视图）
  • 可重复度：在事务启动时创建（即一旦一个事务启动，这个视图就会保持不变，这样可以保证重复读的数据是一样的，也就实现了可重复读）
  • 串行化：用加锁的方式避免并行访问

• MVCC（Multi-version Concurrency Control）多版本并发控制：

  • 假设一个数据从1-2-3-4，那么回滚日志中会存在更改的记录，因此不同时刻启动的事务会有不同的视图，这个数据可能的值有2、3、4，也就是一个记录在系统里面可能会存在多个版本，这就是数据库的多版本并发控制。
  • 回滚日志的删除时间：系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除，因此我们不建议使用长事务，长事务会导致在该事务提交前，数据库里面所有可能用到的回滚记录都必须保留，导致大量占用存储空间。有时候会因为不注意使用长事务（如将auto-commit设为0，这样就会导致之后的查询全都是事务，直到你commit），推荐将auto-commit设为1，显式的开启事务（即使用begin/start tranaction开启，commit结束）

• 主键索引和非主键索引：

  • 主键索引（又叫聚簇索引）的叶子节点存的是整行数据（也就是这一行数据的所有内容都在叶子节点当中），因此通过主键索引查询的话，搜索一颗B+树就能找到相应的数据
  • 非主键索引（又叫二级索引）的叶子节点存放的是主键的值，因此你找到主键的值后再搜索主键的B+树才能找到对应的整行数据（这一过程称之为回表），因此尽量使用主键查询

• 为啥推荐使用自增主键：

  • 自增主键每次在自身ID值上+1，每次加入都是追加操作，不涉及到挪动其他记录，不会触发页分裂，效率高

  • 同时存储空间占用少，假如你用身份证号做主键，每个二级索引的叶子节点占用约20个字节，若用整型，则只需要占用4字节

  • 例外：时候适合用业务字段直接做主键？

    • 答案是KV场景，也就是只有一个索引，且该索引是唯一索引（类似于哈希表），这样直接用Key作为主键，可以只搜索一棵树，避免搜索两棵树。

• 覆盖索引：

  • 查询的结果覆盖在索引当中，不需要再回表
  • 例如你有(name, age)索引，你只需要查某人的age，那么你就select age就行，不要select *，这样就避免了回表

• 索引下推：

  • MySQL5.6引入了索引下推，在联合索引的遍历过程中，会对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
  • 例如：你有个(name, age)的联合索引，你现在要查name like ‘张%’ and age = 10 and ismale = true，这时，当查询都name以张开头时，也会一起判断age是否=10，若不等于10，就不会为了该条记录回表，提高效率

• 关于锁

  • 全局锁：

    • Flush tables with read lock (FTWRL)可以对全局加读锁，其他数据更新，数据定义语句都会阻塞，适用场景是全库逻辑备份。但是这样业务逻辑都会停摆，很不好
    • 为了解决FTWRL导致业务停摆的问题，InnoDB引擎有个自带的逻辑备份工具（mysqldump），当其使用参数–single-transaction时，导数据前会启动一个事务，确保拿到一致性视图。由于有MVCC的支持，这个过程中的数据是可以正常更新的。

  • 表级锁：

    • lock tables … read/write：lock tables 语法除了会限制别 的线程的读写外，也限定了本线程接下来的操作对象。也就是你只加了读锁的话，本线程也是不能写入的。
    • 另一个表级锁是MDL（metadata lock），这个是访问表时自动加的，不用显式使用。MySQL5.5引入的MDL，当对表CRUD时，会加读锁；表结构变更时，会加MDL写锁。读锁不互斥，读写和写写互斥，这样可以保证表结构一致。

• 两阶段锁协议：

  • 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
  • 因此，根据两阶段锁协议，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

• 死锁：如下图

  • 下图中，事务A在等待事务B的id=2的行锁，而事务B在等待事务A的id=1的行锁，就是进入了死锁状态。

  • 解决方案：

  1. 直接进入等待，直到超时（超时时间通过innodb_lock_wait_timeout设置，默认50s）
  2. 发起死锁检测，发现死锁时，主动回滚死锁链条上的某一事务，让其他事务得以执行。（将innodb_deadlock_detect参数设置为on，默认也是on）

• 对于热点行更新的解决办法（例如抢票的场景）

• 不能采用前面两种死锁的解决方案，第一种50s时间太长不可取，第二种并发量过大，死锁检测成本高（O(n)）CPU消耗大，也不可取。

  • 解决办法：

    • 临时关闭死锁检测，在代码和业务层面保证不会出现死锁（风险大，难度高）
    • 控制并发度：可以通过中间件实现，基本思路是对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
    • 也可以再业务层面优化逻辑，例如抢票业务中，可以将票随机分成10行，所有票是这10行的和，但是这样需要对业务逻辑进行详细设计，包括退票逻辑等。

• 数据版本的可见性规则：

  • innoDB每一行都有一个隐藏列trx_id，记录这一行数据不同的版本和每个版本的更新的事务id（transaction id），这也是MVCC的实现原理。因此一个事务启动后，他会构造一个数组，用来保存这个事务启动瞬间，当前正在活跃的所有事务id（即启动了但还没提交的事务），数组中最低的id称之为低水位，最高的id+1称之为高水位，所有的row trx_id分为三种情况，若小于低水位，则肯定可见，若大于高水位，则肯定不可见，若在低水位和高水位间，则看row trx_id是否在数组中，若在，则说明在事务启动的时刻，该版本还未提交，不可见，若不在，则说明已经提交，可见。
  • 对于可重复读，查询只承认在事务启动前就已经提交完成的数据； 对于读提交，查询只承认在语句启动前就已经提交完成的数据；

• 当前读：

  • 在事务中，每次更新数据前，都会读取当前的数据，称为“当前读”，可以理解为对该行数据取已经提交的最新版本的row trx_id的值（不一定是最大的row trx_id，这里要理解），再进行更新。