MySQL性能调优与架构设计【面试题五】

什么是锁？MySQL 中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁，MyISAM 只支持表级锁。

什么是全局锁、共享锁、排它锁？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。 这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。

排他锁 exclusive lock（也叫 writer lock）又称写锁。

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。排它锁是悲观锁的一种实现。

若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。

MySQL中的表锁有哪些？

MySQL 里表级锁有两种：普通表级锁、元数据锁（meta data lock）简称 MDL和AUTO-INC锁。表锁的语法是 lock tables t read/write。

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL：不需要显式使用，在访问一个表的时候会被自动加上。

MDL 的作用：保证读写的正确性。

在对一个表做增删改查操作的时候，加 MDL
读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。

AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值。

InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁，在锁的算法实现上有三种：

· Record lock：单个行记录上的锁

· Gap lock：间隙锁，锁定一个范围，不包括记录本身

· Next-key lock：record+gap 锁定一个范围，包含记录本身

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生，innodb对于行的查询使用next-key lock，Next-locking keying是Record lock和Gap lock的组合。当查询的索引含有唯一属性时，将next-key lock降级为record key。

有两种方式显式关闭gap锁 ，第一种. 将事务隔离级别设置为RC ；第二种. 将参数innodb_locks_unsafe_for_binlog设置为1。

谈一下MySQL中的死锁

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁。

如何查看死锁？

使用命令 show engine
innodb status 查看最近的一次死锁。

InnoDB Lock
Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

对待死锁常见的两种策略：

通过 innodblockwait_timeout
来设置超时时间，一直等待直到超时；

发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

简述下MySQL8中的新增特性有哪些

MySQL8在功能上的我们需要关注增强主要有：1、账户与安全；2、索引3、InnoDB 增强。

主要表现在：

1、用户的创建与授权需要两条单独的SQL语句执行。认证插件更新。密码管理和角色管理发生变化。

2、隐藏索引，被隐藏的索引不会被优化器使用，但依然真实存在，主要用于软删除，可以根据需要后续真正删除或者重新可视化。

开始真正支持降序索引，以往的MySQL虽然支持降序索引，但是写盘的时候依然是升序保存。MySQL8.0中则是真正的按降序保存。

不再对group by操作进行隐式排序。

索引中可以使用函数表达式，创建表时创建一个函数索引，查询的时候使用同样的函数就可以利用索引了。

3、原子ddl操作，MySQL5.7执行drop命令 drop table
t1,t2; 如果t1存在，t2不存在，会提示t2表不存在，但是t1表仍然会被删除，MySQL8.0执行同样的drop命令，会提示t2表不存在，而且t1表不会被删除，保证了原子性。

自增列持久化，解决了之前的版本，主键重复的问题。MySQL5.7及其以前的版本，MySQL服务器重启，会重新扫描表的主键最大值，如果之前已经删除过id=100的数据，但是表中当前记录的最大值如果是99，那么经过扫描，下一条记录的id是100，而不是101。MySQL8.0则是每次在变化的时候，都会将自增计数器的最大值写入redo log，同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。