前言

2023-7-25 15:01:04

以下内容源自《【面试复习】》
仅供学习交流使用

版权

禁止其他平台发布时删除以下此话
本文首次发布于CSDN平台
作者是CSDN@日星月云
博客主页是https://blog.csdn.net/qq_51625007
禁止其他平台发布时删除以上此话

推荐

无

温故而知新-Mysql篇

Mysql常见面试题

MySQL面试50题【mysql】

导航【mysql高级】【java提高】

mysql复习【面试】

mysql笔记【面试】

MySQL笔记【面试】

Mysql事务

事务：就是一组操作要么同时成功，要么同时失败

ACID四个特性

原子性：一组操作同时成功，同时失败
保证：redo日志

一致性：从一个一致性状态到达下一个一致性状态
保证：
约束一致性：创建表结构时所指定的外键、唯一索引等约束。
数据一致性：其他三个保证，一般都是程序员进行处理的

隔离性：两个事务不会互相影响
保证：MVCC和锁
脏写 脏读 可重复读 幻读

持久性：持久化到磁盘上
保证：undo日志

Mysql索引

物理实现：B+树和Hash
类型：聚簇索引和非聚簇索引

最左前缀法则

底层的B+树结构

当一个列的左列的值全部相等时，该列才有序

Mysql锁

第15章 锁：3. 锁的不同角度分类：3.2 从数据操作的粒度划分:表级锁、页级锁、行锁

表锁：

① 表级别的S锁、X锁InnoDB不会对其主动加该锁 LOCK TABLES t READ | LOCK TABLES t WRITE
② 意向锁 （intention lock）多粒度:加行锁之前先加意向锁，标志位的思想
③ 自增锁（AUTO-INC锁）插入自增主键的时候，不需赋值
④ 元数据锁（MDL锁）当对一个表做增删改查操作的时候，加MDL读锁;当要对表做结构变更操作的时候，加MDL写锁。

行锁：

① 记录锁（Record Locks）记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。
② 间隙锁（Gap Locks）gap锁的提出仅仅是为了防止插入幻影记录而提出的
③ 临键锁（Next-Key Locks）记录锁+间隙锁Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。
④ 插入意向锁（Insert Intention Locks）插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

死锁

元数据锁导致死锁
间隙锁导致死锁
互相申请对方所占有的资源导致死锁

where列没加索引，导致全表扫描，进而锁住全表

①超时等待，但是阈值时间不好确定
②死锁检测死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁

Mysql日志

redo log：重做日志

undo log：回滚日志

binlog：二进制日志

relay log：中继日志

Mysql中的Buffer

总结 mysql 的所有 buffer，一网打尽就这篇了！

Buffer Pool

因为不想每次修改，就得把数据页持久到磁盘中

所以设置Buffer Pool

只要适用于普通非唯一索引，因为没有唯一性约束

所以对其的修改，不需要判断

先修改到Buffer Pool中，直到它调入内存中，再进行合并

change buffer

log buffer

doublewrite buffer

数据库的三范式是什么

/* 建表规范 */ ------------------
-- Normal Format, NF
- 每个表保存一个实体信息
- 每个具有一个ID字段作为主键
- ID主键 + 原子表
-- 1NF, 第一范式
字段不能再分，就满足第一范式。
-- 2NF, 第二范式
满足第一范式的前提下，不能出现部分依赖。
消除复合主键就可以避免部分依赖。增加单列关键字。
只能完全依赖主键，而不能依赖主键的一部分
-- 3NF, 第三范式
满足第二范式的前提下，不能出现传递依赖。
某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。
将一个实体信息的数据放在一个表内实现。
只能直接依赖于主键，不能依赖于其他属性

反范式化

MySQL对于LRU的优化

普通LRU算法

LRU = Least Recently Used（最近最少使用）: 就是末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰.

1. 当要访问某个页时，如果不在Buffer Pool，需要把该页加载到缓冲池,并且把该缓冲页对应的控制块作为节点添加到LRU链表的头部。
2. 当要访问某个页时，如果在Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部
3. 当需要释放空间时,从最末尾淘汰

普通LRU链表的优缺点

优点

• 所有最近使用的数据都在链表表头，最近未使用的数据都在链表表尾,保证热数据能最快被获取到。

缺点

• 如果发生全表扫描（比如：没有建立合适的索引 or 查询时使用select * 等），则有很大可能将真正的热数据淘汰掉.
• 由于MySQL中存在预读机制，很多预读的页都会被放到LRU链表的表头。如果这些预读的页都没有用到的话，这样，会导致很多尾部的缓冲页很快就会被淘汰。

改进型LRU算法

改进型LRU：将链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入(就是说从磁盘中新读出的数据会放在冷数据区的头部)，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

冷数据区的数据页什么时候会被转到到热数据区呢 ?

1. 如果该数据页在LRU链表中存在时间超过1s，就将其移动到链表头部 ( 链表指的是整个LRU链表)
2. 如果该数据页在LRU链表中存在的时间短于1s，其位置不变(由于全表扫描有一个特点，就是它对某个页的频繁访问总耗时会很短)
3. 1s这个时间是由参数 innodb_old_blocks_time 控制的

InnoDB三大特性

【MySQL】存储引擎（二）：InnoDB 内存结构

自适应哈希索引（Adaptive Hash Index）

当一个数据频繁的搜索时，将为其创建hash

那什么情况下才会使用自适应Hash索引呢？如果某个数据经常会访问到，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。
需要说明的是：
1）自适应哈希索引只保存热数据（经常被使用到的数据），并非全表数据。因此数据量并不会很大，可以让自适应Hash放到缓冲池中，也就是InnoDB buffer pool，进一步提升查找效率。
2）InnoDB中的自适应Hash相当于是“索引的索引”，采用Hash索引存储的是B+树索引中的页面的地址。这也就是为什么可以称自适应Hash为索引的索引。 采用自适应Hash索引目的是可以根据SQL的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应Hash索引可以提高数据的检索效率。
3）自适应Hash采用Hash函数映射到一个哈希表中，所以对于字典类型的数据查找非常方便 哈希表是数组+链表的形式。通过Hash函数可以计算索引键值所对应的bucket（桶）的位置，如果产生Hash冲突，如果产生哈希冲突，就需要遍历链表来解决。
4）是否开启了自适应Hash，可以通过innodb_adaptive_hash_index变量来查看，比如：mysql> show variables like '%adaptive_hash_index'; 所以，总结下InnoDB本身不支持Hash，但是提供自适应Hash索引，不需要用户来操作，而是存储引擎自动完成的。

插入缓存（insert buffer） 或写缓存（Change Buffer）

因为要减少磁盘IO，所以加入Buffer Pool，
InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫Buffer Pool。
缓冲的加入一般都是内存和磁盘的处理速度不匹配

思考一个问题：当需要更新一个数据页时，如果数据页在Buffe rPool中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘IO，有没有优化的方式呢？向下看…

对于非唯一索引，因为其不要把索引页调入内存，进行唯一性检查
所以可以先写到insert buffer中，
当其数据页调入到内存中，再进行合并数据，merge操作
Change Buffer 默认占 Buffer Pool的25%

两次写（double write）

Mysql数据页大小是16K，操作系统的页大小为4K（1K）
要刷脏页的时候，
先写到共享表空间，顺序的
再写到各自的独立表空间，分散的

Mysql8.0索引新特性

第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】：2. Mysql8.0索引新特性

支持降序索引

CREATE TABLE ts1(a int,b int,index idx_a_b(a asc,b desc));

支持隐藏索引

为了避免删除索引之后，又想加回来

所有有了隐藏索引

先隐藏索引，之后看起影响，考虑是否删除索引

1.创建表时直接创建

CREATE TABLE tablename(propname1 type1[CONSTRAINT1],propname2 type2[CONSTRAINT2],……propnamen typen,INDEX [indexname](propname1 [(length)]) INVISIBLE
);

2.在已有表上创建

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

3.修改ALTER TABLE语句创建

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

4.切换可视状态：

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

5.使隐藏索引对查询优化器可见

set session optimizer_switch="use_invisible_indexes=on";
set session optimizer_switch="use_invisible_indexes=off";

数据库服务器优化步骤

第09章 性能分析工具的使用【2.索引及调优篇】【MySQL高级】

SQL调优的三个步骤:慢查询、EXPLAIN和SHOW PROFILING

1.优化步骤

2.查询系统参数

3.查询执行成本 last_query_cost，单位是需要查询的页的数量

4.定位执行慢的 SQL：慢查询日志 long_query_time

4.1开启慢查询日志参数
1.开启slow_query_log
set global slow_query_log='ON';
再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
show variables like '%slow_query_log_file';
如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log2.修改long_query_time阈值
set global long_query_time = 1;4.2 查看慢查询数目
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
有多少条补充：是否慢查询sql还有个参数
min_examined_row_limit（查询扫描过的最少记录数），默认是04.5 慢查询日志分析工具：mysqldumpslow
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more4.6 关闭慢查询日志
4.7 删除慢查询日志

5.查看 SQL 执行成本：SHOW PROFILE

6.分析查询语句：EXPLAIN

8. 分析优化器执行计划：trace

select * from information_schema.optimizer_trace\G

9.MySQL监控分析视图-sys schema

索引优化

防止索引失效

全值匹配

最左前缀法则

索引覆盖

索引条件下推

锁

间隙锁加锁规则（共11个案例）

间隙锁加锁规则（共11个案例）
间隙锁是在可重复读隔离级别下才会生效的： next-key lock 实际上是由间隙锁加行锁实现的，如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。而在读提交隔离级别下间隙锁就没有了，为了解决可能出现的数据和日志不一致问题，需要把binlog 格式设置为 row 。也就是说，许多公司的配置为：读提交隔离级别加 binlog_format=row。业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

next-key lock的加锁规则

总结的加锁规则里面，包含了两个 “ “ 原则 ” ” 、两个 “ “ 优化 ” ” 和一个 “bug” 。

• 原则 1 ：加锁的基本单位是 next-key lock 。 next-key lock 是前开后闭区间。
• 原则 2 ：查找过程中访问到的对象才会加锁。任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁。
• 优化 1 ：索引上的等值查询，给唯一索引加锁的时候， next-key lock 退化为行锁。也就是说如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁
• 优化 2 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的时候， next-keylock 退化为间隙锁。
• 一个 bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

再谈二进制日志(binlog)

第17章 其他数据库日志：6. 再谈二进制日志(binlog)

两阶段提交

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

还有缓存与数据库的一致性

mq订阅binlog

主从复制

2.1 原理剖析
三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程 来操作，一个主库线程，两个从库线程。

二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。–>二进制日志转储线程负责将数据发送出去。

从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

Mysql8的新特性

第01章 Linux下MySQL的安装与使用：3.登录：3.3 设置远程登录：4. Linux下修改配置

对密码进行加密
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

第05章 存储引擎：4. 引擎介绍：4.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

默认的InnoDB存储引擎：支持事务，行锁，外键

第08章 索引的创建与设计原则：2. Mysql8.0索引新特性

降序索引和隐藏索引

第10章 索引优化与查询优化：10. 索引下推：10.1 使用前后的对比

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，
是一种在存储引擎层使用索引过滤数据的一种优化方式。

第12章 数据库其它调优策略：5. 其它调优策略：5.3 MySQL 8.0新特性：隐藏索引对调优的帮助

隐藏索引对调优的帮助

第15章 锁：3. 锁的不同角度分类：3.1从数据操作的类型划分:读锁、写锁：1.锁定读

MySQL8.0新特性:
在5.7及之前的版本，SELECT …FOR UPDATE，如果获取不到锁，
会一直等待，直到`innodb_lock_wait_timeout`超时。在8.0版本中，在SELECT …FOR UPDATE，SELECT …FOR SHARE后
添加`NOWAIT`、`SKIP LOCKED`语法，跳过锁等待，或者跳过锁定。- 通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询的行已经加锁:- 那么NOWAIT会立即报错返回- 而SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行。

第17章 其他数据库日志：4. 错误日志(error log)：4.4 MySQL8.0新特性

错误日志的改进

最后

2023-7-31 17:00:43

我们都有光明的未来

祝大家考研上岸
祝大家工作顺利
祝大家得偿所愿
祝大家如愿以偿
点赞收藏关注哦