MySQL面试知识点追命连环问（三）锁机制、日志备份及分表策略

上次我们讨论了MySQL的事务索引，SQL优化和处理器。

MySQL面试知识点追命连环问（二）事务、索引及SQL优化

这次我们继续来追命连环问关于MySQL锁机制，日志备份和扩展性等相关的内容。

• 锁机制

• 日志备份

• 集群

• 分库分表

1. MySQL锁机制

面试官：你知道MySQL的锁机制吗？

答：知道的。MySQL锁按加锁粒度可以分为行锁表锁和页锁。按锁的使用方式可以分为共享锁和排他锁。按加锁思想可以分为悲观锁和乐观锁。

1.行锁与表锁

行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。

表级锁是MySQL锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。页锁是介于行锁和表锁之间的一种锁。一次锁定相邻的一组记录。

在 MySQL 中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

InnoDB 行锁是通过给索引项加锁实现的，如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。因为没有了索引，找到某一条记录就得扫描全表，要扫描全表，就得锁定表。

2.共享锁和排他锁

行锁表锁只是描述锁的范围，而加锁方式有共享锁和排他锁两种。

数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

共享锁（s锁 读锁）:对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。
共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

排他锁（x锁 写锁）:对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。

数据库规定同一资源上不能同时共存共享锁和排他锁。 排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

3.乐观锁和悲观锁

悲观锁和乐观锁是两种加锁的思想。乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

乐观锁在操作数据时非常乐观，认为别人不会同时修改数据。因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据：如果别人修改了数据则放弃操作，否则执行操作。

乐观锁主要是基于数据版本机制来实现，实现方式有两种：CAS和版本号机制。MVCC也是乐观锁的一种实现方式。(注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control。MVCC最大的好处是：读不加锁，读写不冲突。读分为快照读和当前读，快照读基于数据的可见版本不加锁。）

3.1 CAS算法（compare and swap）

如果内存位置V的值等于预期的A值，则将该位置更新为新值B，否则不进行任何操作。许多CAS的操作是自旋的：如果操作不成功，会一直重试，直到操作成功为止。CAS是由CPU支持的原子操作，其原子性是在硬件层面进行保证的。

需要3个操作数：需要读写的内存值（V）,进行比较的预期值（A），拟写入的新值（B）。

缺点：

1，ABA问题。解决：引入版本号机制
2，高并发下一直竞争失败。解决：引入退出机制，设置阈值。
3，功能受限，例如CAS只能保证单个变量（或者说单个内存值）操作的原子性。

3.2 版本号机制

版本号机制的基本思路是在数据中增加一个字段version，表示该数据的版本号，每当数据被修改，版本号加1。当某个线程查询数据时，将该数据的版本号一起查出来；当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作。

实际上可以根据实际情况选用其他能够标记数据版本的字段，如时间戳等。

悲观锁

悲观锁在操作数据时比较悲观，认为别人会同时修改数据。因此操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据。

悲观锁的实现方式是加锁，加锁既可以是对代码块加锁（如Java的synchronized关键字），也可以是对数据加锁（如MySQL中的排它锁）。这里主要讲的是MySQL中的悲观锁。悲观锁通过排他锁来实现的。

悲观锁可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性

悲观锁和乐观锁的比较

1.与悲观锁相比，乐观锁适用的场景受到了更多的限制，无论是CAS还是版本号机制。

2.如果悲观锁和乐观锁都可以使用，那么选择就要考虑竞争的激烈程度。竞争不激烈时乐观锁更好，反之悲观锁更好。

面试官：那乐观锁加锁吗？

答：
（1）乐观锁本身是不加锁的，只是在更新时判断一下数据是否被其他线程更新了；
（2）有时乐观锁可能与加锁操作合作，但不能改变“乐观锁本身不加锁”这一事实。

面试官：那你知道死锁吗？是怎么产生的又怎么样解决呢？

4. 死锁

MySQL中的死锁一般是事务相互等待对方资源，最后形成环路造成的。若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。

死锁的产生主要有以下几种情况：不同表相同记录行锁冲突，相同表记录行锁冲突，不同索引锁冲突，gap锁冲突。

死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

所以当出现死锁时要分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。同时可以通过应用业务日志定位到问题代码，找到相应的事务对应的sql。

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

避免死锁的方法：

1）以固定的顺序访问表和行。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

2.MySQL日志与备份

面试官：你知道MySQL有哪些日志吗？

答：MySQL日志有很多种，主要有二进制日志（bin log），错误日志，慢查询日志，通用查询日志和事务日志。二进制日志是MySQL的上层日志，先于存储引擎的事务日志被写入。MySQL的日志主要用于异常监控、性能优化、数据恢复和主从同步等功能。

面试官：那你说一下事务日志吧。

我：Innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。它们都算是用来恢复的日志。

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作都是将页的数据加载到内存中，然后进行操作，再将数据刷回到硬盘上。

1. Redo Log

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)

在启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如二进制日志)要快很多。而且，Innodb自身也做了一定程度的优化，让恢复速度变得更快。

事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。

2. Undo Log

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

事务涉及到数据修改时，默认情况下会在commit的时候调用fsync()将日志刷到磁盘，保证事务的持久性。但是一次刷一个事务的日志性能较低，特别是事务集中在某一时刻时事务量非常大的时候。innodb提供了group commit功能，可以将多个事务的事务日志通过一次fsync()刷到磁盘中。

3.MySQL集群

面试官：你知道MySQL集群吗？

答：MySQL 群集分为三种节点：管理节点，数据节点和SQL节点。

管理节点：主要用于管理各个节点，能够通过命令对某个节点进行重启、关闭、启动等操作。也能够监视全部节点的工作状态。

数据节点：主要是对数据的存储，不提供其他的服务。

SQL节点：主要是对外提供SQL功能，类似一台普通的 MySQL Server。

而SQL节点和数据节点可以是同一台机器，也就是说这台机器即是SQL节点也是数据节点。它们只是逻辑关系上的划分，实际部署时，甚至所有的阶段都可以位于同一台物理机器上，只是配置较复杂些。

我们在考虑MySQL数据库的高可用的架构时，主要要考虑如下几方面：

如果数据库发生了宕机或者意外中断等故障，能尽快恢复数据库的可用性，尽可能的减少停机时间，保证业务不会因为数据库的故障而中断。用作备份、只读副本等功能的非主节点的数据应该和主节点的数据实时或者最终保持一致。当业务发生数据库切换时，切换前后的数据库内容应当一致，不会因为数据缺失或者数据不一致而影响业务。所以需要MySQL集群来保证数据库的高可用性。

面试官：MySQL集群有什么优缺点呢？

优点：

• 99.999 ％的高可用性

• 快速的自动失效切换

• 灵活的分布式体系结构，没有单点故障

• 高吞吐量和低延迟

• 可扩展性强，支持在线扩容

缺点：

• 存在很多限制，比如：不支持外键，数据行不能超过8K（不包括BLOB和text中的数据）

• 部署、管理、配置很复杂

• 占用磁盘空间大，内存大

• 备份和恢复不方便

• 重启的时候，数据节点将数据load到内存需要很长时间

4.MySQL可拓展性(分库分表)

面试官：如果一张表的数量非常大怎么办？

答：随着表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大。这时可进行分表。分表的话有水平分表和垂直分表。

垂直切分：依照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这样的切分称之为数据的垂直（纵向）切分。垂直拆分规则明确，拆分后业务清晰；系统之间进行整合或扩展变的容易；但也会导致部分业务表无法关联（Join），只能通过接口方式解决，提高了系统的复杂度；但随着数据量的增多，极有可能还要增加水平切分；

水平切分：依据表中的数据的逻辑关系，将同一个表中的数据依照某种条件拆分到多台数据库（主机，当然也可能是同一个数据库）上面。在每个表中包含一部分数据，所有表加起来就是全量的数据。这样的切分称之为数据的水平（横向）切分。

水平切分的优点也很明显，比如某个表高峰时段同时有100万次请求，如果是单库，数据库就会承受100万次的请求压力，拆分成100个表分别放入10个库中，每个表进行1万次请求，则每个数据库会承受10万次的请求压力，这样压力就减少了很多，并且是成倍减少的。

面试官：水平分表有哪几种分法呢，优缺点是什么？

水平分库分表的切分规则主要包括如下几种：

1. 按号段分
   user_id为区分，1～1000的对应DB1，1001～2000的对应DB2，以此类推；
   优点：可部分迁移
   缺点：数据分布不均

2. hash取模分：
   对user_id进行hash，余数相同的id会分到同一张表。
   优点：数据分布均匀
   缺点：数据迁移的时候麻烦，不能按照机器性能分摊数据

3. 在认证库中保存数据库配置
   建立一个DB，这个DB单独保存user_id到DB的映射关系，每次访问数据库的时候都要先查询一次这个数据库，以得到具体的DB信息，然后才能进行我们需要的查询操作。
   优点：灵活性强，一对一关系
   缺点：每次查询之前都要多一次查询，性能大打折扣

4. 其他方式
   1）按照地理区域：比如按照华东，华南，华北这样来区分业务。
   2）按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

以上就是通常的开发中我们选择的方式，有些复杂的项目中可能会混合使用这些方式。

水平拆分的优点：高并发的性能瓶颈；应用端改造较少；提高了系统的稳定性跟负载能力。

数据库中的数据在经过垂直和（或）水平切分被存放在不同的数据库（表）主机之后，应用系统面临的最大问题就是怎样来让这些数据源得到较好的整合，当然也包括切分的一个唯一性保障的问题。

一个是要保证ID的全局唯一性，二是查询数据结果集合并问题，这里包括跨节点Join的问题，跨节点合并排序分页问题以及分布式事务问题。

ID的全局唯一性可以通过设置表的不同开始ID和相同步长来解决，跨节点join问题可以通过应用程序来处理，而跨节点排序分页可以从多个数据源并行执行。

好啦，今天的追命连环问就到这里了，下次继续，如对文章有疑惑或补充的地方欢迎留言交流(●'◡'●)。原创不易，如果对你有帮助的话欢迎点赞！

相关推荐阅读

你知道一次完整的HTTP请求过程吗

Redis常见面试题连环问，你能回答到第几问？（上）

大厂高频面试题：高并发下接口幂等性的解决方案？