MySQL

关系型数据库，插件式的存储引擎，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

MySQL的架构分为两层

1. Server: Server层负责建立连接，分析，执行SQL。大多数核心部件在此实现，包括连接器，缓存查询，解析器，预处理器，执行器等。还有所有的内置函数和所有的跨引擎的功能如视图，都在Server层实现
2. Engine: 引擎层负责数据的存储和提取，索引也是有引擎层实现的

执行一条查询语句的流程

1. 连接器建立连接：与客户端进行 TCP 三次握手，校验客户端提供的用户名和密码，读取该客户端的权限
2. 查询缓存：如果要查询一个字段，引擎会先去查询缓存，如果命中，则会直接返回给客户端。但是在MySQL8.0缓存直接被取消了，因为对于更新频繁的表来说，缓存命中率很低。这个缓存不是buffer pool
3. 解析：词法分析：解析器会根据输入字符串构建SQL语法树，提取出表明，字段名，SQL类型等。语法分析：根据词法分析的结果，根据语法规则判断是否满足SQL语法
4. 预处理：检查 SQL 查询语句中的表或者字段是否存在，将 select * 中的 * 符号，扩展为表上的所有列；
5. 优化：确定SQL语句的执行方案，比如使用哪个索引更快
6. 执行：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端

事务的ACID

特性	描述	实现
原子性（Atomicity）	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生，如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态	undo log回滚日志
一致性（Consistency）	事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。就是事务执行前后，数据的改变都是合法，且各方数据都是一致的	由其他三个特性共同保证
隔离性（Isolation）	事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。	MVCC，锁机制
持久性（Durability）	事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。	redo log bin log支持的持久化

范式

1NF(第一范式)

属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式

如果关系表中的属性不可再细分且属性列不可重复，该关系满足第1范式

2NF(第二范式)

2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键

第二范式要求关系表中所有数据都要和关系表的主键有完全函数依赖（即属性不能只和主键有部分依赖关系）

3NF(第三范式)

3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求

每一个非主属性既不部分依赖于码也不传递依赖于码

• 1NF：属性不可再分。
• 2NF：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。
• 3NF：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖

四种隔离级别

1. read uncommitted：所有事务都可以看见未提交的结果，产生脏读
2. read committed：一个事务从开始到提交前，任何数据改变都是不可见的，产生不可重复读问题
3. repeatable read：MySQL默认的隔离级别，解决不可重复读问题，保证同一事物的多个实例在并发读取事务时，会读取到同样的数据行，产生幻读问题（InnoDB的MVCC解决了幻读问题）
4. serializable：最高级别的隔离，强制事务排序，不可能相互冲突，其实就是加锁，效率低

存储引擎

MyISAM和InnoDB的区别是什么

1. InnoDB：支持事务，支持外键，聚集索引：文件存储于主键索引的叶子节点上，所以主键索引效率很高，但是辅助索引需要进行回表，并且主键不能过大，因为辅助索引也会存储主键，所以过大的主键会影响索引的大小。最小粒度锁为行锁
2. MyISAM：不支持事务，不支持外键，非聚集索引，索引保存的是数据文件的指针，主键索引和辅助索引独立。会创建一个单独的变量保存整个表的行数，读取表行数的速度更快。最小粒度锁为表锁，并发性能不好。

基础架构

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 SQL 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

• 优化器： 选择MySQL 认为最优的执行方案。

• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

• 日志模块：在server层是binlog归档日志模块

• 插件式存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎

• 查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎

• 更新语句执行流程如下：分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

索引

为什么要有索引

一般的应用系统读写比例大约在10：1左右，而且数据的插入和更新出现性能问题的几率小于查询。在生产环境中，最容易出现问题的就是查询操作，因此对查询性能的优化就显得十分重要。

索引的本质就是一个满足某种查找算法的数据结构，常见的有BST，AVL，红黑树，Btree，B+树等等，这些结构以某种方式指向数据（索引结点指向数据记录物理地址的指针）

1. 索引大大减少了服务器需要扫描的数据量，提高了检索效率
2. 避免排序，减少CPU消耗
3. 将随机IO转换为顺序IO

索引存储于外存，所以索引的查询也需要磁盘IO开销，访问外存的时间成本大约是内存的十万倍

虽然提高了查询速度，但是在对实体表进行更新时，索引也需要进行相应的维护更新

HASH索引

Memory引擎默认Hash索引

等值查询很快，计算的hash值与对应的行指针一并存入表中，哈希碰撞的元素以链表的形式相连。也不支持排序，以及模糊查询，并且由于散列算法，键值对的存储是无序的，所以不支持范围查询。

B+树索引

InnoDB，MyISAM默认是B+树

考虑到IO时十分高昂的操作，且数据库动辄百万级数据量，所以当一次IO时，常常以页作为单位来读取数据，当读取一页的数据至内存缓冲区时，实际上才发生了一次IO，所以控制IO的次数对于索引效率的提升，至关重要。

B+树的特点：

1. 除叶子结点外的子节点：只起到索引的作用，仅存储指针，不存储信息
2. 所有信息存储于叶子结点，所有叶子结点在底部链接形成一个双向链表（范围查询）

在InnoDB中，叶子节点的容量默认为一页16KB，磁盘预读，预读其实就是利用了局部性原理，具体过程是：对于每个文件的第一个读请求，系统读入所请求的页面并读入紧随其后的少数几个页面（通常是三个页面），这时的预读称为同步预读。4kb(磁盘一页的大小) + 12kb(预读三个页面) = 16kb

叶子节点结构(聚簇索引为例)：页目录（主键）+用户数据区域（单向链表，通过主键排序，在插入数据的时候便会排序）

非叶子节点结构：页指针+指向的该页的最小主键值

MYSQL索引分类

1. 主键索引：一张表一个主键索引，通常与表一起创建。
2. 唯一索引：如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，如果字段的值已经出现过了，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
3. 普通索引：建立在普通字段上的索引，唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREc）或排序条件（ORDERBY）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
4. 前缀索引：字符类型字段的前几个字符建立，可以覆盖多个数据列，遵循最左匹配原则，可能会因为order by失效。

普通索引和唯一索引有什么区别？

概念上的不同：普通索引可以重复。而唯一索引和主键一样，不可以重复，但在一张表里面只能有一个主键，不能为空，唯一索引可有多个。唯一索引可有一条记录为null。在学校，一般用学号做主键，身份证号作为唯一索引

查询实现的不同：若查询语句为

select id from T where k=4

普通索引：查找到满足条件的第一个记录后，继续查找下个记录，直到碰到第一个不满足k=4的记录。

唯一索引：查到第一个满足条件的，就停止搜索。

若重复数据很多，普通索引多了一次“查找和判断下一条记录”的操作，可能会多次IO，但是总体性能其实差别不大

更新性能不同：往表中插入一个新记录，InnoDB会有什么反应？

若在内存中，普通索引直接插入，而唯一索引会判断一次是否有冲突，再插入。判断的性能消耗可以不计

若不在内存中，普通索引会将数据记录在change buffer；唯一索引会将数据页读入内存再插入。众所周知数据库的IO成本很高，所以普通索引更新数据的性能是要更优的。

总结：若更新性能优先级更高，选择普通索引。

主键索引(聚簇索引)

辅助索引(非聚簇索引)

同样是B+树，以非主键而是以自定义规则的索引，以满足不同的查询需求，属于非聚集索引。

叶子节点的用户数据区不再存储完整记录，而是存储主键+部分记录。

所以使用辅助索引有时候需要进行回表，即部分记录无法满足查询需求，需要使用主键来重新到主键索引查找。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引

相反，如果只使用一个字段创建索引，就叫单列索引

索引失效会出现在什么情况

1. 对索引使用左或者左右模糊匹配，例如%like，会进行全盘扫描。如果使用like%则会正常使用索引，因为B+树会根据最左的字段开始构建B+树索引，所以若使用%like，引擎会无法知道该从哪个索引值开始比较，所以就只能通过全盘扫描的方式查询。但是也有特殊情况，如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树
2. 对索引字段使用函数，索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。当然在8.0之后，增加了函数索引，此时就可以使用索引了
3. 对索引字段进行表达式计算，原因同函数一样
4. 对索引进行隐式类型转换： MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，而这里相当于情况二，会使用一个函数将字符串进行转换，然后再进行比较。
5. 未遵循最左匹配原则
6. 在where中使用OR：如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
7. 引擎估算走辅助索引的时间反而比全表扫描的时间更长

什么是最左匹配原则？

对于复合索引，若有字段123，若查询时省略字段1，则无法使用索引。

因为数据库依据联合索引最左的字段来构建 B+ 树，叶子节点的排序是以字段123的顺序进行的，只有先确定了前一个（左侧的值）后，才能确定下一个值。a有序，b才能有序，若a省略，则无法有序查找bc。

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据

非聚簇索引的优缺点？

优点：更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

缺点：

1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

聚簇索引的优缺点？

优点：聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据

缺点：

1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的

MYSQL默认使用B+树，为啥要用B+树，不用B树？

因为两者都是存储于磁盘，而IO是花销很大的操作，InnoDB每次申请磁盘空间时都会申请若干条连续的磁盘块来组成一页，并放入内存，读取索引，放回磁盘，不断重复，直到找到数据。

所以如果每次申请到的数据都能有助于定位到所需数据，这将会减少IO次数，提高查询效率。

对于B树，因为B树的每一个结点都会存储键，指针和数据，每个磁盘块的信息存储能力有限，树的高度也会更高，增加了IO次数，所以B树的查询效率波动很大。

而B+树的非叶子结点只存储键，所以B+树的非叶子结点可以存储更多的信息，降低了树高度，平均一次IO可以获取更多索引，所以B+树更适合外存索引，且查询效率更稳定

其中在 MySQL 底层对 B+ 树进行进一步优化：

叶子节之间是双向链表，节点内部是单向链表，且在链表的头结点和尾节点也是循环指向的。（范围查询的关键）

为什么不用红黑树？

无论是二叉树还是红黑树，都会因为树的深度过深而导致IO次数变多，效率不高

InnoDB一颗高度为3的B+树可以存放多少行数据？

InnoDB的一页大小为16k，若一行数据的大小为1k，那么可以存储16行数据

若主键ID为bigint型，8字节，指针为6字节，总大小为14字节，那么一页可以存储1170个指针

所以数据量大约为1170 * 1170 * 16= 大约两千万

锁

按锁的属性分

共享锁：即读锁

排他锁：即写锁

按锁的粒度分

行级锁：锁住一行或者多行记录

表级锁：给整个表加锁

页级锁：介于行级锁和表锁的一种锁，一次锁定相邻的一组记录

记录锁：行级锁的一种，锁住一条记录，避免数据在查询时被修改的不可重复读问题

间隙锁：行级锁的一种，只出现在Repeatable read的事务中，解决了幻读的问题

临键锁：InnoDB的行锁默认算法，就是记录锁和间隙锁的结合版，会锁住查询的记录，同时也会锁住范围内的所有间隙空间

按锁的状态分

意向共享锁

意向排他锁

MySQL的主从同步

MySQL内建的复制功能是构建大型，高性能应用程序的基础。

将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将MySQL的某一台主机的数据复制到其它主机（slave）上，并重新执行一遍来实现。

复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。

主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循坏，这些日志可以记录发送到从服务器的更新。

当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知的更新。

MySQL支持哪些复制

1. 基于语句的复制：在主服务器上执行的sql语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。
2. 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从MySQL 5.0开始支持
3. 混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制

为什么需要主从同步

1. 若出现锁表不能读的情况，此时可以使用主从复制，让主库负责写，从库负责读，这样就不影响业务的正常运行
2. 当IO频率越来越大时，业务量越来越大时，单机已无法满足，此时多库的处理可以提高IO的性能

MySQL的乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果冲突，则返回给用户异常信息，让用户决定如何去做。乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。

乐观锁采取了更加宽松的加锁机制。也是为了避免数据库幻读、业务处理时间过长等原因引起数据处理错误的一种机制

乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。乐观锁的实现：

CAS+版本号控制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会 +1。当线程 A 要更新数据时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。

MySQL的行级锁

记录锁

记录锁是封锁这一条记录，阻止其他事务插入，更新，删除这一条记录

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

间隙锁

间隙锁是封锁索引记录中的间隙，或者第一条索引记录之前或者之后的范围的数据，间隙锁面向的是范围，所以是左开右开区间

产生条件：RR隔离级别

对唯一索引进行操作：

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
2. 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

对普通索引进行操作：

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

例如表中有6条记录，其中索引d分别为0，5，10，15，20，25

执行 select * from t where d=5 for update，就不止是给数据库中已 有的6个记录加上了行锁，还同时加了 7 个间隙锁。、

比如（0，5）区间内被加上间隙锁，这个区间内无法插入新记录，一定程度上避免了幻读，但是并没有完全解决幻读。

在RC级别下，间隙锁会失效

临键锁

间隙锁和行锁合称临键锁（ next-key lock），每个 next-key lock 是前开后闭区间，同样只在RR级别下有效

1. 若对存在的记录加锁，则会锁住前后两个区间的内容
2. 若对不存在的记录加锁，则会锁住该记录所在区间的内容

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待排队阶段。

MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁

插入意向锁是一种特殊的间隙锁，它锁住了一个点，这个点也相当于一个区间

MySQL的行级加锁机制

InnoDB 引擎支持行级锁，而 MyISAM 引擎并不支持行级锁

唯一索引等值查询

走主键索引查询单条记录，比如 where id = 1

若记录存在：由于主键具有唯一性，所以其他事务插入 id = 1 的时候，会因为主键冲突，导致无法插入 id = 1 的新记录，也无法删除该记录。这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。所以此时临键锁会退化成记录锁。

若记录不存在：若id=1的记录不存在，我们只需保证多次查询id=1的记录都不会存在，所以临键锁就会退化为间隙锁。锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录，只能锁住区间。

唯一索引范围查询

若为大于查询：例如where id > 15，此时查询的第一条数据是id=20，从该条记录开始往后查询，直到最后一条记录。此时数据库为了避免幻读会加两个临键锁： (15, 20] 的 next-key 锁和(20, +∞] 的 next-key 锁。保证了不会出现小于20大于15的新纪录和大于符合条件的最大记录（这里是20）的新记录

若为大于等于查询：若这里有id=15的记录，那么就相当于等值查询，会加一个15的记录锁。临键锁的加锁方式和大于的情况一样

非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，这两个索引都会被同时加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

记录不存在的情况：二级索引会被加上间隙锁，避免幻读。但是即使加上了间隙锁，由于是位于二级索引，还是会出现记录插入成功的特例，因为二级索引树是按照二级索引值按顺序存放的，在相同的二级索引值情况下， 再按主键 id 的顺序存放。即使二级索引值位于间隙锁区间内，但主键值没有，还是可以插入成功的

记录存在的情况：由于不是唯一索引，所以肯定存在值相同的记录，此时数据库会对主键索引和二级索引都加锁，对于主键索引，由于主键的唯一性，事务会对其加上记录锁。对于二级索引，会被加上临键锁和间隙锁，以保证该条记录无法被更改，以及附近范围内无法插入新值。

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，二级索引记录加锁都是加 next-key 锁

所以当对于二级索引的范围查询，事务会对主键索引加记录锁，对二级索引的附近区间全部加上临键锁

没有加索引的查询

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

或者update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式也是全表扫描，也会锁住整张表。

因此，在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，否则若锁住整张表，会造成很严重的性能问题

MVCC

Multi-Version Concurrency Control多版本并发控制，实现对数据库的并发访问，实现读写冲突不加锁，非阻塞并发读。

数据库的并发有三种场景

1. 读读：不存在任何问题，不需要并发控制
2. 读写：有线程安全问题，可能会造成脏读，幻读，不可重复读等问题
3. 写写：有线程安全问题，可能存在更新丢失的问题

MVCC的实现原理就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与时间戳相关联，解决了脏读，幻读，不可重复读的问题，但是不能解决更新丢失的问题，可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低

当前读

也叫锁定读Locking Read，读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁，比如update 、delete 、insert

快照读

也叫普通读Consistent Read，就是单纯的select语句，但不包括for update，就是不加锁的非阻塞读，前提是不使用serializable的隔离级别，实现原理即MVCC

实现原理

隐藏字段

每行记录除了自定义的字段外，还有数据库隐式定义的字段

DB_TRX_ID
6字节，最近修改事务的ID，即创建这条记录或者最后一次修改这条记录的事务ID

DB_ROLL_PTR

7字节，回滚指针，指向这条记录的上一个版本，用于配合undolog

DB_ROW_ID

6字节，隐藏的主键，如果数据表没有主键，innoDB就会自动生成一个row_id

undo log

即回滚日志，即进行插入，更新，删除操作后生成的记录链

当进行insert时，产生的undo log只在事务回滚的时候需要，可以在事务提交后被丢弃

当进行update和delete操作时，undo log不仅在事务回滚时需要，在快照读时也需要，所以必须保留，只有在回滚或者快照读不涉及该日志时，undo log才会被purge线程清除（若delete_bit为true，且DB_TRX_ID相对于purge线程的read view可见，那么这条记录就一定可以被清除）

由上图可知，不同事物或者相同事物对同一条记录的修改，就会导致该记录的undolog生成一条记录版本的线性链，链首就是最新的旧记录，链尾就是最早的旧记录

Read View

Read View是实现repeatable read的基础，当事务进行快照读的时候会产生一个读视图，用来对当前事务的可见性进行判断，也就是说，事务会将生成的Read View作为条件来判断当前事务能够看见哪个版本的数据，有可能读到最新的数据，也有可能读到undolog里面的某个版本的数据。

Read View的可见性算法

Read View的三个全局属性：

1. trx_list：事务列表，即视图生成时刻系统正活跃未提交的事务ID
2. up_limit_id：记录事务列表中ID最小的ID
3. low_limit_id：视图生成时刻系统尚未分配的下一个事务（例如事务123正在活跃，事务4已提交，此时下一个事务ID就是5）

具体的算法如下

1. 取出当前最新记录的DB_TRX_ID，即当前事务ID
2. 比较DB_TRX_ID<up_limit_id，如果小于则说明当前事务能看见DB_TRX_ID所在的记录，如果大于等于就进入下一个判断
3. 判断DB_TRX_ID>=low_limit_id，如果大于等于，代表DB_TRX_ID所在的记录在readView生成后才出现，对于当前事务肯定不可见，如果小于，进入下一个判断
4. 判断DB_TRX_ID是否在活跃事务列表中，如果在，说明在视图生成时刻，该事务还没有提交，当前事务无法看见。若不在，说明以及提交，修改的结果可以看见（除自己以外的活跃trx_id都不可见）

RC，RR级别下的视图

在RR级别下某个事务对记录的第一次快照读会创建一个视图，此后在进行快照读时都会使用同一个视图，所以无论是否有其他事务对记录进行了修改，使用的都是这个视图，修改是不可见的，所以实现了可重复读的级别

在RC级别下，每次快照读都会生成一个新的视图，所以在RC级别下总是可以看见其他事务的提交

总结：

MVCC其实就是在事务进行并发读写时提供一个快照，事务只能看见符合可见性的版本链内的记录，从而实现了并发读写的隔离性。

MVCC解决幻读了嘛

幻读【前后多次读取，数据总量不一致】

1. 快照读：快照读就是普通的select语句，通过MVCC的方式解决了幻读，MVCC为保证了事务执行过程中看到的数据是一致的，使用ReadView和可见性算法可以实现即使有新记录插入，当前事务也是看不到的。
2. 当前读：除了select语句外的所有操作都是当前读，当前读使用临键锁来避免幻读，当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞。

但是在RR级别下，仍然有一些情况会出现幻读

第一种情况：同一事务，使用update导致快照读生成的ReadView被更改

1. 若在事务开始执行之前，表中是没有id=5的这条记录的，所以查询不到
2. 然后事务 B 插入一条 id = 5 的记录，并且提交了事务
3. 此时，事务 A 更新 id = 5 这条记录，事务 A 看不到 id = 5 这条记录，但是他去更新了这条记录，然后再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的纪录了，幻读就是发生在这种违和的场景。

总结：

1. 在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。
2. 接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的DB_TRX_ID的值就变成了事务 A 的事务 id。
3. 之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

因为这种特殊现象的存在，所以我们认为 MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。

第二种情况：同一事务，先快照读，再当前读导致幻读

1. T1 时刻：事务 A 先执行快照读语句：select * from t_test where id > 100 得到了 3 条记录。
2. T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
3. T3 时刻：事务 A 再执行当前读语句select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

总结：MVCC只能很大程度上避免幻读，但是个别情况下，仍然会发生幻读

MySQL调优

代码优化：

1. 少使用select*，指定具体字段
2. 尽量少使用order by排序，而使用联合索引
3. 减少使用Null，有多个null的可以加默认值
4. where后少使用函数运算
5. 避免超过五个以上的表连接

SQL：

1. 对于高频筛选字段可以适当建立索引
2. 一个表的索引不超过五个
3. 联合索引，遵守最左匹配原则

字段选择优先级（由优至劣）

1. 整型
2. time：定长运算快
3. enum：枚举，能约束值，内部由整型存储
4. char：定长，需要考虑字符集
5. varchar：不定长，考虑字符集的转换与排序的校对集，速度慢
6. text：无法使用内存临时表

定长优先。能选整型就不要选字符串，够用就行，不要富余分配空间，尽量避免使用Null

varchar最多可以定义65535个字节

如果MySQL出现慢查询，问题在哪？

1. 索引失效或者无索引
2. 强制查询不存在的字段，此时MySQL会查询整张表
3. 两张表字符集不一样或者编码不一样，但是需要联表查询
4. 多线程查询操作，若线程A查询了很大的一块数据，此时server正在返回A的查询结果并占用了所有的IO，线程B的查询性能就会受限

如何解决呢？

1. 一次查询数量过于庞大，拆成多次查询、拼装
2. 分离冷热数据，将大字段或者是查询频率少的数据分出一张新表
3. IN子查询影响查询性能，用JOIN方式代替
4. 用了反向查询（比如NOT IN）或者IN语句参数集太多，可能会导致全表扫描，这种情况尽量拆分语句