MySQL实战

技术

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

加锁规则

• 原则1：加锁的基本单位是 next-key lock，前开后闭区间
• 原则2：查找过程中访问到的对象才会加锁
• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
• 优化2：索引上的等值查询，向右边遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
• 一个bug：唯一索引上的范围查询会访问到不满足条件的额第一个值为止

死锁检测

当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。默认值 50s
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

change buffer

需要更新一个数据页时，如果数据页在内存中就直接就更新，如果没有，在不影响数据一致性的前提下，InnoDB会将更新操作缓存在change buffer中。下次需要访问此数据页时，将数据也读入内存然后执行缓存的操作。访问数据页会触发merge(持久化)，系统后台有线程会定期merge，数据库正常关闭过程中也会merge。因为减少了随机磁盘访问，提升性能明显。

使用场景
change buffer 只对普通索引起加速作用，不适用于唯一索引。适合写多读少的业务，比如日志类、账单类系统

SQL性能优化

一、索引字段不能进行函数操作，但是索引字段的参数可以玩函数(条件字段函数操作、隐式类型转换、隐式字符编码转换)
二、嵌套循环,驱动表与被驱动表选择错误
三、索引选择不同,造成性能差异较大
四、where条件列的参数比条件列长度大，导致传给引擎的时候做了字符串截断。查到满足条件的如果有非索引字段则需要回表，回表后查出整行和参数比较，不一致，返回结果是空

查询长时间不返回：等MDL锁、等flush、等行锁
查询慢：一致性读(需要执行undo log得到以前的版本)

幻读问题

幻读指的是一个事务前后两次查询同一个范围，后面的查询看到了前面查询结果中没有的行。

间隙锁是在可重复读(RR)隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交(RC)的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。这，也是现在不少公司使用的配置组合。

MySQL IO上出现了性能瓶颈，可以通过哪些方法来提升性能

1. 设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。
2. 将sync_binlog设置为大于1的值（常见是100~1000）。这样的风险：主机掉电时会丢 binlog 日志。
3. 将innodb_flush_log_at_trx_commit设置为2。这样的风险：主机掉电时会丢数据。

主备延迟

主备延迟的来源

• 备库所在的机器的性能比主库所在的机器性能差
• 备库的压力大；可以用一主多从
• 备库的并行复制能力
• 大事务
  
  • 一次性用delete语句删除太多数据
  • 大表DDL

主备延迟导致从库上的过期读现象，有以下几种方案：

• 强制走主库方案；最常用
• sleep方案
• 判断主备无延迟方案
• 配合半同步复制(semi-sync)方案；主库等到一个从库的ack，就开始给客户端返回确认。在某些从库执行查询请求会存在过期读的现象。
• 等主库点位方案
• GTID方案