MySQL 45 讲笔记

查看 MySQL 版本

select version();

version()
5.7.31

MySQL 连接命令

mysql -h$ip -P$port -u$user -p

查看连接

show processlist;
# 或者
select * from information_schema.processlist

Id	User	Host	db	Command	Time	State	Info
1971	root	113.67.73.92:51800	test	Query	0	starting	/* ApplicationName=DataGrip 2020.1.3 */ show processlist

查看查询缓存开关

show variables like 'query_cache_type'

Variable_name	Value
query_cache_type	OFF

查看数据库隔离级别

show variables like 'transaction_isolation'

Variable_name	Value
transaction_isolation	REPEATABLE-READ

长事务查询

# 查询持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

每一个索引在 InnoDB 对应一颗 B+ 树

主键索引的叶子节点存放的是整行数据，主键索引也称为聚簇索引

非主键索引的叶子节点存放的是主键的值，非主键索引也称为二级索引

自增主键好处：

• int 存储空间为 4 个字节， bigint 存储空间为 8 个字节，主键长度越小，普通索引的叶子节点就越小，普通索引占用的存储空间就越小

• 自增主键插入新数据是追加写的，都不涉及挪动其他记录，不会触发叶子节点的分裂

• 非自增主键不能保证有序插入，需要先定位到插入位置，同时可能会触发叶子节点的分裂

覆盖索引：在索引上已经覆盖了查询需求，不需要回表到主键索引上搜索。使用覆盖索引是一个常见的性能优化手段

最左前缀原则：可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

建立联合索引，索引内字段顺序原则

• 如果通过调整顺序可以少维护一个索引，那么这个顺序往往就是需要优先考虑的

• 考虑存储空间，（a,b）+ (b) 还是（b,a） + a，字段长的建立联合索引，字段短的建立单字段索引

索引下推（since MySQL 5.6）可以在索引遍历的过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

加全局锁命令

# FTWRL
flush tables with read lock;

之后其他线程的以下语句会被阻塞：数据库更新语句、数据定义语句、更新类事务的提交语句

全局锁应用场景：做全库逻辑备份

释放全局锁

unlock tables;

使用 FTWRL 做备份

• 在主库上加全局锁做备份，备份期间不能执行更新操作，业务停摆

• 在备库上加全局锁做备份，备份期间备库不能执行主库同步过来的 binlog，导致主从延迟

FTWRL vs set global readonly = true。 ==> FTWRL √

• readonly 的值可能被用来做其他逻辑判断，比如判断一个库是主库还是备库

• 二者在异常处理机制上有差异

  • 执行 FTWRL 命令后由于客户端异常断开， MySQL 会自动释放这个全局锁，整个库会回到正常可以更新的状态

  • set globle readonly = true 客户端异常断开，数据库会一直保持 readonly 状态

Innodb 通过 一致性视图 进行逻辑备份

mysqldump --single-transaction database [tables]

mysqldump 命令

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

表级锁：表锁、元数据锁 MDL（meta data lock）

表锁语法

# 加锁
lock tables ... read/write
# 解锁
unlock tables;

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

元数据锁（MDL）是 server 层的锁，主要用户隔离 DML 和 DDL 操作间的干扰

每执行一条 DML 和 DDL 语句都会申请 MDL 锁，DML 操作需要 MDL 读锁，DDL 需要 MDL 写锁

MDL不需要显示使用，会被自动加上，在语句执行完并不会自动释放，而是等到整个事务提交后再释放

MDL 读写锁竞争关系

• 读读共享

• 读写互斥

• 写写互斥

Innodb 查看行锁等待超时时间

show variables like 'innodb_lock_wait_timeout'

Variable_name	Value
innodb_lock_wait_timeout	50

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

Innodb 查看死锁检测开关

show variables like 'innodb_deadlock_detect'

Variable_name	Value
innodb_deadlock_detect	ON

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。一致性视图是在执行第一个快照读语句时创建的

马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。一致性视图是在执行 start transaction with consistent snapshot 时创建的。

start transaction with consistent snapshot

可重复读的核心就是一致性读（consistent read）；而更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见；

• 版本已提交，但是是在视图创建后提交的，不可见；

• 版本已提交，而且是在视图创建前提交的，可见。

普通索引 VS 唯一索引 ==> 普通索引 √

查询操作：几乎无差别

更新操作：普通索引可以用到 change buffer，可以节省随机读磁盘的 IO 消耗。

change buffer 只能作用于普通索引

普通索引更新操作

• 数据页在内存中，直接更新内存

• 数据也不在内存中，不需要将数据页读到内存在，将 "在这个内存页的写操作" 写入到 change buffer 中

channge buffer merge 时机

• 访问这个数据页会触发 merge

• 系统有后台线程会定期 merge

• 在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作

change buffer 使用场景：写多读少，并且写完之后被马上访问到（触发merge）的概率比较小

优化器选择索引的判断标准

• 扫描行数

• 是否需要临时表

• 是否需要排序

索引的区分度：一个索引上不同的值越多，这个索引的区分度就越好，

一个索引上不同值的个数称之为基数（cardinality）

可以使用 show index 查看索引的基数值

show index from t

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t	0	PRIMARY	1	id	A	92062	NULL	NULL		BTREE
t	1	a	1	a	A	92877	NULL	NULL	YES	BTREE
t	1	b	1	b	A	92877	NULL	NULL	YES	BTREE

MySQL 如何统计索引基准值 - 采样统计

• InnoDB 默认会选择 N 个数据页，统计这些页面的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数

• 而数据表是会持续更新的，索引统计信息也不会固定不变，索引，当变更的行数超过 1/M 的时候，会自动触发重新做一次索引统计

• 存储索引统计信息的方式，可以通过设置参数值 innodb-stats_persistent 来选择

  • on：表示统计信息会持久化保存，这时，默认的 N 是 20，M 是 10

  • off：表示统计信息只存储在内存中，这时，默认的 N 是 8，M 是 16

  show variables like 'innodb_stats_persistent';

  Variable_name	Value
  innodb_stats_persistent	ON

重新统计索引信息

analyze table t;

字符串字段创建索引方式

• 直接创建完整索引，这样可能比较占用空间

• 创建前缀索引

  • 优点：节省空间

  • 缺点：会增加查询扫描次数、不能使用覆盖索引

• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀区分度不够的问题，但不支持范围扫描

• 创建 hash 字段索引，查询性能稳定，又额外的存储和计算消耗，但不支持范围扫描

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。

利用 WAL 技术，数据库将随机写转换成了顺序写（？？？），大大提升了数据库的性能。

---

表数据可以存在共享空间里，也可以是单独的文件。这个行为由参数 innodb_file_per_table 控制的

• OFF: 表示数据放在系统共享表空间，也就是跟数据字典放在一起

• ON: 表示每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

show variables like 'innodb_file_per_table';

Variable_name	Value
innodb_file_per_table	ON

从 MySQL 5.6.6 版本开始，默认值就是 ON，推荐不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删除了，空间也是不会回收的。

InnoDB 引擎删除索引的记录

• 删除一个索引树数据页中的一个记录，InnoDB 引擎会把这个记录标记为删除，如果之后插入的记录可以插入这个位置，可以服用这个位置。

• 如果一个数据也的所有记录都被删除了，这个数据页则可以被复用

因此，delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

因此，不止是删除数据会造成空洞，插入数据也会。

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

alter table A engine=InnoDB
# 相当于
alter table t engine=innodb,ALGORITHM=inplace;

MySQL 5.6 引入 Online DDL 后重建表流程

1. 建立一个临时文件，扫描表 A 主键的所有数据页；

2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；

3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；

4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；

5. 用临时文件替换表 A 的数据文件。

optimize table t = alter table t engine = InnoDB + analyze table t

truncate = drop + create

---

不带 where 条件 count(*) 实现方式

• MyISAM: MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高

• InnoDB: InnoDB 引擎在执行 count(*) 的时候，需要把数据一行一行的引擎里面读出来，然后累计计数（因为事务 MVVC 的原因，不同版本的记录数是不相同的，无法将这个数存起来）

• MyISAM 表虽然 count() 很快，但是不支持事务；

• show table status 命令虽然返回很快，但是不准确；

• InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

count(字段) vs count(id) vs count(1) vs count(*)

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；

count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

分析性能差别的时候，可以记住这么几个原则：

• server 层要什么就给什么；

• InnoDB 只给必要的值；

• 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)。推荐尽量使用 count(*)。

---

排序 https://time.geekbang.org/column/article/73479 重要

• 全字段排序:

• rowid 排序：max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法（排序字段 + 主键 id）。

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

并不是所有的 order by 语句，都需要排序操作的。MySQL 之所以需要生成临时文件，并且在临时文件上做排序操作，其原因是原来的数据都是无序的。-- 排序字段参与的联合主键、覆盖索引

explain Extra 字段

• Using filesort： 本次查询语句中有order by，且排序依照的字段不在本次使用的索引中，不能自然有序。需要进行额外的排序工作。MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer

• Using index: 使用了覆盖索引——即本次查询所需的所有信息字段都可以从利用的索引上取得。无需回表，额外去主索引上去数据。

• Using index condition: 使用了索引下推技术ICP。（虽然本次查询所需的数据，不能从利用的索引上完全取得，还是需要回表去主索引获取。但在回表前，充分利用索引中的字段，根据where条件进行过滤。提前排除了不符合查询条件的列。这样就减少了回表的次数，提高了效率。）

• Using where: 表示本次查询要进行筛选过滤。

• Using temporary：表现需要使用临时表

OPTIMIZER_TRACE：未学习

如果创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。、

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表（Memory）就会转成磁盘临时表。磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

递增主键的记录分页查询的时候，后一次插叙可以基于前一次查询的主键（select * from t where id > id' order by x limit start, offset） 减少需要排序的记录数(ES 深度分页)

归并排序思想

---

B+ 树提供快速定位的能力，来源于同一层兄弟节点的有序性

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。如索引字段加上日期函数操作，MySQL 无法再使用索引快速定位功能，而只能使用全索引扫描。

在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字

隐式类型转换，隐式字符编码转换，可能会触发条件索引字段隐式增加转换函数，即对索引字段做了函数操作。

---

select k from t where id=1 lock in share mode; select 语句如果加锁，是当前读；加了读锁（S 锁，共享锁）

select k from t where id=1 for update; select 语句如果加锁，是当前读；加了写锁（X 锁，排他锁）。

通过 sys.innodb_lock_waits 可以排查是那个线程的锁阻塞了 sql

RC隔离级别下，对非索引字段更新，有个锁全表记录的过程，不符合条件的会及时释放行锁，不必等事务结束时释放；而直接用索引列更新，只会锁索引查找值和行。update产生的X锁在不释放的情况下，DELETE语句无法执行（？），但是UPDATE语句能更新不符合之前X锁的记录。

RR隔离级别下，为保证binlog记录顺序，非索引更新会锁住全表记录，且事务结束前不会对不符合条件记录有逐步释放的过程。DELETE和UPDATE语句都不能执行

MySQL 45 讲（19-21）

幻读产生的原因：即使给所有行加上了锁，也避免不了幻读，这是因为给行加锁的时候，这条记录还不存在，没法加锁。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

行锁 vs 间隙锁

• 行锁：分成读锁和写锁，读读不冲突，读写冲突，写写冲突。与行锁有冲突关系的是另外一个行锁

• 间隙锁：间隙锁之间不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

间隙锁 VS next-key Lock

• 间隙锁：间隙锁为开区间，锁住两个索引（主键索引、普通索引）之间的区间

• next-key Lock：前开后闭区间，即间隙锁 + 行锁 锁住的区间

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

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。

原则 2：查找过程中访问到的对象才会加锁。

• 对于未命中索引的查询要走全表扫描，这种在扫描前就会给全表加上next-key lock

优化 1：索引上的等值查询，给唯一索引加锁的时候，存在记录匹配上的时候 next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

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

lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。

在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读提交隔离级别的原因。

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。

3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

5. sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog 的三种格式对比

• statement（最不推荐）：binlog 里面记录的就是 SQL 语句的原文

• row(推荐)：binlog 里面记录了真实数据行的增删改，row 格式可以将 binlog 用来恢复数据

• mixed：mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

查看当前 binlog 正在写入的文件

show master status;

查看 binlog 事件

show binlog events in 'master.000001';

用 mysqlbinlog 工具解析和查看 binlog 中的内容。

mysqlbinlog  -vv data/master.000001 --start-position=8900; 
# -vv 参数是为了把内容都解析出来，在结果里面可以看到各个字段的值
# --start-position 参数用来指定从哪个位置的日志开始解析
# --start-datetime --stop-datetime 用来指定 event 的时间范围

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：

mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

解决双 M 结构两个节点间的循环复制的问题：

1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；

2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；

3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

设置了双 M 结构，日志的执行流就会变成这样：

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；

2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；

3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

---

将mysql语句返回的数据保存在文件中

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

MySQL 服务端结果集取数据和发数据的流程

1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。

2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。

3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。

4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

也就是说，MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。

如果show processlist State 的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。

对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，建议使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存。

show processslist #state = Sending data：一个查询语句的状态变化是这样的（注意：这里，我略去了其他无关的状态）：

1. MySQL 查询语句进入执行阶段后，首先把状态设置成“Sending data”；

2. 然后，发送执行结果的列相关的信息（meta data) 给客户端；

3. 再继续执行语句的流程；

4. 执行完成后，把state状态设置成空字符串。

也就是说，“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。

也就是说，仅当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"；而如果显示成“Sending data”，它的意思只是“正在执行”

Buffer Pool（重要，未学习）

• 配合 redo log，避免了随机写盘

• Buffer Pool 还有一个更重要的作用，就是加速查询

Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：内存命中率。

show engine innodb status ： 看到的Buffer pool hit rate 字样，显示的就是当前的命中率。

一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要在 99% 以上

InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。

show VARIABLES like '%innodb_buffer_pool_size%'

Variable_name	Value
innodb_buffer_pool_size	134217728 (Byte)

InnoDB 内存(Buffer Pool)管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。

在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。

LRU 算法执行流程：

1. 要访问 young 区域数据页 P3，由于 P3 在 young 区域，将其移到链表头部。

1. 之后要访问一个新的不存在于当前链表的数据页，这时候淘汰掉 old 区域数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。

1. 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：

   • 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部( young 区域)；

   • 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

这个策略，就是为了处理类似全表扫描的操作量身定制的。以的扫描 200G 的历史数据表为例，改进后的 LRU 算法的操作逻辑：

1. 扫描过程中，需要新插入的数据页，都被放到 old 区域 ;

1. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域；

1. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。

这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。

---

Index Nested-Loop Join(NLJ)：被驱动表上有索引

select * from t1 straight_join t2 on (t1.a=t2.a); # 被驱动表 t2.a 上有索引

执行流程：

• 从表 t1 中读入一行数据 R；从数据行 R 中，取出 a 字段到表 t2 里去查找；

• 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；

• 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

扫描行数复杂度为：N + N2log2M

Block Nested-Loop Join(BNL)：被驱动表上没有索引

select * from t1 straight_join t2 on (t1.a=t2.b); # 被驱动表 t2.b 上没有索引

执行流程：

• 把表 t1 的数据读入线程内存 join_buffer 中，由于这个语句中写的是 select *，因此是把整个表 t1 放入了内存；

• 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

复杂度：

• 两个表都做一次全表扫描，所以总的扫描行数是 M+N；

• 内存中的判断次数是 M*N。

BNL 算法对系统的影响主要包括三个方面：

• 可能会多次扫描被驱动表，占用磁盘 IO 资源；

• 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；

• 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。

• 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 x 行 join_buffer 满了，继续第 2 步；

• 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；

• 清空 join_buffer；

• 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

能不能使用 join 语句？

• 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

• 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；

• 如果是 Block Nested-Loop Join 算法

  • 在 join_buffer_size 足够大的时候，是一样的；

  • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

结论就是，总是应该使用小表做驱动表。

select * from t1 join t2 on t1.a = t2.a 会转换成 select * from t1 join t2 where t1.a = t2.a执行

什么叫作“小表”？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，

计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Multi-Range Read 优化（MRR）

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

MRR 优化的设计思路。语句的执行流程为：

• 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;

• 将 read_rnd_buffer 中的 id 进行递增排序；

• 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

Batched Key Access（BKA）BKA 算法，其实就是对 NLJ 算法的优化。

如果要使用 BKA 优化算法的话，需要在执行 SQL 语句之前，先设置 set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

• NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。

• 那怎么才能一次性地多传些值给表 t2 呢？方法就是，从表 t1 里一次性地多拿些行出来，一起传给表 t2。既然如此，我们就把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存就是 join_buffer。

• join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。但是在 NLJ 算法里并没有用。那么，刚好就可以复用 join_buffer 到 BKA 算法中。

临时表在使用上有以下几个特点：

• 建表语法是 create temporary table …。

• 一个临时表只能被创建它的 session 访问，对其他线程不可见。由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。

  • 注：如果在程序中，用的是连接池中的连接来操作的，而这些连接不会释放，和数据库保持长连接，此时临时表不会自动删除。

  • “临时表会自动回收”这个功能，主要用于“应用程序异常断开、MySQL异常重启”后，不需要主动去删除表。平时正常使用的时候，用完删除，还是应该有的好习惯。

• 临时表可以与普通表同名。一个 session 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。

• show tables 命令不显示临时表。

binlog 记录临时表日志

• binlog_format=row ： 跟临时表有关的语句，不会记录到 binlog 里

• binlog_format=statment/mixed：binlog 中会记录临时表的操作。

MySQL 维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个 table_def_key。

• 一个普通表的 table_def_key 的值是由“库名 + 表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了。

• 而对于临时表，table_def_key 在“库名 + 表名”基础上，又加入了“server_id+thread_id”。

union VS union all

• union: 具有去重的语义。

  • 实现方式：创建一个具有唯一索引的内存临时表，以此执行子查询语句，将返回结果插入内存临时表，违反唯一约束的行会插入失败，实现去重。explain 可以在 extra 列看到 using temporary 标识。

• union all: 合并结果集，不去重

  • 实现方式：依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。

group by 执行流程（group by 默认对结果集按自然排序，不需要排序可以在语句末尾增加 order by null）

语义：统计不同的值出现的个数

• group by 字段不带索引

  1. 创建内存临时表，表里有字段不同值 m，出现次数 c，主键是 m

  2. 扫描表，依次取出叶子节点上的字段值，记为 x；

     • 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);

     • 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1

  3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

• group by 字段带索引

  • 由于 InnoDB 索引是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加就可以得到出现次数的值，不要使用内存临时表。

group by 使用的指导原则：

• 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

• 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；

• 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；

• 特定场景利用 generated column 实现列数据的关联更新机制作为 group by 索引字段

• 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

Mysql 自增 id 是递增的，但不保证是连续的（锁性能考虑）。不连续的原因

• 唯一键冲突

• 事务回滚

• 同一语句批量插入（同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍，最后一次申请到的多余自增id会被浪费掉）

insert … select 是很常见的在两个表之间拷贝数据的方法。在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。

而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，需要引入用户临时表来做优化。

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

怎么在两张表中拷贝数据

• mysqldump

  e.g

  mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
  ​
  ​
  mysql  -h$host -P$port -u$user -e "source /client_tmp/t.sql"

  –single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

  –add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；

  –no-create-info 的意思是，不需要导出表结构；

  –set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；

  –result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

  如果希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数–skip-extended-insert。

• 导出 CSV 文件

• 物理拷贝方法

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

show warnings 查看优化器执行读语句的优化