MySQL学习笔记：乐观锁VS悲观锁/八股总结

MySQL乐观锁和悲观锁是什么

乐观锁和悲观锁是一种思想：两种并发控制思想。本质在对于冲突的预期态度不同。
悲观锁假设所有冲突一定会发生，所有操作数据前先把锁拿到，别的事务就必须等锁释放
MySQL中用SELECT…FOR UPDATE拿排他锁，用SELECT…LOCK IN SHARE MODE拿共享锁。拿到锁之后别的事务都会进行等等。直到事务提交或回滚才释放。
乐观锁假设冲突很少发生，默认不加锁，等到正真更新时再检查数据有没有被别人改过。通常用一个version字段来实现，读的时候把version一起读出来，更新的时候在WHERE条件里带上这个version，如果version改变，更新影响0行，业务层自己决定重试还是报错。

悲观锁的两种模式

• 排他锁X，SELECT…FOR UPDATE 拿到后别的事务读和写操作都会被阻塞
• 共享锁S，SELECT…LOCK IN SHARE MODE 都给事务可以同时持有同一行的S锁，但无法获取到X锁。

乐观锁的两种实现方式

1.版本号

-- 读取数据和版本号SELECTid,name,stock,versionFROMproductsWHEREid=1;-- 假设读出来 version = 5-- 更新时带上版本号UPDATEproductsSETstock=stock-1,version=version+1WHEREid=1ANDversion=5;-- 检查影响行数-- 如果返回 0 说明被别人改过，需要重试或报错

2.CAS比较原值

-- 假设读出来 stock = 100UPDATEproductsSETstock=99WHEREid=1ANDstock=100;

减少了额外字段，但是会产生ABA问题。

关于乐观锁和悲观锁选择

乐观锁：

读多写少，冲突概率低。例如用户修改自己的资料，同一时间同一个用户修改同一条概率很低，用乐观锁省去加锁开销，吞吐量高

悲观锁：

写多冲突多。例如：热门秒杀商品

还有一种思路是先乐观后悲观。平时用乐观锁，如果冲突频发，转变为悲观锁

总结

乐观锁和悲观锁是两种并发控制思想。
面试从三点回答

• 定义的解释
• 两种锁实现的机制是什么
• 根据不同业务怎么去选择锁

乐观锁假设冲突不会发生，不加锁。在提交数据时如果发现数据被修改，则会拒绝当前事务并尝试，直到成功。悲观锁假设冲突一致存在，在操作的时候就加锁，其他事务不可访问
机制：乐观锁：版本号或CAS。版本号需要维护额外字段，但不会产生CAS的ABA问题
选择：读多写少用乐观，冲突多用悲观

MySQL发生死锁如何解决

两种方式：自动处理和手动干预
MySQL InnoBD自带死锁检测机制，默认开启。一旦检测到死锁，存储引擎会自动选自一个代价最小的事务回滚。释放他持有的锁让另一个事务继续执行。
另外还有兜底机制：锁等待机制。默认50秒，超时自动放弃并回滚。
手动干预主要用在自动机制不够快或者需要立刻恢复的场景。先SHOWENGINEINNODBSTATUS或者查INFORMATION_SCHEMA 里的锁相关表找到阻塞的线程ID，然后KILL<thread_id>杀掉它。

如何避免死锁

• 拆分大小事务。事务持有事件越长，死锁概率越高
• 固定加锁顺序。先锁A再锁B
• 降低隔离级别。可重复读比读已提交多了间隙锁和临键锁，锁的范围更大。如果业务允许，减少锁冲突
• 合理建索引。没命中索引会导致全表扫面。会锁全表
• 调整锁等待时间

总结

讲清楚：

• 说清楚MySQL中怎么处理死锁的（两种方式）
  额外可说怎么避免死锁

MySQL怎么进行SQL调优

SQL调优核心思路是减少磁盘I/O和避免无效计算。分为三步流程：先定位慢SQL，再分析执行计划，最后针对性能优化。
定位慢SQL靠MySQL查询日志，分析执行计划用EXPLAIN,优化手段主要有几类：

1. 索引优化
   合理设计索引，利用覆盖索引避免回表。
   注意最走前缀原则
   避免再索引上做函数运算
2. SQL写法优化
   禁止写select *，只查必要字段，减少网络传输和内存占用
   避免%like 前缀模糊查询，like ‘%关键词’必然全表扫描
   连表查询时检查字段字符集是否一致
3. 架构优化
   热点数据上Redis缓存，访问频率高但变化少的数据没必要每次查询数据库
   大表考虑分库分表，单表超过2000万行查询性能明显下降
   读写分离，把查询压力分摊到从库

还可以通过业务来优化，少展示不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询。

总结

优化SQL的本质是为了减少磁盘I/O和避免无效计算
我们分为三步走：先定位慢SQL（MySQL慢查询日志），再分析执行计划（EXPLAIN），最后针对性优化

解释一下MySQL的EXPLAIN语句查询

在select语句前加上explain就能看到优化器是如何执行这条语句的。关注有一下几个字段：

1. **type访问类型 **，直接决定查询效率。性能从高到底排列：const>eq_ref>ref>range>index>all。
2. key实际用到的索引。如果显示NULL表示没走索引
3. rows预估要扫描的行数。越小越好
4. Extra额外信息藏着很多细节。Usingindex说明走了覆盖索引l，数据直接从索引拿不用回表；Usingfilesort 说明排序没法用索引得额外排序；Using temporary 说明用了临时表，这两个一般都需要优化

type的每个级别具体含义:
const：主键或唯一索引|等值查询，最多一行匹配，最快eq_ref：连接查询里通过主键或唯一索引关联，每次只取一行ref：用非唯一索引查，可能返回多行
range：索引范围扫描，用在 BETWEEN、>、<这类条件上index：扫描整棵索引树，比ALL 好但还是很慢
ALL：全表扫描，遇到大表直接起飞

如何解决MySQL中的深分页问题

先分析原因：limit 99999999，10这种写法会让MySQL扫描前99999999条记录并丢弃，浪费了大量IO。解决思路就是减少扫描的数据量，常用的优化方案有三种：

1. 子优化查询
   把原本的查询拆分成两步：先用子查询在二级索引上快速定位起始id，再用这个id去主键索引取数据

-- 原始写法，慢SELECT*FROMmianshiyaWHEREname='yupi'ORDERBYidLIMIT99999990,10;-- 优化写法SELECT*FROMmianshiyaWHEREname='yupi'ANDid>=(SELECTidFROMmianshiyaWHEREname='yupi'ORDERBYidLIMIT99999990,1)ORDERBYidLIMIT10;

简单理解为，先用子查询查询到第999999的id是都少，拿去到这个id后直接从999999位后面开始查询，避免了扫描前面的数据。

2. 游标分页
   每次查询都返回当前页最大id，下次查询时带上这个id作为起点

-- 第一页SELECT*FROMmianshiyaWHEREname='yupi'ORDERBYidLIMIT10;-- 假设最大 id 是 100-- 第二页SELECT*FROMmianshiyaWHEREname='yupi'ANDid>100ORDERBYidLIMIT10;

利用id>maxId直接过滤，MySQL可以从索引定位到起始位置，不要扫描前面的数据。
缺点是只能连续翻页，无法直接跳到10000页

• 搜索引擎
  把数据同步到Elasticsearch，用search——after做深度分页。
  

总结

先说明深分页问题是什么，然后从三个方面来解决

• 子查询优化
• 游标分页
• 搜索引擎

什么是MySQL的主从同步机制，它是如何实现的

核心原理：

主库把所有“数据变更操作”写进 binlog，从库把这些操作读过来再执行一遍

MySQL的主从同步的核心就是binlog复制：主库把写操作记录到二进制日志中，从库拉过来重放一遍，数据就同步了。
整个流程涉及三个线程配合：

• 主库的dump线程：监听binlog变更，有新内容就推事件给从库
• 从库的I/O线程：拉去主库数据，把收到的binlog写进本地的relay log
• 从库的SQL线程：读取relay log，逐条执行SQL语句

MySQL支持三种复制模式

MySQL支持异步，同步，半同步三种复制模式，区别在于之苦什么时候给客户端返回响应：

总结

一句话：MySQL 主从同步 =主库写binlog；dump线程推送变更事件；从库拉去数据；从库I/O线程写中继日志；SQL线程重放。
MySQL默认是异步复制，主库binlog就直接返回不管从库有没有收到。

如何处理MySQL的主从同步延迟

主从延迟必然存在，不可能完全删除，只能短暂缩短延迟事件或在业务层面来规避。
业务层面常见的处理方式：

• 关键业务强制走主库。比如用户注册完立马登录，写后读的场景直接走主库。
• 延迟感知。写操作后记录事件戳，短时间内的读请求强制走主库，过了延迟窗口再走主库。
• 二次查询兜底。从库查不到就再查一次主库，属于兜底策略。
• 缓存前置。写入主库的同时写入缓存，读请求先查缓存。