MySQL

1. 基础

1. 什么是关系型数据库？

一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

2. 什么是SQL？

一种结构化查询语言，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。

3. MySQL的优点

一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息，端口默认3306。

成熟稳定，功能完善，开源免费。

文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。

开箱即用，操作简单，维护成本低。

兼容性好，支持常见的操作系统，支持多种开发语言。

社区活跃，生态完善。

事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。

支持分库分表、读写分离、高可用。

4. 基础架构

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

• 优化器： 按照 MySQL 认为最优的方案去执行。

• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

分层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。

• 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。

SQL执行：

• 查询：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎

• 更新：分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit 状态)

5. MySQL自增主键不一定连续

自增主键可以让聚集索引尽量地保持递增顺序插入，避免了随机查询，从而提高了查询效率。

• 场景：

  1. 自增初始值 和 自增步长设置不为 1

  2. 唯一键冲突

  3. 事务回滚

  4. 批量插入（如 insert...select 语句）：并不知道到底需要申请多少 id，所以就采用了这种批量申请的策略

6. 数据库设计通常分为几步？

1. 需求分析 : 分析用户的需求，包括数据、功能和性能需求。

2. 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。

3. 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。

4. 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。

5. 数据库实施 : 包括编程、测试和试运行

6. 数据库的运行和维护 : 系统的运行与数据库的日常维护。

2. 字段类型

数值（整、浮点、定点）、字符串（CHAR VARCHAR）、日期时间（DATETIME TIMESTAMP）

1. 整数类型的 UNSIGNED 属性有什么用？

表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。对于从 0 开始递增的 ID 列，提供了更多的 ID 值可用。。

2. CHAR 和 VARCHAR 的区别是什么？

CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格； VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

3. DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal。

4. 为什么不推荐使用 TEXT 和 BLOB？

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储更长的字符串，即长文本数据，例如博客内容。

BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

缺陷：

• 不能有默认值。

• 检索效率较低。

• 不能直接创建索引，需要指定前缀长度。

• 可能会消耗大量的网络和 IO 带宽。

• 可能导致表上的 DML 操作变慢。

5. DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型没有时区信息 8字节，TIMESTAMP 和时区有关 4字节。

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

6. NULL 和 '' 的区别是什么？

NULL 跟 ''(空字符串)是两个完全不一样的值：

• NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。

• ''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。

• NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。

• 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

7. Boolean 类型如何表示？

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

3. 存储引擎

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎。

1. 存储引擎架构是怎么样的？

插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

2. MyISAM 和 InnoDB 有什么区别？

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

• MyISAM 不支持外键，而 InnoDB 支持。

• MyISAM 不支持 MVCC，而 InnoDB 支持。

• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。

• InnoDB 的性能比 MyISAM 更强大。

4. 索引

1. 理解

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

优点：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

• 索引需要使用物理文件存储，也会耗费一定空间。

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

2. 索引底层 数据结构选择

1. Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

key -> hash -> index -> value

hash = hashfunc(key)
index = hash % array_size

Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。

解决：链地址 将哈希冲突数据存放在链表中，过长引入红黑树。 一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

InnoDB 存储引擎中存在一种特殊的“自适应哈希索引”，每个哈希桶实际上是一个小型的 B+Tree 结构，可以存储多个键值对。

❗️不支持顺序和范围查询。

2. 二叉查找树 BST

平衡的时候，也就是树的每个节点的左右子树深度相差不超过 1 的时候，查询的时间复杂度为 O(log2(N))，具有比较高的效率；最坏变成线性链表，时间复杂退化为 O（N）。

严重依赖其平衡程度。

3. 自平衡二叉查找树 AVL树

任何节点的左右子树高度之差不超过 1，查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)，采用旋转操作来保持平衡。

然而，需要频繁地进行旋转操作来保持平衡，较大的计算开销进而降低了数据库写操作的性能；每次进行磁盘 IO 时只能读取一个节点的数据，增加了磁盘 IO 操作的次数。

4. 红黑树

一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态。

因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。

5. B 树& B+树

多路平衡查找树 ，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率（任何查找都是从根节点到叶子节点的过程）和更适于范围查询（遍历链表）这些优势。

3.类型总结

• 数据结构：BTree 索引、哈希索引、全文索引

• 底层存储：聚簇索引（索引结构和数据一起存放，InnoDB主键索引）、非聚簇索引（MyISAM）

• 应用：主键、普通、唯一、覆盖、联合、全文

1. 不同索引的组织结构

CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',`name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',`age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',PRIMARY KEY (`id`),KEY `I_name` (`name`)
) ENGINE=InnoDB;
​
INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);

每一个索引在 InnoDB 里面对应一棵B+树，那么此时就存着两棵B+树。

区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键id。

SELECT age FROM student WHERE name = '小李'；

1. 在name索引树上找到名称为小李的节点 id为03

2. 从id索引树上找到id为03的节点 获取所有数据

3. 从数据中获取字段命为age的值返回 12

从非主键索引树搜索回到主键索引树搜索的过程称为 回表。因为本次查询中查询结果只存在主键索引树中，我们必须回表才能查询到结果。

2. 覆盖索引

从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。 按键值的顺序存储，把随机 IO 变成顺序 IO 加快查询效率，减少了IO次数。

ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

流程变为：

1. 在name,age联合索引树上找到名称为小李的节点

2. 此时节点索引里包含信息age 直接返回 12

把单列的非主键索引 修改为 多字段的联合索引，在一棵索引树上 就找到了想要的数据, 不需要去主键索引树上，再检索一遍。（不用 回表）

4. 主键索引

数据表的主键列使用的就是主键索引，Primary Key。

• 主键索引：加速查询 + 列值唯一（不 NULL）+ 表中只有一个。

• 普通索引：仅加速查询。

• 唯一索引：加速查询 + 列值唯一（可 NULL）。

没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

5. 二级索引

二级索引（Secondary Index）的叶子节点存储的数据是主键的值，通过二级索引可以定位主键，二级索引又称为辅助索引/非主键索引。（唯一、普通、前缀、全文）

6. 聚簇索引与非聚簇索引

1. 聚簇索引（聚集索引）

索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

• 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。

• 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

1. 非聚簇索引（非聚集索引）

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

优点：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

• 依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据

• 可能会二次查询(回表):这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

5. 事务

• 数据库中途突然因为某些原因挂掉了。

• 客户端突然因为网络原因连接不上数据库了。

• 并发访问数据库时，多个线程同时写入数据库，覆盖了彼此的更改。

事务是逻辑上的一组操作，要么都执行，要么都不执行，ACID

1. 并发事务带来的问题

• 脏读 Dirty Read

B读取A未提交的数据，且1回滚。

• 丢失修改 Lost to modify

A, B 都读取并修改数据

• 不可重复读 Unrepeatable read

B事务内两次读取结果不一样 （ 修改或者减少，delete update ），因为A修改了。

• 幻读 Phantom read

第一个事务就会发现多（ insert ）了一些原本不存在的记录，就好像发生了幻觉一样。

2. 并发事务的控制方法

锁 和 MVCC。 锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制，只能做到 读读并行。根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

• undo log : undo log 用于记录某行数据的多个版本的数据。

• read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

3. SQL 标准定义了哪些事务隔离级别?

• READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

---

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

SERIALIZABLE 隔离级别是通过锁来实现的。

InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

6. MySQL锁

1. 表级锁和行级锁了解吗？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。 InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

2. InnoDB有哪几类行锁？

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。

• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。

• 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。 但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

3. 意向锁有什么用？

意向锁是一个表级锁，其作用就是指明接下来的事务将会用到哪种锁。 由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在数据表的对应意向锁。

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁，表明该事务将对该行进行加锁操作。

• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

4. 当前读 和 快照读 有什么区别？

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

快照读（一致性非锁定读），如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照（记录的历史版本），每行记录可能存在多个历史版本（MVCC 多版本技术）。

7. 性能优化

1. 直接MySQL存储图片会怎么样？

直接存储文件对应的二进制数据即可，但是会严重影响数据库性能，消耗过多存储空间。

建议使用云服务厂商提供的开箱即用的文件存储服务（阿里云OSS），或者自己搭建文件存储系统。

数据库只存储文件地址信息，文件的内容由文件存储服务负责存储。

2. 如何存储IP地址？

将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

• INET_ATON()：把 ip 转为无符号整型 (4-8 位)

• INET_NTOA() :把整型的 ip 转为地址

3. 常见的数据库优化方法？

• 索引优化

• 读写分离和分库分表

• 数据冷热分离

• SQL 优化

• 深度分页优化

• 适当冗余数据（通过预存储某些计算结果或常用的数据组合，减少运行时的计算量，从而更快地响应用户查询）

• 使用更高的硬件配置

4. 读写分离

1. 理解

将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。（小写大读） 处理的是 数据库读并发 问题。

一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

2. 实现

要求：

1. 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。

2. 保证主数据库和从数据库之间的数据实时同步的，这个过程也就是我们常说的主从复制。

3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

软件层面：

• 代理：在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。 （例如 MySQL Router）

• 组件： sharding-jdbc ，直接引入 jar 包即可使用，

3. 主从复制原理

MySQL binlog(binary log 即二进制日志文件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。（还能帮助我们实现数据恢复。）

过程：

1. 主库将数据库中数据的变化写入到 binlog

2. 从库连接主库

3. 从库会创建一个 I/O 线程向主库请求更新的 binlog

4. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收

5. 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。

6. 从库的 SQL 线程读取 relay log 同步数据到本地（也就是再执行一遍 SQL ）。

主写从连，从主线程，从写从同。

4. 如何避免主从延迟？

主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。

解决：强制将读请求路由到主库处理。

Sharding-JDBC 的 HintManager 分片键值管理器，我们可以强制使用主库。

HintManager hintManager = HintManager.getInstance();
hintManager.setMasterRouteOnly();
// 继续JDBC操作

将那些必须获取最新数据的读请求都交给主库处理。

5. 什么情况下会出现？如何尽量减少延迟？

• MySQL 主从同步延时是指从库的数据落后于主库的数据，这种情况可能由以下两个原因造成：

  1. 从库 I/O 线程接收 binlog 的速度跟不上主库写入 binlog 的速度，导致从库 relay log 的数据滞后于主库 binlog 的数据；(主写从收)

  2. 从库 SQL 线程执行 relay log 的速度跟不上从库 I/O 线程接收 binlog 的速度，导致从库的数据滞后于从库 relay log 的数据。（从收从行）

• 与主从同步有关的时间点主要有 3 个：

  1. 主库执行完一个事务，写入 binlog，将这个时刻记为 T1；

  2. 从库 I/O 线程接收到 binlog 并写入 relay log 的时刻记为 T2；

  3. 从库 SQL 线程读取 relay log 同步数据本地的时刻记为 T3。

• 可以得出：

  1. T2 和 T1 的差值反映了从库 I/O 线程的性能和网络传输的效率，这个差值越小说明从库 I/O 线程的性能和网络传输效率越高。

  2. T3 和 T2 的差值反映了从库 SQL 线程执行的速度，这个差值越小，说明从库 SQL 线程执行速度越快。

• 什么情况下会发生？

  1. 从库机器性能比主库差：从库进行性能优化（或者 换一个更好的），比如调整参数、增加缓存、使用 SSD 等。

  2. 从库处理的读请求过多：引入缓存（推荐）、使用一主多从的架构。

  3. 大事务：避免大批量修改数据，尽量分批进行。

  4. 从库太多：减少从库的数量，或者将从库分为不同的层级（分级）。

  5. 网络延迟：优化网络环境，比如提升带宽、降低延迟、增加稳定性等。

  6. 单线程复制：多线程复制。

  7. 复制模式：MySQL默认的复制是异步的，使用半同步复制。

5. 分库分表

1. 理解

解决 MySQL 的存储压力。（如果 MySQL 一张表的数据量过大怎么办?）

2. 分库

将数据库中的数据分散到不同的数据库上。

• 垂直：单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

• 水平：同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

3. 分表

对单表的数据进行拆分。

• 垂直：把一张列比较多的表拆分为多张表。

• 水平：把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。水平分表通常和水平分库同时出现。

4. 什么情况下？

• 单表的数据达到千万级别以上，数据库读写速度比较缓慢（体量）。

• 数据库中的数据占用的空间越来越大，备份时间越来越长（增长）。

• 应用的并发量太大（应该优先考虑其他性能优化方法，而非分库分表）。

但是，分库分表的成本太高，如非必要尽量不要采用。

5. 常见的分片算法？

数据被水平分片之后，数据究竟该存放在哪个表的问题。

• 哈希：使每个表的数据分布相对均匀，适合随机读写的场景。

• 一致性哈希：将哈希空间组织成一个环形结构，解决了传统哈希对动态伸缩不友好的问题。

• 范围：进行范围查找且数据分布均匀。

• 映射表：存储分片键和分片位置的对应关系，维护额外的表。

• 地理位置

• 融合算法：灵活组合多种分片算法，比如将哈希分片和范围分片组合。

6. 分片键如何选择？

数据分片的关键字段，影响数据的分布和查询效率。分片键可以是表中多个字段的组合。 例如 将订单表中的订单主键的尾数取模分片，则订单主键为分片键。

• 共性，即能够覆盖绝大多数的查询场景，尽量减少单次查询所涉及的分片数量，降低数据库压力；

• 离散性，即能够将数据均匀地分散到各个分片上，避免数据倾斜和热点问题；

• 稳定性，即分片键的值不变，避免数据迁移和一致性问题；

• 扩展性，即能够支持分片的动态增加和减少，避免数据重新分片的开销。

7. 带来什么问题？

• join：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作，需要多次查询业务层进行数据组装的方法。（join效率低，且影响分库分表操作，不推荐）

• 分布式事务：单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了，引入分布式事务。

• 分布式ID：数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。

• 跨库聚合查询：导致常规聚合查询操作，如 group by，order by 等变得异常复杂，在多个分片上进行数据汇总和排序。使用中间件来协调分片间的通信和数据传输。

8. 推荐方案？

Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。 除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。

9. 怎么数据迁移？

如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

• 停机迁移：写一个脚本将老库的数据都同步到新库中。

• 双写方案：老库更新写入新库，比对数据少插多删，重复直到新老数据一致。

我们对老库的更新操作（增删改），同时也要写入新库（双写）。 如果操作的数据不存在于新库的话，需要插入到新库中。 这样就能保证，咱们新库里的数据是最新的。

我们还需要自己写脚本将老库中的数据和新库的数据做比对。 如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。

重复上一步的操作，直到老库和新库的数据一致为止。

10. 总结

• 读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

• 读写分离基于主从复制，MySQL 主从复制是依赖于 binlog 。

• 分库 就是将数据库中的数据分散到不同的数据库上。分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

• 引入分库分表之后，需要系统解决事务、分布式 id、无法 join 操作问题。

• 现在很多公司都是用的类似于 TiDB 这种分布式关系型数据库，不需要我们手动进行分库分表（数据库层面已经帮我们做了），也不需要解决手动分库分表引入的各种问题，直接一步到位，内置很多实用的功能（如无感扩容和缩容、冷热存储分离）！如果公司条件允许的话，个人也是比较推荐这种方式！

• 如果必须要手动分库分表的话，ShardingSphere 是首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

6. 深度分页如何优化？

查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低。

# MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

• 范围查询：保证 ID 的连续性时，根据 ID 范围进行分页。

# 查询指定 ID 范围的数据
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10

• 子查询：先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit。

# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;

子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。

• 延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。

SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 10) t2
ON t1.id = t2.id;

减少大量的回表查询，大大的提升了sql查询效率。

7. 数据冷热分离

1. 理解

指根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。对数据进行分类，然后分开存储。

• 划分：

  1. 时间维度：按照数据的创建时间、更新时间、过期时间等，将一定时间段内的数据视为热数据，超过该时间段的数据视为冷数据。 例如，订单系统可以将 1 年前的订单数据作为冷数据，1 年内的订单数据作为热数据。这种方法适用于数据的访问频率和时间有较强的相关性的场景。

  2. 访问频率：将高频访问的数据视为热数据，低频访问的数据视为冷数据。 例如，内容系统可以将浏览量非常低的文章作为冷数据，浏览量较高的文章作为热数据。这种方法需要记录数据的访问频率，成本较高，适合访问频率和数据本身有较强的相关性的场景。

优点：热数据的查询性能得到优化（用户的绝大部分操作体验会更好）、节约成本（可以冷热数据的不同存储需求，选择对应的数据库类型和硬件配置，比如将热数据放在 SSD 上，将冷数据放在 HDD 上）。

缺点：系统复杂性和风险增加（需要分离冷热数据，数据错误的风险增加）、统计效率低（统计的时候可能需要用到冷库的数据）。

2. 冷数据如何迁移？

1. 业务层代码实现：当有对数据进行写操作时，触发冷热分离的逻辑，判断数据是冷数据还是热数据，冷数据就入冷库，热数据就入热库。这种方案会影响性能且冷热数据的判断逻辑不太好确定，还需要修改业务层代码，因此一般不会使用。（不推荐）

2. 任务调度：可以利用 xxl-job 或者其他分布式任务调度平台定时去扫描数据库，找出满足冷数据条件的数据，然后批量地将其复制到冷库中，并从热库中删除。这种方法修改的代码非常少，非常适合按照时间区分冷热数据的场景。

3. 监听数据库的变更日志 binlog ：将满足冷数据条件的数据从 binlog 中提取出来，然后复制到冷库中，并从热库中删除。这种方法可以不用修改代码，但不适合时间维度区分冷热数据的场景。

4. 让 DBA 进行冷数据的人工迁移，一次迁移完成冷数据到冷库。

3. 冷数据如何存储？

容量大，成本低，可靠性高，访问速度可以适当牺牲。

Hbase（常用）、RocksDB、Doris、Cassandra

使用 TiDB 6.0 的数据放置功能，可以在同一个集群实现海量数据的冷热存储，将新的热数据存入 SSD，历史冷数据存入 HDD。

8. 三大日志

主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

重要：二进制日志 binlog（归档日志）、事务日志 redolog（重做日志）、undolog（回滚日志）。

redo log 是 InnoDB 引擎特有的。

1. redo log 重做日志

• 作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

• 内容：物理日志，记录的是物理数据页面的修改的信息，其 redo log 是顺序写入 redo log file 的物理文件中去的。

2. bin log 归档日志（二进制日志）

• 作用：用于复制，在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。 用于数据库的基于时间点的还原。只要发生了表数据更新，都会产生 binlog 日志，顺序写入。保证数据的一致性。

• 内容：逻辑日志，可以简单认为就是执行过的事务中的 sql 语句。但又不完全是 sql 语句这么简单，而是包括了执行的 sql 语句（增删改）反向的信息，也就意味着 delete 对应着 delete 本身和其反向的 insert；update 对应着 update 执行前后的版本的信息；insert 对应着 delete 和 insert 本身的信息。

　　binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）

3. undo log 回滚日志

• 作用：保存了事务发生之前的数据的一个版本，可以用于回滚版本，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

• 内容：逻辑日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

MVCC 的实现依赖于：隐藏字段、Read View、undo log

4. 两段提交

同时使用了 redo log 和 binlog，那么就需要保证这两种日志之间的一致性。否则，在数据库发生异常重启或者主从切换时，可能会出现数据不一致（ 数据库的状态就有可能和用它的日志恢复出来的库的状态不一致 ）的情况。

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交时才写入。

所以，将 redo log 的写入拆成了两个步骤prepare和commit。

1. 在准备阶段，MySQL先将数据修改写入redo log，并将其标记为prepare状态（事务还未提交）。然后将对应的SQL语句写入bin log。

2. 在提交阶段，MySQL将redo log标记为commit状态（事务已经提交）。然后根据sync_binlog参数的设置，决定是否将bin log刷入磁盘。