mysql45讲，读后笔记

摘录自 极客时间  的丁奇 

链接：

https://time.geekbang.org/column/article/67888

重要的日志模块：redo log

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

重要的日志模块：binlog

这两种日志有以下三点不同。

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

innodb_flush_log_at_trx_commit设置成1，这样可以保证MySQL异常重启之后数据不丢失（每次事务的redo log都直接持久化到磁盘）

sync_binlog设置成1，MySQL异常重启之后binlog不丢失（表示每次事务的binlog都持久化到磁盘）

索引

1.最左原则

非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。（不需要回表）

2.索引字段不能进行函数操作，但是索引字段的参数可以玩函数. 

例如where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写。

 2.1案例一：条件字段函数操作

select count(*) from tradelog where month(t_modified)=7;

用这个替换

select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

案例二：隐式类型转换

MySQL里的转换规则了：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

select * from tradelog where tradeid=110717;

对于优化器来说，这个语句相当于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

 

备份

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。single-transaction方法只适用于所有的表使用事务引擎的库（推荐），否则只能用表锁备份

 

主备延迟的来源

1.备库所在机器的性能要比主库所在的机器性能差。

当然，这种部署现在比较少了。因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况。

2.备库的压力大

这种情况，我们一般可以这么处理：

1. 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。

2. 通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。

3.大事务

主库上必须等事务执行完成才会写入binlog，再传给备库

不要一次性地用delete语句删除太多数据。其实，这就是一个典型的大事务场景。

另一种典型的大事务场景，就是大表DDL，用13章的Online DDL

造成主备延迟还有一个大方向的原因，就是备库的并行复制能力。这个话题，我会留在下一篇文章再和你详细介绍。