Mysql45讲学习笔记

以下内容来源于林晓斌的mysql 45讲，如果觉得不错，可以去极客时间订阅一下，可以查看全部内容

名词解释

DML(Data Manipulation Language)数据库操作语言

数据库数据的操作,主要用来对数据库的数据进行一些操作，常用的就是INSERT、UPDATE、DELETE。

DDL(Data Definition Language)数据定义语言

常用的有CREATE和DROP，用于在数据库中创建新表或删除表，以及为表加入索引，加字段等修改表结构等。

WAL(Write-Ahead Logging)

它的关键点就是先写日志，再写磁盘.

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

MDL（meta data lock）元数据锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

MVCC（Multi-Version Concurrency Control）多版本并发控制

一种并发控制机制，在数据库中用来控制并发执行的事务，控制事务隔离进行。

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

中转日志（relay log）主备系统中，备库将主库的binlog写入本地的relay log中，根据relay log对数据库进行操作。

日志逻辑序列号（log sequence number，LSN第24篇）

LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

执行explain 语句后，使用show warnings; 可以看到MySQL 实际执行的SQL，包括字符集的转换

联合索引就是两个字段拼起来作索引
比如一个索引如果定义为(f1,f2),
在数据上，就是f1的值之后跟着f2的值。
查找的时候，比如执行 where f1=M and f2=N, 也是把M,N拼起来，去索引树查找

背景知识：

1.什么是数据表空间和系统表空间
 比如有一个表a
那么a.ibd 就是数据表空间；
“表a的表结构”这个信息存在ibdata1的系统表空间里

索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

2.extra中可能的结果

using index condition是索引下推

use index使用了覆盖索引

use where;

where中的条件不是索引的前导列，需要在server层进行where条件过滤

Using temporary，表示的是需要使用临时表；

Using filesort，表示的是需要执行排序操作。

Using MRR，表示的是用上了 MRR 优化。（索引a是一个范围查询，查询可以得到很多的主键，将主键排序后查询，这样就将随机查询变成了顺序查询）

using join buffer (Block Nested Loop)被驱动表上没有索引使用的算法，需要优化（使用被驱动表索引时，使用Index Nested-Loop Join ），给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。

3.order by group by limit 以及where条件，有执行的先后顺序吗？

先where ,再order by 最后limit

4.查询执行情况相关语句

（1）show processlist 命令，看看当前语句处于什么状态。

MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

（2）怎么查出是谁占着这个写锁。如果你用的是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到。
mysql> select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G

（3）show engine innodb status

（4）show variables like 'transaction_isolation';查看事物隔离级别

（5）在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

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

（6）START TRANSACTION WITH CONSISTENT SNAPSHOT；确保这个语句执行完就可以得到一个一致性视图

（7）set long_query_time=0;

是将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；

（8）show index from t;

看到一个索引的基数

（9）innodb_thread_concurrency控制 InnoDB 的并发线程上限，设置为多少合适？
在 InnoDB 中，innodb_thread_concurrency 这个参数的默认值是 0，表示不限制并发线程数量。但是，不限制并发线程数肯定是不行的。因为，一个机器的 CPU 核数有限，线程全冲进来，上下文切换的成本就会太高。所以，通常情况下，我们建议把 innodb_thread_concurrency 设置为 64~128 之间的值。这时，你一定会有疑问，并发线程上限数设置为 128 够干啥，线上的并发连接数动不动就上千了。

（10）才一百多万条数据 为啥查个总数花了30来秒哦 算正常吗 select count(id) from table;

作者回复: 不正常
执行过程中show processlist和show engine innodb status 看看在干啥

5.“N叉树”的N值在MySQL中是可以被人工调整的么？

1， 通过改变key值来调整
N叉树中非叶子节点存放的是索引信息，索引包含Key和Point指针。Point指针固定为6个字节，假如Key为10个字节，那么单个索引就是16个字节。如果B+树中页大小为16K，那么一个页就可以存储1024个索引，此时N就等于1024。我们通过改变Key的大小，就可以改变N的值
2， 改变页的大小
页越大，一页存放的索引就越多，N就越大。

数据页调整后，如果数据页太小层数会太深，数据页太大，加载到内存的时间和单个数据页查询时间会提高，需要达到平衡才行。

6.等号顺序问题？

实际上 MySQL 优化器执行过程中，where 条件部分， a=b 和 b=a 的写法是一样的。

7.怎么查询慢日志？slow log

 查看是否开启慢查询日志

show variables like '%slow_query_log%';

开启/关闭日志

set global slow_query_log=1; //开启

set global slow_query_log=0; //关闭

如何查找慢查询日志

show global variables like 'slow_query_log_file%';

optimizer_trace 可支持把MySQL查询执行计划树打印出来 《MySQL慢查询优化》之慢SQL日志获取与分析 - 马非白即黑 - 博客园

/* 打开 optimizer_trace，只对本线程有效 */

SET optimizer_trace='enabled=on';

/* 查看 OPTIMIZER_TRACE 输出 */

SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* 或者导入到一个命名为xx.trace的文件，然后用JSON阅读器来查看  */
SELECT TRACE INTO DUMPFILE “xx.trace” FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

修改慢查询阈值

set global long_query_time=5

日志分析工具

MySQL提供了日志分析工具mysqldumpslow，帮助使用者自动化分析慢查询日志，将分析结果按照参数所指定的顺序输出。

常见参数：

• r：返回记录
• t： 查询时间
• c： 访问次数
• -t：即为返回前面多少条的数据
• -s： 是表示按照何种方式排序
• -g：后边搭配一个正则匹配模式，大小写不敏感的

通常，命令可以结合 | 和 less、more 等其他命令使用，便于参看。

常见使用：

• 获取访问次数最多的前10个SQL： mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log | less
• 获取返回记录集最多前10个SQL： mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log | less
• 获取按照时间排序的前10条含有左连接的SQL： mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log | more

8.explain语句后，再执行show warns会显示mysql优化后的语句

9.关于索引加锁问题？

   select * from t where c>=15 and c<=20 order by c desc for update;
        为什么这种c=20就是用来查数据的就不是向右遍历
        select * from t where c>=15 and c<=20 这种就是向右遍历
        怎么去判断合适是查找数据，何时又是遍历呢，是因为第一个有order by desc，然后反向向左遍历了吗？所以只需要[20,25)来判断已经是最后一个20就可以了是吧

作者回复: 索引搜索就是 “找到第一个值，然后向左或向右遍历”，
order by desc 就是要用最大的值来找第一个；
order by就是要用做小的值来找第一个；

“所以只需要[20,25)来判断已经是最后一个20就可以了是吧”，
你描述的意思是对的，但是在MySQL里面不建议写这样的前闭后开区间哈，容易造成误解。
可以描述为：
“取第一个id=20后，向右遍历(25,25)这个间隙”

10.insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

11.自增索引都设置为bigint unsigned

因为现在很多业务插入数据很凶残，容易超过int 上限

12.explain结果中的type字段的含义（连接类型）

 all

全表扫描

index：

也是全表扫描，不过是按照index回表

如果连接类型为type，而且extra列中的值为‘Using index’，那么称这种情况为 索引覆盖；

range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。

ref

 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

ref_eq

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

mysql中explain的type的解释_dennis211的博客-CSDN博客_explain type

1. 查询sql的具体流程

2. 更新语句的执行流程

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

 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

3. redo log和binlog的不同

这两种日志有以下三点不同。redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

回答：这个问题其实问得非常好。这里涉及到了，“redo log 里面到底是什么”的问题。实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

回答：这两个问题可以一起回答。在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：begin;insert into t1 ...insert into t2 ...commit;这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。（这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的 IO 消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。这个问题我们会在后面第 22 篇文章《MySQL 有哪些“饮鸩止渴”的提高性能的方法？》中再详细展开）。单独执行一个更新语句的时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成。

4.隔离级别和视图的关系

数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。(MVCC中使用的一致性读视图)

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。

这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

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

5. 数据库的多版本并发控制（MVCC）

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

“快照”在 MVCC 里是怎么工作的？在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。这个快照是基于整库的。

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

每行数据也都是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。

这是一个隐藏列，还有另外一个roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

两者都在InnoDB的聚簇索引中，大概就长这样：

undo log的回滚机制也是依靠这个版本链，每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

接下来可以说一下事务隔离级别和MVCC的关系了，下面的例子是一个版本链，事务id大家可以看出，三个事务分别作了不同的事情。

在读提交隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的，在这个隔离级别下，事务在每次查询开始时都会生成一个独立的ReadView。

可重复读，在第一次读取数据时生成一个ReadView，对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了，所以一个事务的查询结果每次都是一样的。

总结：

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

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。

普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据；

而当前读，总是读取已经提交完成的最新版本。当前读就包含更新语句和select...for update语句等

6.主键索引和非主键索引

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

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

B+树的叶子节点是page （页），一个页里面可以存多个行

（1）page内部怎么去定位行数据？

作者回复: 内部有个有序数组，二分法

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

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

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

（3）索引的种类和使用场景？

哈希表 只有等值查询的场景

有序数组 在等值查询和范围查询场景中的性能就都非常优秀，插入删除效率差。所以有序数组索引只适用于静态存储引擎，例如是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

B+树

7.索引维护

（1）自增主键优点

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

如果使用业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

（2）不适合做自增主键的情况

只有一个索引；该索引必须是唯一索引。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要回表。

8.覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

 -高频查询，可以建立联合索引来使用覆盖索引，不用回表。
- 非高频查询，在已有的联合索引基础上，使用最左前缀原则来快速查询。

如果为每一种查询都设计一个索引，索引是不是太多了。因此，B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

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

在建立联合索引的时候，如何安排索引内的字段顺序。

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

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

8.1 为什么要重建索引。

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

9. 索引下推

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

不使用索引条件下推优化时的查询过程

获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。然后通过where条件判断当前数据是否符合条件，符合返回数据。

使用索引条件下推优化时的查询过程

获取下一行的索引信息。检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据。 

适用条件

1. 需要整表扫描的情况。比如：range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。（5.6版本不适用分区表查询，5.7版本后可以用于分区表查询）。
2.  对于InnDB引擎只适用于二级索引，因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。 
3. 引用子查询的条件不能下推。 
4. 调用存储过程的条件不能下推，存储引擎无法调用位于MySQL服务器中的存储过程。
5.  触发条件不能下推。

示例

当使用了索引条件下推优化技术后，可以通过索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合条件的数据才将整行数据查询出来。查看执行计划时发现extra一栏中有Using index condition信息，说明使用了索引下推。

索引为(name,address,first_name)

配置（默认开启）

SET optimizer_switch = 'index_condition_pushdown=off';

链接：https://juejin.im/post/5deef343e51d455819022033

10.全局锁，表锁

（1）MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份.

（2）MySQL里面表级锁有两种，一种是表锁，一种是元数据所(meta data lock,MDL)
表锁的语法是:lock tables ... read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

（3）行锁

死锁：几个线程相互等待对方占有的资源

死锁怎么解决：

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

以上方法都有风险，但是减少死锁的主要方向，就是控制访问相同资源的并发事务量。

建议：

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

1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。

innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

(4)如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：第一种，直接执行 delete from T limit 10000;第二种，在一个连接中循环执行 20 次 delete from T limit 500;第三种，在 20 个连接中同时执行 delete from T limit 500。你会选择哪一种方法呢？为什么呢？

第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

第二种方式，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。

11. 普通索引和唯一索引怎么选择？

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

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

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

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

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

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

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。因此写多读少的操作适合使用普通索引（例如：日志系统）。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

change buffer操作逻辑？

change buffer变化与数据块变化是分开的，最初redo中记录的只是change buffer的变更，因为还未应用到数据块上。而merge后redo记录的是数据块、change buffer的变更。

change buffer哪里降低了随机IO操作呢？

正常更新数据时，需要先从磁盘读数据，然后再更新，再写入磁盘。使用了changebuffer后，只需要将更新操作写在changebuffer中，merge的时候，将changeBuffer中相关的数据写到磁盘中。

那么不merge的时候，是不需要读磁盘的，减少了磁盘读入，同时，如果同一个数据更新了多次，且没有merge，那么会少写一次磁盘。

changebuffer是会落盘的，什么时候落盘呢？

“内存不足需要回收change buffer这部分内存“，只需要让change buffer本身持久化可以，不需要执行merge操作。merge操作是在读数据页的时候做的

某次写入使用了 change buffer 机制，之后主机异常重启，是否会丢失 change buffer 和数据。

答案是不会丢失。虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

merge 的过程是否会把数据直接写回磁盘

merge 的执行流程是这样的：从磁盘读入数据页到内存（老版本的数据页）；从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

12. mysql为什么会选错索引？

在普通查询中（不涉及临时表和排序），MySQL 选错索引肯定是在判断扫描行数的时候出问题了。

那么，问题就是：扫描行数是怎么判断的？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

那么，MySQL 是怎样得到索引的基数的呢？

MySQL 采样统计的方法。为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

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

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

除了上面统计行数不准以外，还有一个原因导致选错索引。如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

优化器有可能错误估计扫描行数，可使用analyze table 表名 重新统计信息

选错索引的例子：

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

因为b需要排序，所以选择了索引a,使用a索引只要扫描1000行，使用b索引扫描5001行 

select * from t where a between 10000 and 20000;

开启事务时，由于a是二级索引，需要回表，所以优化器选择了主键索引。

MySQL选错索引怎么破？

• 强制指定使用某个索引，不常用不推荐用
• 调整SQL语句，使优化器选择的和我们想的一样，不具有通用性
• 新建更合适的索引或者删除不合适的索引，是一个思路
• 使用analyze table可以解决索引统计信息不准确导致的索引选错的问题

假如要查 A in () AND B in (), 怎么建索引?

where A in (a,b,c) AND B in (x,y,z)
会转成
 
(A=a and B=x) or (A=a and B=y) or (A=a and B=z) or
(A=b and B=x) or (A=b and B=y) or (A=b and B=z) or
(A=c and B=x) or (A=c and B=y) or (A=c and B=z)

通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。为什么会出现这种情况呢？

session A 开启的事务对 session B的delete操作后的索引数据的统计时效产生了影响，因为需要保证事务A的重复读，在数据页没有实际删除，而索引的统计选择了N个数据页，这部分数据页不收到前台事务的影响，所以整体统计值会变大，直接影响了索引选择的准确性；

13.索引选取

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

（1）创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；前缀索引，由于查询的不确定性，必须到主键索引中再验证一次，因此会增加回表次数

（2）倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

（3）创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟倒序方式一样，都不支持范围扫描。

索引为char类型的201100001，那么可以使用数字类型来存这 9 位数字。比如 201100001，这样只需要占 4 个字节

本来九个字符需要vchar(9)来存储，varchar()保存的是字符.

即可以插入9个中文, 也可以插入9个字母或者数字.一般数据库都是UTF-8编码.在UTF-8编码下, 一个中文 3个字节. 字母或者英文 1个字节.那么就需要9个字节

而使用整型格式的话，只需要4个字节。

14.mysql为什么会抖一下？

更新数据过程为，先变更内存数据，然后记录变更到redo log中，（此时内存数据和磁盘数据不一致，内存中的页称为脏页）然后空闲的时候将redo log刷新到磁盘。抖一下的情况，可能就是在刷脏页（flush）。

flush 一般是说刷脏页，
purge一般是指清undo log,
merge一般是指应用change buffer

脏页是咋产生的？

因为使用了WAL技术，这个技术会把数据库的随机写转化为顺序写，但副作用就是会产生脏页。

WAL怎么把随机写转化为顺序写的？

写redolog是顺序写的，先写redolog等合适的时候再写磁盘，间接的将随机写变成了顺序写，性能确实会提高不少。

那么，什么情况会引发数据库的 flush 过程呢？

• InnoDB 的 redo log 写满了
• 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘
• MySQL 认为系统“空闲”的时候
• MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快

InnoDB 刷脏页的控制策略

首先，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

那个说fio命令会破坏硬盘的兄弟，是没用对命令。估计把-filename=/dev/sdb1 。。。这个的意思是从 分区 sdb1 的第一个扇区开始写入随机数据，去判断这个磁盘的写入速度。如果指定路径+文件名就不会出这事了~比如老师给的例子~

我们现在已经定义了“全力刷脏页”的行为，但平时总不能一直是全力刷吧？毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。

所以接下来，我们就一起看看 InnoDB 怎么控制引擎按照“全力”的百分比来刷脏页。

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

InnoDB 会根据这两个因素先单独算出两个数字。参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字。

然后，根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

总结：

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

1：啥是随机写？为啥那么耗性能？

随机写我的理解是，这次写磁盘的那个扇区和上一次没啥关系，需要重新定位位置，机械运动是很慢的即使不是机械运动重新定位写磁盘的位置也是很耗时的。

2：啥是顺序写？

顺序写我的理解是，这次写磁盘那个扇区就在上一次的下一个位置，不需要重新定位写磁盘的位置速度当然会快一些。

3：WAL怎么把随机写转化为顺序写的？

写redolog是顺序写的，先写redolog等合适的时候再写磁盘，间接的将随机写变成了顺序写，性能确实会提高不少。

100M的redo很容易写满，系统锁死，触发检查点推进，导致写操作卡住。由于主机IO能力很强，检查点会很快完成，卡住的写操作又很快可以执行。循环往复，现象就是写操作每隔一小段时间执行就会变慢几秒。

InnoDB 认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。为什么会影响性能，是因为要读取的数据不在内存中，但是脏页过多导致新页必须等待脏页刷盘导致的么？

4:数据页是“数据的存储单位”，每次要读写都是整页读入内存的。

5.mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
1146 - Table 'mysql.global_status' doesn't exist

为啥会出现表不存在呢？

作者回复: 先use information_schema再执行

14.表空间回收

1.一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

系统数据表，主要用于存储MySQL的系统数据，比如：数据字典、undo log(默认)等文件

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

•  OFF ，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
•  ON ，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

默认为ON，建议设为ON，因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

删除流程：

假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

现在，你已经知道了 InnoDB 的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就可以被复用了。

但是，数据页的复用跟记录的复用是不同的。

当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。以图 1 为例，如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。

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

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

重建表，就可以达到这样的目的。

使用 alter table A engine=InnoDB 命令来重建表

3.重建表（alter table A engine=InnoDB;可以帮你重建表）

当一个索引空洞很多时，可以通过重建表来复用空间。

新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中；

如果在往临时表插入数据过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。

Online DDL 之后，重建表的流程：

• 建立一个临时文件，扫描表 A 主键的所有数据页；
• 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
• 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
• 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
• 用临时文件替换表 A 的数据文件

DDL 之前是要拿 MDL 写锁的，这样还能叫 Online DDL 吗？

alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。

Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的

大表是很消耗cpu和io资源的，因此线上推荐使用GitHub 开源的 gh-ost 来做，比较安全。

4： online和inplace

临时表插入数据时，不允许表 A 有增删改操作，否则会造成数据丢失。所以表数据 copy 的方式不是 online 的。
而 inplace 的方式，在构建临时文件时，允许表 A 有增删改操作，期间新的增删改操作会记录到另外的日志文件，表 A 数据页的所有数据复制完成后，再应用日志文件（自己理解：应用日志文件时，不允许对表 A 增删改操作，即非 online 的）。整体操作是 online 的。
切换表名或临时文件时，需要获取 MDL 写锁。
inplace 表示在 innodb 引擎完成所有操作，对 server 层是透明的。inplace 操作不一定是 online 的，如 MySQL 8.0 的添加全文索引和空间索引。而 online 的一定是 inplace 的。

5.如果delete的数据还会被用于MVCC,那么该数据页(二级索引和聚簇索引)上的记录不会被物理删除,是被标记删除。只有当该事务不会用于mvcc了,才可以被purge线程把之前标记删除的数据真正删除掉.但是即便数据物理删除了,磁盘空间也不会返回给操作系统.可以通过show table status like 't';观察data_free来预估该表的碎片。如果过大,可以用alter table t engine=innodb来清除

6.假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：一个表 t 文件大小为 1TB；对这个表执行 alter table t engine=InnoDB；发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了 1.01TB。你觉得可能是什么原因呢 ？

本来就很紧凑，没能整出多少剩余空间。
重新收缩的过程中，页会按90%满的比例来重新整理页数据（10%留给UPDATE使用），
未整理之前页已经占用90%以上，收缩之后，文件就反而变大了。

在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

71.Truncate 会释放表空间吗

Truncate 可以理解为drop+create

2.重建表的时候如果没有数据更新，有没有可能产生页分裂和空洞

Online 可以认为没有
4.应用 row log 的过程会不会再次产生页分裂和空洞

可能会
5.不影响增删改，就是 Online；相对 Server层没有新建临时表，就是 inplace，这里怎么判断是不是相对 Server 层没有新建临时表

一个最直观的判断方法是看命令执行后影响的行数，没有新建临时表的话新建的行数是0。

6.optimize table t 等于 recreate+analyze

15.count的用法

首先你要弄清楚 count() 的语义。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

扫描索引走的是普通索引，因为普通索引数据较小，代价更低。

count(字段值):

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
count(主键id):

InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

count(id) 也是可以走普通索引得到的。

count(1)：

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

count(*) :

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

并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。可以理解为，返回了一行，但是0个字段。

count字段，包含了server层复制字段，判断等操作，因此更加慢

count(id)可能会选择最小的索引来遍历
而count(字段)的话，如果字段上没有索引，就只能选主键索引

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

如果count(*)效率很差，应该要怎么解决呢？

方法的基本思路：你需要自己找一个地方，把操作记录表的行数存起来。

在数据库保存计数

在缓存中保存计数，这样是不能保持数据一致性的，因为更新计数和更新数据是两个操作，操作中间可能会有其他事物进行查询，这个查询的返回结果是不一致的，因此这个方法不能使用，主要是因为缓存和数据库没有一致性视图

答疑文章：业务实践问题

添加relation字段，a关注b为1，b关注a为2，相互关注为3

表中Liker_d>User_id

A的id为a_id,B的id为b_id。

当a_id>b_id时。

在A关注B情况下，此时A_id(a_id)>B_id(b_id)

insert into `like`(user_id, liker_id, relation_ship) values(B(b_id), A(a_id), 1) on duplicate key update relation_ship=relation_ship | 1;

如果B要关注A，此时A_id(b_id)<B_id(a_id)

insert into `like`(user_id, liker_id, relation_ship) values(A(b_id),B(a_id) 2) on duplicate key update relation_ship=relation_ship | 2;

A关注B,B关注A,这个描述中，第一个参数为a_id,第二个参数为b_id

业务上这么写,a_id,b_id是形参。因此a关注b和b关注a会执行到下面两个分支语句，其实更新的是一条数据。

if(a_id>b_id){

relation_ship = 1;

insert(b_id,a_id...)

}else{

relation_ship = 2;

insert(a_id,b_id...)

}

其实就是保证liker_id>user_id就可以了

结果为

like表中只有1或者2，或者3的一条数据，当有一方关注的时候，另一方也想关注时，会执行了1|2=3的操作

16.order by的内部逻辑

（1）MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size
  1>如果排序的数据量小于sort_buffer_size，排序将会在内存中完成
  2>如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
  3>在使用外部排序时（一般是归并排序），MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件
（2）mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序
   1>如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
   2>该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
   3>按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表
全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端

缺点：
1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

排序的单行长度太大，就会使用下面的rowid排序
    SET max_length_for_sort_data = 16;
rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
3.按照排序后的顺序，取id进行回表取出想要获取的数据
4.将结果集返回给客户端

优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问

缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问。

在查询结果中可以看是否使用临时表（），扫描行数确认使用了哪个索引。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

number_of_tmp_files 中看到是否使用了临时文件。

examined_rows=4000，表示参与排序的行数是 4000 行。

sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

sort_mode 里面显示的<sort_key,row_id>时，使用rowid 排序

filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。

（3）评论精选
在上述两种排序的基础之上，讲述了联合索引，联合索引解决了不需要按照姓名进行排序，这样只需要扫描1000次。进一步是覆盖索引，连回到主键取索引都不需要了。’

背景：有个 order by 使用场景 ， 有个页面，需要按数据插入时间倒序来查看一张记录表的信息 ，因为除了分页的参数 ， 没有其他 where 的条件 ，所以除了主键外没有其他索引 。

1： bigInt(20) 、 tinyint(2) 、varchar(32) 这种后面带数字与不带数字有何区别？ 每次建字段都会考虑长度 ，但实际却不知道他有何作用

bigint和int加数字都不影响能存储的值。
bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是2^32-1。只是有些前端会根据括号里来截取显示而已，建议不加。

varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。

需要注意的是255这个边界。小于255都需要一个字节记录长度，超过255就需要两个字节(1个字节8位，最大值是 255，用来记录 varchar 的长度。如果大于255，1个字节无法存储，因此需要2个字节（16位）)

mysql表中字段总长度可以有65535个字节，意思就是如果一个表只有varchar（n）这么一个字段，而且是utf8的话，那么这个字段最大可以有65535个字节的长度，差不多最大为n=65535/3=21845的字符。（

一个中文字占3个字节。编码：UTF-8）
为什么说差不多。因为varchar在存储的时候，会另加一个字节来记录长度(如果列声明的长度超过255字节，则使用两个字节，刚好2的8次方是255，超过255就只能用2个字节16位来记录了)。
如果表中有个char（10）和varchar（n）的话，那么就这样，n的最大值=65535-10*3

2：无条件查列表页除了全表扫还有其他建立索引的办法么
select * from t order by create_time desk limit 100

1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序
2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.
因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。

 3：如果加入 group by ， 数据该如何走

todo:

4：分页问题

分页limit过大时会导致大量排序，可以记录上一页最后的ID，下一页查询条件带上 where ID>上一页最后ID limit 100

17.随机取数据的方法

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

从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。

是不是所有的临时表都是内存表呢？

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。如果需要临时表，还要再看临时表大小，小的用memory引擎，大的用innodb

排序用的算法？

这里用到优先队列排序算法，原因是，这条 SQL 语句是 limit 1000，如果使用优先队列算法的话，需要维护的堆的大小就是 1000 行的 (name,rowid)，超过了我设置的 sort_buffer_size 大小，所以只能使用归并排序算法。

前提是有limit 子句情况下，只要sort buffer 足够，就采用优先队列排序，而不用管到底是全字段排序还是rowid排序。

解决方案：

为什么以下的随机算法比order by rand()的代价小很多？
因为随机算法2进行limit获取数据的时候是根据主键排序获取的，主键天然索引排序。获取到第9999条的数据也远比order by rand()方法的组成临时表R字段排序再获取rowid代价小的多。

1 总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)，实际上它还是可以继续优化，来进一步减少扫描行数的。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

解决

假设Y1，Y2，Y3是由小到大的三个数，则可以优化成这样，这样扫描行数为Y3
id1 = select * from t limit @Y1，1；
id2= select * from t where id > id1 limit @Y2-@Y1，1；
select * from t where id > id2 limit @Y3 - @Y2，1；

或者

取 Y1、Y2 和 Y3 里面最大的一个数，记为 M，最小的一个数记为 N，然后执行下面这条 SQL 语句：

mysql> select * from t limit N, M-N+1;

再加上取整个表总行数的 C 行，这个方案的扫描行数总共只需要 C+M+1 行。

当然也可以先取回 id 值，在应用中确定了三个 id 值以后，再执行三次 where id=X 的语句也是可以的。

2 取 max(id) 和 min(id) 都是不需要扫描索引的（因为id索引是按照顺序存储的，直接取头部和尾部的值就可以了），而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。但实际上，这个算法本身并不严格满足题目的随机要求，因为 ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。空洞是因为id可能是不连续的，如果是连续的空洞就没有了，可以以此为出发点改进代码

mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

3 对应单词这种总量不是很多的数据，第一感觉应该装jdk缓存或者redis缓存。由于需要随机访问，数组比较好。假如一个单词平均10个字节，10*10000，不到1M就装下了。

如果一定要用数据库来做，上面的方案2比较好，空洞的问题，如果单词库不变，可以在上线前整理数据，把空洞处理调。比如：原来单词存在A表，新建B表 ，执行 insert into B(word) select word from A. B的id是自增的，就会生成连续的主键。当然如果A表写比较频繁，且数据量较大，业务上禁用 这种写法，RR的隔离级别会锁A表

18.sql损耗性能的语句

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

select count(*) from tradelog where month(t_modified)=7;

如果计算 month() 函数的话，你会看到传入 7 的时候，在树的第一层就不知道该怎么办了。 

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

rows=100335，说明这条语句扫描了整个索引的所有值；Extra 字段的 Using index，表示的是使用了覆盖索引。也就是说，由于在 t_modified 字段加了 month() 函数操作，导致了全索引扫描。为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法，优化器就能按照我们预期的，用上 t_modified 索引的快速定位能力了。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

（2）隐式类型转换 

select * from tradelog where tradeid=110717;

tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。 相当于以下语句

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

 这里就使用到函数了，因此放弃了走树搜索功能

（3）隐式字符编码转换

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行，这个步骤用上了主键索引，rows=1 表示只扫描一行；

第二行 key=NULL，表示没有用上交易详情表 trade_detail 上的 tradeid 索引，进行了全表扫描。

这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，因此涉及到编码转换问题

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

这其实也是第一个问题

但是如果函数是放到输入参数上的，那么不会影响使用索引，如以下例子

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

 优化方法

a.tradeid 字段的字符集也改成 utf8mb4

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null; 

b.如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大， 或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。 

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

思考题 :
（1）select * from tradelog where id="83126";

当主键是整数类型条件是字符串时，会走索引。
文中提到了当字符串和数字比较时会把字符串转化为数字，所以隐式转换不会应用到字段上，所以可以走索引。

（2）a表有100条记录，b表有10000条记录，两张表做关联查询时，是将a表放前面效率高，还是b表放前面效率高？

作者回复: (这题目改成100万禾10000万比较好)
如果是考察语句写法，这两个表谁放前面都一样，优化器会调整顺序选择合适的驱动表；

如果是考察优化器怎么实现的，你可以这么想，每次在树搜索里面做一次查找都是log(n), 所以对比的是100*log(10000)和 10000*log(100)哪个小，显然是前者，所以结论应该是让小表驱动大表。

19.为什么会出现幻读？

即使把所有的记录都加上锁，还是阻止不了新插入的记录。

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

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

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

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，这个逻辑一旦有并发，就会碰到死锁。

你看到了，其实都不需要用到后面的 update 语句，就已经形成死锁了。我们按语句执行顺序来分析一下：session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。你现在知道了，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

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

 innodb_locks_unsafe_for_binlog 这个参数就是这个意思 “不加gap lock”，

这个已经要被废弃了（8.0就没有了），所以不建议设置哈，容易造成误会。

如果真的要去掉gap lock，可以考虑改用RC隔离级别+binlog_format=row。

间隙锁和行锁合称 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]。

（a，b]代表着会锁住a跟b之间，不让插入数据，还会锁住数据b本身，但不会锁住数据a(即开和闭对应着要不要锁住数据本身)

至于为什么左开右闭，说是迎合自增主键特性？

要让整个区间连续，总要有一边闭区间哈，二选一。然后MySQL一直支持的是升序索引

加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁。

评论精华

（1） insert into t2 (id,v) select 2,2 from dual where not exists(select * from t2 where id=2); (session2) // 这里为什么会阻塞，直到session1提交呢？

Insert...select 是会给select部分加读锁的

这个也是为了保证一致性

（2）mysql官方提到自增锁是个表级锁，老师能介绍下这个吗，以及实际项目中高并发insert是否需要避免自增主键？

作者回复: 好问题，innodb_auroinc_lock_mode设置为2，binlog_formate设置成row就行，没有表锁问题

（3）对于非索引字段进行update或select .. for update操作，代价极高。所有记录上锁，以及所有间隔的锁。
对于索引字段进行上述操作，代价一般。只有索引字段本身和附近的间隔会被加锁。

（4）next-key lock = gap lock + record lock
间隙锁是一种锁的类型
排他是一种锁的行为

（5）在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读提交隔离级别的原因。

20.锁是怎么锁的？

1、查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）；
2、等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁；
3、范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止；

锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。因为覆盖索引不能锁住主键索引上的数据。（也就是说使用了覆盖索引，是不能锁住查询的其他字段的）

范围查询和等值查询？

<=，在找“第一个值”的时候，按照等值去找的，找到第一个值后，要在索引内找下一个值，对应的就是范围查找

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

补充：binlong和redo logo是怎么保证数据不丢失的？

第23篇

（1）binlog

写入规则：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。write 和 fsync 的时机，是由参数 sync_binlog 控制的：sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

（2）redo log

存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

如果每次提交都要写这两次磁盘的话，那是不是优化效果就没有那么明显了呢？那么了解下 组提交（group commit）机制。

并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好

• 多个并发需要提交的事务共享一次fsync操作来进行数据的持久化
• 将fsync操作的开销平摊到多个并发的事务上去
• group commit 不是在任何时候都能发挥作用，要有足够多并发的需要提交的事务

实现

• 多个并发提交的事务在写redo log或binlog前会被加入到一个队列中
• 队列头部的事务所在的线程称为leader线程，其它事务所在的线程称为follower线程
• leader线程负责为队列中所有的事务进行写binlog操作，此时，所有的follower线程处于等待状态
• 然后leader线程调用一次fsync操作，将binlog持久化
• 最后通知follower线程可以继续往下执行

参数

binlog_group_commit_sync_delay=N

定时发车，在等待N 微秒后，进行binlog刷盘操作

binlog_group_commit_sync_no_delay_count=N

人满发车，达到最大事务等待数量，开始binlog刷盘，忽略定时发车

注意

• 当binlog_group_commit_sync_delay=0时，binlog_group_commit_sync_no_delay_count参数设置无效，即没有定时发车情况下，人满发车也就没有了~_~

• 当sync_binlog=0或sync_binlog=1，在刷盘前，对每个binlog应用定时发车
• 当sync_binlog=N（N>1），在每N个binlog后应用定时发车
• 设置了定时发车增加了并发提交事务的数量，从而增加slave并行apply的速度（slave开启多线程复制）
• 定时发车增加了事务提交的延迟，在高并发情况下，延迟有可能增加争用从而减少吞吐量

• 定时发车有优点也有缺点，要更具业务负载持续优化来决定最佳设置

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。

将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。

binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？

我们先来看一下崩溃恢复时的判断规则。如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。

redo log 和 binlog 是怎么关联起来的?

回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

非双 1”配置，是设置innodb_flush_logs_at_trx_commit=2、sync_binlog=1000。有哪些使用场景

业务高峰期。一般如果有预知的高峰期，DBA 会有预案，把主库设置成“非双 1”。备库延迟，为了让备库尽快赶上主库。

用备份恢复主库的副本，应用 binlog 的过程，这个跟上一种场景类似。

批量导入数据的时候。

21.主备同步？

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。

一个事务日志同步的完整过程是这样的：

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

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

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

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

sql_thread 读取中转日志，解析出日志里的命令，并执行。（sql_thread 演化成为了多个线程）

binlog怎么保持主备数据同步一致的？

（1）binlog_format=statement

在mysql的配置文件中my.cnf有相关配置

log文件中主要是执行的sql语句，所以在主库和备库选择不同索引的时候，可能导致数据不一致问题.

mysql> delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;--不同的索引会删除不同的数据

（2）binlog_format=‘row’

主要是记录使用的是哪个表，和操作的行为

查看binlog内容

show binlog events in 'master.000001';

借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容

mysqlbinlog  -vv data/master.000001 --start-position=8900; 

start-position 参数来指定从这个位置的日志开始解析。 可以从show binlog语句中查看语句开始的位置

（3）mixed binlog

为什么有混合型的binlog?

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。但很少使用

（4）数据恢复

row格式的binlog恢复数据也更简洁,因为日志中记录了变换前后的数据

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

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

 （5）循环复制

背景：主库和备库互为主备关系（双M）

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。

解决

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

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

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

还可能出现的问题？

一种场景是，在一个主库更新事务后，用命令 set global server_id=x 修改了 server_id。等日志再传回来的时候，发现 server_id 跟自己的 server_id 不同，就只能执行了。另一种场景是，有三个节点的时候，如图 7 所示，trx1 是在节点 B 执行的，因此 binlog 上的 server_id 就是 B，binlog 传给节点 A，然后 A 和 A’搭建了双 M 结构，就会出现循环复制。

如果出现了循环复制，可以在 A 或者 A’上，执行如下命令：

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;

过一段时间后再修改回来 

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;

（6）备库落后主库很多要怎么处理？

最好是换硬件,把备库的磁盘能力提上来,

可以考虑一下备库设置 innodb_flush_log_at_trx_commit 和 sync_binlog 为非双1 试试

（7）一主多从，某个从库中出现数据丢失？

一、
1.在业务低峰期，锁库，重新做主从，但是如果一天不能同步完的话，白天主库还要解锁。不会搞了……
又不像mongoDB那样不用记录pos值😂
2.减少主库io压力试下，比如分库分表

二、mysql主库高可用
mysql主从+keepalived/heatbeat
HMA/MMM
ZK
是我看配置视频，老师简单讲了一下，我在网上搜的

三、主库写压力过大
只能通过分库，分表

22.主备延迟？

1、主备延迟，就是在同一个事务在备库执行完成的时间和主库执行完成的时间之间的差值，包括主库事务执行完成时间和将binlog发送给备库，备库事务的执行完成时间的差值。每个事务的seconds_behind_master延迟时间，每个事务的 binlog 里面都有一个时间字段，用于记录主库上的写入时间，备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时的差值。
2、主备延迟的来源

①首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差，原因多个备库部署在同一台机器上，大量的查询会导致io资源的竞争，解决办法是配置”非双1“，redo log和binlog都只write fs page cache

②备库的压力大，产生的原因大量的查询操作在备库操作，耗费了大量的cpu，导致同步延迟，解决办法，使用一主多从，多个从减少备的查询压力；通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力（ canal 可以了解下）。

③大事务，因为如果一个大的事务的dml操作导致执行时间过长，将其事务binlog发送给备库，备库也需执行那么长时间，导致主备延迟，解决办法尽量减少大事务，比如delete操作，使用limit分批删除，可以防止大事务也可以减少锁的范围。
④大表的ddl，会导致主库将其ddl binlog发送给备库，备库解析中转日志，同步，后续的dml binlog发送过来，需等待ddl的mdl写锁释放，导致主备延迟。
3、主备切换可靠性优先策略，①判断备库 B 现在的 seconds_behind_master如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步，②把主库 A 改成只读状态，即把 readonly 设置为 true，③判断备库 B 的 seconds_behind_master的值，直到这个值变成 0 为止；4 把备库 B 改成可读写也就是把 readonly 设置为 false；5 把业务请求切换到备库；

个人理解如果发送过来的binlog在中转日志中有多个事务，业务不可用的时间，就是多个事务被运用的总时间。如果非正常情况下，主库掉电，会导致出现的问题，如果备库和主库的延迟时间短，在中转日志运用完成，业务才能正常使用，如果在中转日志还未运用完成，切换为备库会导致之前完成的事务，”数据丢失“，但是在一些业务场景下不可接受。
4、主备切换可用性策略

如果强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。

出现的问题：使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。使用使用 row 格式的 binlog 时，数据不一致的问题更容易发现，因为binlog row会记录字段的所有值。

5、按照可靠性优先的思路，异常切换会是什么效果？

假设，主库 A 和备库 B 间的主备延迟是 30 分钟，这时候主库 A 掉电了，HA 系统要切换 B 作为主库。我们在主动切换的时候，可以等到主备延迟小于 5 秒的时候再启动切换，但这时候已经别无选择了。

按照可靠性优先，主库 A 掉电后，我们的连接还没有切到备库 B。这时整个系统都是不可用的。

那能不能直接切换到备库 B，但是保持 B 只读呢？

这样也不行。因为，这段时间内，中转日志还没有应用完成，如果直接发起主备切换，客户端查询看不到之前执行完成的事务，会认为有“数据丢失”。虽然随着中转日志的继续应用，这些数据会恢复回来，但是对于一些业务来说，查询到“暂时丢失数据的状态”也是不能被接受的。

在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

主备延迟时，会导致备库在没有运用中转日志时，业务查询时导致”数据丢失“，那如何解决呢？

如果使用的是普通的异步复制模式，就可能会丢失，但 semi-sync 就可以解决这个问题。

6、主备同步延迟，工作中常遇到几种情况：
  1.主库做大量的dml操作，引起延迟
  2.主库有个大事务在处理，引起延迟
  3.对myisam存储引擎的表做dml操作，从库会有延迟。
  4.利用pt工具对主库的大表做字段新增、修改和添加索引等操作，从库会有延迟。

7、复制延迟排查思路：

评论中的，我感觉不全部对，思路可以

1、查数据库在干什么

pager cat - | grep -v Sleep | sort -rn -k 12 | head -n 20

show full processlist;

select * from information_schema.processlist where 1=1 order by TIME desc limit 10;

2、查看sql_thread在干什么

slave上查看状态：show slave status\G;

查看relay_master_log_file以及exec_master_log_pos

master上解析binglog日志：mysqlbinlog -v --base64-output=decode-rows --start-position=exec_master_log_pos relay_master_log_file

如果发现卡在操作某表上：

检查表结构

      没有索引：stop slave 可能会卡主，建议关闭mysql，启动后先加索引，然后start slave

      有索引：只能等，大事务需要做拆分，不要操作太多数据

大事务：M上session回话使用statement格式，使用语句级别的复制

3、查看MySQL状态

机器性能（CPU、IO等）：从库配置适当高一点，使用新硬件PCI-E或SSD设备

表结构: 设计要合理，必须有主键，主键要短小，为查询字段建索引

业务程序：适当使用缓存，减少数据库压力

分析MySQL进程并结合源码：perf top `pidof mysqld`

4、参数临时优化

主库开启group commit

从库开启writeset

从库设置sync_binlog=0 && innodb_flush_log_at_trx_commit=2

5、检查锁情况

show engine innodb status\G;

8.评论精选：

生产环境有一张表需要清理，该表大小140G。要保留最近一个月的数据，又不能按时间直接用detele删（全表扫描），本来想通过清空分区表删，但是分区表又是哈希的。。有没好的办法呢？

作者回复: 估计下一个月占多少比例，如果比较小就建新表，把数据导过去吧
如果一个月占比高的话，只能一点点删了。

时间字段有索引的话，每个分区按时间过滤出来删除

9.主备延迟为什么会长达一个小时？

当备库持续比主库慢的时候，备库就会越来越慢。

在5.6以前是不支持多线程的，5.6以后有多个线程了。将只有一个线程的 sql_thread，拆成多个线程。

拆分后，原来的线程就变成收取relaylog和分发日志的功能了。

分发：

按表分发：每个 worker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的表。hash 表的 key 是“库名. 表名”，value 是一个数字，表示队列中有多少个事务修改这个表

按行分发：解决热点表的并行复制问题。如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。每个 worker，分配一个 hash 表。只是要实现按行分发，这时候的 key，就必须是“库名 + 表名 + 唯一键的值”。

MariaDB 的并行复制策略：能够在同一组里提交的事务，一定不会修改同一行；主库上可以并行执行的事务，备库上也一定是可以并行执行的。

MySQL 5.6 版本的并行复制策略：按库并行

MySQL 5.7 版本的并行复制策略：由参数 slave-parallel-type 来控制并行复制策略：配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。不过，MySQL 5.7 这个策略，针对并行度做了优化。这个优化的思路也很有趣儿。

两阶段提交中，不用等到 commit 阶段，只要能够到达 redo log prepare 阶段，就表示事务已经通过锁冲突的检验了。

同时处于 prepare 状态的事务，在备库执行时是可以并行的；处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

MySQL 5.7.22 的并行复制策略：新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种。

COMMIT_ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略。

WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。当然为了唯一标识，这个 hash 值是通过“库名 + 表名 + 索引名 + 值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个 hash 值。

22.1. 主从切换

（1）基于位点的主备切换

需要估算位点，不够准备还可能出现错误。

（2）基于 GTID 的主备切换

• Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：

GTID=server_uuid:gno

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
• gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。
• GTID 模式的启动也很简单，我们只需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on 就可以了。
• GTID 有两种生成方式，而使用哪种方式取决于 session 变量 gtid_next 的值。如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。a. 记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;b. 把这个 GTID 加入本实例的 GTID 集合。如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next='current_gtid’指定为 current_gtid，那么就有两种可能：a. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；b. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加 1。

备库 B 要设置为新主库 A’的从库的语法如下：

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 

执行逻辑：

实例 B 指定主库 A’，基于主备协议建立连接。实例 B 把 set_b 发给主库 A’。

实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务。

a.  如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；

b.  如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；

之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

GTID 和在线 DDL

在双 M 结构下，备库执行的 DDL 语句也会传给主库，为了避免传回后对主库造成影响，要通过 set sql_log_bin=off 关掉 binlog。

这个做法可能会丢失一部分日志，所以使用gtid模式可以解决这个问题，即不用关掉日志，也能同步日志。

在实例 X 上执行 stop slave。在实例 Y 上执行 DDL 语句。注意，这里并不需要关闭 binlog。

执行完成后，查出这个 DDL 语句对应的 GTID，并记为 server_uuid_of_Y:gno。

到实例 X 上执行以下语句序列：

set GTID_NEXT="server_uuid_of_Y:gno";
begin;
commit;
set gtid_next=automatic;
start slave;

这样可以让实例 Y 的更新有 binlog 记录，同时也可以确保不会在实例 X 上执行这条更新。接下来，执行完主备切换，然后照着上述流程再执行一遍即可。

23.读写分离

一主多从做读写分离时，可能碰到过期读的原因，以及几种应对的方案。

1：单机的性能总是有限的，所以，就出现了读写分离
2：读写分离带来了更高的性能，也引入了数据不一致的问题
3：为了数据一致性，又产生了各种解决方案，即过期读解决方案

（1）强制走主库方案；

（2）sleep 方案；

（3）判断主备无延迟方案；

show slave status;

• seconds_behind_master 是否已经等于 0
• 对比位点确保主备无延迟：
• 对比 GTID 集合确保主备无延迟：

（4）配合 semi-sync 方案；

引入半同步复制，也就是 semi-sync replication。

• 事务提交的时候，主库把 binlog 发给从库；
• 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
• 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

如果主库掉电的时候，有些 binlog 还来不及发给从库，会不会导致系统数据丢失？答案是，如果使用的是普通的异步复制模式，就可能会丢失，但 semi-sync 就可以解决这个问题。

存在两个问题：

• 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
• 在持续延迟的情况下，可能出现过度等待的问题。

（5）等主库位点方案；

select master_pos_wait(file, pos[, timeout]);

解决（4）的延时问题，

先执行 trx1，再执行一个查询请求的逻辑，要保证能够查到正确的数据，我们可以使用这个逻辑：trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；选定一个从库执行查询语句；在从库上执行 select master_pos_wait(File, Position, 1)；如果返回值是 >=0 的正整数，则在这个从库执行查询语句；否则，到主库执行查询语句。

（6）等 GTID 方案。

 select wait_for_executed_gtid_set(gtid_set, 1);

等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；超时返回 1。

trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；选定一个从库执行查询语句；在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；如果返回值是 0，则在这个从库执行查询语句；否则，到主库执行查询语句。

4：如何判断主库出问题了？

在系统库（mysql 库）里创建一个表，比如命名为 health_check。

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话，那么备库也要进行更新检测。但，备库的检测也是要写 binlog 的。由于我们一般会把数据库 A 和 B 的主备关系设计为双 M 结构，所以在备库 B 上执行的检测命令，也要发回给主库 A。但是，如果主库 A 和备库 B 都用相同的更新命令，就可能出现行冲突，也就是可能会导致主备同步停止。所以，现在看来 mysql.health_check 这个表就不能只有一行数据了。

insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

每个库对应的serverId插入一条记录 。如果更新语句，如果失败或者超时，就说明除了问题，可以发起主备切换了。

第二种情况，数据库的响应特别慢.

增加检测 performance_schema 的信息。具体操作查看原文第29篇

5：怎么看死锁

在出现死锁后，执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。

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) 这一行的锁。当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。

“ 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) 这个记录锁。

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

评论：

主库大表的DDL操作，我看了问题答案，有两种方案。第一种是读写请求转到主库，在主库上做DDL。第二种是从库上做DDL，完成后进行主从切换。

作者回复: update 没索引就是锁住主键索引上所有的行和间隙
锁的内容太多了， 这样确实容易出现死锁哦。

24.误删数据库后怎么办？

如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

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

（1）预防：

把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。

代码上线前，必须经过 SQL 审计。

对生产数据库操作，公司DBA提出的编写脚本方法，个人觉得还是值得分享，虽说可能大部分公司也可能有这样的规范。
修改生产的数据，或者添加索引优化，都要先写好四个脚本：备份脚本、执行脚本、验证脚本和回滚脚本。备份脚本是对需要变更的数据备份到一张表中，固定需要操作的数据行，以便误操作或业务要求进行回滚；执行脚本就是对数据变更的脚本，为防Update错数据，一般连备份表进行Update操作；验证脚本是验证数据变更或影响行数是否达到预期要求效果；回滚脚本就是将数据回滚到修改前的状态。
虽说分四步骤写脚本可能会比较繁琐，但是这能够很大程度避免数据误操作。

1.权限控制与分配(数据库和服务器权限)
2.制作操作规范
3.定期给开发进行培训
4.搭建延迟备库
5.做好sql审计,只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核
6.做好备份。备份的话又分为两个点.
(1)如果数据量比较大,用物理备份xtrabackup。定期对数据库进行全量备份,也可以做增量备份。
(2)如果数据量较少,用mysqldump或者mysqldumper。再利用binlog来恢复或者搭建主从的方式来恢复数据。
定期备份binlog文件也是很有必要的
还需要定期检查备份文件是否可用,如果真的发生了误操作,需要恢复数据的时候,发生备份文件不可用,那就更悲剧了

（2）如果发生了数据删除的操作,又可以从以下几个点来恢复:
1.DML误操作语句造成数据不完整或者丢失。可以通过flashback,不过我们目前用的是美团的myflash,也是一个不错的工具，本质都差不多.都是先解析binlog event,然后在进行反转。把delete反转为insert,insert反转为delete,update前后image对调。所以必须设置binlog_format=row 和 binlog_row_image=full.
切记恢复数据的时候,应该先恢复到临时的实例,然后在恢复回主库上。
2.DDL语句误操作(truncate和drop),由于DDL语句不管binlog_format是row还是statement.在binlog里都只记录语句,不记录image所以恢复起来相对要麻烦得多。只能通过全量备份+应用binlog的方式来恢复数据。一旦数据量比较大,那么恢复时间就特别长,
对业务是个考验。所以就涉及到老师在第二讲提到的问题了，全量备份的周期怎么去选择。

误删数据：flashback解析binlong恢复

误删表、库：

方法一：定期全量备份，实时备份binlog

流程：

取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；

用备份恢复出一个临时库；

从日志备份里面，取出凌晨 0 点之后的日志；

把这些日志，除了误删除数据的语句外，全部应用到临时库。

加速流程：

（1）使用 mysqlbinlog 命令时，加上一个–database 参数，用来指定误删表所在的库。这样，就避免了在恢复数据时还要应用其他库日志的情况。

在应用日志的时候，需要跳过 12 点误操作的那个语句的 binlog：如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用–stop-position 参数执行到误操作之前的日志，然后再用–start-position 从误操作之后的日志继续执行；如果实例使用了 GTID 模式，就方便多了。假设误操作命令的 GTID 是 gtid1，那么只需要执行 set gtid_next=gtid1;begin;commit; 先把这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时候，就会自动跳过误操作的语句。

（2）一种加速的方法是，在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，这样：在 start slave 之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；这样做也可以用上并行复制技术，来加速整个数据恢复过程。

方法二：搭建延迟复制的备库

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

24.1使用了 kill 命令，却没能断开这个连接。这条语句的 Command 列显示的是 Killed？说明线程还在等待中，并没有被终止

（1）kill命令种类：

kill query + 线程 id，表示终止这个线程中正在执行的语句；

一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

（2）kill无效的两种情况：

第一种：线程没有执行到判断线程状态的逻辑，解决方式是让这个线程获得资源继续执行，就会执行到判断线程状态的逻辑（比如时调大 innodb_thread_concurrency 的值，或者停掉别的线程，让出位子给这个线程执行）

第二种：终止逻辑耗时过长，比如大事务，大查询产生了较大的临时文件，解决方式是由于受到 IO 资源限制执行得比较慢，就通过减少系统压力让它加速

kill connection本质上只是把客户端的sql连接断开，后面的执行流程还是要走kill query的，额外的一个不同就是show processlist的时候，kill connection会显示“killed”。

（3）用pstack保留个现场

pstack <pid of mysqld> > /tmp/pstack.1

（4）如果一个事务被 kill 之后，持续处于回滚状态，从恢复速度的角度看，你是应该重启等它执行结束，还是应该强行重启整个 MySQL 进程？

因为重启之后该做的回滚动作还是不能少的，所以从恢复速度的角度来说，应该让它自己结束。

当然，如果这个语句可能会占用别的锁，或者由于占用 IO 资源过多，从而影响到了别的语句执行的话，就需要先做主备切换，切到新主库提供服务。

切换之后别的线程都断开了连接，自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的，减少系统压力，加速终止逻辑。

25.查询很多数据会不会把内存打爆？（33）

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

• 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
• 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
• 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
• 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

由于 MySQL 采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。

如果客户端使用–quick 参数，会使用 mysql_use_result 方法。这个方法是读一行处理一行

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

全表扫描对 InnoDB 的影响

我们要扫描一个 200G 的表，而这个表是一个历史数据表，平时没有业务访问它。那么，按照这个算法扫描的话，就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。

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

扫描过程中，需要新插入的数据页，都被放到 old 区域 ;一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域；再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。

评论精华：

（1）之前在开发工程中实际有碰到这样的业务，批量从MySQL中查询大量数据，每次通过限制起始+limit数量的来分批次查询，后来有同事推荐使用MySQL JDBC中的fetchSize()方法，不做分页通过一次大查询然后客户端流式读取来批量查询数据，这个内部原理是否就是文中所说的使用了mysql_use_result接口读一行处理一行实现的流式？或者也是mysql_store_result方式客户端边缓存边处理？请老师指教

作者回复: 对，这种一般就是用mysql_use_result
各有优劣吧
一次性取的好处是，对服务端只全表，只扫描一遍；坏处是可能会出现大事务。
一般更常见的做法是，分批取，然后每一批拿到最大的一个id（主键值）
下一批查询的时候用 where Id > N 这种写法

（2）Sending to client 状态，到底是体现了服务端的网络栈 socket receive buffer 写满了（客户端未及时读取），还是 net_buffer 满了，还有查询结果没有写入到 net_buffer 中？

网络栈 socket receive buffer 写满了

（3）改进后的 LRU 算法的思路有点像JVM GC里面分代回收的思想，把数据分成新生代和老年代，一个用于存储短时间内就会被清理的对象，一个用于存储存活时间长的对象。不过有趣的是对于两种区域的叫法，刚好相反：JVM里面把短时间内被清除的区域叫做”young“，InnoDB里面却是叫做”old"。

作者回复: 👍

被你一说我赶紧再去翻了下代码，嗯嗯，InnoDB确实是最近访问的叫做young 😆

（4）最近系统出现了由于查询大量数据而导致服务不可用的情况，当时第一反应是数据库内存打满了，后来发现数据库正常，应用服务因为OOM挂掉了，虽然这个问题解决了，但一直担心如果应用服务并发这样的查询语句，MySQL是否也会OOM。所以说今天这篇文章真是太及时了。
一直有一个疑问，我个人理解用户进程挂掉的根本原因几乎都是来自内存（访问非法地址或者OOM等）。既然MySQL在查询大量数据时不会导致OOM，在连接数和内存大小方面如果参数设置合理应该也不会导致OOM，那还有什么情况可以使MySQL挂掉？

作者回复: 一般我们说“MySQL挂掉”，大多数情况下就是响应慢了；

如果说重启的话， 有一种是InnoDB 读 io迟迟不返回，会自己重启；
还有是innodb_buffer_pool_size 设置太大，再加上server层使用的内存，导致内存超过系统上限被oom。我们说一个大查询不会打爆，但是如果很多并发查询，还是可能的。

（5）作为一个java后端开发人员有个疑问，如果对一个大表进行select，如：select * from big_table;
用一个List<Object>来接，那么应该是把数据都一次性读入内存了吧，是和上面的全表扫描边读边写的情况不同的吧？也就是我所说的这种操作是可能导致OOM的是吗

作者回复: 嗯，这种情况下是可能导致你的客户端应用占用内存过多的。

26. 到底可不可以使用join？

（1）Index Nested-Loop Join（NLJ）

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

这个过程是先遍历表 t1(驱动表)，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2(被驱动表) 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ。 

在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N + N*2*log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

(2)Simple Nested-Loop Join()

驱动表上没有索引的时候走的是全表扫描，这个算法比较笨重，因此mysql没有使用，而使用了下面这个算法

（3）Block Nested-Loop Join（BNL）

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

把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次。

如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

结论：

a.基于以上两个算法可以得到结论

当被驱动表可以使用索引时，可以使用join语句，否则不建议使用join语句（因为在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源）

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

b.大表驱动还是小表驱动

如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

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

27. join优化？

背景：

select * from t1 where a>=1 and a<=100;

Multi-Range Read 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。

这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

如何启用mrr?

如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"

（1）NLJ优化

Batched Key Access(BKA) 算法.BKA 算法的优化要依赖于 MRR。

启用该算法

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。

（2）BNL算法优化

BNL 算法对系统的影响主要包括三个方面：可能会多次扫描被驱动表，占用磁盘 IO 资源；判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

在表 t2 中插入了 100 万行数据，但是经过 where 条件过滤后，需要参与 join 的只有 2000 行数据。如果这条语句同时是一个低频的 SQL 语句，那么再为这个语句在表 t2 的字段 b 上创建一个索引就很浪费了。

在表 t2 的字段 b 上创建索引会浪费资源，但是不创建索引的话这个语句的等值条件要判断 10 亿次，想想也是浪费。那么，有没有两全其美的办法呢？

这时候，我们可以考虑使用临时表。使用临时表的大致思路是：把表 t2 中满足条件的数据放在临时表 tmp_t 中；为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；让表 t1 和 tmp_t 做 join 操作。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

结论：在原表上加索引，或者使用有索引的临时表。我们的思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。

不使用临时表的方案？

我们可以自己实现在业务端。实现流程大致如下：

select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。

select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。

把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

理论上，这个过程会比临时表方案的执行速度还要快一些。

28.临时表

（1）临时表和内存表的区别

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

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

（2）临时表的特点

建表语法是 create temporary table …。

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

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

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

（3）临时表为什么适合在join优化中使用

• 不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。

• 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

（4）临时表的应用

分库分表系统的跨库查询

把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。

• 在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；

• 在各个分库上执行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

• 把分库执行的结果插入到 temp_ht 表中；执行select v from temp_ht order by t_modified desc limit 100;

（5）alter table 语法修改临时表的表名，而不能使用 rename 语法。

临时表可以重名，实际的存储文件名有线程id，在内存中表的命名有table_ref_key，是由库名加表名加serverid+线程id组成。bin log设置为row模式，临时表不会同步到备库中，设置为statement模式，会同步到备库中。

（6）评论精选

不同线程可以使用同名的临时表，这个没有问题。但是如果在程序中，用的是连接池中的连接来操作的，而这些连接不会释放，和数据库保持长连接。这样使用临时表会有问题吗?

 会，“临时表会自动回收”这个功能，主要用于“应用程序异常断开、MySQL异常重启”后，不需要主动去删除表。

而平时正常使用的时候，用完手动删除，还是应该有的好习惯。

如果A客户端在执行过程中创建了临时表，用完了连接就放回池子里面，没有做别的清理工作，然后新的客户端B复用这个连接，就可能会看到A的临时表

我们在排序的时候用到了 sort buffer，在使用 join 语句的时候用到了 join buffer。然后，你可能会有这样的疑问，MySQL 什么时候会使用内部临时表呢？

29.内存临时表

 using temporary。

使用场景

（1）union

此时，内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键 id 的唯一性约束，实现了 union 的语义（去重）。

如果把上面这个语句中的 union 改成 union all 的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

（2）group by

• 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；（group by 的返回结果默认是按照group by的字段排序过的）
• 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
• 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
• 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

（3）MySQL 什么时候会使用内部临时表？

如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。distinct 也是唯一约束

（4）group by 优化

不使用临时表

group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。那么，如果扫描过程中可以保证出现的数据是有序的，是不是就简单了呢？如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。创建InnoDB 的索引，就可以满足这个输入有序的条件。

如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。那么，这时候的 group by 要怎么优化呢？

一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。那么，我们就会想了，MySQL 有没有让我们直接走磁盘临时表的方法呢？答案是，有的。在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

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

30.什么时候使用memory引擎？

（1）内存表的数据结构

Memory 引擎的数据和索引是分开的。内存表的数据部分以数组的方式单独存放，而主键 id 索引里，存的是每个数据的位置。主键 id 是 hash 索引，可以看到索引上的 key 并不是有序的。

（2）内存表是hash索引结构，所以不支持范围查询，如果想要支持范围查询应该要怎么办呢？

内存表也是支 B-Tree 索引的。在 id 列上创建一个 B-Tree 索引，SQL 语句可以这么写：

alter table t1 add index a_btree_index using btree (id);

（3）为什么不建议使用memory引擎

a.锁粒度问题：表级锁，并发性能不是很好；

b.数据持久化问题：数据存储在内存中，数据库重启的时候，数据会丢失。

业务正常访问主库；备库硬件升级，备库重启，内存表 t1 内容被清空；备库重启后，客户端发送一条 update 语句，修改表 t1 的数据行，这时备库应用线程就会报错“找不到要更新的行”。

这样就会导致主备同步停止。当然，如果这时候发生主备切换的话，客户端会看到，表 t1 的数据“丢失”了。

我们线上就有一个因为内存表导致的主从同步异常的例子,我的做法是先跳过这个表的同步,然后开发进行改造,取消这张表的作用

作者回复: 嗯嗯，联系开发改造是对的

31.自增主键如何使用？

（1）自增主键保存在哪里？

MyISAM 引擎的自增值保存在数据文件中。

InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：

• 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
• 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

（2）自增主键的作用？保存机制？修改机制？
作用：让主键索引尽量地保持递增顺序插入，避免页分裂，使索引更紧凑。
保存机制：不同的存储引擎不一样。
MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值，先是保存在了内存里，到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，放在了redolog里。
修改机制：
在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：
1：如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
2：如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。
1：如果 X<Y，那么这个表的自增值不变；
2：如果 X≥Y，就需要把当前自增值修改为新的自增值。

（3）自增主键什么时候是不连续的？

1：唯一键冲突，冲突后自增主键没有改回去
2：事务回滚
3：自增主键的批量申请，每次按照原来的两倍申请，一次申请的多了，没有用，下次也不能使用，直接重新申请了
深层次原因是，不判断自增主键是否已存在和减少加锁的时间范围和粒度->为了更高的性能->自增主键不能回退->自增主键不连续

32.两个表之间拷贝数据

insert … select 是很常见的在两个表之间拷贝数据的方法。

你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁（只锁住需要访问的资源）。

而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。（这类语句的执行为什么需要临时表，原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。如果数据较少，可以考虑使用内存临时表来做这个优化）

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)（发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。）。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

作者的方法：

（1）一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句。 

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

–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 语句放到 db2 库里去执行。

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

（2）另一种方法是直接将结果导出成.csv 文件。MySQL 提供了下面的语法，用来将查询结果导出到服务端本地目录：

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

得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。

load data infile '/server_tmp/t.csv' into table db2.t; 

select …into outfile 方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。mysqldump 提供了一个–tab 参数，可以同时导出表结构定义文件和 csv 数据文件。这条命令的使用方法如下：

mysqldump -h$host -P$port -u$user ---single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

这条命令会在 $secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。 

（3）物理拷贝方法

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：执行 create table r like t，创建一个相同表结构的空表；执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；执行 unlock tables，这时候 t.cfg 文件会被删除；执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

我们来对比一下这三种方法的优缺点。物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：必须是全表拷贝，不能只拷贝部分数据；需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。

用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。

用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。后两种方式都是逻辑备份方式，是可以跨引擎使用的。

评论精选

1 关于insert造成死锁的情况,我之前做过测试,事务1并非只有insert,delete和update都可能造成死锁问题,核心还是插入唯一值冲突导致的.我们线上的处理办法是 1 去掉唯一值检测 2减少重复值的插入 3降低并发线程数量
2 关于数据拷贝大表我建议采用pt-archiver,这个工具能自动控制频率和速度,效果很不错,提议在低峰期进行数据操作

我用的最多还是insert into select 。如果数量比较大，会加上limit 100,000这种。并且看看后面的select条件是否走索引。缺点是会锁select的表。方法二：导出成excel，然后拼sql 成 insert into values(),(),()的形式。方法3，写类似淘宝调动的定时任务，任务的逻辑是查询100条记录，然后多个线程分到几个任务执行，比如是个线程，每个线程10条记录，插入后，在查询新的100条记录处理。
 补充应用表空间迁移的场景
1 冷数据表的复制和迁移
2 大表数据的恢复,线上DDL操作失误,需要恢复时,利用备份+binlog进行恢复后,表空间迁移进行导入
对于热表数据的复制建议还是采用pt-archiver慢慢搞

33. grant之后要跟着flush privileges吗？

（1）全局权限，作用于整个 MySQL 实例，这些权限信息保存在 mysql 库的 user 表里。如果我要给用户 ua 赋一个最高权限的话，语句是这么写的：

grant all privileges on *.* to 'ua'@'%' with grant option;

磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；

内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。 

在这个 grant 命令执行完成后，如果有新的客户端使用用户名 ua 登录成功，MySQL 会为新连接维护一个线程对象，然后从 acl_users 数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位。

如果要回收上面的 grant 语句赋予的权限，你可以使用下面这条命令：

 revoke all privileges on *.* from 'ua'@'%';

grant 命令对于全局权限，同时更新了磁盘和内存。命令完成后即时生效，接下来新创建的连接会使用新的权限。对于一个已经存在的连接，它的全局权限不受 grant 命令的影响。

 （2）库权限

grant all privileges on db1.* to 'ua'@'%' with grant option;

让用户 ua 拥有库 db1 的所有权限

基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs 中。这条 grant 命令做了如下两个动作：磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。

每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断。也就是说，grant 修改 db 权限的时候，是同时对磁盘和内存生效的。

特殊情况：

如果当前会话已经处于某一个 db 里面，之前 use 这个库的时候拿到的库权限会保存在会话变量中。

如果session C 在 某 时刻执行的 use db1，拿到了这个库的权限，在切换出 db1 库之前，session C 对这个库就一直有权限。

（3）表权限和列权限

表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。

这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中。

create table db1.t1(id int, a int);

grant all privileges on db1.t1 to 'ua'@'%' with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接。

（4）flush privileges使用场景

flush privileges 命令会清空 acl_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl_users 数组。

也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。

同样地，对于 db 权限、表权限和列权限，MySQL 也做了这样的处理。

也就是说，如果内存的权限数据和磁盘数据表相同的话，不需要执行 flush privileges。而如果我们都是用 grant/revoke 语句来执行的话，内存和数据表本来就是保持同步更新的。

因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。

显然，当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。这种不一致往往是由不规范的操作导致的，

比如直接用 DML 语句操作系统权限表。比如直接删除系统用户：delete 语句删除了用户 ua，这样会导致系统表和内存中的数据不一致，出现查询错误等问题。

34.分区表

（1）概念

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

这个表包含了一个.frm 文件和 4 个.ibd 文件，每个分区对应一个.ibd 文件。也就是说：对于引擎层来说，这是 4 个表；对于 Server 层来说，这是 1 个表。

（2）优劣

分区表和手工分表区别，一个是由 server 层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。

引擎层：每个分区单独加锁，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

server层：当第一次访问一个分区表的时候，MySQL 需要把所有的分区都访问一遍。果一个分区表的分区很多，比如超过了 1000 个，而 MySQL 启动的时候，open_files_limit 参数使用的是默认值 1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错（使用 InnoDB 引擎的话，并不会出现这个问题）。

在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；

（3）使用场景

分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。

如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过 alter table t drop partition …这个语法删掉分区，从而删掉过期的历史数据。这个 alter table t drop partition …操作是直接删除分区文件，效果跟 drop 普通表类似。与使用 delete 语句删除数据相比，优势是速度快、对系统影响小。

（4）注意

分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。

35.答疑

（1）

join语义

“找到这两个表里面，f1、f2 对应相同的行。对于表 a 中存在，而表 b 中匹配不到的行，就放弃”。

left join 语义

• 把表 a 的内容读入 join_buffer 中。因为是 select * ，所以字段 f1 和 f2 都被放入 join_buffer 了。
• 顺序扫描表 b，对于每一行数据，判断 join 条件（也就是 (a.f1=b.f1) and (a.f1=1)）是否满足，满足条件的记录, 作为结果集的一行返回。
• 如果语句中有 where 子句，需要先判断 where 部分满足条件后，再返回。
• 表 b 扫描完成后，对于没有被匹配的表 a 的行（在这个例子中就是 (1,1)、(2,2) 这两行），把剩余字段补上 NULL，再放入结果集中。

如果用 left join 的话，左边的表一定是驱动表吗？不是的

select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

上面这条语句虽然用的是 left join，但是语义跟 join 是一致的（Q4）。而且这条语句的驱动表是b,（因为表a中有索引f1表b没有索引，所以优化器进行了优化）

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/

select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/

这两个语句的执行是一样的，优化器都该写成以下语句
select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

（2）“在线服务最好不要让索引树超过4层“；那么有没有什么好的方法或者工具可以查看当前表的层数吗？

 innblock 可以了解下

（3）学习思路

实践是很好的学习方式，所以我会让新人来了以后先搭主备，然后你就会发现每个人的自学能力都不一样。比如遇到有延迟，或者我们故意构造一个主备数据不一致的场景，让新人了解怎么分析问题，解决问题。

36.自增id

(1)表定义自增值 id

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。下次插入是就会报主键重复的错（Duplicate entry '4294967295' for key 'PRIMARY'）

在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 bigint unsigned。

（2）InnoDB 系统自增 row_id

row_id 留的只是 6 个字节的长度。

写入表的 row_id 是从 0 开始到 248-1。达到上限后，下一个值就是 0，然后继续循环。如果表中已经存在 row_id=N 的行，新写入的行就会覆盖原有的行。

从这个角度看，我们还是应该在 InnoDB 表中主动创建自增主键。因为，表自增 id 到达上限后，再插入数据时报主键冲突错误，是更能被接受的。毕竟覆盖数据，就意味着数据丢失，影响的是数据可靠性；报主键冲突，是插入失败，影响的是可用性。而一般情况下，可靠性优先于可用性。

（3）Xid

redo log 和 binlog 相配合的时候，提到了它们有一个共同的字段叫作 Xid。它在 MySQL 中是用来对应事务的。

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。

但是如果 global_query_id 达到上限后，就会继续从 0 开始计数。从理论上讲，还是就会出现同一个 binlog 里面出现相同 Xid 的场景。因为 global_query_id 定义的长度是 8 个字节，这个自增值的上限是 2^64-1。一般还是很难达到的。

（4）Innodb 中的trx_id

Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。

InnoDB 内部维护了一个 max_trx_id 全局变量，每次需要申请一个新的 trx_id 时，就获得 max_trx_id 的当前值，然后并将 max_trx_id 加 1。

InnoDB 数据可见性的核心思想是：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。对于正在执行的事务，你可以从 information_schema.innodb_trx 表中看到事务的 trx_id。

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