吉法师的博客

不知道能否追到喜欢的人呀，今年努力下吧~ 2022.1.4

MySQL实战45讲

基础架构

先看MySQL的架构图

架构图

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。

其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

MySQL的用户建立连接后，如果修改了权限，也得下次重新连接才会更新了。

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。（基于tcp的）

长连接会占用内存，直到连接断掉才会释放，所以累积下来的话内存占用会很大。

1.SQL查询语句执行过程

连接创建过程见上。

MySQL的查询缓存会非常消耗性能，而且表更新之后就得刷新，所以8.0版本之后将整块功能都删除了。

分析器和优化器

SQL解析器，词法分析。

优化器会从SQL的解析结果，判断有多个索引先用哪个索引，逻辑相同的SQL条件会选择最快的执行方式。

执行器

会判断对某个表是否有查询权限，有权限就打开表执行，根据引擎定义去使用接口。

有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的

如果表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

2.更新SQL语句的执行过程

与查询不同的是，更新还涉及两个模块，redo log（重做日志），binlog（归档日志）。

redo log

MySQL有用到WAL技术，Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。

redolog示意图

数据写满之后，MySQL就会整体将切入点推进以下，擦掉已经写库的记录。

binlog 日志模块

这两种日志有以下三点不同。

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

两阶段提交

定义：redo log 等待 binlog 写入完成后，由 prepare 变为 commit 提交状态

两阶段提交

先看数据的恢复过程：

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

redo log和binlog是两个独立的逻辑，如果不用两阶段提交，会出现数据库的状态和日志恢复出来的库状态不一致。

两个阶段的执行

1.请求阶段（commit-request phase，或称表决阶段，voting phase）
在请求阶段，协调者将通知事务参与者准备提交或取消事务，然后进入表决过程。
在表决过程中，参与者将告知协调者自己的决策：同意（事务参与者本地作业执行成功）或取消（本地作业执行故障）。

2.提交阶段（commit phase）
在该阶段，协调者将基于第一个阶段的投票结果进行决策：提交或取消。
当且仅当所有的参与者同意提交事务协调者才通知所有的参与者提交事务，否则协调者将通知所有的参与者取消事务。
参与者在接收到协调者发来的消息后将执行响应的操作。

崩溃恢复时的判断规则

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

a. 如果是，则提交事务；b. 否则，回滚事务。

3.事务和隔离级别

四种隔离级别：读未提交、读已提交、重复读、串行。

事务

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

实现上，数据库会创建一个视图，访问的时候以视图的逻辑为准。

事务执行会生成多个视图，在隔离级别高的时候，要读到之前的事务数据，就必须通过回滚来得到。

当没有事务需要回滚日志的时候，回滚日志会被删除，当系统没有比这个回滚日志更早的视图，就说明可以删除了。

尽量不使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

曾有数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。

set autocommit=0 的命令会导致事务不自动提交，最后就有可能执行了一个长事务。

查询执行时间超过60s的事务


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

4.索引

对于数据库的表而言，索引就相当于目录。

常见的索引数据结构：哈希表，有序数组，搜索树。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。

哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。

哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

哈希表

哈希表只适合等值查询。

有序数组

只适合静态存储引擎，比如存储某年某个城市的所有人口信息，这类不会被修改的数据。

身份证号递增排序，二分查找可以很快的查到某个人的信息。

但有序数组完全不适合插入和删除操作，所以最好只用于静态存储引擎。

二叉搜索树

保持树枝节点的左子树都比节点小，右子树都比节点大，某种程度的二分查找。但维护平衡的时候会比较耗时。

为什么数据库存储使用b+树 而不是二叉树，因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。

数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

回表


mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

select * from T where k between 3 and 5

一起来看看这条 SQL 查询语句的执行流程：

  1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；

  2. 再到 ID 索引树查到 ID=300 对应的 R3；

  3. 在 k 索引树取下一个值 k=5，取得 ID=500；

  4. 再回到 ID 索引树查到 ID=500 对应的 R4；

  5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。回表也就是值不在索引树上面，需要从表里进行查询。

最左前缀原则

索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。

这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

索引下推

索引下推（Index Condition Pushdown），简称 ICP。 是Mysql 5.6版本引入的技术优化。

旨在 在“仅能利用最左前缀索的场景”下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。

过滤会减少遍历索引查出的主键条数，从而减少回表次数，提示整体性能。

5.锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

表级锁

MySQL里面的表级锁共两种，表锁和元数据锁。

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

元数据锁

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；

当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

死锁

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。

由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。

前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

二阶段锁

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

死锁

死锁简单来说就是两个事务在等待对方释放锁，造成了死锁。

死锁解决策略：

  1. 直接等待，直到超时。

缺点在于无法准确地估算等待时间，太长的话业务系统可能是无法接受的缓慢，短的话可能会造成误伤。

  1. 死锁检测。

发现死锁，主动回滚死锁链条的某一个事务，让其他事务得以继续进行。

所有事务都要更新同一行的场景呢：

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。

假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。

虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。

因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；

如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。

基本思路就是，对于相同行的更新，在进入引擎之前排队。

这样在 InnoDB 内部就不会有大量的死锁检测工作了。

你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。

还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。

这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

实战问题

1.普通索引和唯一索引选择

查询

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

先看区别：

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的

记录。对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

性能差距其实是微乎其微的：

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

在 InnoDB 中，每个数据页的大小默认是 16KB。

MySQL将数据一次性加载进了内存，所以查询起来的速度也很快了。

更新

InnoDB更新有一个change buffer，不会直接写盘，会先存放在内存中。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。

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

如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。

这时，InnoDB 的处理流程如下：

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

但，这不是我们关注的重点。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。

change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

如果业务模型是插入后立刻查询，就会立即出发merge操作，影响性能了。

索引选择和实践

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。

所以，建议尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。

而在其他情况下，change buffer 都能提升更新性能。

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

2.MySQL选错索引

具体原因：MySQL的一张表支持多个索引，可能由于MySQL选错索引，导致执行速度很慢。

优化器的逻辑

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。

扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

判断扫描的行数

这是因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。

而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。

当然，从执行时间看来，这个选择并不是最优的。

mysql> select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

索引a需要查询1000行，索引b则是50000行，显然是索引a更快，但是优化器可能会选择索引b。

这个时候用force index是一种处理方案，但必须指定索引的名字，如果名字改了也会出现问题。

主要在于MySQL认为选了索引b可以避免排序，因为索引已经是有序的了

3.给字符串字段加索引

字符串支持前缀索引，用全字符串索引还是前缀索引，各有优劣。

前缀索引节省空间，但可能会增加额外的记录扫描次数，全字符颙会占空间。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

全字符索引肯定只要查一次，前缀索引也许也要好几次，但是有一定区分度的话，也可以减少查询次数，还能节省空间。

4.MySQL抖动

抖动是指性能出现波动，或者某些时间内数据库无法处理新的请求。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。

这时候只能把最久不使用的数据页从内存中淘汰掉：

如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；

日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。

所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

5.表数据删除 表文件大小不变

表数据既可以存在共享表空间里，也可以是单独的文件。

这个行为是由参数 innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。

将这个值设置为ON，这样单独存储一个文件，删除表的时候也能够将空间清理，放在共享表空间就无法轻松地清理了。

数据删除流程

假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。

如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

如果删除了一个数据页的数据，那么整个数据页都可以复用了。

插入也会

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

由于 page A 满了，再插入一个 ID 是 550 的数据时，就不得不再申请一个新的页面 page B 来保存数据了。页分裂完成后，page A 的末尾就留下了空洞（注意：实际上，可能不止 1 个记录的位置是空洞）。

可以通过重建表来达到去除空洞的目的。

alter table A engine=InnoDB

6.count(*)的效率问题

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

而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么InnoDB不维护一个行数的属性呢？

即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

各种count的区别

  1. count(主键id)

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。

server 层拿到 id 后，判断是不可能为空的，就按行累加。

  1. count(1)

InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

  1. count(字段)

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

  1. count(*)

count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

7.order by 是怎么工作的？

select city,name,age from t where city='杭州' order by name limit 1000  ;

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

通常情况下，这个语句执行流程如下所示 ：

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；

  2. 从索引 city 找到第一个满足 city=‘杭州’条件的主键 id，也就是图中的 ID_X；

  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；

  4. 从索引 city 取下一个记录的主键 id；

  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；

  6. 对 sort_buffer 中的数据按照字段 name 做快速排序；

  7. 按照排序结果取前 1000 行返回给客户端。

如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。

sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

rowid排序

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。

它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

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

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

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

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

如果对排序的字段设置索引，就可以不需要再重新排序了

8.如何显示随机消息

直接使用 order by rand()，这个语句需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的。所以，在设计的时候你要尽量避开这种写法。

可以考虑以下的方法

取得整个表的行数，记为 C；

根据相同的随机方法得到 Y1、Y2、Y3；

再执行三个 limit Y, 1 语句得到三行数据。

9.SQL逻辑相同但是性能差异大

例如： 为什么条件是 where t_modified=‘2018-7-1’的时候可以用上索引，而改成 where month(t_modified)=7 的时候就不行了？

因为month函数有可能会破坏索引的有序性，MySQL会因此而不走索引。

需要注意的是，优化器并不是要放弃使用这个索引。

在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。

不过优化器在个问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索引。

比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。

所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

10.隐式类型转换

select * from tradelog where tradeid=110717;

字段是varchar ，然而输入的是数字的时候。

现在这里就有两个问题：

数据类型转换的规则是什么？

为什么有数据类型转换，就需要走全索引扫描？

因为在内部会有类型转换，本质上是调用了函数，对索引字段做函数操作，优化器会放弃走树搜索功能。

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

还有编码转换 但比较少见。

11.查询变得很慢

1.查询长时间不返回

有可能是表被锁了。
show processlist
select blocking_pid from sys.schema_table_lock_waits

可以拿到id 然后kill。

等flush

MySQL 里面对表做 flush 操作的用法，一般有以下两个：

flush tables t with read lock;

flush tables with read lock;

这两个 flush 语句，如果指定表 t 的话，代表的是只关闭表 t；如果没有指定具体的表名，则表示关闭 MySQL 里所有打开的表。但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。

所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。

连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。

2.查询慢

mysql> select * from t where c=50000 limit 1;

比如这种需要扫描好多行的SQL语句。

带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；

而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。

12.幻读

幻读

  1. Q1 只返回 id=5 这一行；

  2. 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；

  3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

关于幻读的说明：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

  2. 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

这样看，这些数据也没啥问题，但是我们再来看看这时候 binlog 里面的内容。

  1. T2 时刻，session B 事务提交，写入了两条语句；

  2. T4 时刻，session C 事务提交，写入了两条语句；

  3. T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

可以看到，按照日志顺序执行，id=0 这一行的最终结果也是 (0,5,5)。

所以，id=0 这一行的问题解决了。

但同时你也可以看到，id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地，把所有的记录都上了锁，还是阻止不了 id=1 这一行的插入和更新呢？

原因很简单。在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁。

也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

新加入的记录，不是加锁能够解决的

如何解决幻读的问题

因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

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

也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

  1. session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);

  2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；

  3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；

  4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。

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

13.为什么只改一行 锁很多内容？

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

  • 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

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

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

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

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

举例子：

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);

等值查询间隙锁.png

由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；

同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

非唯一索引等值锁

看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？我们来分析一下吧。

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。

根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。

根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。

比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。你可以自己验证一下效果。

具体可以参考极客时间MySQL实战45讲

14.MySQL饮鸩止渴的提高性能方法

1.短连接风暴

MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

但是，短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

如果增加了连接上限，会导致大量的资源花在了权限验证等逻辑，已经连接的线程拿不到CPU资源去执行SQL请求。

主动断开连接

可能是有损的，服务端主动断开连接，客户端未必能够立刻通知到。而客户端一直用原先的连接请求数据库，看上去MySQL就一直没恢复了。

减少连接过程的消耗

有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

2.慢查询问题

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

1.索引没有设计好；

2.SQL 语句没写好；

3.MySQL 选错了索引。

紧急创建索引

比较理想的是能够在备库先执行。假设现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

1.在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；

2.执行主备切换；

3.这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。这是一个“古老”的 DDL 方案。平时在做变更的时候，应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。

至于SQL语句的问题，只能改语句才能解决了。比如force index等。

3.QPS突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

1.一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。

2.如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。

3.如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

方案3很可能会影响到其他的接口，也可能导致后续的业务逻辑全都失败，所以是优先级最低的解决方案。

15.MySQL保证数据不丢失

binlog写入机制

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

1.sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

2.sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

3.sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

redo log的写入机制

redo log先写到redo log buffer

如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。

具体参考 极客时间MySQL

16.MySQL如何保证主备一致

主备切换流程

切换时设为只读，是为了防止切换过程中出现双写，导致主备不一致。

切换的过程将binlog发给备库，写到本地文件，称为中转日志。

为什么会有 mixed 格式的 binlog？

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。

但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

17.MySQL如何保证高可用

先说一个概念，同步延迟

与数据同步有关的时间点主要包括以下三个：

1.主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;

2.之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;

3.备库 B 执行完成这个事务，我们把这个时刻记为 T3。所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

seconds_behind_master 的计算方法是这样的：

1.每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；

2.备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。

因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况。

备库压力大

由于主库直接影响业务，大家使用起来会比较克制，反而忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。

大事务

一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。

18.备库延迟好几个小时

并行复制在分发的时候，需要满足以下这两个基本要求：

1.不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。

2.同一个事务不能被拆开，必须放到同一个 worker 中。

19.主库出问题 从库怎么办？

MySQL 5.6 版本引入的 GTID 模式

20. 读写分离的坑

客户端直连

客户端会感知到一切数据库的操作细节，并为此调整数据库的连接信息，所以可以用后端管理组件，如Zookeeper。

proxy代理

proxy的高可用架构会比较复杂

不论哪种结构，客户端都希望查询从库的数据结果，跟查主库的数据结果是一样的。

  1. 强制走主库

如果是必须拿最新结果的请求，强制走主库，如果可以读到旧数据，就可以走从库。

  1. sleep

这个方案是假设主从延迟在n秒以内 通过select sleep(1)实现

  1. 判断主备无延迟

  • 1.第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。

  • 2.对比位点确保主备无延迟。

  • 3.对比 GTID 集合确保主备无延迟。

  • 4.配合 semi-sync

  • 5.等主库位点方案

select master_pos_wait(file, pos[, timeout]);
  • 6.GTID 方案

19.如何判断一个数据库是否出现问题了

在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在最大连接数里面的。

MySQL 这样设计是非常有意义的。因为，进入锁等待的线程已经不吃 CPU 了；更重要的是，必须这么设计，才能避免整个系统锁死。

可以改为查表判断：

为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，我们需要找一个访问 InnoDB 的场景。一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：

mysql> select * from mysql.health_check;

20.误删数据后除了跑路，还能怎么办？

为了找到解决误删数据的更高效的方法，我们需要先对和 MySQL 相关的误删数据，做下分类：

  1. 使用 delete 语句误删数据行；

  2. 使用 drop table 或者 truncate table 语句误删数据表；

  3. 使用 drop database 语句误删数据库；

  4. 使用 rm 命令误删整个 MySQL 实例。

1.误删行

Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。

而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。

不建议在主库执行操作

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。

2.误删库 / 表

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

延迟复制备库

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。

预防误删就是账号分离，制定操作规范。

3.rm 删除数据

只要不是恶意删除，会选举出一个新的主库。

21.kill不掉的语句

  1. 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；

  2. 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；

  3. 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

MySQL需要判断进程的连接状态。

22.查数据会不会把内存打爆？

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

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

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

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

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

也就是说，MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

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

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

23.到底可不可以问join

join 语句到底是怎么执行的?

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。

在这条语句里，被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，因此这个语句的执行流程是这样的：

  1. 从表 t1 中读入一行数据 R；

  2. 从数据行 R 中，取出 a 字段到表 t2 里去查找；

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

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

如果不用join就必须先取出数据，程序里每次单独组SQL，在这种情况下是不如join效率高的。

在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

简而言之，结论是，应该让小表当驱动表。

参考极客时间

join 语句的两种算法，分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。

24.为什么临时表可以重名

  • 内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。

  • 而临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。

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

  1. 建表语法是 create temporary table …。

  2. 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。

  3. 临时表可以与普通表同名。

  4. session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。

  5. show tables 命令不显示临时表。

25.什么时候会用内部临时表

union

(select 1000 as f) union (select id from t1 order by id desc limit 2);

这条语句用到了 union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。

这个语句的执行流程是这样的：

  1. 创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。执行第一个子查询，得到 1000 这个值，并存入临时表中。执行第二个子查询：

  2. 拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；

  3. 取到第二行 id=999，插入临时表成功。从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。

group by

select id%10 as m, count(*) as c from t1 group by m;

这个语句的执行流程是这样的：

  1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

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

  3. 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

26.要不要使用内存表 momery引擎的特性

由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。因此，在生产上，我不建议你使用普通内存表。

27.自增主键为什么不是连续的

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

  1. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；

  2. 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。

假设，某次要插入的值是 X，当前的自增值是 Y。

如果 X<Y，那么这个表的自增值不变；如果 X≥Y，就需要把当前自增值修改为新的自增值。

假设，表 t 里面已经有了 (1,1,1) 这条记录，这时我再执行一条插入数据命令

insert into t values(null, 1, 1);

这个语句的执行流程就是：

  1. 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);

  2. InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；

  3. 将传入的行的值改成 (2,1,1);将表的自增值改成 3；

  4. 继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。

可以看到，这个表的自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。

唯一键冲突是导致自增主键 id 不连续的第一种原因。同样地，事务回滚也会产生类似的现象，这就是第二种原因。

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

28.insert语句的锁为什么这么多？

insert锁

29.怎么最快地复制一张表？

mysqldump方法

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

这条命令中，主要参数含义如下：

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

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

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

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

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

导出csv文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

物理拷贝方法

直接复制不可行。

因为，一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的。

30. grant 之后是否需要刷新

如果内存的权限数据和磁盘数据表相同的话，不需要执行 flush privileges。而如果我们都是用 grant/revoke 语句来执行的话，内存和数据表本来就是保持同步更新的。因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。

直接操作系统表是不规范的操作。

31.自增id用完怎么办？

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

  1. 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。

  2. row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。

  3. Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。

  4. InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。

  5. thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。

结课

后面有需要再补充吧。

Share