MySQL面试问题（三）

快乐江小鱼

于 2024-08-20 10:49:44 发布

阅读量441

点赞数 4

分类专栏： # MySQL 文章标签： mysql 面试数据库

本文链接：https://blog.csdn.net/dolly_baby/article/details/141352287

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL面试问题（三）

文章目录

- MySQL面试问题（三）

MVVC机制

多版本并发控制(Multi-Version Concurrency Control)简称MVCC，是用来解决读写冲突的。它主要由隐藏字段trx_id当前事务ID、roll_pointer回滚指针，undo log日志，read-view来配合完成的，是一种乐观锁实现。
MVVC原理是通过保存数据在某个时间点的快照来实现的。一个事务无论运行多长时间，在同一个事务里能看到数据一致的视图。根据事务的开始事件不同，意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

每行数据都存在一个版本，每次数据更新时都更新该版本。修改时拷贝当前版本随意修改，各个事务彼此独立。保存时比较版本号，如果提交成功，则覆盖原纪录；失败就回滚拷贝。MVCC只适用于隔离级别为读已提交和可重复读。
InnoDB存储引擎的MVCC的实现策略是在每行数据中额外保存当前创建时的版本号和删除时的版本号。每次开始新的事务，系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

如何排查锁的问题

一、表锁

查看表锁的情况

-- 查看所有线程情况
show full processlist;
-- 查看当前正在使用的表
show open tables;
-- 查看innodb服务器情况
show engine innodb status;

表锁分析

-- Table_locks_waited：出现表级锁争用而发生等待的次数，这个比例值越大说明表级锁竞争越激烈
-- Table_locks_immediate：表示可以立即获取锁的次数
show status like 'table%';

二、行锁

查看行锁的情况

-- Innodb_row_lock_current_waits：当前正在等待锁的数量
-- Innodb_row_lock_time：从系统启动到现在锁定的总时间
-- Innodb_row_lock_time_avg：每次等待所花费的平均时间
-- Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
-- Innodb_row_lock_waits：从系统启动到现在总共等待的次数
show status like 'innodb_row_lock%';

information_schema库

innodb_trx表：用来显示当前innodb事务情况，不能判断锁的情况
innodb_locks表：可以查看锁的情况
innodb_lock_waits表：可以查看锁等待的情况

三、锁优化

尽可能让所有数据的检索都通过索引来完成，避免无索引导致行级锁升级为表级锁。
合理设计索引，尽量缩小锁的范围。
控制事务大小，减少锁定的资源数量和占用时间。
尽量使用低级别的事务隔离。

MySQL的锁机制

锁按照粒度可以分为表锁和行锁。

行锁是MySQL中锁定粒度最细的一种锁，只针对当前操作的行进行加锁。==行级锁能减少数据库操作的冲突，加锁粒度小，但加锁的开销最大，也可能出现死锁。==实现算法有Record Lock，Gap Lock和Next-key Lock。Record Lock是单个行记录的锁，对索引项加锁，锁定符合条件的行，其他事务不能修改和删除加锁的项。Gap Lock间隙锁锁定的是一个范围，不包含记录本身，对索引项之间的间隙加锁，对第一条记录前的间隙或最后一条将记录后的间隙加锁，不包含索引项本身，其他事务不能在锁范围内插入数据，可以防止别的事务新增幻影行。Next-key Lock是默认锁会同时锁住数据和间隙锁，锁定索引项本身和索引范围，是Record Lock和Gap Lock的结合，可以解决幻读的问题。
表锁是粒度最大的锁，表示当前的操作对整张表加锁，==资源开销比行级锁少，不会出现死锁的情况，发生锁冲突概率很大。==大部分的MySQL引擎都支持。

锁按照是否可写分为共享锁和排他锁。

共享锁又称为读锁，其他用户可以并发读取数据，但是任何事务都不能获取数据上的排他锁，指导已释放所有的共享锁。如果事务T对数据对象A增加了共享锁，则事务T只能读取A，其他事务只能再对A加共享锁，而不能加排他锁，直到T释放A上的共享锁。
排他锁又称为写锁，如果事务T对数据对象加上排他锁，则只允许T读取和修改A，其他任何事物都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其他事务获取资源上的锁。

MySQL日志

MySQL三大日志包括binlog、redo log、undo log。

binlog用于记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。binlog是MySQL的逻辑日志，并且由Server层进行记录，使用任何存储引擎的MySQL数据库都会记录binlog日志。逻辑日志可以理解为记录的SQL语句，物理日志是数据页变更。binlog是通过追加的方式写入的，可以通过max_binlog_size参数设置文件大小，当文件大小达到给定值后会生成新的文件来保存日志。

binlog可以用于主从复制和数据恢复。我们可以通过mysqlbinlog工具来恢复数据。主从复制在master端开启binlog，然后将binlog发送到各个slave端，slave端重做binlog从而达到主从数据一致。

redo log通常是物理日志，记录的是数据页的物理修改，而不是某几行修改成怎么样，用来恢复提交后的物理数据页。只记录事务对数据页做了哪些修改，主要记录了一段时间内物理数据页的变化。
undo log主要记录了数据的逻辑变化，比如一条插入语句对应一条删除语句的undo log，对于每个更新语句对应一条相反的update的undo log，这样在发生错误时候，就能回滚到事务之前的数据状态。数据库事务的原子性底层是通过undo log实现的。
redo log用来记录某数据块被修改后的值，可以用来恢复未写入数据文件的已成功事务更新的数据；undo log是用来记录数据更新前的值，保证数据更新失败能够回滚。

redo log用来记录某数据块被修改后的值，可以用来恢复未写入数据文件的已成功事务更新的数据；undo log是用来记录数据更新前的值，保证数据更新失败能够回滚。