MySQL之（四）整理部分原理

最新推荐文章于 2021-10-16 16:30:07 发布

zglx123

最新推荐文章于 2021-10-16 16:30:07 发布

阅读量95

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_42324034/article/details/117934561

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Mysql 的基础架构

在这里插入图片描述

查询执行流程

select * from test where id =10

在这里插入图片描述
大体来说Mysql 分成了Server和存储引擎两部分
Server包括了连接器、查询缓存、分析器、优化器、执行器，并且涵盖了mysql大量的内置函数，比如日期、时间、数学和加密函数等。
存储引擎负责数据的存储和提取。它的架构是插件式的，支持我们熟知的innodb、myisam等多个数据存储引擎在mysql5.5以后,innodb称为了默认存储引擎。

binlog & redolog

binlog 是MySQL的Server层实现的，所有的引擎都可以用，redolog是Innodb引擎特有的
redolog是物理日志，记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID为2这一行的c字段+1
redolog是循环写的，空间固定会被用完，binlog是可以追入写的，意思就是写到一个大小的文件后会切换到另外一个文件，不会覆盖已有的文件

执行更新流程

update T set c = c+1 where ID = 2;

在这里插入图片描述

执行器会去找ID=2的这一行数据，ID是主键，可以直接通过聚簇索引找到这一行数据。
但是如果这一行所在的数据页就在内存中，它就会直接返回给执行器，否则才根据索引去磁盘找，读入内存返回
执行器拿到数据后，给c字段+1，得到新的一行数据，准备调用存储引擎的接口写入内存。
存储引擎先将这行数据更新到内存，同时记录到redolog里面，这时候的redolog处于prepare状态，告知执行器完成了，随时准备提交事务。
执行器生成这个操作的binlog,然后写入磁盘。
执行器调用存储引擎的提交事物接口，引擎把redolog刚才的prepare状态改成commit状态，更新完成。

ID自增的好处

自增ID是递增的，是递增插入的场景，每次插入一条新纪录都是追加操作，都不会涉及挪动其他记录，重点是不会触发B+tree叶子节点的分裂
主键索引数叶子节点存储的是整行数据，其他索引树存储的叶子节点是主键，当使用自增Id为主键，一般占用的树空间比较小，那么普通索引存储的空间就比较大（根据主键索引的查询，只需要一棵树，而根据非主键索引查询，会根据非聚簇索引得到主键，然后在到聚簇索引获取行数据）

事物隔离的实现

多版本控制(MVCC)
实际上mysql在更新每条记录的时候会记录一条回滚操作，通过回滚操作可以得到前一个状态的值
假设一个值从1按顺序改成了2,3,4,在回滚日志里会有如下的记录
在这里插入图片描述
上述图中read_view代表不同时刻的事物，在值变化的过程中，值可以有多个版本被不同时刻的事物读取，这就是mysql的多版本并发控制。

主键索引&非主键索引

前面说过，主键索引数的叶子节点存储的是整行数据。在Innodb里，主键索引也被称为聚簇索引
非聚簇索引的叶子节点才存储的是主键的值，在Innodb里，非主键索引被称为二级索引
注意：也就是说，基于非主键索引的查询需要多扫描一颗索引树。因此，我们在应用中应该尽量使用主键索引。
对于普通索引来说，主键长度越小，索引数的叶子节点就越小，在相同空间下，索引数能存储更多的索引值

Mysql 锁

全局锁

顾名思义，全局锁就是对整个数据库加锁，命令是FTWRL，使用的场景就是全数据库备份，不允许有任何数据的读写，即使有，命令都会等待执行，要读写也是命令执行完以后才开始。

表级锁

Mysql 里面表级锁有两种，一种是锁表，一种是元数据锁（meta data lock mdl）

表锁的语法就是 lock tables [表名] write/read
元数据锁MDL不需要显示调用，在访问一个表的时候会自动加上。
其作用就是保证读写的一致性。如果一个线程正在读，期间又有另外一个线程改变了表结构，那么查询线程取到的数据就跟表结构对不上，所以需要MDL锁。
Mysql 在5.5 版本引入MDL，当对一个表进行操作的时候加上MDL读锁;
当要对表结构变更操作的时候加MDL写锁，读锁之间互不影响，因此你可以有很多个线程同时对表进行增删查改。
但是读写锁、写锁之间是互斥的`当无法获取MDL锁的时候，线程处于阻塞状态，所以MDL是用来保证表结构变更操作的安全性。

行锁

行锁就是针对数据表中记录的锁，行锁是给各个引擎实现的，有些引擎不支持行锁，比如myism就不支持行锁，这也是被替代成Innodb的原因
Innodb事物中，行锁是在需要的时候才加上，但并不是不需要就立马释放的，它需要等到事物结束了才释放。

☆☆☆思考题：

 CREATE TABLE `t_k` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

在这里插入图片描述

事物A、B分别查询的k是多少呢？(默认autocommit = 1)
这里涉及mysql的mvcc相关知识了，mysql的可重复读、读已提交的事物实现在于查询不同时刻版本的视图，所以取到的结果就不一样了

begin/start transaction 命令并不是事物的一个起点，在执行到这里之后的第一个sql语句事物才是真正的开启，如果你想马上启动一个事物，可以使用start transaction with consistent snapshot 这个命令。

第一种启动方式 一致性视图是在执行第一个快照读语句创建的（也就是是开始事物）
第二种启动方式一致性视图是在start transaction with consistent snapshot 立马创建事物

事物C并没有显示的调用begin/commit，说明这个update语句本身就是一个事物，语句执行完就自动提交。

可重复读隔离级别下 只需要在事物开始的时候就创建一致性视图，之后事物里的其他查询语句共用这个一致性视图，也就是说只要当前事物不提交，读取的数据都是开始事物时刻那个版本的数据。
读已提交级别每一个语句执行之前都会重新计算一个新的视图（获取已经提交过的诗句）
所以答案为
读已提交的情况下，事物A、B查询的k值是2，3
可重复读的情况下，事物A、B查询的k值是1，3

前缀索引

– 使用整个emali字段作为索引
alter table s_user add index index1(email);

– 前缀索引，使用email 前面6位作为索引
alter table s_user add index index2(email(6));

优点

前缀索引相对比较节省索引数的空间

缺点

由于区分度比整个字符串的索引要低一些，所以会增加扫描查询的次数，还有就是会影响覆盖索引的使用，无法直接从索引树取到想要的结果

MySQL突然抖动原因

InnoDB在处理更新语句的时候，只是做了一个redolog和更新内存的操作，但是内存与磁盘很有可能出现不一致，所以mysql会自行将redolog的变化更新到磁盘中，这个操作叫做flush
在flush过程中，可能会出现磁盘数据页跟内存数据页不一致的时候，这种内存页称之为“赃页”，内存写入磁盘后，内存和磁盘上数据页的内容一致就称为干净页
MySQL平时都是很快的操作，其实就是写内存和日志 mysql突然抖动一下，很有可能就是做刷赃页。
所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

关于count()的用法和性能比较

count(param)本来就是一个聚合函数 ,如果param不是NULL，就累计加1，否则就不加，最后返回累加值

count(id)，Innodb引擎会遍历整张表，把每一个id取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
count(1)，innodb引擎会遍历整张表，由于1是常量，不可能为NULL,所以就是按行累加的
count(字段),如果这个字段是not null 就累加，如果null就不累加
count(**)是一个例外，并不会把全部字段取出来，而是专门做了优化，不取值。count()肯定不是Null,按行累加。
**结论：**性能比较结果：count(字段)<count(主键 id)<count(1)≈count()，所以建议，尽量使用 count(*)进行统计。

redolog 一般设置多大

如果redolog 设置的太小，会导致很快就被写满，会导致操作系统强行刷页,这样WAL机制的能力就发挥不出来，所以还是看你的机器配置，设置成不同的文件和大小。

order by 排序相关的执行流程


CREATE TABLE `t_order` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

查询语句select city,name, age from t_order where city = ‘杭州’ order by name limit 100

我们先来看看这个SQL查询语句的执行过程，先来看一下city这个索引的B+tree
在这里插入图片描述
根据B+tree 索引树图，来看看mysql orderby 的执行流程

初始化排序缓存区sort_buffer 然后放入要查询的字段，如city, name, age
根据索引树，找到key为’杭州’的叶子节点，根据这个叶子节点存储的主键id，可以找到整行数据，然后取这行数据的city, name, age 对应的值，存入sort_buffer
从索引city取下一个记录的主键id；
重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_6
对sort_buffer中的数据按字段name 做快速排序
按照排序结果取面前100行返回给前端。

执行流程的示意图如下
在这里插入图片描述
图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。 sort_buffer_size，就是MySQL为排序开辟的内存

幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（别的事务在期间新增的行）。隔离级别为可重复读的情况，可能出现幻读；
需要对“幻读”做一个说明：
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读（可理解为更新类型的读，在更新时会先一次查询再更新）”下才会出现。

如何解决幻读

产生幻读的原因是，行锁只能是锁住行，但是新增插入这个操作是更新记录之间的间隙。因此为了解决幻读的问题，InnoDB引入了新的锁间隙锁

间隙锁：锁的两个值之间的空隙，比如初始化插入了6个记录，这就产生7个间隙, 这样就确保了无法再插入新的记录

间隙锁跟我们之前碰到过的锁都不太一样。间隙锁是防止“往这个间隙中插入一个记录”，间隙锁之间都不存在冲突关系（即可能存在一个间隙之间加了多把间隙锁）。

MySQL有哪些常见的影响性能的问题

短风暴链接
正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

慢查询性能问题
慢查询主要有三种可能
1、索引没有设计好；
2、SQL 语句没写好；
3、MySQL 选错了索引。

QPS暴增
由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

binlog 的写入机制

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把 binlog cache 写到binlog文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定大小的数据，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

MySQL主备一致的实现流程

主库和备库之间维持了一个长连接，主库内部有一个线程专门用来服务备库的长连接。我们来看看主备执行一个事物，主库和备库之间干了什么

在备库上执行change master命令，设置主库ip地址、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置就包含了文件名和日志偏移量
在备库上执行start slave命令，这时候备库会启动两个线程，一个io_thread，一个sql_thread，其中io_thread负责和主库建立连接
主库校验完用户名、密码以后开始按照备库传过来的位置，读取自己的binlog给备库
备库拿到binlog后，写到本地文件，称为中转日志(relay log)
sql_thread 开始读取中转日志，解析出其中的命令并执行
备份完成！