/

MySQL 实战 45 讲 Part1

MySQL 实战 45 讲 | 林晓斌

01 | 基础架构:一条 SQL 查询语句是如何执行的?

image

连接器:跟客户端建立连接、获取权限、维持和管理连接。

SHOW PROCESSLIST;

137462239 db 10.135.4.7:41261 db Query 0 starting show processlist
-- 一个空闲连接
137462293 db 10.130.128.1:47174 db Sleep 24
-- 客户端如果太长时间没动静,连接器就会自动将它断开
select @@wait_timeout;
show variables like 'wait_timeout';
-- 28800 8h

通过执行 mysql_reset_connection 来重新初始化连接资源,这是个接口函数,不是一个 SQL 语句。

查询缓存:不建议使用。在一个表上有更新的时候,跟这个表有关的查询缓存都会失效。

select @@query_cache_type;
-- OFF

分析器:词法分析、语法分析。进行分词和验证语法规则。解析器和预处理器。

  • 解析器处理语法和解析查询,生成一课对应的解析树。
  • 预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在,别名是否有歧义等。如果通过则生成新的解析树,再提交给优化器。

优化器:决定使用哪个索引,决定各个表的连接顺序。

执行器:有没有执行查询的权限,操作引擎,返回结果。执行器调用的次数(rows_examined)与引擎总共扫描行数可能是不等的,后文有例子。

读写、存取数据在 engine 引擎层,连接、鉴权、计算在 server 服务层。

连接的长短是由客户端来决定的,MySQL 服务端不会主动断开连接,除非到了 waiting_timeout 所设置的时间。

查询一个没用 k 列的表 1054 - Unknown column 'k' in 'field list' 是在哪阶段报错的?

  • 答案是:分析器。分析器在词法分析阶段,需要知道 SQL 中的每个字段代表什么意思,所以在这个阶段就可以判断表中存不存在 k 这一列。
  • 不是执行器的原因:有人说在执行器时才打开表获取数据,但是表的字段不是数据,是事先定义好的,所以可以直接读取的,不需要打开表。

02 | 日志系统:一条 SQL 更新语句是如何执行的?

WAL 的全称是 Write-Ahead Logging,先写日志,再写磁盘。

  1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用
  2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1”。
  3. redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
  • 物理:InnoDB 存储引擎提供接口给执行器调用(操作数据),数据库的数据是存在磁盘上的(或者说硬盘上),那么 redo log 记录存储引擎修改硬盘上的数据的操作就叫做物理操作;物理日志就只有“我”自己能用,别人没有共享我的“物理格式”。
  • 逻辑:binlog 归档日志,有两种模式 1 statement 记录 SQL;2 row 格式记录两条数据,数据修改前的样子,数据修改后的样子。记录的是一种逻辑上的变化 。逻辑日志可以给别的数据库,别的引擎使用,已经大家都讲得通这个“逻辑”。
image

把 binlog 夹在 redo log 中间,就是为了保证如果 redo 提交前的任何失败,都会带来回滚,binlog 的写入也应该不会成功,只有这样,才能保证两个一致。

  • 如果提交了 binlog,提交事务接口崩溃了,恢复时 redo log 有日志记录,binlog 有日志记录,一致,直接自动提交事务,事务完成确认数据修改成功。
  • 如果提交 binlog 前就崩了,redo log 是 prepare 阶段,binlog 没有记录不一致,事务回滚,事务执行失败。

redo log 负责事务、crash-safe;binlog 负责归档恢复。redo log 是物理的,binlog 是逻辑的。

redo log 是顺序写,数据文件是随机写。

MySQL 的记录是以“页”为单位存取的,默认大小 16K。也就是说,你要访问磁盘中一个记录,不会只读这个记录,而会把它所在的 16K 数据一起读入内存。

-- 每个 binlog 文件的大小
SELECT @@max_binlog_size / 1024 / 1024;
-- 256.00000000

-- 一页数据大小
SELECT @@innodb_page_size / 1024;
-- 16.0000

redo log 和 binlog 都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。

-- 保证 crash-safe 能力;每次事务的 redo log 都直接持久化到磁盘
select @@innodb_flush_log_at_trx_commit;
-- 2

-- 每次事务的 binlog 都持久化到磁盘
select @@sync_binlog;
-- 0

SELECT @@binlog_format;
-- STATEMENT 是记 SQL 语句,但是有风险比如时间函数
-- ROW 记录行的内容,记两条,更新前和更新后都有

03 | 事务隔离:为什么你改了我还看不见?

脏读(dirty read)读到其他事务未提交的数据,仅发生在读未提交的的隔离级别下:

-- session1 当数据库中一个事务A正在修改一个数据但是还未提交或者回滚
BEGIN;
SELECT k FROM T1 WHERE id=1;
-- 1
UPDATE T1 SET k=2 WHERE id=1;

-- session2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT k FROM T1 WHERE id=1;
-- 2 session2 出现脏读

不可重复读(non-repeatable read)前后读取的记录内容不一致,发生在读未提交、读提交的隔离级别:

-- session1 在一个事务A中多次操作数据,在事务操作过程中(未最终提交)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT k FROM T1 WHERE id=1;
-- 1

-- session2 修改改值
UPDATE T1 SET k=2 WHERE id=1;

-- session1
SELECT k FROM T1 WHERE id=1;
-- 2 session1 出现不可重复读

幻读(phantom read)前后读取的记录数量不一致,发生在读未提交、读提交的隔离级别,InnoDB RR 不发生幻读:

-- session1 获取当前行数量
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM T1 WHERE k=1;
-- 1

-- session2 插入一行数
INSERT INTO T1 (k) VALUES (1);

-- session1
SELECT count(*) FROM T1;
-- 2
-- session1 表数据
BEGIN;
SELECT * FROM T1;
-- id,k 1,1 只有一行数据

-- session2 更新 id 主键
UPDATE T1 SET id=5 WHERE id=1;

-- session1
SELECT * FROM T1;
-- id,k 1,1
UPDATE T1 SET id=id+1;
-- ok
SELECT * FROM T1;
-- 竟然看到了两行 1,1 6,1
-- 1,1 MVCC 保证了 1,1 的存在
-- 因为主键的 UPDATE 在 MySQL 里是以 insert+delete 方式执行的。这个 6 和 1 在 MySQL 看来已经不是同一行数据了,1 的 delete version 是在事务 1 的可见范围,所以才能看得到。如果是非主键就只用一行,就是下面的例子.
-- 这个例子不能被归类为幻读,只不过是当前最新读带来的问题。
-- session1 获取 id=1 的 k
BEGIN;
SELECT k FROM T1;
-- 1 一行数据

-- session2 更新
UPDATE T1 SET k=2;

-- session1
SELECT k FROM T1;
-- 1
UPDATE T1 SET k=k+1;
-- ok
SELECT k FROM T1;
-- 3

不可重复读和幻读区别:由于在 InnoDB RR 下模拟不出幻读的场景,退回到 RC 隔离级别的话就容易把幻读和不可重复读搞混淆。理论上 RR 级别是无法解决幻读的问题, 但是由于 InnoDB 引擎的 RR 级别还使用了 MVCC,所以也就避免了幻读的出现。

后文有讲到 FOR UPDATE 下的幻读。

事务隔离级别:

  • 读未提交(READ UNCOMMITTED)一个事务还没提交时,它做的变更就能被别的事务看到。直接返回记录上的最新值,没有视图概念。
  • 读提交(READ COMMITTED)一个事务提交之后,它做的变更才会被其他事务看到。这个视图是在每个 SQL 语句开始执行的时候创建的。
  • 可重复读(REPEATABLE READ)一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的 read-view。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。这个视图是在事务启动时(第一个 SQL 执行时)创建的,整个事务存在期间都用这个视图。
  • 串行化(SERIALIZABLE)对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。用加锁的方式来避免并行访问。
show variables like '%tx_isolation%';
show variables like 'transaction_isolation';
-- tx_isolation REPEATABLE-READ

select @@tx_isolation;
-- REPEATABLE-READ

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 修改 session 事务隔离级别

SET TRANSACTION Statement | mysql

多版本并发控制(MVCC)

set autocommit=0,这个命令会将这个线程的自动提交关掉。

-- 持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
commit work and chain;
-- 等价于 commit; begin;

如何避免长事务对业务的影响?

  • 确认是否使用了 set autocommit=0,如果没有,则可以使用 set autocommit=1 来避免长事务对业务的影响。
  • 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。
  • 业务连接数据库的时候,根据业务本身的预估,通过 SET MAX_EXECUTION_TIME 命令,来控制每个语句执行的最长时间,避免单个语句意外执行太长时间。
  • 监控 information_schema.Innodb_trx 表,设置长事务阈值。
  • 开发测试阶段输出所用 general_log,分析日志提前发现问题。

04 | 深入浅出索引(上)

  • 哈希表:这种结构适用于只有等值查询的场景。
  • 有序数组:有序数组在等值查询和范围查询场景中的性能就都非常优秀。但有序数组索引只适用于静态存储引擎
  • 搜索树:平衡二叉树是 O(log(N)) 的查询复杂度;N 叉树,以 InnoDB 整数字段索引为例,这个 N 差不多是 1200。

MySQL 默认一个节点的长度为 16K,一个整数(bigint)字段索引的长度为 8B,另外每个索引还跟着 6B 的指向其子树的指针;所以 16K/14B ≈ 1170

innodb B+树主键索引的叶子节点存的是什么:

回复 1:

InnoDB 磁盘管理的最小单位就是“页”,也就是说无论是叶子节点、非叶子节点和行数据,都是存放在页当中。页组成结构有头部数据、主体数据和尾部数据。头部数据主要存的是页相关数据,例如上一页、下一页、当前页号等。是一个双向链表结构。主体数据主要关注索引和数据的存储,也就是我们常说的索引和数据的存储位置。主体数据当中有一个“User Records”的概念,用来存储索引和数据,是一个单链表结构。
User Records 根据节点的不同,User Records 又分为四种不同类型:主键索引树叶子节点和非叶子节点,二级索引树叶子节点和非叶子节点。
有了页和 User Records 的认识,其实说叶子节点存的是页是一种笼统的回答,基于我的理解,我认为叶子节点(主键索引树叶子节点)存放的是行数据更为贴切。

回复 2:

B+树的叶子节点是 page(页),一个页里面可以存多个行。 B+树的结点跟 innoDB 的“页”都属于一种抽象逻辑概念。如果你要问“存”的是什么?我觉得回答行数据没毛病。因为存的不可能是“页”。这一逻辑概念,只能说这个叶结点大小等于 innoDB 里设置的页大小,或者说这个叶结点其实就是“页”。但存的是什么?那当然是数据,什么数据?当然是表中的行数据。

索引类型分为主键索引和非主键索引。

  • 主键索引也被称为聚簇索引(clustered index)
  • 非主键索引也被称为二级索引(secondary index)

基于非主键索引的查询需要多扫描一棵索引树,也就是主键索引树,也就是回表操作。

自增主键的意义:

  • 性能方面:每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂(页分裂)。
  • 存储空间方面:主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

有没有什么场景适合用业务字段直接做主键的呢?

  • 只有一个索引
  • 该索引必须是唯一索引

即典型的 KV 场景。

非聚集索引上为啥叶子节点的 value 为什么不是地址,这样可以直接定位到整条数据,而不用再次对整棵树进行查询?

这个叫作“堆组织表”,MyISAM 就是这样的,各有利弊。你想一下如果修改了数据的位置的情况,InnoDB 这种模式是不是就方便些。主键索引页分裂的场景,就可能会导致主键记录的地址发生变化,这时候需要更新每一个索引上面对主键记录地址的引用。

05 | 深入浅出索引(下)

覆盖索引:在查询里索引已经覆盖了查询需要的列。覆盖索引可以减少树的搜索次数,显著提升查询性能,是一个常用的性能优化手段。

select * from T where k between 3 and 5;

表中 k 的值是:1,2,3,5,6,7。引擎内部使用覆盖索引在索引 k 上其实读了三个记录(第一次 3,第二次 3 的下一个 5,第三次 5 的下一个 6 不满足 ),但是对于 MySQL 的 Server 层来说,它只是找引擎拿到了两条记录,因此 MySQL 认为扫描行数是 2。这就是引擎实际扫描条数不等于 MySQL explain 语句中的 rows 字段的原因。

最左前缀原则:B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

如果需要常查人的名字与年龄。可以建立索引 (name,age) 与 (age),而不是 (age,name) 与 (age),考虑的原则就是空间。

索引下推优化(index condition pushdown):

联合索引(name,age)为例,名字第一个字是张,而且年龄是 10 岁的所有男孩:

select * from tuser where name like '张%' and age=10 and ismale=1;

这个语句在搜索索引树的时候,只能用 “张”。

  • 在 MySQL 5.6 之前,只能从符合 ‘张%’ 开始一个个回表。到主键索引上找出数据行,再对比字段值。
  • 而 MySQL 5.6 引入的索引下推优化(index condition pushdown),可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
image

InnoDB 在(name,age)索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。

联合索引中是按索引列的顺序,来排序的。(name,age)先排 name 列的值,再排 age 列的值。

在满足语句需求的情况下,尽量少地访问资源是数据库设计的重要原则之一。

06 | 全局锁和表锁:给表加个字段怎么有这么多阻碍?

全局锁:

-- 加全局读锁 FTWRL,整个库处于只读状态;做全库逻辑备份
FLUSH TABLES WITH READ LOCK;

-- 插入一行数据

SHOW PROCESSLIST;
-- Waiting for global read lock

-- 释放锁
UNLOCK TABLES;

但是让整库都只读,听上去就很危险:

  • 如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
  • 如果你在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog,会导致主从延迟。

在可重复读隔离级别下开启一个事务,来确保拿到一致性视图。mysqldump 使用参数 -single-transaction 就是如此执行的。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。

有了可重复读的事务隔离级别却还需要 FTWRL 的原因是:引擎不都支持这个事务隔离级别。

既然要全库只读,为什么不使用 set global readonly=true 的方式

  • 在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。修改 global 变量的方式影响面更大。
  • 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将
    整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。

表级锁:

第一类表锁:lock-tables | mysql

LOCK TABLES T1 READ, T2 WRITE;
-- 其他线程 写 T1、读写 T2 的语句都会被阻塞
-- 当前线程也只能执行 读 T1、读写 T2 的操作,其他表都不能访问

-- 当前线程读 T2
-- Waiting for table metadata lock

-- 当前线程读 T3
-- 1100 - Table 'T3' was not locked with LOCK TABLES
-- 查看表锁
SHOW OPEN TABLES WHERE In_use > 0;

第二类 MDL(metadata lock):

MDL 不需要显式使用:

  • 当对一个表做增删改查操作的时候,加 MDL 读锁。读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。共享锁。
  • 当要对表做结构变更操作的时候,加 MDL 写锁。读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。排他锁。

给一个小表加个字段,导致整个库挂了:

-- session1
SET autocommit = 0;
-- 关闭自动提交

BEGIN;
-- 开启使用

SELECT * FROM T;
-- 查询 T 表,一切 ok
-- 获取 MDL 读锁 ok,因为在事务中,查询结束后没有释放读锁。

-- 事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

SELECT * FROM information_schema.innodb_trx;
-- 查询事务有个 RUNNING
-- session2
SELECT * FROM T;
-- 查询 T 表,一切 ok
-- 获取 MDL 读锁 ok,查询结束后释放读锁。
-- session3
ALTER TABLE `T` ADD COLUMN `c2` varchar(255) NULL;
-- 修改字段,卡住
-- blocked 原因:是因为 session1 的 MDL 读锁还没有释放,而 session3 需要 MDL 写锁,因此只能被阻塞。

SHOW OPEN TABLES WHERE In_use > 0;
-- In_use 1
-- session4
SELECT * FROM T;
-- 查询 T 表,卡住
-- blocked 原因:对表的增删改查操作都需要先申请 MDL 读锁,都被 session3 阻塞。
image

这里自己想了一个问题:session4 卡住后,session1 COMMIT 后,session4 的 SELECT 执行结果是否有 session3 的新列呢?

从结果上看是没有的。开始以为是事务级别,但是调整为 READ UNCOMMITTED,session4 仍然没有新列。后来了解到 online DDL 意识到应该有关系。测试后得出 session3 在获取写锁后,在做 DDL 前会释放写锁加读锁,这时 session4 就可以执行了,但是这时 DDL 还没有执行,所以 session4 的执行结果没有新列。

如果 session3 执行语句是:

ALTER TABLE `T` ADD COLUMN `c2` varchar(255) NULL, LOCK=EXCLUSIVE;
-- NONE:允许并发查询和DML操作;
-- SHARED:允许并发查询,但不允许DML操作;
-- DEFAULT:允许尽可能多的并发查询或DML操作(或两者都允许),没指定LOCK选项默认就为DEFAULT;
-- EXCLUSIVE:不允许并发查询和DML操作。

EXCLUSIVE 时写锁会一直持有,一直等到 DDL 完毕 session4 才开始执行,所以 session4 的执行结果有新列。

衍生问题:session3 如果没有 LOCK=EXCLUSIVE,session4 查询前 BEGIN; 开启事务那结果是如何呢?实验结果:session1 提交后,session3 session4 依然阻塞。

衍生问题:session3 添加 LOCK=EXCLUSIVE,session4 查询前 BEGIN; 开启事务那结果是如何呢?实验结果:ssession1 提交后,session3 session4 正常结束。

分析猜测 session3 阻塞的原因是:session1 提交后释放了读锁,session3 session4 拿到读锁,session3 DDL 操作完成后又要拿写锁,但是 session4 的读锁未释放,所以 session3 阻塞。那 session4 为啥阻塞呢?session4 可以获取读锁就应该可以执行完毕,遗留问题。

如何安全地给小表加字段?

  • 解决长事务,事务不提交,就会一直占着 MDL 锁。
  • 在 ALTER TABLE 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

实测:MySQL 5.7.30 不支持。腾讯云 MySQL 5.7.18-txsql-log 支持。

07 | 行锁功过:怎么减少行锁对性能的影响?

在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

举例:

  1. 从顾客 A 账户余额中扣除电影票价;UPDATE
  2. 给影院 B 的账户余额增加这张电影票价;UPDATE 最容易发送锁等待的地方
  3. 记录一条交易日志。INSERT

以 3、1、2 顺序执行可以最大程度地减少了事务之间的锁等待,提升了并发度。

当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。

image

上图 session1 在等 session2 id=2 的锁,session2 在等 session1 id=1 的锁,进入死锁状态。

-- 所等待超时时间 s
SELECT @@innodb_lock_wait_timeout;
-- 7200 2h

-- 死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务
SELECT @@innodb_deadlock_detect;
-- 1213 - Deadlock found when trying to get lock; try restarting transaction

主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的:

每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。

每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是 O(n)的操作。假设有 1000 个并发线程要同时更新同一行,那么死锁检测操作就是 100 万这个量级的。这期间要消耗大量的 CPU 资源。即:热点行更新导致的性能问题。

可以通过尝试将热点数据拆分多行进行处理,提高并发处理。

08 | 事务到底是隔离的还是不隔离的?

begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句,事务才真正启动。如果你想要马上启动一个事务:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

有两个“视图”的概念:

  • 一个是 view。它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果。创建视图的语法是 create view …,而它的查询方法与表一样。
  • 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view,用于支持 RC(Read Committed,读提交)和 RR(Repeatable Read,可重复读)隔离级别的实现。

在可重复读隔离级别下,事务在启动的时候就“拍了个快照”。注意,这个快照是基于整库的。

InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。