MySQL进阶 分页优化 表连接原则 mvcc原理

分页优化

我们知道在日常查询数据的时候,分页查询是很正常的

假设这里我给出一个sql

select * from a limlt 10000,10;

实际上这里的1w多条数据是全查出来的

最后删除掉前1w条数据

假设这里需要查询的基准数值越来越大

假设从1w变到了9w

这里的效率就会非常慢了

于是我们将其这样优化

select * from a where id>9w limlt 5;

这样sql的查询效率就会好很多了

但是有人会问,这是id连续的情况呀

万一我不想使用主键排序咋办

于是我们这样优化

select * from employee e join (select id from a order by name limit 9w,5)
-> ed on e.id = ed.id; 

这样虽然有一个临时表的开销

但是也比直接使用name回表来的快

临时表由于只有很少的数据,所以使用的是全表扫描

其余也是都用上了索引字段

相对效率还不错

 表连接原则

假设我们这里创建两个一样的表t1,t2

t1添加1w条数据

t2添加100条数据

数据内容如下

注意表中字段a是有索引的,b是没有索引的

这个时候我们执行一下按照属性a进行连接

select * from t1 join t2 on t1.a = t2.a;

这个时候我们其实使用到的就是一个NLJ(嵌套循环连接算法)

总共扫描的是200行记录

本质上就是将b表全扫描出来

一条数据那出来对应到t1根据索引直接扫描到一条数据

一直扫描到100条为止

那么使用b做链接条件,如何呢?

select * from t1 join t2 where t1.b = t2.b;

这里会将t2加载到内存中的jin_buffered缓存中,默认最大空间位256k

如果t2的数据量大于256k就会分批加入

这个就会对t1进行1w次扫描一条一条拿出来进行比对

如果是极端情况下得扫描100w次才能拿到过滤结果

注意这里的原则就是小表驱动大表,关联字段最好带有索引,不然扫描就是磁盘扫描,效率很低

下面介绍一下in和exists的使用 

count的效率对比

count(*) count(1) count(字段) count(主键id)

这四个执行的效率如何呢???

首先我们先说结论,都差不多

但是硬要分出一个高下

那就是

count(1) ≈ count(*)

其余两个的效率跟字段是否有索引有关

字段有索引的话效率是高于主键索引的,反之亦然

原因: 字段扫描的是辅助索引,更加轻量级  

         主键扫描的是聚簇索引,更加重量级

然后有人会觉得count(*)的效率应该不高呀

其实不然,mysql在5.7之后对其进行了优化,是一个按行累加的效果,并不会查找所有数据

可以理解为是内存中放一个计数器,遇到一行就进行原子++

然后count(1)是按照常量1进行一个统计

行锁 表锁 页锁

我们知道为了并发安全mysql提供了这些锁机制

行锁就是对一行进行锁定

页锁就是对B+树数据结构的叶子结点一页进行锁定

表锁就是对一张表进行锁定

一般MyISAM只有表锁,因为其不支持事务

下面先不探讨表锁,表锁一般是进行数据迁移的时候会锁表操作

从加锁的效率来看也是表锁的效率更高

因为行锁需要先查询到对应的行,这里页设计到一个遍历的操作

而表锁就是直接给表加上一个标记即可

行锁则需要找到对应的行实际上是给id字段加上一个锁的标记

但是如果没有id索引,就会形成对应的表锁

MVCC

我们都知道mvcc是数据库实现可重复读(RR)隔离级别的关键

那么mvcc是什么呢,为啥能保证可重复读的特性呢,下面我们一一解答

在这之前我们先谈一谈间隙锁和临键锁

间隙锁就类似于分段锁

其实就是按照id将整张表进行划分

假设是这样的一张表

对应的间隙锁就可以加在3,10   10,20    20,正无穷的开区间中

我们只需要对其中的数据进行加锁

比如在事务中对18加锁

这里对应的10到20的开区间就都不能进行写操作了

临键锁其实就是一个开闭区间的意思

假设(10,20]左开右闭区间进行加锁就是一个临键锁

其实就是间隙锁和行级锁的结合

常见锁等待机制

我们可以根据这里的属性进行分析

使用一个sql语句

show engine innodb status;

然后我们就能看到对应的日志了

假设这里给一个死锁

比如我现在让事务1对1号数据加锁

事务2 对2号数据加锁

事务一尝试对2号数据加锁

事务2尝试对一号事务加锁

执行以上sql就可以看到对应的死锁发生原因...

最大等待锁的时间默认为50s,等不到就放弃

这时候我们可以根据查找到的执行事务的线程号杀死事务

对应的trx_mysql_thread_id就是对应的线程,通过kill 线程号杀死即可

 MVCC

这里的mvcc(多版本并发控制)

其实就是基于undoLog日志进行的一种解决方案

我们先以RR锁为例

我们每次读取到的始终是第一次select取得的数据

即使后面的事务修改了数据并提交也不行

读到的还是对应第一个版本的数据

我们先看看undoLog的版本链控制表大概有啥

这里前面的是我表中的字段 

trx_id 事务号

roll_pointer 回滚指针 指向的就是上一个版本的数据 

插入对应的就是del

注:执行查询操作是不会开启事务的(会有临时事务但是不是真的事务)

只有执行修改操作的时候才会真正开启事务

下面我们解释对应的mvcc可见性算法,也就是mvcc的执行原理

我先给上一个示例

先解释一下

从上到下分别按照时间线操作

事务100更新了test表的数据

事务200更新了test表的数据

事务300更新了account表的数据

并且提交

这时候select1事务开启

就会读取到一个 readView(唯一性视图) 可以理解为一个当时时间点活跃事务的切片

大概包含以下内容

一个数组,一个最大值

数组中包含所有活跃事务,其中最重要的就是最小的事务id和最大的事务id

最大的事务id直接拎出来,其余在数组中

注:事务id是根据时间线递增的

我们先给一个图理解一下

最小id之前的事务一定是已经提交了的事务

最大id之后的事务没提交

中间是可能提交可能没提交的事务

这时候就有一个版本链比对原则,和之前的undoLog就联系起来了

在中间的不在视图数组中的就是可见的也就是最大的是可见的,其余不可见,因为没提交

后续不管怎么操作怎么commit都获得的是同样的视图结果

即使来了一个事务修改成1k,我还是只能看到对应的500

我们可以根据版本链去推

我们可以看到时间线上事务2已经进行了修改,但是我修改是之后

100仍然在活跃事务数组中,这里对应的select事务仍然看到的是500

这也就是为啥能保证可重复读的关键了

而对应RC也就是读已提交状态下,对应的视图是会随着修改提交操作而改变的

这也就是为啥他能每次拿到最新的数据库数据

那我RR级别咋拿到最新的呢,新开一个select事务即可

特殊情况:假设我事务自己修改数据

那么对于本事务肯定是可见的呀,得到的也是最新数据