MySql面试题

未名湖～～

已于 2024-05-17 16:23:40 修改

阅读量13

点赞数

文章标签： mysql 数据库 sql

于 2022-11-28 16:04:00 首次发布

本文链接：https://blog.csdn.net/qq_34750463/article/details/128081770

版权

文章目录

数据库基础
- 数据库三大范式是什么
- sql查询语句执行流程
- 更新语句的流程
- 顺序IO和随机IO的区别
- 磁盘是如何完成单次IO的
- 为什么mysql优化要尽量保证顺序读
存储引擎
- InnoDB
- MyISAM
- 聚簇索引和非聚簇索引
查询优化
- explain
SQL优化
建表优化
怎么找到哪个sql慢和优化慢查询
B+树和B树
- B树
- B+树
- B+树和B树的区别
索引
- 原理
- 类型
- 索引优化
- 索引失效
- 索引下推
- 回表和跳表
- - 回表
  - 跳表
- Mysql为什么使用B+树作为索引
log
- bin log
- undo log
- - undo log的工作原理
- redo log
- - redo log 特点
- relay log
事务
- 事务的四个特性（ACID）
- 如何保证数据一致性
- 事务实现的原理
- 事务的隔离级别
- 事务并发可能存在的问题
- 如何解决数据的读取一致性
- - LBCC
  - MVCC
MVCC
- MVCC解决的问题
- MVCC原理
- 在RC（读已提交）隔离级别下解决了脏读的原理
- 在RR（不可重复读）隔离级别下解决了可重复读的原理
锁
- 全局锁（Global Locks）
- 表级锁（Table-level Locks）
- 行级锁（Row-level Locks）
- 意向锁（Intention Locks）
- 记录锁（Record Locks）
- 间隙锁（Gap Locks）
- 临键锁（Next-key Locks）
- 死锁
- - 死锁的发生条件
  - 解决
- select ... for update 是锁表还是锁行
集群（高可用）
- 主从复制
- 读写分离
- 监控预警
- 定期备份
sharding sphere
- sharding JDBC
- 分库分表规则
- 分库分表下如何分页
- sharding Proxy
- 不停机分库分表迁移

序号	内容
1	基础面试题
2	JVM面试题
3	多线程面试题
4	MySql面试题
5	集合容器面试题
6	设计模式面试题
7	分布式面试题
8	Spring面试题
9	SpringBoot面试题
10	SpringCloud面试题
11	Redis面试题
12	RabbitMQ面试题
13	ES面试题
14	Nginx、Cancal
15	Mybatis面试题
16	消息队列面试题
17	网络面试题
18	Linux、Kubenetes面试题
19	Netty面试题

数据库基础

数据库三大范式是什么

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

sql查询语句执行流程

1、客户端与数据库通讯。在通讯前，通过数据库连接池建立数据库驱动和数据库连接，连接完成后，发送sql语句。使用连接池，可以减少频繁创建和销毁造成的性能下降。

2、查询缓存。：优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。默认关闭，不推荐使用。

3、语法解析和预处理。词法解析就是将完整的SQL打碎成一个个的单词。它会打碎成8个符号，记录每个符号是什么类型，从哪里开始到哪里结束。

语法解析：对SQL做一些语法检查，然后根据MYSQL定义的语法规则，根据SQL生成一个数据结构。这个数据结构被称为解析数

预处理：如果表名错误，会在预处理器处理时报错。它会检查生成的解析数，解决解析数无法解析的语句。

4、查询优化与查询执行计划。

5、查询执行引擎。返回查询结果

更新语句的流程

1、客户端将SQL语句传入到MYSQL。

2、MySql根据条件查询到要修改的数据。

3、修改根据查询条件查出来的数据，将旧值set 为新值（不会将修改后的结果更新到磁盘）。

4、将修改的结果更新进内存。（等待刷脏）

5、记录redo log ，并将数据的状态改为prepare。

6、修改好了之后可以提交事务（准备提交事务）。

7、将业务数据写入bin log。

8、commit 提交事务。

9、将redo log 里这个事务的相关记录状态设置为commit。

顺序IO和随机IO的区别

顺序IO：记录首地址和偏移量，记录速度快，只能追加，浪费空间。不能删除和修改，只能新增。适合日志

随机IO：记录地址，省空间，相对慢，适合数据和索引。

磁盘是如何完成单次IO的

单次IO时间 = 寻道时间 + 旋转延迟 + 传送时间。

为什么mysql优化要尽量保证顺序读

因为顺序读取磁盘数据比随机读取磁盘数据快的多，相差几十-几百的数量级。

进行磁盘读取的过程：

1.先寻找数据所在柱面号、盘片号、扇区号，从而定位到数据所在扇区（所用的时间称为寻道时间，时间最久）

2.然后盘面旋转到刚才定位的扇区所在位置，磁头读取数据（所用的时间称为旋转时间）

3.最后把读取的数据传输出去（所用的时间称为传输时间）

所以磁盘IO时间=寻道时间+旋转时间+传输时间

磁盘预读：每次读取为一页(一般4kb大小)，可能包含几个扇区（一般512bit）。

如果顺序读取的话，可以省略寻道时间，把相邻的几个扇区都读取出来。如果随机读取，每次读取数据，都需要重复以上3个步骤，耗费时间更久。

存储引擎

InnoDB

InnoDB支持事务操作、外键，是聚簇索引，不保存数据的总条数，5.7版本后支持全文索引，支持表锁，行锁（默认行锁），必须有主键（如果没有，会隐藏的生成一个6byte的int类型的索引作为主键），存储frm文件为表结构，ibd文件存储数据内容。

MyISAM

非聚簇索引，有一个专门的变量保存整个表的行数（count快），支持全文索引，支持表锁，不支持行锁，frm存表结构，myd 存数据

聚簇索引和非聚簇索引

只有我们创建的主键id索引，我们可以叫他id索引，它别名又叫做聚簇索引，（因为只有id索引，叶子节点包含了完整的记录行）理解成一个别名的即可。

如果我们再创建一个name索引，它就叫做非聚簇索引，或者辅助索引。

查询优化

explain

All：全表扫描，效率最低的。

index：全索引扫描，比all的效率要好。主要有两种情况，一是当前查询时覆盖索引（需要查询的数据在索引中就可以索取），或者是使用了索引进行排序，

range：表示利用索引查询的时候限制了范围，在指定的范围内查询，这样避免了index 的全表扫描。（一般情况下，至少要达到range级别。最好能达到ref级别）。例：explain select * from emp where empno between 1 and 10;

index_subquery：利用来关联子查询，不在扫描全表。例：explain select * from emp where emp.job in(select job from t_job);

unique_subquery：该类型类似于index_subquery，使用的是唯一索引。例：explain select * from emp where deptno in (select deptno from dept);

ref_or_null：对于某个字段即需要关联条件，也需要null值的情况。例：explain select * from emp where mgr is null or mgr = 1;

ref：使用了非唯一性索引进行数据查找。例：explain select * from emp e, dept d where e.deptno = d.deptno;

eq_ref：使用唯一性索引查找。explain select * from emp e, dept d where e.empno= d.empno;

const：这个表中最多有一个匹配行。explain select * from emp where empno = 1；

SQL优化

1、避免使用SELECT *来选择所有列，而应该具体指定所需的列。
2、使用索引：确保在经常用于搜索、排序和连接的列上建立索引。
3、减少JOIN数量，优化JOIN条件
4、避免在列上使用函数：这会导致全表扫描，降低查询性能
5、使用LIMIT限制返回结果：如果只需要部分结果，使用LIMIT来限制返回的行数。如果知道结果只有一条数据的时候，使用limit 1 会提升效率
6、优化WHERE子句：避免在WHERE子句中使用!=或NOT IN，这些操作可能导致全表扫描。使用EXISTS替代IN，当子查询返回的结果集很大时，EXISTS通常更快。
7、使用UNION ALL替代UNION：
8、建表优化
9、对数据分库分表

建表优化

1、设计表时，数据类型能选择小的就选小的，小的占用硬盘容量比较少。

2、设计表时，数据类型越简单越好。使用整形比字符串操作代价更低，字符集的校对规则比整形复杂。使用mysql自建类型，不是使用字符串存日期和时间。

使用整形存储IP地址。ip转整形函数：INET_ATON，整形转IP：INET_NEOA。

3、尽量避免使用null。数据库中null ！= null。如果查询中包含可谓null的列，对mysql来说很难优化，因为null列使得索引、索引统计和值都比较复杂。

4、varchar(5)和varchar(255)存储相同的内容，硬盘存储空间相同，但是内存占用空间不同。所以使用varchar时，使用最小的符合需求的长度。

5、datetime：占用8字节，可保存到毫秒

timestamp：占用4字节，精确到秒，采用整形存储。时间范围：1970-01-01 到 2038-01-19。依赖数据库设置的时区。自动更新列的值。

date：占用3个字节，可以利用日期函数进行日期之间的运算。

怎么找到哪个sql慢和优化慢查询

1、启用慢查询日志：对于MySQL，你可以通过修改配置文件或动态地设置slow_query_log参数来启用慢查询日志。慢查询日志会记录执行时间超过设定阈值的SQL语句。

2、分析慢查询日志：检查慢查询日志，找出执行时间最长的SQL语句。分析这些SQL语句，查看它们是否涉及复杂的JOIN操作、缺少索引、扫描大量数据等。

3、使用EXPLAIN：对于疑似慢查询的SQL语句，使用EXPLAIN关键字来分析查询的执行计划。EXPLAIN会展示查询如何被数据库执行，包括使用的索引、扫描的行数等。

4、监控工具：使用数据库监控工具（Performance Schema）来实时观察SQL语句的执行情况。这些工具通常可以显示查询的执行时间、消耗的资源等。

5、检查网络和机器负载：有时，SQL查询慢可能不是由于查询本身的问题，而是由于网络延迟或机器负载过高。使用网络监控工具和系统监控工具来检查这些因素。

6、考虑热点数据：如果某个特定的数据点或数据集被频繁访问，可能会导致单点负载不均衡。这种情况下，你可能需要考虑数据分区或缓存策略来优化性能。

7、审查数据库设计：某些情况下，慢查询可能由于不恰当的数据库设计引起，例如缺乏合适的索引、表结构不合理等。
审查并优化数据库设计可能有助于解决慢查询问题。

8、定期维护和优化：定期更新数据库统计信息、重建索引、清理无用数据等，以保持数据库性能。

B+树和B树

当我们想要进行范围查询，比如找某一类所有的书时，我们只需要找到最顶层对应的格子，然后沿着这个格子下面的路径一直走到最底层，就可以找到所有相关的数据了，非常方便。

总的来说，B树和B+树都是为了高效地查找数据而设计的。B树可以直接找到数据，而B+树则更适合进行范围查询。它们都是数据库和文件系统中非常重要的数据结构。

B树

B树就像是一个多层的书架，每一层都有多个格子，每个格子可以存放一本书（也就是数据）。每个格子都有一个标签，表示这个格子存放的是哪一类书。当我们想找一本书时，我们可以从顶层开始，根据标签找到对应的格子，然后看那个格子里的标签，再进一步找到下一层的格子，这样一层一层地找下去，直到找到我们想要的书。

B树的一个特点是，它尽量让每一层的格子数量都差不多，这样就不会出现某一层格子太多或太少的情况，从而保证查找的效率。

B+树

B+树和B树很像，也是一个多层的书架，每个格子也可以存放数据和标签。但是，B+树有一个不同的地方：它把所有的数据都放在了最底下一层，也就是说，只有最底层的格子才存放实际的数据，上面的所有格子都只存放标签（值对应的键）。

B+树和B树的区别

B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。

B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

原理

索引的原理很简单，就是把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

类型

B 树索引

将数据以一种树状的数据结构存储，每个叶节点都位于相同的距离。当对数据进行查找时，MySQL可以通过比较并遍历指针快速导航到树的节点，这比线性搜索要快得多。适用于全值匹配、匹配行的最左前缀、匹配范围值、NULL值的查询

hash索引

基于哈希表实现，只有精确匹配索引中的所有列的查询才有效。不能用于排序操作

全文索引

它能够将存储在数据库中的大段文本中的任意内容信息高效地查找出来，类似于关键词和文件内容的映射。

只有数据类型为char, varchar, text, bigtext的列才可以创建全文索引。

组合索引

组合索引是基于表中的多个列创建的索引。当你经常需要根据表中的多个列来进行查询时，组合索引就能帮助你提高查询效率。

案例：组合索引（a,b,c）。

where a = 1 ：只使用了a。

where a= 1 and b= 2：只使用了a，b。

where a= 1 and b= 2 and c = 3 ：使用了 a b c

where b =2 or c = 3 ：没有使用索引，使用or导致索引失效

where a = 1 and c = 3 ：只使用了a

where a= 1 and b > 2 and c = 3 ：只使用了a b

where a= 1 and b like ’%2%‘ and c = 3 ：只使用了a

索引优化

1、不要在sql的查询条件中不要使用表达式，把计算放在业务代码中

2、尽量使用主键查询，而不是其他索引，主键查询不会触发回表。

3、使用前缀索引。例：alter table citydemo add key (city(7)); 创建前缀索引，只取city字段的前7个字节。用在某些值是固定的几个字符串的时候，

4、使用索引扫描来做排序。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，

5、union all ，in，or都能使用索引，但是推荐使用in。使用union时，会默认distinct，不推荐使用union。

6、范围列可以使用索引。范围条件：> 、< 、>= 、<= 、between。但是范围列后面的列的索引无法使用，索引只能使用一个范围列。

7、查询中的条件强制类型转换会全表扫描。

8、更新十分频繁的数据，区分度不高的字段不适合建立索引。区分度：count(distinct(列名)) /count(*) 大于80%可以建立索引

9、创建索引的列，不允许为null，可能会得到不符合预期的结果。

10、需要表连接的时候最好不要超过三张，因为需要join的字段，数据类型必须一致。左连接：会查左表全部数据。右连接：会查右表全部数据。

11、如果明确知道只有一条返回值，limit能提高效率

12、单表索引控制在5个以内。

索引失效

查询的列没有被索引，或者查询条件没有用到索引列。
查询条件使用了函数或表达式，导致索引失效
查询条件中使用了不等于（!= 或 <>）操作符，使用了IS NULL或IS NOT NULL，如果列不是索引列，索引不会被使用
like 条件中，前面带%，例如，like %A 会导致索引失效。如果是后面带%，例如 like A% ，不会造成
查询条件中列的数据类型与索引列的数据类型不一致
隐式类型转换也可能导致索引失效
如果OR连接的多个条件中的列不是同一个索引的一部分

索引下推

回表和跳表

回表

“回表”一词通常用于数据库查询中，尤其是在使用索引进行查找时。当执行一个需要返回额外数据列的查询，而这些列没有包含在索引中时，数据库需要执行一个“回表”操作。以下是关于回表的关键点：

1、索引查找：首先，数据库使用索引来查找满足查询条件的行。

2、额外数据获取：如果查询需要的数据没有完全包含在索引中（即索引不是覆盖索引），数据库需要根据索引中的指针回到原始数据表中获取完整的行数据。

3、性能影响：回表操作会增加磁盘I/O，可能会使查询性能受影响。因此，对于高性能的查询，推荐使用覆盖索引，这样查询就可以直接在索引中完成，而不需要回表。

跳表

跳表是一种随机化的数据结构，可以在对数平均时间内完成搜索、插入和删除操作，是一种替代平衡树的高效数据结构。跳表的关键特点包括：

1、层级结构：跳表由多层链表组成，每一层都是下一层的子集。最底层包含所有元素，而顶层包含的元素最少。

2、快速搜索：搜索时，从顶层开始，利用链表的顺序性在每层进行快速跳跃，直到找到目标元素或到达需要检查的最低层。

3、概率平衡：跳表中元素层的提升是基于随机化决策的，这使得结构在统计意义上保持平衡，而无需像平衡树那样复杂的重平衡操作。

4、动态性：跳表可以很容易地动态添加和删除节点，平均性能下仍然保持效率。

Redis 中的有序集合（sorted sets）就是使用跳表作为其底层数据结构之一的例子。

Mysql为什么使用B+树作为索引

1、B+树减少了IO次数

由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。

2、B+树查询效率稳定

由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。

3、B+树更适合范围查找

B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

log

bin log

Binlog则是MySQL的Server层实现的。以事件的形式记录了所有的DDL和DML语句。它记录的是操作而不是数据，属于逻辑日志。可以用来恢复数据和做主从复制。

查看binlog命令： mysqlbinlog --no-defaults --start-datetime=‘2023-02-08 17:29:40’ --stop-datetime=‘2023-02-08 17:29:50’ binlog.000016 --base64-output=decode-rows -vv

undo log

undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

insert: 插入一条记录时，将这条记录的主键记录下来，回滚时根据这个主键删除即可。
delete: 删除一条记录时，将这条记录的内容记录下来，回滚时重新插入到表中即可。
update：修改一条记录时，将被更新的列的旧值记录下来，回滚时将这些值更新回去即可。

undo log的工作原理

在更新数据之前，MySQL会提前生成undo log日志，当事务提交的时候，并不会立即删除undo log，因为后面可能需要进行回滚操作，要执行回滚（rollback）操作时，从缓存中读取数据。undo log日志的删除是通过通过后台purge线程进行回收处理的。

redo log

redo log：InnoDB引擎特有的日志，重做日志，我们把所有对内存数据的修改操作写入日志文件，如果服务器出了问题（崩溃），我们从这个日志中读取数据。用它来实现事务的持久性。

redo log 特点

1、记录修改后的值。

2、redo log 的大小是固定的，前面的内容会被覆盖，所以不能用于业务数据恢复。

3、redo log 是 innoDB实现的，不是所有的存储引擎都会实现。

relay log

中继日志。主要组成部分包括事务ID、事件类型、表名和行号、数据内容以及时间戳等。这些组件共同记录了对数据的修改操作，从而确保在从服务器上能够准确地重现主服务器上的数据更改。

主要用于主从复制

事务

事务的四个特性（ACID）

原子性、隔离性、持久性最终都是为了实现一致性。

原子性（atomicity）

事务是一个不可分割的工作单元，事务里的操作要么都成功，要么都失败，如果事务执行失败，则需要进行回滚。

在InnoDB里面是通过 undo log 实现的，undo log 记录了数据库修改之前的值（逻辑日志），一旦发生异常，就可以用undo log 实现回滚。

一致性（consistent）

数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。

数据的完整性：实体完整性、列完整性（如字段的类型、大小、长度要符合要求）、外键约束等

业务的一致性：例如在银行转账时，不管事务成功还是失败，双方钱的总额不变。

隔离性（isolation）

事务的所操作的数据在提交之前，对其他事务的可见程度。

Innodb事务的隔离级别是由MVVC和锁机制实现的。

持久性（durable）

一旦事务提交，它对数据库中数据的改变就是永久的。

持久性是通过redo log 实现的。操作数据的时候，会先写到内存的buffer pool中，同时记录redo log ，如果在刷盘之前出现异常，在重启后可以读取redo log 的内容，写入到磁盘保证数据的持久性。

如何保证数据一致性

其实数据一致性是通过事务的原子性、持久性和隔离性来保证的。

事务实现的原理

Mysql 的事务实现原理，就是 InnoDB 是如何保证 ACID 特性的。

A 表示 Atomic 原子性，也就是需要保证多个 DML 操作是原子的，要么都成功，要么都失败。那么，失败就意味着要对原本执行成功的数据进行回滚，所以 InnoDB 设计了一个 UNDO_LOG 表，在事务执行的过程中，把修改之前的数据快照保存到UNDO_LOG 里面，一旦出现错误，就直接从 UNDO_LOG 里面读取数据执行反向操作就行了。

事务的隔离级别

为保证在并发环境下读取数据的完整性和一致性，数据库提供了四种事务隔离级别，隔离级别越高，越能保证数据的完整性和一致性，但对高并发性能影响也越大，执行效率越低。（四种隔离级别从上往下依次升高）

读未提交：允许事务在执行过程中，读取其他事务尚未提交的数据；
读已提交：允许事务在执行过程中读取其他事务已经提交的数据；
可重复读（默认级别）：在同一个事务内，任意时刻的查询结果都是一致的；
读序列化：所有事务逐个依次执行，每次读都需要获取表级共享锁，读写会相互阻塞。

事务并发可能存在的问题

脏读

事务A正在操作数据，对数据进行了修改，但是没有提交事务。这时，事务B来访问这条数据，事务B拿到数据后，事务A回滚了事务。导致事务B获取到的数据和数据库中的不一致，就是事务B读到的数据是脏数据。

幻读

在一个事务中，先后两次进行读取相同的数据（一般是范围查询），但由于另一个事务新增了数据，导致前后两次结果不一致。

不可重复读

在一个事务内，多次读取同一个数据，但是由于另一个事务在此期间对这个数据做了修改或者删除并提交，导致前后读取到的数据不一致；

更新丢失

两个或多个事务操作相同的数据，然后基于选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：最后的更新覆盖了其他事务所做的更新。

如何解决数据的读取一致性

LBCC

基于锁的并发控制。既然要保证前后两次读取的数据的一致性，那么读取数据的时候，锁定要操作的数据，不允许其他事物修改。

如果仅仅是基于锁来实现事物的隔离，一个事务读取的时候不允许其他事物的修改，意味着不支持并发的读写操作。而大多数应用都是读多写少，这样会极大的影响效率。

MVCC

参考下面MVCC原理。

MVCC

多版并发本控制: 是一种控制数据库并发的方法。在MySQL的InnoDB中，主要是为了提高数据库的并发性能，不用加锁，非阻塞并发读。MVCC只在已提交读（Read Committed）和可重复读（Repeatable Read）两个隔离级别下工作。

MVCC解决的问题

MVCC为数据库解决了在并发读写数据库时，可以做到读操作不阻塞写操作，写操作不阻塞读操作。提高了数据库读写并发的性能。这里的读是快照读，也就是普通的select语句
快照读不用加锁，但是可能会读到历史数据。类似乐观锁。

MVCC的快照读在读已提交和可重复读隔离级别下都能有效防止脏读的发生，在可重复读隔离级别下，快照读通过保持事务内一致的ReadView解决了不可重复读问题。幻读通常需要通过其他机制（如间隙锁）来防止。

当前读（不属于MVCC）：读取的是记录数据的最新版本，显式加锁的都是当前读。例如：select … for update; select … lock in share mode; 。

MVCC原理

MVCC主要是依赖于三个隐藏字段（DB_ROW_ID，DB_TRX_ID，DB_ROLL_PTR，这三个字段是每行数据都有的）和undolog，read view 实现的。

DB_ROW_ID：自动生成的唯一标识符。
DB_TRX_ID：事务ID，用于标识修改该行数据的事务。
DB_ROLL_PTR：指向undolog的指针（回滚），用于记录该行数据的修改历史（当前数据修改前的数据）。

Read View

当时数据的快照，但并不是记录的全部数据，而是只记录了几个字段，通过这几个字段可以从版本链（一个数据多次修改后的版本）中找到当时的数据。
Read View包含以下四个字段
m_ids ：表示在生成 ReadView 时当前系统中活跃的读写事务的事务id 列表。（通俗来说：活跃的事务指的就是没有提交的事务）
min_trx_id ：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务id ，也就是 m_ids 中的最
小值。
max_trx_id ：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。
creator_trx_id ：表示生成该 ReadView 的事务的事务id 。

版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链

RC和RR级别下MVCC的差异

RC（读已提交）:每次进行快照读都会生成新的readview.
RR（不可重复读）:事务开启后第一次进行快照读的时候才会生成readview,以后的快照读都会使用第一次生成的readview(如果进行当前读,则会重新生成readview)

在RC（读已提交）隔离级别下解决了脏读的原理

在MVCC中，每个事务在开始时都会获得一个唯一的事务ID，并且只能读取到那些在其事务ID之前已经提交的数据版本。当一个事务正在修改数据时，它会生成一个新的数据版本，并将修改后的数据保存在这个版本中。其他事务在读取这些数据时，会根据它们的Read View来判断哪个版本的数据是可见的。由于未提交的事务修改的数据版本不会被其他事务看到，因此脏读问题得以解决。

在RR（不可重复读）隔离级别下解决了可重复读的原理

MVCC通过为每个事务提供一个一致的Read View来解决这个问题。当事务第一次进行快照读时，它会生成一个Read View，这个Read View在事务的生命周期内保持不变。因此，无论其他事务如何修改数据并提交更改，当前事务后续的读取操作都会基于这个固定的Read View，从而确保读取到的数据版本是一致的

锁

全局锁（Global Locks）

全局锁会锁定整个数据库系统，这是MySQL中最不精细的锁类型。例如，使用FLUSH TABLES WITH READ LOCK命令可以对所有表加读锁，这在备份整个数据库时很有用。

表级锁（Table-level Locks）

表级锁是MySQL中最基本的锁类型，它会锁定整个表。MyISAM和MEMORY存储引擎主要使用表级锁。表级锁包括：

表锁（Table Locks）：允许多个读操作同时进行，但写操作会阻塞其他的读写操作。
元数据锁（Metadata Locks, MDL）：当访问表元数据时，MySQL会使用MDL来保证读写的一致性。

表级锁的缺点是并发性能较低，尤其是在写操作频繁的环境中。

行级锁（Row-level Locks）

行级锁是最精细的锁类型，它只会锁住被操作的特定行。InnoDB存储引擎使用行级锁。行级锁包括：

共享锁（Shared Locks）：允许事务读一行数据，并且其他事务也可以读这行数据，但不能修改它。
排他锁（Exclusive Locks）：允许事务读取并修改一行数据，并且阻止其他事务读取或修改这行数据。

行级锁可以大大提高并发处理能力，但管理行级锁需要更多的内存和存储空间。

意向锁（Intention Locks）

意向锁是InnoDB使用的一种表级锁，用来表明某个事务打算对表中的行进行加锁，这样就可以快速检测到行级锁与表级锁之间的冲突。意向锁分为两种：

意向共享锁（Intention Shared Locks, IS）：事务打算给数据行加共享锁。
意向排他锁（Intention Exclusive Locks, IX）：事务打算给数据行加排他锁。

记录锁（Record Locks）

记录锁是针对索引项的锁，InnoDB会对符合条件的索引记录加锁。

间隙锁（Gap Locks）

间隙锁锁定一个范围，但不包括记录本身，即锁定索引记录之间的间隙。它主要用于防止幻读。

临键锁（Next-key Locks）

临键锁是InnoDB特有的，它是记录锁和间隙锁的组合，锁定一个范围并包含行记录。它可以防止幻读，并且是默认的隔离级别（可重复读）。

死锁

死锁是指在数据库管理系统（DBMS）中，两个或多个事务在执行过程中因为竞争资源而相互等待，导致它们中的任何一个都无法继续执行的情况。在死锁发生时，每个事务都在等待其他事务释放它需要的资源。

死锁的发生条件

互斥条件：资源不能被多个事务同时使用。
持有并等待条件：一个事务至少已经持有一个资源，并且请求新的资源，而该资源被其他事务持有。
不可抢占条件：已经分配给一个事务的资源不能被强制从该事务中抢占，只能由事务自愿释放。
循环等待条件：存在一种事务等待循环，每个事务都在等待链中的下一个事务所持有的资源。

解决

确保事务按照一定的顺序请求资源

数据库系统定期检测死锁，一旦检测到死锁

1、结束一个或多个事务，释放它们持有的所有资源

2、选择一个或多个事务进行回滚，以释放它们持有的资源

select … for update 是锁表还是锁行

在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了。

如果查询条件用了索引/主键，那么select … for update就会进行行锁。

如果是普通字段(没有索引/主键)，那么select … for update就会进行锁表。

集群（高可用）

主从复制

1、从节点连接主节点，主节点创建binlog dump 线程，从节点建立I/O线程。binlog dump 线程用来给从节点同步数据，主节点有多少个从节点就会创建多少个binlog dump 线程。从节点的I/O线程用来接收主节点发送过来的binlog数据

2、当主节点进行增、删、改操作时，会按顺序写入到bin log 中。

3、当主节点的binlog发生变化时，binlog dump会通知从节点，并将binlog内容推送给从节点。

4、从节点的I/O线程接收主节点推送过来的binlog，并将内容写到本地的relay log （中继日志）中。

5、从节点的SQL线程通过轮询或者通知的方式来读取relay log（中继日志）的数据，根据relay log（中继日志）的内容修改从节点。

读写分离

写请求发送到主数据库，读请求分散到多个从数据库。

监控预警

部署监控系统（如Zabbix、Nagios、Percona Monitoring and Management）来监控数据库的健康状况，并在问题发生时及时发出预警。

定期备份

定期执行数据备份，并确保备份数据可以快速恢复。可以使用物理备份（如Percona XtraBackup）或逻辑备份，并要定期测试恢复流程的有效性。

sharding sphere

sharding JDBC

读写分离

原理是什么

用了读写分离，必须要用主从复制。

# ShardingSphere 数据源配置示例
spring:
  shardingsphere:
    datasource:
      names: ds_master, ds_slave0, ds_slave1 # 数据源名称列表，可以根据实际情况定义多个主库和从库
      ds_master:
        type: com.alibaba.druid.pool.DruidDataSource # 数据源类型，使用druid连接池
        driver-class-name: com.mysql.jdbc.Driver # 数据库驱动类名
        jdbc-url: jdbc:mysql://localhost:3306/master_db?serverTimezone=UTC&useSSL=false
        username: root
        password: password
      ds_slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3307/slave_db0?serverTimezone=UTC&useSSL=false
        username: root
        password: password
      ds_slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3308/slave_db1?serverTimezone=UTC&useSSL=false
        username: root
        password: password
	sharding:
		default-data-source-name: ds_master #默认数据源，主要用于写，但是一定要配置下面的master-slave-rules。如果不配，会读写都操作这个库
    master-slave-rules: # 主从规则配置
      ms_ds: # 规则名称，可自定义
        master-data-source-name: ds_master # 主库数据源名称
        slave-data-source-names: 
          - ds_slave0
          - ds_slave1 # 从库数据源名称列表
        load-balance-algorithm-type: ROUND_ROBIN # 负载均衡算法，默认轮询,RANDOM,随机策略。

    props: # 其他属性配置
      sql-show: true # 是否开启SQL显示，默认false

分库分表

垂直分库

按照业务将表进行分类，根据系统的业务逻辑将一个数据库中的表拆分成多个不同的数据库中，适合业务耦合度低、业务逻辑清晰的系统。

垂直分表

将大表拆成小表，将一个表字段拆分成多个表，每个表存储一部分字段。将常用的字段放入到一张表中，不常用的字段放入一张表中。

水平分库

将同一个表数据按照某种规则（ID的哈希值、时间范围、地理区域）划分为多个部分，然后将这些数据分别存到不同的数据库中（多库多表）。每个库中的表结构一致，数据分布在不同的物理节点上。这些数据整合到一起是完整的数据。

水平分表

在同一个数据库内将数据，把一张大表的数据拆分到多个数据结构相同的表中（单库多表）。这些子表存储原表的部分数据，所有子表的结构完全一样

分库分表

分库分表规则

逻辑表在数据库中是不存在的。逻辑表例：订单数据根据主键尾数拆分为 10 张表，分别是 t_order_0 到 t_order_9，他们的逻辑表名为 t_order，真实表： t_order_0 。

分库分表配置（inline）

spring:  
  shardingsphere:  
    datasource:  
      names: ds0,ds1 # 数据源名称列表  
      ds0: # 数据源0配置  
       
      ds1: # 数据源1配置  
     
    rules:  
      sharding:  
        tables:  
          order: # 订单表分片配置  
            actual-data-nodes: ds$->{0..1}.order$->{0..1} # 真实的数据节点，对应数据源和表名  
            table-strategy: # 表分片策略  
              inline:  
                sharding-column: order_id # 分片键  
                algorithm-expression: order$->{order_id % 2} # 分片算法表达式 有几个库 % 几 
            key-generator: # 主键生成策略  
              type: SNOWFLAKE  
              column: order_id  
            database-strategy: # 数据库分片策略（可选）  
              inline:  
                sharding-column: user_id # 分片键  
                algorithm-expression: ds$->{user_id % 2} # 分片算法表达式  有几个表 % 几 
        binding-tables: # 绑定表，用于联合查询优化  
          - order  
        default-key-generator: # 默认主键生成策略（可选）  
          type: SNOWFLAKE  
          column: id

分库配置（inline）

spring:  
  shardingsphere:  
    datasource:  
      names: ds0,ds1 # 数据源名称列表  
      ds0: # 数据源0配置  
       
      ds1: # 数据源1配置  
     
    rules:  
      sharding:  
        tables:  
          order: # 订单表分片配置  
            actual-data-nodes: ds$->{0..1}.order # 真实的数据节点，对应数据源和表名  
            database-strategy: # 数据库分片策略（可选）  
              inline:  
                sharding-column: user_id # 分片键  
                algorithm-expression: ds$->{user_id % 2} # 分片算法表达式  
        binding-tables: # 绑定表，用于联合查询优化  
          - order  
        default-key-generator: # 默认主键生成策略（可选）  
          type: SNOWFLAKE  
          column: id

分表配置（inline）

spring:  
  shardingsphere:  
    datasource:  
      names: ds0# 数据源名称列表  
      ds0: # 数据源0配置  
      
    rules:  
      sharding:  
        tables:  
          order: # 订单表分片配置  
            actual-data-nodes: ds0.order$->{0..1} # 真实的数据节点，对应数据源和表名  
            key-generator: # 主键生成策略  
              type: SNOWFLAKE  
              column: order_id  
            database-strategy: # 数据库分片策略（可选）  
              inline:  
                sharding-column: user_id # 分片键  
                algorithm-expression: order->{user_id % 2} # 分片算法表达式  
        default-key-generator: # 默认主键生成策略（可选）  
          type: SNOWFLAKE  
          column: id

标准分库分表

spring:  
  shardingsphere:  
    datasource:  
      names: ds0,ds1 # 定义数据源名称，这里假设有两个数据源  
      ds0:  

      ds1:  

    rules:  
      sharding:  
        tables:  
          your_table: # 要分片的逻辑表名  
            actual-data-nodes: ds$->{0..1}.your_table_$->{0..1} # 实际存储的表节点，这里假设每个数据源有两个表  
            table-strategy: # 表分片策略  
              standard:  
                sharding-column: your_sharding_column # 分片键  
                precise-algorithm-class-name: com.example.sharding.YourPreciseShardingAlgorithm # 精确分片算法类名  
                range-algorithm-class-name: com.example.sharding.YourRangeShardingAlgorithm # 范围分片算法类名  
            key-generator: # 主键生成策略  
              type: SNOWFLAKE  
              column: id  
        binding-tables: # 绑定表规则，如果有多个表需要联合查询，可以放在这里  
          - your_table  
        default-database-strategy: # 默认数据库分片策略  
          standard:  
            sharding-column: your_sharding_column  
            precise-algorithm-class-name: com.example.sharding.YourDefaultDatabasePreciseShardingAlgorithm