21MySQL

CodeRabbit
73 阅读46分钟

MySQL

  • 我现在一张表存20亿的数据，你觉得这个服务能稳定运行吗，为什么？
  • 为什么数据量大了查表会慢呢？怎么样查慢？怎么样查不会慢？
  • 除了跟Mysql索引机制有关，还有什么有关？
  • 使用查询语句的时候有什么规范，sql怎么写，是必须做到的？

基础

关于主键和唯一键的区别

主键是一种约束，用来标识一条记录的唯一性，每个表只能有一个主键。主键必须是唯一的、非空的，且每个表只有一个主键。主键可以作为其他表的外键，建立表与表之间的关系。一般来说，主键使用自增长的整型数据类型，比如int。

唯一键也是用来保证数据的唯一性，但是不同于主键，一个表可以有多个唯一键。唯一键可以包含NULL值，但是只能有一个NULL值。唯一键一般不作为其他表的外键，而是用来确保某一列的唯一性。比如在一个用户表中，邮箱和手机号都是唯一的，那么可以分别将邮箱和手机号设置为唯一键。

in 和 exists 的区别

MySQL 中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。我们可能认为 exists 比 in 语句的效率要高，这种说法其实是不准确的，要区分情景：

  1. 如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
  2. 如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。
  3. not in 和 not exists：如果查询语句使用了 not in，那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用 not exists 都比 not in 要快。

在 MySQL 中，IN 和 EXISTS 都可以用来检索满足条件的数据，但它们的使用方式和效率略有不同。

IN 子句用于指定一个值列表，它用来匹配列值的集合。它的语法如下：

scssCopy codeSELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

其中，value1、value2 等是用逗号分隔的一个值列表，IN 子句会检查列值是否在这个列表中，如果匹配，则返回这些行。IN 子句适用于检索静态列表，可以一次性地检查多个值。

EXISTS 子句用于检查子查询是否返回了任何行。它的语法如下：

sqlCopy codeSELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

EXISTS 子句的关键字是子查询，它会检查子查询中是否有符合条件的行，如果有，则返回这些行。EXISTS 子句适用于检索动态列表，因为它可以检查子查询的结果是否为空。

在效率方面，IN 子句的查询速度比 EXISTS 子句快，因为 IN 子句只需要查找静态列表，而 EXISTS 子句需要执行一个子查询，这个子查询可能需要访问磁盘或创建临时表，效率较低。但是，当检索动态列表时，EXISTS 子句比 IN 子句更快。

另外，在使用 IN 子句时，需要注意传递的值列表的长度，如果列表太长，可能会导致内存溢出或查询时间过长。因此，通常需要在代码中限制值列表的长度，或者使用 EXISTS 子句来代替 IN 子句。

Mysql中常用的函数，

  1. 聚合函数：SUM、AVG、COUNT、MAX、MIN等，用于对数据进行统计计算；
  2. 字符串函数：CONCAT、SUBSTR、LENGTH、TRIM等，用于对字符串进行处理；
  3. 日期函数：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等，用于对日期时间进行处理；
  4. 数学函数：ROUND、CEIL、FLOOR、ABS等，用于对数值进行计算；
  5. 逻辑函数：IF、CASE、COALESCE等，用于逻辑判断和条件赋值；
  6. 其他函数：RAND、NOW、UUID、INET_ATON等，用于生成随机数、获取当前时间、处理IP地址等。

mysql中的数据类型

数值类型包括整数类型（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）和浮点数类型（FLOAT、DOUBLE、DECIMAL）。

日期时间类型包括日期类型（DATE）、时间类型（TIME）、日期时间类型（DATETIME）、时间戳类型（TIMESTAMP）等。

字符串类型包括固定长度字符串类型（CHAR）和变长字符串类型（VARCHAR）、文本类型（TEXT）等。

二进制类型包括二进制字符串类型（BINARY、VARBINARY）、大对象类型（BLOB、TEXT）等。

JSON类型是MySQL 5.7版本之后新增的数据类型，用于存储和处理JSON格式的数据。

数据类型转换可能存在的问题

  1. 精度问题：例如将浮点数转换为整型时可能会造成精度损失；
  2. 类型不匹配：例如将字符串类型转换为数值类型时，如果字符串中包含非数字字符，则会转换失败；
  3. 溢出问题：例如将一个大整数转换为小整数时，如果超过了目标数据类型的范围，则会发生溢出；
  4. 时间格式问题：例如将时间字符串转换为时间戳时，如果时间字符串格式不符合要求，则会转换失败。

leftjoin和rightjoin的作用

LEFT JOIN 和 RIGHT JOIN 是 SQL 中 JOIN 操作的两种类型，用于将两个或多个表的行连接起来。它们的主要区别在于连接操作的方向不同。

LEFT JOIN 返回左侧表的所有行以及与其关联的右侧表的匹配行，如果右侧表中没有匹配行，则使用 NULL 填充。

RIGHT JOIN 返回右侧表的所有行以及与其关联的左侧表的匹配行，如果左侧表中没有匹配行，则使用 NULL 填充。

union不同类型的数据会发生什么，

在MySQL中，如果使用UNION联合查询的两个SELECT查询结果集的列的数据类型不同，MySQL会自动将其转换为相同的类型。如果某个查询结果集的某一列是字符串类型，而另一个查询结果集的同一列是数字类型，MySQL会尝试将该字符串转换为数字，如果无法转换，MySQL会将该字符串转换为0。

如果使用UNION ALL，MySQL不会尝试将列的数据类型进行转换。

去除重复元素的SQL语句

select *的弊端

  1. 不必要的网络流量：如果表中有很多列，使用SELECT *会返回所有列的数据，即使查询结果只需要其中的一部分，这样会增加网络传输的流量，导致查询速度变慢。
  2. 不利于缓存：如果将查询结果缓存在内存中，使用SELECT *会导致缓存效率低下。这是因为如果表中有更新，所有缓存中的列都需要刷新，包括不需要使用的列。
  3. 查询时间变长：如果表中有很多列，使用SELECT *会增加查询时间。这是因为MySQL在解析查询语句时需要检查所有的列，而不是只检查需要的列。

索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

0.索引优缺点

优点：提高查询效率

缺点：占用存储空间，降低了增删改的效率。

索引缓存

执行查询语句的时候，会先查询缓存。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。

不过，MySQL 8.0 版本后移除，因为这个功能不太实用

1.索引分类： +5

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
  • 按「字段个数」分类：单列索引、联合索引。

2.为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？ +4

B+Tree 相比于 B 树、二叉树或 Hash 索引结构的优势在哪儿？

*1、B+Tree vs B Tree* +2

1）B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，一层可以放的叶子节点更多，在相同的磁盘 I/O 次数下，就能查询更多的节点。

2）另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

*2、B+Tree vs 二叉树*

1）普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。

2）平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B+ 树，可以存储更多的节点数据，树的高度也会降低，读取磁盘的次数就少

*3、B+Tree vs Hash*

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询。

3.什么是聚簇索引？什么是非聚簇索引？ +6

主键索引和普通索引的叶子节点上存了什么？

  • 聚簇索引：索引和数据一起存放，叶子节点存放的是实际数据。

  • 二级索引：叶子节点存放的是主键值，而不是实际数据

  • 索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

B+ 树的叶子结点的链表有什么作用 为什么层数是 3 左右呢，不是其他数

它的叶子节点是按顺序排列的，并且形成一个链表，这个链表有助于区间查找，因为在B+树中，相邻的叶子节点的区间是连续的。叶子节点的链表可以通过头结点和尾结点进行快速的遍历。

回表

覆盖索引

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

最左前缀原则

最左前缀原则是指在使用联合索引（composite index）时，如果查询条件中只涉及到联合索引的左边前缀列，则索引可以被有效利用，反之则不能。

以最左匹配为原则，进行查询时使用 （A）（AB）(AC)，（ABC）会使用到索引，其它情况不会使用索引

索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

4.哪些字段适合加索引 +2

  1. 主键字段：主键字段自动具有唯一性约束
  2. 外键字段：外键字段连接两个表，经常用于关联查询，因此在外键字段上添加索引可以提高关联查询的效率。
  3. 经常查询的字段：经常在 WHERE 子句中出现的字段，如经常使用的过滤条件、连接条件等，应该添加索引。
  4. 经常进行排序和分组的字段：排序和分组操作需要对查询结果进行排序和聚合，如果在排序和分组字段上添加索引，可以加速排序和分组操作。

建索引有什么需要注意的

5.索引设计原则

img

索引优化方法

1）前缀索引优化，减小索引字段大小

2）覆盖索引优化，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

3）主键索引最好是自增的，每插入一条新记录，都是追加操作，不需要重新移动数据。

4）索引最好设置为 NOT NULL

导致优化器在做索引选择的时候更加复杂，比如进行索引统计时，count 会省略值为NULL 的行。如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表

5）防止索引失效；

普通索引和主键索引的查询过程

索引失效的情况，以及为什么会失效 +2

  1. 模糊查询：如果在查询中使用了LIKE操作符，但是在查询中使用的通配符（%）在开头，MySQL无法使用索引
  2. 当我们在查询条件中对索引列使用函数，计算，不等于操作符或者（ is null， is not null） 就会导致索引失效。
  3. 如果字符串是索引列，和数字比较会因为隐式类型转换导致索引失效

​ MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

  1. 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
  2. or连接的条件 包含没索引的，那么涉及到的索引都失效。
  3. MySQL评估使用全表扫描要比使用索引快,则不使用索引。

日志

MySQL 中常见的日志有哪些？

Innodb 存储引擎层

  • undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
  • redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；

Server层

  • 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。
  • 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
  • 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。
  • 二进制日志（bin log）：关于二进制日志，它记录了数据库所有执行的 DDL 和 DML 语句（除了数据查询语句 select、show 等），以事件形式记录并保存在二进制文件中。

慢sql怎么查

  1. MySQL自带的慢查询日志（slow query log）：开启慢查询日志功能，MySQL会记录执行时间超过指定时间的SQL语句，并将它们写入到一个文件中。可以通过分析该文件来查找慢SQL语句。
  2. MySQL Performance Schema（性能模式）：MySQL Performance Schema可以提供详细的性能数据和统计信息，包括慢查询的相关信息，可以通过查询相关的系统表来查找慢SQL语句。
  3. MySQL Show Processlist命令：Show Processlist命令可以显示当前正在执行的SQL语句，以及它们的执行状态和执行时间，可以通过该命令查找执行时间较长的SQL语句。
  4. 使用性能分析工具：可以使用一些开源或商业的性能分析工具，例如Percona Toolkit、pt-query-digest等，这些工具可以分析慢查询日志，并提供可视化的性能报告和分析。

redo log 如何保证事务的持久性？

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

redo log 什么时候刷盘？

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

  • MySQL 正常关闭时；
  • 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
  • InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
  • 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）。

页修改之后为什么不直接刷盘呢？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

redo log 文件写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1 。重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

undo log 如何保证事务的原子性？

当一个事务对数据库进行修改时，MySQL会将这些修改操作记录在undo log中。如果事务执行失败或被回滚，MySQL可以通过undo log来撤销这些修改操作，从而将数据库恢复到执行该事务之前的状态。

binlog 主要记录了什么？

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

binlog 和 redolog 有什么区别？

1、适用对象不同：

  • binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
  • redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

  • binlog 逻辑日志 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
    • STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
    • ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
    • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
  • redo log 是物理日志，每个页（Page）的更改的物理情况，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3、写入方式不同：

  • binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
  • redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

  • binlog 用于备份恢复、主从复制；
  • redo log 用于掉电等故障恢复。

5.写入时间不同

bin log 仅在事务提交前进行提交，也就是只写磁盘一次。

在事务执行过程中不断写入 redolog，然后在事务提交时将 redolog 中的操作写入磁盘。

为什么要两阶段提交

  • 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段是旧值 jay，与主库的值不一致性；
  • 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段还是旧值 jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xiaolin，与主库的值不一致性；

两阶段提交的过程是怎么样的

  • prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
  • commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

  • 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
  • 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

事务

mysql事务特性: +2 ACID mysql的存储引擎innodb是如何实现这些特性的？

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

  • 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转 10 块钱，不管成功与否，A 和 B 的总金额是不变的。
  • 隔离性：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。
  • 持久性：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

那 ACID 靠什么保证的呢？

  • 事务的隔离性是通过数据库mvcc或者锁的机制实现的。
  • 事务的原子性由 undo log 来保证：undo log 是逻辑日志，记录了事务的 insert、update、deltete 操作，回滚的时候做相反的 delete、update、insert 操作来恢复数据。
  • 事务的持久性由 redo log 来保证：redolog 被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入 redo log 持久化。
  • aid都是为了保证c 一致性

四种隔离级别:

RU RC RR S 默认隔离级别是****可重复读 (Repeatable Read)

1.读未提交（read uncommitted）： 一个事务还未提交时，他做的变更就能被别的事务看到。

2.读提交（read committed）：提交以后，他做的变更才能被其他事务看到。

3.可重复读（repeatable read）：事务A如果想看事务B，那事务A在执行过程中看到的数据前后一致，与启动时是一样的。

4.串行化（serializable ）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

事务的三种问题：幻读遇到过吗, 咋解决: +2

1.脏读：脏读指事务A读取到了事务B更新了但是未提交的数据，然后事务B由于某种错误发生回滚，那么事务A读取到的就是脏数据。

2.不可重复读： 同一个事务中，两次相同查询 同一条记录值不一样 （update）

3.幻读：是两次读取的记录数量不同。 （insert,delete）事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入 / 删除了数据，并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

img

事务的各个隔离级别都是如何实现的？

1.读未提交

读不加锁,写加写锁。

2.读取已提交&可重复读

读取已提交和可重复读级别利用了ReadView和MVCC，也就是每个事务只能读取它能看到的版本（ReadView）。

  • READ COMMITTED：每次读取数据前都生成一个 ReadView
  • REPEATABLE READ ：在第一次读取数据时生成一个 ReadView

3.串行化

串行化的实现采用的是读写都加锁的原理。

串行化的情况下，对于同一行事务，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

mysql隔离级别，RR怎么实现的？发生错误怎么进行回滚

MySQL的隔离级别怎么由读已提交提高到可重复读

MVCC MVCC实现过程+2

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL 数据库的一个核心特性，它可以实现多个事务并发执行，保证数据的一致性和隔离性。

版本链

聚簇索引记录中两个跟事务有关的隐藏列

对于 InnoDB 存储引擎，每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR

  • DB_TRX_ID，事务 ID，每次修改时，都会把该事务 ID 复制给DB_TRX_ID；
  • DB_ROLL_PTR，回滚指针，指向回滚段的 undo 日志。

由于每次变动都会先把undo日志记录下来，并用DB_ROLL_PTR指向undo日志地址。因此可以认为，对该条记录的修改日志串联起来就形成了一个版本链，版本链的头节点就是当前记录最新的值。

Read View

有四个重要的字段：

image-20230312155740186

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

  • 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。

  • 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。

  • 如果记录的 trx_id 值在 Read View 的 min_trx_id max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

    • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。

  • 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

MySQL默认事务隔离级别，如果设置成读已提交会产生什么问题？（不可重复读）

会产生幻读吧

锁

数据库中有哪些锁，你介绍下MySQL中的锁？

根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

如果按照兼容性，有两种，

  • 共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。
  • 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。

表级锁

  1. 表锁：当对整张表进行加锁时，称为表锁。表锁分为读锁和写锁

  2. 元数据锁（MDL）;

    ​ 我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

    • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
    • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

    MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

    当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

    反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

  3. 意向锁；

    • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
    • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

    意向锁的目的是为了快速判断表里是否有记录被加锁。如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

  4. AUTO-INC 锁；

    在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。 防止多线程访问，保证自增

行锁

  • Record Lock，记录锁，也就是仅仅把一条记录锁上；

  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；

    间隙锁是 MySQL InnoDB 存储引擎中一种特殊类型的锁，它锁定了一个范围（两个索引键之间的间隔），防止其他事务在这个范围内插入记录。这种锁主要用于解决幻读问题。

  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。左开右闭区间

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

介绍一下间隙锁？ +2

意向锁是什么知道吗？

说说 InnoDB 里的行锁实现?

for update是锁表还是锁行？

delete语句会加哪些锁

在 MySQL 中，DELETE 语句会加排它锁（X 锁）和间隙锁（GAP 锁）。

当删除语句执行时，MySQL 会对需要删除的记录加上 X 锁，以保证当前事务可以独占这个记录，防止其它事务修改或删除该记录。同时，MySQL 还会对需要删除记录的前后记录加上 GAP 锁，以防止其它事务往这个范围内插入新记录，导致当前事务无法正常提交。

需要注意的是，如果使用了 WHERE 子句，MySQL 还会对匹配到的所有记录加上 X 锁和 GAP 锁。如果使用了 LIMIT 子句，则只有查询到的记录才会被加锁。因此，在写 DELETE 语句时，应该尽量限制 WHERE 条件和 LIMIT 数量，避免不必要的加锁操作。

如何预防数据库死锁

数据库死锁是指两个或多个事务在执行时，由于互相等待对方释放资源而陷入无限等待的状态，从而导致事务无法继续执行的现象。

以下是预防数据库死锁的几种方法：

  1. 减少事务的持有时间：尽量在需要加锁的代码块中将业务逻辑的代码部分减少，这样可以缩短事务的持有时间，从而减少死锁的可能性。
  2. 避免长事务：长事务往往需要长时间占用资源，因此，它们更容易引起死锁。尽量将长事务拆分成多个较短的事务，这样可以减少死锁的风险。
  3. 尽量按照相同的顺序访问数据表：通过约定访问数据库中的数据表的顺序，可以减少不同的事务之间发生死锁的可能性。
  4. 为表添加合适的索引：索引的使用可以提高查询效率，减少数据库的锁定时间，从而减少死锁的风险。
  5. 尽量不要在事务中进行大量的数据处理：在事务中进行大量的数据处理会导致锁定的时间增加，从而增加死锁的可能性。
  6. 定期检查数据库的锁定情况：定期检查数据库的锁定情况，及时发现锁定问题，可以快速解决锁定问题，避免死锁的发生。

MySQL 遇到过死锁问题吗，你是如何解决的？

（chat）MySQL 的乐观锁和悲观锁了解吗？

悲观锁是指，在整个数据处理过程中，将数据处于锁定状态。悲观锁通常会在访问数据之前先加锁，然后在访问数据时，其他线程无法对其进行更改。MySQL中的行锁和表锁就是悲观锁的实现。

乐观锁则是指，在整个数据处理过程中，不加任何锁，而是通过版本号或时间戳等机制，判断当前操作是否有其他线程对数据进行修改。若有，则回滚操作；若没有，则进行数据更新。MySQL中的CAS（Compare And Swap）操作就是乐观锁的一种实现方式。

MySQL 中的 CAS 通常是通过版本号来实现的。每个数据都有一个版本号，当事务想要修改一个数据时，首先需要读取该数据的版本号。如果版本号与事务开始时读取的版本号一致，则说明该数据没有被其他事务修改过，此时事务可以修改该数据，并更新版本号。否则，说明该数据已被其他事务修改，此时事务需要使用 CAS 机制回滚操作。

性能优化

  1. 如果让你做sql优化，你想到的常用措施？

    sql常用的优化，以及每一种优化的原因

  2. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

  3. 用索引可以提高查询

  4. SELECT子句中避免使用*号，尽量全部大写SQL

  5. 应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL

  6. where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描

  7. in 和 not in 也要慎用，否则会导致全表扫描

  8. 使用索引：索引是提高查询效率的重要手段，可以避免全表扫描。使用合适的索引可以大大提高查询效率。

  9. 减少子查询：子查询是 SQL 查询中常见的一种查询方式，但是它的执行效率较低。尽量避免使用子查询，可以使用 JOIN 等其他方式代替。

  10. 避免使用 SELECT *：在实际查询中，不需要查询全部字段的情况很常见，因此尽量使用 SELECT 指定需要查询的字段，避免查询无用字段，提高查询效率。

  11. 避免使用 OR：在 SQL 查询中，使用 OR 会增加查询的复杂度，降低查询效率。尽量使用 UNION、UNION ALL 或者其他方式代替。

  12. 优化 JOIN 查询：在查询多个表时，使用 JOIN 查询是一种常见的方式。但是，JOIN 查询的性能可能会受到表大小、索引、查询语句等多种因素的影响，因此需要进行优化。

  13. 避免使用临时表：在 SQL 查询中，使用临时表可能会导致性能问题。尽量避免使用临时表，可以使用其他方式替代，如表变量或者内联查询等。

  14. 分析 SQL 执行计划：了解 SQL 查询的执行计划可以帮助优化查询语句，提高查询效率。通过 EXPLAIN 等工具可以获取 SQL 执行计划，从而进行优化。

存储引擎

执行一条 SQL 查询语句，期间发生了什么？

  • 连接器：建立连接，管理连接、校验用户身份；
  • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  • 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  • 执行 SQL：执行 SQL 共有三个阶段：
    • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
    • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
    • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

image-20230312182204446

数据库有哪些存储引擎？

MylSAM MEMORY InnoDB

MySQL 5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5版本之后，InnoDB 是 MySQL 的默认存储引擎。

说说inndob和myisam区别 + 3

  1. 事务支持：InnoDB支持事务处理，而MyISAM不支持事务处理。
  2. 锁级别：MyISAM只支持表级别的锁，InnoDB支持行级别的锁，这意味着当多个用户同时对同一表进行操作时，InnoDB能够更好地保证数据的并发性和一致性。
  3. 外键支持：InnoDB支持外键，而MyISAM不支持。
  4. 索引：MyISAM 的索引为非聚簇索引，数据结构是 B 树；InnoDB 的索引是聚簇索引，数据结构是 B+树。
  5. 可靠性：InnoDB具有更好的可靠性和稳定性，因为它支持事务和崩溃恢复机制，而MyISAM在出现崩溃时容易造成数据损坏。
  6. 性能：在并发读写方面，InnoDB比MyISAM更优秀，因为InnoDB支持行级别的锁和MVCC机制，可以减少锁的争用，提高并发性能。而在大量读操作时，MyISAM则具有更好的性能，因为它不支持事务和行级别的锁。
  7. 主键必需：MyISAM 允许没有任何索引和主键的表存在；InnoDB 如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键(用户不可见)

综上所述，InnoDB适合于对数据完整性要求较高、并发性能要求较高的应用场景，而MyISAM适合于只进行简单的读写操作、对数据完整性要求不高的应用场景，

什么场景下该用myisam

MyISAM 适用于对静态表进行高速读取的应用，尤其是只读应用，比如 Web、新闻等，对事务完整性没有要求。

实操

何如衡量sql的优劣，explain的字段

Explain的作用

用于解释查询的执行计划，即查询优化器如何执行查询语句

explain会获取sql语句的执行信息。

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

image-20221122090612080

image-20221122090627343

id id表示执行顺序， id相同，执行顺序从上到下，id不同 值越大越先执行。

select type ： simple 没有使用表连接

​ primary 主查询 就是最外层的查询

​ union union中第二个或者后面的查询

​ subquery select/where 后面的子查询

type null 不访问任何表 select 1；

​ system 访问系统表

​ const 根据主键或者唯一索引查询

​ ref 非唯一索引。

​ all 全表扫描

MySQL是如何保证主从库数据一致性

MySQL通过主从复制技术来实现主库和从库之间的数据同步。在主从复制过程中，MySQL采用了以下机制来保证主从库之间的数据一致性：

  1. 二进制日志（binlog）：主库将所有的数据变更操作记录在binlog中，并将binlog传输到从库。从库通过解析binlog来进行数据同步。由于binlog中包含了所有的数据变更操作，因此可以保证主从库数据的一致性。
  2. GTID（Global Transaction ID）：MySQL 5.6版本之后引入了GTID机制，通过为每个事务分配唯一的GTID标识，可以避免主从库之间出现数据重复或者漏传的情况，保证数据同步的完整性和正确性。
  3. 主从复制线程：MySQL通过两个线程来实现主从复制，即IO线程和SQL线程。IO线程负责从主库读取binlog，并将binlog传输到从库，SQL线程负责解析binlog并在从库上执行相应的数据变更操作。通过这两个线程的配合，可以保证主从库之间的数据同步。
  4. 从库的复制过程是异步的，可能存在主从数据不一致的情况。为了尽可能减小主从数据不一致的时间窗口，MySQL提供了一些机制来优化主从同步的性能，例如半同步复制、并行复制等。

总之，MySQL通过binlog、GTID、主从复制线程等机制来保证主从库之间的数据一致性，同时提供了一些优化机制来提高主从同步的性能和稳定性。

mysql主从复制的原理

image-20230312184636886

mysql读写分离

MySQL读写分离的实现方式是在主库上进行写操作，在从库上进行读操作，通过主从复制技术，将主库的写操作同步到从库上。应用程序访问数据库时，可以将读请求分发到从库上，写请求发送到主库上。

实现MySQL读写分离有多种方式，其中比较常见的方式是使用MySQL官方提供的MySQL Proxy或者使用中间件，如MyCat、MHA等。这些中间件可以拦截应用程序的数据库请求，将读请求转发到从库上，将写请求转发到主库上，从而实现读写分离。

数据库中假设有十个字段，只有五个才能提供给外部，如何实现？

可以使用SQL中的SELECT语句中的列别名(alias)来实现这个需求。具体做法是在SELECT语句中仅选择需要提供给外部的五个字段，并给它们指定别名，例如：

SELECT col1 AS field1, col2 AS field2, col3, col4 AS field4, col5 AS field5
FROM table_name;

这样，SELECT语句的结果集中只包含这五个字段，并且这些字段的列名被修改为了field1、field2、field4、field5，可以直接提供给外部使用。而另外的五个字段不会出现在结果集中，也不会被提供给外部。

（chat）数据库并发问题，100个线程同时update一个数据，结果是多少？

这取决于数据库的并发控制机制。如果数据库使用悲观并发控制机制，则100个线程将被顺序执行，每个线程都会在更新数据之前获取独占锁，这会导致其他线程需要等待锁释放才能继续执行，因此执行时间会非常长。如果数据库使用乐观并发控制机制，则所有线程都可以并发执行，但只有最后一个线程的更新结果会被提交到数据库中，其他线程的更新结果将被丢弃。如果数据库使用MVCC机制，则所有线程都可以并发执行，每个线程都会看到一个快照版本的数据，并根据快照版本执行更新操作。最终，数据库会将所有更新结果合并为一个版本，并将其提交到数据库中。因此，多线程更新同一条数据时，具体的结果取决于数据库的并发控制机制。

你知道MySQL为什么能够支持那么高的QPS吗，他是怎么取数据的（提示Buffer Pool数据结构）

QPS指的是“每秒查询率”（Queries Per Second），表示在一秒内处理的数据库查询次数。它通常用于衡量数据库系统的性能，越高的QPS意味着系统处理查询的能力越强。

Buffer Pool是一个用于缓存索引和数据的内存池，MySQL通过将热点数据缓存在内存中，避免了频繁的磁盘I/O操作，从而大大提升了系统的性能。

当MySQL需要取数据时，它首先会去查看 Buffer Pool 中是否有相应的数据。如果数据已经缓存在内存中，MySQL会直接从 Buffer Pool 中获取数据，避免了访问磁盘的操作，从而提升了系统的响应速度。如果数据没有缓存在 Buffer Pool 中，则 MySQL 会从磁盘中读取数据，并将其放入 Buffer Pool 中，以便后续的访问。

在高并发的情况下，Buffer Pool 可以帮助 MySQL 避免过多的磁盘I/O操作，从而提高了系统的吞吐量和响应速度。因此，合理配置 Buffer Pool 的大小是保证 MySQL 高并发和高QPS的重要因素之一。

什么情景下做分表，什么情景下做分库？

分表和分库都是针对数据水平切分的技术，适用于数据量大、性能瓶颈明显的场景。一般来说，分表是指在同一个数据库中将一个大表水平切分成多个小表，而分库则是将一个数据库中的多个表分布到多个物理数据库中。

下面列举一些通用的分表和分库的情景：

1.分表：

  • 数据表的数据量非常大，查询速度较慢，无法满足业务需求。
  • 数据表的数据写入速度较慢，无法满足业务需求。
  • 数据表的数据需要按照一定规则进行分片存储，比如按照时间范围或者地理位置等。
  • 数据表的字段太多，查询速度较慢，需要根据业务需求进行垂直切分。

2.分库：

  • 单个数据库的数据量过大，无法承受并发访问的压力。
  • 单个数据库的性能已经达到极限，无法通过硬件升级或优化来提升性能。
  • 多个业务之间需要进行数据隔离，避免业务之间的数据干扰。
  • 多个业务之间需要灵活地进行数据库的水平扩展和收缩。

需要注意的是，分表和分库都会带来一定的复杂性，比如数据的一致性问题、跨表查询的问题等，因此需要根据具体业务需求和系统架构来进行权衡和选择。

分库分表以及用到的中间件

水平分表策略

分库分表是解决单一数据库存储能力瓶颈的一种方案，通常在数据量达到一定程度后，采用水平拆分的方式将数据分散到多个数据库实例中进行存储和查询。但是，分库分表会带来新的问题，如跨库事务、数据迁移、数据一致性等，因此需要使用中间件来解决这些问题。

以下是几种常用的中间件：

  • sharding-jdbc
  • Mycat
  1. MyCat：MyCat是基于MySQL协议和JDBC协议实现的一个分布式数据库系统，支持数据的读写分离、分库分表、全局序列号生成器等功能。
  2. ShardingSphere：ShardingSphere是一套开源的分布式数据库中间件，支持分库分表、读写分离、分布式事务等功能。
  3. Vitess：Vitess是一个开源的分布式数据库系统，支持水平拆分和垂直拆分，可以运行在各种云环境和容器中。

这些中间件可以有效地解决分库分表所带来的问题，提升数据库的性能和可扩展性。

(chat)如何创建分布式ID

在分布式系统中，通常需要创建分布式ID以确保ID的唯一性。创建分布式ID有多种方法，以下是几种常见的方法：

  1. UUID：使用UUID可以生成一个128位的唯一标识符。在Java中，可以使用java.util.UUID类来生成UUID。
  2. Twitter的Snowflake算法：Snowflake算法是一种使用时间戳、机器ID和序列号来生成64位唯一ID的算法。它可以支持高并发，且生成的ID是有序的。Snowflake算法的核心思想是将一个64位的整数分成4部分：时间戳、机器ID、数据中心ID和序列号。在Java中，可以使用第三方库如Twitter的Snowflake库来实现该算法。
  3. 数据库自增ID：在数据库中创建一个自增ID列可以确保每个ID都是唯一的。如果需要在多个数据库中生成ID，可以使用分布式锁来保证ID的唯一性。但是，这种方法可能会影响性能，并且在分库分表的情况下需要特殊处理。
  4. 第三方分布式ID生成器：除了上述方法外，还可以使用第三方分布式ID生成器如百度的UidGenerator或美团的Leaf来生成分布式ID。这些ID生成器使用一些特殊的算法和技术来确保生成的ID是唯一的。

(chat)mysql达到最大连接数会发生什么问题？

MySQL 服务器有一个 max_connections 参数，用于控制同时打开的最大连接数。当连接数超过该值时，新的连接请求将被拒绝，连接失败并返回错误信息。

可能会遇到MySQL: ERROR 1040: Too many connections”的异常情况

如果 MySQL 达到最大连接数，可能会发生以下问题：

  1. 应用程序无法连接到 MySQL，导致无法处理请求。
  2. 如果没有优秀的连接池机制，频繁地打开和关闭连接会消耗服务器的资源。
  3. 如果同时进行大量的查询或更新操作，会导致系统性能下降，甚至崩溃。
  4. 如果连接请求没有得到及时处理，可能会导致应用程序的性能下降或崩溃。

因此，为了避免达到最大连接数，可以采取以下措施：

  1. 合理地配置 max_connections 参数，根据服务器的硬件配置、应用程序负载和并发连接数来设置合适的值。
  2. 使用连接池机制，避免频繁地打开和关闭连接。
  3. 优化 SQL 查询语句，减少不必要的查询和更新操作，以降低服务器负载。
  4. 对于高并发的场景，可以考虑使用主从复制和分库分表等技术来分摊数据库负载。

数据库最有效的清理方式（×）

大表优化（√）+2

水平/垂直分库之后代码要怎么改（√重写Result）

avatar
文章
阅读
粉丝