MySQL常见问题

Nature's Beauty

Explore the wonders of the world

参考资料

• https://dunwu.github.io/db-tutorial/pages/5fe0f3/
• https://dunwu.github.io/pages/7b0caf/

数据库设计基础

三大范式

范式是构建良好数据库结构的设计原则，用于描述数据之间的关系。

首先明确：主键、关键字段、候选键这三个概念是等价的。

第一范式（1NF）

表的字段具有原子性，即每个字段不可再分。

不满足第一范式的数据库就不是关系型数据库。

定义表时，如果每个字段都只能用一种数据类型表示，则该表满足第一范式。

第二范式（2NF）

在满足第一范式的基础上，若存在复合主键，则要求非关键字段不能与复合主键的部分字段存在部分函数依赖。

不满足第二范式的后果包括：

1. 数据冗余：每张表应保存自身特定的数据，而非全部包含
2. 数据更新耗时：因冗余导致修改需要更多操作，影响数据库稳定性
3. 新增数据异常：字段过多时，部分字段可能要求非空
4. 删除数据异常：如学生表和成绩表混在一起，删除某课程学生时可能导致课程也被删除

如果一张表没有复合主键，则一定满足第二范式。设计复合主键时需注意与其他字段是否存在依赖关系。

第三范式（3NF）

在满足第二范式的基础上，要求不存在非关键字段对候选键的传递依赖。

传递依赖指：由一个字段值可以推出另一个字段的值。

设计时应将存在传递依赖的字段分在不同表中，再通过表关联来查询。

数据库依赖

• 部分依赖：如果属性集合 A 能够决定属性集合 B，但在 A 中存在真子集 A’ 也能决定 B，则称 B 对 A 部分依赖

• 完全依赖：如果属性集合 A 能够决定属性集合 B，但在 A 中不存在任何真子集能决定 B，则称 B 对 A 完全依赖

• 传递依赖：如果属性集合 A 能决定 B，B 能决定 C，但 A 不能直接决定 C，则称 C 对 A 传递依赖

表的关系

• 一对一：例如学生与学号的对应关系
• 一对多/多对一：例如一个班级包含多名学生；多名学生属于同一班级
• 多对多：例如学生与课程的关系

数据类型与存储

int(10)、char(10)、varchar(10)

参考：https://blog.csdn.net/weixin_49263647/article/details/123180332

• int(10) 中的 10 表示数据显示的宽度，而非存储大小
• CHAR 是定长字符串，VARCHAR 是变长字符串
• CHAR(M) 存储时会填充空格至 M 个字符，检索时自动去除空格
• VARCHAR(M) 存储时需用 1 或 2 个额外字节记录长度，检索时无需额外处理
• M 代表能够保存的字符数上限

CHAR 更适合存储长度较短或相对固定的字符串，如 bcrypt/MD5 加密后的密码、身份证号码。

VARCHAR 适合存储长度不确定或差异较大的字符串，如用户昵称、文章标题等。

数据查询与操作

MySQL 查询语句的执行过程

检查权限、查询缓存、分析器、优化器、权限校验、执行器、引擎。

以如下查询为例：

1
2
3
4
5
6
7

SELECT
  *
FROM
  `user`
WHERE
  id > 1
  AND NAME = 'myMySQL';

执行流程如下：

1. 检查权限，有权限则调用数据库引擎接口返回结果，否则返回错误
2. 查询缓存，命中则直接返回，否则继续下一步
3. 进行词法分析和语法分析，提取表名和查询条件，检查语法
4. 优化器根据优化算法选择执行效率最优的方案。同一查询可能有多种执行顺序，优化器会自动选择

模糊查询与通配符

LIKE 一般配合通配符使用，可匹配字符型或数值型数据。

• %：匹配任意 0 个或多个字符
• _：匹配任意 1 个字符

DELETE、TRUNCATE、DROP 的区别

参考：https://www.php.cn/mysql-tutorials-68773.html

• TRUNCATE 清空表内容，重置自增索引从 1 开始

• DELETE FROM TABLE WHERE ... 根据条件删除数据

• 以上两种方式删除后，DESC tablename 仍显示表结构

• DROP TABLE 直接删除表，包括表结构和索引

分页读取数据

假设有数据如下：

1
2
3
4
5
6
7
8

-- 输出前 2 条数据：001、002
LIMIT 2

-- 忽略前 2 行，输出 3 条数据：003、004、005
LIMIT 2, 3

-- 忽略前 3 行，输出 2 条数据：004、005
LIMIT 2 OFFSET 3

LIMIT 子句必须放在整个 SELECT 语句的最后。

过滤条件放在 ON 和 WHERE 的区别

1
2
3
4
5
6
7

SELECT
  ...
FROM
  ... (INNER / LEFT / RIGHT)
  JOIN...ON...
WHERE
  ...

WHERE 在 JOIN 之后执行，ON 在 JOIN 之中执行。JOIN 要求保留索引列，因此如果过滤条件放在 ON 中，筛选条件只对非索引列起作用。过滤条件放在 WHERE 中则不会出现此问题。

数据库引擎与索引

MySQL 的存储引擎

• InnoDB：默认引擎，适合需要提交、回滚、崩溃恢复能力及并发控制的场景
• MyISAM：适合数据表操作以插入和查询为主的场景
• MEMORY：适合临时存放数据，可作为临时表或存放查询中间结果
• Archive：适合数据表操作以插入和查询为主的场景

数据库索引

主键属于索引的一种。通过 PRIMARY KEY 约束创建主键，要求唯一且非空。

按逻辑分类

• 主键索引：每张表只能有一个，键值唯一且非空
• 唯一索引：每张表可有多个，键值唯一但可为空
• 组合索引：对多个字段组合创建，遵循最左前缀原则
• 普通索引：每张表可有多个，键值可重复可为空
• 全文索引

按物理分类

• 聚集索引：每张表只能有一个，数据物理存储顺序与索引逻辑顺序相同
• 非聚集索引

创建索引的优缺点

优点：

• 大幅提高系统性能
• 保证数据唯一性
• 加快数据检索速度
• 加速表间连接
• 加速分组和排序

缺点：

• 创建和维护索引耗时，时间随数据量增加而增加
• 索引占用物理空间
• 数据增删改时需同步维护索引，降低维护速度

综上，索引适合在经常搜索的列、主键列、经常用于连接的列、经常排序的列上使用。不适合在更新频繁的列或取值较少的列（如性别）上使用。

创建和删除索引

创建索引（ALTER 语句）：

1
2
3
4
5
6
7
8

-- 添加主键
ALTER TABLE mytable ADD PRIMARY KEY (col)

-- 添加唯一索引
ALTER TABLE mytable ADD UNIQUE (col)

-- 添加普通索引
ALTER TABLE mytable ADD INDEX (col)

创建索引（CREATE 语句）：

1
2
3
4
5

-- 添加唯一索引
CREATE UNIQUE INDEX myindex ON mytable (col)

-- 添加普通索引
CREATE INDEX myindex ON mytable (col)

删除索引（ALTER 语句）：

1
2
3
4
5

-- 删除普通索引
ALTER TABLE mytable DROP INDEX myindex

-- 删除主键索引
ALTER TABLE mytable DROP PRIMARY KEY

删除索引（DROP 语句）：

1

DROP INDEX myindex ON mytable

数据库索引结构

• 二叉树：查找时，目标值小于当前节点则向左，大于则向右。使用自增整型字段作为索引时，新数据总是添加到右侧，等同于未建立索引的情况

• 红黑树（二叉平衡树）：解决自增整型索引问题，通过左旋和右旋调整结构。但数据量大时树深度仍然很深，因为每个节点只能有两个子节点

• B 树：在红黑树基础上增加每个节点存储的数据个数，减少树深度。左右子树深度相同，每个节点包含索引值和表记录。查询单条数据快，范围查询需从根节点开始效率较低。相比二叉树，减少了中间节点空间占用，可存放更多指针，减少查询时的磁盘 IO 次数

• B+ 树：只有叶子节点存储数据，非叶子节点存放索引，增大了节点度值，提高查询命中率。叶子节点组成单链表，适合区间查找。MySQL 将叶子节点和非叶子节点保存在不同段上，段分区，区又分块

事务与并发控制

事务 ACID

事务的并发问题

假设有两个事务 T1 和 T2 并发执行，当它们处理同一数据时会引发问题：

• 脏读：T2 更新某列但未提交，T1 读取了该列。设想 T2 回滚后，T1 读取的仍是更新后的值，但这是临时的无效数据

• 不可重复读：T1 首次读取某列后，T2 对该列进行了更新并提交。T1 再次读取同一列时，值已发生变化

• 幻读：T1 首次读取某表后，T2 向表中插入了几行。T1 再读取该表时，发现多了几行

为避免这些问题，需要设置合适的数据库隔离级别。

事务隔离级别

事务实现的基本思想

使用预写日志记录（WAL）完成。维护一个日志记录数据库计划执行的操作（存储在硬盘或永久存储介质中，保证信息不因崩溃而丢失）。如果事务执行完成，删除日志中对应内容；如果中途崩溃，重启后重新执行日志中的完整事务。

这要求日志中的事务具有幂等性，无论执行多少次结果都相同。例如将用户余额变为特定值。事务的原子性指的是事务要么完成，要么数据处于原始状态。

死锁情况：假设订单系统包含 orders 表和 inventory 表。事务 1 先更新 orders 表再更新 inventory 表，事务 2 先更新 inventory 表再更新 orders 表，两个事务资源访问顺序不一致，更新同一行时容易导致死锁。遇到死锁时需要数据库具备撤销或回滚能力，回滚可通过逆向执行预写日志实现。

数据库复制（备份）是抵御数据丢失的重要手段，但也会引入复制品之间数据不一致的问题。一般多个复制品中会有一个是主节点。

锁的种类

按类型分

• 乐观锁：假设数据一般情况下不会发生冲突，在提交更新时才检测冲突
• 悲观锁：假设并发操作下必定发生冲突，在读取数据前就将数据锁住

按粒度分

• 行级锁
• 页级锁
• 表级锁

按作用分

• 共享锁：读操作时创建
• 排他锁：写操作时创建。假设事务 T 对某表进行查询并对结果集申请排他锁，其他线程对查询出的数据只能读不能修改或删除，直到事务 T 提交或回滚

数据库优化与扩展

水平切分与垂直切分

• 水平切分：例如一张表有 100 万条数据，切分为 10 个表，每个表共 10 万条数据。可通过地区、年份、索引取模实现

• 垂直切分：一张表数据条数不多但列数很多时，可按列拆分为多个表

• 分表原则：能不拆分就不拆分；列之间业务耦合性低时可垂直切分；百万级大表进行插入和查询耗时长时，可通过拆表提升性能

高并发解决方案

• 分表分库：将表水平拆分
• 增加缓存：将高频访问数据写入缓存
• 增加索引
• 主从读写分离：只在主服务器写，从服务器读
• 负载均衡集群：通过集群或分布式方法解决并发压力

SQL 优化

• 数据量越大，提升 I/O 越重要，例如使用短索引
• 优化索引和访问方式
• 检查是否出现死锁
• 优化查询语句
• 升级硬件、提高网速、扩大服务器内存

索引失效的情况

1. LIKE 查询以 % 开头时索引失效，变成全表扫描
2. 条件中有 OR，即使部分条件带索引也不会使用（尽量少用 OR）。想让索引生效，需将 OR 条件中每个列都加上索引
3. 字符串不加单引号索引会失效
4. 全表扫描比使用索引快时，不会使用索引
5. 使用 IS NOT NULL 或 IS NULL 会导致无法使用索引
6. 在索引列上做任何操作，会导致索引失效而全表扫描
7. 多列索引未使用第一部分（第一个），则不会使用索引

关联与关系型数据库

关系型数据库特点

• 易于理解
• 保持数据一致性
• 数据更新开销较小
• 支持 WHERE 等语句的查询

非关系型数据库特点

• 读写效率高
• 基于键值对，数据扩展性好
• 支持存储更多数据类型

SQLite 和 MySQL

两者都是常见的关系型数据库管理系统（RDBMS）。

异同点：

• 架构和部署方式

  • SQLite 是嵌入式数据库，以库形式集成到应用程序中，不需要独立服务器进程
  • MySQL 基于客户端/服务器模型，需要独立服务器进程，支持多用户并发访问

• 数据库类型

  • SQLite 基于文件，整个数据库存储在单个文件中
  • MySQL 基于服务器，数据存储在服务器上，可处理大量数据和复杂查询

• 扩展性和性能

  • SQLite 适用于小规模应用，并发访问和大规模数据处理能力有限
  • MySQL 具有良好扩展性和性能，可处理大量数据和高并发访问

• 安全性和权限管理

  • SQLite 安全性较低，通常用于本地应用，不支持用户和权限管理
  • MySQL 提供完善的用户和权限管理机制，可进行细粒度访问控制

SQLite 适用于小型、嵌入式应用；MySQL 适用于大型、多用户应用。

其他知识

连接池

适用于复杂的数据库应用，防止频繁建立、关闭连接操作，减少系统性能消耗。

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

作用：资源重用、更快的系统响应速度、新的资源分配手段、避免数据库连接资源泄露。

排序问题

假设要对班级语文成绩排序，出现同分情况时：

1. 重名次跳过：例如有三个同学都是第 2 名，下一个名次为第 5 名
2. 重名次不跳过：例如有三个同学都是第 2 名，下一个名次还为第 3 名

参考：https://dbfiddle.uk/p9Owpwod

ROUND 函数

ROUND 函数遵循"四舍六入五成双"的银行家舍入法。当舍去部分等于 0.5 时，向最近的偶数舍入。

1
2
3
4
5

SELECT ROUND(2.4);  -- 结果为2
SELECT ROUND(2.6);  -- 结果为3

SELECT ROUND(2.5);  -- 结果为2
SELECT ROUND(3.5);  -- 结果为4

负数情况同理：

1
2
3
4
5

SELECT ROUND(-2.4);  -- 结果为-2
SELECT ROUND(-2.6);  -- 结果为-3

SELECT ROUND(-2.5);  -- 结果为-2
SELECT ROUND(-3.5);  -- 结果为-4

ROUND 函数第二个参数可指定保留的小数位数：

1
2
3

SELECT ROUND(123.456, 1);   -- 123.5
SELECT ROUND(123.456, -1);  -- 120，舍入到个位
SELECT ROUND(123.456, 0);   -- 123

对于财务计算，建议使用 DECIMAL 类型而非 FLOAT/DOUBLE，并明确指定保留的小数位数。

SQL 注入防护

• 使用参数化查询或预编译语句
• 禁止直接拼接用户输入到 SQL 语句中

1
2
3
4
5
6
7

# 错误方式（易受注入攻击）
sql = "SELECT * FROM users WHERE id = " + user_input
cursor.execute(sql)

# 正确方式（参数化查询）
sql = "SELECT * FROM users WHERE id = %s"
cursor.execute(sql, (user_input,))

关于二叉树

N 层满二叉树（完全二叉树）的节点个数：

$$2^{0}+2^{1}+2^{2}+…+2^{N-1}=2^{N}-1$$

（等比数列求和公式：$S_{n}=\frac{a_{1}(1-q^{n})}{1-q}$）

N 个节点的完全二叉树深度为 $log_{2}{N}+1$

二叉树节点数计算公式：在二叉树中，节点的度指该节点拥有的子节点数量。设叶子节点数为 $n_{0}$，度为 1 节点数为 $n_{1}$，度为 2 节点数为 $n_{2}$：

• $N=n_{0}+n_{1}+n_{2}$：总节点数等于各类别节点数之和
• $N=n_{1}+2n_{2}+1$：各节点贡献的节点数之和（叶子节点贡献 0，度为 1 节点各贡献 1，度为 2 节点各贡献 2，根节点单独加 1）
• $n_{0}=n_{2}+1$：由上述公式推出

示例：

• 总节点数为 999（奇数）：999/2 = 499.5，叶子节点 500 个，度为 2 节点 499 个，度为 1 节点 0 个
• 总节点数为 1500（偶数）：1500/2 = 750，叶子节点 750 个，度为 2 节点 749 个，度为 1 节点 1 个

SQL Server 数据格式的转换问题

CAST 可以进行数据格式转换，但是如果原数据格式不统一造成转换失败，就会报错终止
而 TRY_CAST 即使转换失败也不会造成整个语句的报错，无法转换的时候返回 NULL

1
2

TRY_CAST('20250101' AS DATETIME)
TRY_CAST('2025/01/01 10:08:00' AS DATETIME)

TRY_PARSE 对于原数据格式的要求比较宽松
'Thursday, 19 Nov 2015' 这样的字符串也支持时间格式的转换
但是只支持字符串转 数值 或者 时间 类型

拓展与后续学习建议

• 数据库分区：深入学习 MySQL 的水平分区和垂直分区策略，理解分区表的工作原理
• 读写分离架构：了解主从复制的配置与原理，以及如何实现读写分离
• 数据库调优工具：学习使用 EXPLAIN 分析查询计划，使用慢查询日志进行性能诊断
• NoSQL 数据库对比：进一步了解 Redis、MongoDB 等非关系型数据库的特点与适用场景
• 分布式数据库：探索分布式数据库的基本概念，如分片、分布式事务等