MySQL开发中易忽略知识总结-0

隔离级别

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

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

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

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

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

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

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务期间都用这个视图。在读提交隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。

这里需要注意的是，读未提交隔离级别下直接返回记录上的最新值，没有视图概念。而串行化隔离级别下直接用加锁的方式来避免并行访问。

一个事务只需要在启动的时候声明说：“以我启动的时候为准，如果一个数据版本是在我启动之前生成的就认。如果是我启动之后才生成的，我就不认，我必须要找到它的上一版本。”

当然，如果上一个版本也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认得。

在实现上，InnoDB为每个事务构造了一个数组，用于保存在这个事务启动瞬间当前正在活跃的所有事务ID。活跃指的就是，启动了但还没提交。

数组里面的事务ID的最小值记为低水位(low_limit_id)。当前系统里面已经创建过的事务ID的最大值加1记为高水位(up_limit_id)。

这个视图数组和高水位，就组成了当前事务的一致性视图。而数据版本的可见性规则就是基于数据的row trx_id和这个一致性视图的对比结果得到的。

IMG_20190212_161057.jpg

绿色可见，红色不可见。 如果是黄色，分成2种情况。1.row trx_id在数组中，表示这个版本是由还没提交的事务生成，不可见。 2.row trx_id不在数组里面，表示这个版本是由已经提交了的事务生成的，可见。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见之外，还有3种情况： 1.版本未提交，不可见。 2.版本已提交，但是是在视图创建之后提交的，不可见。 3.版本已提交，而且是在视图创建前提交的，可见。

而更新数据都是先读后写，而这个读，只能读当前的值，被称为当前读。 可重复读的核心就是一致性读，而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待了。

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

事务启动的时候还需要保存"现在正在执行的所有事务ID列表"，如果一个row trx_id在这个列表中，也是不可见的。

查看事务是否被锁住可以通过select * from information_schema.INNODB_TRX语句查看。

索引

MySQL在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数。

mysql执行语句过程_图来自网络.png

mysql执行语句过程_图来自网络.jpg

分析器.png

看完这3张图片，想必你对MySQL执行SQL语句过程的理解更加深入了。

回到正题，上文所说的"统计信息"就是索引的区分度。很显然，一个索引上不同的值越多，这个区分度越好。而一个索引上不同数值的个数，我们称之为基数( cardinality)。也就是说，这个基数越大，索引的区分度越高。

那么MySQL是怎得到索引的基数呢？ 把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高，所以只能选择采样统计。

看这个例子，我们查询t这张表有100000条数据

image.png

而通过explain语法查看基数，只有97739条。

image.png

也可以通过show index from test.t查看基数。

image.png

采样统计的时候，InnoDB默认选择N个数据页，统计这些界面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以当变更的数据行数超过了1/M的时候，会自动触发一次索引统计。

通过innodb_stats_persistent设置存储索引统计的方式。 我们可以通过show global variables like '%innodb_stats_persistent%'来查看当前系统中存储索引统计的方式。innodb_stats_persistent_sample_pages为20说明采样的方式是选择20个数据页。

image.png

当innodb_stats_persistent为ON时，统计信息会持久化。N=20，M=10 当innodb_stats_persistent为OFF时，统计信息只会存储在内存中。N=8，M=16

基数(Cardinality)是不精确的，优化器还要判断这个语句本身要扫描多少行。

使用analyze table test.t用于重新统计索引信息。

image.png

我们可以看到explain select * from test.t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1这样的SQL语句。之所以优化器会选择索引b，是因为它认为使用索引b可以避免排序。如果选择索引b，不需要再做排序，只需要遍历就行了)。所以即使是扫描行数多，也会判断代价更小。

image.png

如果是order by b,a的话，按照b,a排序，扫描行数成为了影响决策的主要条件。于是就会选择了扫描100行的索引a。

image.png

如果你遇到过优化器误判的现象，可以使用force index来强制指定索引。

话外音：再说一下MySQL执行计划中的extra列。 1.Using Index:查询的列被索引覆盖，并且where筛选条件是索引的前导列。

2.Using Where,Using Index:查询的列被索引覆盖，并且where筛选条件不是索引的前导列或者是查询的列被索引覆盖，并且where筛选条件是索引前导列的一个范围，同样意味着无法直接从索引中查到复合条件的数据。

3.NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列。意味着用到了索引，但是部分字段未被索引覆盖，必须通过回表来实现。

4.Using Where:查询的列未被索引覆盖，where筛选条件非索引的前导列。

5.Using Index Condition：查询的列不会在索引之中，where条件中只是一个索引前导列的范围。