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条件中只是一个索引前导列的范围。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券