MYSQL速通（5/5）

十四、锁

MySQL 的锁机制是数据库并发控制的核心组成部分，用于保证数据的一致性和完整性

1、锁的分类

1.1、按锁的粒度划分

①表级锁：锁定整张表

• 开销小，加锁快，不会出现死锁

• 锁定粒度大，并发度最低

• 如：LOCK TABLES 命令

②行级锁：锁定某一行或多行

• 开销大，加锁慢，会出现死锁

• 锁定粒度最小，并发度最高

• InnoDB 引擎支持

③页级锁：锁定一页（BDB 引擎支持）

• 介于表锁和行锁之间

1.2、 按锁的性质划分

①共享锁(S锁/读锁)：

• 多个事务可以同时获取

• 语法：SELECT ... LOCK IN SHARE MODE (MySQL 8.0 中改为 SELECT ... FOR SHARE)

②排他锁(X锁/写锁)：

• 只有一个事务能获取

• 阻止其他事务获取共享锁或排他锁

• 语法：SELECT ... FOR UPDATE

③意向锁(Intention Locks)：

• 意向共享锁(IS)：事务打算给数据行加共享锁

• 意向排他锁(IX)：事务打算给数据行加排他锁

2、全局锁

2.1基础语法

  # 加入全局锁
  FLUSH TABLES WITH READ LOCK;  -- 简称FTWRL
  ​
  # 释放全局锁
  UNLOCK TABLES;

2.2实操

3、表级锁

①、表锁

Ⅰ、表共享读锁

Ⅱ、表独占写锁

以下是二者的基本语法

  -- 显式加锁
  LOCK TABLES <表名> READ;   -- 读锁
  LOCK TABLES <表名> WRITE;  -- 写锁
  ​
  -- 释放锁
  UNLOCK TABLES;

②、元数据锁

元数据锁（Metadata Lock，简称 MDL）是 MySQL 5.5 之后引入的一种表级锁，由 Server 层自动控制，无需显式声明

Ⅰ、加锁模式

操作类型	自动加的 MDL 类型	是否阻塞 DDL
SELECT / INSERT / UPDATE / DELETE	共享锁（SHARED_READ / SHARED_WRITE）	✅ 不阻塞，读读/读写兼容
ALTER / DROP / CREATE INDEX / RENAME …	排他锁（EXCLUSIVE）	❌ 与任何共享锁互斥，必须等所有共享锁释放

• 共享锁之间互相兼容：多个会话可以同时 CRUD，不会互相阻塞。

• 排他锁与任何锁都不兼容：只要还有一条未提交的事务在访问表，DDL 就会被阻塞；反过来，一旦 DDL 拿到排他锁，后续所有 CRUD 也会被阻塞。

Ⅱ、实操演示

  
  # 开启事务方便查看状态
  begin；
  ​
  # 监控元数据锁
  SELECT * FROM performance_schema.metadata_locks;
  ​
  # 对表进行增删改查操作后（提交），再次监控元数据锁
  SELECT * FROM <表名> ;
  ALTER TABLE <表名> ADD <要加的列>;
  DROP TABLE <表名>;
  commit;
  SELECT * FROM performance_schema.metadata_locks;
  ​
  -- 当然将上面监控元数据锁的命令换成这个可能效果更好
  SELECT
      waiting_thread_id  AS 被阻塞线程,
      waiting_query      AS 被阻塞SQL,
      blocking_thread_id AS 阻塞线程
  FROM sys.schema_table_lock_waits;

只有当右边的事务提交后，才会释放元数据锁，左边的修改表的操作就可以进行。

Ⅲ、错误案例

这里左边进行了 SELECT 和 UPDATE，右边也是 SELECT 和 UPDATE，按理说二者应该兼容，但是右边进行更新操作时却阻塞了，这是为什么呢？

注意：MDL 是表结构层面的锁，行锁才是数据行层面的锁，两者作用对象完全不同。我们这里遇到的阻塞是 InnoDB 行锁冲突，不是元数据锁（MDL）冲突。

③、意向锁

Ⅰ、设计目的

行锁与表锁共存：协调行级锁与表级锁的关系

锁类型

• 意向共享锁（IS）：事务准备加行级共享锁

• 意向排他锁（IX）：事务准备加行级排他锁

Ⅱ、兼容模式

	表IS	表LX	表S	表X
行IS	兼容	兼容	兼容	不兼容
行IX	兼容	兼容	不兼容	不兼容
行S	兼容	不兼容	兼容	不兼容
行X	不兼容	不兼容	不兼容	不兼容

• 意向共享锁和表共享读锁兼容，和表独占写锁排斥

• 意向排他锁和表共享读锁、表独占写锁都排斥

• 意向共享锁和意向排他锁之间兼容

4、行级锁

①、记录锁（Record Lock）

• 锁定索引记录（即使无索引也会锁定隐藏的聚簇索引）

• 基本行锁类型，分为共享锁(S)和排他锁(X)

Ⅰ、行锁兼容模式

请求锁类型	已持有S锁	已持有X锁
申请S锁	兼容	冲突
申请X锁	冲突	冲突

Ⅱ、注意事项

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

精简总结：不通过索引检索数据，行锁变表锁

②、间隙锁（Gap Lock）

• 锁定索引记录之间的间隙（防止幻读）

• 仅存在于 REPEATABLE READ 隔离级别

Ⅰ、注意事项

• 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。

• 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

• 索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止。

Ⅱ、实操演示

即间隙锁锁间隙，临键锁锁间隙+当前记录

③、临键锁（Next-Key Lock）

• 记录锁 + 间隙锁的组合（默认行锁模式）

• 锁定左开右闭区间，如 (5, 10]

• 解决幻读问题的关键机制

④、插入意向锁（Insert Intention Lock）

• 特殊的间隙锁，用于INSERT操作

• 多个事务可在同一间隙的不同位置插入而不冲突

十五、InnoDB引擎

1、逻辑存储结构

1.1、表空间（Tablespace）：

• InnoDB 将数据存储在表空间中，这是 InnoDB 存储数据的基本单位。

• 表空间可以是共享的（系统表空间）或独立的（每个表一个表空间）。

• 系统表空间是所有表共享的，而每个表的独立表空间只包含该表的数据。

1.2、段（Segment）：

• 表空间被划分为多个段，包括数据段、索引段和回滚段。

• 数据段存储表的实际数据。

• 索引段存储索引数据，InnoDB 使用 B+树作为索引结构。

• 回滚段存储事务的回滚信息，用于事务回滚和崩溃恢复。

1.3、区（Extent）：

• 区是 InnoDB 中用于管理存储空间分配的逻辑单位，每个区包含多个连续的页（通常是64个页，即1MB）。

• 当创建新表或索引时，InnoDB 会分配一个或多个区来存储数据和索引。

• 区的引入减少了磁盘I/O操作的次数，因为可以一次性读取或写入更多的数据。

1.4、页（Page）：

• 段进一步被划分为页，InnoDB 中的页大小通常是 16KB。

• 页是 InnoDB 磁盘 I/O 的基本单位，也是数据压缩和加密的基本单位。

1.5、行（Row）：

• 页内存储的是行数据，InnoDB 支持行存储和页存储两种存储格式。

• 行存储格式是 InnoDB 的默认格式，它将每一行数据单独存储。

• 页存储格式将多个行数据存储在同一个页中，适用于列式存储。

2、架构

2.1、内存结构

①、 缓冲池 (Buffer Pool)

功能：缓存表和索引数据的主要内存区域

组成：

• 数据页 (Data pages)：存储表数据

• 索引页 (Index pages)：存储索引数据

• 插入缓冲 (Insert Buffer)：优化非唯一二级索引的插入操作

插入缓冲的增强版是更改缓冲区，是5.5版本MySQL引入的新特性，即更改缓冲区替代了插入缓冲区

• 自适应哈希索引 (Adaptive Hash Index)：自动为频繁访问的页创建哈希索引

InnoDB引擎默认支持的是B+树索引，此处的自适应哈希用于优化查询

• 锁信息 (Lock info)：行锁信息

• 数据字典 (Data Dictionary)：元数据缓存

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

• free page：空闲page，未被使用。

• clean page：被使用page，数据没有被修改过。

• dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

②、日志缓冲区 (Log Buffer)

功能：缓存要写入磁盘的重做日志(redo log)

特性：

• 大小由 innodb_log_buffer_size 控制

• 日志刷新到磁盘的时机由innodb_flush_log_at_trx_commit控制

• 定期或事务提交时刷新到磁盘

③、额外内存池 (Additional Memory Pool)

• 用于存储内部数据结构的内存区域

2.2、磁盘结构

①、表空间 (Tablespaces)

Ⅰ、系统表空间 (System Tablespace)：

• 存储数据字典、双写缓冲、插入缓冲、回滚段

• 文件通常为 ibdata1

Ⅱ、独立表空间 (File-Per-Table Tablespaces)：

• 每个表单独的表空间文件(.ibd)

• 启用 innodb_file_per_table 后创建

Ⅲ、通用表空间 (General Tablespaces)：

• 可包含多个表的共享表空间

• 需要通过创建语句来创建

   
    CREATE TABLESPACE <表空间名> ADD DATAFILE <关联文件> ENGINE = <要使用的引擎名>;

Ⅳ、临时表空间 (Temporary Tablespaces)：

• 存储临时表和临时数据

②、重做日志 (Redo Logs)

功能：确保事务的持久性

组成：

• 通常两个文件：ib_logfile0 和 ib_logfile1

• 循环写入方式

• 特性：

  • 实现 WAL (Write-Ahead Logging) 机制

  • 大小由 innodb_log_file_size 控制

③、撤销日志 (Undo Logs)

功能：实现事务回滚和 MVCC

存储：

• 默认存储在系统表空间

• MySQL 8.0+ 可配置独立表空间

④、双写缓冲 (Doublewrite Buffer)

功能：防止页写入不完整(partial page writes)

位置：

• 内存中的双写缓冲

• 磁盘上的双写文件(在系统表空间中)

2.3、后台线程

①、主线程 (Master Thread)

• 负责协调各种后台操作

②、IO 线程

Ⅰ、读线程：innodb_read_io_threads（4）

Ⅱ、写线程：innodb_write_io_threads（4）

Ⅲ、日志线程：负责重做日志刷新（1）

Ⅳ、插入缓冲线程：负责合并插入缓冲（1）

③、清理线程 (Purge Thread)

• 回收已提交事务不再需要的undo日志

④、页清理线程 (Page Cleaner Thread)

• 负责脏页刷新

• 配合主线程以减轻主线程压力，减少阻塞

3、事务原理

ACID 特性：

• 原子性(Atomicity)：通过undo log实现

• 一致性(Consistency)：通过redo/undo log实现

• 隔离性(Isolation)：通过锁和MVCC实现

• 持久性(Durability)：通过redo log实现

redo log记录物理日志，undo log 记录逻辑日志（历史版本，变更前的模样 ）

4、MVCC

4.1、基本概念

①、当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁), select ... for update、update、insert、delete(排他锁)都是一种当前读。

②、快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed：每次select，都生成一个快照读。

• Repeatable Read：开启事务后第一个select语句才是快照读的地方。

• Serializable：快照读会退化为当前读。

③、MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

4.2、实现原理

InnoDB 通过以下三个隐藏字段实现 MVCC：

• DB_TRX_ID (6字节)：记录最近修改该行的事务ID

• DB_ROLL_PTR (7字节)：回滚指针，配合undo日志记录，指向当前记录的上一个版本

• DB_ROW_ID (6字节，可选)：行ID（当没有主键时自动生成）

4.3、MVCC核心组件

①、 Undo 日志

• 存储行记录的历史版本

• 组成版本链，通过回滚指针(DB_ROLL_PTR)连接

• 类型：

  • INSERT undo log：事务回滚时需要删除，即事务提交后可被立即删除

  • UPDATE undo log：事务回滚时需要将数据还原，不会立即删除

②、 ReadView (读视图)

决定事务能看到哪些版本的数据，包含：

1. m_ids：生成ReadView时活跃的事务ID列表

2. min_trx_id：m_ids中的最小事务ID，最小活跃事务ID

3. max_trx_id：生成ReadView时InnoDB将分配的下一个事务ID，当前最大事务ID+1

4. creator_trx_id：创建该ReadView的事务ID

4.4、MVCC 工作流程

①、版本可见性判断规则

对于每行记录的每个版本，判断是否可见：

• 如果 DB_TRX_ID < min_trx_id：版本已提交，可见

• 如果 DB_TRX_ID >= max_trx_id：版本由将来事务创建，不可见

• 如果min_trx_id <= DB_TRX_ID < max_trx_id

  • 如果 DB_TRX_ID 在 m_ids 中：版本由未提交事务创建，不可见

  • 否则：版本已提交，可见

• 如果 DB_TRX_ID == creator_trx_id：当前事务自己修改的版本，可见

②、 不同隔离级别的实现

• READ UNCOMMITTED：直接读取最新版本，不适用MVCC

• READ COMMITTED：每次读取都生成新的ReadView

• REPEATABLE READ：第一次读取时生成ReadView，后续复用

• SERIALIZABLE：通过加锁实现，不使用MVCC

十六、MySQL管理

1、系统数据库

1.1、 mysql 数据库

①、核心功能：

存储用户权限、安全设置和系统变量

②、主要表结构：

• user：用户账户和全局权限

• db：数据库级权限

• tables_priv：表级权限

• columns_priv：列级权限

• procs_priv：存储过程和函数权限

• proxies_priv：代理用户权限

• time_zone*：时区相关表

• engine_cost/server_cost：优化器成本模型

管理示例：

  -- 查看用户权限
  SELECT * FROM mysql.user WHERE User='username';
  ​
  -- 创建新用户并授权
  CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
  GRANT ALL PRIVILEGES ON database.* TO 'newuser'@'localhost';

1.2、information_schema 数据库

①、核心功能：

提供数据库元数据访问的虚拟数据库（视图）

②、重要视图：

• TABLES：所有表信息

• COLUMNS：所有列信息

• STATISTICS：索引信息

• SCHEMATA：所有数据库

• ROUTINES：存储过程和函数

• TRIGGERS：触发器

• TABLE_CONSTRAINTS：表约束

• INNODB_*：InnoDB相关元数据

查询示例：

  -- 查看数据库：
  ```sql
  ​
  -- 查看数据库大小
  SELECT table_schema "Database", 
         ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "Size (MB)" 
  FROM information_schema.tables 
  GROUP BY table_schema;
  ​
  -- 查看表结构
  SELECT column_name, data_type, is_nullable 
  FROM information_schema.columns 
  WHERE table_name = 'your_table';

1.3、performance_schema 数据库

①、核心功能：

监控服务器性能和资源使用情况

②、主要组件：

• 事件监控：语句、事务、等待事件等

• 性能指标：内存使用、线程活动等

• 锁监控：当前锁等待情况

• 连接信息：客户端连接统计

③、常用表：

• events_statements_summary_by_digest：SQL语句统计

• file_summary_by_event_name：文件I/O统计

• memory_summary_global_by_event_name：内存使用

• threads：服务器线程信息

使用示例：

  -- 查看高负载SQL
  SELECT * FROM performance_schema.events_statements_summary_by_digest 
  ORDER BY sum_timer_wait DESC LIMIT 10;
  ​
  -- 查看锁等待
  SELECT * FROM performance_schema.data_lock_waits;

④、sys 数据库（MySQL 5.7+）

①、核心功能：

提供简化的性能监控视图和存储过程

②、主要特点：

• 基于performance_schema和information_schema的友好视图

• 提供预定义的诊断存储过程

• 帮助DBA快速识别性能问题

③、常用视图：

• schema_table_statistics：表访问统计

• statement_analysis：SQL语句分析

• user_summary：用户活动摘要

• innodb_buffer_stats_by_table：InnoDB缓冲池统计

使用示例：

  -- 查看最耗资源的SQL
  SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;
  ​
  -- 查看内存使用情况
  SELECT * FROM sys.memory_global_total;

2、常用工具

自行了解