MySQL 知识点整理

1. 架构

大体来说，MySQL 可以分为服务器层和存储引擎层两部分。不同的存储引擎共用一个 服务器层，也就是从连接器到执行器的部分。

1. 服务器层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
2. 存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

1.1 连接器：管理连接，身份认证

通过TCP与客户端建立连接，并管理连接，完成对用户的身份认证。

连接完成后，如果没有后续的动作，这个连接就处于空闲状态。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

短连接与长连接
短连接指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
长连接指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

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

长连接解决方案

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

1.2 查询缓存：命中则返回结果

执行过的查询语句及其结果会以 key-value 的形式，被直接缓存在查询缓存中。key 是查询的语句，value 是查询的结果。

当MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条查询语句。
如果能直接找到 key，完成权限认证后，将value返回给客户端。
如果查询语句不在缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

当MySQL拿到的是一个更新请求，会将该表的查询缓存全部清空。

查询缓存弊大于利
虽然查询命中缓存，就不需要执行后面的复杂操作，就可以直接返回结果，效率很高。但是查询缓存弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。对于不经常更新的数据来说，使用缓存还是可以的。

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

1.3 分析器：词法分析，语法分析

对语句进行词法和语法分析。

1. 词法分析：分析关键词，表名，列名等。
2. 语法分析：根据语法规则，判断这个 SQL 语句是否满足 MySQL 语法。

1.4 优化器：执行计划生成

经过了分析器，MySQL 就知道你要做什么了。

优化器会按MySQL认为的最优的执行方案去执行，制定执行计划。比如在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

1.5 执行器：权限认证，操作引擎，返回结果

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，会校验该用户有没有权限，如果没有权限，就会返回错误信息；如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

1.6 存储引擎：存储数据，提供读写接口

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

MyISAM与InnoDB区别

区别点	MyISAM	InnoDB
是否支持行级锁	表级锁	支持表级锁和行级锁，默认为行级锁
是否支持事务和崩溃后的安全恢复	强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。	提供事务支持事务，外部键等高级数据库功能。 具有事务、回滚和崩溃修复能力的事务安全（ACID）型表
是否支持外键	不支持	支持
是否支持MVCC	不支持	支持。应对高并发事务，MVCC比单纯的加锁更高效；MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作；MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现；各数据库中MVCC实现并不统一。
BTree索引B+Tree实现	非聚簇索引	聚簇索引

1.7 查询SQL执行流程

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

1. 连接器完成身份认证，创建连接成功。
2. 查询缓存，如果命中，进行权限验证后返回结果；如果未命中，到分析器。
3. 分析器完成词法和语法分析，调用 precheck 验证权限（如果有触发器，只能在运行中才能确认权限）。
4. 优化器会按MySQL认为的最优的方式执行，确认执行计划。
   这里既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。具体如何做，由优化器决定。
5. 执行器先判断对表t1，t2是否有查询权限。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口，返回引擎的执行结果。
6. InnoDB在内存Buffer Pool中判断行所在的数据页是否存在，不存在再从磁盘加载出来到数据页。完成join等操作后将结果返回给执行器。

1.8 更新SQL执行流程

update T set c=c+1 where ID=2;

1. 连接器完成身份认证，创建连接成功。
2. 清空查询缓存中表T的缓存。
3. 分析器完成词法和语法分析。
4. 优化器会按MySQL认为的最优的方式执行，确认执行计划。
5. 执行器先判断对表T是否有更新权限，再调用存储引擎接口。

1.8.1 执行器和 InnoDB 引擎内部流程

1. 执行器存储引擎取出ID为2的该行。ID 是主键，引擎直接用树搜索找到这一行。如果所在的数据页在内存Buffer Pool中，直接从内存中获取返回给执行器；如果没有就要去磁盘加载该页到内存Buffer Pool中，返回该行数据。
2. 执行器拿到引擎给的行数据，将c加1后，得到新行，再调用引擎接口写入这行新数据。
   这部直接更新内存，然后记录到redo log buffer，此时 redo log 处于 prepare 状态，告知执行器执行完成了，随时可以提交事务。
3. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
4. 执行器调用引擎的提交事务接口，引擎把redo log buffer写到redo log中，修改为提交（commit）状态，更新完成。

更新SQL涉及到服务层的日志模块binlog和InnoDB中的日志模块redo log，并采用WAL 技术。

1.9 binlog 与 redo log

1.9.1 WAL

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。就比如上面更新SQL的执行流程，都先写日志，再写磁盘。

1.9.2 binlog

binlog记录了对MySQL数据库执行更改的所有操作，它是 MySQL 的服务器层实现的，所有引擎都可以使用。它是逻辑日志，以追加写的方式记录。

作用

1. 恢复：某些数据的恢复需要二进制日志
2. 复制：用于主从复制
3. 审计：通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

1.9.3 redo log

redo log，重做日志，它保证了事务的持久性。

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

1. write pos：当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
2. checkpoint ：当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示rego log满了，这时候不能再执行新的更新，需要先记录到磁盘中，把 checkpoint 推进一下。

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

1.9.4 binlog和redo log区别

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

1.9.5 二阶段提交

redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。都会存在问题。

假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1. 先写 redo log 后写 binlog。
   假设写完 redo log 后，机器挂了，binlog 日志没有被写入。那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
2. 先写 binlog 后写 redo log。
   假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，

1.9.6 崩溃恢复

在两阶段提交的不同时刻，MySQL 异常重启的崩溃恢复。

对于 1.8.1 执行器和 InnoDB 引擎内部流程：

1. 如果是3~4，redo log处于prepare状态，MySQL异常重启。
   由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
2. 如果是4~5，redo log 处于prepare状态，binlog也写了，MySQL异常重启。
   如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
   如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：如果完成，则提交事务；否则，回滚事务

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

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

2. 事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

2.1 特性

四大特性（ACID）

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

2.2 错误类型

1. 脏读（dirty read）：一个事务读数据，并且修改了数据，但是未提交；另一个事务读数据，即旧数据；前一个事务提交，就脏读了。
2. 不可重复读（non-repeatable read）：一个事务中多次读同一个值，都是相同的；另一个事务修改了这个值并提交。前一个事务读到的值变了。
3. 幻读（phantom read）：一个事务中多次查询，都是相同的；另一个事务修改了并提交。前一个事务查询多了或少了。

2.3 隔离级别

1. 读未提交（read uncommitted）： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. 读提交（read committed）： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3. 可重复读（repeatable read）： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4. 串行化（serializable）： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

2.4 事务隔离的实现

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

1. 读未提交（read uncommitted）： 直接返回记录上的最新值，没有视图概念。
2. 读提交（read committed）： 这个视图是在每个 SQL 语句开始执行的时候创建的。
3. 可重复读（repeatable read）： 这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
4. 串行化（serializable）： 直接用加锁的方式来避免并行访问。

2.5 事务的启动方式

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。
   意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
3. set autocommit=1，这个命令会将这个线程的自动提交开启。
   在这情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，
4. INSERT/DELETE/UPDATE本身就是一个事务，语句完成的时候会自动提交。

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动，才会创建一致性视图。

start transaction with consistent snapshot 这个命令会马上启动一个事务，并创建一致性视图。

一致性视图与视图

1. 视图：一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
2. 一致性视图（consistent read view）：InnoDB 在实现 MVCC 时创建的，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

2.6 InnoDB 默认可重复读

InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统是不同的。

所以说 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

2.7 可重复读（MVCC + undo log）

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
不同时刻启动的事务会有不同的一致性视图，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

对于后面的一致性视图，要获取之前的值，就必须将当前值执行的回滚操作后才能得到。

一致性视图在不需要的时候才删除，即当系统里没有比这个回滚日志更早的一致性视图的时候。系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

2.7.1 避免长事务

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

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

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

2.8 MVCC

MVCC全称是： Multiversion concurrency control，多版本并发控制。提供并发访问数据库时，对事务内读取的到的内存做处理，用来避免写操作堵塞读操作的并发问题。在事务隔离级别为读已提交，可重复读时使用。

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

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

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

2.9 undo log

undo log，回滚日志，用于保证事务的原子性。MVCC下的非锁定读。

事务开始之前，将当前事务版本生成 undo log，undo log 也会产生 redo log 来保证 undo log 的可靠性。

当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息，从而决定是否可以清理 undo log 的日志空间。

undo log 存储的是逻辑格式的日志，保存了事务发生之前的上一个版本的数据，可以用于回滚。当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着 undo 链找到满足其可见性的记录。

5.7之前undo log是存在共享表空间，也即 ibdatafile 文件中。当数据库中发生一些大的事务性操作的时候，要生成大量的 undo log 信息，这些信息全部保存在共享表空间中，因此共享表空间可能会变得很大。

5.7之后有了独立的undo表空间。

2.10 查询逻辑

有多个事务的时候，每个事务的id，即row trx_id是不同的。每个事务在执行前会记录当前的活跃事务ID和自身事务ID添加到自身的视图数组中，查询行数据的时候，会根据事务ID依次在视图数据中判断后选择值。

1. row trx_id大于视图数组最大值，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
2. row trx_id小于视图数组最大值，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
3. row trx_id在视图数组中，如果row trx_id为自身就可以使用；否则，表示这个版本是由还没提交的事务生成的，就不可见。

这样能保证，即使该行被修改过，同一个事务中看到这行数据的结果都是一致的，所以称之为一致性读。

2.11 更新逻辑

当更新数据的时候，不能在历史版本上更新了，否则前面事务更新的数据就丢失了。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。除了 update 语句外，select 语句如果加锁，也是当前读。

2.12 总结

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据；而当前读，总是读取已经提交完成的最新版本。

3. 索引

如果未加索引，MySQL会扫描所有行，把所有行所在的数据页都加载到内存中。如果还要求排序，就会生成一张内存临时表，把表排序完再将相应的行返回给客户端。如果内存临时表的大小大于 tmp_table_size 的值（默认为 16 M），内存临时表会转为磁盘临时表，性能会更差。

如果加了索引，索引本身有序，根据索引排序的话就不会生成临时表，也不用在额外排序，提升了性能。

作用

1. 减少扫描行数
2. 避免排序
3. 将随机 IO 变成顺序 IO

分类

1. 按实现：哈希索引，B树索引
2. 按类型：唯一索引，普通索引，全文索引，空间索引

3.1 哈希索引与B树索引

3.1.1 哈希索引

哈希索引是基本散列表实现，将key经过哈希函数转换映射到散列表对应的位置上，在等值查询时效率很高，范围查询还是会全表查。

因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

3.1.2 B树索引

BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

MyISAM
B+树叶节点的data域存放的是数据记录的地址，索引文件和数据文件是分离的。在索引检索的时候，首先搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB
数据文件本身就是索引文件。表数据文件本身就是按B+树组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，称为主键索引，也称为“聚簇索引”。

3.2 InnoDB 的BTree索引模型

B+树是N叉树，通过降低树的高度，适配磁盘的访问模式，减少单次查询的磁盘访问次数。查询时顺着根节点层层往下查找，找到目标数据后MySQL会将数据所在的数据页都加载到内存中。（即磁盘预读，程序局部性原理：读到了某个值，很大可能这个值周围的数据也会被用到，干脆一起读入内存）。
叶子节点通过指针相互指向连接，能有效减少顺序遍历时的随机IO。叶子节点是按索引的顺序排序好的，意味着根据索引查找和排序的时候不会生成临时表。

3.2.1 关于叶子节点的大小

B+树中叶子节点一页或页的倍数最为合适，防止资源浪费。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。根据叶子节点的内容，分为主键索引（聚簇索引）和非主键索引（二级索引）。主键索引的叶子节点存的是整行数据，非主键索引的叶子节点内容是主键的值。

当使用非主键索引的查询，需要带着主键值去主键索引中搜索数据，这个过程称为回表。

3.2.2 索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。
如果插入的数据在一个数据页的中间，就需要挪动数据；很有可能数据页满了，就需要申请一个新的数据页。这个过程叫做页分裂。
如果相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
使用自增主键能保证有序插入。

3.3 唯一索引与普通索引

唯一索引值不能重复，普通索引值可重复。

3.3.1 查找过程

通过 B+ 树从树根开始，按层搜索到叶子节点。

对于普通索引，查找到满足的第一个记录后，需要继续往下查找，直到碰到第一个不满足的记录。
对于唯一索引，由于索引定义了唯一性，查找到第一个满足的第一个记录后，就会停止记录查找。

虽然两者查询方式不同，但是查询效率相差不大。
InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

3.3.2 更新过程

当数据页需要更新的时候，如果在内存中直接更新。如果不在内存中，InnoDB会先将更新操作缓存到change buffer中。下次查询这个数据页的时候，将数据页读入内存，执行change buffer中与这个页有关的操作。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

对唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，因此不能使用change buffer。
对于普通索引就可以使用，如果数据页不在内存中，可以将更新记录在 change buffer，就算语句执行就结束了。

3.4 全文索引

在基于文本的列（CHAR， VARCHAR或TEXT列）上创建的， 以帮助加快对这些列中包含的数据的查询和DML操作

3.5 联合索引

联合索引又叫复合索引，即在两个或更多个列上创建的索引。

3.5.1 覆盖索引

当使用非主键索引的查询，需要带着主键值去主键索引中搜索数据，这个过程称为回表。
当需要查找的字段在非主键索引上就能找到，那么就不用再回表了。
对于高频的请求，可以在这用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

覆盖索引只是特定于具体查询语句而言的联合索引。一个联合索引对于某个查询语句，通过索引就可以直接获取查询结果，而不再需要回表查询，就称该联合索引覆盖了这条查询语句。

如果你想要通过索引覆盖查询多列，那么需要给需要的列建立一个多列索引。如果带查询条件，where条件要求满足最左前缀原则。

3.5.2 最左匹配原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

3.5.3索引下推

最左匹配原则遇到（>、<、between、like左匹配)等就不能再进一步匹配了。
MySQL 5.6 之前，只能开始一个个回表，到主键索引上找出数据行，再对比字段值。
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

4. 锁

按加锁范围：全局锁、表级锁和行锁

4.1 全局锁

全局锁是对整个数据库实例加锁。

FLUSH TABLES WITH READ LOCK

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

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

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

4.2 表级锁

表级锁包括表锁，元数据锁和意向锁

4.2.1 表锁

对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。但是锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

lock tables … read/write

表锁除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

4.2.2 元数据锁

元数据锁（metadata lock)，MDL 不需要显式使用，在访问一个表的时候会被自动加上。保证读写的正确性，防止并发事务下对表结构的修改。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
MDL的读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查；读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

在事务中的 MDL 锁，在语句执行开始时申请，需要等到整个事务提交后才会被释放。

4.2.3 意向锁

当需要的资源被其他锁占有的时候，会在表上加一个意向锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。

意向锁

1. 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。
2. 意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

4.3 行锁

对当前操作的行进行加锁，减少了数据库操作的冲突。加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。数据库的增删改操作默认都会加行锁。

行锁

1. Record Lock（行锁）: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
2. Gap Lock（间隙锁）: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
3. Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

间隙锁和 next-key lock 的引入，解决了幻读的问题，但同时也带来了一些问题。间隙锁可能会导致同样的语句锁住更大的范围，影响了并发度。

4.3.1 两阶段锁

可重复读隔离级别遵守两阶段锁协议。在 InnoDB 事务中，行锁是在需要的时候才加上的，但是要等到事务结束时才释放。这个就是两阶段锁协议。

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

4.3.2 行锁加锁规则

1. 加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间。
2. 查找过程中访问到的对象才会加锁。
3. 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

4.4 乐观锁与悲观锁

4.4.1 乐观锁

自己实现。可以通过添加version字段，更新时更新version，version加1。
在写入前查询出version，当要写入时判断version是否还和库中相等，相等说明这段期间没有其他程序操作，就执行，并将version加1；如果不等，就不进行更新操作。

4.4.2 悲观锁

数据库自带，又分为共享锁（读锁，S锁）和排它锁（写锁，X锁）。
持有共享锁，在事务未提交前可进行读操作

begin;
SELECT * from city where id = "1"  lock in share mode;

只有持有排它锁，才能进行写操作。数据库的增删改操作默认都会加排他锁。

... for update

4.5 死锁和策略

双方都需要对方的资源，会导致死锁。

策略

1. 直接进入等待，直到超时。
   这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。。在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s。
2. 主动死锁检测。
   发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。参数 innodb_deadlock_detect 的默认值本身就是 on。
   主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

5. InnoDB存储引擎

5.1 Buffer Pool

MySQL以数据页为单位从磁盘中读取数据，InnoDB会缓存访问的数据页。

Buffer Pool 是一个以页为元素的链表，基于 LRU（least recently used） 的算法变体来管理内存。

Buffer Pool中的内存页的状态

1. 还没有使用的；
2. 使用了并且是干净页；
3. 使用了并且是脏页。

5.1.1 脏页与干净页

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

5.2 Change Buffer

对不在缓冲池中的二级索引页的更改将缓存在更改缓冲区中，仅对二级索引（普通索引）有效。

当数据页需要更新的时候，如果访问的数据页在内存中就直接更新。如果不在内存中，InnoDB会先将更新操作缓存到 Change Buffer 中。下次查询这个数据页的时候，将数据页读入内存，执行 Change Buffer 中与这个页有关的操作。

Change Buffer 实际上它是可以持久化的数据。也就是说，Change Buffer 在内存中有拷贝，也会被写入到磁盘上。将 Change Buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。

除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 Change Buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

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

5.3 merge 与 purge

1. merge：Change Buffer -> Buffer Pool
   定期将Change Buffer中对二级索引页的修改合并到Buffer Pool中
2. purge：Buffer Pool -> Disk
   定期将内存中更新的索引页写到磁盘

5.4 flush

flush就是刷新脏页，将更新的数据页写到磁盘的过程。刷新脏页耗时，通过参数innodb_io_capacity，提前告诉InnoDB磁盘的能力。

5.4.1 InnoDB的flush策略

1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3. MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。
4. MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

5.5 Adaptive Hash Index

B树索引不管在磁盘里，还是被加载到内存后，都是 B+ 树，B+ 树的查找次数取决于树的深度。在生产环境中，B+树的高度一般为3~4层，故需要3~4此查询。

InnoDB引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index）。它是数据库自优化，无需人为调整。

AHI是通过Buffer Pool中的B+树的页构造而来，索引建立的速度很快。Innodb会自动根据访问的频率和模式来自自动的为某些热点数据建立哈西索引。启用AHI，读取和写入速度可以提升2倍，辅助索引的连续操作性能可以提高5倍。

5.6 Log Buffer

Log Buffer用于保存要写入磁盘上的日志文件的数据，会存储redo log和undo log。

Log Buffer的内容定期刷新到磁盘。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将redo log数据写入磁盘。

5.7 表空间

表空间包括：系统表空间（The System Tablespace），表文件表空间（File-Per-Table Tablespaces），通用表空间（General Tablespace），Undo 表空间（Undo Tablespaces）和临时表空间（Temporary Tablespaces）

5.7.1 系统表空间（The System Tablespace）

系统表空间包含：InnoDB数据字典，Doublewrite Buffer，Change Buffer和Undo logs的存储区

5.7.1.1 InnoDB数据字典

InnoDB数据字典由内部系统表组成，这些系统表包含用于跟踪对象（例如表，索引和表列）的元数据。 元数据实际上位于InnoDB系统表空间中。 由于历史原因，数据字典元数据在某种程度上与InnoDB表元数据文件中存储的信息重叠

5.7.1.2 Doublewrite Buffer

写入到磁盘过程中如果MySQL崩溃，就会使磁盘中的数据不完整，所以MySQL在写数据之前，会先把数据写到Doublewrite Buffer，写完后在开始写磁盘。

Doublewrite Buffer 可以理解为是一个备份（recovery），万一真的发生崩溃，就可以利用 它来修复磁盘里的数据。

尽管数据被写入两次，但双写缓冲区不需要两倍的I / O开销或两倍的I / O操作。只需调用一次fsync()操作系统，数据就可以按较大的顺序块写入Doublewrite Buffer。

5.7.2 表文件表空间（File-Per-Table Tablespaces）

包含单表的数据和索引，在文件系统中存储在一个文件中。

5.7.3 通用表空间（General Tablespace）

使用CREATE TABLESPACE语法创建的共享表空间

5.7.4 Undo 表空间（Undo Tablespaces）

存储undo log

5.7.5 临时表空间（Temporary Tablespaces）

保存用户创建的未压缩的临时表

6. 大表优化

6.1 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。

6.2 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

6.3 垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

6.4 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

6.4.1 数据库分片常见方案

1. 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
2. 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现

6.4.2 全局唯一id

1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
4. Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
5. 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，也需要依赖关系数据库、Zookeeper等中间件。

7. 调优

7.1 选择类型原则

1. 更小的通常更好
   一般情况下，应该尽可能选择正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘空间、内存，并且处理时需要的CPU周期更少。但是，在选择更小数据类型时，一定不要低估存储值的范围，因为后期修改数据类型及长度是一件非常痛苦、耗时的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。
2. 简单就好
   简单的数据类型操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校队规则(如：排序规则)使得字符比较比整型比较更复杂。
3. 尽量避免用NULL
   NULL是在常见不过的值了，通常都习惯对某些字段设置默认值为NULL，这其实是一种非常不好的习惯。如果查询中的字段值恰巧是设置的NULL值，对MySQl来说更难优化，因为可为NULL的字段使得索引、值比较都更复杂。NULL值不能进行索引，影响索引的统计信息，影响优化器的判断。复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
4. 使用可变长度字符串 varchar
5. 时间类型使用timestamp
6. text和blob大量删除有性能问题

7.2 创建索引原则

1. 最左前缀匹配原则。
   非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. =和in可以乱序。
   比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3. 尽量选择区分度高的列作为索引。
   区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例。区分度越高的列，越容易锁定更少的记录，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
4. 索引列不能参与计算，避免隐式转换
   保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。避免隐式转换，保证字符集相同。
5. 尽量的扩展索引，不要新建索引。
   比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

7.3 参数

参数名	策略
sync_binlog	设置成 1 的时候，每次事务的 binlog 都持久化到磁盘
innodb_flush_log_at_trx_commit	设置成 1 的时候，每次事务的 redo log 都直接持久化到磁盘
innodb_change_buffer_max_size	Change Buffer 的大小，这个参数设置为 50 的时候，表示 Change Buffer 的大小最多只能占用 buffer pool 的 50%
innodb_max_dirty_pages_pct	脏页比例上限，默认值是 75%
innodb_io_capacity	提前告诉InnoDB磁盘的能力，合理地设置的值
tmp_table_size	如果应用中有很多group by、distinct等语句，同时数据库有足够的内存，可以增大tmp_table_size（max_heap_table_size）的值，以此来提升查询性能。
sort_buffer_size，number_of_tmp_files	如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

7.4 其他

子查询使用join代替，on 的列使用索引

like优化
前缀匹配，添加一个前缀，将table类型添加到前面

select优化
指定需要的字段，不用*

使用自增主键，有序，区分度高，避免页分裂

查看慢查询日志

information_schema 库的 innodb_trx 表看长事务

使用强制索引 force index(a)

使用count(*)

8. 常用命令

连接命令

mysql -h$ip -P$port -u$user -p

查看MySQL提供的所有存储引擎

show engines;

查看MySQL当前默认的存储引擎，我们也可以通过下面的命令查看默认的存储引擎。

show variables like '%storage_engine%';

查看表的存储引擎

show table status like "table_name" ;

查看事务隔离级别

SELECT @@tx_isolation;

参考：
MySQL 5.7 手册
面试突击-MySQL
一条SQL语句在MySQL中如何执行的
MySQL 技术内幕 InnoDB存储引擎
MySQL 的 InnoDB 存储引擎是怎么设计的？
MySQL实战45讲

索引
数据库索引
每次面试官让帅地讲一讲索引，我都用这一套连招给他致命的四击
Mysql的覆盖索引和联合索引的区别在哪里？
MySQL 关于唯一索引和普通索引的抉择
MySQL索引原理及慢查询优化

锁
Mysql锁机制简单了解一下
大厂面试官必问的Mysql锁机制
谈谈mysql的悲观和乐观锁
MySQL 常用锁和 MVCC 总结

调优
盘点开发中那些常用的MySQL优化
数据库调优
MySQL性能优化(七)：MySQL执行计划，真的很重要，来一起学习吧

日志
MySQL中的几种日志了解
mysql MVCC Undo Redo
MySQL 中 redo log、undo log、binlog 的总结

本文地址：https://blog.csdn.net/qq_36160730/article/details/107389116