1.精通MySQL—基础 MySQL 知识、进阶 MySQL知识

本文目录如下：

• 精通MySQL — 基础 MySQL 知识
• 一、基础理论
• SQL语言包括哪几部分？每部分都有哪些操作关键字？
• 数据库的三大范式是什么？
• 说说 MySQL 的基础架构？存储引擎有什么作用？
• ⼀条 SQL 查询语句 的 执行流程？
• 什么是视图？为什么要使用视图？
• 二、数据格式 & 关键字
• MySQL中 char 和 varchar 的区别是什么？
• MySQL里记录 货币 用什么字段类型好?
• blob 和 text 有什么区别？
• MySQL 中 Exists 和 IN 有什么区别？
• UNION 与 UNION ALL 的区别？
• count(*) 与 count(列名) 的区别？
• delete、truncate 和 drop 的区别？
• 三、事务
• 数据库 事务 的特性 (ACID)？
• 事务有几种 隔离级别？
• 什么是脏读？不可重复读？幻读？
• 四、存储引擎
• 存储引擎：MyISAM 和 InnoDB 的区别？
• 如何选择 数据引擎？
• InnoDB 和 MyISAM 的索引结构有什么区别？
• 聚簇索引 与 非聚簇索引的区别？
• 谈一谈 MySQL 中的回表？
• 五、索引
• 什么是索引？
• 常用的 索引 有哪几种类型？
• 主键索引 与 唯一索引的区别?
• 为什么使用 索引 会加快查询？
• 索引 有什么缺点？
• 创建索引的 原则 有哪些？
• 创建了A, B 组合索引，使用 B 能否索引
• 索引什么时候会失效?
• MySQL 索引用的什么数据结构 (B+树)？
• B+树 相比于 B树 有什么优点？
• 六、日志
• MySQL 中有哪些日志文件？
• redo log 的 两次提交 的过程？
• redo log 为什么要分两次提交？
• 七、锁
• 表锁 和 行锁 的区别？共享锁 和 排他锁 的区别？
• MySQL 的 乐观锁 和 悲观锁 了解吗？
• 七、高可用 & 性能
• 如何做 MySQL 的性能优化？
• 数据库 读写分离 了解吗？
• 如何进行 分库？
• 如何进行 分表？
• 分库分表会带来什么问题呢？
• 八、其他
• 什么是数据库连接池? 为什么需要数据库连接池呢?
• 如何防止 SQL注入？
• 精通MySQL — 进阶 MySQL 知识
• 百万级别以上 的数据如何删除？
• 百万级别以上 大表如何添加字段？
• 100万数据的 A表 和10万数据的 B表进行Join操作，哪个表在前？
• 商品超卖 的 解决方案？
• 批量往数据库导入1000万条数据方法？
• 大数据量的分页优化方案?
• MySQL 服务器 CPU 飙升的话，要怎么处理呢？

精通MySQL — 基础 MySQL 知识

一、基础理论

SQL语言包括哪几部分？每部分都有哪些操作关键字？

• 数据定义(DDL): CREATE、DROP、ALTER等语句
• 数据操作(DML): INSERT(插入)、UPDATE(修改)、DELETE(删除)语句
• 数据查询(DQL) : SELECT语句
• 数据控制(DCL): GRANT、REVOKE、COMMIT、ROLLBACK等语句。

数据库的三大范式是什么？

• 第一范式：表的每一列都 不可分割。
• 第二范式：要求实体的属性 完全依赖 于 主关键字，即不存在 部分依赖。
• 第三范式：消除 非主键 之间的 传递依赖 关系，即不存在 传递依赖。

说说 MySQL 的基础架构？存储引擎有什么作用？

MySQL 基础架构 包含三部分：客户端、Server层、存储引擎层。

• Server 层：负责 连接管理、权限认证 等操作；将 API请求 转换为 存储引擎 可以理解的操作。
• 存储引擎层：位于最底层，存储引擎 负责管理 数据文件、索引文件、数据读写操作。

⼀条 SQL 查询语句 的 执行流程？

• 1.先由 Server 层 检查该语句是否有 执行权限 ，没权限则返回 错误信息。
• 2.有权限则由 分析器 进行 语法分析 ，判断 sql 语句 是否有 语法错误。
• 3.若没有语法错误，则 优化查询语句，然后交给 存储引擎层 进行处理 ，返回 执行结果。

什么是视图？为什么要使用视图？

点击查看

• 视图是一个虚表，只存放 定义，而不存放对应的数据。
• 视图能够 简化 操作, 可以更清晰的表达查询。

二、数据格式 & 关键字

MySQL中 char 和 varchar 的区别是什么？

• char(n) ：固定长度，长度不够的部分用空格补充；适用场景：存储 用户ID 等长度固定的字段。
• varchar(n) ：可变长度。

总结：从 空间 上考虑 varcahr 比较合适；从 效率 上考虑 char 比较合适。

MySQL里记录 货币 用什么字段类型好?

• 在 MySQL 中，金额用 DECIMAL 类型。
• DECIMAL 类型的值作为 字符串 存储，⽽不是作为 二进制浮点数 存储，和 Java 中的 BigDecimal 类似。

blob 和 text 有什么区别？

• blob 用于存储 二进制数据 (字节字符串)，主要用于存储 图片 等 非文本数据。
• text 用于存储 字符串 (字符字符串)，主要用于存储 文章 等 文本数据。

MySQL 中 Exists 和 IN 有什么区别？

• IN 则用于判断一个值是否在指定的值列表中。
• Exists 用于判断表中 是否存在记录，即使记录为空也可以。Exists 更高效。
• 注：MySQL 会把 IN 的查询语句改成 Exists 再去执行。

# 下面两句话等价
SELECT * FROM table_name WHERE column_name IN ('value1', 'value2');
SELECT * FROM table_name WHERE EXISTS (SELECT * FROM table_name WHERE column_name = 'value1' OR column_name = 'value2');


UNION 与 UNION ALL 的区别？

• UNION：会合并 重复的记录行
• UNION ALL：不会合并 重复的记录行
• 从效率上说：UNION ALL 要⽐ UNION 快很多，因为 不需要合并数据。

count(*) 与 count(列名) 的区别？

点击查看

从 执行结果 来说：

• count(1) 不会 过滤空值。
• count(列名) 会 过滤空值。

从 执行效率 来说：

• 如果 列为主键，count(列名) 效率优于 count(*)。
• 如果 列不为主键，count(*) 效率优于 count(列名)。
• count(1) 和 count(*) 效率相同。

delete、truncate 和 drop 的区别？

• delete：根据条件 删除行数据
• truncate：删除全表数据，保留表结构
• drop：删除全表数据，删除表结构
• 注：执行速度一般来说：drop > truncate > delete

三、事务

数据库 事务 的特性 (ACID)？

数据库–事务的ACID–介绍/详解

事务 是一个不可分割的 操作序列，也是数据库 并发控制 的基本单位。事务 的四大特性：

• 原子性: 要么全部 执行成功，要么全部 不执行。
• 一致性: 事务 前后 数据的完整性 必须 保持一致。
• 隔离性: 事务 之间 互不干扰。
• 持久性: 事务 一旦提交，它对数据库的改变就应该是 永久性的。

注：分布式 的 CAP 原则：一致性、可用性、分区容错性。

事务有几种 隔离级别？

什么是脏读？不可重复读？幻读？

• 脏读：事务A 读取了 事务B 更新的数据，然后 事务B 回滚操作，那么 事务A 读取到的数据是 脏数据。
• 不可重复读：事务A 多次读取同一数据，事务B 在 事务A 读取过程中，更新了数据，导致 事务A 多次读取同一数据 结果不一致。
• 幻读：当 事务A 读取某个范围内的记录时，事务B 又在该范围内插入了新的记录，当 事务A 再次读取该范围的记录时，会产生 幻行。

总结:

• 脏读 是因为 事务回滚。
• 不可重复读 是因为 修改数据。
• 幻读 是因为 新增或删除数据。

四、存储引擎

存储引擎：MyISAM 和 InnoDB 的区别？

MySQL–存储引擎–MyISAM和InnoDB的区别

• InnoDB：支持事务，支持 行级锁。支持 崩溃恢复。
• MyISAM：不支持事务，支持 表级锁。不支持 崩溃恢复。

如何选择 数据引擎？

默认使用 InnoDB 即可。

• InnoDB：适合 并发读写 或 增删改频繁 的场景，因为 InnoDB 支持 事务 和 行锁。
• MyISAM：适合 查询频繁 的场景。

InnoDB 和 MyISAM 的索引结构有什么区别？

• InnoDB 中的 主键索引 的 叶子节点 存储的是 实际数据，也就是 聚簇索引。
• MyISAM 中的 主键索引 的 叶子节点 存储的是 数据地址。就是 普通的主键索引 。
• 总结：InnoDB 和 MyISAM 的 索引结构 主要区别在于 主键索引，除了 主键索引 之外都是 非聚簇索引。

聚簇索引 与 非聚簇索引的区别？

聚簇索引和非聚簇索引有什么区别？

• 聚簇索引：叶子节点 就是 实际数据。
• 非聚簇索引: 叶子节点 存储的是 主键键值 ，一次查询 后需要根据 主键键值 在 主键索引 上进行 回表 查询
• ⼀个表中只能有⼀个 聚簇索引，但是可以有多个 非聚簇索引。

注：聚簇索引 决定了数据的 物理存储顺序，因此在查询中可以直接提供 实际数据。

谈一谈 MySQL 中的回表？

MySQL 中的回表

在 MyISAM 存储引擎 里，先通过 非聚簇索引 找到 主键索引 的 键值，再通过 主键索引 查询 数据，它比基于 主键索引 的查询多扫描了⼀棵 索引树，这个过程就叫 回表。

• 例如：select * from user where name = '张三';
  

五、索引

什么是索引？

• 索引 是数据表中的 一列或多列数据。可以用来 加快查询速度。
• 创建索引 会生成相应的 索引文件，查询时则不需要 遍历整张表。

常用的 索引 有哪几种类型？

• 主键索引：用于确定每一条记录的 唯一标识符。
• 唯一索引：确保表中的 某个列的值 是唯一的。
• 普通索引：根据 单个列 的值来查询数据。
• 组合索引：根据 多个列 的值来查询数据。

主键索引 与 唯一索引的区别?

MySql主键和唯一索引的区别

• 一个表只能有一个 主键索引，一个表能创建多个 唯一索引。
• 主键索引 不能为 null，唯一索引 可以为 null。

为什么使用 索引 会加快查询？

• 数据库 在执行一条 SQL语句 的时候，默认是根据 搜索条件 进行 全表扫描。
• 添加 索引 之后，MySQL 会生成⼀个 索引文件，查询数据时通过 索引文件 查找，大幅减少 扫描行数，从而提高了 查询效率。

索引 有什么缺点？

• 降低了数据写入的效率：增删改操作 要更新对应的 索引文件。
• 索引占物理空间

创建索引的 原则 有哪些？

数据库中哪些情况需要创建索引，哪些情况不需要创建索引

创建索引 的情况：

• 查询 频率高 的 字段 创建 索引
• 经常 排序 (order by) 的 字段 创建 索引
• 经常 分组 (group by) 的 字段 创建 索引
• 一般情况尽量创建 唯一索引
  -高并发 情况尽量创建 组合索引 【参考 最左匹配原则】

不创建索引 的情况：

• 频繁 更新 的字段 不适合创建索引
• 频繁 增删改 的表不适合创建索引

创建了A, B 组合索引，使用 B 能否索引

MySQL联合索引–使用/原理/优化

• 在当 A 的值确定的情况下，B 的值也是有序的。即在 A 确定时能使用 B 索引。
• 注：组合索引 遵循 最左匹配原则。

索引什么时候会失效?

• like 以 % 或者 _ 开头的时候
• 对 索引 列进行 计算 或使用 函数 的时候

MySQL 索引用的什么数据结构 (B+树)？

MySQL 的默认 存储引擎 是 InnoDB，它采用的是 B+树 结构的索引 (聚簇索引)。

• B+树 中 非叶子节点 存储 多个索引 和 多个分支，一般情况下 3次比较 就能查询到数据。
• B+树 中只有 叶子节点 才会 存储数据，非叶子节点 只存储 键值。
• 叶子节点 之间使用 双向指针 连接，形成了⼀个 双向有序链表。
  

B+树 相比于 B树 有什么优点？

B+树的特点和优势

B+树 中只有 叶子节点 才会 存储数据，非叶子节点 只存储 键值。叶子节点 之间使用 双向指针 连接，形成了⼀个 双向有序链表。因此有如下优点：

• 范围查询 和 排序能力 更强：叶子节点 之间使用 双向指针 连接。
• 扫表能力更强：进行 全表扫描 只需要遍历 叶子节点 就可以了。

六、日志

MySQL 中有哪些日志文件？

• bin log 日志：记录了所有 数据变更操作，包括 INSERT、UPDATE、DELETE 等操作。
• redo log 日志：用于 事物重做 操作。事务 执行前将 事务操作 记录到 undo Log 中。
• undo log 日志：用于 事务回滚 操作。事务 执行前将 原始数据 记录到 undo Log 中。

注：事务中断 进行 恢复 时，是要结合 redo log 和 bin log 进行数据恢复的。

redo log 的 两次提交 的过程？

redo log 为什么要分两次提交？

首先，redo log 写入 代表 事务已提交，bin log 写入 代表 数据已写入。

• prepare 阶段：将更新提交到 redo log，然后 redo log 标记状态为 prepare。
• commit 阶段：将更新写入 磁盘，即写入 bin log，然后 redo log 标记状态为 commit。
  

redo log 为什么要分两次提交？

【MySQL】一文彻底搞懂 Redo-log 为什么要两阶段提交？

• 提高数据库的性能：将数据首先写入内存比直接写入磁盘要快得多。
• 确保数据的持久性：因为数据最终会被同步写入磁盘，两次提交 的过程也叫做 预写日志(WAL)，可以保证 数据一致性 和 可恢复性。

七、锁

表锁 和 行锁 的区别？共享锁 和 排他锁 的区别？

MySQL–行级锁与表级锁

• 行锁：会死锁。发生 锁冲突 的 概率小，并发度高。
• 表锁：不会死锁。发生 锁冲突 的 概率高，并发量低。
• 注：FOR UPDATE 和 FOR SHARE 都是 行锁。

如果按照 兼容性，可分为两种：

• 共享锁：也叫 读锁，读锁 之间相互 不排斥。
• 排它锁：也叫 写锁，写锁 排斥其他 写锁 和 读锁。
• 注：FOR UPDATE 是 排他锁；FOR SHARE 都是 共享锁。

MySQL 的 乐观锁 和 悲观锁 了解吗？

悲观锁：认为 并发访问 时一定会 发生冲突，因此 访问数据 前都会 上锁。

• 行锁、表锁、共享锁、排它锁 都是 悲观锁。

乐观锁: 认为 并发访问 时 不会发生冲突，只是在 修改数据 时 检测 数据 是否被修改 (CAS 算法)。

• 乐观锁 只能由 开发人员 在 程序 中实现。

七、高可用 & 性能

如何做 MySQL 的性能优化？

• 避免不必要的列：避免使用 select *。
• 索引优化：根据 索引创建原则 合理地 添加索引。
• 合适的存储引擎：选择正确的 存储引擎。(判断业务 查询操作多 还是 更新操作多 来选择存储引擎)
• 分页优化：在数据量比较大，需要考虑分页。
• 读写分离
• 分库分表

数据库 读写分离 了解吗？

读写分离 的基本实现是:

• 1.数据库服务器 搭建 主从集群。(⼀主⼀从、⼀主多从 都可以)
• 2.主节点 (master) 处理 写操作，从节点 (slave) 处理 读操作。
• 3.主节点 通过 主从复制 将 业务数据 同步到 从节点。
  

如何进行 分库？

• 垂直分库：以 表 为依据，按照 业务归属 不同，将 不同的表 拆分到不同的库中。
  
• ⽔平分库：以 字段 为依据，按照 ⼀定策略，将⼀个表中的 数据 拆分到多个库中。
  

如何进行 分表？

• 水平分表：以 字段 为依据，按照⼀定策略，将⼀个表中的 数据 拆分到多个表中。
• 垂直分表：以 字段 为依据，按照 字段的活跃性，将表中 字段 拆到不同的表（主表 和 扩展表）中。
  

分库分表会带来什么问题呢？

• 事务的问题：分库之后无法使用 单机事务，必须使⽤ 分布式事务 来解决。
• 跨库 JOIN 问题：跨库了之后就无法 JOIN，只能在业务代码中进行关联。
• 跨节点的 count, group by 以及 聚合函数 问题：只能在业务代码中实现。

八、其他

什么是数据库连接池? 为什么需要数据库连接池呢?

数据库连接池 原理：在 内部对象池 中，维护一定数量的 数据库连接，并对外暴露 数据库连接 的获取和返回方法。

• 提高响应速度：高并发场景下大量 创建连接程 很费时, 使用连接池可以 提高响应速度。
• 统一的连接管理，避免数据库连接泄漏

如何防止 SQL注入？

数据库–防止SQL注入的方案

• 权限区分：普通用户与系统管理员用户的权限要有严格的区分。
• 使用合适的ORM框架：好的 ORM框架 可以很大程度防止SQL注入。推荐使用：MyBatis-Plus。
• 对用户的输入进行验证

精通MySQL — 进阶 MySQL 知识

百万级别以上 的数据如何删除？

当我们对数据进行 增加、修改、删除 操作时, 会产生额外的对 索引文件 的操作, 这些操作会降低 执行效率。所以 删除数据的速度 和 索引数量 是成正比的。

因此删除 百万级别数据 的步骤如下：

1. 先 删除索引。
2. 然后 删除⽆⽤数据。
3. 删除完成后 重新创建索引 (速度很快)。

百万级别以上 大表如何添加字段？

当表中数据量到达 百万级别以上 时，加一个字段就没那么简单，因为可能会 长时间锁表。

大表添加字段，通常有这些做法：

• 通过中间表转换：创建⼀个 临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，删除旧表，新表命名为旧表的名称，这种方式可能会 丢失数据。
• 先在从库添加字段，然后进行主从切换。

100万数据的 A表 和10万数据的 B表进行Join操作，哪个表在前？

• 在进行 表连接 (Join) 操作时，通常将 较小的表放在前面 效率更高。
• 数据库 进行 表连接 时，会从 左表 中选择一行记录，然后在 右表 中查找 匹配的记录。
• 原理：减少 外层循环次数。

商品超卖 的 解决方案？

避免商品超卖的4种方案

最优的解决方案：

• 使用 Redis队列 来实现。将要促销的 商品数量 以 队列 的方式存入 Redis，每当用户抢到一件促销商品则从 队列 中 删除一个数据，确保商品 不会超卖。这个方法 效率极高。

// 使用 Redis队列 实现，用户过来直接入队列，然后再将操作更新到数据库
// 最佳体验（redis pconnect 9.481s, 无丢失， 无框架）
public void push() {
// 入队列
jedis.lpush(QUEUE, "1");
}

// 脚本调用pop方法
public void pop() {
String key;
while ((key = jedis.rpop(QUEUE)) != null) {
Shop shop = getShopById(1); // Assuming shop with ID 1
if (shop.getNumber() > 0) {
DB.updateShopNumber(shop.getId(), shop.getNumber() - 1);
}
}
}


批量往数据库导入1000万条数据方法？

点击查看

使用 批处理，减少 数据库连接次数，同时将 单条插入语句 改为 一次插入多条数据 以提高效率。

• 总结：批处理:一次发送 多条SQL语句 + 一条 SQL语句 插入 多条数据

大数据量的分页优化方案?

MySQL 服务器 CPU 飙升的话，要怎么处理呢？

排查过程：

1. 使⽤ top 命令观察，确定是否是 MySQL 导致的。
2. 如果是 MySQL 导致的，使用 show processlist 查看 session 情况，确定是不是有 消耗资源的 sql 在运⾏。
3. 找出 消耗高的 SQL语句，分析原因。