《MySQL实战45讲》读后笔记

《MySQL实战45讲》读后笔记

• WAL
  MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
• bin log
  MySQL的Server层的日志，binlog只能用于归档，不能用于crash-safe，所以MyISAM不具备crash-safe能力。
• redolog和binlog比较:
  1.redolog是物理日志,记录的是某一页做了什么修改。binlog是逻辑日志，记录了sql。
  2.redoglog会循环覆盖，binlog是一直追加
  3.redolog是innodb独有，binlog是MySQL的Server层实现的。
• 回滚日志(undo log)与read-view：
  read-view可用于回滚，因为他记录了旧值。当没有事务需要用到readview时，就会删除它，如果长事务，会导致大量的read-view链无法删除，因为长事务与current read-view之间的都要在长事务结束才可能删除。这就是为什么不使用长事务的原因。此外，长事务还会占用锁资源。实际上不会有readview真是存在，而是由undolog计算出readview。
• 查询长事务命令
  select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
• 一周一备和一天一备的对比
  最坏情况所需要的的binlog不一样，一天一备最坏所需binlog也只是一天，而一周一备最坏所需的binlog需要一周。对应的指标是RT0，即恢复目标所需时间。
• 什么情况下业务字段也适合做主键?
  对于个人信息这种，身份证号码不适合做主键，因为个人信息表通常有其他索引，二级索引包含主键，占用空间大。当整张表只有一个索引时，才考虑用业务字段作为主键，一般情况下我们都用自增主键。
• 索引顺序考虑
  1.这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。2.我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。
• 重建索引使得索引页更紧凑
  对于二级索引使用alter table T drop index k;alter table T add index k没问题，但是对于主键索引，这样的语句会重建整张表。可以用这个语句代替 :alter table T engine=InnoDB
• 如何避免长事务
  首先，从应用开发端来看：

1. 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log（所有到达MySQL的语句都将记录） 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。(spring的read的时候不要乱加事务)
3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）
   其次，从数据库端来看：
4. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
5. Percona 的 pt-kill 这个工具不错，推荐使用；
6. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
7. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

• 全局锁
  Flush tables with read lock (FTWRL)，显示加全局锁，不允许修改语句执行。应用场景是全局逻辑备份。针对INNODB，其实有MVVC机制，mysqldump则可以利用这种机制，加上-- single-transaction，mysqldump期间不能使用ddl语句。而MyISAM没有事务，没有MVVC机制，且readonly字段通常会被系统用于其他作用，比如标记为从主库，FTWRL在客户端异常断开之后就会使数据库恢复为可写，设置readonly则一直不能恢复。
• 表级锁
  locak table t1 read, t2 write。MDL锁，即表元数据(表结构数据)锁，访问表的时候便会隐式地加上。
• 给一张表加个字段，导致整个库挂了的场景
  长事务霸占了MDL读锁，当加个字段时，会尝试加MDL写锁，因为互斥，所以阻塞了。此后的事务，都会因为MDL写锁在排队，而阻塞。所以，在加字段的时候，要确保没长事务在执行，可以通过select * from innodb_trx查询，如果有可以尝试kill掉。如果是查询很频率的场景，可以使用alter table table_name WAIT add column,在 N时间内如果获取不到MDL锁就结束掉，避免阻塞，这是MariaDb才有的。
• 行锁
  行锁是引擎层实现的，MyISAM不支持行锁，InnoDB支持。
• 两阶段加锁带来的启发
  如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
• MySQL死锁，CPU很高
  两阶段加锁时，相互等待。解决方法:1.innodb_lock_wait_timeout设置短一些，但是这会引起不是死锁情况的失败返回。2.开启死锁检测，但是带来的消耗很大，因为每个事务执行都需要判断自己的加入会不会引起死锁，那么就是O(n)的时间复杂度。假如1000个并发，那么就是100W次检查，效率很低。如果开了死锁检测，就要控制并发度，确保同时修改一行数据的并发度低。
• 主从架构下，mysqldump --singe-transaction可能的问题，用从库在备份，主库修改了表结构，此时会怎么样？
  
  在InnoDb的引擎下，mysqldump利用mvcc机制，可以做到在备份过程中允许数据写入，和可重复读原理一样。但是，假设有DDL语句，进来，会因为MDL锁和表结构变化等原因导致备份期间binlog阻塞或失败，下面不同时刻接收到DDL语句的具体的情况: