《MySQL45讲》笔记

  1. 建立连接的过程复杂，应尽量减少建立连接动作，尽量使用长连接。但是长连接不断开就不会释放占用的内存，积累下来可能会导致oom，造成mysql 重启

    1. 定期断开长连接
    2. 每执行一次比较大的操作后，通过执行mysql_reset_connection 来初始化连接资源（仅限于5.7及之后版本）

  2. 查询缓存弊大于利。每次更新就会清空缓存

  3. redo log（重做日志）innoDB特有，记录数据，如c=1，在innoDB引擎空闲时，把这个记录更新到磁盘里

  4. binlog（归档日志）Server 层实现，所有引擎都可以使用，记录语句原始逻辑，如c=0+1

  5. undo log

  6. 两阶段提交，保证数据和逻辑一致性。

    1. redo log ：肚子里有食物
    2. binlog ：吃食物的过程
    3. 先写redo log后写binlog：在写redo log未写binlog 时crash，恢复时，肚子里已经有食物了，但是没吃
    4. 先写binlog后写redo log：在写binlog未写redo log 时crash，恢复时，吃了，但是肚子里没有食物
    5. 无疑上述两种情况都是不合理的

  7. innodb_flush_log_at_trx_commit = 1；每次食物的redo log 都持久化到磁盘，可以保证mysql 异常重启后数据不丢失。

  8. 可重复读隔离级别下，SQL在执行的时候会创建一个视图，避免不可重复度的问题

  9. 尽量不要使用长事务：长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。长事务还会占用锁资源

  10. 建议总是使用set autocommit=1, 通过显式语句的方式来启动事务

  11. 主键索引和非主键索引的区别：非主键索引在非索引覆盖的情况下，会搜索索引树得到主键id，再通过主键id 的索引树搜索到数据，称为回表。

  12. 主键索引一般选用自增字段，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，在kv 场景（只有一个索引且该索引必须是唯一索引）可以选择业务字段作为主键。

  13. 创建索引时应考虑覆盖索引的情况。

  14. 对大表频繁进行insert 和delete 操作，时间一长会缠上表碎片，insert 随机值作为主键id， 会产生很多数据页分裂操作，而delete 一些排列有序的主键值，折线delete 的空间不会被直接释放，而是仅仅进行delete 的标记。重建表可以释放空余空间。alter table A engine=Innodb

  15. 官方自带的逻辑备份工具是mysqldump，使用single-transaction 方法可以启动一个事务来确保一致性。single-transaction方法只适用于所有的表使用事务引擎的库。

  16. Flush tables with read lock（FTWRL) 命令可以让数据库处于只读状态。典型的使用场景是做全库逻辑备份。

  17. 改变表结构会对表加上MDL锁，这个时候增删改查都会被阻塞，如果这个时候，有频繁的查询语句，超时后客户端有重启机制，会再起一个新的session，让这个库的线程很快爆满。理想的解决机制是：在改表语句中设置超时时间，拿不到先放弃，之后重试。

  18. 两条事务并行的时候，后启动的事务会被阻塞，知道第一条执行commit后。

  19. 两阶段锁协议：航所在需要的时候加上，等到事务结束时释放。因此在写事务的时候顺序越靠前的事务行被锁住的时间越长。

  20. 死锁

    img

  21. 在rr 隔离级别下，事务在启动的时候会创建一个基于整库的快照，InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

    image-20211103115152458
    1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
    2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
    3. 如果落在黄色部分，那就包括两种情况
      a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
      b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

    id = 1，k = 1

    image-20211103115328426 image-20211103115220659

    事务A 在启动时，事务C 还未开始，处于红色部分，未开始区域，不可见；事务B 在事务A 启动后启动，处于黄色部分，并且事务B 在事务A 提交后提交，黄色部分第一种情况，不可见。因此，事务A select 语句的值为1。

思考题

  1. 定期全量备份的周期“取决于系统重要性，有的是一天一备，有的是一周一备”。那么在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？

    A：好处是“最长恢复时间”更短。在一天一备的模式里，最坏情况下需要应用一天的binlog。比如，你每天0点做一次全量备份，而要恢复出一个到昨天晚上23点的备份。一周一备最坏情况就要应用一周的binlog了。系统的对应指标就是提到的RTO（恢复目标时间）。

  2. 系统里面应该避免长事务，如果你是业务开发负责人同时也是数据库负责人，你会有什么方案来避免出现或者处理这种情况呢？

    MA：1. 事务超时回滚；

    A：从应用开发端，1. 确认是否使用set autocommit=0。这个确认工作可以在测试环境中开展，通过general_log 的日志来确认。2. 确认不必要的事务（select）。3. 根据业务本身的预估，通过SET MAX_EXECUTION_TIME 命令来控制每个语句的执行最长时间，避免单个语句意外执行太长时间（意外原因？）。

    ? 从数据库端：1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警/Kill。2. percona 的pt-kill 工具。3. 测试阶段要求输出general_log，分析日志行为提前发现。4. mysql 5.6+ 的版本，吧innodb_undo_tablespaces 设置成2（或者更大）

  3. 对于上面例子中的InnoDB表T，如果你要重建索引 k，你的两个SQL语句可以这么写：

    alter table T drop index k;
alter table T add index(k);

    如果你要重建主键索引，也可以这么写：

    alter table T drop primary key;
alter table T add primary key(id);

    我的问题是，对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

    MA：重建索引k，先加索引，再删除。

    A：重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

  4. DBA小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

    CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

    公司的同事告诉他说，由于历史原因，这个表需要a、b做联合主键，这个小吕理解了。

    但是，学过本章内容的小吕又纳闷了，既然主键包含了a、b这两个字段，那意味着单独在字段c上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？

    同事告诉他，是因为他们的业务里面有这样的两种语句：

    select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

    我给你的问题是，这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？

    MA：第一句不用，第二句要。根据最左匹配原则，第一句可以用到主键索引，而第二句不行。

    A：是ca可以去掉，cb需要保留。

  5. 备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

    MA：备库会在执行完备份后，再进行DDL。

    A：备份关键语句

    /* 为了确保RR 隔离级别，再设置一次 */
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* 启动事务 */
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
/* 设置一个保存点 */
Q3:SAVEPOINT sp;
/* 时刻 1 */
/* 拿到表结构 */
Q4:show create table `t1`;
/* 时刻 2 */
/* 拿数据，导数据 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
/* 回滚到sp，这里是为了释放t1 的DML锁 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

    因此有下面几种情况：

    1. 如果在Q4 语句执行之前到达，现象：没有影响，备份拿到的是DDL 后的表结构
    2. 如果在时刻2 到达，则表结构被改过，Q5 执行的时候报Table definition has changed, please retry transaction，现象：mysqldump 终止
    3. 如果在时刻2 和时刻3 之间到达，mysqldump 占着t1 的DML 读锁，binlog被阻塞，现象：主从延迟，直到Q6 执行完成
    4. 如果从时刻4 开始，mysqldump 释放了MDL 读锁，现象：没有影响，备份拿到的是DDL 前的表结构

  6. 如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到：

    • 第一种，直接执行delete from T limit 10000;
    • 第二种，在一个连接中循环执行20次 delete from T limit 500;
    • 第三种，在20个连接中同时执行delete from T limit 500。

    你会选择哪一种方法呢？为什么呢？

    MA：第二种，第一种会造成较长的阻塞。第三种消耗过多的资源。

程序猿嘿咻

相关

标签