mysql的一些介绍

执行计划

执行计划应该都很熟悉了，一般有慢查询的话，第一件事都是先看下这条sql的执行计划：

执行计划一共有10个字段，挨个看下吧。

• id：这个是执行的序列号，也就是sql执行的顺序，像什么子查询，关联查询，数字越大的越先执行，数字相同的从上往下执行。

• select_type：查询类型，用于区别普通查询、联合查询、子查询等，没什么用。

• table：查询的表，也没什么用；

• type：索引类型，这个是最关键的了，是使用索引快慢的指标，有这几种值（从快到慢）：
  system>const>eq_ref>ref>range>index>ALL，一般来说至少要到range级别。
  system：只有一条记录，一般就是系统表了，没什么意义；
  const：单表中只有一个匹配行，一般都是根据主键或者唯一键查询，速度很快；
  eq_ref：唯一性索引扫描，也是主键或者唯一键，和const的区别是eq_ref主要是在关联查询时使用主键或者唯一键关联；
  ref：使用非唯一性索引或者索引前缀查询，可能会找到多条结果行；
  range：范围查询，像between、in等关键字，会查询一定范围的索引；
  index：会遍历整颗索引树，这就很慢了，应该尽量避免；
  ALL：没有走索引，遍历整张表，这就不说了。

• possible_keys：查询条件字段上的索引，但是不一定会用到，不是很重要；

• key：实际上使用的索引，NULL则表示没有使用索引；

• key_len：索引长度，理论上是越短越好了；

• ref：索引列？

• rows：大概遍历的行数；

• Extra：额外信息

普通索引和唯一索引的区别

解释这个之前需要先引入另外一个概念：change buffer。

• change buffer：
  首先了解下数据页的概念，在计算机中有一个局部性原理，基于这个原理，InnoDB从磁盘上读取数据到内存中时是按数据页（默认为16KB）为单位读取的。
  InnoDB在更新数据的时候为了减少随机磁盘io并不会直接去更新磁盘数据，而是分为两种情况：
  1、如果要更新数据的数据页刚好在内存中，则会直接更新内存中的数据记录；
  2、如果数据页不在内存中，则会将更新操作缓存在change buffer中，如果在后续查询中将该数据页读取到内存中的话，会将该更新语句执行到内存数据页中，这个过程叫merge。
• 查询区别：因为索引是用B+树实现的，B+树本身就是有序的，所以在查询时都是从根节点开始采用二分法往下遍历就行了，例如当要查询a=1时：
  1、唯一索引因为它的唯一性，当它查询到a=1的数据时就会直接返回结果了；
  2、普通索引还会继续查询，直到查询到a!=1的数据记录为止，这个两者差别不大。
  在查询上唯一索引和普通索引的区别体现在一种极端情况下：查询结果数据刚好在内存数据页中的最后，唯一索引会直接返回，而普通索引则要多进行一次磁盘io，继续查询。因为这是极端情况，所以总体说，唯一索引和普通索引在查询上区别不大。
• 更新区别：唯一索引因为要维护数据的唯一性，所以change buffer在唯一索引上是无效的，在更新时且内存中不存在该数据页时，唯一索引需要判断更新的数据是否唯一，需要进行一次磁盘io读取数据来做判断，change buffer变得毫无意义。所以考虑到change buffer对更新操作的优化，在更新上普通索引比唯一索引更快。

binlog、redo log、undo log区别

先说下几点区别：

• binlog是逻辑日志，redo log和undo log是事务日志；
• binlog是Server层的，redo log和undo log是存储引擎层的，也就是和InnoDB有关；
• binlog日志的作用是主从同步和数据恢复，redo log和undo log的主要作用是支持事务的一致性和回滚。

binlog

概念

binlog是MySQL数据库的二进制日志，它记录了所有的DDL和DML语句（不包括select语句），以事务的形式记录。

记录格式

binlog有三种模式：statement、row、mixed。

• statement：仅记录执行的sql语句，优点是日志文件比较小，缺点是有一些语句准确性差，有一些语句不能复制，像now()、uuid()等函数，一般不推荐；
• row：记录实际数据的变更，优点是能准确复制数据的变更，缺点是日志文件会比较大，推荐使用这种，5.7.7之后默认也是这种；
• mixed：混合模式，一般复制使用statement模式，statement模式无法复制的语句就使用row模式，优点是文件大小适中，准确性强，缺点是会导致主从数据不一致问题，所以一般也不推荐。

刷盘时间

其实binlog也是有缓存的，并不会直接写入磁盘，这个写入磁盘的时间可以通过sync_binlog参数配置，取值范围是0-N：

• 0：不强制要求，由系统自行决定写入时间，显然这是不行的；
• 1：每次commit之前都先binlog日志写入磁盘，这是最保险的方式，也是默认方式，只是需要损失一些性能，但是binlog写入磁盘是顺序IO，效率也是很高的；
• N：每N个事务commit的时候，才将binlog日志写入磁盘。

redo log

redo log日志使数据库具有了crash-safe的能力，要说清楚crash-safe，先要了解下MySQL更新数据的过程，前面有提到过，InnoDB在更新数据的时候是不会直接更新磁盘数据的，而是先更新内存中数据，然后再刷脏页数据落盘，但是如果在刷脏页之前数据库挂了这部分还没来得及落盘的数据不就丢失了吗？显然这是不符合需求的，MySQL用到了WAL（Write Ahead Logging）技术，也就是日志先行，在修改数据之前，先记录修改日志。

更新数据的过程

Server层就先不看了，先看下InnoDB的处理，上张图（网上找的，侵删）：

这张图是语句"update table set C = C + 1 where ID = 2"的执行过程：

• 执行器找InnoDB取ID = 2的数据页，如果内存中没有就去磁盘上读取，并加载到内存中，这里需要说明一下的是，上面也提到过，因为有change buffer的存在，也有可能不用去磁盘读取数据，而是就更新操作缓存在change buffer中，过后再进行merge操作；
• 执行器调用InnoDB修改数据；
• InnoDB将字段C更新为C+1，并将这个修改写入redolog日志，让redolog日志处于perpare状态，然后告知执行器更新好了，随时可以提交事务；
• 执行器收到通知后将这个修改数据追加到binlog日志中，然后调用InnoDB提交事务；
• InnoDB将redolog日志修改为commit状态，一次修改操作就执行完了。

可以看到，所有的操作都是内存操作，没有一次磁盘操作，值得一提的是redolog和binlog也有自己的内存缓存，过后再刷盘持久化。

两阶段提交的必要性

主要也是为了数据和binlog的一致性，如果：

• 先写redolog，后写binlog，但是中间数据库断电了，数据库恢复之后会根据redolog日志恢复数据，但是这个时候binlog日志里面就少了一条数据了，失去了归档的意义；
• 先写binlog，后写redolog，中间数据库断电了，change buffer中的数据丢失了，数据库恢复之后就会丢失一条数据，binlog中也会多出一条日志记录来。

两阶段提交，让binlog做事务的管理者，现在假设下数据库断电的各个时间点：

• 在图中1之前（redolog和binlog都没刷盘）：这种情况没办法，没数据持久化，数据丢失；
• 在1-2之间：会先将redolog事务恢复，但是binlog中没有修改数据，会使事务回滚（保持了一致性）；
• 在2-3之间：redolog和binlog都有修改数据，会先恢复事务，然后提交事务；

为什么binlog不具有crash-safe的能力

binlog只是归档日志，因为change buffer的存在，会使磁盘上的数据和binlog日志有差异，当发生crash时，change buffer这部分数据会丢失，binlog并不知道有哪些脏数据没有落盘，无法恢复数据，如果有主从的话，还会导致主从不一致。

为什么有了redo log才具有crash-safe能力

要解释这个问题，就要先看下redo log是什么，redo log日志是一个固定大小，循环写的日志文件，相当于是个环形，它有两个点：write pos（日志写入点）和check point（脏数据刷盘的点），如图：

• 当脏数据刷盘之后，这部分空间释放掉，可以再次写入了；
• 当write pos = check point时，它就相当于是一个新的日志文件了；
• 它会记录脏数据刷盘的点（check piont），这样当数据库恢复的时候，可以直接根据redo日志恢复change buffer的数据；

undo log

undo log日志的概念就比较简单了，就是事务需要回滚时的回滚日志，好像没什么介绍的，在事务执行之前生成。

MVCC 多版本并发控制

MVCC主要适用于RC（Read Committed 读已提交）、RR（Repeatable Read 可重复读）隔离级别。
拿RR作为例子解释下MVCC的概念：这里可以先思考下RR隔离级别可以怎么实现，肯定不能读取数据库当前数据了，因为数据可能已经被其他事务修改过了，那怎么办？那就只能是生成数据快照了，MVCC就是干这个的，MVCC保存了数据在某个时间点的快照。

一致性视图 read-view

MVCC机制保存了很多个数据快照，但是哪些快照是这个事务可以读取了，这个就是一致性视图（read-view）决定的了，因为事务id是递增的，所以一致性视图会根据自身的事务id来确定哪些快照是可读的，大概过程是：

• 绿色部分是已经提交的事务的快照版本，是可以访问的；
• 红色部分是没有开始的事务的快照版本，不可以访问；
• 黄色部分，如果是活跃的，就是未提交的，不能访问，不是活跃的就是已经提交了的，可以访问。
  说的有点复杂，其实就是一句话，提交了的事务的快照版本可以访问，没有提交的不能访问。

隔离级别和一致性视图

有了一致性视图就可以很轻松地解释RC、RR隔离级别的实现原理了。

• RC：读已提交，就是一个事务里面可以读到别的事务已经提交的数据，两次读取数据可以不一样。这个很简单，只要每次读取的时候重新生成一致性视图就可以了，这样新的一致性视图里面就包含了新提交的事务数据快照，也就是可以读到新提交的数据了；
• RR：可重复读，和上面差不多，只是在事务开启的时候，就生成一致性视图，后面操作一致性视图不变，这样每次读取数据的一致性视图都一样，能读到的快照版本也一样，当然也就能重复读了。