01 基本架构：一条SQL查询语句是如何执行的？

• Mysql 可以分成 Server 层 和存储引擎 两个方面

• Server层包括连接器、查询缓存、分析器、优化器、执行器等

• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，需要在建表的时候，指定你需要的存储引擎，默认是innodb

1 连接器

• 有些时候MySQL占用内存涨得特别快，这是因为 MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

• 可以采用 showprocesslist 查看连接时间，和空闲连接（连接后没有后续动作，连接就处于空闲状态）

• 客户端长时间没有动作，8个小时后，连接器会自动断开

长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

如何解决：

2 查询缓存

• 通过Key-value 进行存储

• 如果查询到了，就直接返回

• 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。不建议使用

• 对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。 比如，一个系统配置表，那这张表上的查询才适合使用查询缓存

3 分析器

• 语法分析:会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法检查语法 是否出现错误

• 词法分析：分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识 别出里面的字符串分别是什么，代表什么。

---

MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别 成“表名T”，把字符串“ID”识别成“列ID”。

---

语法分析：根据词法分析的结果，语法分析器会根据语法规则， 判断你输入的这个SQL语句是否满足MySQL语法。

4 优化器

• 决定使用哪个索引

• 决定表如何 join,哪个表用来join，哪个表用来被连接

• 决定执行的方案

5 执行器

• 首先判断权限

• 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

• 你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的

思考题

• 如果表T中没有字段k，而你执行了这个语句 select *fromTwhere k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

• 答案： 分析器阶段

02 日志系统： 一条SQL更新语句是如何执行的

WAL技术 : 先写日志在写磁盘

• 前文回顾

1 redo log

• 两个位置信息
  • checkpoint： 数据刷盘的位置
  • write pos： 当前记录的位置，写到 3 号继续返回到 0 号文件写
• InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录4GB的操作

---

作用

• redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候， 表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证 MySQL异常重启之后数据不丢失

---

redo log 设置过小的问题：

• 如果redolog 设置过小，说明需要一直去推进checkpoint， 说明需要一直刷盘，产生大量的随机写，影响效率

2 binlog

• 属于 Server 层的日记，所以只要采用 mysql 都有bin log

• sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建 议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

• binlog 主要用于归档和复制

3 两种日志的区别：

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

4 更新操作的过程

• 这里存在两阶段提交，先写 redolog 到 prepare 阶段，再写 binlog 最后再把 redolog 改为commit

5 如何实现回档操作

需要存在完整的 Binlog

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备 份恢复到临时库； 然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时 刻

思考题

定期全量备份的周期“取决于系统重要性，有 的是一天一备，有的是一周一备”。那么在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？

在一天一备的模式里，最坏情况下需要应用一天的binlog。比如，你每天0点做一次全量备份， 而要恢复出一个到昨天晚上23点的备份。

一周一备最坏情况就要应用一周的binlog了

03 事务隔离：为什么你改了我还看不见？

1 隔离性和隔离级别

• ACID:即原子性、一致性、隔离性、持久性

• 隔离级别

• MVCC 需要配合 undoLog 使用，指针所指向的是上一个版本的数据，而后根据你的事务版本号和数据版本号进行判断数据是否需要显示

2 长事务的缺点

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

3 事务的启动方式

• 显示的启动事务 begin 或者 start transaction 配合 commit 或者是 rollback
• 通过set autocommit = 0，关闭自动提交，直到你主动去 commit 或者 rollback

思考题

如何避免长事务的出现？

1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的 general_log 开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架 如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。

2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我 见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务 可以去掉。

3. 业务连接数据库的时候，根据业务本身的预估，通过SETMAX_EXECUTION_TIME命令， 来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后 续的文章中会提到这类案例）

4. 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；

5. Percona的pt-kill这个工具不错，推荐使用；

6. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；

7. 如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

04 | 深入浅出索引（上）

1 常见的索引模型

hash

哈希表这种结构适用于只有等值查询的场景

不能进行范围查找，模糊查询

有序数组

有序数组索引只适用于静态存储引擎，插入删除这类操作非常复杂

B树

无法实现范围查询，

树的非叶子节点上面存在的是节点的整个数据信息，需要多次IO

2 InnoDB 的索引模型的

B+ 树

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

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

非主键索引在没索引覆盖的情况下需要回表

---

索引维护

• 插入和删除数据的时候，需要再所有有索引的地方进行插入删除操作

为什么需要重建索引

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

思考题

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

05 | 深入浅出索引（下）

1 索引覆盖

出现 人的名字和身份证同时查询的时候

2 最左前缀原则

3 索引下推

• 简单来说，索引下推就是，再索引层次先进行 一次筛选，然后再进行回表
• 如果存在联合索引 （name ，age），需要查询 名字第一个是 张， 并且年龄小于10岁的
• 如果没有索引下推，会再查询到 第一个名字是张之后，将所有的信息进行回表去判断年龄小于10岁的
• 如果采用索引下推，会再索引一层继续判断，年龄小于10岁的 再进行回表（减少回表的次数）

---

• 无下推

• 索引下推

思考题

• 答案
• c-a 索引：才能让第一条语句走完C索引后，直接根据a索引信息，返回第一条
• c-b 索引：才能让第二条语句走完C索引，直接根据b索引信息，返回第一条

06|全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

• Mysql 里面的所可以分成三类 全局锁、表锁、 行锁

1 全局锁

• 用于全局的逻辑备份
• Flush tables with read lock (FTWRL)，让整个库都处于只读状态 官方自带的备份工具 mysqldump （需要支持MVCC，才能使用）

2 表锁

表锁

lock tables xxx read/write

元数据锁（MDL）

• 不需要显示指定，会自动加上
• 目的：MDL的作用是维护数据的一致性
• 对表做增删改查操作，加MDL读锁，对表结构变更的时候加MDL写锁

给小表添加字段可能导致库宕机

A –》 加上了一个 MDL 读锁

B -》 加上了一个MDL 读锁

C -》 MDL 写锁无法添加，被阻塞

D -》 由于前面由 MDL 写锁，所以后面的查询语句将全被被阻塞

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

如何安全添加字段

• 在alter table语句里面 设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后 面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程

意向锁

• 意向共享锁，事务想要给数据库某些行加共享锁，需要先加上意向共享锁
• 意向互斥锁，事务想要给数据库某些行加互斥锁，需要先加上意向互斥锁
• 意向锁的目的：当需要对表申请表级锁的时候，需要判断内部是否存在行锁，需要遍历全部数据，太慢了，有了意向锁之后，事物A对表的某条记录取得行级别的X锁之前会先取得表级别的IX锁，然后再取得X锁

07 | 行锁功过：怎么减少行锁对性能的影响？

• 注意：行锁是再事务需要的时候进行添加，再事务结束的时候才进行释放，所以，如果你的事务中需要获得多个锁，可以将粒度较小的锁放在前面，粒度较大的锁放在后面。

死锁和死锁检测

• 死锁检测的特征: CPU压力大，但是执行的事务却非常少
• 假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务
• 解决方案：
  • 直接关闭死锁检测：确定不会死锁的情况下
  • 控制并发度
  • 将数据进行拆分（更新1个数据，变成更新10个数据，10个数据的和是1个数据）

思考题

• 删除表的前面 10000 行，哪个方案友好
  • 第一种方案，直接删除delete fromTlimit 10000
  • 第二种方案，在 20 个连接中执行 执行delete fromTlimit 500
  • 第三种方案，在 一个连接中 执行 20次 delete fromTlimit 500
• 答案：
  • 第一个方案锁住了 前面 10000条数据
  • 第二个方案会形成锁冲突
  • 第三个方案最好，锁的粒度最小，每一次就锁一点，然后执行了就释放

08|事务到底是隔离的还是不隔离的？

1、MVCC

• 图中虚线框里是同一行数据的4个版本，当前最新版本是V4，k的值是22，它是被transaction id为25的事务更新的，因此它的rowtrx_id也是25，三个箭头就是UndoLog

对于MVCC可见性的判断，可以快速判断

• 首次发起select 的时候才进行视图的创建

• select 之前 已经提交的事务，都是可见的

• select 之后，提交的事务都是不可见的

• 对于自身事务的修改是可见的

• 例子

1. 如果最开始创建时候并没有创建视图，仅仅是start transaction按照顺序来说

• 对于事务A 来说，开始事务的时候并没创建视图
• 对于事务B 来说，开始事务的时候并没有创建事务
• k 初始值是 1 ，所以C进行了更新 k = 2
• 事务 B 进行了更新，同时查询但是并没有提交，但是事务B 得到的 k = 3
• 事务 A 进行了查询，创建了事务，这时候可以读取到 C 的事务，所以 k =2

1. 如果按照上图，创建事务的时候，进行了视图的创建

• 对于事务A 来说，创建了一个视图
• 对于事务B 来说，创建了一个视图
• 对于事件C 来说，提交了事务 K = 2
• 对于事件B 来说，又进行了一次更新，对于自身事务的修改是可见的 所以 K =3 ;
• 对于事件A 来说，创建视图的时候，k=1，所以再次查询还是1

记住两个准则两个准则

1. select的时候才创建视图，视图创建前面所有提交的事务都可以看见
2. 自己提交的事务自己可以看见

09|普通索引和唯一索引

1、查询过程性能差距不大

• 唯一索引找到一个就可以返回
• 普通索引需要找到第一个不满足的才能返回
• 从性能上来说，两者的差距不大，由于InnoDB中每个数据页的默认大小是 16KB，最多就是在下一个节点，多一次指针寻找

2、更新过程性能差距较大

• 唯一索引，必须将数据拉到内存中（buffer pool中），进行更新操作
• 普通索引，可以将更新数据的操作写道 change buffer 中，而后直接返回，等待操作系统刷新 changebuffer 中的数据（merge操作）

---

• change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示changebuffer的大小最多只能占用buffer pool的50%。

3、changebuffer 的性能分析

• 对于 写多读少的业务，如果写完之后该数据也立刻发起查询，那么会走磁盘拉数据到内存，通过changebuffer进行更新，这样子性能较低
• 如果并没有立刻查询，性能会比较高，会减少系统 IO 的次数，特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的

4、change buffer 和 redolog

• 如果 数据 page1在内存中，直接更新内存，写入redolog
• 如果 数据 page2不在内存中，在内存的 changebuffer 区域，写入 “我要向 page2 进行更新”，而后将 changge buffer 中的内容同样计入到 redolog 中

10| Mysql 索引分析

1、索引的选择逻辑

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

2、扫描函数如何判断

• MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

• 这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）

• 我们可以使用showindex方法，看到一个索引的基数。

• Mysql 是如何得到基数的？

  • 抽样统计，随机选取N 个数据页，统计这些数据页上的不同值，得到一个平均值，最后乘以数据也的个数，得到这个索引的基数

• 如果统计的信息差距较大，可以通过analyze进行重新刷新

3、索引选择异常和处理

1、强制选择某个索引 force index

2、尝试用analyze table 刷新索引的统计信息

3、重新建立一个索引，删除之前错误的索引

11| 如何给一个字符串字段加索引

• 总结

  • 直接创建完整的索引，比较占用空间，但是在一些情况下可以不需要回表

  • 创建前缀索引，节约空间，但是会增加查询的次数

  • 统计不同前缀的下面的区分度，进行选择

  • 倒序创建索引，节省空间，但是会失去范围查询

• 总的来说，需要根据你的业务需求进行 字符串字段索引的创建

思考题

12|MySQL 的瞬间卡顿

• mysql 卡顿无非从 3 个方面进行分析

  • 内存

    • 内存跑满的时候，发生了刷页（脏页需要刷新到磁盘中），可能会出现卡顿

    • innoDB 采用缓冲池进行内存管理，缓冲池中的内存页面存在三种

      • 没有使用的 可以直接覆盖

      • 使用了，但是是赶紧的，可以直接覆盖

      • 使用了的脏页，需要刷盘（这时候有可能发生卡顿）

  • 磁盘IO

    • 和磁盘有关的操作大概有三种

      • 日志的顺序写：一般不会出现什么问题，性能较好

      • 磁盘的随机读取：走的是索引，一般也比较快，不会出现什么问题

      • 内存刷盘，随机写：这个影响非常大，需要把内存中的脏页刷回磁盘中，IO开销很大

  • CPU

    • CPU 跑满的情况

      • 死锁检测，这时候情况是cpu占用率非常高，但是执行的事件数目却很少

      • 一些复杂的操作，比如 join、group by，这时候不会是瞬间的卡顿，而是较长时间的卡顿，同时磁盘的io也会比较大

• 总结

  • 总的来说，瞬时的卡顿主要会出现在 系统内存刷脏页，这时候有两种可能

    • 一种是 内存不足 必须刷脏也才能有新的空间放磁盘拉取的数据

    • 一种是 redolog 的checkpoint 设置过小

  • 解决方案

    1. innodb_io_capacity 去设置系统的读写能力

    2. innodb_max_dirty_pages_pct是脏页比例上限，一半设置位 75%

    3. innodb_flush_neighbors 刷脏页的时候， 是否把邻居一起刷了

思考题

• redolog 设置过小会导致什么问题

• 每次事务提交都要写redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这

个“环”将很快被写满，write pos一直追着CP。

-这时候会出现，磁盘的压力很小，内存压力也很小，但是数据库出现间歇性的性能下跌（间歇性的卡顿）

13| 数据库删除一般数据，但是占用空间不变

1、数据页复用

• 数据页复用：当一整个数据页都删除后，数据页会被标记为可以复用，可以复用到B+树的任何位置

2、记录复用

• 记录复用：删除的记录不会将空间回收，只会留下一个空位，可以留给下一个数据插入

• 如果我们用delete命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记 为可复用。但是磁盘上，文件不会变小。

3、重建表

• 减少内存空间的方案，可以采用重建表

• alter table A engine=InnoDB

思考题

• 重建表之后 发现存储空间占用变得更大了，是什么原因？

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

14 | count(*) 这么慢

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

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

1 为什么 Mysql 不能通过一个记录给出当前一共有多少条数据

• 由于 MVCC 的存在，事务返回的查询数据在不同时刻是不相同的

2、不同 count（） 的用法

• 速度上来说
  • count(字段)<count(主键id)<count(1)≈count(*)

15|日志和索引相关的问题

1、两阶段提交时候，mysql 发生异常重启会出现什么现象

• 如果 redo log 中的事务是完整的，已经有commit 的表示，就会直接提交
• 如果 redo log 中只有完整的 prepare 就需要去判断对应事务的 bin log 是否完整
  • 如果binlog 完整，就提交事务，将 redo log 设置为 commit
  • 如果binlog 不完整，就回滚事务

2、如果直到 bin log 是完整的

• statement 格式的 bin log 最后有个commit
• row 格式的 bin log 最后有个 XID

3、redo log 和 bin log 是如何联系到一起的

通过 XID

16| order by 的工作流程

• 给出表的定义：

• 给出 SQL 语句

1、全字段排序（Using filesort）

• 初始化 sort_buffer,放入 name city age 三个字段
• 通过索引 杭州 找到第一个满足的主键id
• 通过主键 id 去回表找到 name city age 放入 sort_buffer
• 重复上述操作，直到 city 不满足为止
• 对 sort_buffer 中的数据按照 name 字段进行快速排序，选取其中的1000行

2、rowid 排序

• 简单来说 就是当 排序的单行长度过长，就只会将主键 和 待排序字段 抽取到 sort_buffer 中，进行排序

3、索引方式

创建 （city， name） 的索引，就可以通过索引的方式，直接选取，不需要进行排序，这和B+树的底层结构有关系

思考题

• 假设你的表里面已经有了city_name(city, name)这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录。如果SQL查询语句是这么写的 ：

• mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;

• 问会走排序吗？

• 答： 不会走排序，因为要查询的是 杭州 和 苏州的

• 如何修改？

• select * from t where city="杭州" order by name limit 10100;

• select * from t where city="苏州" order by name limit 10100;

• 将上述两条语句的进行归并排序

17| 如何正确显示随机消息

• APP 有一个需求，需要随机抽取数据表中的 三个单词，这个数据表比较大，同时支持插入和删除操作。

1、通过内存临时表，order by rand（）

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

需要全表扫描！！

• 执行流程：
  1. 在 sort_buffer 中会生成调用 rand（）函数生成一个 大于0 小于1 的随机数
  2. 根据主键 和 生成的随机数进行排序，而后回表
  3. 返回前三个

2、 使用磁盘临时表

• 同样是 order by rand（），但是不同的是采用的是磁盘临时表，数据会存储到磁盘上，进行多次归并排序

3、 磁盘临时表和内存临时表

• 什么是临时表： MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。为什么会产生临时表：一般是由于复杂的SQL导致临时表被大量创建临时表分为两种，一种是内存临时表，一种是磁盘临时表。

• 内存临时表采用的是memory存储引擎，磁盘临时表采用的是myisam存储引擎(磁盘临时表也可以使用innodb存储引擎，通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎，从mysql5.7.6之后默认为innodb存储引擎，之前版本默认为myisam存储引擎)。分别通过Created_tmp_disk_tables 和 Created_tmp_tables 两个参数来查看产生了多少磁盘临时表和所有产生的临时表(内存和磁盘)。

内存临时表空间的大小由两个参数控制：tmp_table_size 和 max_heap_table_size 。一般来说是通过两个参数中较小的数来控制内存临时表空间的最大值，而对于开始在内存中创建的临时表，后来由于数据太大转移到磁盘上的临时表，只由max_heap_table_size参数控制。针对直接在磁盘上产生的临时表，没有大小控制。

下列操作会使用到临时表：

1、union查询

2、对于视图的操作，比如使用一些TEMPTABLE算法、union或aggregation

3、子查询

4、join 包括not in、exist等

5、查询产生的派生表

6、复杂的group by 和 order by

7、Insert select 同一个表，mysql会产生一个临时表缓存select的行

8、多个表更新

9、GROUP_CONCAT() 或者 COUNT(DISTINCT) 语句Mysql还会阻止内存表空间的使用，

直接使用磁盘临时表：

1、表中含有BLOB或者TEXT列

2、使用union或者union all时，select子句有大于512字节的列

3、Show columns或者 desc 表的时候

随机排序方法

• 所有通过 order by rand（）方案的排序都会导致计算过程变得非常复杂，需要全表排序，全表扫描

• 如果已经知道 主键的最大值和主键的最小值，可以通过生成 X = (M-N)* rand() + N 的方式，选取一个值

• 缺陷： 上述方案只适合主键分布比较均匀的情况，如果主键是 1，2，2000，4000，就不行，所以需要进行压缩操作，

• 求出 @C 就是表的总函数 ，而后根据总行数，选取一个随机值，然后遍历到那个随机值 limit 1.就可以。

18| 为什么这些SQL语句逻辑相同，性能却差异巨大？

1、对字段添加了函数操作，直接不走索引了

• 修改让其通过索引操作

2、隐式类型转换

• 所以不会走索引，需要将他修改成 “110717” 才可以 在MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

tradeid 将会变成 数字，不能走索引

3、字符编码转换

• 字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

19| 为什么我只查询一行数据执行这么慢

1、简单的查询语句长时间不放回

• 大概率是锁表了
• 可以通过 show processlist 参看当前线程的状态

• 等 元数据 写锁

• 等刷新

• 等行锁

• session A 拿走了 行锁，事务B 不能查询

  • 这时候 可以通过查询 innodb 的行锁表进行查看，发现是 Querey 4 这个线程执行的语句把当前行锁住了，可以通过 kill 4 将当前连接断开，让其释放

2、查询慢

查询写的有问题

• 当 C 上面没有索引的适合，需要遍历 50000 行 数据

• 可以通过将 慢查询 添加到 慢查询表上面，进行进一步的处理和分析

存在快照需要一直回表

思考题

• 有100万行数据，其中有10万行数据的b的值是1234567890， 假设现在执行

• 语句是这么写的:

• 问： MySql 会如何执行

20| 幻读

• 表:

• 记住 幻读 主要针对的是插入， 不可重复读主要针对的是 修改

• 解决幻读是通过 间隙锁 和 行锁 进行解决的

思考题：

c: 5 10 15 20 25

扫描到哪里，哪里就需要加间隙锁，c 扫描到了 10

21 | 为什么我只改一行的语句，锁这么多？

1 加锁的基本规则

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

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

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

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

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

---

• 总结：

• 首先，加锁都是左开右闭，所有可以被范围到的对象都会被加锁

• 其次： 如果出现等值查询 当是唯一索引的时候命中的时候，变成行锁，只锁一个，不命中的时候，退化成为间隙锁

• 最后： 如果是等值查询，如果是普通索引需要访问到第一个不满足条件的，当右边的闭合是取不到的时候，退成间隙锁

---

案例1 ：唯一索引等值查询间隙锁

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

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

• 如果这时候id

案例2 非唯一索引等值锁

根据原则

1. 加锁单位是next-key lock，因此会给(0,5]加上next-key lock。
2. 要注意c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到 c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。
3. 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此 退化成间隙锁 (5,10)。

案例3 唯一索引范围锁

0 ——5 —–10 —–15 —–20 —–25

• 主键索引，是唯一的要找到，第一个id=10的行，因此本该是next-key lock(5,10]。

• 根据优化1， 主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁

• 范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]。 —–bug 唯一索引范围会访问到第一个不满足条件

session A这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]。这 样，session B和session C的结果你就能理解了

首次session A定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。

案例四 非唯一索引范围锁

0 ——5 —–10 —–15 —–20 —–25

在第一次用c=10定位记录的时候，索引c上加了 (5,10] 这个next-key lock后，由于索引c是非唯一索引，没有优化规则，也就是 说不会蜕变为行锁，因此最终sesion A加的锁是，索引c上的(5,10] 和(10,15] 这两个next-key -lock。

案例五：唯一索引范围锁 案 bug

0 ——5 —–10 —–15 —–20 —–25

• 按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了。

• InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上

• 加锁范围 （10，20]

22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

1 处理掉那些占着链接但是不工作的线程

• 如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断 开事务内空闲太久的连接。

• showprocesslist的结果里，踢掉显示为sleep的线程，可能是有损的

2 减少连接过程的损耗

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

3 慢查询优化

1. 创建更加合适的索引
   • 创建紧急索引

1. SQL没有写好

2. 选错索引

   • 加上 force index，

23 | MySQL是怎么保证数据不丢的？

1、binlog 写入

2、redolog 写入

• 可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件

• redolog 公用在内存中的 redolog buffer

• redo log buffer 也是在内存中的。文件系统就是内存中的一个地方

• 增加在write 阶段可以停留的时间，让更多的数据一次性写入

• prepare 状态的数据也会持久化

• IO 瓶颈：

24 | MySQL是怎么保证主备一致的？

1 主备模式流程图

2 binlog 格式

如何保证幂等性

• 双主情况下，两边都可以写，如何保证幂等性
• 通过 GTID
• 通过记录发送的server id ， 当出现server id 相同的时候，说明是自己发送的

25 | MySQL是怎么保证高可用的？

• 主备模式

1、主备延迟

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产binlog 的速度要慢。

2、主备延迟的来源

1. 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差
2. 备库压力较大，承担了查询任务
3. 处理大事务
4. 备库的并行复制能力

3、主备切换

3.1 可靠性优先

3.2 可用性优先

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

思考题

26 | 备库为什么会延迟好几个小时？

如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级 别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏

1 在Mysql5.5 版本之前

只支持并行复制

并行复制的方案：

1. 按表分发===》不同的表，一定可以并行
2. 按行分发===》如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求binlog格式必须 是row

2 Mysql5.6 的并行复制策略

按库并行

用于决定分发策略的hash表里，key就是数据库名

27 | 主库出问题了，从库怎么办？

• 主备多从的结构

• 失败切换

1 基于位点的主备切换

当A 宕机后，A2 进行接替，这时候需要在B、C、D上执行change master 指令

需要知道主库对应日志的文件名和偏移量

所以需要寻找同步位点

• 然而这个同步位点是不准确的。有可能在那个时间点，已经发送了binlog，从库也已经执行过了，如果继续选择这个同步点，这时候会出现重复执行的情况。会提示主键冲突错误，这时候可以通过设定跳过指定错误的方法进行执行

这种方案不适合！很容易出现 从库中存在备库未执行的语句，可以通过跳过错误的方法进行忽略，容易出错，再MYSQL5.6时候，引入了 GTID 行改善

2 基于GTID的同步

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

• 修改命令

• master_auto_position=1就表示这个主备关系使用的是GTID协议，不需要再和之前的基于位点的方法需要指定 MASTER_LOG_FILE和MASTER_LOG_POS参数

• 同步操作

在双M情况下，完成表结构的修改：通过GTID

思考题

28 | 读写分离有哪些坑？

• 由于主从同步时间的延迟，当主库执行结束一个更新操作，client立刻发起一个读请求，很有可能出现读到的数据是过期的

1、强制走主库

• 根据业务需求进行处理

2、sleep方案

3、判断主备无延迟方案

seconds_behind_master是否已经等于0。如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求

4、等主库位点方案

5、等GTID方案

思考题

29 | 如何判断一个数据库是不是出问题了？

1 select（1）判断

• 实际上，select 1成功返回，只能说明这个库的进程还在，并不能说明主库没问题

并发连接和并发查询

• 并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为 并发查询太高才是CPU杀手。

你在showprocesslist的结果里，看到的几千个连 接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询

2 查表判断

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

• 我们可以检测出由于并发线程过多导致的数据库不可用的情况。 但是，我们马上还会碰到下一个问题，即：空间满了以后，这种方法又会变得不好使。

• 我们知道，更新事务要写binlog，而一旦binlog所在磁盘的空间占用率达到100%，那么所有的更新语句和事务提交的commit语句就都会被堵住。但是，系统这时候还是可以正常读数据的，实际上这时候系统是异常的

3 更新判断

• 既然要更新，就要放个有意义的字段，常见做法是放一个timestamp字段，用来表示最后一次执 行检测的时间。这条更新语句类似于：

• 你可以设想一个日志盘的IO利用率已经是100%的场景。这时候，整个系统响应非常慢，已经需 要做主备切换了。

• 但是你要知道，IO利用率100%表示系统的IO是在工作的，每个请求都有机会获得IO资源，执行自己的任务。而我们的检测使用的update命令，需要的资源很少，所以可能在拿到IO资源的时候就可以提交成功，并且在超时时间N秒未到达之前就返回给了检测系统。

• 检测系统一看，update命令没有超时，于是就得到了“系统正常”的结论。 也就是说，这时候在业务系统上正常的SQL语句已经执行得很慢了，但是DBA上去一看，HA系 统还在正常工作，并且认为主库现在处于可用状态。

• 之所以会出现这个现象，根本原因是我们上面说的所有方法，都是基于外部检测的。外部检测天 然有一个问题，就是随机性

4 内部统计

统计每次请求的 IO 时间进行判断

通过设定请求的IO 时间阈值，去判断系统是否属于正常状态

30 | 答疑文章（二）：用动态的观点看加锁

1 不等号条件里的等值查询

有同学对“等值查询”提出了疑问：等值查询和“遍历”有什么区别？为什么我们文章的例子里 面，where条件是不等号，这个过程里也有等值查询？

0—–5—–10—–15——20

主键索引 找=12 变成加锁 （10，15】，利用唯一优化 15不能等 变成 （10，15）

向左遍历，范围到 5 进行加锁 （0-5】（5-10】 所以加锁的范围 （0- 15）

2 怎么看死锁

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

• 结论

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

1 使用delete语句误删数据行

• 可以用Flashback工具通过闪 回把数据恢复回来。

• Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。而能够使用这个方案的前提是，

• 需要确保binlog_format=row和 binlog_row_image=FULL。

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

• 使用delete命令删除的数据，你还可以用Flashback来恢复。而使用truncate /drop table和drop database命令删除的数据，就没办法通过Flashback来恢复了

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

3 延迟复制备库

• 如果一个库的备份特别大，或者误操作的时间距离上一个全量备份的时间较长，比如一周一备的 实例，在备份之后的第6天发生误操作，那就需要恢复6天的日志，这个恢复时间可能是要按天 来计算的。

• 一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。 延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TOMASTER_DELAY =N命令，

• 可以指定这个备库持续保持跟主库有N秒的延迟。 比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误 操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

32 | 为什么还有kill不掉的语句？

• 在MySQL中有两个kill命令：

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

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

1、收到kill后，线程做什么

• kill并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了， 可以开始“执行停止的逻辑了”。会进入到结束的过程中

2、kill不掉的情况

情况1 线程没有执行到判断线程逻辑的状态

• 这里的 query C 并没有成功的结束，只是他的网络连接断掉了

• 在kill query C 的时候，修改了kill_query_C状态，同时发出信号。

• 但是由于并发线程不足，所以session C 受到了信号，也无法进行执行，所以没有办法kill

• 而当session E执行kill connection 命令时，是这么做的，

  1. 把12号线程状态设置为KILL_CONNECTION；

  2. 关掉12号线程的网络连接。因为有这个操作，所以你会看到，这时候session C收到了断开 连接的提示

• kill connection

• kill query

• 两个东西是不同的

情况2 终止的逻辑时间过长

33| 我查这么多数据，会不会把数据库内存打爆？

• 服务器是采用边读边发

1、服务端取数据和发送数据的流程

• 如果客户端不去读取 socket 中的数据，服务端将会停止在 sending to clent 阶段

mysql_use_result：客户端读取一行，服务器发送一行，继续开始查询下一行

mysql_store_result：服务端将需要发送的先缓存到本地，逐步发送

Sending to clien：等待客户端接收结果”的状态

Sending data： 正在执行操作

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

2、改进的LRU算法

• 当出现全表扫描的时候，如果表很大，需要淘汰Buffer Pool 中所有的数据，这对于正在执行业务的 Mysql 不有友好，会导致内存命中率急剧下降，出现了 改进LRU 算法

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

1. 图7中状态1，要访问数据页P3，由于P3在young区域，因此和优化前的LRU算法一样，将其移到链表头部，变成状态2。

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

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

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

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

小结

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

• 而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于InnoDB对 LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。

思考题

34 | 到底可不可以使用join？

• 这两个表都有一个主键索引id和一个索引a，字段b上无索引。存储过程idata()往表t2 里插入了1000行数据，在表t1里插入的是100行数据

1 Index Nested-Loop Join

• 我改用straight_join让 MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1 是驱动表，t2是被驱动表。

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

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

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

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

2 如何选择驱动表

N 对函数的影响更大，需要选择较小的N 所以需要让小表作为驱动表

3 Simple Nested-Loop Join

不走索引的情况

a 上面有普通索引，b 没有索引，所以每一次查询时候，都需要扫描一遍 t2 表

4 Block Nested-Loop Join

由于 t2 表没有索引

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

• 需要将有索引的 t1 表全部放到 join_buffer 中，如果放不下，会进行分块放入

• 图中的步骤4和5，表示清空join_buffer再复用。

• 如果你的join语句很慢，就把join_buffer_size改 大

小结

思考题

35 | join语句怎么优化？

• 我在表t1里，插入了1000行数据，每一行的a=1001-id的值。也就是 说，表t1中字段a是逆序的。同时，我在表t2中插入了100万行数据

• id 为主键， a 为普通索引，b无

1 Multi-Range Read 优化

回表过程是一行行地查数据，还是批量地查数据？

主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。因此，回表肯定是 一行行搜索主键索引的

2 NLJ优化==》 Batched Key Access

传统的 NLJ 不会采用到 Join_buffer

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

• 那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些行出来，一起传给 表t2。

• 既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer

3 BNL优化

• 使用Block Nested-Loop Join(BNL)算法时，可能会 对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致IO压力大以外，还 会对系统有什么影响呢？

• 我们说到InnoDB的LRU算法的时候提到，由于InnoDB对Bufffer Pool的LRU 算法做了优化，

即：第一次从磁盘读入内存的数据页，会先放在old区域。如果1秒之后这个数据 页不再被访问了，就不会被移动到LRU链表头部，这样对Buffer Pool的命中率影响就不大。

• 但是，如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒， 就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。

• 这种情况对应的，是冷表的数据量小于整个Buffer Pool的3/8，能够完全放入old区域的情况。

• 如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入young区域由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

你可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数

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

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

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

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

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

4 BNL 转 BKA

1. 在被驱动的表上建立索引，变成BKA，
2. 创建临时表，用上索引，触发 BKA 算法

小结

思考题

36 | 为什么临时表可以重名？

内存表和临时表的区别：

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

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

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

1. 建表语法是create temporary table …。
2. 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的 临时表t，对于session B就是不可见的。
3. 临时表可以与普通表同名。
4. showtables命令不显示临时表。
5. 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表 名重复导致建表失败的问题。
6. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或 者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动 回收，所以不需要这个额外的操作。

主备复制

在实现上，每个线程都维护了自己的临时表链表。这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表里的每个临时表，执行 “DROPTEMPORARY TABLE +表名”操作。

如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog 里。也就是说，只在binlog_format=statment/mixed 的时候，binlog中才会记录临时表的操作

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主 库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我 们就需要在主库上再写一个DROPTEMPORARY TABLE传给备库执行。

在备库中如何区分不同的临时表

MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中。这样，在备库的应用线程就能够知道执行每个语句的主库线程id，并利用这个线程id来构造临时表的

小结

**在实际应用中，临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的， 所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。 **

在binlog_format='row’的时候，临时表的操作不记录到binlog中，也省去了不少麻烦，这也可以 成为你选择binlog_format时的一个考虑因素。

37 | 什么时候会使用内部临时表？

1、Union

create table t1(
id int primary key, 
a int, 
b int,
index(a));
(select 1000 as f) union (select id from t1 order by id desc limit 2);

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

2、 group by

1. 创建内存临时表，表里有两个字段m和c，主键是m；

0. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

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

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

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

小结

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

38 | 都说InnoDB好，那还要不要使用Memory引擎？

39 | 自增主键为什么不是连续的？

1 自增的值保存在哪里

AUTO_INCREMENT=2，表示下一次插入数据时，如果需要 自动生成自增值，会生成id=2。

2 自增值不能回退

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯 定要加锁，然后顺序申请。

1. 假设事务A申请到了id=2， 事务B申请到id=3，那么这时候表t的自增值是4， 之后继续执行。

2. 事务B正确提交了，但事务A出现了唯一键冲突。

3. 如果允许事务A把自增id回退，也就是把表t的当前自增值改回2，那么就会出现这样的情 况：表里面已经有id=3的行，而当前的自增id值是2。

4. 接下来，继续执行的其他事务就会申请到id=2，然后再申请到id=3。这时，就会出现插入语 句报错“主键冲突”。

而为了解决这个主键冲突，有两种方法：

1. 每次申请id之前，先判断表里面是否已经存在这个id。如果存在，就跳过这个id。但是，这 个方法的成本很高。因为，本来申请id是一个很快的操作，现在还要再去主键索引树上判断 id是否存在。

2. 把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

所以 自增值不能回退

3 自增锁优化

普通insert语句，自增锁在申请之后就马上释放；

类似insert …select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放

批量插入的时候，

• statement 记录的是 插入的语句，并没有指定主键的值

• 其实，这是因为原库session B的insert语句，生成的id不连续。这个不连续的id，用statement格式的binlog来串行执行，是执行不出来的。

• 容易出现 库不一致的问题

40 | insert语句的锁为什么这么多？

44 | 答疑文章（三）：说一说这些好问题

Where 和 On的区别

在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤

3、 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的

45 | 自增id用完怎么办？

1 表定义自增

2 InnoDB 系统自增

3 XID

4 Innodb trx_id

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

• InnoDB数据可见性的核心思想是：每一行数据都记录了更新它的trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的trx_id做对 比

5 thread_id

• thread_id的逻辑很好理解：系统保存了一个全局变量thread_id_counter，每新建一个连接，就 将thread_id_counter赋值给这个新连接的线程变量。

• thread_id_counter定义的大小是4个字节，因此达到2 -1后，它就会重置为0，然后继续增加。

• 但是，你不会在showprocesslist里看到两个相同的thread_id。 这，是因为MySQL设计了一个唯一数组的逻辑，给新线程分配thread_id的时候，逻辑代码是这样的：

小结