MySQL-复习

时间:2021-03-06 钱和途人气:0

MySQL升级

升级的方式

Inplace：立即升级，在业务库直接从低版本升级到高版本，危险性极高
迁移升级：选择合适的时间点将业务库的数据备份，准备好目标库，将备份的数据页导入，进行业务割接
主从升级：主库正常提供服务，将从库升级到目标版本，停止主库进行日志追加，业务割接。

升级的注意事项

升级之前一定要做好备份
只支持在GA版本之间升级
不支持跨版本升级，例如：5.6升级到8.0？ 5.6升级到5.6版本的最新版--升级到5.7版本的最新版--升级到8.0版本
提前预估停机窗口

回退方案

GA版本支持降级
8.0版本不支持downgrade降级
5.7版本降级到5.7版本可以使用downgrade
5.7降级到5.6使用logical

Inplace升级的思路

备份业务库
安装高版本软件
关闭业务库，挂维护页（innodb_fast_shutdown=0;shutdown;）
使用高版本软件启动低版本数据（--skip-grant-tables，--skip-networing）
使用mysql_upgrade升级数据，8.0版本不需要
进行业务功能的测试
恢复业务

迁移升级

在升级之前找一个业务不繁忙期间对业务库进行一个全备
在准备好的服务器上提前初始化好一套目标版本的环境
将全备的数据导入目标服务器
追加逻辑日志binlog
当目标服务器的数据和原业务库的数据相差无几的时候，停止业务库（innodb_fast_shutdown;shutdown;）
再次进行日志追加，确保完完整整的将所有数据同步到目标服务库
进行功能性的测试
测试通过以后，业务割接
恢复业务

主从升级

升级从库

5.7.30降级到5.7.10（downgrade）

安装低版本软件
修改高版本表结构，以及与低版本不兼容的功能特性
关闭业务库，挂维护页（innodb_fast_shutdown;shutdown;）
使用低版本软件启动高版本数据（--skip-grant-tables，--skip-networing）
执行mysql_upgrade降级数据
对业务功能进行测试
恢复业务

5.7.10降级到5.6.46（logical）

安装低版本软件
修改业务库表结构
关闭业务库，挂维护页面（innodb_fast_shutdown;shutdown）
逻辑导出所有数据
初始化一套低版本数据库
导入数据
对业务功能进行测试
恢复业务

请你介绍sql_mod的作用？only_full_group_by是用来做什么的？

从5.6版本以后进入了严格模式

作用：

sql_mod是用来约束语句的执行行为，保证能够满足我们生活当中的常识与科学逻辑，比如mysql当中有一个时间相关的sql_mod，不允许时间为0，这个在我们实际生活当中也没有0年0月0时0分的这种情况，再比如mysql当中还有一个计算相关的sql_mod，不允许除数为0，这个同样符合我们的认知，再比如mysql当中有一个sql_mod，only_full_group_by，它是用来保证在数据库中查询出来的数据都是有意义的，不可以出现一行对多行的现象，它需要满足在select列表中的列要么在group by后边，要么在聚合函数中，满足其一即可。如果其中一个列在group by后出现了，但是其他的列没有在聚合函数中出现，那么此时，除了表中没有重复值这种情况，就会出现一行对多行的现象，出现这种情况在mysql5.6版本及之前默认是取第一个重复的值，这样显然也没有意义，在mysql5.7及以后有了only_full_group_by的限制直接报错。

做group by分组的是主键或唯一键可以忽略only_full_group_by.

only_full_group_by的规则

 凡是在group by后面出现的字段，必须同时在select后面出现；在select后边出现的其他字段，如果没有在group by中出现必须在聚合函数中，否则就会违反sql_mod。

字符集&&校对规则

字符集

设定字符存储的格式

写入的数据时，编译一下，转换成二进制或者十六进制，计算机能够认识的方式存储；

读取数据时再编译一下，转换成我们能够看懂的形式，显示给我们，这个编码的格式就是字符集。

这个utf8不是我们广义认为的UTF-8（Unicode Transformation Format）万国码，mysql当中的utf8是一个不完整的utf8，在生产当中难免会出现转义不了的字符，utf8mb4更接近于“万国码”可以转义更多类型的字符，最典型的就是emoji字符了，还有好多，只要是4字节的utf8都不能够存储。

utf8：最大存储3字节的字符

utf8mb4：最大存储4字节的字符，支持emoji字符，8.0版本默认就是utf8mb4

校对规则

影响排列顺序

MySQL存储字符是区分大小写的，底层使用ASCII码排序

可以通过设定校对规则，来规定mysql在存储字符时是否区分大小写

数据类型

数字类型

	占用字节	字符长度
tinyint	1	3
int	4	10
bigint	8	20

字符串类型

char：定长，存储长度0~255

varchar：变长，0~65535，（额外预留1~2个字符存储字符长度）

开发规范

建库规范

库名不能以数字开头，不能使用大写字母
库名要与业务相关
库名不能创建为数据库内置字符
创库显示设置字符集

建表规范

表名不能以数字开头、不能使用大写字母、不可以出现数据库内置字符，16个字符以内
创建的列要有意义，不可以出现预留列
每个表必须要有主键
建议每个列设置为not null
建议每个列要有注释
显示设置字符集和存储引擎
数据类型的选择遵循合适的、简短的、足够的三大原则

什么是Online DDL（Algorithm）？

业务在线期间做的操作，通常就是Alter这种语句居多，平常做得最多的也就是

1.添加列、删除列

2.创建索引、删除索引

3.数据类型、属性的更改

在5.6及以后版本中都是系统自动选择算法（Algorithm），不需要我们人为干预

copy

修改表结构的时候生成一个临时表，逐行拷贝原表的数据到临时表中，在拷贝的过程当中原表会产生DML锁，拷贝完成rename临时表。

Inplace

无需拷贝全表数据，但还是需要重构整表，Inplace分为三个阶段，初始化阶段、执行阶段、结束阶段，在初始化阶段和结束阶段需要进行加锁，除此之外DDL期间不会阻塞其他DDL操作，在执行阶段所做的修改操作都会记录到row log中，在结束阶段将记录的日志应用到原表当中。

Instant

对于追加式添加列、修改表名、添加虚拟列，只需要修改数据字典中的元数据，无需拷贝全表数据，无需重构整表，不会产生DML锁，原表数据也不会受到影响，也不会阻塞DML语句，整个过程几乎是瞬间完成的，此功能特性是在8.0.12版本以后引入的。

pt-osc&&gh-ost工具的工作原理

使用这款工具降低了对于线上业务的影响，但是不能加快语句执行的速度。

查看是否有外键
查看是否有从节点
创建临时表
修改表结构
创建触发器 insert、update、delete
拷贝原表数据
rename临时表
删除原表、删除触发器

研发同学需要紧急上线，需要DBA审核SQL，写明审核SQL的要点

1.分析语句的作用和影响

2.评估表的数据量

3.只要涉及表变更类的操作，都要放到业务不繁忙期间去做，对业务的影响降低到最小

drop、truncate、delete删除数据的区别？

drop：

属于DDL操作，会删除表中所有数据以及表结构，属于物理性质的删除，会立即释放磁盘空间，删除操作不可以回滚

truncate：

属于DDL操作，会删除表中所有数据，但是不会删除表结构，属于物理性质的删除，会立即释放磁盘空间，删除操作不可以回滚

delete：

属于DML操作，不会真正的删除数据，只是将数据打上一个删除标记，并标记为可覆盖状态，属于逻辑性质的删除，在磁盘当中不会真正的删除，所以不会立即释放磁盘空间，会产生碎片，不会降低高水位线，查询数据时被delete掉的数据仍然需要被扫描，插入数据除非是手动指定覆盖被delete掉的数据行，否则就是追加式插入。

介绍一下段、区、页，以及它的设计理念？

段、区、页的介绍：

段：segments，一个表就是一个段，一个段由一个或N个区构成

区：extents，也被称之为“簇”，一个区是连续的64个page，默认1M，存储数据时最小的分配单元

页：page，数据库中最小的IO单元，是连续的4个 OS block，默认16KB，

block和page（初始化之前在配置文件中设定innodb_page_size）的大小都是可以手动设定的，page最大可以设置到64KB。

设计理念：

MySQL这样设计的最终目的就是为了能够快速的从磁盘当中读取数据，在磁盘当中最小的一块连续的存储区域是“扇区”一个“扇区”是512字节，在操作系统层最小的存储单元是block，也就是连续的八个扇区，在数据库层最小的存储单元是page，也就是连续的4个block，这样就保证了数据库中的数据在磁盘存储时都是连续的，为什么一定要保证连续？因为数据在磁盘存储时都是一个一个的金属小颗粒附着在磁道上，然后由机械臂控制磁头读取磁道上的”小颗粒“，如果这些“小颗粒”在不同的磁道上磁头就需要来回摆动，这个过程是非常消耗时间的，所以他最终需要保证数据在磁盘存储时是连续的。这个就是它设计的理念。

MySQL中一条SQL语句的执行过程

查询语句的执行顺序：

１.客户端通过TCP连接发送连接请求到mysql连接器，连接器会加载授权表（db,user,tables_priv,columns_priv）对该请求进行权限验证及连接资源分配（提供连接线程）

２.建立连接后客户端发送一条语句，mysql收到该语句后，如果是在开启查询缓存的情况下，先在查询缓存中查找该SQL是否完全匹配，如果完全匹配，验证当前用户是否具备执行该语句的权限，如果权限验证通过，直接返回结果集给客户端，该查询也就完成了。如果不匹配继续向下执行，因为连接线程本身没有办法处理SQL语句，所以将语句传递给SQL层

3.SQL层将语句交给分析器做语法、语义的分析，如果语法（syntax,sql_mod）不对，直接报错，如果检测语法通过则进行语义的分析，判断一下客户端发起的执行语句属于哪种类型的语句是DML、DDL还是DCL，通过判断发现是查询类的语句select。

4.将语句传递解析器，根据统计信息解析预处理并生成解析树，检查数据表和数据列是否存在等

5.语句解析完成后，会将语句传递给优化器进行优化，选择执行代价最低的执行方案，选择合适的索引，并生成执行计划。

6.之后交给执行器去具体执行优化器生成的执行计划，在执行之前，会先检查该用户是否具有查询权限，如果有，继续执行该语句。

7.将语句传递给存储引擎，存储引擎与磁盘交互，从而获得数据，执行器开始执行后，会逐渐将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，当然了在8.0版本以后查询缓功能直接被取消了，就不存在查询缓存和写入缓存的操作了。

7.最后存储引擎层将结果集返回给SQL层，将数据结构化成表也就是我们能够看懂的语言，再返回给连接线程，最后通过连接线程返回给用户

更新语句的执行顺序：

１.客户端通过TCP连接发送连接请求到mysql连接器，连接器接收到连接请求以后，会加载授权表对该请求进行权限验证（user,db,tables_priv,colnums_priv）及连接资源分配（提供连接线程）

2.建立连接后客户端发送一条语句，mysql收到该语句后，因为连接线程本身并不能够处理SQL语句，所以将语句传递给SQL层。

3.SQL层首先通过分析器会检查执行语句的语法、语义，如果，执行语法（语法本身，sql_mod）有误，就会返回语法错误中断操作；如果执行语法正确，则判断客户端执行的语义，也就是客户端发起执行语句的类型，看看究竟是DML、DDL还是DCL类型的语句，通过判断发现是更新类型的语句update。

4.将语句传递给解析器，根据统计信息，解析预处理并生成解析树，检查数据表和数据列是否存在等

5.语句解析完成后，MySQL就知道更新哪些内容了，然后将语句传递给优化器进行优化，选择执行代价最低的执行方案，选择合适的索引，并生成执行计划。

6.执行器根据优化器生成的执行计划去打开一张表，此时会先去查看该表上是否有元数据（MDL）排他锁（如果有元数据共享锁则无影响），如果有元数据排他锁，则事物被阻塞，进入等待状态（时间由lock_wait_timeout决定，默认是一年），等元数据锁被释放后，继续执行。如果没有元数据排他锁，则该事务需要在表上加元数据共享锁（因为元数据共享读锁之间是不冲突的，这样做的目的就为了防止在此期间有DDL语句的操作，因为DDL语句在执行期间需要加元数据排他锁，如果有DDL语句的操作就会进入等待状态）

7.之后进入引擎层，默认innodb存储引擎，首先去innodb_buffer_pool里面的data dictionary得到表中数据页中的数据行。

8.根据获取到的信息去innodb_buffer_pool里面的lock info查看是否有相关的锁信息，如果有则等待（因为要加排它锁，防止多个事务同时更新同一行数据），如果没有则加上排它锁，更新lock info。

9.加完锁之后取读相关数据页到innodb_buffer_pool中（如果数据页本身就在缓存中，则不用从硬盘读取）

10.在修改数据页之前，将undo提取到buffer poll中，对undo进行修改，将原始数据（快照）保存到undo中，在此过程当中需要将变化的日志记录到redo log buffer中，然后将undo刷盘操写入到undo tablespace

11.之后在innodb_buffer_pool中将相关页面更新，该数据页变为脏页，脏页会以相关参数定义的规则进行刷盘

12.页面修改完成后，会把修改后的物理页面保存到redo log buffer中，redo log buffer的刷盘策略通过innodb_flush_log_at_trx_commit参数定义的规则进行刷盘操作写入到ib_logfile中

13.如果开启binlog，则更新数据的逻辑语句也会记录在binlog_cache中，binlog的刷盘策略通过sync_binlog参数定义的规则进行刷盘操作写入到binlog file 中

14.如果该表上有二级索引并且本次操作会影响到二级索引，则会把相关的二级索引修改写入到innodb_buffer_pool中的change buffer里（change buffer 会以相关参数定义的规则进行刷盘操作，如果在此期间有其他SQL对修改的语句做操作则会在Change Buffer中进行一个合并操作）

15.之后就是事务的commit或者rollback操作。

16.如果执行的是commit操作，由于要保证redolog与binlog的一致性，redolog采用2阶段提交方式。

17.首先将redo log buffer刷盘（innodb_flush_log_at_trx_commit=1），并将该事务的redolog标记为prepare状态。

18.之后再将binlog_cache刷盘（sync_binlog=1）

19.如果开启了主从结构，此时会将binlog_cache中的信息通过io线程发送给从库，如果开启了半同步复制则需要等待从库落盘（relay log）并反馈。如果是异步复制则无需等待(默认是异步复制)

20.等待binlog落盘完成以后，再将redolog中该事务信息标记为commit，释放相关锁资源。此时一个更新事务的操作流程就已经完成了，然后存储引擎层将更新成功的信息返回给SQL层，SQL层将信息返回给连接线程，最后通过连接线程返回给客户端

22.如果此时触发了脏页刷盘操作，会先将脏页写入到double write buffer中，为了防止写入过程中出现数据页写入不完整的情况，所以先将脏页写入Double Write Buffer的内存中，在将Double Write Buffer内存当中的数据写入Double Write Buffer磁盘，然后才是将脏页刷新到操作系统的磁盘中。

———————————————— 原文链接：https://blog.csdn.net/finalkof1983/article/details/84450896

Crash Recovery 过程

情景一：实现前滚

数据库在重新启动后，会拿着ibd中的LSN与ib_logfile中的LSN对比，发现ib_logfile中的LSN大于ibd中的LSN，此时就会触发Crash Recovery
首先将ibd中的数据加载到InnoDB Buffer Pool中，再将ib_logfile中的redo日志和undo日志提取到redo log buffer中
根据redo log buffer中的undo日志更新InnoDB Buffer pool中数据页的回滚指针和事务ID
通过检查redo log故障前记录的状态信息，发现是commit状态
此时就会应用redo log实现前滚的功能，使数据库快速恢复到故障之前的状态

情景二：实现回滚

数据库在重新启动后，会拿着ibd中的LSN与ib_logfile中的LSN对比，发现ib_logfile中的LSN大于ibd中的LSN，此时就会触发Crash Recovery
首先将ibd中的数据加载InnoDB Buffer Pool中，再将ib_logfile中的redo日志和undo日志提取到redo log buffer中
根据redo log buffer中的undo日志更新InnoDB Buufer Pool中数据页的回滚指针和事务ID
通过检查redo log 故障之前的状态信息，发现是prepare状态
直接通过DB_ROLL_PTR和DB_TRX_ID找到undo tablespace中的回滚日志，实现回滚功能

简述事务的ACID？

A：原子性

也被称之为不可再分性，一个事务的执行语句，在执行过程当中要么全部执行成功，要么全部执行失败，不可以出现中间状态，通过undo保证了事务的原子性。

C：一致性

一个事务在执行前、中、后都能够保证事务的一致性。通过CR机制和DWB保证了事务的一致性。

I：隔离性

一个事务在执行期间是否可以被另一个事务读取到，亦或者一个事务在更新数据行的过程当中不会受到另一个事务的影响。

读隔离：通过隔离级别和MVCC保证

写隔离：通过隔离级别和锁保证

D：持久性

事务一旦提交在内存当中做的所有操作都会被保存下来，这个过程也被称之为刷盘，通过redo保证事务的持久性。

简述事务的隔离级别？

RU：读未提交

在这个隔离级别当中，一个事务可以读取到另一个事务修改了但是还没有提交的数据。在这个隔离别当中可能会存在脏读、不可重复读、幻读的现象。

RC：读已提交

在这个隔离级别中，一个事务可以读取到另一个是修改了并且已经提交完成的数据，在一个事务当中每次获取到的都是最新的数据，在这个隔离级别当中可能存在不可重复读和幻读的现象。

RU：可充读取

在这个隔离级别中，事务每次获取到的数据都是一致的，在第一次获取数据之前会生成一个一致性快照，一直伴随事务结束。

RR隔离级别下加锁原则？

原则一：在RR隔离级别下基本的加锁单位是next-key lock，以左开右闭的方式加锁（5,10]。

原则二：在查询过程当中被访问到的索引才会被加锁。

原则三：索引上的等值查询，给唯一索引加锁时，next-key lock退化为行锁。

原则四：索引上的等值查询，从右边开始遍历到最后一个不满足等值查询条件，next-key lock退化为间隙锁

8019之前，唯一索引范围查询，会访问到第一个不满足查询范围的值为止。

案例一：

 CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `c` (`c`)
 ) ENGINE=InnoDB;
 
 insert into t values
 (0,0,0),
 (5,5,5),
 (10,10,10),
 (15,15,15),
 (20,20,20),
 (25,25,25);
 
 T1：update t set d=d+1 where id=7;
 T2：insert into t values(9,9,9);  --阻塞
 T3：update t set d=d+1 where id=10; --正常
 
 分析T1的加锁范围：
 1. 应用原则一，使用next-key lock
 2. 应用原则四，next-key lock退化成了间隙锁 （5,10] 
 3. 加锁范围 6，7，8，9

案例二：

 T1：select id from t where c=5 lock in shard mode
 T2：update t set d=d+1 where id=5; --正常
 T3：insert into t values(7,7,7);   --阻塞
 
 分析T1的加锁范围：
 1. 应用原则一，使用next-key lock
 2. 应用原则二，查询过程当中使用到的索引才进行加锁
 3. 应用原则四，向右边遍历时，遇到最后一个不满足等值查询的条件，next-key lock退化为间隙锁
 4. 加锁范围 （5,10） 6，7，8，9。

DML语句的执行流程

Insert语句：

将插入记录的主键值记录到Undo
将Undo信息记录到Redo当中
将Undo落盘
首先对聚簇索引进行插入，插入过程当中变化的日志记录到Redo当中
其次对辅助索引进行插入，插入过程当中变化的日志记录到Redo当中
将Redo落盘
将脏页落盘

Delete语句：

delete语句在磁盘上不是真正的删除数据，在InnoDB内部为Delete Mark操作，在记录上标识为Delete_Bit。

将当前记录的系统列写入Undo，所谓的系统列就是DB_TRX_ID、DB_ROLL_PTR
将当前记录的主键列写入Undo
将当前记录的所有索引列写入Undo
对Undo数据页的修改记录到Redo当中，将Undo落盘
首先删除聚簇索引列，将变化的日志记录到Redo当中
其次删除辅助索引列，将变化的日志记录到Redo当中
将Redo落盘
将脏页落盘

以上记录写入Undo的目的，就为了在回滚的时候快速找到删除之前的数据

Update语句

情况一：未修改聚簇索引键值，修改的属性列长度不变

将当前记录的系统列写入Undo
将当前记录的主键列写入Undo
将当前updte列的前镜像写入Undo
修改列如果是联合索引列，需要将联合索引中的其他索引列记录到Undo
将对Undo数据页的修改记录到Redo当中
将Undo落盘
对于聚簇索引来说，采用Inplace Update的方式更新，并将变化的日志记录到Redo当中
对于辅助索引来说，将之前的数据打上DeleteMark，在插入新的数据，并将变化的日志记录到Redo当中
将Redo落盘
将脏页落盘

情况二：未修改聚簇索引键值，修改列的属性长度有所变化

将当前记录的系统列写入Undo
将当前记录的主键列写入Undo
将当前update列的前镜像写入Undo
当前修改列如果是联合索引，需要将联合索引中，其他索引列记录到Undo
对Undo数据页的修改记录到Redo
将Undo落盘
对于聚簇索引来说，需要先删除之前的旧数据，在插入新数据。将变化的日志记录到Redo
对于辅助索引来说，需要将之前的旧数据打上Delete Mark，在插入新的数据，覆盖旧数据。将变化的日志记录到Redo
将Redo落盘
将脏页落盘

情景三：修改聚簇索引键值

在修改之前，将Delete Mark 操作记录到Undo
对聚簇索引执行Delete Mark操作，将变化的日志记录到Redo
将插入的信息记录到Undo
对聚簇索引插入新数据，将变化的日志记录到Redo
对辅助索引执行Delete Mark操作，将变化的日志记录到Redo
对辅助索引执行Insert操作，将变化的日志记录到Redo
将redo落盘
将脏页落盘

MVCC多版本并发控制

MVCC采用乐观锁机制，实现非锁定读取。

MVCC也会受到不同隔离级别的影响；

RC隔离级别下，一个事务可以立即读取到另一个事务commit过的Read View。

RR隔离级别下，一个事务从第一次查询开始，获取一个一致性的ReadView直到事务结束。

备份工具

mysqldump：

在恢复数据的过程当中，会判断即将恢复的库或表是否已经存在，如果已经存在，则会删除原库或原表，然后再创建新的库和表，基于这种恢复模式，不适合再业务库直接恢复数据，因为我们最终要保证的是将业务的宕机窗口降到最低，所以一般情况下在恢复数据的时候我们都会将全备的数据恢复到一个测试库，从测试库当中再导出所需的数据恢复到业务库。

mydumper：

可以实现多线程备份，提高备份效率

视图

加载全部内容