MySQL进阶 二

MySQL高级进阶

缓冲池 Buffer Pool

select * from user where id between 1 and 10;

这个全表查询需要从id为1一直到10 需要进行10次I/O，但是id从1到10却在一个页上，所以innodb设计了buffer pool
访问当前表空间的第一行数据，缓存当前页，一次I/O

Innodb引擎会在mysql启动的时候，向操作系统申请一块连续的空间当做buffer pool，空间的大小由变量innodb_buffer_pool_size确定

内部结构

缓冲页

buffer pool中存放的数据页我们称之为缓冲页，和磁盘上的数据页是一一对应的，都是16KB，缓冲页的数据，是从磁盘上加载到buffer pool当中的一个完整页

控制块

是缓冲页描述信息，这一块区域保存的是数据页所属的表空间号，数据页编号，数据页地址，以及一些链表相关的节点信息等，每个控制块大小是缓存页的5%左右，大约是800个字节

buffer pool的前一部分存储控制块，后一部分存储缓冲页，如果中间有未被利用的空间，叫内存碎片

buffer pool初始化

数据库会在启动的时候，按照配置中的Buffer Pool大小，去向操作系统申请一块内存，作为Buffer Pool的内存区域，然后会按照默认的缓存页的的大小16KB以及对应的800个字节左右的控制块的大小，在Buffer Pool中划分出一个一个的缓存页和一个一个与其对应的描述数据控制块。此时的buffer pool中没有任何内容

free链

程序在执行过程中会不断的有新的页被缓存起来，所以要判断哪些缓冲页是闲置状态，此时要控制块来进行标记和管理了。innodb设计初，将所有空闲的缓冲页所对应的控制块作为一个个的节点，形成一个链表，这个链表就是free链，就是空闲链表

free链表是一个双向链表，链表上除了控制块以外，还有一个基础节点，存储了free链有多少个描述信息块，也就是有多少个空闲的缓存页，以及指向链表头尾的指针,当我们加载数据的时候，会从free链中找到空闲的缓存页，把数据页的表空间号和数据页号写入控制块,加载数据到缓存页后，会把缓存页对应的控制块从free链表中移除

判断数据页是否被缓存

使用表空间号+页号可以确定一个唯一的页，所以我们可以设计一个hash表，使用表空间号+页号当做key，使用控制块地址做value，每次查询的时候只需要通过key进行查找即可，hash的时间复杂度是O(1)，这样就能迅速定位缓存的页

flush链

脏页

sql的执行过程中，无论是增删改查，都是优先在buffer pool中进行的，这样可以极大的保证执行效率。但是我们对缓存页的某些数据进行了修改，就会导致buffer pool中的缓冲页和磁盘的数据页数据不一致，那么此时的缓冲页就称之为脏页，所以脏页的数据是要刷到磁盘上的

其实就是在高峰期我们找一个临时的地方先存放，然后等到过了高峰期在写入到磁盘上

flush链表是一个双向链表，链表结点是被修改过的缓存页的控制块

刷盘时机

后台会有专门的线程每隔一段时间就把flush链表中的脏页刷入磁盘中，刷新的速率取决与当前系统是否繁忙。在如果系统奔溃，是会产生数据不一致的问题的，没有刷入磁盘的数据就会丢失，mysql通过日志系统解决这个问题

lru链

当需要更多的空间缓存新的数据页的时候，我们将最近使用最少的缓冲页淘汰掉，这是LRU算法，对于innodb而言，是通过LRU链表来完成的，结构和free链表、flush链表基本相同，只是负责的功能不同

当客户端访问一条数据时，会加载对应的数据页到buffer pool，并会将缓冲页对应的控制块放置到LRU链表的首位,一旦buffer pool被占满，则从链表的末端开始淘汰数据

对于预读的数据页，会在第一次访问时放入old区域，如果在sql执行的过程中访问相邻数据时，再次访问访问到该数据页，则把他加入如热数据区

Innodb提供了一个参数innodb_old_blocks_time，默认是1s，所以执行流程为

页被首次访问时会记录访问的时间戳
以后访问都和首次访问的时间进行对比，如果时间大于1s，就讲当前页放入yong区
一个sql的扫描一个页的时间，哪怕在慢也不会低于1s，所以解决了一个全表扫秒而导致全表成为热点数据的问题

热点数据要求首次访问时间和最后一次访问时间的时间差不能低于1s

查看innodb当前的状态

show engine innodb status

MySQL临时表

创建外部临时表

create temporary table 表名

查看执行计划

explain select * from 表名

内部临时表

内部临时表用来存储某些操作的中间结果，这些操作可能包括在优化阶段或者执行阶段，这种内部表对用户来说是不可见的。MySQL内部将使用自动生成的临时表，辅助SQL的执行。我们可以使用执行计划查看，如果一条sql语句的执行计划中列extra结果为Using temporary，那么也就说明这个查询要使用到临时表

常见创建内部临时表

• 使用GROUP BY分组，且分组字段没有索引时。

• 使用DISTINCT查询。

• 使用UNION进行结果合并，辅助去重，union all不会使用临时表，因为他不需要去重

• 复杂的sql中很容易产生临时表

内存临时表

内存临时表使用memery引擎（Memory引擎不支持BOLB和TEXT类型）

磁盘临时表

磁盘临时表默认使用innodb引擎

创建爱你磁盘临时表时机

• 数据表中包含BLOB/TEXT列

• 在 GROUP BY 或者 DSTINCT 的列中有超过 512字符的字符类型列

• 在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列

MySQL事务

在 MySQL 中只有使用了Innodb 数据库引擎的数据库或表才支持事务

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

事务的分类

显式事务

• 开启事务

begin    
或者
start transaction

• 提交事务

commit

• 事务回滚

rollback

隐式事务

默认的事务是隐式事务，由变量autocommit控制。隐式事务的环境下，我们每执行一条sql都会自动开启和关闭事务

只读事务

read only

保存点

start transaction;
UPDATE user set ...;
savepoint a;
UPDATE user set ....;
rollback to a;

上边update执行，下边updata回滚

Mysql不支持嵌套事务，开启一个事务的情况下，若再开启一个事务，会隐式的提交上一个事务

事务四大特征(ACID)

原子性(Atomicity)

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。如果事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样

一致性(Consistency)

在事务开始之前和结束以后，数据库的完整性没有被破坏，数据库状态应该与业务规则保持一致，其他的三个特性都在为他服务

隔离性(Isolation)

数据库允许多个并发事务同时对其数据进行读取和修改，隔离性可以防止多个事务在并发修改共享数据时产生数据不一致的现象

持久性(Durability)

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务隔离级别

-隔离级别的设置与查看

-- 查看全局和当前事务的隔离级别
SELECT @@global.transaction_isolation, @@transaction_isolation_isolation;
show variables like 'transaction_isolation';
--5.7   tx_isolation
--8.0   transaction_isolation

-- 设置下一个事务的隔离级别
SET transaction isolation level read uncommitted;
SET transaction isolation level read committed;
set transaction isolation level repeatable read;
SET transaction isolation level serializable;
-- 设置当前会话的隔离级别
SET session transaction isolation level read uncommitted;
SET session transaction isolation level read committed;
set session transaction isolation level repeatable read;
SET session transaction isolation level serializable;
-- 设置全局事务的隔离级别
SET GLOBAL transaction isolation level read uncommitted;
SET GLOBAL transaction isolation level read committed;
set GLOBAL transaction isolation level repeatable read;
SET GLOBAL transaction isolation level serializable;

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

读未提交(Read uncommitted)

一个事务可以读取其他未提交的事务修改的数据，隔离级别最低

发生的问题？

脏读，不可重复度，幻读

脏读：一个事务读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，读到的数据不一定准确

读已提交(Read committed)

当前事务只能读到别的事物已经提交的数据，一个事务a修改了另一个未提交事务b读取过的数据。那么事务b再次读取，会发现两次读取的数据不一致。也就是说在一个原子性的操作中一个事务两次读取相同的数据，却不一致，一行数据不能重复被读取。主要是update语句，会导致不可重复读

发生的问题？

不可重复读和幻读

可重复读(Repeatable read)

同一个事务中发出同一个SELECT语句两次或更多次，那么产生的结果数据集总是相同的，

发生的问题？

幻读：一个事务按照某些条件进行查询，事务提交前，有另一个事务插入了满足条件的其他数据，再次使用相同条件查询，却发现多了一些数据，就像出现了幻觉一样。幻读主要针对针对delete和insert语句

串行化(Serializable)

事务A和事务B，事务A在操作数据库时，事务B只能排队等待

很少使用，吞吐量太低，用户体验差

可以避免幻读，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

正确的开始 微小的长进 然后持续 嘿 我是小博 带你一起看我目之所及的世界……