面试：MySQL基础知识

主要内容来源：
JavaGuide
CS-Notes

一、存储引擎

1、InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

2、MyISAM

MyISAM是MySQL的默认数据库引擎（5.5版之前）
虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

3、对比

• 是否支持行级锁：MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
• 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
• 是否支持外键： MyISAM不支持，而InnoDB支持。
• 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。
• 索引：MySQL索引使用的数据结构主要有BTree索引 和哈希索引。
  （1）MyISAM：B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  （2）InnoDB：表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

附加：Hash索引

• 优点：定位速度快
• 缺点：无法用于排序与分组；不支持顺序和范围查询
  InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree
  索引之上再创建一个哈希索引，这样就让B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

4、为什么MyISAM会比Innodb的查询速度快

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多:

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

为什么MyISAM会比Innodb的查询速度快

二、索引

1、B树与B+树

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O（h）=O（logmN）。一般实际应用中，m是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

B-树和B+树的应用：数据搜索和数据库索引

相同点：一棵m阶B树

• 每个分支节点最多有m棵子树
• 非叶根节点至少有两颗子树，其他分支节点最少有┍m/2┑棵子树
• 所有的叶节点都出现在同一层

不同点：

• B 树的所有节点既存放 键(key) 也存放 数据(data);而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

2、选择B+树作为索引的原因

（1）B+树与B树的对比

• B+树非叶子节点不存储数据，由于磁盘IO一次读取的数据量大小是固定的，因此磁盘一次能够读取的索引范围大，能够减少磁盘IO次数。 B-树/B+树 的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数。但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。
• B+树支持基于范围的查询：由于B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

（2）B+树与红黑树的对比

1. B+ 树有更低的树高（主要）
   B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。
2. 利用磁盘预读特性
   为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

原文链接：为什么Mysql用B+树做索引而不用B-树或红黑树

3、使用索引的优缺点

优点

• 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

缺点

• 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

4、索引类型

（1）主键索引

• 数据表的主键列使用的就是主键索引。
• 一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB
会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

（2）二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。

（3）聚簇索引（聚集索引）

索引结构和数据一起存放的索引。主键索引属于聚集索引。

• 优点：聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
• 缺点：依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改。所以对于主键索引来说，主键一般都是不可被修改的。

（4）非聚簇索引

索引结构和数据分开存放的索引。二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

• 优点：更新代价比聚集索引要小 ：非聚集索引的叶子节点是不存放数据的
• 缺点：依赖于有序的数据；可能会二次查询(回表) : 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

（5）覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用。
• 无需回表查询：对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

覆盖索引的另一个好处是对某些统计问题而言的

alter table bug_log add key(userid);
alter table bug_log add key(userid,buy_date);

explain select count(*) from bug_log;

InnoDB并不会选择通过查询聚集索引来进行统计。由于表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择如下图所示：

5、最左前缀原则

• 单列索引：由一列属性组成的索引
• 联合索引（多列索引）：由多列属性组成索引
• 最左前缀原则：如果查询的时候查询条件精确匹配联合索引的左边连续一列或几列，则此列就可以被用到。

假设User表具有name和city的联合索引：(name,city)
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引 

注意点：

• 查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
• 由于最左前缀原则，在创建联合索引时，让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为1，此时每个记录都有唯一的索引与其对应。
选择性越高，每个记录的区分度越高，查询效率也越高。

联合索引

• 从本质上说，联合索引也是一棵树，不同的是联合索引的键值的数量不是1，而是>=2
• 下面我们以键值的数量为2来介绍，假定两个键值的名称分别为a,b，如下图所示
  
• 从图中可以看到多个键值的B+树情况，其实和前面讨论的单个键值的B+数并没有区别，键值都是排序的
• 重点：如果是联合索引，索引排序是看第一个字段的值， 因此在此处键值的排序是根据a进行排序的，即(1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2)

对于下面的两种查询语句，可以使用(a,b)这个联合索引来查询，因为其实按照a进行查询的

select * from table where a=xxx and b=xxx;
select * from table where a=xxx;

但是对于下面的查询语句就不能使用上面那颗B+索引树了，因为其是按照b字段进行查询的

select * from table where b=xxx;

联合索引的第二个好处是已经对第二个键值进行了排序处理。例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了

因此联合索引(a,b)是根据列a,b进行排序，因此下列语句可以直接使用联合索引得到结果

select ... from table where a=xxx order by b;

然而对于联合索引(a,b,c)来说，下列语句同样可以直接通过联合索引得到结果：

select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引(a,c)并未排序

select ... from table where a=xxx order by c;

MySQL(InnoDB剖析):—B+树索引的使用（联合索引、覆盖索引、优化器不使用索引的情况、索引提示、MRR优化、ICP优化）

6、索引创建注意点

• 选择合适的字段
  （1）不为NULL的字段：因为对于数据为NULL的字段，数据库较难优化。
  （2）被频繁查询的字段
  （3）被作为条件查询的字段
  （4）被经常频繁用于连接的字段：对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
• 不合适创建索引的字段
  （1）被频繁更新的字段应该慎重建立索引
  （2）不被经常查询的字段没有必要建立索引
• 尽可能的考虑建立联合索引而不是单列索引：节省空间
• 注意避免冗余索引。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
• 考虑在字符串类型的字段上使用前缀索引代替普通索引

7、explain指令（执行计划）

explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

（1）id

select 查询到序列号，包含一组数字，表示查询中select子句或操作表的顺序。
（1）id相同，表执行顺序由上到下，与sql中顺序无关
（2）id不同，id值越大优先级越高，越先被执行
（3）id有相同有不同。在所有组中，id值越大，优先级越高，越先执行；id 如果相同，可以认为是一组，从上往下顺序执行

（2）select_type

1. simple：简单查询，查询中不包含子查询或者union等任何复杂查询
2. primary：查询中若包含任何复杂的子查询，则最外层被标记为paimary
3. subquery：在select或where列表包含了子查询
4. derived：在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里（临时表会增加系统负担，但有时不得不用）
5. union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为：derived
6. union result：两种union结果的合并

（3）type

访问类型判断（跟语句性能息息相关），显示查询使用了何种类型 不要为了优化而优化

从最好到最差依次是：

system > const > eq_ref > ref > range > index > all(全表扫描)

一般来说，得保证查询级别至少达到range级别，最好能达到ref

type类型	解释
system	const的特例，表中只有一行记录，相当于系统表
const	查找主键索引，返回的数据至多一条。 属于精确查找
eq_ref	查找唯一性索引，返回的数据至多一条。属于精确查找
ref	查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找
range	查找某个索引的部分索引，一般在where子句中使用 < 、>、in、or、between等关键词。 只检索给定范围的行，属于范围查找
index	另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。 Mysql官方文档有解释index有两种方式查询： 1.Extra中有Using index，即索引树中即可拿到所需查询数据 2.Extra 中没有Using index，即按照索引顺序查找全表数据
all	不使用任何索引，全表扫描，性能最差

explain 的type类型的理解

（4）extra

包含不适合在其他列显示但是很重要的额外信息

1、using filesort（九死一生）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为"文件排序"，一旦出现这种情况很危险。

2、using index

使用到了索引、这种情况是好事。表示响应的select操作中使用了覆盖索引（cocering index），避免访问了表的数据行，效率不错！如果同时出现了using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

3、using where

使用了where条件

4、using temporary（十死无生）

使用了临时表保存中间结果。mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by。（group by 最好与索引的字段、顺序一致）

（5）possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

（6）key

实际上使用到的索引，如果为null，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中，

（7）其他字段

1、key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好

2、ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

3、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数(越少越好)，每张表有多少行被优化器查询

SQL中explain的使用介绍

MySQL中使用IN会不会走索引

8、sql语句增删索引

1.PRIMARY  KEY（主键索引）
        mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
        mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) 
3.INDEX(普通索引)
        mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
        mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
        mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

9、Mysql对索引的优化

（1）索引下推

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

(name, age)
联合索引满足最左匹配，但是遇到非等值判断时匹配停止。
name like ‘陈%’ 不是等值匹配，所以 age = 20 这里就用不上 (name,age) 联合索引了。如果没有索引下推，联合索引只能用到 name，age 的判定就需要回表才能做了。5.6之后有了索引下推，age = 20 可以直接在联合索引里判定。

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。

mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;

根据最左前缀原则，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3）再从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

回表与覆盖索引，索引下推

三、事务

1、事务的特性（ACID）

• 原子性(Atomicity)：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
• 一致性(Consistency)：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。
• 隔离性(Isolation)： 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性(Durability)： 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  
• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况

AUTOCOMMIT

MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

2、并发一致性问题

• 脏读（Dirty read）: 读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
• 丢失修改（Lost to modify）: 丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。
• 幻读（Phantom read）: 幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

不可重复度和幻读区别： 不可重复读的重点是修改，幻读的重点在于新增或者删除。

产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。
并发控制可以通过锁来实现，但是锁操作需要用户自己控制，相当复杂。
数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

解决方法：

• 脏读：事务提交后再释放锁
• 丢失修改：使用Serializable隔离级别；乐观锁；悲观锁
• 不可重复读：MVCC事务级别的快照
• 幻读：Next-Key Locks

Next-Key Locks

• Record lock：行锁，锁直接加在索引记录上面，锁住的是key。如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。
Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

3、事务的隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
  
• MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。
• 与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下，允许应用使用 Next-Key Lock 锁算法来避免幻读的产生。从而达到了 SQL 标准的 SERIALIZABLE(可串行化) 隔离级别。
• InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化) 隔离级别。

四、锁

• 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
• MyISAM在执行查询语句SELECT前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预

主要内容出自:数据库两大神器【索引和锁】

1、表锁与行锁

• 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
• InnoDB行锁和表锁都支持，MyISAM只支持表锁。
• InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁。即InnoDB的行锁是基于索引的。

2、表锁：表读锁、表写锁

读读不阻塞，读写阻塞，写写阻塞

• 读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁
• 读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁
• 写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁

如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！

MyISAM可以支持查询和插入操作的并发进行。在MyISAM中默认是：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。
但是InnoDB不支持。

3、行锁：共享锁（S锁；读锁）、排他锁（X锁；写锁）

• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

4、意向共享锁（IS）、意向排他锁（IX）

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁，意向锁是InnoDB自动加的，不需要用户干预。：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

InnoDB的锁机制兼容情况如下：注意此处共享锁、排他锁指的是表级锁

• 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
• 这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）
• IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。
• 当一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果请求不兼容，则该事务就等待锁释放

5、死锁和避免死锁

InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突。

不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务回滚，另一个则可以获取锁完成事务。

避免死锁：

1. 以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
2. 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4. 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5. 为表添加合理的索引。可以看到如果不走索引将会为表添加上锁，死锁的概率大大增大。

6、乐观锁、悲观锁

悲观锁（从数据库层面加锁）

悲观锁是基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库本身锁的机制都是基于悲观锁的机制实现的;

特点：可以完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁， 然后进行数据操作，最后再解锁，而加锁释放锁的过程会造成消耗，所以性能不高;

乐观锁（使用版本号控制）

乐观锁是对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁，只有到数据提交的时候才通过一种机制来验证数据是否存在冲突(一般实现方式是通过加版本号然后进行版本号的对比方式实现);

特点：乐观锁是一种并发类型的锁，其本身不对数据进行加锁通而是通过业务实现锁的功能，允许多个请求同时访问数据，同时也省掉了对数据加锁和解锁的过程，这种方式大大的提高了数据操作的性能;

什么是悲观锁和乐观锁

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

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

在内部实现中，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

主要内容来源：
CS-Notes
MySQL-InnoDB-MVCC多版本并发控制
数据库两大神器【索引和锁】
MySQL 是如何实现四大隔离级别的？
InnoDB的多版本并发控制（MMVC）
乐观锁和 MVCC 的区别？

1、MVCC知识点速览

• MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系。
• 在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。
• MVCC是被Mysql中事务型存储引擎InnoDB 所支持的;
• 应对高并发事务, MVCC比单纯的加锁更高效;
• MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
• MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;

2、相关数据结构

快照的级别

• 语句级：针对于Read committed隔离级别
• 事务级别：针对于Repeatable read隔离级别

Read committed是语句级别的快照！每次读取的都是当前最新的版本！

Repeatable read避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。

版本号

• 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号 TRX_ID ：事务开始时的系统版本号。

Undo 日志

保证事务原子性；MVCC(多版本并发控制)实现的关键

实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。

INSERT、UPDATE、DELETE 操作会创建一个日志记录，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。

ReadView

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 最大值TRX_ID_MAX。

在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

• TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
• TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
• TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
  （1）READ COMMITTED：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。若TRX_ID 不在 TRX_IDs 列表中，表示已经提交，可以使用。（具体的单个值，不再是范围）
  （2）REPEATABLE READ ：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

ReadView生成时机

可重复读级别：同一个事务内readview只生成一次，即事务开始后的第一条select语句，后面的所有查询都使用前面生成的readview

读已提交级别：同一个事务内readview会生成多次，即事务开始后的每一条select语句，都重新生成一个readview，因每次生成的readview不同，就出现了每次select读到的内容不一致

3、快照读与当前读

快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

SELECT * FROM table ...;

当前读

什么时候会发生当前读？

1. 在执行修改操作（INSERT、UPDATE、DELETE）的时候，需要执行当前读，然后再更新数据
2. select语句加锁时，执行的是当前读。

SELECT * FROM table WHERE ? lock in share mode;  加共享锁
SELECT * FROM table WHERE ? for update;  加排他锁

在REPEATABLE READ 级别下，快照读是通过MVCC和undo log来实现的，当前读是通过加record lock(行锁)和gap lock(间隙锁)来实现的。

例子

在我们更新表中的数据的时候，有的时候一次性的更新多个列的值，这多个待更新的列之间又有业务逻辑上的关系。这个时候我们在更新的时候一定要格外的注意更新过程中的当前读，这里的当前读是指读取当前事务中的值。

INSERT INTO `test` (`id`,`a`,`b`,`c`) VALUES (1,100,100,100);
update test set a=a-1, b=a-1-1, c=a-1-1-1 where id=1;

答案是：a=99, b=97, c=96。

分析：在执行a=a-1这个语句块，MySQL在计算a到底需要等于多少的时候，要获取数据库中a的值，此时得到的a为100，那100-1后赋值给了a，在当前这个更新语句的事务中，a此时为99了。

在执行b=a-1-1的时候，它会获取a的值然后再去为b赋值，因为这个更新语句是一个事务，所以这个事务前面修改的a的值，当前的事务还是会承认的，所以它读到a的值为99，此时再执行b=99-1-1=97,

在执行c=a-1-1-1的时候，同样会读取当前事务中a的值为99，然后计算出c=99-1-1-1=96。

update test set b=a-1-1, c=a-1-1-1, a=a-1 where id=1;

答案：a=99, b=98, c=97

MySQL中的当前读

4、MVCC和事务的隔离级别

事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节

• 未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC
• MVCC用于实现提交读和可重复读这两种隔离级别
• 可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

READ-UNCOMMITTED(读未提交)

• 读最新的数据，不管这条记录是不是已提交。不会遍历版本链，少了查找可见的版本的步骤。
• 对写仍需要锁定，策略和读已提交类似，避免脏写。

出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据。

READ-COMMITTED(读已提交)

语句级别快照。与可重复读级别主要有两点不同：

• 获得ReadView的时机。每个语句开始执行时，获得ReadView，可见性判断是基于语句级别的ReadView。读的策略与可重复读类似。
• 写锁的使用方式。这里不需要GAP LOCK，只使用行锁（Record lock）。并且事务只持有被UPDATE/DELETE记录的写锁（可重复读需要保留全部写锁直到事务结束，而读已提交只保留真正更改的）

避免脏读的做法：就是把释放锁的位置调整到事务提交之后，此时在事务提交前，其他进程是无法对该行数据进行读取的，包括任何操作

Read Skew

读偏序（Read Skew）是RC级遇到的问题。如果数据项x与y存在一致性约束，T1先对读x，而后T2修改x和y后commit，此时T1再读y。T1得到的x与y不满足原有的一致性约束。

REPEATABLE-READ(可重复读)

事务级别快照。读不加锁，只有写才加锁，读写互不阻塞，并发度相对于可串行化级别要高，但会有Write Skew异常。

• 事务在开始时创建一个ReadView，当读一条记录时，会遍历版本链表，通过当前事务的ReadView判断可见性，找到第一个对当前事务可见的版本，读这个版本。
• 对于写操作，包括Locking Read(SELECT … FOR UPDATE), UPDATE, DELETE，需要加写锁。根据谓词条件上索引使用情形，锁定有不同的方式：
  1）有索引：对于索引上有唯一约束且为等值条件的情形，不用GAP LOCK，只锁定索引记录。对于其它情形，使用GAP LOCK，相当于谓词锁。
  2）没有索引：由于MySQL没有实现通用的谓词锁，这时就相当于锁全表。

Repeatable read避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据

Write Skew

写偏序（Write Skew）也是一致性约束下的异常现象，即两个并行事务都基于自己读到的数据集去覆盖另一部分数据集，在串行化情况下两个事务无论何种先后顺序，最终将达到一致状态，但快照隔离级别下无法实现。下图的“黑白球”常常被用来说明写偏序问题。

再谈数据库事务隔离性

SERIALIZABLE(可串行化)

在可串行化级别上，MySQL执行S2PL并发控制协议, 一阶段申请，一阶段释放。读写都要加锁。

5、MVCC的实现方式

下面看一个在REPEATABLE READ（可重复读）隔离级别下，MVCC的具体操作：

SELECT

只有符合上述两个条件的记录，才能返回做为查询结果。

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前系统版本号到原来的行作为删除标识。

---

保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好。并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的检查工作，以及一些额外的维护工作。

InnoDB的多版本并发控制（MMVC）

6、多版本并发控制（MVCC）与乐观并发控制（OCC）

• 多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读
• 乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，认为事务间争用没有那么多，所以先进行修改，在提交事务前，检查一下事务开始后，有没有新提交改变，如果没有就提交，如果有就放弃并重试。乐观并发控制适用于低数据争用，写冲突比较少的环境。

因为有了MVCC，所以我们可以形成两个组合：

• MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

乐观锁和 MVCC 的区别？

7、MVCC几个读取结果的例子

t1	t2
select x from table; //return 10
begin;
update table set x=20;
	begin
	select x from table; //return rs1
commit;
	select x from table; //return rs2
	commit;

上图执行的事务，rs1与rs2的值在不同事务隔离界别下的值都不一样。

• 如果事务隔离级别是RU，rs1与rs2的值均为20。
• 如果事务隔离级别是RC，rs1为10，rs2为20。
• 如果事务隔离级别是RR或Serializable，rs1与rs2的值均为10。

可重复读级别下ReadView生成的例子

t1	t2
begin;
select x from table; //return 10	begin
	update table set x=20;
	commit;
select x from table; //return 10
commit;

t1	t2
select x from table; //return 10
begin;
	begin
	update table set x=20;
	commit;
select x from table; //return 20
commit;

RR事务中当前读的例子

时刻	事务A	事务B	事务C
t1	begin;
t2		begin
t3	select x from table; //return 1	select x from table; //return 1	select x from table; //return 1
t4			update table set x=x+1;
t5	select x from table; //return 1	select x from table; //return 1	select x from table; //return 2
t6		update table set x=x+1;
t7	select x from table; //return 1	select x from table; //return 3	select x from table; //return 2
t8	commit;
t9	select x from table; //return 2
t10		commit;
t11	select x from table; //return 3	select x from table; //return 3	select x from table; //return 3

T5时刻，在事务A和事务B中都为1，因为在这两个事务启动的时候，他们已经创建好了一致性视图，此时他们是一致性的快照读，在可重复读事务隔离级别下，此时事务A和B中，v的值为仍然为1。而事务C的值之所以为2是以为此时的事务C已经修改且已经提交。所以它能看到最后提交的v的值，是2。

T7时刻，在事务A中，v的值为1，遵循的是在事务A运行期间可重复读的原则，所以，在一致性快照读的时候，v的值仍然是1。但是在事务B中查询发现v的值是3而不是2，是因为在更新的时候，要读后写，此时的读是读取了数据库中最新的v的值为2，发生了当前读，然后在2的基础上再次加1得到了3。所以事务B中v的值为3。在事务C之后的查询就是一个普通的查询，它没有任何事务包裹，所以它要查询到数据库中最新的v值2，3之所以看不到是因为事务B还没有提交。

MySQL事务隔离级别、并发事务问题与MVCC机制原理

mysql的write skew问题

MySQL中的当前读

六、数据库优化

MySQL大表优化方案

1、水平拆分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

常用策略：

• 哈希取模：hash(key) % N；
• 范围：可以是 ID 范围也可以是时间范围；
• 映射表：使用单独的一个数据库来存储映射关系。

水平拆分能够支持非常大的数据量存储，应用端改造少，但分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。

存在的问题：

1. 事务问题
   使用分布式事务来解决，比如 XA 接口。
2. 连接
   可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
3. ID 唯一性
   使用全局唯一 ID（GUID）
   为每个分片指定一个 ID 范围
   分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

数据库分片的两种常见方案：

客户端架构

通过修改数据访问层，如JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以Jar包的方式呈现

优点：

• 应用直连数据库，降低外围系统依赖所带来的宕机风险
• 集成成本低，无需额外运维的组件

缺点：

• 限于只能在数据库访问层上做文章，扩展性一般，对于比较复杂的系统可能会力不从心
• 将分片逻辑的压力放在应用服务器上，造成额外风险

中间件架构

通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署、运维代理组件

代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理

优点：

• 能够处理非常复杂的需求，不受数据库访问层原来实现的限制，扩展性强
• 对于应用服务器透明且没有增加任何额外负载

缺点：

• 需部署和运维独立的代理中间件，成本高
• 应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险

2、垂直拆分

垂直拆分是将一张表按列拆分成多个表，通常是按照列的关系密集程度进行拆分，也可以利用垂直拆分将经常被使用的列和不经常被使用的列拆分到不同的表中。

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

3、主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志binlog（Binary log）中。
• 从库I/O 线程 ：负责从主服务器上读取binlog日志，并写入从服务器的中继日志（Relay log）。
• 从库SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
  
  （1）在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制。
  （2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接Master服务器，并请求从binlog日志文件中的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。
  （3）Master服务器接收来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。
  （4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件名及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master.info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取
  （5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay Log 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

复制方式

基于语句的复制

基于语句的复制，主库会记录那些造成数据更改的操作，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍。

优点：

1. 实现简单。理论上讲，简单地记录和执行这些语句，能够让主备保持同步。
2. 二进制日志里的事件更加紧凑。进行复制时，基于语句的模式不会占用太多带宽。

缺点：

1. 主库上的数据更新除了执行的语句外，可能还依赖于其他因素。例如，同一条SQL在主库和备库上执行的时间可能稍微或很不相同。因此在传输的binlog日志中，除了查询语句，还包括一些元数据信息，如当前的时间戳。
2. 存在一些无法被正确复制的SQL语句。例如：触发器、存储过程
3. 更新必须是串行的，需要更多的锁。另外不是所有的存储引擎都支持基于语句的复制。

基于行的复制

基于行的复制，这种方式会将实际数据记录在binlog日志中。

优点：

1. 可以正确的复制每一行。一些语句可以被更加高效的复制。
2. 可以减少锁的使用，因为它并不要求强串行化是可重复的。

缺点：

1. 语句做了全表更新，使用基于行的复制开销会很大。因为每一行的数据都会被记录到binlog日志中，这使得binlog日志事件非常庞大。
2. 由于语句并没有在日志里记录，因此无法判断执行了哪些SQL。当出现问题时，可能很难找到问题所在。
3. 基于行的复制模式，很难进行时间点恢复。

总结

没有哪种模式对所有的情况都是完美的，Mysql能够在这两种复制模式间动态切换。默认情况下使用基于语句的复制方式，但是如果发现语句无法被正确地复制，就切换到基于行的复制模式。还可以根据需要设置会话级别的变量binlog_format，控制binlog日志格式。

同步方式

异步复制

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

全局同步复制

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会受到严重的影响。

半同步复制

是介于全同步复制与异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间。

介于异步复制和全同步复制之间，相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

组复制

组复制技术，简称MGR。是MySQL在5.7.17版本中推出的一种新的数据复制技术，这种复制技术是基于paxos协议的状态机复制。

MGR将MySQL带入了数据强一致性的时代，是一个划时代的创新，其中一个重要原因是MGR是基于paxos协议的。

异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景，比如金融领域。MGR 很好地弥补了这两种复制模式的不足。

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应Node节点）的同意，大多数指的是同意的节点数量要大于N/2+1，这样才可以进行提交，而不是一方说了算。而针对只读（RO）事务则不需要经过组内同意，直接COMMIT即可。

在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了源自消息和全局有序消息，从而保证组内数据的一致性。

如何解决主从同步的数据一致性问题？

1、半同步复制（不能保证强一致性）

2、组复制

MGR将MySQL带入了数据强一致性的时代，是一个划时代的创新，其中一个重要原因是MGR是基于paxos协议的。

3、利用中间件

（1）所有的读写都走数据库中间件，通常情况下，写请求路由到主库，读请求路由到从库。
（2）记录所有路由到主库的key，在经验主从同步时间窗口内（假设是500ms），如果有读请求访问中间件，此时有可能从库还是旧数据，就把这个key上的读请求路由到主库。
（3）经验主从同步时间过完后，对应key的读请求继续路由到从库。

中间件带来的好处就是能保证数据的相对一致性，但同时也带来成本上升的问题。

4、利用缓存

写请求：
（1）将某个库上的某个key要发生写操作，记录在cache里，并设置“经验主从同步时间”的cache超时时间。
（2）修改数据库。

读请求：
（1）先到cache里查看，对应库的对应key有没有相关数据；
（2）如果cache hit，有相关数据，说明这个key上刚发生过写操作，此时需要将请求路由到主库读最新的数据；
（3）如果cache miss，说明这个key上近期没有发生过写操作，此时将请求路由到从库，继续读写分离。

显然，利用缓存，减少了中间件带来的成本问题，但多了一个Cache组件，并且读写数据库多了一步Cache操作，操作相对其他稍较繁琐。

MySQL高级——MySQL主从复制及读写分离实战

话说数据库主从复制，读写分离，数据一致性

数据库主从同步的作用是什么，如何解决数据不一致问题？

4、读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

七、SQL优化

MySQL 高性能优化规范建议

1、一条SQL语句执行得很慢的原因有哪些？

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

• 数据库在刷新脏页。例如 redo log 写满了需要同步到磁盘、内存不足、Mysql认为系统空闲。

• 执行的时候，遇到锁，如表锁、行锁。show processlist命令查看状态

2、这条 SQL 语句一直执行的很慢，则有如下原因。

• 没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

• 数据库选错了索引。

系统通过索引的区分度来判断是否走索引。我们也把区分度称之为基数，即区分度越高，基数越大，意味着走索引查询越有优势。

索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。既然是采样，那就有可能出现失误的情况。

系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

一条SQL语句执行得很慢的原因有哪些？

2、Mysql哪些情况不走索引

现假设有t_stu表，age,sname上建立了索引

1、索引列参与计算

SELECT `sname` FROM `t_stu` WHERE `age`=20;-- 会使用索引
SELECT `sname` FROM `t_stu` WHERE `age`+10=30;-- 不会使用索引！！因为所有索引列参与了计算
SELECT `sname` FROM `t_stu` WHERE `age`=30-10;-- 会使用索引

2、索引列使用了函数

SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT `sname` FROM `stu` WHERE `sname` =concat('Jaskey','abc'); -- 会使用索引

3、索引列使用了Like %XXX

SELECT * FROM `houdunwang` WHERE `uname` LIKE '前缀就走索引%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE '%后缀不走索引' -- 不走索引

所以当需要搜索email列中.com结尾的字符串而email上希望走索引时候,可以考虑数据库存储一个反向的内容reverse_email

SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); -- 走索引

4、字符串列与数字直接比较
这是一个坑，假设有一张表,里面的a列是一个字符char类型,且a上建立了索引,你用它与数字类型做比较判断的话：

 CREATE TABLE `t1` (`a` char(10));

 SELECT * FROM `t1` WHERE `a`='1' -- 走索引
 SELECT * FROM `t2` WHERE `a`=1 -- 字符串和数字比较，不走索引！

但是如果那个表那个列是一个数字类型，拿来和字符类型的做比较，则不会影响到使用索引

 CREATE TABLE `t2` (`b` int);

 SELECT * FROM `t2` WHERE `b`='1' -- 虽然b是数字类型，和'1'比较依然走索引

但是，无论如何，这种额外的隐式类型转换都是开销，而且由于有字符和数字比就不走索引的情况，故建议避免一切隐式类型转换

5、尽量避免 OR 操作

select * from dept where dname='jaskey' or loc='bj' or deptno=45 
如果条件中有or,即使其中有条件带索引也不会使用。
换言之,就是要求使用的所有字段,都必须建立索引

所以除非每个列都建立了索引，否则不建议使用OR，在多列OR中，可以考虑用UNION 替换

select * from dept where dname='jaskey' union
select * from dept where loc='bj' union
select * from dept where deptno=45

6、where比较判断不一定走索引

select * from t_stu where age < 20;

该查询不一定走索引，和表的数据量有关系，只有在满足条件的数据占比较小时（占全部数据的比例）才会走索引

mysql哪些情况不走索引

mysql 索引字段，比较大小时不走索引

3、IN肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描

MySQL中使用IN会不会走索引

4、如果使用了 not in，则不走索引

5、如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个

只能使用1个，所以要合理的使用组合索引，而不是单列索引

数据库中查询记录时是否每次只能使用一个索引？

6、Mysql Join算法原理：嵌套循环连接

create table a(a1 int primary key, a2 int ,index(a2));  		--双字段都有索引
create table c(c1 int primary key, c2 int ,index(c2), c3 int);  --双字段都有索引
create table b(b1 int primary key, b2 int);						--有主键索引
create table d(d1 int, d2 int); 								--没有索引

（1）驱动表的选择

驱动表的概念是指多表关联查询时，第一个被处理的表，使用此表的记录去关联其他表。

驱动表的选择遵循一个原则：在对最终结果集没影响的前提下，优先选择结果集最小的那张表作为驱动表。

mysql在选择前会根据where里的每个表的筛选条件，相应的对每个可作为驱动表的表做个结果记录预估，预估出每个表的返回记录行数，同时再根据select里查询的字段的字节大小总和做乘积：

每行查询字节数 * 预估的行数 = 预估结果集

通过where预估结果行数，遵循以下规则：

• 如果where里没有相应表的筛选条件，无论on里是否有相关条件，默认为全表
• 如果where里有筛选条件，但是不能使用索引来筛选，那么默认为全表
• 如果where里有筛选条件，而且可以使用索引，那么会根据索引来预估返回的记录行数

select a.*,c.c2 from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;

explain显示结果里排在第一行的就是驱动表，此时表c为驱动表。

如果将sql修改一下，将select 里的条件c.c2 修改为 c.* ：

select a.*,c.* from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;

此时驱动表还是c，按理来说 c.* 的数据量肯定是比 a.*大的，似乎结果集大小的规则在这里没有起作用。

(理解以下语句，需要先理解主键索引与辅助索引的区别与联系)

此情形下如果用a作为驱动表，通过索引c2关联到c表，那么还需要再回表查询一次，因为仅仅通过c2获取不到c.*的数据，还需要通过c2上的主键c1再查询一次。

而以c作为驱动表的话，不需要额外查询（走主键索引，key为主键，data为整行数据）。同时因为a表只有两个字段，通过a2索引能够直接获得a.*,不需要额外查询。（辅助索引key为普通索引，data为主键值）

综上所述，虽然使用c表来驱动，结果集大一些，但是能够减少一次额外的回表查询，所以mysql认为使用c表作为驱动来效率更高。

结果集是作为选择驱动表的一个主要因素，但不是唯一因素。

（2）mysql对 Nested-Loop join 的优化

在使用索引关联的情况下，有Index Nested-Loop join和Batched Key Access join两种算法；
在未使用索引关联的情况下，有Simple Nested-Loop join和Block Nested-Loop join两种算法；

（3）使用索引关联

使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

select a.*,c.* from a join c on a.a2=c.c2 where a.a1>4;

首先根据第一步的逻辑来确定驱动表a，然后通过a.a1>4，来查询一条记录a1=5，将此记录的c2关联到c表，取得c2索引上的主键c1，然后用c1的值再去聚集索引上查询c.*，组成一条完整的结果，放入net buffer，然后再根据条件a.a1>4，取下一条记录，循环此过程。过程图如下：

通过索引关联被驱动表，使用的是Index Nested-Loop join算法。根据驱动表的筛选条件逐条地和被驱动表的索引做关联，每关联到一条符合的记录，放入net-buffer中，然后继续关联。此缓存区由net_buffer_length参数控制，最小4k,最大16M,默认是1M。 如果net-buffer满了，将其发送给client，清空net-buffer，继续上一过程。

通过上述流程知道，驱动表的每条记录在关联被驱动表时，如果需要用到索引不包含的数据时，就需要回表一次，去聚集索引上查询记录，这是一个随机查询的过程。每条记录就是一次随机查询，性能不是非常高。mysql对这种情况有选择的做了优化，将这种随机查询转换为顺序查询，执行过程如下图：

此时会使用Batched Key Access join 算法，顾名思义，就是批量的key访问连接。

逐条的根据where条件查询驱动表，将符合记录的数据行放入join buffer，然后根据关联的索引获取被驱动表的索引记录，存入read_rnd_buffer。join buffer和read_rnd_buffer都有大小限制，无论哪个到达上限都会停止此批次的数据处理，等处理完清空数据再执行下一批次。也就是驱动表符合条件的数据可能不能够一次处理完，而要分批次处理。

当达到批次上限后，对read_rnd_buffer里的被驱动表的索引按主键做递增排序，这样在回表查询时就能够做到近似顺序查询。


如上图，左边是未排序前的随机查询示意图，右边是排序后使用MRR( Multi-Range Read)的顺序查询示意图。
因为mysql的InnoDB引擎的数据是按聚集索引来排列的，当对非聚集索引按照主键来排序后，再用主键去查询就使得随机查询变为顺序查询，而计算机的顺序查询有预读机制，在读取一页数据时，会向后额外多读取最多1M数据。此时顺序读取就能派上用场。

BKA算法在需要对被驱动表回表的情况下能够优化执行逻辑，如果不需要回表，那么自然不需要BKA算法。

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前先设置：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前两个参数的作用是要启用 MRR（Multi-Range Read）。这么做的原因是，BKA 算法的优化需要依赖于MRR，官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

（4）未使用索引关联

mysql没有直接使用Simple Nested-Loop join，而是对其做了一个优化，不是逐条的获取驱动表的数据，而是多条的获取，也就是一块一块的获取，取名叫Block Nested-Loop join。每次取一批数据，上限是达到join buffer的大小，然后全表扫描被驱动表，每条数据和join buffer里的所有行做匹配，匹配上放入最终结果集中。这样就极大的减少了扫描被驱动表的次数。

使用Block Nested-Loop Join 算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on。 默认为开启，如果关闭则使用Simple Nested-Loop Join 算法；

BNL(Block Nested-Loop join) 和 BKA(Batched Key Access join)的流程有点类似， 但是没有read_rnd_buffer这个步骤

（5）多表连接如何执行

一条记录贯穿全局

select a.*,b.*,c.* from a join c on a.a2=c.c2 join b on c.c2=b.b2 where b.b1>4;

Block Nested-Loop join 和 Batched Key Access join : 这两个关联算法和Index Nested-Loop join算法类似，不过因为他们能使用join buffer，所以他们可以每次从驱动表筛选一批数据，而不是一条。同时每个join关键字就对应着一个join buffer，也就是驱动表和第二个表用一个join buffer，得到的块结果集与第三个表用一个join buffer。

（6）总结

不论是Index Nested-Loop Join 还是 Block Nested-Loop Join 都是在Simple Nested-Loop Join的算法的基础上进行优化，这里 Index Nested-Loop Join 和 Block Nested-Loop Join 算法是分别对Join过程中循环匹配次数和IO 次数两个角度进行优化。

Index Nested-Loop Join 是通过索引的机制减少内层表的循环匹配次数达到优化效果，而Block Nested-Loop Join 是通过一次缓存多条数据批量匹配的方式来减少内层表的扫表IO次数，通过 理解join 的算法原理我们可以得出以下表连接查询的优化思路。

1、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)

2、为匹配的条件增加索引(减少内层表的循环匹配次数)

3、增大join buffer size的大小（一次缓存的数据越多，那么内层表的扫表次数就越少）

4、减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）


假设 A 表 N 个记录，B 表 M 个记录

	Simple Nested-Loop Join	Index Nested-Loop Join	Block Nested-Loop Join
驱动表扫描次数	1	1	1
被驱动表扫描次数	N	0	N * (used_column_size) / join_buffer_size + 1
join 比较次数	N * M	N + M(match)	N * M
回表次数	0	M(match)	0

Mysql多表连接查询的执行细节（一）

数据库基础（七）Mysql Join算法原理

MySQL 深入浅出：join 的使用和原理

八、SQL的执行过程

MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。

• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

（1）各组件介绍

连接器

身份认证和权限相关(登录 MySQL 的时候)

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存(MySQL 8.0 版本后移除)

主要用来缓存执行的 SELECT 语句以及该语句的结果集。

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

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

分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来做什么的，分析器也会分为几步：

1. 词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

2. 语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

优化器

优化器的作用就是以它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

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

执行器

首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用存储引擎的接口，返回接口执行的结果。

（2）查询语句

连接器—>查询缓存—>分析器—>优化器—>执行器(权限校验)—>存储引擎

（3）更新语句

连接器—>分析器---->优化器—>执行器(权限校验)—>存储引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

redo log（重做日志）（InnoDB独有）

在 MySQL 中如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本很高。为了解决这个问题，MySQL 的设计者采用了WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

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

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

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

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

为什么bin log没有crash-safe的能力？

redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。

当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。

举个例子，binlog 记录了两条日志：

给 ID=2 这一行的 c 字段加1
给 ID=2 这一行的 c 字段加1

在记录1刷盘后，记录2未刷盘时，数据库 crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备。

为什么REDO LOG具有CRASH-SAFE的能力，是BINLOG无法替代的？

binlog（归档日志）（server层，通用）

为什么会有两份日志呢？

因为最开始 MySQL 里并没有 InnoDB引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

这两种日志有以下三点不同：

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

bin log 主要用来主从复制 和 数据恢复：人工恢复，通过使用 mysqlbinlog 工具来恢复数据
redo log主要用来崩溃恢复

两阶段提交

这是为了让两份日志之间的逻辑一致。假如不两阶段提交：

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

• 判断 redo log 是否完整，如果判断是完整的，就立即提交。
• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

一条sql语句在mysql中如何执行的

一条SQL更新语句的执行过程

九、关系数据库设计理论

1、函数依赖

• 记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。
• 如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。
• 对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。
• 对于 A->B，B->C，则 A->C 是一个传递函数依赖。
• 平凡的函数依赖：X→Y，其中，Y是X的子集。
• 非平凡的函数依赖：X→Y，且Y不是X的子集。不特别声明，总是讨论非平凡依赖。

2、异常

• 冗余数据：某个属性的值重复次数过多
• 插入异常：没有主键属性的时候，其他属性无法插入
• 删除异常：因删除某个属性所在的行而连带彻底删除了某些其他属性
• 更新异常：属性的某个值发生改变时，因处理不当而没有完成该值对应的所有元组的修改

3、范式

• 第一范式（1NF）： 数据表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成。
• 第二范式（2NF）： 在第一范式的基础上，数据表中所有非关键字段都完全依赖于任意一组候选关键字。
• 第三范式（3NF）： 在第二范式的基础上，数据表中所有非关键字段都不传递依赖于任意一组候选关键字。（一般企业都要求满足）
• BCNF范式：也叫作扩充的第三范式，或修正的第三范式。要求关系模型中所有的属性（包括非键属性和键属性）都不传递地依赖于任何候选键。

关键字：如果某个字段或多个字段的值可以唯一地标识一条记录，则该字段或字段组就称为关键字。
主关键字：如果一个关键字是用以标识每条记录的唯一性，并作为该表与其他表实现关联之用，则称其为主关键字（主键，primary key）或主码。
候选关键字：除主关键字以外的其他关键字称为候选关键字。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库三大范式（1NF,2NF,3NF）及ER图

2NF

3NF

十、相关聚合函数特性

1、聚合函数忽略null值的情况

AVG()函数忽略列值为NULL的行。
MAX()函数忽略列值为NULL的行。
MIN()函数忽略列值为NULL的行。
SUM()函数忽略列值为NULL的行。

COUNT()函数有两种情况：

1.使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

2.使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

例如：
CREATE TABLE student2(
	id INT,
	sname VARCHAR(20),
	address VARCHAR(20) 	
);

INSERT INTO student2 VALUES (NULL,NULL,NULL);

1.      SELECT COUNT(id) FROM student2;//结果为0。

2.		SELECT COUNT(*) FROM student2; //结果为1。

SQL语句中聚合函数忽略NULL值的总结

2、sql 语句中count()有条件的时候为什么要加上or null。

如count(province = ‘浙江’ or NULL) 这部分，为什么要加上or NULL，直接count(province=‘浙江’)有什么问题吗？

答案：
因为当 province不是浙江时 province=‘浙江’ 结果false，不是 NULL。count在值是NULL是不统计数， （count(‘任意内容’)都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数）。加上or NULL ，当province不为浙江时，province = ‘浙江’ or NULL 的结果是NULL，Count不会统计上这条记录数

sql 语句中count()有条件的时候为什么要加上or null