MySQL进阶学习一(2024.10.07版)

2024-10-06

--------------------------------------------------------------------------------------------------------------------------------

1.一条SQL语句是如何执行的

单进程的多线程模型

MySQL的物理目录

show global variables like "%basedir%";

InnoDB是如何存储表数据的

InnoDB其实是使用页为基本单位来管理存储空间的，默认的页大小为16KB。
对于InnoDB存储引擎来说，每个索引都对应着一棵B+树，该B+树的每个节点都是一个数据页，数据页之间不必要是物理连续的，因为数据页之间有双向链表来维护着这些页的顺序
InnoDB的聚簇索引的叶子节点存储了完整的用户记录，也就是所谓的索引即数据，数据即索引

系统表空间(system tablespace)
系统表空间可以对应文件系统上一个或多个实际的文件，默认，InnoDB会在数据目录下创建一个名为ibdata
独立表空间(file-per-table tablespace)
在MySQL5.6.6以及之后的版本中，InnoDB默认为每一个表建立一个独立表空间，根据参数innodb_file_per_table控制

客户端与服务端的交互

数据库的核心在于存储引擎

MySQl逻辑架构

向MySQl发送一个请求---连接器

客户端如果长时间没有动静，连接器会将其断开，由wait_timeout控制，默认是8

长期使用长连接会导致内存飙升

向MySQl发送一个请求---查询缓存

将缓存放入一个引用表之中，通过哈希值进行引用

查询缓存时按需使用的，参数querycache type设置成DEMAND(默认不使用查询缓存）
虽然查询缓存有时可以提千系统性能，但也不得不因维护这块缓存而造成一些开销,比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL8.0中删除。

向MySQl发送一个请求---分析器

向MySQl发送一个请求---优化器

基于成本的查询优化器（Cost-Based Optimizer），IO和CPU

向MySQl发送一个请求---执行引擎

三个阶段的权限判断

各种不同的存储引擎向上边的MySQL服务器层提供统一的调用接口(也就是存储引擎API)包含了几十个底层函数，像"读取索引第一条内容"、"读取索引下一条内容"、"插入记录"等等。

存储引擎

从 MySQL5.5之后，默认的存储引擎是 InnoDB。InnoDB 是一个通用的存储引擎，除非有特殊需求，推荐使用InnoDB.
SHOW ENGINES;

InnoDB体系架构

简要架构

InnoDB整体也分为三层:(多线程模型)

内存结构(In-Memory(1)Structure)，这一层在MySQL服务进程内;
OS Cache，这一层属于内核态内存;
磁盘结构(On-Disk)这一层在文件系统上;

多线程模型

Master Thread:刷新脏页到磁盘，保证数据一致性(10秒操作与1秒操作)
I0 Thread:大量使用异步处理写I0请求，包括4类I0 Thread
Purge Thread:回收已经使用并分配的undo页
Page Cleaner Thread:1.2.X版本以上引入脏页刷新，减轻master的工作，提高性能

管理单位

当我们想从表中获取某些记录时，1noDB存储引擎需要一条一条的把记录从磁盘上读出来么?

InnoDB采取的方式是:

将数据划分为若干个页，以可作为磁盘和内存之间交互的基本单位
InnoDB中页的大小一般为16KB，也就是在一般情况下，一次最少从磁盘中读取16 KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中

InnoDB内存架构

InnoDB在内存中主要包括下面几个部分:缓冲池、Change缓冲区、自适应哈希索引、Log缓冲区

缓冲池

Page:为了高效读取，缓冲池划分为页结构组织
LRU:当需求添加新的page时，最近最少使用的page被清除，同时新页面被添加到链表的中间部分
这种中间点插入的策略，把链表分为两个子链表
头部:最近被访问过的“年轻”页
尾部:最近被访问的old page
这样使新子列表中保存更重要的page，旧子列表包含较少使用的这部分page

change buffer

Change Buffer是一种特殊的数据结构，当某些页面不在缓冲池中，缓存会改变二级索引page，这可能会造成insert，update，delete(DML)操作会与其他从缓冲池中的读操作加载的page合并。
为了避免频繁的I0随机读写，当更新和删除操作时，并不会立即写入磁盘，而是会选择系统空闲时定期进行写入磁盘的操作。

落盘机制(redo、change buffer):

定时 master thread 1s or 10s
业务使用(提交或查询)
缓存区达到阀值(1/2)

日志缓冲区

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域，即redo log buffer。
Write Ahead Log策略:当事务提交时，先写重做日志，然后再择时将脏页写入磁盘【随机写变为顺序写】
日志缓存区大小:由innodb log_buffer_size 变量定义，默认16M, 可以节省磁盘I/0
日志刷新频率:innodb flushlog_at_trx_commit控制如何写入日志缓冲区的内容并刷新到磁盘

行-页-段的过程

分析成本的考量

I/O成本:从磁盘到内存这个加载的过程损耗的时间称之为1/0成本
CPU成本:读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本
页是磁盘和内存之间交互的基本单位，读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2

步骤:

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

查询:

explain过程进行解释

2.MySQL事务隔离

保证一组数据库操作，要么全部成功，要么全部失败的
事务支持是在引擎层实现
MySQL是一个支持多引的系统，但并不是所有的弓擎都支持事务
支持事务是InnoDB引擎的重要特性之一

隔离性

ACID的I:隔离性
(1)脏读(dirtyread):读取创了未提交的脏数据
(2)不可重复读(non-repeatable read):两次读取之间的数据被改变或消失
(3)幻读(phantom read):A事务在更新事务读取的同时B事务更新数据,就会惊奇的发现了这些新数据，貌似之前A读到的数据

隔离级别

(1)读未提交:一个事务还没提交时，它做的变更就能被别的其他他事务看到。
(2)读已提交:一个事务提交之后，它做的变更才会被其
(3)可重复读:一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务是不可见的。
(4)串行化:对于同一行记录，读或写都会加锁。当出现锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

读未提交: 2 2 2
读已提交:1 2 2
可重复读:1 1 2(事务A执行期间的数据是一样的)
串行化:1 1 2(事务B执行之后会更新锁住)

实现过程

读未提交:直接返回记录上的最新值，没有视图概念。
读已提交:在每个SOL语句开始执行的时候创建视图。
可重复读:在事务启动时创建视图，整个事务存在期间都用这个视图。
串行化:直接用加锁的方式来避免并行访问。

默认隔离级别参数

3.深入浅出索引

索引是什么东东?

索引是存储引擎用于快速查找记录的一种数据结构

七大查找算法

顺序查找
二分查找
插值查找
斐波那契查找
树表查找
分块查找
哈希查找

索引数据结构

哈希表

适用于等值查询

有序数组

插入和修改成本高

B+树

为什么MySQL用B+树做索引而不用B-树或红黑树

原因:面试的时候背过

表中 R1~R5 的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)

基于主键索引和普通索引的查询有什么区别?

主键索引:则只需要接叟索 ID 这棵 B+ 树
普通索引:要先搜索k索引树，得到ID内值为 500，再普通索引查询方式，则需要到 ID 索引树搜索一次。这个过程称为回表.

MyISAM索引实现

叶节点的data域存放的是数据记录的地址
主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的

InnoDB索引实现

InnoDB是进行行锁的,所以MyISAM不如使用InnoDB

--------------------------------------------------------------------------------------------------------------------------------

2024.10.07

4.普通索引和唯一索引的选择

InnoDB体系结构

select id fromT where k=5

条件的第一个记录(5,500)后，需要查找对于普通索引来说，查找到满下一个记录，直到碰到第一个满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

更新过程

当需要更新一个数据页时，如果类据页在内存中就直接更新，
而如果这个数据页不在内存中的讨舌，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个类据页的时候，将数据页读入内存，然后执行change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer的使用场景

对于唯一索引来说，所有的更新桑作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内字才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer 了。
因此，唯一索引的更新就不能使，月change buffer，实际上也只有普通索引可以使用。

InnoDB插入数据的流程

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下:

对于唯一索引，找到3和5之间的位置, 判断到没有冲突，插入这个值,语句执行结束;
对于普通索引,找到3和5之间的位置,插入这个值,语句执行结束.

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下:

对于唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值!
对于普通索引，则是将更新记录在 change buffer.

注意:在change buffer中有此行记录的情况下，再次更改，是增加一条还是原地修改?
增加一条

change buffer的使用场景

普通索引的所有场景，使用 change buffer 都可以起到加速作用吗 ?

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，changebuffer 记录的变更越多(也就是这个页面上要更新的次数越多)，收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反之，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发merge 过程。这样随机访问 10 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。