05-Mysql(63)

1. 如何实现数据排序

1. 索引排序

如果查询中的 ORDER BY 子句包含的字段已经在索引中，并且索引的排列顺序和 ORDER BY 子句一致，则可直接利用索引进行排序。这种方式效率最高，因为索引有序

2. filesort

当使用 explain 分析 SQL 发现执行计划的 extra 中包含 using filesort 时，说明它无法应用索引的顺序，而主动排序了

  • 如果需要排序的数据比较少，则直接在内存中通过 sort_buffer 就能排了。通过 sort_buffer_size 控制 sort_buffer 的大小
    • 如果需要排序的数据量小于 sort_buffer 则直接在内存中排序
    • 反之需要利用磁盘临时文件排序，性能就比较差了。

1. 内存排序

sort_buffer 中排序的细节

1. 双路排序

max_length_for_sort_data（默认 1024 字节），如果 select 列的单行数据长度超过它，则 MySQL 采用row_id 排序，即把 《row_id（有主键就是主键）+ 排序字段》放置到 sort_buffer 中排序。排序需要多个回表的过程，等于需要两次查询，也叫双路排序（Two-Pass Sort）

select a, b, c from t1 where a = '面试鸭' order by b;
  • 为了节省排序占用的空间，此时 sort_buffer 只会有放置 id 和 b 来排序，排序好后，再回表
image.png
2. 单路排序

select 列的数据没有超过 max_length_for_sort_data，进行单路排序（Single-Pass Sort），将 select 字段都放置到 sort_buffer 中

image.png
  • 排序后直接得到结果集，返回给客户端即可，相比双路排序它减少了回表的动作，因此效率更高

2. 磁盘文件临时排序

如果查询的数据超过 sort_buffer，说明内存放不下了，因此需要利用磁盘文件进行外部排序，一般会使用归并排序（简单理解：将数据分为很多份文件，单独对文件排序，之后再合并成一个有序的大文件）

  • 利用磁盘排序效率会更低，针对一些情况可以调大 sort_buffer_size，避免磁盘临时文件排序

2. changeBuffer有了解吗

官网图：

710.png

buffer pool 里面有一块内存是留给 change buffer 用的。它主要用来避免于二级索引页修改产生的随机I/O

  1. change buffer 是什么东西呢？

    • 假设直接执行一条 update table set name = 'yes' where id = 1，如果当前数据页不在 buffer pool 中，innodb 会把更新操作缓存到 change buffer 中，当下次访问到这条数据后，会把数据页加载到 buffer pool 中，并且应用上 change buffer 里面的变更，保证数据的一致性

  2. change buffer 有什么好处？

    • 当二级索引页不在 buffer pool 中时，change buffer 可以避免立即从磁盘读取对应索引页导致的昂贵的随机I/O ，对应的更改可以在后面当二级索引页读入 buffer pool 时候被批量应用
    • change buffer 只能用于二级索引的更改，不适用于主键索引、空间索引、全文索引。唯一索引也不行，因为唯一需要读取数据然后检查数据的一致性

  3. 更改先缓存在 change buffer 中，假如DB挂了，更改不是丢了吗？

    • change buffer 也是要落盘存储的，会落盘到系统表空间里，然后 redo log 也会记录 chang buffer 的修改来保证数据一致性

如果内存够大能装下所有数据，或者二级索引很少，或者磁盘是固态的对随机访问影响不大，其实可以关闭 change buffer，因为它增加了复杂度

3. SQL语句的执行过程

select * from user where id = 1;
  • Server层：涉及到一系列的业务组件
  • 存储引擎层：负责存储发送过来的数据、提供读写接口等功能（补充：MySQL 的存储引擎是插件式的，一个DB里面的不同表可以用不同的存储引擎）。
image-20240430095137010.png

具体流程：

  1. 当客户端的 SQL 发送到 MySQL 时，首先是到达 Server 层的连接器，连接器会对此次发起的连接进行权限校验，以此来获取这个账号拥有的权限。当账号或密码不正确时，会报 Access denied for user 错误，相信大家对这个错误也并不陌生。连接成功如果后续没有任何操作，那么这个连接就处于空闲状态，到达一定时间后它便会断开连接，这个时间一般是 8 小时，是由 wait_timeout 参数控制的
  2. 查询缓存（在 MySQL 8.0 之后就被砍了）：将一个查询语句作为 key ，将上一次请求的结果作为 value，存储在缓存组件中，当同样的语句来查询时即可立马返回结果，不需要经历词法、语法分析等以下的步骤。只要表有数据改动缓存就失效了，在常见的联机事务处理（OLTP） 场景下是个鸡肋
  3. 分析器进行语法分析、词法分析。MySQL 会首先对语句进行 ”词法分析“ ，来判断语句是什么类型以及携带什么参数等
    • eg：MySQL 会将输入语句的 select 提取出来，判断出这是一条查询语句，将 from 后面的 user 提取出来作为查询表名、把 id 提取出来作为列名等。做完这些 MySQL 将会进行 ”语法分析“ 来判断语句的语法是否有误、是否满足 MySQL 的语法。如果语法有问题：You have an error in your SQL syntax; check the manual ...
  4. 优化器：对语句进行优化判断。平日里用的 explain 其实就是 MySQL 优化决定策略是怎样的。eg：表中有多个索引，优化器会帮选择使用哪个索引、使用了 join 多表连接，优化器会调整表的连接顺序
  5. 执行器：先会判断你 user 表是否有权限查询，如果没有权限它将会拒绝本次查询，返回错误信息。如果有权限，它将会根据表的存储引擎提供的接口进行数据查询将重复遍历表的行数据，判断 id 字段是否等于 1。直到遍历完整个表将符合条件的数据作为结果集返回给客户端

4. 存储引擎区别

在 MySQL 8.4 版本一共提供了 10 个引擎！

image.png

每种引擎都有其特定的特性和用途，但相对而言比较常见的是以下几个：

特性MyISAMMemoryInnoDBArchiveNDB
B+树索引YesYesYesNoNo
备份/按时间点恢复YesYesYesYesYes
集群DB支持NoNoNoNoYes
聚簇索引NoNoYesNoNo
压缩数据YesNoYesYesNo
数据缓存NoN/AYesNoYes
加密数据YesYesYesYesYes
外键支持NoNoYesNoYes
全文检索YesNoYesNoNo
地理空间数据类型支持YesNoYesYesYes
地理空间索引支持YesNoYesNoNo
哈希索引NoYesNoNoYes
索引缓存YesN/AYesNoYes
锁的粒度TableTableRowRowRow
MVCCNoNoYesNoNo
复制支持YesLimitedYesYesYes
存储限制256TBRAM64TBNone384EB
T-tree 索引NoNoNoNoYes
事务NoNoYesNoYes

1. MyISAM

  1. MyISAM 是基于 ISAM 引擎而来的，支持全文检索、数据压缩、空间函数，不支持事务和行级锁，只有表级别锁，它适用于 OLAP 场景，也就是分析类的，基本上都是读取，不会有什么写入动作的场景
  2. MyISAM 的索引也是 B+ 树，只是不像 InnoDB 那种叶子节点会存储完整的数据，MyISAM 的数据是独立于索引单独存储的，所以主键和非主键索引差别不大
  3. MyISAM 不支持崩溃后的安全恢复，而 InnoDB 有个 redolog 可以支持安全恢复
  4. MyISAM 写入性能差。因为锁的粒度太粗了，不支持行锁，只有表锁，所以写入时会对整张表加锁。不过有个并发插入的开关，开启之后当数据中间没有空洞时，也就是插入的新数据是从末尾插入时，读取数据是不会阻塞的

2. InnoDB

（MySQL 默认引擎）

  1. InnoDB 支持事务，实现了四种标准的隔离级别，利用 MVCC 来支持高并发，默认事务隔离级别为可重复读，支持行锁，利用行锁+间隙锁提供可重复读级别下防止幻读的能力，支持崩溃后的数据安全恢复
  2. 支持外键，不过一般互联网项目都不会用外键的，性能太差，利用业务代码来实现约束即可
  3. 由于 InnoDB 使用行级锁定和支持事务，因此在并发性能方面通常表现较好，特别是在多个用户同时对DB进行读写操作时

5. 索引有哪几类

  1. B+tree 索引：B+tree 是 MySQL 中最常用的索引类型。它使用平衡树结构来存储索引数据，适用于范围查询和排序
  2. 哈希索引：哈希索引基于哈希算法，将索引列的值转换为哈希值，然后通过哈希表来进行快速的等值查询。不过，哈希索引不支持范围查询和排序
  3. 全文索引：全文索引用于对文本字段进行全文搜索，允许在文本数据中进行关键字搜索和模糊匹配
  4. 空间索引：空间索引用于处理空间数据，如地理位置和几何形状。MySQL 提供了专门的空间索引类型，可以用于空间数据的查询和分析
  5. 全文空间索引：这是一种组合索引，结合了全文索引和空间索引的功能，用于同时处理文本数据和空间数据的查询
  6. 前缀索引：前缀索引允许在索引列的前缀上创建索引，而不是整个列值。这可以节省索引空间，并提高查询性能，尤其对于长文本字段很有用
  7. 复合索引：复合索引是在多个列上创建的索引，可以同时对多个列进行快速查找。合理设计复合索引可以提高查询性能

InnoDB 和 MyISAM 对各索引的支持：

索引类型InnoDBMyISAM
B+tree 索引支持支持
哈希索引不支持支持
全文索引支持（全文搜索）支持（全文搜索）
空间索引支持（R-tree）不支持
全文空间索引不支持不支持
前缀索引支持支持
复合索引支持支持

6. 聚簇、非聚簇索引区别

在 InnoDB 里聚簇索引就是主键索引，非聚簇索引指的是非主键索引（辅助索引、二级索引），默认情况下它们都是 B+树实现的

image.png

主键索引：

  • 非叶子节点存储主键和页号
  • 叶子节点存储完整的数据
  • 叶子节点之间有双向链表链接，便于范围查询
  • 叶子节点内部有页目录，内部记录是单链表链接，通过页目录二分再遍历链表即可得到对应记录
  • B+ 树只能帮助快速定位到的是页，而不是记录
  • 页大小默认 16k，是按照主键大小排序的，所以无序的记录插入因为排序会插入到页中间，又因为容量有限会导致页分裂存储，性能比较差，所以主键要求有序

非聚簇索引：

  • 和主键索引的差别就在于叶子节点仅存储索引列和主键，不包含没有完整行的数据
image.png

不要所有查询都用 select * ，可能伴随着很多随机 I/O

7. 回表

回表：使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应的主键（聚簇索引）键值，因此需要根据主键中去聚簇索引查找实际的数据行的过程

select * from user where age = 20;
  1. 根据二级索引（age）找到 age = 20 的 主键键值
image-20240503133710205.png
  1. 再根据主键去查找整行的数据
image-20240503133904146.png

回表其实不仅仅只是多查一次，还会带来随机 I/O

  • 通过 id 去主键索引查询时，id 肯定是不连续的，所以去主键索引频繁查询会造成大量随机 I/O。顺序 I/O 查询快，而随机 I/O 慢，所以频繁回表效率很低

8. 最左匹配原则

使用索引进行查询时，查询条件从索引的最左边开始逐个字段匹配，直到遇到第一个不能匹配的字段为止。这个原则主要应用于组合索引（复合索引），即一个索引包含多个列

9. 覆盖索引

覆盖索引（Covering Index）：指二级索引中包含了查询所需的所有字段，从而使查询可以仅通过访问二级索引而不需要访问实际的表数据（主键索引）

优点：

  • 减少I/O操作：因为查询可以直接从索引中获取所有需要的数据，避免了访问实际表的数据页，从而减少了I/O操作
  • 提高查询速度：索引比表数据更紧凑，因此从索引中读取数据比从表中读取要快
  • 减少内存占用：只需要读取索引页而不是表数据页，可以减少内存占用

10. 索引下推

索引下推（Index Condition Pushdown, ICP）是一种减少回表查询，提高查询效率的技术。通过将部分带有索引的条件交由存储引擎层进行过滤操作，从而减少了 IO（本该由 Server 层做操作，交由存储引擎层，因此叫做 “下推” ）

# 查询年龄在 18 到 25 岁之间，并且成绩在 80 分以上的学生
# (age, score) 建立了联合索引
SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
AND score >= 80;

# 《Using index condition》

没有使用索引下推：

  1. 通过索引 age 找到多条主键 ID（假设 1、3、5、6）数据
  2. 对每条数据，再通过主键 ID 回表查询完整的数据行（有四个主键 ID，需要回表 4 次）
  3. 再将数据交由 Server 层，将满足 score 的数据过滤出来得到最终数据行

使用索引下推：

  1. 通过索引 age 找到多条主键 ID（假设是 1、3、5、6）数据
  2. 因为联合索引内包含 score 字段，可直接再存储引擎通过 score >= 80 来进行索引过滤，最终得到的主键 ID 分别是（1、3、6）
  3. 再进行回表得到完整数据行（有三个主键 ID，需要回表 3 次）

注意：

  1. 索引下推在 MySQL 5.6 及以后的版本支持，InnoDB 和 MyISAM 这两个存储引擎都生效
  2. 如果查询中引用了子查询索引下推可能不会生效，具体看 explain
  3. 使用了函数、表达式索引下推也不能生效，这个和是否能命中索引的条件是一样的
  4. 使用了聚簇索引（主键）查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数

11. 建索引注意事项

  1. 不能盲目的建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时可能都需要维护索引的数据，消耗资源
  2. 对于字段的值有大量重复的不要建立索引
    • eg：性别字段，在这种重复比例很大的数据行中，建立索引也不能提高检索速度
    • 也不绝对，eg：定时任务的场景，大部分任务都是成功，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的
  3. 对于一些长字段不应该建立索引
    • eg：text、longtext 这种类型字段不应该建立索引。因为占据的内存大，扫描时大量加载至内存中还耗时，使得提升的性能可能不明显，甚至可能还会降低整体的性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取
  4. 当数据表的修改频率远大于查询频率时，应该考虑适当建立索引。因为建立索引会减慢修改的效率，如果很少的查询较多的修改，则得不偿失
  5. 需要频繁作为条件查询的字段应该建立索引。在 where 关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则可以考虑联合索引，减少索引数量
  6. 对经常在 order by、group by、distinct 后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速度

12. 索引一定有用吗

执行计划来查看索引是否生效 explain SQL语句：

  • type（访问类型）：这个属性显示了查询使用的访问方法
    • 当查询使用索引时，这个属性通常会显示为 index 或 range ，表示查询使用了索引访问
    • 是 ALL ，则表示查询执行了全表扫描，没有使用索引
  • key（使用的索引）：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是 NULL，则表示查询没有使用索引
  • rows（扫描的行数）：这个属性显示了查询扫描的行数，即查询返回的行数，需要评估下扫描量

当然，建立了索引不一定有用，我们从两个方面来看待这个问题：

1. 索引生效了吗

1. 联合索引不符合最左前缀

# `name_age_id` 的联合索引
select * where from age = 10 and id = 1;

2. 使用了运算

select * from user where id + 3 = 8;

3 .使用了函数

select * from user where LOWER(name) like 'cong%';

4. like随意使用

# 将 % 写在首位
select * from user where name like '%cong%';

5. or随意使用

# `or` 关键字时要两边同时都拥有索引才会生效
select * from user where name= 'cong' or age = 18;

# < 和 > 号也导致了索引失效
select * from user where age > 1 or age < 18;

6. 随意的字段类型使用

# name 为 varchar 类型
select * from user where name = 1;

7. 参数不同导致失效

# Mysql 使用了索引不如直接全表扫描效率高，直接让索引失效
select * from user where age > 18;

select * from user where age > 180;

8. 两个字段进行比较

select * from user where id > age;

9. 使用!= 、<>

# SQL 是否失效是根据查询到的数据集决定
select * from user where name != 'cong';

10. 使用is not null

  • 使用 is not null 会导致失效。DB系统需要扫描整个索引，找出所有不为 null 的值
  • 使用 is null 则不会。对于索引而言，查找 null 值的行与查找其他任何特定值的行都没有本质区别。在大多数情况下，null 值都会被索引包括在内

11. 使用order by

order by 后面跟的 不是主键 或者 不是覆盖索引 会导致不走索引

2. 索引生效反而变慢

  1. 确认是否选对了索引！MySQL 根据优化器会评估成本选择对应的索引，因为估计值不准确，导致选错了索引，因此查询速度反而更慢
  2. 对大字段建立了索引。给 text 字段建立了索引导致查询需要扫描更多的数据块，反而变慢

13. 索引越多越好吗

索引并不是越多越好。，因为索引不论从时间还是空间上都是有一定成本的

  1. 从时间上
    • 每次对表中的数据进行增、删、改、查时，都需要维护对应索引的数据，索引越多需要修改的地方也就越多，并且 B+ 树可能会有页分裂、合并等操作，时间开销就会更大
  2. 从空间上
    • 每建立一个二级索引，都需要新建一个 B+ 树，默认每个数据页都是 16kb，如果数据量很大，索引有很多，占用的空间可大
    • 查询优化器，需要分析当前的查询，选择最优的计划，这过程就需要考虑选择哪个索引的查询成本低。如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为数据的不准确而选择了次优的索引

14. B+树层面查询数据全过程

数据从根节点找起，根据键值的大小确定左子树还是右子树，从上到下最终定位到叶子节点

img

定位到叶子节点后，因为一片叶子默认有 16k 大小，所以理论上可以存多条记录。叶子节点的实际构造如图：

image.png

叶子节点有页目录结构，它其实就是一个索引，通过它可以快速找到记录

  • 页目录分为了多个槽，每个槽都指向对应一个分组内的最大记录，每个分组内都会包含若干条记录
  • 通过二分查询，利用槽就能直接定位到记录所在的组，从而就能获取到对应的记录，或还需通过链表遍历找到对应的数据

实际上，每个分组的记录数是有规定的，InnoDB 规定：

  • 第一个分组只有一条记录
  • 中间的分组 4-8 条记录
  • 最后一个分组 1-8 条记录

因此不必担心遍历很长的链表导致性能问题

15. 为什么要用B+树

image.png
  1. B+ 树是一种自平衡树
    • 每个叶子节点到根节点的路径长度相同，B+ 树在插入和删除节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除时树结构的变化小，更高效
    • 查找、插入、删除等操作的时间复杂度为 O(logn)O(log n)，能够保证在大数据量情况下也能有较快的响应时间
  2. 树的高度增长不会过快，使得查询磁盘的 I/O 次数减少
    • B+ 树不像红黑树，数据越多树的高度增长就越快。它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的 I/O 次数减少
  3. 范围查询能力强
    • B+ 树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效

16. 如何实现事务

主要是通过：锁、redo log 、undo log、mvcc

锁，为了防止数据的并发修改，满足事务的隔离性

  • 行级锁：针对单行记录的锁，细粒度的锁定，提高并发性能
  • 表级锁：针对整张表的锁，粒度较粗，但锁定和管理的开销较低
  • 意向锁：表明事务打算对某些行加锁，便于实现锁的层次化管理，意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求
  • redo log：重做日志，它会记录事务对DB的所有修改，在崩溃时恢复未提交的更改，用来满足事务的持久性
  • undo log：用于事务的回滚
  • MVCC 多版本并发控制：满足了非锁定读的需求，提高了并发度，实现读已提交和可重复读两种隔离级别

17. 长事务造成问题

# 删除一张一共有 5 亿数据的表里面的 2021 年数据
delete from yes where create_date > "2020-12-31" and create_date < "2022-01-01";

长事务，前提：这张表有 5 亿的数据，是一张超大表，where 条件可能涉及非常多的数据，所以可以从离线数仓或者备库查下数据量，这条 SQL 会删除 3 亿左右的数据

  • 长事务涉及到加锁，由于长事务锁了很多数据，如果期间有频繁的 DML 想操作这些数据，就会造成阻塞
  • 连接都阻塞住了，业务线程自然就阻塞了，可能还会影响到别的服务，可能产生雪崩，导致非常严重的线上事故
  • 长事务还可能会造成主从延迟，你主库执行了好久，才执行完给从库，从库又要重放好久，期间可能很长一段时间数据不同步
  • 业务都有个特殊停机窗口，执行长事务，然后执行了 5h 之后，中间抛错了，事务回滚了，于是浪费了 5h，还得重新开始

18. 什么是MVCC

MVCC（Multi-Version Concurrency Control）多版本并发控制。其实指的是一条记录会有多个版本，每次修改记录都会存储这条记录被修改之前的版本

  • 多版本之间串联起来就形成了一条版本链，这样不同时刻启动的事务可以无锁地获得不同版本的数据（普通读）。此时读（普通读）写操作不会阻塞，写操作可以继续写，无非就是多加了一个版本，历史版本记录可供已经启动的事务读取
image.png
  • 实际上 InnoDB 不会真的存储了多个版本的数据，只是借助 undolog 记录每次写操作的反向操作，所以索引上对应的记录只会有一个版本，即最新版本。只不过可以根据 undolog 中的记录反向操作得到数据的历史版本，所以看起来是多个版本
image.png

insert (1，XX) 举例，成功插入之后数据页的记录上不仅存储 ID 1, name XX，还有两个隐藏字段：

  • trx_id：当前事务ID
  • roll_pointer：指向 undolog 的指针
    • undolog 是一个类型为 TRX_UNDO_INSERT_REC 的 log，代表是 insert 生成的，里面存储了主键的长度和值，所以 InnoDB 可以根据 undolog 里的主键的值，找到这条记录，然后把它删除来实现回滚
image.png
  • 事务1提交，然后另一个 ID 为 5 的事务再执行 update NO where id 1
    • 之前 insert 产生的 undolog 没了，insert 的事务提交了之后对应的 undolog 就回收了
    • update 产生的 undolog 不一样，类型为 TRX_UNDO_UPD_EXIST_REC
image.png
  • 事务5提交，然后另一个 ID 为 11 的事务执行 update Yes where id 1
    • update 产生的 undolog 不会马上删除，因为可能有别的事务需要访问之前的版本。这样就串成了一个版本链，可以看到记录本身加上两条 undolog，这条 id 为 1 的记录共有三个版本
image.png

1. readView

readView 用来判断哪个版本对当前事务可见，有四个概念：

  • creator_trx_id：当前事务ID
  • m_ids：生成 readView 时还活跃的事务ID集合，也就是已经启动但是还未提交的事务ID列表
  • min_trx_id：当前活跃ID之中的最小值
  • max_trx_id：生成 readView 时，InnoDB 将分配给下一个事务的 ID 的值（事务 ID 是递增分配的，越后面申请的事务ID越大）

对于可见版本的判断是从最新版本开始沿着版本链逐渐寻找老的版本，如果遇到符合条件的版本就返回

  • 当前数据版本 trx_id == creator_trx_id，说明修改这条数据的事务就是当前事务，可见
  • 当前数据版本 trx_id < min_trx_id，说明修改这条数据的事务在当前事务生成 readView 时已提交，可见
  • 当前数据版本 min_trx_id < trx_id < max_trx_id
    • trx_id 在 m_ids 中，说明修改这条数据的事务此时还未提交，不可见
    • trx_id 不在 m_ids 中，表明事务已经提交，可见
  • 当前数据版本 trx_id >= max_trx_id，说明修改这条数据的事务在当前事务生成 readView 时还未启动，所以不可见（结合事务ID递增来看）

2. 读已提交

假设此时上文的事务1 已经提交，事务5 已经执行，但还未提交，此时有另一个事务在执行 update YY where id 2，也未提交，它的事务 ID 为 6，且也是现在最大的事务 ID

现在又一个查询开启了事务，语句为 select name where id 1，那么这个查询语句：

  • 此时 creator_trx_id 为 0，因为一个事务只有当有修改操作时才会被分配事务 ID
  • 此时 m_ids 为 [5，6]，这两个事务都未提交，为活跃的
  • 此时 min_trx_id，为 5
  • 此时 max_trx_id，为 7，因为最新分配的事务 ID 为 6，那么下一个就是7，事务 ID 是递增分配的

由于查询的是 ID 为 1 的记录，所以先找到 ID 为 1 的这条记录，此时的版本如下：

image.png
  • 此时最新版本的记录上 trx_id 为 5，不比 min_trx_id 小，在 m_ids 之中，表明还是活跃的，未提交，所以不可访问，根据 roll_pointer 找到上一个版本
  • 于是找到了图上的那条 undolog，这条log上面记录的 trx_id 为 1，比 min_trx_id 还小，说明在生成 readView 时已经提交，所以可以访问，因此返回结果 name 为 XX

然后事务 5 提交。此时再次查询 select name where id 1，这时又会生成新的 readView

  • 此时 creator_trx_id 为 0，因为还是没有修改操作
  • 此时 m_ids 为 [6]，因为事务5提交了
  • 此时 min_trx_id，为 6
  • 此时 max_trx_id，为 7，此时没有新的事务申请

同样还是查询的是 ID 为 1 的记录，所以还是先找到 ID 为 1 的这条记录，此时的版本如下（和上面一样，没变）：

image.png
  • 此时最新版本的记录上 trx_id 为 5，比 min_trx_id 小，说明事务已经提交了，是可以访问的，因此返回结果 name 为 NO
  • 这就是读已提交的 MVCC 操作，可以看到一个事务中的两次查询得到了不同的结果，所以也叫不可重复读

3. 可重复读

可重复读和读已提交的 MVCC 判断版本的过程是一模一样的，唯一的差别在生成 readView 上

  • 读已提交每次查询都会重新生成一个新的 readView
  • 可重复读在第一次生成 readView 之后的所有查询都共用同一个 readView。可重复读只会在第一次 select 时候生成一个 readView ，所以一个事务里面不论有几次 select ，其实看到的都是同一个 readView

上面的情况，差别就在第二次执行 select name where id 1，不会生成新的 readView，而是用之前的 readView，所以第二次查询时：

  • m_ids 还是为 [5，6]，虽说事务5 此时已经提交了，但是这个readView是在事务5 提交之前生成的，所以当前还是认为这两个事务都未提交，为活跃的
  • 此时 min_trx_id，为 5

所以在可重复级别下，两次查询得到的 name 都为 XX，所以叫可重复读

19. 没有MVCC怎么办

没有 MVCC 读写操作之间就会冲突

想象一下有一个事务 1 正在执行，此时一个事务 2 修改了记录 A，还未提交事务

  • 事务 1 无法读取最新的记录 A，不然万一事务 2 回滚了，这不就产生脏读了吗？
  • 事务 1 读记录 A 被事务 2 修改之前的数据？但是数据已经被事务 2 改了啊，事务 1 去哪里找之前的数据？

事务 1 只能用锁阻塞等待事务 2 的提交，这种实现叫 LBCC（Lock-Based Concurrent Control）

image.png

有MVCC

  • 事务 2 修改的记录 A，还未提交，但是记录 A 被修改之前的版本还在，此时事务 1 就可以读取之前的版本数据，这样读写之间就不会阻塞啦，所以说MVCC 提高了事务的并发度，提升DB的性能

20. 事务的隔离级别

  1. 读未提交
    • 最低的隔离级别，在该级别下，一个事务可以看到另一个事务尚未提交的未提交数据修改。这可能会导致脏读问题，即读取到其他事务未提交的数据
  2. 读已提交
    • 在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这可以避免脏读问题，但是可能会引发不可重复读问题，即在同一个事务中，相同的查询可能返回不同的结果
  3. 可重复读
    • 在这个级别下，确保在一个事务中的多个查询返回的结果是一致的。这可以避免不可重复读问题，但是可能会引发幻读问题，即在同一个事务中，多次查询可能返回不同数量的行
  4. 串行化
    • 最高的隔离级别，在这个级别下，事务串行执行，即每个事务都会等待前一个事务执行完毕才会开始执行。这可以避免所有的并发问题，但是会大大降低并发性能

21. 默认事务隔离级别，为什么？

可重复读（ Repeatable Read ），即 RR

  • 原因：为了兼容早期 binlog 的 statement 格式问题，如果是使用读已提交、读未提交等隔离级别，使用了 statement 格式的 binlog 会导致主从（备）DB数据不一致问题

为了避免单台故障，使用主从（备）机制：

image.png

主从（备）涉及 binlog 的复制，即从（备）库的数据是通过 binlog 从主库复制过来的

image.png

在早期，MySQL binlog 仅支持 statement 格式，这个格式其实存的就是原先的SQL 语句，使得在读未提交（ru）和读提交（rc）两种隔离级别下会出问题

例如：有两个事务 A 和 B ，以下图的时间顺序执行：

image.png

  • 在读已提交隔离级别且手动提交事务的情况下， 插入的 5 这条记录会被保留

  • 但是由于事务 B 先提交，所以它会先被记录在 binlog 中，这个操作就导致了问题。记录顺序是：

image.png
  • 使得从库同步 binlog 重放时，先执行了 insert 再执行 delete，这就使得从库的数据与主库不一致了！

为了避免这个问题，只能默认可重复读级别。因为这个隔离级别下有间隙锁（Gap Locks）、临键锁（Next-Key Locks），所以事务 B 是无法先提交的，会被事务 A 阻塞。因此 binlog 的记录只能是先 delete 再 insert

22. 脏读、不可重复读、幻读

1. 脏读

事务读到了其他事务未提交的数据

2. 不可重复读

同一个事务内，对于同一条数据前后读取不一致

3. 幻读

  • 针对数据的数量，好像凭空多出了几条或减少了几条
  • eg：当事务 A ，更新全表数据（数据表总数为 20），在此期间事务 B 加了一条数据并提交，此时事务 A 最后提交，B 新增的一条没有被更改，产生了幻觉

23. 锁？

1. 共享锁、排他锁

在 MySQL 中锁可以分为两大类，分别是 shared (S) locks 和 exclusive (X) locks。

  • S锁，共享锁，事务在读取记录时获取 S 锁，它允许多个事务同时获取 S 锁，互相之间不会冲突
    • SELECT ... LOCK IN SHARE MODE;
  • X锁，独占锁（排他锁），事务在修改记录时获取 X 锁，且只允许一个事务获取 X 锁，其它事务需要阻塞等待
    • SELECT ... FOR UPDATE;
冲突SX
S不冲突冲突
X冲突冲突

MyIASM 引擎仅支持表锁，而 Innodb 即支持表锁也支持行锁

  • LOCK TABLES yes READ;：对 yes 表上 S 锁
  • LOCK TABLES yes WRITE;：对 yes 表上 X 锁

不过一般情况下，不会使用表锁，因为平日的 UPDATE 、SELECT 要用也是用行锁了，不可能用粒度粗的表锁，不然性能太低

唯一能想到用上表锁的就是 DDL 语句了，比如 ALTER TABLE 时，应该锁定整个表，防止查询和修改，但是这个 server 已经提供了一个叫 MDL 的锁，即 Metadata Locks（元数据锁）

2. 元数据锁

  1. 读锁（MDL_SHARED）
    • 当一个事务需要读取表的元数据时（SELECT 操作），会获取读锁。 多个事务可以同时持有读锁，不会互相阻塞
  2. 写锁（MDL_EXCLUSIVE）
    • 当一个事务需要修改表的元数据时（ALTER TABLE 操作），会获取写锁。 写锁会阻塞其他任何读锁和写锁，确保独占访问

作用如下：

  1. 防止并发的 DDL 操作、DML 操作冲突
    • 当一个事务对表进行结构性更改（ALTER TABLE），元数据锁（写锁）会阻止其他事务对该表进行操作，直到结构更改完成
    • 当一个事务对表进行数据操作（SELECT、INSERT、UPDATE、DELETE），元数据锁（读锁）会阻止其他事务对该表进行结构性更改
  2. 保护元数据一致性
    • 确保在执行 DDL 操作（如 CREATE TABLE、DROP TABLE、ALTER TABLE）时，元数据不会被其他事务同时修改

3. 意向锁

因此 IS 和 IX 作用：在上表级锁时，可以快速判断是否可以上锁，而不需要遍历表中的所有记录

业务上真用到了表锁，那么表锁和行锁之间肯定会冲突，当 InnoDB 加表锁时，如何判断表里面是否已经有行锁？难道得一条记录一条记录遍历过去找吗？显然效率太低了！

所以有了意向锁（Intention Locks）

  • IS（Intention Shared Lock），共享意向锁
  • IX（Intention Exclusive Lock），独占意向锁

这两个锁是表级别的锁，当需要对表中的某条记录上 S 锁时，先在表上加个 IS 锁，表明此时表内有 S 锁。当需要对表中的某条记录上 X 锁时，先在表上加个 IX 锁，表明此时表内有 X 锁

  • 这样操作之后，如果要加表锁，就不需要遍历所有记录去找了，直接看看表上面有没有 IS 和 IX 锁
  • 所以 IS 和 IX 互相之间是不会冲突的，因为它们的作用只是打个标记
冲突SXISIX
S不冲突冲突不冲突冲突
X冲突冲突冲突冲突
IS不冲突冲突不冲突不冲突
IX冲突冲突不冲突不冲突

至此，已经理解了共享锁、独占锁、表锁相关的知识，接下来再来看看 MySQL 中的行锁有哪几种

4. 行锁

1. 记录锁

记录锁（Record Locks）锁住当前的记录，作用到索引上的。innodb 是肯定有索引的，即使没有主键也会创建隐藏的聚簇索引，所以记录锁总是锁定索引记录

# name = xx 这条记录就被锁定了，其他事务无法插入、删除、修改 name = xx 的记录
SELECT * FROM yes WHERE name = 'xx' FOR UPDATE;

# 此时会被阻塞，这个很好理解
insert into yes (name) values ('xx');

# 被阻塞吗？
insert into yes (name) values ('aa')
# name 没有索引，干脆都锁。事务 C 会被阻塞
# name 有索引，则不会被阻塞

2. 间隙锁和临键锁

间隙锁（Gap Locks）给间隙（此时还未存在的记录）加上锁，防止其他事务插入数据到间隙中。预防幻读的出现！

  • 间隙锁之间不会冲突，即使两个间隙锁要锁住相同的间隙也没有关系，因为它们的目的是一致的
  • 间隙锁可以显式禁用，在可重复读时生效，为 READ COMMITTED，就会禁用，此时，间隙锁对于搜索和索引扫描是禁用的，仅用于外键约束检查和重复键检查

eg：有 1、3、5、10 这四条记录，数据页中还有两条虚拟的记录，分别是 Infimum 和 Supremum

image.png

把 3 和 5 之间的间隙锁了，此时要插入 id = 4 的记录，被间隙锁给阻塞，这样就避免了幻读的产生！也就实现了锁定未插入的记录的需求！

临键锁（Next-Key Locks）就是《记录锁 + 间隙锁》，是一个前开后闭的区间 (3, 5]

3. 插入意向锁

插入意向锁（Insert Intention Locks）也是一类间隙锁，但是它不是锁定间隙，而是等待某个间隙释放

  • eg：id = 4 事务 ，由于被间隙锁给阻塞了，所以事务会生成一个插入意向锁，表明等待这个间隙锁的释放
  • 插入意向锁之间没有冲突、不会阻塞，因为它们的目的也是只等待这个间隙被释放

锁其实就是内存里面的一个结构

  • 每个事务为某个记录或者间隙上锁就是创建一个锁对象来争抢资源
  • 如果某个事务没有抢到资源，那也会生成一个锁对象，只是状态是等待的
  • 而当拥有资源的事务释放锁之后，就会寻找正在等待当前资源的锁结构，然后选一个让它获得资源并唤醒对应的事务使之得以执行
image.png
  • 间隙锁的事务在释放间隙锁时，才能得以找到那些等待插入的事务，然后进行唤醒，而由锁的类型也可以得知是插入意向锁，之间不需要阻塞，所以可以一起执行插入
image.png

5. Auto-Inc Lock

Auto-Inc Lock 是一个特殊的表级锁，用于自增列插入数据时使用。 在插入一条数据时，需要在表上加个 Auto-Inc Lock，然后为自增列分配递增的值，在语句插入结束之后，再释放 Auto-Inc Lock

  • 在 MySQL 5.1.22 版本之后，又弄了个互斥量来进行自增减的累加。互斥量的性能高于 Auto-Inc Lock，因为 Auto-Inc Lock 是语句插入完毕之后才释放锁，而互斥量是在语句插入时，获得递增值之后，就可以释放锁，所以性能更好
  • 考虑主从的情况，由于并发插入的情况，基于 statement -based binlog 复制时，自增的值顺序无法把控，可能会导致主从数据不一致。

所以 MySQL 有个 innodb_autoinc_lock_mode 配置，一共有三个值：

  • 0：只用 Auto-Inc Lock
  • 1（默认值）：对于插入前已知插入行数的插入，用互斥量，对于插入前不知道具体插入数的插入，用 Auto-Inc Lock，这样即使基于 statement -based binlog 复制也是安全的
  • 2：只用互斥量

6. 谓词锁

谓词锁（Predicate Locks），比较少见，了解下就好

  • 背景：InnoDB 是支持空间数据的，所以有空间索引，为了处理涉及空间索引操作的锁定，next-key locking 不好使，因为多维数据中没有绝对排序的概念，因此不清楚 “下一个” key 在哪
  • 为了支持具有空间索引的表的隔离级别，InnoDB 使用谓词锁
  • 空间索引包含最小边界矩形（MBR）值，因此 InnodB 通过在用于查询的 MBR 值上设置谓词锁定，使得 InnoDB 在索引上执行一致性读， 其他事务无法插入、修改与查询条件匹配的行

24. 乐观锁、悲观锁区别

1. 乐观锁

认为每次执行都不会发生冲突，非常乐观。仅需每次进行提交操作时利用标识的对比，确认其他操作没修改过便可提交

  • 标识通常使用一个版本号字段，然后使用 CAS 操作进行比较

场景：

  • 适合并发冲突少，读多写少的场景，不用通过加锁只需通过比较字段版本号（或时间戳）是否发生改变的形式，无锁操作，吞吐量较高。

2. 悲观锁

认为每次操作都会发生冲突，非常悲观。它会在任何可能发生冲突的地方进行加锁，其他操作想修改都需要等它执行完后释放锁，再通过争抢到锁而进行操作

场景：

  • 适合并发冲突多，写多读少的场景。通过每次加锁的形式来确保数据的安全性，吞吐量较低

25. 死锁怎么解决

死锁的主要原因是资源的循环依赖

  • 像 MyISAM 引擎只有表锁，基本上不会产生死锁，而 InnoDB 有行锁，锁的粒度比较低，因此可能会产生死锁
  • 不过 MySQL InnoDB 有一个自动检测的功能（innodb_deadlock_detect），如果发生死锁会回退事务从而释放锁，也有锁等待超时的参数（innodb_lock_wait_timeout），当获取锁的等待时间超过阈值时，就释放锁进行回滚

如果线上遇到死锁的情况，可以通过 SHOW ENGINE INNODB STATUS 来获取死锁的日志信息，从而定位到死锁发生的原因。

  • 死锁日志，参考学习，有个大致印象即可：
------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)









 










 











  • 上半部分在解释事务 1 即 (1) TRANSACTION，它执行delete from test where a = 2 在申请索引 a 的 X 锁（index a of table ... lock_mode X waiting）
  • 下半部分在解释事务 2 即 (2) TRANSACTION，它持有索引 a 的 X 锁，又因为 insert into test (id,a) values (10,2) 这个插入语句在申请 S 锁（表中的 a 建立了唯一索引，因此需要 S 锁来进行重复 key 判断）
  • 所以事务 1 排着队等事务 2 释放 X 锁，而事务 2 又需要 S 锁，申请 S 锁时发现等锁的队伍前还有个 X 锁，这样一来就形成了相互依赖

通过日志分析就能知晓死锁的原因，然后通过一定的修改来避免死锁的产生或降低死锁的概率，常见手段：

  1. 避免大事务。大事务占据锁的时间长，将大事务拆分成多个小事务快速释放锁，可降低死锁产生的概率和避免冲突
  2. 调整申请锁的顺序。在更新数据时要保证获得足够的锁，举个例子：先获取影响范围大的锁，比如说修改操作，先将排他锁获取到，再获取共享锁。或固定顺序访问数据，这样也能避免死锁的情况
  3. 更改DB隔离级别。可重复读比读已提交多了间隙锁和临键锁，利用读已提交替换之可降低死锁的情况
  4. 合理建立索引，减少加锁范围。如果命中索引，则会锁对应的行，不然就是全表行都加锁，这样冲突大，死锁的概率就高了
  5. 开启死锁检测，适当调整锁等待时长

1. 死锁案例

-- 事务1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- 事务2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- 如果这两个事务几乎同时执行，可能会发生死锁

死锁发生过程：

  1. 事务1开始，并锁定账户1的记录
  2. 事务2开始，并锁定账户2的记录
  3. 事务1尝试锁定账户2的记录，但由于事务2已经持有该记录的锁，事务1被阻塞
  4. 同时，事务2尝试锁定账户1的记录，但由于事务1已经持有该记录的锁，事务2也被阻塞
  5. 这样，两个事务相互等待对方释放锁，形成了死锁

26. explain

  1. id
    • 查询中每个 SELECT 子句的标识符。简单查询的 id 通常为 1，复杂查询（如包含子查询或 UNION）的 id 会有多个
  2. select_type
    • 描述查询的类型。eg：简单查询显示为 SIMPLE，子查询显示为 SUBQUERY，UNION 中的第二个和后续查询显示为 UNION
  3. table
    • 表名称
  4. partitions
    • 表示查询涉及到的分区。使用分区表
  5. type（重点记忆）表示访问的类型
    1. system：查询的表只有一行（系统表）
    2. const：查询的表最多只有一行匹配结果。查询条件是主键或唯一索引，并且是常量比较
      EXPLAIN SELECT * FROM employees WHERE employee_id = 12345;
    3. eq_ref：对于每个来自前一张表的行，MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况
      EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
    4. ref：使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的
      EXPLAIN SELECT * FROM employees WHERE department_id = 5;
    5. range：会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中（BETWEEN、>, <, >=, <=）
      EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
    6. index：扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引
      EXPLAIN SELECT name FROM employees;
    7. all（性能最差）：需要扫描表中的所有行，即全表扫描
      EXPLAIN SELECT * FROM employees;
  6. possible_keys
    • 查询可能使用的索引列表
  7. key
    • 实际使用索引的长度。如果没有使用索引，该字段显示为 NULL
  8. key_len
    • 使用的索引的长度。该值是根据索引的定义和查询条件计算的
  9. rows
    • 估计找到所需行，需要读取的行数。该值是一个估计值，不是精确值
  10. filtered
    • 显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好
  11. Extra：额外信息
    • Using index：使用覆盖索引
    • Using where：使用 WHERE 条件进行过滤
    • Using temporary：使用临时表
    • Using filesort：需要额外的排序步骤

1. demo

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX (department_id)
);

EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;
idselect_typetypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLErefdepartment_iddepartment_id4const500020.00Using where; Using filesort
  • type： 为 ref，表示使用了 department_id 索引，这是个非唯一索引
  • key ： 为 department_id 这个索引
  • rows： 为 5000，表示扫描了 5000 行匹配的 department_id = 5 的条件
  • Extra： 看出在应用 WHERE 条件后，还需要进行文件排序来满足 ORDER BY 子句

尽管查询使用了索引，但由于索引不完全覆盖查询的条件和排序，查询需要进行额外的文件排序。这可能会导致性能瓶颈，特别是在结果集较大时

# 复合索引可以使查询在扫描 `department_id` 列时，同时按 `salary` 列排序，避免额外的文件排序
CREATE INDEX idx_department_salary ON employees (department_id, salary);
idselect_typetypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLErangeidx_department_salaryidx_department_salary5NULL500100.00Using where
  • type: range，表示使用范围扫描，这是个相对高效的访问类型
  • key: idx_department_salary，表示实际使用了复合索引
  • rows: 500，估计读取的行数减少了，因为索引更精确地覆盖了查询条件
  • Extra: 仅显示 Using where，不再需要文件排序，因为索引已经覆盖了排序需求

27. count()

count(*)、count(1)、count(字段名) 分别有什么区别？

都是用来统计行数的聚合函数，但有些许区别：

  • count(*)：会统计表中所有行的数量，包括 null 值（不会忽略任何一行数据）。由于只是计算行数，不需要对具体的列进行处理，因此性能通常较高
  • count(1)、count(*)：几乎没差别，也会统计表中所有行的数量，包括 null 值
  • count(字段名)：只会统计指定字段不为 null 的行数

效率上：

  • count(1)、count(*) 效率一致，官网：There is no performance difference. 没有差异的！
image.png
  • count(字段)：全表扫描，还需要判断字段是否是 null 值，因此理论上会比 count(1) 和 count(*) 慢
  • 在 MyISAM 中，由于只有表锁，因此它把每张表的总数单独记录维护（表锁使得对表的修改是串行，因此能维护总数），所以 count(*) 非常快，因为等于直接返回一个字段。当然，前提是不需要条件过滤而是直接返回整表数据
  • 而 InnoDB 由于支持行锁，所以会有很多并发来修改表的数据，因此无法维护记录总数，但是 InnoDB 对count(*)和count(1)也做了一定的优化
    • count 需要扫描全表，如果扫的是主键索引，由于主键索引保存的是整行记录，占据的空间和内存都比较大，此时选择二级索引扫描则成本会更低
    • 因此 InnoDB 会评估这个成本选择合适的索引扫描。前提是不能有对应的条件过滤等功能

28. int(11)的11

在 MySQL 中，int(11) 中的 11 不表示整数类型的存储大小或范围，而是表示显示宽度，用于控制显示结果的一个参数

  • 并不会影响数据的存储大小！int 类型占据的就是 4 个字节的固定大小
  • 字段定义为 int(11)，而存储的值是 30
    • 不使用 ZEROFILL 时，显示结果是 30
    • 使用 ZEROFILL 时，显示结果是 0000000030

为了在查询结果中保持一致的显示格式，方便阅读和理解，但实际业务上基本用不到

29. varchar、char区别

  • char(n)：固定长度的字符串。即使存储的字符串长度小于定义的长度，MySQL 也会在字符串的末尾填充空格以达到指定长度（如果 char 类型的字符串后面有空格的话，innodb 会忽略）
  • varchar(n)：可变长度的字符串。存储的字符串长度与实际数据长度相等，并且在存储数据时会额外增加 1 到 2 个字节（字符长度超过 255，则使用两个字节）用于存储字符串的长度信息
特点charvarchar
存储方式定长字符串（字符串长度小于定义的长度，会使用空格进行填充）变长字符串
存储空间始终占用固定长度空间只占用实际需要的存储空间
性能影响始终占用固定长度的存储空间，因此在存储时可能会浪费一些空间 (不需要记录额外长度信息，在某些情况下可能更快)只占用实际需要的存储空间，因此可以节省存储空间 （需要记录额外长度信息，占据1~2个字节），在某些情况下可能稍微影响性能）
适用场景适合存储固定且短的字符串适合存储变化或较长的字符串

1. order by

select a,b,c from t1 where a = '面试鸭' order by b;
# a b c 总长度比较长，sort_buffer 可能放不下，就会使用 双路排序
# select 字段长度少，那么就可以使用 单路排序
image.png
image.png

计算 a、b、c 长度依据的就是 varchar(n) 中的 n，所以如果 n 设置很大，虽然占用空间是动态的，但是会隐性影响排序的性能

30. 怎么做SQL调优

观察慢 SQL，然后通过 explain 分析查询语句的执行计划，识别性能瓶颈，优化查询语句

  • 合理设计索引
  • 利用联合索引进行覆盖索引优化，减少回表
  • 通过缓存来优化。一些变化少或者访问频繁的数据设置到缓存中，减轻DB的压力，提升查询的效率
  • 通过业务来优化。eg：少展示一些不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询等

1. 慢SQL

MySQL 自带的日志记录，默认关闭，通过 set global slow_query_log = 'ON' 即可开启

  • 通过 show variables like '%slow_query_log%' 即可查询当前慢日志是否开启，以及存储的路径
  • 通过 set global long_query_time = 3 即可设置慢 SQL 的阈值，3 就是 3 秒，当一个 SQL 执行的时间操作 3 秒，就会被记录到慢日志中

2. explain

具体 explain 的分析

31. Mysql避免单点故障

1. 主备

备机是不干活的，也就是不对外提供服务，只是默默地在同步主机的数据，然后等着某一天主机挂了之后，它取而代之！

img

切换的话主要有两种方式：

  • 人工切换，得知主机挂了之后手动把备机切成主机，缺点就是慢
  • 利用 keepalived 或自己写个脚本来作监控，然后自动切换

2. 主从

从机对外提供服务的，一般而言主从就是读写分离，写请求指派到主机，读请求指派到从机

img

3. 主主

img

一般情况下都不会有主主的架构

32. MySQL读写分离

1. 什么是读写分离

  1. 读写分离：读操作、写操作从以前的一台服务器上剥离开来。本质上是因为访问量太大，主库的压力过大，单机DB无法支撑并发读写。然后一般而言读的次数远高于写，因此将读操作分发到从库上
  2. 主库不建查询的索引，从库建查询的索引。因为索引是需要维护的，eg：你插入一条数据，不仅要在聚簇索引上面插入，对应的二级索引也得插入，修改也是一样的。所以将读操作分到从库了之后，可以在主库把查询要用的索引删了，减少写操作对主库的影响

2. 代码封装

就是搞了个代理类，对外暴露正常的读写接口，里面封装了逻辑，将读操作指向从库的数据源，写操作指向主库的数据源

  • 优点：简单，并且可以根据业务定制化变化，随心所欲
  • 缺点：如果DB宕机了，发生主从切换了之后，就得修改配置重启。如果系统是多语言的话，需要为每个语言都实现一个中间层代码，重复开发

3. 使用中间件

一般而言是独立部署的系统，客户端与这个中间件的交互是通过 SQL 协议的

640.png

在客户端看来连接的就是一个DB，通过 SQL 协议交互也可以屏蔽多语言的差异

  • 缺点：整体架构多了一个系统需要维护，并且可能成为性能瓶颈，毕竟交互都需要经过它中转
  • 常见的开源DB中间件有：官方的 MySQL-Proxy、360的Atlas、Mycat 等

33. 主从同步机制

主从同步主要依赖 binlog，MySQL 默认是异步复制，具体流程：

主库：

  • 接受到提交事务请求
  • 更新数据
  • 将数据写到binlog中
  • 给客户端响应
  • 推送binlog到从库中

从库：

  • 由 I/O 线程将同步过来的 binlog 写入到 relay log 中
  • 由 SQL 线程从 relay log 重放事件，更新数据
  • 给主库返回响应
650.png
  • 异步复制。数据丢失风险，eg：数据还未同步到从库，主库就给客户端响应，然后主库挂了，此时从库晋升为主库的话数据是缺失的
  • 同步复制。主库需要将 binlog 复制到所有从库，等所有从库响应了之后才会给客户端响应，这样的话性能很差，一般不会选择同步复制
  • 半同步复制。MySQL 5.7 之后搞了个半同步复制，有个参数可以选择“成功同步几个从库就返回响应。”

34. 主从同步延迟

首先需要明确 延迟是必然存在的，无论怎么优化都无法避免延迟的存在

常见解决方式：

  • 二次查询。如果从库查不到数据，则再去主库查一遍，由 API 封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了
  • 强制将写之后立马读的操作转移到主库上。这种属于代码写死了，eg：一些写入之后立马查询的操作，就绑定在一起，写死都走主库。不推荐，比较死板
  • 关键业务读写都走主库，非关键还是读写分离。eg：上面举例的用户注册这种，可以读写主库，这样就不会有登陆报该用户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接口
  • 使用缓存。主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟的问题，不过又引入了缓存数据一致性的问题

35. 分库分表理解

  • 随着用户量激增和时间堆砌，存在DB里面的数据越来越多，此时DB就会产生瓶颈，出现资源报警、查询慢等场景
  • 首先单机DB所能承载的连接数、I/O及网络的吞吐等都是有限的，所以当并发量上来之后，DB就渐渐顶不住了
  • 如果单表的数据量过大，查询性能也会下降。因为数据越多 B+ 树就越高，树越高则查询 I/O 的次数就越多，性能也就越差
660.png
  • 分库： 存在一个DB实例里的数据拆分成多个DB实例，部署在不同的服务器中
    • 为了解决服务器资源受单机限制，顶不住高并发访问的问题，把请求分配到多台服务器上，降低服务器压力
  • 分表： 存在一张表里面的数据拆分成多张表
    • 为了解决由于单张表数据量过大，而导致查询慢的问题。大致三、四千万行数据就得拆分，不过具体还是得看每一行的数据量大小，有些字段都很小的可能支持更多行数，有些字段大的可能一千万就顶不住了

1. 垂直分表

  1. 将原本的一张表切割成多张表
    • eg：有张 student 表存储着学生家庭背景、学生入学宣言（500字）、学生信息，现在将其进行垂直分表，可以分为 学生基本信息表、学生入学宣言表、学生家庭背景表，将其进行切割
  2. 一般是将不常用的字段单独放在一张表、将大字段分一张表、把经常需要同时查出来的信息放一张表。这样做可以冷数据和热数据分开提高查询效率

2. 垂直分库

  1. 把一个DB里面的多个表，按照功能，分成多个DB存放
    • eg：一个DB有很多张表，用户表、商品表、订单表等，那么可以根据功能属性进行垂直分库，将用户表等信息放到存放用户信息的DB，将商品表、订单表存放到与商品订单有关的商品订单库
  2. 好处：将数据负载分散到不同的DB上，从而提高系统的性能和扩展性、降低单一DB的复杂度

3. 水平分表

  1. 同一个DB中，有几个相同表，里面的数据是不一样的，但表结构是一样的，数据按照固定的规则选择数据表存放
  2. 提高了读写性能，减少了单表压力、可弹性增加存储容量只需要增加一个表就行

4. 水平分库

  1. 相同的表结构复制一份分到另一个库中，每个库的表结构是一样的，但是数据是不一样的
  2. 大数量的情况下提高读写性能，因为减少了单一DB的读写压力
  3. 能提高存储容量。可以通过增加或减少DB进行弹性伸缩
  4. 提高容错性。当一个DB故障了，别的DB还能正常运行，只影响小部分数据查询

36. 怎么分库、分表

1. 分库

  • 一般分库都是按照业务划分的。eg：订单库、用户库等
  • 有时候会针对一些特殊的库再作切分，eg：一些活动相关的库都做了拆分。因为做活动时并发可能会比较高，怕影响现有的核心业务，所以即使有关联，也会单独做拆分
670.png

2. 分表

垂直分表：把一些不常用的大字段剥离出去

  • 一个数据页的空间是有限的，把一些无用的数据拆分出去，一页就能存放更多行的数据。内存存放更多有用的数据，就减少了磁盘的访问次数，性能就得到提升
690.png

水平分表

  • 一张表内的数据太多了，B+ 树就越高，访问的性能就差
700.png

37. 分库分表问题

1. 事务问题

使用关系型DB，有很大原因在于它保证事务的完整性

  • 分库之后单机事务就用不上了，必须使用分布式事务来解决，而分布式事务相对而言就比较重了，而且大部分的分布式事务只能保证最终一致性，业务上会存在数据不一致的场景

2. JOIN问题

跨库了之后无法使用 JOIN 连表查询

  • 在业务代码中进行关联，先把一个表的数据查出来，得到的结果再去查另一张表，然后利用代码来关联得到最终结果
  • 反范式，适当的冗余一些字段
  • 通过宽表的形式查询。eg：将数据全量存储至 ES 中，利用 ES 来查询数据

3. 全局ID唯一性问题

  • 单库单表直接使用DB的自增 ID 即可
  • 分库分表，利用雪花算法或其他全局唯一 ID 发号器来生成唯一主键

4. 排序问题

  • 单表直接通过 order by 进行排序即可
  • 利用分库分表中间件的能力进行汇总排序
  • 在业务代码中排序
  • 利用 ES 存储全量数据排序查询

5. count问题

  • 单表可以直接 count
  • 多表 count 然后业务代码中累加
  • 利用 ES

38. 数据从磁盘读取的吗

MySQL 获取数据可以从缓存中读取，也可以从从磁盘中读取

之所以设计了一个缓存机制是为了优化读取的性能，减少磁盘 I/O

1. 优先读取缓存中的数据

  1. 查询缓存（MySQL 8.0 已废除）：在 MySQL 8.0 之前，MySQL 提供了查询缓存功能，用于缓存查询结果。如果相同的查询（同一个查询 SQL）再次执行，并且表没有发生任何变化（这个条件比较苛刻，所以后续废除了），则 MySQL 可以直接从查询缓存中返回结果，而无需重新执行查询。具体的实现类似用一个 map 存储了之前的结果，key 是 SQL，value 为结果，SQL 执行时，先去这个 map 看看通过 key 是否能找到值，如果找到则直接返回
  2. InnoDB 缓冲池（buffer pool）：这是 InnoDB 存储引擎的核心缓存组件。缓冲池缓存了数据页、索引页和其他相关信息。查询数据时，MySQL 首先在缓冲池中查找，如果找到则直接返回数据，否则从磁盘读取数据页并将其缓存到缓冲池中

查询缓存和 buffer pool 大致的结构关系：

image.png

2. 其次从磁盘读取

当数据不在内存缓存中时，MySQL 需要从磁盘读取数据。以页为单位从磁盘获取数据，这里还有个额外的知识点，因为以页为单位，使得顺序遍历数据的速度更快，因为后面的数据已经被加载到缓存中了！ 这也符合空间局部性

1. 数据页、索引页

InnoDB 存储引擎将表数据和索引以页为单位存储，每页通常为 16KB。当需要读取某条记录时，MySQL 会加载包含该记录的整个数据页到缓冲池中，从而减少频繁的磁盘 I/O 操作

  • MySQL 是以页为单位加载数据的，而不是记录行为单位

3. buffer pool

buffer pool 内存中的一块缓冲池，用来缓存表和索引的数据。缓冲池维护的是页数据，即使只想从磁盘中获取一条数据，但是 innodb 也会加载一页的数据到缓冲池中，一页默认是 16k

  • 直接修改内存中的数据，然后到一定时机才会将这些脏数据刷到磁盘上（redolog保证数据）
  • 内存放不下全部的DB数据，那说明缓冲池需要有淘汰机制，淘汰那些不常被访问的数据页

innodb 的实现并不是朴素的 LRU（最近最少使用的页面将被淘汰），而是一种变型的 LRU

image.png

buffer pool 分为了老年代（old sublist）和新生代（new sublist）

  • 老年代默认占 3/8，当然，可以通过 innodb_old_blocks_pct 来调整比例
  • 当有新页面加入 buffer pool 时，插入的位置是老年代的头部，同时新页面在 1s 内再次被访问的话，不会移到新生代，等 1s 后，如果该页面再次被访问才会被移动到新生代
  • 正常 LRU 实现是新页面插入到头部，且老页面只要被访问到就会被移动到头部，这样保证最近访问的数据都留存在头部，淘汰的只会是尾部的数据

那为什么要实现这样改造的 LRU 呢？

  • innodb 有预读机制，读取连续的多个页面后，innodb 认为后面的数据也会被读取，于是异步将这些数据载入 buffer pool 中，但是这只是一个预判，也就是说预读的页面不一定会被访问。所以如果直接将新页面都加到新生代，可能会污染热点数据，但是如果新页面是加到老年代头部，就没有这个问题
  • 同时大量数据的访问（eg：不带 where 条件的 select 或者 mysqldump 的操作等），都会导致同等数量的数据页被淘汰，如果简单加到新生代的话，可能会一次性把大量热点数据淘汰了，所以新页面加到老年代头部就没这个问题

4. 1s机制是为什么

  • 为了处理大量数据访问的情况，因为基本上大数据扫描之后，可能立马又再次访问，正常这时候需要把页移到新生代了，但等这波操作结束了，后面还有可能再也没有请求访问这些页面了，但因为这波扫描把热点数据都错误淘汰了
  • 搞个时间窗口，新页面在 1s 内的访问，并不会将其移到新生代，这样就不会淘汰热点数据了，然后 1s 后如果这个页面再次被访问，才会被移到新生代，这次访问大概率已经是别的业务请求，也说明这个数据确实可能是热点数据

经过这两个改造， innodb 就解决了预读失效和一次性大批量数据访问的问题

至此，对 buffer pool 的了解就差不多了

39. Doublewrite Buffer

innodb 默认一页是 16K，而操作系统 Linux 内存页是 4K，那么一个 innodb 页对应 4 个系统页

720.png

所以 innodb 的一页数据要刷盘等于需要写四个系统页，假设 innodb一页数据落盘时，只写了一个系统页就断电了，那 innodb 一页数据就坏了，这就完了，不好恢复不了

730.png

即产生了部分页面写问题，因为写 innodb 的一页无法保证原子性，所以引入了 Doublewrite Buffer

  • innodb 要将数据落盘时，先将页数据拷贝到 Doublewrite Buffer 中，然后 Doublewrite Buffer 再刷盘到 Doublewrite Buffer Files，这时候等于数据已经落盘备份了
  • 最后再将数据页刷盘到本该到的文件上

数据是写了两次磁盘，所以这东西叫 double write

这样操作就是先找个地方暂存这次刷盘的完整数据，如果出现断电这种情况导致的部分页面写而损坏原先的完整页，可以从 Doublewrite Buffer Files 恢复数据

  • 虽然是两次写，性能的话也不会低太多，因此数据拷贝到 Doublewrite Buffer 是内存拷贝操作，然后写到 Doublewrite Buffer Files 也是批量写，且是顺序写盘，所以对整体而已，性能损失不会太多
  • 有了这个 buffer，在崩溃恢复时，如果发现页损坏，就可以从 Doublewrite Buffer Files 里面找到页副本，然后恢复即可

工作原理

  1. 写入 Doublewrite Buffer
    • 当 InnoDB 需要将脏页（dirty page，即已被修改但尚未写入磁盘的页）写入磁盘时，首先将这些数据页写入到 Doublewrite Buffer 和 Doublewrite Buffer File 中
  2. 写入实际数据文件
    • 将数据页写入 Doublewrite Buffer 和落盘后，InnoDB 将这些数据页从 Doublewrite Buffer 写入到实际的数据文件中（如 .ibd 文件）
  3. 如果发生部分写的情况：
    • 数据页写入一半断电了，在崩溃恢复时，InnoDB 会检查 Doublewrite Buffer File 中的数据页。从 Doublewrite Buffer File 中将完整的页重新写入实际数据文件，修复受影响的页

Doublewrite Buffer 通常在系统表空间文件（ibdata1）中，分为两个 1MB 的区域，共 2MB，每个区域可存储 64 个 16KB 的页

40. Log Buffer有了解过吗

官网图：

710.png

redo log 重做日志，保证崩溃恢复数据的正确性，innodb 写数据时是先写日志，再写磁盘数据，即 WAL (Write-Ahead Logging)，把数据的随机写入转换成日志的顺序写

  • 即使是顺序写 log ，每次都调用 write 或 fsync 也是有开销的，毕竟也是系统调用，涉及上下文切换
  • Log Buffer 来缓存 redo log 的写入
  • 即写 redo log 先写到 Log Buffer 中，等一定时机再写到 redo log 文件里

每次事务可能涉及多个更改，这样就会产生多条 redo log，这时会都先写到 Log Buffer 中

  • 等事务提交时，一起将这些 redo log 写到文件里
  • 当 Log Buffer 超过总量的一半（默认总量是 16mb），也会将数据刷到 redo log 文件中
  • 后台线程，每隔 1s 就会将 Log Buffer 刷到 redo log 文件中

Log Buffer 其实就是一个写优化操作，把多次 write 优化成一次 write，一次处理多数据，减少系统调用。innodb_flush_log_at_trx_commit 来控制写盘时机

  • 当值为 0，提交事务不会刷盘到 redo log，需要等每隔一秒的后台线程，将 log buffer 写到操作系统的 cache，并调用 fsync落盘，性能最好，但是可能会丢 1s 数据
  • 当值为 1，提交事务会将 log buffer 写到操作系统的 cache，并调用 fsync 落盘，保证数据正确，性能最差，默认配置
  • 当值为 2，提交事务会将 log buffer 写到操作系统的 cache，但不调用 fsync，而是等每隔 1s 调用 fsync 落盘，性能折中，如果DB挂了没事，如果服务器宕机了，会丢 1s 数据

41. 不推荐多表Join

多表往往指的是超过三张表才是多表，正常两个表 join 是没问题的！（但是也需要评估下量级和是否命中索引）

  • 阿里的 Java 规范手册里也有一句话：“超过三个表禁止使用 Join”
  • 其实数据量小都无所谓。但当数据量大时，影响就被放大了。如果让DB来承担这个复杂的关联操作，需要对联接的每个表进行扫描、匹配和组合，消耗大量的 CPU 和内存资源。让复杂的关联操作占用了大量的DB资源，会影响其他查询修改操作
  • DB往往是系统的弱点，很多情况下性能瓶颈都在DB，因此尽量避免把压力放在DB上
  • Join 时，需要关注被驱动表的查询是否能命中索引，不然就会导致全表扫描
  • 并且尽量让小表做驱动表，因为驱动表需要全表扫描，而被驱动表是通过索引查询的

42. 深度分页解决思路

深度分页是指数据量很大时，按照分页访问后面的数据（limit 99999990，10），这会使得DB扫描前面的 99999990 条数据，才能得到最终的 10 条数据，大批量的扫描数据会增加DB的负载，影响性能

  1. 子查询
    • name 有索引的情况下，这样的查询直接扫描 name 的二级索引，二级索引的数据量少，且在子查询中能直接得到 id 不需要回表。将子查询得到的 id 再去主键索引查询，速度很快，数据量也小
    • 如果直接扫描主键索引的话，数据量就比较大，因为主键索引包含全部的数据
    • Join 本质上是一样的
select *
from mianshiya
where name = 'yupi'
limit 99999990, 10;

select *
from mianshiya
where name = 'yupi'
  and id >
      (select id from mianshiya where name = 'yupi' order by id limit 99999990, 1)
order by id
limit 10;

select *
from mianshiya
         inner join
     (select id from mianshiya where name = 'yupi' order by id limit 99999990, 10)
         as mianshiya1 on mianshiya.id = mianshiya1.id
  1. 记录 id
    • 每次分页都返回当前的最大 id ，然后下次查询时，带上这个 id，就可以利用 id > maxid 过滤了
    • 这种查询仅适合连续查询的情况，如果跳页的话就不生效了
  2. elasticsearch
    • 可以考虑用搜索引擎来解决这个问题，不过 es 也会有深度分页的问题

43. 如何监控慢SQL

MySQL 自带的 slow_query_log 来监控慢 SQL，MySQL 提供的一个日志功能，用于记录执行时间超过特定阈值的 SQL 语句

  • my.cnf、my.ini 中添加或修改以下配置项：
[mysqld]
slow_query_log = 1                    					# 启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  	# 指定慢查询日志文件路径
long_query_time = 2.0                 					# 设置慢查询的阈值时间（单位：秒）
log_queries_not_using_indexes = 1     					# 记录未使用索引的查询

通过 SQL 命令动态设置：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2.0;
SET GLOBAL log_queries_not_using_indexes = 'ON';

44. Delete、Drop、Truncate

从性能来看，drop > truncate > delete

  1. Delete 用于删除行数据，但保留表结构和相关的对象

    • 本质上这个删除其实就是给数据行打个标记，并不实时删除，因此 delete 之后，空间的大小不会变化
    • delete 操作会生成 binlog、redolog 和 undolog，所以如果删除全表使用 delete 的话，性能会比较差！ 但是它可以回滚

  2. Truncate 只删除数据，不会删除表结构和索引等其他结构

    • Truncate 会对整张表的数据进行删除，且不会记录回滚等日志，所以它无法被回滚
    • 主键字段是自增的，使用 Truncate 删除后自增重新从 1 开始

  3. Drop 用于完全删除DB表，包括数据和结构

    • 在 InnoDB 中，每张表数据内容和索引都存储在一个以 .ibd 后缀的文件中，drop 就是直接把这个文件给删除了！还有一个 .frm 后缀的文件也会被删除，这个文件包含表的元数据和结构定义
    • 文件都删了，所以这个操作无法回滚，表空间会被回收，但是如果表存在系统共享表空间，则不会回收空间
    • 默认创建的表会有独立表空间，把 innodb_file_per_table 的值改为 OFF 后，就会被放到共享表空间中，即统一的 ibdata1 文件中

45. Inner、Left、Right Join区别

Inner Join 是内连接，返回两个表中满足连接条件的交集

image.png

Left Join 返回左表（左边表）的所有行，以及右表中与左表匹配的行。如果右表中没有匹配的行，则会返回 NULL 值

image.png

Right Join 返回右表（右边表）的所有行，以及左表中与右表匹配的行。如果左表中没有匹配的行，则会返回 NULL 值

image.png
  • Left Join与Right Join都称为外连接（outer join）
  • Left Join 为例，如果左表中的某一行在右表中有多行匹配，结果集会包含左表的这行与右表中每一匹配行的组合，可能会导致结果集的行数超过左表的行数

46. datetime、timestamp区别

  1. 存储格式

    • datetime：存储的是具体的日期和时间，YYYY-MM-DD HH:MM:SS。它不依赖于时区信息，存储的是原始数据
    • timestamp：存储的是自 1970-01-01 00:00:01 UTC 以来的秒数，YYYY-MM-DD HH:MM:SS。它依赖于时区信息，存储的是 UTC 时间

  2. 时间范围

    • datetime：表示从 1000-01-01 00:00:00 到 9999-12-31 23:59:59 的时间范围
    • timestamp：表示从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 的时间范围（32 位整数表示的限制）

  3. 时区处理

    • datetime：不考虑时区，存储的是输入的日期和时间值，不进行任何时区转换
    • timestamp：考虑时区，存储的是 UTC 时间，插入和检索时会根据当前时区进行转换。在不同的时区中，timestamp 类型的数据会自动调整以显示本地时间

  4. 默认值和自动更新

    • datetime：在 MySQL5.6 及更早版本中，datetime 列不能有自动更新的默认值。在 MySQL5.6 及以后版本中，可以使用 DEFAULT 和 ON UPDATE 子句来指定自动初始化和更新行为，但不像 timestamp 那么直观
    • timestamp：在 MySQL5.6 及更高版本中，timestamp 列可以有默认的当前时间戳 CURRENT_TIMESTAMP，并且可以使用 ON UPDATE CURRENT_TIMESTAMP 使其在行更新时自动更新为当前时间戳。这使得 TIMESTAMP 非常适合记录行的创建和修改时间

48. DB三大范式

目的：减少数据冗余，提高数据的完整性和一致性，使得表的设计更清晰

  • 不过现在业务上的表设计基本都是反范式的。经常需要冗余字段，减少联表查询，提升性能，特别是业务量比较大的公司，这种冗余是很有必要的！

1. 1NF：规范化

  • 定义：要求DB表中的所有字段值必须是原子值，即每个字段值是不可再分的基本数据项
  • 目的：确保数据表的每一列都是单一值，消除重复的列，从而保证数据的原子性
  • eg：地址作为一个字段，实际上可以拆分成省、市、区

2. 2NF：消除部分依赖

  • 定义：在满足第一范式的基础上，要求所有非主键字段必须完全依赖于整个主键
  • 目的：消除非主键字段对主键部分依赖，从而避免数据冗余和更新异常
  • eg：【学号、姓名、课程号、课程名称、分数】
    • 【学号、姓名】
    • 【学号、课程号、分数】
    • 【课程号、课程名称】

3. 3NF：消除传递依赖

  • 定义：在满足第二范式的基础上，要求非主键字段必须直接依赖于主键，而不能通过其他非主键字段间接依赖于主键
  • 目的：消除非主键字段对主键的传递依赖，从而进一步减少数据冗余和更新异常
  • eg：【员工ID、员工姓名、部门ID、部门名、经理ID】
    • 员工表【员工ID、员工姓名、部门ID】
    • 部门表【部门ID、部门名、经理ID】

49. MySQL哪些函数

  1. 字符串函数
    • CONCAT：连接字符串
    • SUBSTRING：提取子字符串
    • LENGTH：返回字符串的长度
    • REPLACE：替换字符串中的子字符串
    • UPPER、LOWER：将字符串转换为大写或小写
    • TRIM：去除字符串两端的空格
    • LEFT、RIGHT：返回字符串左边或右边的字符
  2. 数学函数
    • ABS：返回绝对值
    • CEIL、FLOOR：返回大于或等于/小于或等于指定数的最小整数/最大整数
    • MOD：返回除法的余数
    • POWER：返回一个数的指定次幂
  3. 日期函数
    • NOW：返回当前日期和时间
    • DATE_ADD 和 DATE_SUB：日期加上或减去一个时间间隔
    • DATEDIFF：计算两个日期之间的差异
    • YEAR、MONTH、DAY：提取日期的年份、月份、日期
    • STR_TO_DATE：将字符串转换为日期
  4. 聚合函数，汇总数据
    • COUNT：计算行数
    • SUM：计算总和
    • AVG：计算平均值
    • MAX、MIN：返回最大值和最小值
  5. 条件函数
    • IF：条件判断函数
    • IFNULL：返回第一个非 NULL 值
    • CASE：条件选择函数

50. TEXT 最大能存多长

TEXT 是非标准字符串类型，除了它还有 TINYTEXT、MEDIUMTEXT 和 LONGTEXT，一般存储一些不确定的较长文本才会使用它们

  1. TINYTEXT：最大长度为 255 字节
  2. TEXT：最大长度为 65,535 字节（约 64 KB）
  3. MEDIUMTEXT：最大长度为 16,777,215 字节（约 16 MB）
  4. LONGTEXT：最大长度为 4,294,967,295 字节（约 4 GB）

51. AUTO INCREMENT最大值

在 MySQL 中，如果表定义的自增 ID 到达上限后，再申请下一个 ID，得到的值不变！

# int 是 4 个字节，上限是 (2^32-1)，即 2147483647
create table mianshiya(
    id int auto_increment primary key
)  auto_increment=2147483647;

insert into mianshiya values(null);

insert into mianshiya values(null);
image.png

AUTO_INCREMENT 列不同数据类型的最大值：

  • TINYINT（8位），最大值是 127（有符号）或 255（无符号）
  • SMALLINT（16位），最大值是 32,767（有符号）或 65,535（无符号）
  • MEDIUMINT（24位），最大值是 8,388,607（有符号）或 16,777,215（无符号）
  • INT（32位），最大值是 2,147,483,647（有符号）或 4,294,967,295（无符号）
  • BIGINT（64位），最大值是 9,223,372,036,854,775,807（有符号）或 18,446,744,073,709,551,615（无符号）

52. 存储金额合适

在DB中业界常用两种类型来存储金额：bigint 和 decimal

  1. bigint 代码中用 long
    • 范围：可以存储的整数范围为 -2^63 到 2^63 - 1（在 MySQL 中为 64 位有符号整数）
    • 存储空间：占用 8 字节（64 位）
    • 精度：精确存储整数，但不支持小数部分，存储的金额单位是分
  2. decimal 代码中用 BigDecimal
    • 范围：可以存储的数字范围和小数位数由定义的精度和标度决定
    • 存储空间：存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间
    • 精度：支持高精度的小数运算，精确存储定点数，一般用 decimal(18, 6)，18 是总位数，6 是小数
  • long 类型保存到分，使得小数位（厘）的数据不好处理（需要手动处理，比较麻烦），因此精度不够高
  • BigDecimal 则很适用于高精度金额场景，且非常灵活，只不过相对于 long 性能会差一些（忽略性能问题）

53. 什么叫视图

视图是一种虚拟表，它是基于 SQL 查询结果的，它本身并不存储数据，而是存储 SQL 查询

  • 视图可以包含一个或多个表的数据，并且可以对这些数据进行筛选、计算、排序等操作

作用：

  • 简化复杂查询：视图可以将复杂的查询封装成一个简单的视图，使得用户在查询数据时更加方便
  • 安全性：通过视图可以限制用户访问特定的表和列，保护敏感数据
  • 数据抽象：视图提供了一种数据抽象层，用户可以通过视图获取需要的数据，而不必关心底层表的结构和关系
  • 可重用性：定义一次视图，可以在多个查询中重复使用，减少代码冗余

54. 游标是什么

面试遇到的几率比较低，但是游标这个概念还是需要了解

游标是一种DB查询机制，它允许逐行处理查询结果，主要用于需要逐行处理数据的情况（存储过程、触发器、应用程序）

作用：

  • 逐行处理：游标允许逐行处理查询结果集，在需要对结果集中的每一行进行复杂操作时，非常有用
  • 灵活性：游标提供了对结果集的灵活控制，可以前进、后退、跳到特定行等
  • 性能开销：游标的使用会带来性能开销，因为它需要在内存中维护结果集的状态。所以应尽量使用集合操作（eg：批量更新）而不是逐行处理

55. 直接存储图片、音频、视频?

本身 MySQL 是关系型DB，它设计的初衷是高效处理结构化和关系型数据，所以存储大容量的内容本身就不是它的职责所在，因此这方面的能力也不够

1. DB本身性能问题

  • DB性能：存储、检索大容量二进制数据（图片、音频、视频）显著增加DB的 I/O 操作和处理时间，从而影响整体性能
  • 查询性能：大容量的二进制数据会增加数据表的大小，导致查询性能下降。尤其是在涉及表连接或复杂查询时，性能影响更大

2. 可扩展性问题

文件系统可以通过分布式文件系统（eg：Hadoop HDFS、Amazon S3）更容易地进行扩展，而DB扩展则复杂得多

3. 权限控制问题

像文件系统和对象存储系统（eg：Amazon S3、OSS 等）通常提供更灵活的访问控制和权限管理机制，适合处理大容量数据的存储和访问，eg：访问的时效性等，而 MySQL 不提供这些功能，需要应用程序额外开发

4. 最佳实践

将大容量文件存储在文件系统或云服务提供的对象存储服务中，仅在DB中存储文件的路径或 URL 即可

56. 比Oracle优势

  1. MySQL 有免费版本，成本低，Oracle DB的许可费用和维护成本较高
  2. MySQL 是开源的，有广泛的社区支持和丰富的文档资源，能快速上手和解决问题
  3. MySQL 适合小型和中型应用，相比 Oracle 资源占用较少，更加轻量
  • Oracle 在处理大型企业级应用、复杂事务、并发控制和高级功能（eg：高级分析、数据仓库支持等）方面具有更强的能力
  • Oracle 起步早，结构严谨、高可用、高性能且安全，在传统行业（运输、制造、零售、金融等）中早早的占据了核心地位

57. VARCHAR() 100、10区别

两者的区别就是能存储的字符串长度上限不同，字符数上限是由定义中的括号内的数字决定的

  • VARCHAR(100) 最多可以存储 100 个字符
  • VARCHAR(10) 最多可以存储 10 个字符
  • 两者存储相同字符串时占用的空间是一样的。除了存储字符本身外，还需要额外的 1 或 2 个字节来记录字符串的长度。对于长度小于 255 的字符串，使用 1 个字节；对于长度 255 及以上的字符串，使用 2 个字节
  • 虽然存储的空间一样，但是在查询时，即带上 SORT、ORDER 这些字段时，VARCHAR(100) 字段会使得查询所占用的内存空间更多，因为在排序相关内存分配时，它是按照固定大小的内存块进行保存，VARCHAR(100) 的内存块会大于 VARCHAR(10)

58. 不推荐建索引

一般有以下几种情况不推荐建立索引：

  1. 对于数据量很小的表，查询速度不会明显受索引影响，建立了索引反而慢，因为还需要维护索引。
  2. 频繁插入、更新、删除操作的列不推荐建立索引
  3. 执行大量的 SELECT * ，索引不会显著提升性能，不推荐建立索引，因为大量的回表查询，开销大，DB最终可能会选择走全表扫描
  4. 高度重复值的列（枚举列），不推荐建立索引
  5. 低频查询的列，也不推荐建立索引
  6. 非常长的 varchar、JSON、BLOB、TEXT 类型，这些类型的列通常包含大量数据，不适合建立索引。数据量大排序时都无法用内存排，只能利用磁盘文件，排序很慢。且数据量大，每个页能存放的行数就少，扫描查询可能会涉及大量的 I/O。还有文本字段过大都需要额外 blob 页存储，每次查询还需要查额外的页，也是随机 I/O 效率低

59. exists、in区别

  • exists 检查子查询是否返回结果集。返回布尔值
  • in 检查某个值是否在子查询返回的结果集中。返回具体的值

exists 的查询机制是循环外表，通过外表的每行数据去内表查询是否有匹配的值，一旦找到符合条件的记录，此次内表子查询就会停止执行

  • 适合内表比外表大且有索引的场景
SELECT * FROM `mianshiya`
    WHERE exists (SELECT * FROM `order` WHERE mianshiya.user_id = order.user_id)

in 执行时会先执行子查询并生成结果集，然后将结果集与外部查询的列进行比较

  • 适合子查询记录少，且主查询表大有索引的场景
  • 如果子查询得到的结果比较大，还会将子查询的结果存储在哈希表中，快速匹配数据
SELECT * FROM `mianshiya`
    WHERE user_id in (SELECT user_id FROM `order`)

60. WAL技术优点

WAL（Write-Ahead Logging）技术是一种DB事务日志管理技术，它确保在修改真正的数据之前，先将修改记录写入日志。这使得即使系统崩溃，通过日志也能恢复数据。保证了数据的持久性和一致性

WAL 核心思想就是先写日志，再写数据，流程如下：

  1. 当一个事务开始时，所有对DB的修改都会先记录到一个日志文件中，而不是直接应用到DB文件，这些日志记录了数据的变更信息，可以用于恢复数据
  2. 当日志记录被安全地写入磁盘后，才会将这些修改应用到DB文件中

优点：

  • 崩溃恢复：在事务提交之前，变更首先记录到日志中，在系统崩溃后，DB可以通过重做日志中的操作来恢复到崩溃前的状态，确保数据一致性和持久性
  • 性能提升：把数据的随机写转化成日志的顺序写，提高了整体性能

重做日志（Redo Log）就是 WAL 的实现，用于保证事务的持久性和崩溃恢复能力。工作机制如下：

  1. 当一个事务开始时，所有对DB的修改首先记录到重做日志缓冲区中
  2. 重做日志缓冲区的数据会周期性地刷新到磁盘上的重做日志文件（ib_logfile0、ib_logfile1）
  3. 当事务提交时，InnoDB 确保重做日志已写入磁盘，然后将数据页的修改写入数据文件
  4. 如果系统崩溃，InnoDB 会在启动时通过重做日志重新应用所有未完成的事务，以恢复DB到一致状态

61. DB隔离级别

MySQL DB的默认隔离级别是 RR（可重复读），但是很多大公司把隔离级别改成了 RC（读已提交），为了提高并发和降低死锁概率

  • 为了解决幻读的问题 RR 相比 RC 多了间隙锁（gap lock）和临键锁（next-key lock）。而 RC 中修改数据仅用行锁，锁定的范围更小，因此相比而言 RC 的并发更高。

创建如下的表，并插入一些记录（以下例子参考 MySQL 官网）：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;

INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

此时执行 SQL-A，且未提交事务：

UPDATE t SET b = 5 WHERE b = 3;

在 RR 即可重复隔离级别情况下，会锁哪几条数据呢？

x-lock(1,2); 保留 x-lock
x-lock(2,3); update(2,3) to (2,5); 保留 x-lock
x-lock(3,2); 保留 x-lock
x-lock(4,3); update(4,3) to (4,5); 保留 x-lock
x-lock(5,2); 保留 x-lock

可以看到全锁了，此时执行 SQL-B。就会被阻塞

UPDATE t SET b = 4 WHERE b = 2;

而执行 SQL-A 在 RC 即读已提交隔离级别下，会锁哪几条数据呢？

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); 保留 x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); 保留 x-lock
x-lock(5,2); unlock(5,2)

可以看到，只锁了两条数据，此时执行 SQL-B 会怎样？

x-lock(1,2); update(1,2) to (1,4); 保留 x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); 保留 x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); 保留 x-lock

仅锁了 b = 2，完美避开了 SQL-A 加的锁。此时可能有同学会有疑问：SQL-B 不是应该被 (2,3) 这行的锁给阻塞吗？

1. 半一致性读

半一致性读（“semi-consistent” read）

  • InnoDB 做的一个优化。在执行 update 时，扫描发现当前行已经被锁定了，它就会执行半一致性读的操作，得到当前数据的最新版本（上述中 SQL-A 锁定的行最新版本的 b 都为 5），来判断是否和当前的（SQL-B） update 的 where 条件匹配，如果匹配则说明当前的 update 也需要锁定这行，因此需要等待。如果不匹配说明它们之间没关联，因此不需要等待锁，这个优化提升了并发度
  • 《RC + 半一致性读》能进一步的提升 SQL 执行的并发度
  • 并且 RC 锁的粒度更小，意味着死锁的概率会更低，但是缺点是可能会产生幻读，这个就需要业务评估幻读问题（大部分情况下都没啥影响）

62. 阿里禁止使用存储过程

存储过程是存储在DB中的一段预编译的 SQL 代码，封装了一组操作来执行特定的业务逻辑

  • 优点：预编译提升执行速度且减少网络通信开销、提高代码重用性
# 通过员工 ID 获取员工的姓名和薪资

DELIMITER $$

CREATE PROCEDURE GetEmployeeInfo(IN emp_id INT, OUT emp_name VARCHAR(100), OUT emp_salary DECIMAL(10, 2))
BEGIN
    SELECT name, salary
    INTO emp_name, emp_salary
    FROM employees
    WHERE id = emp_id;
END $$

DELIMITER ;

直接利用 CALL 就可以调用存储过程：

-- 定义变量用于存储输出参数
SET @emp_name = '';
SET @emp_salary = 0;

-- 调用存储过程
CALL GetEmployeeInfo(1, @emp_name, @emp_salary);

-- 查看结果
SELECT @emp_name AS EmployeeName, @emp_salary AS EmployeeSalary;

缺点：

  1. 可移植性差
    • DB依赖性：存储过程是在DB服务器上执行的，通常使用DB特定的 SQL 方言和功能，这会导致应用程序的DB依赖性增加，迁移到其他DB系统时有成本
    • 跨平台问题：不同的DB系统实现存储过程的方式和支持的功能不完全相同，维护比较复杂
  2. 调试困难
    • 调试工具有限：相比应用层代码，DB层的存储过程缺乏良好的调试工具和环境。常规的代码调试方法（设置断点、逐步执行）在存储过程中无法直接应用，导致复杂业务场景，不容易定位错误
  3. 维护复杂
    • 存储过程通常与应用程序代码分离，维护起来需要同时管理DB层和应用层的逻辑，增加了代码管理的复杂性

63. DB不停服迁移怎么做

但是实际上有很多细节，假装思考下，然后向面试官复述以下几点：

  • 首先关注量级，如果是几十万的数据其实直接用代码迁移，简单核对下就结束了
  • 不停服数据迁移需要考虑在线数据的插入和修改，保证数据的一致性
  • 迁移还需要注意回滚，因为一旦发生问题需要及时切换回老库，防止对业务产生影响

1. 双写

大部分DB迁移都会采用双写方案，例如自建的DB要迁移到云上的DB这个场景，双写就是同时写入自建的DB和云上的DB

迁移流程：

  1. 将云上DB（新库）作为自建DB（旧库）的从库，进行数据同步（或可以利用云上的能力，eg：阿里云的 DTS）
  2. 改造业务代码，数据写入修改不仅要写入旧库，同时也要写入新库，这就是双写，注意这个双写需要加开关，即通过修改配置实时打开双写和关闭双写
  3. 在业务低峰期，确保数据同步完全一致时（即主从不延迟，这个都是有对应的监控的），关闭同步，同时打开双写开关，此时业务代码读取的还是旧DB
  4. 进行数据核对，数据量很大的场景只能抽样调查（可以利用定时任务写代码进行抽样核对，一旦不一致就告警和记录）
  5. 如果确认数据一致，此时可以进行灰度切流，比如 1% 的用户切到读新的DB（比如今天访问前 1% 的用户或者根据用户 ID 或其他业务字段），如果发现没问题，则可以逐步增加开放的比例，eg：5%->20%->50%->100%
  6. 继续保留双写，跑个几天，确保新库确实没问题了，此时关闭双写，只写新库，这时候迁移就完成了

2. 补充

除了主从同步，代码双写的方案，也可以采用第三方工具。eg：flink-cdc 等工具来进行数据的同步，它的优点方便，且支持异构（mysql 同步到 pg、es 等）的数据源

image.png

像 flink-cdc 支持先同步全量历史数据，再无缝切到同步增量数据。上图中蓝色小块就是新增的插入数据，会追加到实时一致性快照中；上图中黄色小块是更新的数据，则会在已有历史数据里做更新