深入理解MySQL锁机制与性能优化：详解记录锁、间隙锁、临键锁及慢SQL查询分析

1. 事务隔离和锁机制详解

记录锁

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10。

间隙锁

第二种情况，当我们查询的记录不存在，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

临键锁

第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。

比如我们使用 >5 <9 ，它包含了不存在的区间，也包含了一个 Record 7。

锁住最后一个 key 的下一个左开右闭的区间。

SELECT * FROM t2 WHERE id > 5 AND id <= 7 FOR UPDATE; -- 锁住 (4, 7] 和 (7, 10]
SELECT * FROM t2 WHERE id > 8 AND id <= 10 FOR UPDATE; -- 锁住 (7, 10] 和 (10, +∞)

总结：为什么要锁住下一个左开右闭的区间？——就是为了解决幻读的问题。

MVCC出现幻读问题的本质

为什么出现幻读问题：

假设我们查询表格

事务A对 user 表执行了 SELECT * FROM user WHERE age > 15 FOR UPDATE; 语句，这将会对满足条件的行加行级锁，以阻止其他事务对这些行进行修改。事务B则尝试插入新数据，但是它不会受到事务A的锁定影响，因为插入操作不会涉及到已存在的行。

-- 第一个事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM t2 WHERE name > 6 FOR UPDATE;

-- 第二个事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO t2 VALUES (8, '2');
COMMIT;

-- 查询锁状态
SELECT * FROM sys.innodb_lock_waits;

在这种情况下，事务A加的锁是行级锁（记录锁），而不是间隙锁（Gap Lock）。行级锁仅锁定满足条件的每一行，而不包括间隙或未满足条件的行。因此，事务B可以在不影响事务A的情况下插入新数据。

第二种情况：

Read View

MVCC 机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log 里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢？这时就需要用到 Read View 了，它帮我们解决了行的可见性问题。Read View 保存了当前事务开启时所有活跃（还没有提交）的事务列表，换个角度，可以理解为 Read View 保存了不应该让这个事务看到的其他的事务 ID 列表。

快照读

读取的是快照数据，不加锁的简单的 SELECT 都属于快照读（只是普通的读操作）。

当前读

当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读（包括加锁的读取和 DML 操作）。

如何解决幻读

在快照读情况下，MySQL 通过 MVCC 来避免幻读。

在当前读情况下，MySQL 通过 X 锁或 next-key 来避免其他事务修改：

1. 使用串行化读的隔离级别
2. (update、delete)当where条件为主键时，通过对主键索引加record locks(索引加锁/行锁)处理幻读。
3. (update、delete)当where条件为非主键索引时，通过next-key锁处理。next-key是record locks(索引加锁/行锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)的结合。

2. 如何进行慢 SQL 查询

MySQL 官方文档

打开慢日志开关

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：

SHOW VARIABLES LIKE 'slow_query%';

除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

SHOW VARIABLES LIKE '%long_query%';

可以直接动态修改参数（重启后失效）。

SET @@global.slow_query_log = 1; -- 1 开启，0 关闭，重启后失效 
SET @@global.long_query_time = 3; -- MySQL 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值 

SHOW VARIABLES LIKE '%long_query%'; 
SHOW VARIABLES LIKE '%slow_query%';

或者修改配置文件 my.cnf。

以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON 
long_query_time = 2 
slow_query_log_file = /var/lib/mysql/localhost-slow.log

模拟慢查询：

SELECT SLEEP(10);

查询 user_innodb 表的 500 万数据（检查是不是没有索引）。

SELECT * FROM `user_innodb` WHERE phone