公号:码农充电站pro

主页:https://codeshellme.github.io

这 5 篇文章是我在学习 MySQL 的过程中,总结的笔记:

  • 第一篇 MySQL 学习笔记1-基础篇
    • 1,关于 SQL
    • 2,一条 SQL 的执行步骤
    • 3,MySQL 存储引擎
    • 4,数据库的基本操作
    • 5,关于自增主键
    • 6,SELECT 语句顺序
    • 7,WHERE 子句
    • 8,DISTINCT 去重
    • 9,关于 COUNT(*) 操作
    • 10,MYSQL 函数
    • 11,GROUP BY 数据分组
    • 12,子查询(嵌套查询)
    • 13,JOIN 连接查询
    • 14,VIEW 视图
    • 15,存储过程
    • 16,临时表
    • 17,MySQL 权限管理
    • 18,Python 操作 MySQL 的库
  • 第二篇 MySQL 学习笔记2-进阶篇-上
    • 19,MySQL 的基础架构
    • 20,数据库缓冲池
    • 21,数据库中的存储结构
    • 22,InnoDB 中表数据的存储
  • 第三篇 MySQL 学习笔记3-进阶篇-中
    • 23,事务处理
    • 24,事务的隔离级别
    • 25,MySQL 中的锁
    • 26,MVCC 多版本并发控制
    • 27,MySQL 传输数据的原理
  • 第四篇 MySQL 学习笔记4-进阶篇-下
    • 28,Join 语句的原理
    • 29,MySQL 如何进行排序
    • 30,MySQL 中 kill 命令的原理
    • 31,MySQL 中的 mysqldump 命令
    • 32,MySQL 主从同步
    • 33,MySQL 主备原理
  • 第五篇 MySQL 学习笔记5-调优篇
    • 34,关于 MySQL 索引
    • 35,定位数据库 SQL 性能问题

23,事务处理

23.1,事务的四大特性

ACID 是数据库管理系统为了保证事务的正确性而提出来的一个理论,ACID 包含四个约束:

  • 原子性Atomicity):一个事务中的所有操作,要么全部完成,要么全部不完成,不会在中间某个环节结束。
    • 如果事务在执行过程中发生错误,会被回滚到事务开始前的状态
  • 一致性Consistency):事务开始之前和事务结束以后,数据库的完整性没有被破坏。
  • 隔离性Isolation):多个事务并发处理时,事务之间的数据可见性
    • 读未提交:一个事务还没提交时,它做的变更就能被别的事务看到。
    • 读已提交:一个事务提交之后,它做的变更才会被其他事务看到。
    • 可重复读:一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。
      • MySQL 的默认隔离级别。
    • 串行化:此时不存在并发事务,但是性能较低。
  • 持久性Durability):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

23.2,事务的控制语句

事务的控制语句:

  • START TRANSACTION 或者 BEGIN:显式开启一个事务。
    • begin/start transaction 命令并不是一个事务真正的起点,在执行到它们之后的第一个操作表的语句,事务才真正启动。
    • start transaction with consistent snapshot 命令会马上启动一个事务。
  • COMMIT:提交事务。当提交事务后,对数据库的修改是永久性的。
  • ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]:回滚事务。意思是撤销正在进行的所有没有提交的事物,或者将事务回滚到某个保存点。
  • SAVEPOINT:在事务中创建保存点,方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
    • RELEASE SAVEPOINT:删除某个保存点。
  • SET TRANSACTION:设置事务的隔离级别

以下 SQL 可查询持续时间超过 60s 的事务:

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事务有两种方式:

  • 隐式事务:事务自动提交,MySQL 默认是隐式提交。
    • set autocommit = 1,打开自动提交
    • 如果没有使用 START TRANSACTIONBEGIN 显示开启事物,则每条 SQL 语句都会自动提交
    • 当采用 START TRANSACTIONBEGIN 来显式开启事务,那么这个事务只有在 COMMIT 时才会生效,在 ROLLBACK 时才会回滚。
  • 显示事务:事务需手动提交
    • set autocommit = 0,关闭自动提交
    • 此时不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务,都需要用 COMMIT 进行提交

completion_type 参数的含义:

  • completion_type=0,这是默认情况。当我们执行 COMMIT 的时候会提交事务,在执行下一个事务时,还需要我们使用 START TRANSACTION 或者 BEGIN 来开启。
  • completion_type=1,这种情况下,当我们提交事务后,相当于执行了 COMMIT AND CHAIN,也就是开启一个链式事务,即当我们提交事务之后会开启一个相同隔离级别的事务
  • completion_type=2,这种情况下 COMMIT 相当于 COMMIT AND RELEASE,也就是当我们提交后,会自动与服务器断开连接。

23.3,事务示例

示例1:

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;                           # 显示开启事物
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';   # 由于 name 不能重复,所以插入失败
ROLLBACK;                        # 回滚,两次插入均被回滚
SELECT * FROM test;

结果如下:

在这里插入图片描述

示例 2:

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞'; # 没有显示开启事物,执行完自动提交
INSERT INTO test SELECT '张飞'; # 出错
ROLLBACK;                      # 只回滚了最后一个 insert
SELECT * FROM test;

结果如下:

在这里插入图片描述

示例 3:

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;       # 每次提交之后,自动开启下一个事物
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;                          # 自动开启下一个事物,直到下一个 COMMIT 提交
INSERT INTO test SELECT '张飞';   # 没有执行COMMIT,不提交
INSERT INTO test SELECT '张飞';   # 出错
ROLLBACK;                        # 两个 `张飞` 都回滚
SELECT * FROM test;

结果如下:

在这里插入图片描述

24,事务的隔离级别

事务在并发处理时会出现 3 种异常(SQL92 标准定义了这三种异常):

  • 脏读:还没有提交的事物,就别读到了。
  • 不可重复读:在同一个事务里读同一条记录,两次读的结果不一样。
  • 幻读

事物的隔离级别用于解决这 3 种异常。

SQL92 标准定义了事务的 4 个 隔离级别:

  • 读未提交:可以读到没有提交的事务。
  • 读已提交:只能读到已提交的事务。
  • 可重复读:保证一个事务在相同的查询条件下读到的数据是一致的。 - MySQL 的默认隔离级别。
  • 串行化:所有的事务只能串行处理,不能并发处理,能解决所有的异常问题,但是性能最低。

不同的隔离级别能够解决不同的异常问题:

在这里插入图片描述

查看当前的隔离级别:

mysql> SHOW VARIABLES LIKE 'transaction_isolation';

结果如下:

在这里插入图片描述

设置事务的隔离级别:

mysql> SET SESSION/global TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

24.1,脏读

在这里插入图片描述

24.2,不可重复读

在这里插入图片描述

24.3,幻读

看以下场景:

在这里插入图片描述

session A 里执行了三次查询,分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同,都是 select * from t where d=5 for update当前读:就是要能读到所有已经提交的记录的最新值)。

Q3 读到 id=1 这一行的现象,被称为幻读。幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行

说明

  • 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现
  • 上面 session B 的修改结果,被 session A 之后的 select 语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”

在 T3 时刻,我们给所有行加锁的时候,id=1 这一行还不存在,不存在也就加不上锁。也就是说,即使把所有的记录都加上锁,还是阻止不了新插入的记录。这就是幻读出现的原因

24.4,不可重复读 VS 幻读的区别

不可重复读是同一条记录的内容被修改了,重点在于UPDATE或DELETE

幻读是查询某一个范围的数据行变多了或者少了,重点在于INSERT。比如,SELECT 显示不存在,但是INSERT的时候发现已存在,说明符合条件的数据行发生了变化,也就是幻读的情况,而不可重复读指的是同一条记录的内容被修改了。

24.5,幻读的解决办法

产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。间隙锁,锁的就是两个值之间的空隙

间隙锁和行锁合称 next-key lock

主键索引上的行锁和间隙锁

在这里插入图片描述

这样,当执行 select * from t where d=5 for update 时,就不止是给数据库中已有的 6 个记录加上了行锁,还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

间歇锁的缺点:

  • 间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实影响了并发度

间隙锁是在可重复读隔离级别下才会生效的。所以,如果把隔离级别设置为读提交(要看业务需求),就没有间隙锁了。但同时,你要解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row。这,也是现在不少公司使用的配置组合。

25,MySQL 中的锁

事务有 4 大隔离级别,这些隔离级别的实现都是通过锁来完成的。

加锁的目的是为了,在多线程同时操作一个数据的时候,保证数据的一致性。

25.1,锁的划分

数据库中的锁有以下三种划分方式:

  • 按照锁的粒度划分
  • 从数据库管理的角度对锁进行划分
  • 从程序员的视角来看锁
1,按照锁的粒度划分

可分为 5 种:

  • 行锁:按照行的粒度对数据进行锁定。锁定力度小,发生锁冲突概率低,可以实现的并发度高,但是对于锁的开销比较大,加锁会比较慢,容易出现死锁情况。InnoDB 三种行锁的方式:
    • 记录锁:针对单个行记录添加锁。
    • 间隙锁(Gap Locking):可以锁住一个范围(索引之间的空隙),但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。
    • Next-Key 锁:可以锁住一个范围,同时锁定记录本身,相当于间隙锁 + 记录锁,可以解决幻读的问题。
  • 页锁:在页的粒度上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录。当我们使用页锁的时候,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。
  • 表锁:对数据表进行锁定,锁定粒度很大,同时发生锁冲突的概率也会较高,数据访问的并发度低。不过好处在于对锁的使用开销小,加锁会很快。
  • 区锁
  • 数据库锁(全局锁):就是对整个数据库实例加锁

不同的数据库和存储引擎支持的锁粒度不同:

行锁 页锁 表锁
InnoDB 支持 支持
MyISAM 支持
Oracle 支持 支持
SQL Server 支持 支持 支持
BDB 支持 支持
2,从数据库管理的角度对锁划分

常见的有以下 2 种:

  • 共享锁:也叫读锁或 S 锁。共享锁锁定的资源可以被其他用户读取,但不能修改。在进行SELECT的时候,会将对象进行共享锁锁定,当数据读取完毕之后,就会释放共享锁,这样就可以保证数据在读取时不被修改。
  • 排它锁:也叫独占锁、写锁或 X 锁。排它锁锁定的数据只允许进行锁定操作的事务使用,其他事务无法对已锁定的数据进行查询或修改
3,从程序员的视角来看锁

可以将锁分成:

  • 乐观锁:乐观锁认为对同一数据的并发操作不会总发生,属于小概率事件,不用每次都对数据上锁,也就是不采用数据库自身的锁机制,而是通过程序来实现。在程序上,一般可以采用版本号机制或者时间戳机制实现。
    • 乐观锁的版本号机制:在表中设计一个版本字段 version,第一次读的时候,会获取 version 字段的取值。然后对数据进行更新或删除操作时,会执行 UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改,修改就不会成功。
    • 乐观锁的时间戳机制:时间戳和版本号机制一样,也是在更新提交的时候,将当前数据的时间戳和更新之前取得的时间戳进行比较,如果两者一致则更新成功,否则就是版本冲突。
  • 悲观锁:对数据被其他事务的修改持保守态度,会通过数据库自身的锁机制来实现,从而保证数据操作的排它性。

在这里插入图片描述

乐观锁与悲观锁的适用场景:

  • 乐观锁:适合读多写少的场景,它的优点在于不存在死锁问题。
  • 悲观锁:适合写多读少的场景,因为写的操作具有排它性。采用悲观锁的方式,可以在数据库层面阻止其他事务对该数据的操作权限,防止读 - 写写 - 写的冲突。

25.2,锁升级

每个层级的锁数量是有限制的,因为锁会占用内存空间,锁空间的大小是有限的。

当某个层级的锁数量超过了这个层级的阈值时,就会进行锁升级

锁升级就是用更大粒度的锁替代多个更小粒度的锁,比如 InnoDB 中行锁升级为表锁,这样做的好处是占用的锁空间降低了,但同时数据的并发度也下降了。

25.3,意向锁

当我们想要获取某个数据表的排它锁的时候,需要先看下这张数据表有没有上了排它锁。如果这个数据表中的某个数据行被上了行锁,我们就无法获取排它锁。这时需要对数据表中的行逐一排查,检查是否有行锁,如果没有,才可以获取这张数据表的排它锁。

这个过程有些麻烦,这里就需要用到意向锁

意向锁(Intent Lock),简单来说就是给更大一级别的空间示意里面是否已经上过锁。举个例子,你可以给整个房子设置一个标识,告诉它里面有人,即使你只是获取了房子中某一个房间的锁。这样其他人如果想要获取整个房子的控制权,只需要看这个房子的标识即可,不需要再对房子中的每个房间进行查找。

返回数据表的场景,如果我们给某一行数据加上了排它锁,数据库会自动给更大一级的空间,比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排它锁了,这样当其他人想要获取数据表排它锁的时候,只需要了解是否有人已经获取了这个数据表的意向排他锁即可

如果事务想要获得数据表中某些记录的共享锁,就需要在数据表上添加意向共享锁。同理,事务想要获得数据表中某些记录的排他锁,就需要在数据表上添加意向排他锁。这时,意向锁会告诉其他事务已经有人锁定了表中的某些记录,不能对整个表进行全表扫描。

25.4,死锁

死锁就是多个事务在执行过程中,因为竞争某个相同的资源而造成阻塞的现象。

如何避免死锁:

  • 如果事务涉及多个表,操作比较复杂,那么可以尽量一次锁定所有的资源,而不是逐步来获取,这样可以减少死锁发生的概率;
  • 如果事务需要更新数据表中的大部分数据,数据表又比较大,这时可以采用锁升级的方式,比如将行级锁升级为表级锁,从而减少死锁产生的概率;
  • 不同事务并发读写多张数据表,可以约定访问表的顺序,采用相同的顺序降低死锁发生的概率。
  • 采用乐观锁的方式

MySQL 遇到死锁时的策略

有两种策略(只能选其中一个):

  • 进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置,默认值是 50s。
  • 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。
    • 将参数 innodb_deadlock_detect 设置为 on(默认值),表示开启这个逻辑(超时逻辑回被关闭)。

25.5,锁操作命令

1,数据库锁(全局锁)

MySQL 提供了加全局读锁的命令(FTWRL):

> flush tables with read lock -- 加锁
> unlock tables				  -- 释放锁

该命令会使整个库处于只读状态,之后其他线程的以下语句会被阻塞:

  • 数据更新语句(数据的增删改)、
  • 数据定义语句(包括建表、修改表结构等)
  • 更新类事务的提交语句

如果执行 FTWRL 命令之后,客户端发生异常断开,那么 MySQL 会自动释放这个全局锁

2,表级锁

MySQL 里面表级别的锁有两种:

  • 表锁:客户端断开的时候自动释放
    • 加锁:lock tables … read/write
      • 示例:lock tables t1 read, t2 write
    • 释放锁:unlock tables
  • 元数据锁(MDL):
    • MDL 的作用是,保证读写的正确性。
    • MDL 不需要显式使用,在访问一个表的时候会被自动加上。
    • 在 MySQL 5.5 版本中引入了 MDL
      • 当对一个表做增删改查操作的时候,加 MDL 读锁;
      • 当要对表做结构变更操作的时候,加 MDL 写锁。

读写锁的互斥关系:

  • 读锁之间不互斥,因此可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。

表锁一般是在数据库引擎不支持行锁/事务的时候才会被用到的。

共享锁操作:

# 给表加共享锁
LOCK TABLE tableName READ;

# 当对数据表加上共享锁的时候,该数据表就变成了只读模式,此时想要更新表中的数据,比如:
UPDATE tableName SET product_id = 10002 WHERE user_id = 912178;

# 系统会做出如下提示:
ERROR 1099 (HY000): Table 'product_comment' was locked with a READ lock and can't be updated

# 解除共享锁,不需要参数
UNLOCK TABLE;

# 给某一行加共享锁,可以像下面这样:
SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 LOCK IN SHARE MODE;

排它锁操作:

# 给表加排它锁
LOCK TABLE tableName WRITE;

# 这时只有获得排它锁的事务可以对 tableName 进行查询或修改,
# 其他事务如果想要在 tableName 表上查询数据,则需要等待。

# 在某个数据行上添加排它锁,则写成如下这样:
SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 FOR UPDATE;

# 释放排它锁
UNLOCK TABLE;

命令 > show open tables 可以查看当前数据库中关于表锁的使用情况。 命令 > unlock tables 可以释放所有的表锁

执行 > show open tables 命令后,结果如下:

在这里插入图片描述

其中 1 表示有锁占用,0 表示无锁占用。

另外,当我们对数据进行更新的时候,也就是 INSERTDELETE 或者 UPDATE 的时候,数据库也会自动使用排它锁,防止其他事务对该数据行进行操作

命令 show status like '%row_lock%'; 可以查看当前系统中行锁的状态:

在这里插入图片描述

对各个状态量的说明如下:

  • Innodb_row_lock_current_waits:当前正在等待锁的数量(重要
  • Innodb_row_lock_time:从系统启动到现在,锁定的总时间(重要
  • Innodb_row_lock_time_avg:每次等待锁,花费的平均时长(重要
  • Innodb_row_lock_time_max:从系统启动到现在,等待最长的一次时间
  • Innodb_row_lock_waits:从系统启动到现在,总共等待的次数(重要
3,行级锁

不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。

InnoDB 引擎支持行锁。行锁,分成读锁和写锁。下图是这两种类型行锁的冲突关系:

在这里插入图片描述

行锁就是针对数据表中行记录的锁。比如事务 A 更新了一行,而这时候事务 B 也要更新同一行,则必须等事务 A 的操作完成后才能进行更新。

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

例如下图中:

在这里插入图片描述

事务 B 会被阻塞,直到事务 A 的 commit 执行。

26,MVCC 多版本并发控制

MVCC 就是采用乐观锁思想的一种实现。

26.1,MVCC 的作用

数据库有四种隔离方式,前三种都存在一定的问题,只有串行化不存在问题,但是串行化的并发性能最低。

在这里插入图片描述

MVCC 的存在就是采用乐观锁的方式,即能解决各种读的问题,又不影响数据库的并发性能;它可以在大多数情况下替代行级锁,降低系统的开销

MVCC 可以解决以下问题:

  • 读写之间阻塞的问题,通过 MVCC 可以让读写互相不阻塞,即读不阻塞写,写不阻塞读,这样就可以提升事务并发处理能力。
  • 降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式,读取数据时并不需要加锁,对于写操作,也只锁定必要的行。
  • 解决一致性读的问题。一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果。

在可重复读的情况下,InnoDB 可以通过 Next-Key 锁 + MVCC 来解决幻读问题。

26.2,MVCC 的实现原理

MVCC 是通过数据行的多个版本管理来实现数据库的并发控制,简单来说就是保存数据的历史版本。这样我们就可以通过比较版本号决定数据是否显示出来,从而达到读取数据的时候不需要加锁也可以保证事务的隔离效果。

MVCC 的核心是 Undo Log + Read View

  • “MV” 是通过 Undo Log 来保存数据的历史版本,实现多版本的管理
  • “CC” 是通过 Read View 来实现管理,通过 Read View 原则来决定数据是否显示。同时针对不同的隔离级别,Read View 的生成策略不同,也就实现了不同的隔离级别。

MVCC 是一种机制,MySQL、Oracle、SQL Server 和 PostgreSQL 的实现方式均有不同。

26.3,什么是快照读

快照读读取的是快照数据。不加锁的 SELECT 或者说普通的 SELECT 都属于快照读,比如:

SELECT * FROM tableName WHERE ...

26.4,什么是当前读

当前读就是读取最新数据,而不是历史版本的数据。加锁的 SELECT,或者对数据进行增删改都会进行当前读,比如:

-- 加锁的 select
SELECT * FROM player LOCK IN SHARE MODE;

-- 相当于加锁的 select
SELECT * FROM player FOR UPDATE;

-- Insert 操作
INSERT INTO player values ...

-- Update 操作
UPDATE player SET ...

-- Delete 操作
DELETE FROM player WHERE ...

26.5,记录的多个版本包括哪些内容

在 InnoDB 中,MVCC 是通过 Undo Log + Read View 进行数据读取,Undo Log 保存了历史快照,而 Read View 规则帮我们判断当前版本的数据是否可见。

一个记录的多个版本包括以下:

  • 事务版本号:每开启一个事务,会从数据库中获得一个事务 ID(事务版本号),这个事务 ID 是自增长的,通过 ID 大小,可以判断事务的时间顺序。
  • 行记录中的隐藏列:InnoDB 的叶子段存储了数据页,数据页中保存了行记录,行记录中有一些重要的隐藏字段:
    • b_row_id:隐藏的行 ID,用来生成默认聚集索引。如果我们创建数据表的时候没有指定聚集索引,这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率。
    • db_trx_id:操作这个数据的事务 ID,也就是最后一个对该数据进行插入或更新的事务 ID。
    • db_roll_ptr:回滚指针,也就是指向这个记录的 Undo Log 信息。
  • Undo Log:InnoDB 将行记录快照保存在了 Undo Log 里,我们可以在回滚段中找到它们。

在这里插入图片描述

26.6,Read View 原理

在这里插入图片描述

27,MySQL 传输数据的原理

对于一个 Select 操作,MySQL 会将查到的每一行都放到结果集里面,然后返回给客户端。

MySQL 服务端并不保存一个完整的结果集,而是边读边发。这就意味着,如果客户端接收得慢,会导致 MySQL 服务端发数据变慢。

MySQL 取数据和发数据的流程是:

  • 获取一行数据,写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的,默认是 16k。
    • 因此,一个查询在发送过程中,占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大
  • 重复获取行,直到 net_buffer 写满,调用网络接口发出去。
  • 如果发送成功,就清空 net_buffer,然后继续取下一行,并写入 net_buffer
  • 如果发送函数返回 EAGAINWSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。
    • 如果 show processlist 命令的执行结果中有 Sending to client,则表示 socket send buffer 写满了:
    • 在这里插入图片描述
    • 还有一个类似的状态是 Sending data,表是正在查询数据,还没有到发送数据的阶段:
    • 在这里插入图片描述

MySQL 取数据和发数据的流程图:

在这里插入图片描述

MySQL 中提供了两个接口来获取数据:

  • mysql_use_result:一行一行的返回数据
  • mysql_store_result:一下子将整个结果集返回,如果结果集非常大(则会很消耗客户端的内存),则不建议使用该接口