分页优化
我们知道在日常查询数据的时候,分页查询是很正常的
假设这里我给出一个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事务即可
特殊情况:假设我事务自己修改数据
那么对于本事务肯定是可见的呀,得到的也是最新数据