MySQL 学习汇总

学习总结自《MySQL实战45讲》和 MySQL 官方文档。

InnoDB 架构图

事务

当数据库上有多个事务同时执行的时候，就可能出现脏读、不可重复读、幻读的问题，为了解决这些问题，就有了隔离级别的概念。

标准的隔离级别：

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

一致性视图（通过 MVCC 实现）的启动时机：

• 在执行第一个快照读语句时创建的；
• 在执行 start transaction with consistent snapshot 时创建的。

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图； 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

InnoDB 里面每个事务有一个唯一的事务 ID，每行数据有多个版本，每个版本会标记是由哪个事务 ID 产生的。

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况
   • 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   • 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

更新数据都是先读后写的，而这个读，只能读当前的值（已经提交完成的最新版本），称为“当前读”（current read）。select 语句如果加锁，也是当前读。

索引

InnoDB 使用了 B+ 树索引模型，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一遍主键索引，这个过程称为回表。

索引的更新会涉及到页分裂和页合并。主键通常采用自增字段，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

业务字段做自增主键的场景：只有一个索引，并且该索引必须是唯一索引。

覆盖索引：检索的字段可以从索引页获取到，不需要回表。

最左前缀原则：如何安排字段顺序：

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

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

索引的选择：

优化器会根据扫描的行数、是否使用临时表、是否排序等因素综合判断。

• 扫描行数的估算：一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

MySQL 选错索引时的应对策略：

• force index 强行选择一个索引
• 修改语句，引导 MySQL 使用我们期望的索引
• 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

前缀索引：可以节省空间，但会增加扫描成本，以及无法使用覆盖索引。

锁

根据加锁范围，可以分为全局锁、表级锁和行锁。

全局锁：加全局读锁，命令是 Flush tables with read lock (FTWRL)。

表级锁：

• 表锁：lock tables … read/write。除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
• 元数据锁（MDL）：不需要显式使用，在访问一个表的时候会被自动加上。
  • 如果一个 DDL 语句阻塞等待获得 MDL 写锁，那么之后的其他 DDL、DML 语句也会阻塞
• 行锁：由引擎层实现
  • 两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
    • 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁应对策略：

• 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置；
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
  • 每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。
    • 控制热点行更新的并发量
    • 热点行更新可以拆为逻辑上一致的多行更新，以减少锁冲突

查看最后一次死锁信息：show engine innodb status，LATEST DETECTED DEADLOCK 章节，例子：

如果一个语句因为死锁被终止，语句所在的事务并不会自动回滚。

1. 结果分成三部分：
   • (1) TRANSACTION，是第一个事务的信息；
   • (2) TRANSACTION，是第二个事务的信息；
   • WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
2. 第一个事务的信息中：
   • WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
   • index c of table test.t，说明在等的是表 t 的索引 c 上面的锁；
   • lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
   • Record lock 说明这是一个记录锁；
   • n_fields 2 表示这个记录是两列，也就是字段 c 和主键字段 id；
   • 0: len 4; hex 0000000a; asc ;; 是第一个字段，也就是 c。值是十六进制 a，也就是 10；
   • 1: len 4; hex 0000000a; asc ;; 是第二个字段，也就是主键 id，值也是 10；
   • 这两行里面的 asc 表示的是，接下来要打印出值里面的“可打印字符”，但 10 不是可打印字符，因此就显示空格。
   • 第一个事务信息就只显示出了等锁的状态，在等待 (c=10,id=10) 这一行的锁。
   • 当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。
3. 第二个事务显示的信息要多一些：
   • “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
   • index c of table test.t 表示锁是在表 t 的索引 c 上；
   • hex 0000000a 和 hex 00000014 表示这个事务持有 c=10 和 c=20 这两个记录锁；
   • WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (c=5,id=5) 这个记录锁。

从上面这些信息中，我们就知道：

1. “lock in share mode”的这条语句，持有 c=5 的记录锁，在等 c=10 的锁；
2. “for update”这个语句，持有 c=20 和 c=10 的记录锁，在等 c=5 的记录锁。因此导致了死锁。

这里，我们可以得到两个结论：

1. 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
2. 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。

查看锁等待：show engine innodb status，TRANSACTIONS 章节

1. index PRIMARY of table test.t ，表示这个语句被锁住是因为表 t 主键上的某个锁。
2. lock_mode X locks gap before rec insert intention waiting 这里有几个信息：
   • insert intention 表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是这个插入动作本身。
   • gap before rec 表示这是一个间隙锁，而不是记录锁。
3. 那么这个 gap 是在哪个记录之前的呢？接下来的 0~4 这 5 行的内容就是这个记录的信息。
4. n_fields 5 也表示了，这一个记录有 5 列：
   • 0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段，十六进制 f 就是 id=15。所以，这时我们就知道了，这个间隙就是 id=15 之前的，因为 id=10 已经不存在了，它表示的就是 (5,15)。
   • 1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id，表示最后修改这一行的是 trx id 为 1299 的事务。
   • 2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到，这里的 acs 后面有显示内容 (% 和 4)，这是因为刚好这个字节是可打印字符。
   • 后面两列是 c 和 d 的值，都是 15。

因此，我们就知道了，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。

查询

count(*)

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

为了避免 count(*) 的扫描，可以使用单独的计数表。

效率：count(字段)<count(主键 id)<count(1)≈count(*)。

order by

Explain 命令执行结果中 Extra 字段有 Using filesort 表示需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

• 全字段排序：行数据长度小于 max_length_for_sort_data，所需要的字段都查出来放到 sort_buffer 中（如果 sort_buffer 不够放，会借助磁盘临时文件），然后根据排序字段进行排序。
• rowid 排序：行数据长度达到 max_length_for_sort_data，只把排序字段和主键放到 sort_buffer 中，然后排序，最后回表。

sort_buffer 足够，会采用快速排序或优先队列排序，否则采用归并排序。

如果要排序的字段都在某个索引上，则直接扫描索引就可以了，也就没有 Using filesort。

Explain 命令中如果出现 Using index 表示使用了覆盖索引。

可能无法使用上索引的情况

• 条件字段函数操作：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。优化器并不是要放弃使用这个索引，可能会进行全索引扫描。
• 隐式类型转换：在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。
• 隐式字符编码转换：字段比较时，utf8 会转成 utf8mb4

随机获取记录

1、select word from words order by rand() limit 3;

如果临时表大小 <= tmp_table_size，则使用内存临时表，内存临时表排序的时候使用 rowid 排序方法。

如果临时表大小 > tmp_table_size，则使用磁盘临时表（默认为 InnoDB 引擎），

2、非严格随机方法

1. 取得这个表的主键 id 的最大值 M 和最小值 N;
2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
3. 取不小于 X 的第一个 ID 的行。

3、严格随机方法

1. 取得整个表的行数，并记为 C；
2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分；
3. 再用 limit Y,1 取得一行。

只查一行的语句，执行也很慢？

1、查询长时间不返回

通过 show processlist 命令，看看当前语句处于什么状态。

• Waiting for table metadata lock：现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。
• Waiting for table flush：现在有一个线程正要对表 t 做 flush 操作。有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
• 等待行锁：查询某个表上的锁等待情况，select * from t sys.innodb_lock_waits where locked_table=’`test`.`t`‘\G

2、查询慢

• 查询走主键顺序扫描
• 快照读时，需要执行太多的 undo log 记录

kill

当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：

1. 把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
2. 给 session B 的执行线程发一个信号。

当用户执行 kill thread_id_B 时：

1. 把 thread_id_B 号线程状态设置为 KILL_CONNECTION；
2. 关掉 thread_id_B 号线程的网络连接。

show processlist 上看到 Command=Killed 的情况：

1. 线程没有执行到判断线程状态的逻辑
2. 终止逻辑耗时较长
   1. 超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
   2. 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
   3. DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

join

在能用上被驱动表索引的情况下，让小表作为驱动表，效率更高，使用 Index Nested-Loop Join 算法。

如果无法使用被驱动表上的索引，依然是小表作为驱动表，使用 Block Nested-Loop Join 算法（尽量不要出现这种查询）：

1. 把驱动表的数据读入到 join_buffer 中
   • 假设驱动表的数据行数是 N，需要分 K 段才能完成算法流程，K 可以表示为 λ * N，λ的取值范围是 (0,1)
2. 从被驱动表每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回
   • 假设被驱动表的数据行数是 M
   • 内存判断需要 N * M 次
   • 扫描行数 N + K * M = N + λ * N * M，N 越小，效率越高
3. 清空 join_buffer
4. 继续扫描驱动表，将数据读入到 join_buffer，继续执行第 2 步

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

内部临时表

explain 的 Extra 中包含 Using temporary，表示使用了内部临时表（如果数据量小于 tmp_table_size，使用内存临时表，否则使用磁盘临时表）。

• union 去重的时候
• group by：默认会排序，显式加上 order by null，可以去掉排序
  • 优化：
    • 在 group by 字段上建立索引
    • SQL_BIG_RESULT 用于大数据集计算的情况下，避免先使用内存临时表，发现不够用时，再使用磁盘临时表

自增主键不是连续的

自增值保存在哪儿？MySQL <= 5.7 保存在内存中，第一次打开表的时候会去找自增值的最大值 max(id) + 1 作为这个表当前的自增值。MySQL 8.0 将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

自增值修改机制：

• 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
• 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。
  • 假设，某次要插入的值是 X，当前的自增值是 Y。
    • 如果 X<Y，那么这个表的自增值不变；
    • 如果 X≥Y，就需要把当前自增值修改为新的自增值。

自增值不连续的原因：

• 唯一键冲突导致插入失败
• 事务回滚
• 批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略，可能会出现多分配了一些 id 没有被用到

自增锁：每次申请完就马上释放，以便允许别的事务再申请。

grant 之后要跟着 flush privileges 吗？

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。

flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

NULL 跟任何值执行等值判断和不等值判断的结果，都是 NULL

查询 select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2); 等价于 select * from a join b on(a.f1=b.f1) and (a.f2=b.f2);

show processlist

• State: Sending to client：表示服务端的 net_buffer 写满了，等待客户端接收
• State: Sending data：表示正在执行

幻读

幻读：多次”当前读”情况下，看到”新插入的行”。

为了解决”当前读”情况下出现的幻读，InnoDB 引入了间隙锁（可重复读隔离级别）。

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

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

假设有记录值 a, b, c，间隙锁 (a, b)、(b, c)，如果 b 被删除，那么原本持有 (b, c) 的间隙锁会”变大”为 (a, c)。

加锁规则（5.x 系列 <=5.7.24，8.0 系列 <=8.0.13）

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
  • 当要加 next-key lock 的时候，是先加间隙锁，后加行锁。
• 原则 2：查找过程中访问到的对象才会加锁。
  • 如果查询使用覆盖索引，并不需要访问主键索引，那么主键索引不会加锁
    • 使用 lock in share mode 的覆盖索引查询，只锁覆盖索引，但如果是 for update 的覆盖索引查询，还是会给主键索引上满足条件的行加上行锁
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，（如果存在等值记录）next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

以表 t 为例：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

唯一索引上的等值查询：

• 记录存在：select * from t where id = 10 for update; 加行锁 10
  • 原则 1：加锁 (5, 10]
  • 优化 1：(5, 10] 退化为行锁
• 记录不存在：select * from t where id = 11 for update; 加间隙锁 (10, 15)
  • 原则 1：加锁 (10, 15]
  • 优化 2：(10, 15] 退化为间隙锁

非唯一索引上的等值查询：

• 记录存在：select * from t where c = 10 for update; 加锁范围 (5, 15)
  • 原则 1：加锁 (5, 10]、(10, 15]
  • 优化 2：(10, 15] 退化为间隙锁 (10, 15)
• 记录不存在：select * from t where c = 12 for update; 加锁范围 (10, 15)
  • 原则 1：加锁 (10, 15]
  • 优化 2：(10, 15] 退化为间隙锁 (10, 15)

limit 语句对加锁的影响：

• 记录存在：select * from t where c = 10 limit 1 for update; 加锁范围 (5, 10]
  • 原则 1：加锁 (5, 10]
  • 由于 limit 1，不再往后扫描

唯一索引上的范围查询：

• select * from t where id > 10 for update; 加锁范围 (10, +supremum]
  • 根据 10 等值查询到第一个 15，加锁 (10, 15]，后续扫描到的记录都加 next-key lock
• select * from t where id >= 10 for update; 加锁范围 [10, +supremum]
  • 等值查询到 10，加锁 (5, 10]
  • 优化 1：(5, 10] 退化为行锁，后续扫描到的记录都加 next-key lock
• select * from t where id < 10 for update; 加锁范围 (-∞, 10]
  • 一个 bug：范围查询到 10，会加锁 (5, 10]
• select * from t where id <= 10 for update; 加锁范围 (-∞, 15]
  • 一个 bug：范围查询到 15，会加锁 (10, 15]
• select * from t where 5 < id and id < 15 for update; 加锁范围 (5, 15]
  • 等值查询到第一个比 5 大的值 10，会加锁 (5, 10]
  • 一个 bug：范围查询到 15，会加锁 (10, 15]
• select * from t where 5 <= id and id < 15 for update; 加锁范围 [5, 15]
  • 等值查询到 5，加锁 (0, 5]
  • 优化 1：(0, 5] 退化为行锁
  • 一个 bug：范围查询到 15，会加锁 (10, 15]
• select * from t where 5 <= id and id <= 15 for update; 加锁范围 [5, 20]
  • 等值查询到 5，加锁 (0, 5]
  • 优化 1：(0, 5] 退化为行锁
  • 一个 bug：范围查询到 20，会加锁 (15, 20]

非唯一索引上的范围查询：

force index(c) 是为了避免走全表扫描，走全表扫描，会锁住整个主键索引

• select * from t force index(c) where c > 10 for update; 加锁范围 (10, +supremum]
  • 等值查询到第一个比 10 大的值 15，会加锁 (10, 15]，后续扫描到的记录都加 next-key lock
• select * from t force index(c) where c >= 10 for update; 加锁范围 (5, +supremum]
  • 等值查询到 10，会加锁 (5, 10]，后续扫描到的记录都加 next-key lock
• select * from t force index(c) where c < 10 for update; 加锁范围 (-∞, 10]
  • 范围查询到 10，会加锁 (5, 10]
• select * from t force index(c) where c <= 10 for update; 加锁范围 (-∞, 15]
  • 范围查询到 15，会加锁 (10, 15]
• select * from t force index(c) where 5 < c and c < 15 for update; 加锁范围 (5, 15]
  • 等值查询到第一个比 5 大的值 10，会加锁 (5, 10]
  • 范围查询到 15，会加锁 (10, 15]
• select * from t force index(c) where 5 <= c and c < 15 for update; 加锁范围 (0, 15]
  • 等值查询到 5，会加锁 (0, 5]
  • 范围查询到 15，会加锁 (10, 15]
• select * from t force index(c) where 5 <= c and c <= 15 for update; 加锁范围 (0, 20]
  • 等值查询到 5，会加锁 (0, 5]
  • 范围查询到 20，会加锁 (15, 20]

order by 对加锁的影响：select * from t where c >= 15 and c <= 20 order by c desc lock in share mode; 加锁范围：索引 c 上 (5, 25)，因为是 select *，所以会在主键索引上加 id=15、20 两个行锁

• 由于是 order by c desc，第一个要定位的是索引 c 上“最右边的”c=20 的行，所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。
  • 优化 2：等值查询到 25，(20, 25] 退化为间隙锁
• 在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]。

insert t2 select … t 语句，会把表 t 的扫描到的行和间隙加锁，避免主从同步时的不一致。

insert t select … t 语句，也会把表 t 的所有行和间隙加锁，并且需要内部临时表（原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符），可以引入用户临时表优化。

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

自增 id 用完怎么办

每种自增 id 有各自的应用场景，在达到上限后的表现也不同：

1. 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
2. 如果你创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
3. Xid 是由 Server 层维护的，会被写入到 binlog 文件中，MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
4. trx_id 是 InnoDB 通过 max_trx_id 为写事务分配的递增 ID，读事务不会分配。max_trx_id 递增值每次 MySQL 重启都会被保存起来，理论上 MySQL 跑得足够久的话就会出现脏读的 BUG。
5. thread_id 自增，达到 2^32-1 后，它就会重置为 0，分配时会先判断是否存在

主备复制

binlog 三种格式：

• statement：只记录执行的 SQL
  • 可能出现数据不一致，比如 delete … limit …，如果主备选择的索引不同，则可能删除的记录不一样
• row：只记录行的变化
  • binlog 日志量较大
  • 好处时对于误操作，方便恢复数据
• mixed：row + statement

主备延迟

seconds_behind_master 的计算方法是这样的：

1. 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。

来源：

• 备库机器性能差
• 备库压力大
• 大事务，比如一次性 delete 语句删除太多数据；大表 DDL
• 受限于备库的并行复制能力：sql_thread 从单线程发展到多线程

GTID

全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：GTID=server_uuid:gno

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
• gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

GTID 的生成方式，取决于 session 变量 gtid_next 的值：

1. 如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。
   1. 记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;
   2. 把这个 GTID 加入本实例的 GTID 集合。
2. 如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next=’current_gtid’指定为 current_gtid，那么就有两种可能：
   1. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；
   2. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加 1。

一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行 set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic。

读写分离

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。

解决方案：

• 强制走主库方案；
• sleep 方案；
• 判断主备无延迟方案；
• 配合 semi-sync 方案；
• 等主库位点方案；
• 等 GTID 方案。