【JavaGuide面试总结】MySQL篇·中

1.MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的。除了 SERIALIZABLE 隔离级别其他的隔离级别都是基于 MVCC 实现。

不过 SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制就比如 REPEATABLE-READ 在当前读情况下需要加锁来保证不会出现幻读。

---

2.表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁一锁就锁整张表这在并发写的情况下性非常差。

InnoDB 不光支持表级锁还支持行级锁默认为行级锁。行级锁的粒度更小仅对相关的记录上锁即可（对一行或者多行记录加锁所以对于并发写入操作来说 InnoDB 的性能更高。

表级锁和行级锁对比 ：

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外是针对非索引字段加的锁对当前操作的整张表加锁实现简单资源消耗也比较少加锁快不会出现死锁。其锁定粒度最大触发锁冲突的概率最高并发度最低MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定粒度最小的一种锁是针对索引字段加的锁只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小并发度高但加锁的开销也最大加锁慢会出现死锁。

InnoDB 的行锁是针对索引字段加的锁表级锁是针对非索引字段加的锁。😓

当我们执行 UPDATE、DELETE 语句时如果 WHERE条件中字段没有命中唯一索引或者索引失效的话就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到一定要多多注意

---

3.共享锁和排他锁简单说说

不论是表级锁还是行级锁都存在共享锁（Share LockS 锁和排他锁（Exclusive LockX 锁这两类：

• 共享锁（S 锁 ：又称读锁事务在读取记录的时候获取共享锁允许多个事务同时获取（锁兼容。
• 排他锁（X 锁 ：又称写锁/独占锁事务在修改记录的时候获取排他锁不允许多个事务同时获取。如果一个记录已经被加了排他锁那其他事务不能再对这条事务加任何类型的锁（锁不兼容。

由于 MVCC 的存在对于一般的 SELECT 语句InnoDB 不会加任何锁。不过 你可以通过以下语句显式加共享锁或排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

---

4.意向锁有什么作用？

如果需要用到表锁的话如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁共有两种：

• 意向共享锁（Intention Shared LockIS 锁：事务有意向对表中的某些记录加共享锁（S 锁加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive LockIX 锁：事务有意向对表中的某些记录加排他锁（X 锁加排他锁之前必须先取得该表的 IX 锁。

---

5.InnoDB 有哪几类行锁？

MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock ：也被称为记录锁属于单个行记录上的锁。
• 间隙锁（Gap Lock ：锁定一个范围不包括记录本身。
• 临键锁（Next-key Lock ：Record Lock+Gap Lock锁定一个范围包含记录本身。记录锁只能锁住已经存在的记录为了避免插入新记录需要依赖间隙锁。

InnoDB 的默认隔离级别 RR（可重读是可以解决幻读问题发生的主要有下面两种情况：

• 快照读 ：由 MVCC 机制来保证不出现幻读。
• 当前读 ： 使用 Next-Key Lock 进行加锁来保证不出现幻读。

---

6.当前读和快照读有什么区别？

快照读就是单纯的 SELECT 语句但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE

快照读的情况下如果读取的记录正在执行 UPDATE/DELETE 操作读取操作不会因此去等待记录上 X 锁的释放而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读下InnoDB 才会使用快照读：

• 在 RC 级别下对于快照数据一致性非锁定读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下对于快照数据一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。😔

当前读 就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

---

7.RC 和 RR 隔离级别下 MVCC 的差异

在事务隔离级别 RC 和 RR 下InnoDB 存储引擎使用 MVCC（非锁定一致性读但它们生成 Read View 的时机却不同

• 在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)
• 在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表

---

8.你们项目中的MySQL是如何存储 IP 地址的？

可以将 IP 地址转换成整形数据存储性能更好占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON() ：把 ip 转为无符号整型 (4-8 位)
• INET_NTOA() ：把整型的 ip 转为地址

插入数据前先用 INET_ATON() 把 ip 地址转为整型显示数据时使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

# 插入ip数据
insert into users (name,ip) VALUES ('dahezhiquan',INET_ATON('192.168.5.102'));
# 读取ip数据
SELECT INET_NTOA(ip) FROM users where id = 1;

查询结果：

---

9.RR隔离级别下如何防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：

1、执行普通 select此时会以 MVCC 快照读的方式读取数据

在快照读的情况下RR 隔离级别只会在事务开启后的第一次查询生成 Read View 并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见实现了可重复读和防止快照读下的 “幻读”

2、执行 select...for update/lock in share mode、insert、update、delete 等当前读

在当前读下读取的都是最新的数据如果其它事务有插入新的记录并且刚好在当前事务查询范围内就会产生幻读InnoDB 使用Next-key Lock来防止这种情况。当执行当前读时会锁定读取到的记录的同时锁定它们的间隙防止其它事务在查询范围内插入数据。只要我不让你插入就不会发生幻读

---

10.你们项目中是如何存储时间的？

Datetime 和 Timestamp 是 MySQL 提供的两种比较相似的保存时间的数据类型

通常我们都会首选 Timestamp。

那么为什么首选Timestamp呢？

DateTime 类型没有时区信息

DateTime 类型是没有时区信息的（时区无关 DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。当你的时区更换之后比如你的服务器更换地址或者更换客户端连接时区设置的话就会导致你从数据库中读出的时间错误

Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化自动换算成相应的时间说简单点就是在不同时区查询到同一个条记录此字段的值会不一样。

DateTime 类型耗费空间更大

Timestamp 只需要使用 4 个字节的存储空间但是 DateTime 需要耗费 8 个字节的存储空间。但是这样同样造成了一个问题Timestamp 表示的时间范围更小。

• DateTime ：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp： 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

数值型时间戳是更好的选择吗？

很多时候我们也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。

这种存储方式的具有 Timestamp 类型的所具有一些优点并且使用它的进行日期排序以及对比等操作的效率会更高跨系统也很方便毕竟只是存放的数值。缺点也很明显就是数据的可读性太差了你无法直观的看到具体时间。

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
|                            1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32       |
+---------------------------+
1 row in set (0.01 sec)