MySQL|实战45讲总结

1.一条SQL查询语句是如何执行的？

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核

心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎

的功能都在这一层实现，比如存储过程、触发器、视图等。

• 连接器：负责跟客户端建立连接、获取权限、维持和管理连接

  首先进行TCP连接。

  然后是身份认证：如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端 程序结束执行。如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

  客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

  长链接和短连接：

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

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

  建立连接的过程通常是比较复杂的，所以在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。但是全部使用长连接后，由于 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，所以有些时候 MySQL 占用内存涨得特别快，导致 MySQL 异常重启。

  如何解决：

  1. 定期断开长连接。

  2. MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行

     mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验

     证，但是会将连接恢复到刚刚创建完时的状态。

• 查询缓存：MySQL 8.0 版本直接将查询缓存的整块功能删掉了

  MySQL 拿到一个查询请求后，会先到查询缓存。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

  如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询

  缓存中。

  但是大多数情况下我会建议你不要使用查询缓存：

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

  可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于要使用查询缓存的语句，可以用 SQL_CACHE 显式指定。

• 分析器

  词法和语法解析后，将输入的语句转换成server能看懂的语句，要做什么。如果语法错误，则返回给客户端。

• 优化器

  一条语句有多种执行的方式，选择较优的进行执行，怎么做。例如：在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，决定各个表的连接顺序。

• 执行器：

  权限查询，有没有表的权限，调用引擎结构获得数据。

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

引擎层负责数据存储和提取。常用的引擎有：innodb，myisam，memory等。innodb 在 mysql5.5.5 版本成为默认引擎。可以用engine=memory修改

2：日志系统

MySQL 的 WAL 技术：WAL 的全称是 Write-Ahead Logging，先写日志，再写磁盘。

redo log：用于cash safe

当有一条记录需要更新的时候，InnoDB 引擎先把记录写到 redo log 里面，并更新内存。此时更新就算完成。InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

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

write pos 是当前记录的位置，一边写一边后移，checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据库中。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

binlog：归档日志

两阶段提交

为什么用两阶段提交：保证数据库的一致性。

InnoDB 认为：写完 redo log的数据是需要恢复的。binlog 认为：写完binglog的数据是需要恢复的。

如果先写redo log，再写 binlog，假设在写完 redo log 系统崩溃。此时恢复，恢复到了写入之后的状态。但是binglog 中没有这条记录，做备份失败。

如果先写 binlog 再写 redo log，假设在写完 binlog 后系统崩溃。此时，redo log 中没有这条记录，认为无效，但是 binlog 中有这条记录，认为有效，导致不一致。

redo bog 和 binlog 的不同点：

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

03：事务的隔离

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL中，事务支持是在引擎层实现的。

MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是MyISAM 被 InnoDB 取代的重要原因之一。

SQL 标准的事务隔离级别包括：读未提交（readuncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

• 读未提交，事务未提交的时候，可以被其他事务看到。不创建视图，脏读
• 读提交，一个事务的提交后，才能被其他事务看到。视图是在每个 SQL 语句开始执行的时候创建的。存在不可重读的特性
• 可重读，一个事务只能看到在它开始前的提交。一个事务在启动后，看到的统一数据是一致的。视图是在事务启动时创建的，整个事务存在期间都用这个视图。存在幻读，但是可以去除
• 串行化，加锁

Oracle 数据库的默认隔离级别其实就是“读提交”。

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

当系统里没有比这个回滚日志更早的 read-view 的时候，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

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

可以在 information_schema 库的 innodb_trx 这个表中查询长事务

事务的启动方式

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到主动执行 commit 或 rollback 语句，或者断开连接。
• 建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

04：索引基础

常见的索引模型：

• 哈希表：一种以键 - 值（key-value）存储数据的结构，只要输入待查找的值即 key，就可以找到其对应的值即 Value。多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。做等值查询快，做范围查询慢，插入快。适合于只有等值查询的场景。
• 有序数组：在等值查询和范围查询场景中的性能就都非常优秀。但是更新，插入和删除慢。有序数组索引只适用于静态存储引擎。
• N叉搜索树：查找，插入，删除等操作都较快。但是需要多次读磁盘。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。就不应该使用二叉树，而是要使用“N 叉”树。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

05：InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。innoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

主键索引和非主键索引：

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

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

• 基于非主键索引的查询如果不能被覆盖，需要返回主键索引获得所需的数据。因此有时候需要多扫描一棵索引树。在应用中应该尽量使用主键查询。回到主键索引树搜索的过程，称为回表。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。维护过程中可能有叶分裂和叶合并。

自增主键的优劣：

• 自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

• 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

• 从性能和存储空间方面考量，自增主键往往是更合理的选择。

覆盖索引

索引上有查询的所有数据，不用回表操作，成为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。B+树是将比较字段按大小排序，因此最左前缀肯定有序。只要满足最左前缀，就可以利用索引来加速检索。

建立索引的原则：尽可能的通过安排好字段的顺序，提高索引的复用能力。并且尽量使用较小空间。

索引下推

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

06：全局锁和表锁

全局锁

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

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

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
• 如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

**法只适用于所有的表使用事务引擎的库。**可以使用官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

全库只读，也可以使用 set global readonly=true 的方式，但是不建议使用。

• 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。
• 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

**表锁的语法是 lock tables … read/write。**与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。也就是在对表有访问的时候，不能修改表的结构，在修改表的结构的时候，不能访问表。

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

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

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

如何安全地给小表加字段？

• 要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
• 在 alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

07：行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一

行锁就是针对数据表中行记录的锁。比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

两阶段锁

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

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

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

正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的 CPU 资源。

• 把死锁检测关掉
• 热点行更新导致的性能
• 在中间件实现
• 修改MySQL源码，对于相同行的更新， 在进入引擎之前排队
• 过将一行改成逻辑上的多行，分散单表压力。

08：如何实现事务的隔离

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB 表的语句，事务才真正启动。如果想要马上启动一个事务，可以使用 starttransaction with consistent snapshot 这个命令。

autocommit=1时，事务不用显式地使用 begin/commit，语句本身就是一个事务，语句完成的时候会自动提交。

MVCC中的快照

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

• InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
• 每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

图 中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。

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

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

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

总结下就是：在数组中以及大于数组最大值的不可见。其余可见。

1. 版本未提交，不可见；

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

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

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

除了 update 语句外，select 语句如果加锁，加上 lock in share mode 或 for update，也是当前读。

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

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

09：普通索引和唯一索引

主键选择：字段不能过长。因为其他索引会带上主键值，增大索引所占空间。、

查询过程

• 普通索引的等值查询：从B+树从树根开始，按层搜索到叶子节点，页内部通过二分法来定位记录。查找到满足条件的第一个记录后，需要查找下一个记录，直到碰 到第一个不满足条件的记录。
• 唯一索引的等值查询：从B+树从树根开始，按层搜索到叶子节点，页内部通过二分法来定位记录。查找到满足条件的第一个记录后，停止查找（因为有唯一性约束）。

性能差距微乎其微：InnoDB的数据是按数据页为单位来读写的多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针 寻找和一次计算。

更新过程

change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，如果数据页还没有在内存中，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中。在下次查询需要访问这个数据页的时候，将数据页读入内 存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。虽然名字叫作 change buffer，实际上它是可以持久化的数据。

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

用的是buffer pool里的内存，不能无限增大。

什么条件下可以使用 change buffer

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。必须要将数据页读入内存才能判断。直接更新内存会更快，所以不会使用change buffer 了。因此，唯一索引的更新就不能使用 change buffer。实际上也只有普通索引可以使用。

• 对于在内存中的数据，唯一索引和普通索引的更新效率一样。
• 对于不在内存中的数据，唯一索引需要先读数据到内存中，然后更新。普通索引直接使用 change buffer，更快。

可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

如何选择 写多读少的表利用change buffer更好，可以将多次写合并为一次，使用一次磁盘io。

读多写少的，如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。

change buffer和redo log

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的IO消耗。

10：为什么会选错索引

MySQL 有时会选错索引

优化器的逻辑

目的：找到一个最优的执行方案，并用最小的代价去执行语句，扫描行数，是否使用临时表、是否排序，是否回表等因素进行 综合判断。

基数：索引的区分度，索引中不同值的个数，以使用showindex方法，看到一个索引的基数。如果基数和实际相差较大，可以使用analyze table t 命令，来重新统计索引信息。

基数计算方法：会选择N个数据页，统计这些页面上的不同值，得到一个平均 值，然后乘以这个索引的页面数。

索引选择异常和处理

• 采用 force index f 强行选择一个索引
• 可以考虑修改语句，引导 MySQL 使用期望的索引
• 可以新建一个更合适的索引，提供给优化器做选择，或删掉误用的索引。

11：给字段加索引

前缀索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。当字段太长时，可以根据基数选择合适的前缀建立索引。

前缀索引对覆盖索引的影响

• 缺点：不能使用覆盖索引性质，必须回表
• 优点：减少空间

使用前缀索引就用不上覆盖索引对查询性能的优化了，这是在选择是否使用前缀索引时需要考虑的一个因素。

倒序存储

把信息倒序存储，增大前缀的基数。

• 不支持范围查询
• 不会消耗额外的存储空间
• 额外调用一次reverse函数

使用 hash 字段

在表上再创建一个字段，保存需要建立索引的哈希值，然后在新字段建立索引。

• 不支持范围查询
• 要额外调用一次哈希函数

哈希字段方法和倒序存储方法比较:

• 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash 字段方法需要增加一个字段。
• 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
• 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

12:redo log 和刷盘

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

平时的时候，mysql执行得很快，其实就是在写日志和内存，而有时候mysql偶尔执行变慢，可能的原因是在刷脏页。

数据库刷脏页的情景：

• 一是当redo log文件满的时候。系统会停止所有的更新操作，将脏页刷到磁盘，从而推进checkpoint。因为刷到磁盘后，redo log是不用保存的，可以进行覆盖。

• 第二种情况是当系统的buffer pool用完，又需要读入新的数据页时，需要淘汰掉一个内存的脏页。这个时候脏页就要刷新到磁盘。

• 第三种情况是，当系统空闲时，数据库会将脏页刷入磁盘。

• 第四种情况是，当数据库正常关闭时，会将所有的buffer pool中的脏页刷新到磁盘。

第三种情况于第四种情况，不会影响数据库的性能。

• 第一种情况，当redo log写满，需要刷脏页到磁盘推进checkpoint。这种情况要尽量避免，因为在这种情况下，整个系统就不接收更新了，所有的更新会被堵住。这种情况是 InnoDB 要尽量避免的。

• 第二种情况是当内存不够用时，先将脏页刷到磁盘，再读入需要的数据也，这种情况经常发生。

  当要读入一个内存中没有的数据页时，就需要在缓冲池中申请一个数据页，这个时候如果缓冲池中没有干净的数据也，系统会把缓冲池中最久没有用过的那个数据页淘汰掉，如果这个数据页是个脏页，就需要先刷到磁盘。耗费时间。

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

• 当某个语句淘汰的脏页个数太多，会使得查询时间明显变长。

• 日志写满需要，为了推进checkpoint，就要将脏页刷到磁盘，堵塞更新

• InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

InnoDB刷脏页的控制策略：

• 告诉InnoDB所在主机的IO能力：innodb_io_capacity。这样InnoDB才知道,在刷脏页时可以刷多快。

• 在准备刷一个脏页的时候，如果这个数据页旁 边的数据页刚好是脏页，就会把这个“邻居”递归着一起刷掉。这个设置在机械硬盘的时候，可以减少随机io，提升性能，但是现在大多数用的是ssd硬盘。可以将这个功能关掉。innodb_flush_neighbors 参数就是用来控制这个行为，设置为0不生效

什么是checkpoint？

• 因为 redo log的文件是固定大小的，而且是循环写的，可以看成一个循环队列。当队列的头追上队列尾的时候。意味着整个redo log的文件就写满了，不能继续写入。当buffer pool中的脏页刷到磁盘后，这个脏页对应的redo log就不再需要了。是可以被覆盖掉的，就推进了队列尾。将脏页刷入磁盘，推进队列尾的这种情况就叫checkpoint。
• 所谓的chackpoint 就是指：将buffer pool 中的脏页刷到磁盘，推进redo log 文件为的行为。

13:表空间回收

数据库中的一个表包含两部分：表结构定义和数据。

表数据存放位置：innodb_file_per_table 控制。

• 可以存放在共享表空间里
• 可以存放在单独文件中。

建议存放在单独文件中，因为更容易管理，而且当不需要这个表的时候，可以通过drop table命令。系统就会直接删除这个文件，释放内存。如果放在共享表空间里，即使表删除了，空间也不会释放。

删除行

• 在删数据库删除行的流程，只是把这一行标记为删除，并且放入已删除链表中。并没有释放空间。
• 如果之后再插入新的记录，可能会复用这个位置。
• 如果删除掉了一个数据页上的所有记录，整个数据页就变得可复用了，也不会释放空间。
• 如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一 个数据页就被标记为可复用。
• 如果使用delete命令，将表的所有行都删除，结果就是：所有的数据页都会标记为可复用，但在磁盘上，文件不会变小。

通过delete命令是不能回收表空间的。这些可以复用，但没有被使用的空间，看起来就像空洞。插入数据可能引发业分裂，引入空洞。

页分裂

• 是按照索引递增顺序插入的，那么索引是紧凑的，不会引发
• 数据是随机插入或更新的，就可能造 成索引的数据页分裂。
• 经过大量增删改的表，都是可能是存在空洞的。把这些空洞去掉，就 能达到收缩表空间的目的

经过大量增删改的表都是可能存在空洞的。

重建表可以真正的去除这些空洞，达到收缩表空间的目的。

• 新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A 里读出来再插入到表B中。用表B替换A，从效果上看，就起到了收缩表A空间的作用。

• 可以使用alter table a engine=InoDB命令来重建表。

在5.6版本以后引入了online ddl 对这个流程做了优化。

• 建一个临时文件，扫描表 a 的主键的所有数据页。

• 用数据页中表 a 的记录生成 b+ 树存储到临时文件中。

• 生成临时文件的过程中，将所有对 a 的操作记录在一个日志文件中。

• 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑上与表 a 相同的表。

• 用临时文件替换表 a 的文件。

由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 a 做增删改的操作。

这个重建方法会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用GitHub开源的gh-ost来做。

进行这个操作的时候，硬盘的剩余存储空间要大于调文件，因为temple feel是要占用临时空间的。

14：count 函数

count(*) 的实现方式

在不同的 MySQL 引擎中，不加过滤条件的count(*) 有不同的实现方式。

• MyISAM 引擎把一个表的总行数存在了磁盘上，执行 count(*) 的时候会直接返回这个数，效率很高；
• InnoDB 引擎执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果加了where 条件的话，MyISAM 表也需要全表扫描。

为什么 InnoDB 不跟 MyISAM 一样，把数字存起来呢？

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

可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

InnoDB 对count(*) 的优化

普通索引树比主键索引树小很多，对于 count(*) 来说，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

show table status 命令显示的行数不能用来代替count。*

索引统计的值是通过采样来估算的。TABLE_ROWS 是从这个采样估算得来的，官方文档说误差可能达到 40% 到 50%。所以，show tablestatus 命令显示的行数也不能直接使用。

在数据库保存计数

把这个计数直接放到数据库里单独的一张计数表 C 中能够快速精确地得到数据库中的行数。

• 不会崩溃丢失，InnoDB 是支持崩溃恢复不丢数据的。
• 因为mvcc版本控制，所以在同一个事物中，能得到这个事物开启时的版本，从而获得正确的结果。

不同的 count 用法

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

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

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

15：日志和索引

崩溃恢复时的判断规则：

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

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

  完整：

  • 如果是，则提交事务；
  • 否则，回滚事务。

MySQL 怎么知道binlog是完整的

一个事务的 binlog 是有完整格式的：

• statement 格式的 binlog，最后会有 COMMIT；
• row 格式的 binlog，最后会有一个 XID event。
• 在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog内容的正确性。

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

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

MySQL为什么要这么设计?

• binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。

为什么还要两阶段提交呢？干脆先redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

• 如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩溃恢复，又能支持归档，为什么不可以？

• 如果说历史原因的话，那就是 InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就有没有支持崩溃恢复。
• binlog 没有能力恢复“数据页”。 InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页。 崩溃后，无法判断哪个事务的修改写入了磁盘，哪个事务的修改没有写入磁盘。可能崩溃事务的上一个事务只写了内存，没有写磁盘。并且崩溃事务涉及修改的数据页的一部分可能已将写入磁盘，重新恢复的时候，可能导致重复修改。

只用 redo log ，不要 binlog？

• 只从崩溃恢复的角度来讲是可以的。
• binglog 有自己的用途：
  • 一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
  • 一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。

redo log 一般设置多大？

• redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样WAL 机制的能力就发挥不出来了。
• 如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为4 个文件、每个文件 1GB 吧。

数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？

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

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

• 生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。
• redo log buffer 就是一块内存，用来先存 redo 日志的。数据的内存先被修改，然后redo log buffer 也写入了日志。
• 真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit语句的时候做的。

16：order by 工作原理

全字段排序

用 explain 命令来看看这个语句的执行情况，Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

• 初始化 sort_buffer，确定放入的字段（排序条件+需要的字段）；
• 从索引上按顺序找到满足条件的主键 id，到主键索引取出整行，再取出要放入的字段，存入 sort_buffer 中；
• 对 sort_buffer 中的数据按排序条件做快速排序；

排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。

• 如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。
• 如果排序数据量太大，内存放不下，则利用磁盘临时文件辅助排序。
• MySQL 将需要排序的数据分成若干份，每一份单独排序后存在这些临时文件中。然后把这临时有序文件归并排序合并成一个有序的大文件。

rowid排序

如果查询要返回的字段很多的话，那么sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，就会使用rowid 排序。

• 初始化 sort_buffer，确定放入的字段（主键+排序条件）；
• 从索引上按顺序找到满足条件的主键 id，到主键索引取出整行，再取出要放入的字段（主键+排序条件），存入 sort_buffer 中；
• 对 sort_buffer 中的数据按排序条件做快速排序；
• 遍历排序结果，按照 id 的值回到原表中取出需要的字段回给客户端。

rowid 排序相比于全字段排序，多了一次回表操作。

全字段排序减少一次回表过程，如果sort_buffer足够大，会尽力使用全字段排序，如果不够大，不得不使用rowid排序。rowid排序会要求回表多造成磁盘读，因此不会被优先选择。

覆盖索引天然有序

如果按照排序条件，在表中找到的数据天然有序，就不需要另外的排序的过程，可以直接拿到有序的数据（可能回表，如果是覆盖索引，则不需要回表）。

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

17：随机输出

• Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。tmp_table_size这个配置限制了内存临时表的大小，默认值是16M，如果临时表大 小超过了tmp_table_size，内存临时表就会转成磁盘临时表。

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

• 对于临时内存表的排序来说，InnoDB 执行全字段排序会减少磁盘访问，因此会被优先选择。

• 对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越小越好了，所以，MySQL 这时就会选择 rowid 排 序。

• order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

• MySQL 5.6 版本引入的一个新的排序算法，即：优先队列排序算法。当要输出的有序信息比较少的时候，会使用这个排序方式。

如何正确的随机输出数据库中的若干行？

• 方法一：

  • 取得这个表的主键id的最大值M和最小值N;O（1）
  • 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() +N;O（1）
  • 取不小于X的第一个ID的行。O（1）
  • 不足：不严格满足题目的随机要 求，因为ID中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

• 方法二：

  • 首先求出数据库中的总行数。

  • 用总行数乘以一个0~1的随机数Y。需要输出多少行就得到多少个Y。

  • 执行语句select * from tables Limit Y, 1 若干次。

  • MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。

18:什么时候会走索引失败

• 如果对筛选字段做了函数计算，数据库会放弃走索引。原因是经过函数计算，可能破坏有序性，因此优化器决定放弃走索引数的功能。注意，只是放弃了走索引速搜索的功能，并没有放弃使用索引的功能，因为如果在索引上遍历的代价比较小，优化器会选择在索引以上便利。

  • 时间函数
  • 算数运算
  • 大小写转换

• 为了能够用上索引的快速定位能力，基于字段本身的查询要直接查询，不要对字段加函数。

• 有些时候，数据库会对我们写的语句进行隐式转换，导致不走索引树的情况。

  • 第一种情况，数据类型的隐式转换。将字符串转换成数字。

  • 第二种情况，字符集的隐式转换将utf8转换成utf8mb4。

  • 如果有有这种情况发生，可以手动将转换放到比较表达式的右边，避免左边的转换引发的不走索引的问题。

这里再说一下join的细节

• 当对两个表进行连接操作的时候，一个表是驱动表，另个一个表是被驱动表。执行流程是这样的。

  • 首先优化去会根据条件在驱动表表中找出需要的行，在找出行的时候，可能会选择索引或全表遍历。

  • 然后数据库会根据得到的行取出表连接条件，再去被驱动表中拿到相应的数据。这个过程如果有索引，也是可能走索引的。

19:为什么有的查询会很慢

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

有时候我们只查询一行记录也会很慢，主要的原因有以下几个。

• 其他线程拿了表的MDL写锁，表被锁住了，查询语句没法拿到表达mdl读锁。
• 等待刷脏页。刷脏页本身执行很快。他们可能被其他的线程给堵住。
• 等待行锁。当语句为当前读的时候，需要等待操作同一行的写锁释放。可与使用kill 线程id 命令将持有写锁线程强行杀掉，使用kill query 线程id 命令无效。
• 该语句进行了全表扫描。解决方法时建立索引。
• 回滚日志过长。

这类问题的处理方式，谁引发的这种问题，然后把它 kill 掉。

20:幻读及幻读的问题

• 幻读是指一个事务在前后两次查询同一个范围的时候，后一次查询，看到了前一次查询没有看到的行，并且这个行得是新插入的行。

• 在可重复读隔离级别下，普通的查询是快照读，不会出现幻读现象。

• 幻读仅指看到了新插入的行，看到了原先不符合规则，后来符合规则的行不算幻读。

幻读存在的问题。

• 破坏了语义。当前读的命令的含义是将所有符合条件的行锁住。

• 破坏了数据的一致性。不仅破坏了数据库内部的一致性，还破坏了数据和日志逻辑的一致性。因为binlog是按事物前后依次追加记录的，所以说幻读会破坏掉数据致性。

如何解决幻读

• 引入了间隙所，间隙锁的含义是锁住记录之间的空隙，不让其他事物在空隙中插入新的行。间隙锁存在冲突关系的，是 跟 “往这个间隙中插入一个记录 往 ”这个操作。
• 间隙锁和行锁统称为next key lock，每个next key lock是前开后闭的区间。在当前读的时候，加锁的是next key lock。Next key lock有优化规则，根据特定的情况，会退化成间隙锁或者行锁。
• 由于next key lock之间不存在互斥，所以next key lock的引入可能导致死锁。如果两个事务同事当前读一个不存在的行，会同时锁住同一个间隙，此时两个事务又同时在间隙里插入行，会相互等待对方先释放间隙锁，造成死锁。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度 。
• 加（读/写）行锁的时候也加上间隙所。也就是加上了next_key lock。

22:临时提升mysql性能

• 短连接风暴

  正常的短连接模式就是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候再重连。 如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

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

  max_connections参数，用来控制一个MySQL实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

  解决办法：

  • 第一种方法：先处理掉那些占着连接但是不工作的线程。过 kill connection 主动踢掉。

  • 第二种方法：减少连接过程的消耗。 跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables参数启动。这种方法风险极高，是我特别不建议使用的方案。 尤其库外网可访问的话，就更不能这么做了。

• 慢查询性能问题

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

  1. 索引没有设计好；

  2. SQL语句没写好；

  3. MySQL选错了索引。

  索引没有设计好： 这种场景一般就是通过紧急创建索引来解决。MySQL 5.6版本以后，创建索引都支持Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table 语句。

  语句没写好：MySQL 5.7提供了query_rewrite功能，可以把输入的一种语句改写成另外一种模式。

  MySQL选错了索引：使用查询重写功能，给原来的语句加上force index。

• QPS Q 突增问题

23:binlog和redolog如何保证数据不丢

binlog 的写入机制

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

• 一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就 涉及到了 binlog cache 的保存问题。

• 系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

• 事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

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

• write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

• fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

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

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

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

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

redo log 的写入机制

• redo log buffer 里面的内容，并不是每次生成后都要直接持久化到磁盘.

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

• 事务还没提交的时候，redo log buffer 中的部分日志有可能已经被持久化到磁盘。

• 为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

  1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache

• InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
• 事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。
• 除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo log 写入到磁盘中。

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

• 时序上 redo log 先 prepare， 再写binlog，最后再把 redo log commit。如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog来恢复的。

MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

志逻辑序列号（log sequence number，LSN）：LSN是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。 LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。

WAL 机制好处：

1. redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；

2. 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

24：MySQL是怎么保证主备一致

MySQL 主备的基本原理：

• 在状态 1 中，虽然节点 B 没有被直接访问，但依然建议你把节点 B（也就是备库）设

  置成只读（readonly）模式：

  1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；

  2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；3. 可以用 readonly 状态，来判断节点的角色

备库设置成只读了，还怎么跟主库保持同步更新呢？

• readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

主备同步流程

• 主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。

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

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

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

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

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

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

binlog 的三种格式对比

• statement 格式：记录到 binlog 里的是语句原文，可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 b。
• binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id对应的行，不会有主备删除不同行的问题。

为什么会有mixed格式的binlog？

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

• row 格式的缺点是，很占空间。

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

• 如果线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed。

越来越多的场景要求把 MySQL 的 binlog 格式设置成 row

• 好处：恢复数据。
• 执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。同理 insert 语句也是。

双 M 结构的循环复制问题

• binlog 的特性确保了在备库执行相同的 binlog，可以得到与主库相同的状态。

• 业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。

如何解决循环复制问题

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

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

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

25：如何保证主备的高可用性

主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。

主备库同步有关的时间点主要包括以下三个：

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

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

3. 备库 B 执行完成这个事务，我们把这个时刻记为 T3

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

可以在备库上执行 show slave status 命令，它的返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。

seconds_behind_master 的计算方法是这样的：

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

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

备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行seconds_behind_master 计算的时候会自动扣掉这个差值。

网络正常情况下，主备延迟的主要来源是备库接收完 binlog和执行完这个事务之间的时间差。

所以说，主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产binlog 的速度要慢。接下来，我就和你一起分析下，这可能是由哪些原因导致的。

主备延迟的原因

• 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。更新请求对 IOPS 的压力，在主库和备库上是无差别的。所以，做这种部署时，一般都会将备库设置为“非双 1”的模式。

• **备库的压力大。**一般的想法是，主库既然提供了写能力，那么备库可以提供一些读能力。或者一些运营后台需要的分析语句，不能影响正常业务，所以只能在备库上跑。

  这种情况，我们一般可以这么处理：

  1. 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。

2. 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
3. 一主多从的方式大都会被采用。因为作为数据库系统，还必须保证有定期全量备份的能力。而从库，就很适合用来做备份。

• **大事务。**为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10分钟。

  不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。删除数据的时候，要控制每个事务删除的数据量，分成多次删除。

• 大表的DDL。

可靠性优先策略

主备切换的过程如下：

1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；2. 把主库 A 改成只读状态，即把 readonly 设置为 true；

2. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；

3. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；

4. 把业务请求切到备库 B。

这个切换流程，一般是由专门的 HA 系统来完成的，称之为可靠性优先流程。

这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。

可用性优先策略

强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。 这个切换流程的代价，就是可能出现数据不一致的情况。例如先后插入顺序不一致导致主键值不一致。

• 使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者statement 格式的 binlog 时，数据很可能悄悄地就不一致了。
• 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，建议使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

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

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

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

28: **读写分离有哪些坑？**未完成

“在从库上会读到系统的一个过期状态”的现象，在这篇文章里，我们暂且称之为“过期读”。

强制走主库方案

强制走主库方案其实就是，将查询请求做分类。通常情况下，我们可以将查询请求分为这么两类：

1. 对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。
2. 对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。

这个方案有点畏难和取巧的意思，但其实这个方案是用得最多的。

Sleep 方案

主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。

这个方案的假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。

缺点：

• 等待体验不友好
• 延迟超过sleep时间也会出现过期读。

判断主备无延迟方案

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

**第二种方法，**对比位点确保主备无延迟：

Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；

Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。

如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。

33：查询大量数据的影响

全表扫描对 server 层的影响

InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。

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

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

从这个流程中，可以看到：

1. 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；
2. socket send buffer 也不可能达到 200G（默认定义 proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

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

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

Sending data 含义

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

• MySQL 查询语句进入执行阶段后，首先把状态设置成“Sending data”；然后，发送执行结果的列相关的信息（meta data) 给客户端；
• 再继续执行语句的流程；
• 执行完成后，把状态设置成空字符串。

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

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

全表扫描对 InnoDB 的影响

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

执行 show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。

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

如果一个 Buffer Pool 满了，而又要从磁盘读入一个数据页，那肯定是要淘汰一个旧数据页的。

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

InnoDB 不能直接使用这个 LRU 算法。实际上，InnoDB 对 LRU 算法做了改进。

• 在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。

• 处于young区域的数据，每次访问后，数据块会被移动到链表头。

• 当访问不存在内存中的数据是，读入后会放在old区。

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

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

  如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变（对吗？不会移动到old的头上吗）。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

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

数据库的join分两种，一种是索引join。一种是内存join。

• 当被驱动表上的筛选条件可以走索引，查询的时候，数据库会选择索引join。

  从驱动表中取出一条符合条件的行，然后在该行中提取出连接条件，根据连接条件和where语句到被驱动表中查询对应的行。然后输出结果行。

  这样的情况下，应该使用小表驱动大表，因为总的行上扫描次数是驱动表的行数+驱动表的行数*大表的索引数的高度。

  小表的行数是影响总扫描行数的关键因素，所以选择小表做驱动表。

• 当被驱动表上没有索引可以选择时，数据库采用的是buffer join。

  从驱动表上根据条件选出符合的行，将若干行同时放到join buffer中。从被驱动表中取出符合筛选条件的行，然后和join buffer中的行进行连接条件对比。符合连接条件的返回。

  这种情况下，扫描的总行数是驱动表的行数+驱动表的行数/join buffer * 被驱动表的行数。

  对扫描行数，起影响的是驱动表的行数，所以应该选择小表作为驱动表。但即便是这样，总的扫描行数也是很大的。

所以说能不能用早join两种情况。

• 如果被驱动表上有索引，则可以使用join语句。

• 如果被驱动表上没有索引。那么数据库会选择buffer join。对比于暴力join，虽然做了优化，但是总扫描行数和比较次数很大，不建议使用。

35:join的优化

当我们进行范围查询时，如果查询走的索引非主键索引，那么会得到多个id的值，然后回表拿到所需要的字段。在这种情况下，数据库会执行MRR优化。

• 本质就是把多个id值先保存在内存中，对id值进行升序排序，然后根据排序之后的id值回表拿数据。这样的话很有可能前后两个id访问的是同一个数据页，减少从内存从磁盘中把数据页调到内存。

这个优化也可以用到index join。用到MRR优化的join称为bka算法。

• 将驱动表符合筛选条件的行放入join buffer，通过join buffer一次向被驱动表传入多行，这样能减少被驱动表的访问次数。????

Buffer join的优化。

如果被驱动表是一个没有索引的冷数据表，那么对这个被驱动表做join操作会：

• buffer pool中的页面儿全都置换成冷数据库中的页面，影响内存命中率。

• 需要对比多次，消耗cpu资源。

• 被驱动表被扫描多次。消耗io资源。

优化方案：

• 将bufferjoin转换成buffer index drawn。

• 如果被驱动表上有筛选条件，将符合筛选条件多行做成一个临时表，在临时表上建立索引，这样再执行驱动表与临时表做join。能将buffer join转换成buffer index join加快处理速度。

不支持哈希 join。

• 哈希 join 就是将驱动表符合筛选条件的行保存成一个哈希文件，然后被驱动表拿出符合筛选条件的行，在哈希文件中进行哈希查找，然后把拼接结果返回给客户端。

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

union执行流程

在执行union的时候内存临时表起到了暂存数据的作用。

group by执行流程

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

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

1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；
2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；
3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。 如果大小不够用就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。

group by优化方法 -- 索引

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。

group by 的语义逻辑，是统计不同的值出现的个数。但是，同一个统计集合里的数据往往是无序的，所以就需要有一个临时表，来记录并统计结果。

在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。可以增加一个等分组的列，然后在该列上创建一个索引。之后执行就不会需要临时表，也不需要排序。

group by优化方法--直接排序

如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景， group by 要怎么优化呢？

一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按 照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

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

执行流程就是这样的：

1. 初始化 sort_buffer，确定放入一个整型字段，记为 m；
2. 扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；
3. 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利 用磁盘临时文件辅助排序）；
4. 排序完成后，就得到了一个有序数组。根据有序数组，得到数组里面的不同值，以及每个值的出现次数。

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

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

38：InnoDB与Memory的比较

内存表的数据组织结构

InnoDB 表的数据就放在主键索引树上，主键索引是 B+ 树。主键索引上的值是有序存储的。在执行 select * 的时候，就会按照叶子节点从左到右扫描，所以得到的结果里，0 出现在第一行。

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

InnoDB 和 Memory 引擎的数据组织方式是不同的：

• InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
• 而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

这两个引擎的一些典型不同：

1. InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
2. 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
3. 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
4. InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
5. InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

让内存表如何支持范围扫描

内存表也是支 B-Tree 索引的。执行 范围查询的时候的时候，优化器会选择 B-Tree 索引。

内存表为什么快

• Memory 引擎支持 hash 索引。
• 内存表的所有数据都保存在内存，而内存的读写速度总是比磁盘快。

为什么我不建议你在生产环境上使用内存表

内存表的锁：内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作。

数据持久性问题：数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。

39:自增主键增长策略

当数据库中设置了自增主键，数据库只保证主键是自增的，但并不保证是连续的。

• 数据库5.7及之前的版本，自增值是保存在内存里，没有持久化。每次重启，第一次打开表的时候，都会去找最大主键值，然后将那值加一当做当前的自增值。

• 在8.0版本之后，将自增值的变更保存在了redo log中，重启的时候依靠redo log恢复启动之前的值。

自增值的修改机制

• 当插入字段的主键为零，null或者未指定值时，就把表当前的自增值添入主键字段。
• 如果插入数据时，主键字段指定了，具体的值就用指定的值当做主键值

插入大于自增值的行后，新的字增值的生成算法是

• 从设置的自增值起始量开始，以自增步长持续叠加，找到第一个大于当前最大主键值得值当做新的自增值。

• 自增值的增加量不一定是1，可以设置成其他值,。例如想让一个库的自增值为基数，另一个库的自增值为偶数。就可以将自增值设置成2。

自增值的修改时机：

• 执行器调引擎接口写入一行，传入该行。

• 引擎发现用户没有指定主键值，获取当前表的自增值，用自增值替换主键值。

• 修改表的主键值。

• 将数据插入表，如果主键值冲突就报错。但是不会将自增值修改回之前的值。

唯一键冲突可能导致自增值不连续

事务回滚也会产生自增值不连续。

主要的原因是自增值只会增大，不会减小。如果之前的事物因为某种原因失败了，或者回滚了，那么自增值不会减小。

为什么把自增值设成不减小呢？

因为多事务并发时，每个事务申请到的自增值不一样，假设有一个事务回滚或者失败了，如果引擎把自增值减小到失败事务申请的自增值，那么在后续插入的时候，可能造成自增值冲突。

如果要避免出现冲突，可以每次申请新的自增值时，先判断表中是否已存在，这样会浪费时间。可以以把自增值的锁范围扩大，必须等到每一个事物执行完成再提交，下一个事务才能申请自增值，但是锁的力度太大，系统的并发能力下降。

所以系统采用了自增值止增大不减小的策略。

自增值的优化：

• 在5点零版本之前，自增值锁的范围是语句级别，只有当语句执行完成才释放锁，影响事务的并发。

• 5.1.22引入了一个新的策略，自增锁可以在语句申请到自增值后立刻释放，而不用等待语句执行完。如果想用这个策略，要bin log的格式设置为row。

批量插入数据的语句，有一个批量申请自增值的策略：

第一次申请自增值分配一个，用完之后再分配两个，用完之后再分配四个。这样也可能导致自增值不连续。

自增主键值不连续的原因

• 插入语句失败或者回滚
• 唯一键冲突导致插入失败
• 批量插入导致申请的自增值过多

根本原因：为了不影响并发度和效率，数据库将自增值设置成值增大，不减小。

45:自增用完

数据库的自增id

• 自定义的自增主键id用完之后会保持不变，发生冲突。报主键冲突错误。使用自定义的自增主键id时，应预估表的大小，如果表较大，应使用八个字节的big int。

• 自增row_rid。

  如果自己没有指定主键，数据库会创建一个不可见的长度为六字节的row_d作为主键。在代码实现上 row_id是一个长度为八字节的无符号长整形。但实际上留给row_id 的只有六个字节长度，共48位。

  当row_id用完时再插入新的数据。拿到以后再去后六位字节的话就是零.也就是说。如row_id达到上限后，下一个值就是零，继续循环。

  如果继续插入数据，会覆盖掉前面的值。相比于覆盖，我们更能接受的是爆冲突，所以建议使用自定义的自增id做主键。

• Xdi

  bin log和redo了个配合时，需要xdi建立起bin log 和redo log的对应关系。

  Xid是一个内存变量，重启之后就清零了，所以在同一个数据库中，不同的事物xid是可能是相同的。数据库重启之后，会重新生成新的bin log文件。保证了同一个冰log文件里xid的值是唯一的。

  如果xid的值达到上限，就会继续重零开始计数。Xid的长度是八个字节，理论上限是二的64次方减一，Xid达到上限值这种情况出现出现的概率是极低的。

• 事务id。 每一行数据都记录了更新它的事务id。每当一个事务读到一行时。判断这个数据是否可见。对于只读事务不会分配事务id的。事务id用完后会从零开始计数，导致出现脏读等现象。

线程id。

• 当线程id达到上限后，二的32次方-1，它会重置为零继续增加。
• 不会出现线程id的重复，因为数据库设计了一个唯一数组的逻辑，当发现线程id重复时，它会重新生成新的线程id。

总结一下，就是

• 自定义的自增id达到上限后。再申请时，Id值保持不变，数据库报冲突。

• 数据库自己生成的自增数据，达到理论上向后会从零开始。

  row_id达到上限后重零开始。数据会覆盖之前写的数据。6字节

  xid的值很大，发生上线的可能性很小，如果达到了上线也是从零开始。需要注意一点的是， id在数据库重启后会重新从零开始，并且重新生成bin log的日志文件。8字节

  事务id。达到上限后。会从零开始，有可能导致事故的脏读，但这个时间很长。6字节

  线程id。达到双结合会从零开始，但是系统保证了不会出现重复的线程id。4字节