数据库知识点个人笔记

一、数据库基础操作：增删改查（CRUD）

CRUD是数据库操作的基石，所有业务数据交互本质上都是这四种操作的组合，重点在于理解语法规范与业务场景的适配，避免出现数据异常。

1. 新增（Create）

核心作用是向数据表中插入新的记录，分为插入单个元组和插入子查询结果两种常见方式，需注意字段顺序、数据类型匹配，以及非空字段的赋值要求。

基础语法（以MySQL为例）：

// 插入单个元组，指定字段顺序

INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

// 插入单个元组，不指定字段（需与表字段顺序一致）

INSERT INTO 表名 VALUES (值1, 值2, ...);

// 插入子查询结果，将查询结果批量插入新表

INSERT INTO 目标表 (字段1, 字段2) SELECT 字段1, 字段2 FROM 源表 GROUP BY 字段1;

备注：插入时需遵守表的完整性规则，比如非空字段（NOT NULL）必须赋值，主键字段需保证唯一，字符串常量需用英文单引号括起。

2. -- 示例1：向用户表（user）插入单个元组（指定字段）
3. CREATE TABLE IF NOT EXISTS `user` (
4. `id` INT PRIMARY KEY, -- 主键，唯一且非空
5. `name` VARCHAR(50) NOT NULL, -- 非空字段
6. `age` INT,
7. `gender` VARCHAR(10)
8. );
9. INSERT INTO `user` (id, name, age, gender) VALUES (1001, '张三', 22, '男');
11. -- 示例2：插入多个元组（批量插入）
12. INSERT INTO `user` (id, name, age, gender)
13. VALUES (1002, '李四', 23, '女'), (1003, '王五', 24, '男');
15. -- 示例3：插入子查询结果（将学生表中年龄>20的学生插入用户表）
16. CREATE TABLE IF NOT EXISTS `student` (
17. `sid` INT,
18. `sname` VARCHAR(50),
19. `sage` INT
20. );
21. INSERT INTO `student` VALUES (1, '赵六', 21), (2, '孙七', 19);
22. INSERT INTO `user` (id, name, age)
23. SELECT sid, sname, sage FROM `student` WHERE sage > 20;

复制代码

2. 查询（Read）

核心作用是从数据表中获取所需数据，是日常开发中使用频率最高的操作，可结合过滤、排序、分组、关联等需求灵活组合。

基础语法：SELECT 字段1, 字段2 FROM 表名 WHERE 条件 ORDER BY 字段 DESC LIMIT 条数;

关键技巧：避免使用SELECT * ，指定具体字段可减少网络传输；利用WHERE子句过滤无效数据，ORDER BY实现排序，LIMIT控制返回条数，提升查询效率。

2. -- 示例1：基础查询（查询指定字段，过滤+排序+限制条数）
3. SELECT id, name, age FROM `user`
4. WHERE age > 18 AND gender = '男'
5. ORDER BY age DESC
6. LIMIT 10;
8. -- 示例2：分组查询（统计各性别用户数量）
9. SELECT gender, COUNT(id) AS user_count
10. FROM `user`
11. GROUP BY gender
12. HAVING user_count > 1; -- 过滤分组结果
14. -- 示例3：关联查询（关联用户表和订单表，查询用户及其订单）
15. CREATE TABLE IF NOT EXISTS `order` (
16. `order_id` BIGINT PRIMARY KEY,
17. `user_id` INT,
18. `order_time` DATETIME,
19. FOREIGN KEY (user_id) REFERENCES `user`(id)
20. );
21. INSERT INTO `order` VALUES (10001, 1001, '2026-04-13 10:00:00'), (10002, 1002, '2026-04-13 11:00:00');
22. SELECT u.name, o.order_id, o.order_time
23. FROM `user` u
24. LEFT JOIN `order` o ON u.id = o.user_id;
26. -- 示例4：模糊查询（查询姓名包含"张"的用户）
27. SELECT id, name FROM `user` WHERE name LIKE '%张%';

复制代码

3. 修改（Update）

核心作用是更新数据表中已存在的记录，必须搭配WHERE条件（除非确需批量更新所有记录），否则会导致全表数据修改，风险极高。

基础语法：UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE 条件;

备注：修改时需注意数据类型匹配，比如将字符串值赋给数值型字段会报错；对于关联表的修改，需先确认关联关系，避免出现数据不一致。

2. -- 示例1：修改单个字段（指定条件）
3. UPDATE `user` SET age = 23 WHERE id = 1001;
5. -- 示例2：修改多个字段（批量修改符合条件的记录）
6. UPDATE `user`
7. SET age = age + 1, gender = '男'
8. WHERE name LIKE '李%';
10. -- 示例3：关联修改（修改用户1001的所有订单状态为"已完成"）
11. UPDATE `order` o
12. JOIN `user` u ON o.user_id = u.id
13. SET o.order_status = '已完成'
14. WHERE u.id = 1001;
16. -- 注意：禁止无WHERE条件修改（会全表更新）
17. -- UPDATE `user` SET age = 20; -- 危险操作，谨慎使用

复制代码

4. 删除（Delete）

核心作用是删除数据表中不需要的记录，同样必须搭配WHERE条件，谨慎使用，避免误删数据（建议删除前先执行查询，确认待删除记录无误）。

基础语法：DELETE FROM 表名 WHERE 条件;

补充：TRUNCATE TABLE 表名 也可删除全表数据，但会清空自增主键，且无法撤销，而DELETE删除全表数据可通过事务回滚撤销，根据业务场景选择使用。

2. -- 示例1：删除指定条件的记录（删除前先查询确认）
3. SELECT * FROM `user` WHERE age < 18; -- 确认待删除记录
4. DELETE FROM `user` WHERE age < 18;
6. -- 示例2：删除关联数据（先删从表，再删主表，避免外键约束报错）
7. DELETE FROM `order` WHERE user_id = 1003; -- 先删订单（从表）
8. DELETE FROM `user` WHERE id = 1003; -- 再删用户（主表）
10. -- 示例3：删除全表数据（可回滚）
11. BEGIN; -- 开启事务
12. DELETE FROM `user`;
13. ROLLBACK; -- 若误删，可回滚恢复
15. -- 示例4：清空全表+重置自增主键（不可回滚）
16. TRUNCATE TABLE `user`; -- 自增主键重新从1开始

复制代码

二、数据库核心基础：数据类型

数据类型是定义表字段的基础，直接影响数据存储效率、查询性能和数据完整性，需根据业务需求选择合适的类型，避免过度占用资源。常见数据类型分为以下几大类，结合主流数据库实现整理：

1. 数值类型

用于存储整数、小数等数值数据，核心是根据数值范围选择对应类型，避免浪费存储空间。

// 整数类型（常用）

TINYINT：1字节，范围-128~127（有符号），适合存储状态值（如0=禁用、1=启用）；

INT：4字节，范围-2147483648~2147483647，适合存储普通整数（如用户ID、年龄）；

BIGINT：8字节，适合存储大范围整数（如订单号、时间戳）；

// 小数类型

DECIMAL(p,s)：精确小数，p为总位数，s为小数位数，适合存储金额、精度要求高的数值（如订单金额、单价）；

FLOAT/DOUBLE：近似小数，适合存储精度要求不高的数值（如温度、体重）。

2. 字符串类型

用于存储文本数据，核心区分固定长度和可变长度，避免存储冗余。

CHAR(n)：固定长度，n为字符数，适合存储长度固定的数据（如手机号、身份证号），查询效率高；

VARCHAR(n)：可变长度，n为最大字符数，适合存储长度不固定的数据（如姓名、地址），节省存储空间；

TEXT：适合存储长文本（如文章内容、备注），分为TINYTEXT、TEXT、LONGTEXT，根据文本长度选择。

3. 日期时间类型

用于存储时间相关数据，需根据时间精度需求选择，避免格式混乱。

DATE：存储日期（年-月-日），适合存储生日、注册日期；

TIME：存储时间（时:分:秒），适合存储具体时间点（如打卡时间）；

DATETIME：存储日期+时间（年-月-日 时:分:秒），适合存储大部分时间场景（如订单创建时间、修改时间）；

TIMESTAMP：存储日期+时间，自动关联时区，适合跨时区场景。

4. 二进制类型

用于存储二进制数据（如图片、文件），常用BLOB类型，分为TINYBLOB、BLOB、LONGBLOB，根据文件大小选择，注意：数据库一般不建议存储大文件，通常存储文件路径，文件本身存在服务器或对象存储中。

三、数据库数据安全：备份机制（全量+增量+差量+二进制）

数据备份是数据库运维的核心，用于应对数据丢失、误操作、系统故障等场景，常用的备份方式包括全量备份、增量备份、差量备份和二进制备份，其中全量备份为基础，后三者常与全量备份结合使用，全方位保障数据可恢复性。

1. 全量备份（Full Backup）

定义：对数据库中所有数据（包括表结构、数据记录、索引等）进行完整备份，是所有备份策略的基础，不依赖任何历史备份记录，可独立用于数据恢复。

核心原理：通过扫描数据库所有数据文件，将完整的数据副本保存到备份介质（如磁盘、磁带、云存储），备份过程中会锁定数据（或采用热备份技术减少影响），确保备份数据的一致性。

特点：

优点：恢复流程简单，无需依赖其他备份，直接还原全量备份即可恢复所有数据，适合数据量较小或对恢复速度要求高的场景；缺点：备份速度慢、占用存储空间大，频繁备份会消耗大量系统资源（CPU、I/O），适合定期执行（如每日凌晨、每周一次）。

2. -- MySQL全量备份（使用mysqldump命令，终端执行）
3. -- 备份整个数据库实例
4. mysqldump -u root -p --all-databases > /backup/all_database_backup_20260413.sql
6. -- 备份指定数据库（如test_db）
7. mysqldump -u root -p test_db > /backup/test_db_full_backup_20260413.sql
9. -- 备份指定表（如test_db的user表）
10. mysqldump -u root -p test_db user > /backup/test_db_user_full_backup_20260413.sql
12. -- 全量备份恢复（终端执行）
13. mysql -u root -p test_db < /backup/test_db_full_backup_20260413.sql

复制代码

2. 差量备份（Differential Backup）

定义：每次只备份自上一次全量备份后发生变化的数据，核心区别于增量备份——增量备份以上一次任意备份（全量或增量）为基准，差量备份仅以上一次全量备份为基准。

核心原理：以最近一次全量备份为基线，通过对比全量备份数据与当前数据，仅捕获新增、修改的数据块，无需记录历史增量备份的变更，依赖数据库的变更标记或快照对比技术实现。

特点：

优点：备份速度快于全量备份，占用空间小于全量备份；恢复流程比增量备份简单，只需还原最近一次全量备份+最近一次差量备份，无需依次还原所有增量备份；缺点：备份体积会随全量备份后的数据变更量增加而增大，长期使用会占用较多存储空间。

2. -- MySQL差量备份（基于全量备份，使用xtrabackup工具，终端执行）
3. -- 1. 先执行全量备份（作为基线）
4. xtrabackup --user=root --password=123456 --backup --target-dir=/backup/full_backup_20260413
6. -- 2. 执行差量备份（以上一次全量备份为基准）
7. xtrabackup --user=root --password=123456 --backup --target-dir=/backup/diff_backup_20260413 --incremental-basedir=/backup/full_backup_20260413
9. -- 差量备份恢复（终端执行）
10. -- 先准备全量备份
11. xtrabackup --user=root --password=123456 --copy-back --target-dir=/backup/full_backup_20260413
12. -- 再准备差量备份并合并
13. xtrabackup --user=root --password=123456 --copy-back --target-dir=/backup/diff_backup_20260413

复制代码

3. 增量备份

定义：每次只备份自上一次备份（全量或增量）后发生变化的数据，核心思路是“只记录变更”，无需重复备份未变更数据。

核心原理：以一次全量备份为基线，后续每次备份仅保存新增、修改的的数据块或操作记录，依赖数据库的变更日志（如binlog、WAL日志）或快照技术实现。

特点：

优点：备份速度快、占用存储空间小，适合高频备份（如每小时备份），减少资源消耗；

缺点：恢复流程复杂，需先还原最近一次全量备份，再依次还原后续所有增量备份，若其中一个增量备份丢失，后续数据无法正常恢复。

常见类型：文件级增量（适合简单文件备份）、块级增量（适合数据库物理文件）、日志型增量（适合数据库变更备份）、CDC型增量（适合跨源实时同步）。

2. -- MySQL增量备份（基于binlog日志，终端执行）
3. -- 1. 查看当前binlog日志文件
4. show master status; -- 记录当前日志文件（如mysql-bin.000001）和位置（如156）
6. -- 2. 备份指定时间段的binlog（增量备份）
7. mysqlbinlog --start-datetime="2026-04-13 00:00:00" --stop-datetime="2026-04-13 12:00:00" /var/lib/mysql/mysql-bin.000001 > /backup/increment_backup_20260413.sql
9. -- 3. 备份指定位置后的binlog（增量备份）
10. mysqlbinlog --start-position=156 /var/lib/mysql/mysql-bin.000001 > /backup/increment_backup_20260413_pos.sql
12. -- 增量备份恢复（终端执行，需先恢复全量备份）
13. mysql -u root -p test_db < /backup/test_db_full_backup_20260413.sql
14. mysql -u root -p test_db < /backup/increment_backup_20260413.sql

复制代码

2. 二进制备份

定义：备份数据库的二进制日志（binlog），二进制日志是数据库的核心日志，记录了所有对数据的变更操作（INSERT、UPDATE、DELETE、DDL等），不记录查询操作，是增量备份和主从复制的基础。

核心作用：

1. 数据恢复：当数据库发生数据丢失时，可通过全量备份+二进制日志回放，恢复到指定时间点的数据；

2. 主从复制：主库将二进制日志同步到从库，从库通过回放日志实现与主库的数据一致。

特点：

优点：体积小、备份简单，可实现精准的时间点恢复，是数据库高可用的核心支撑；

缺点：仅记录变更操作，无法单独用于数据恢复，必须搭配全量备份或增量备份使用。

备注：MySQL默认开启二进制日志，可通过配置文件调整日志格式（SBR、RBR、MIXED），推荐使用ROW格式（RBR），保证主从数据一致性。

2. -- 1. 查看二进制日志配置
3. show variables like 'log_bin'; -- 查看是否开启binlog（ON为开启）
4. show variables like 'binlog_format'; -- 查看binlog格式
6. -- 2. 临时修改binlog格式（重启失效）
7. set global binlog_format = 'ROW'; -- 改为ROW格式（推荐）
9. -- 3. 永久修改binlog格式（修改my.cnf配置文件）
10. [mysqld]
11. log_bin = /var/lib/mysql/mysql-bin -- 开启binlog，指定日志存储路径
12. binlog_format = ROW -- 日志格式为ROW
13. server-id = 1 -- 主库必须配置，唯一标识
15. -- 4. 查看binlog日志内容（查看具体变更操作）
16. mysqlbinlog /var/lib/mysql/mysql-bin.000001
18. -- 5. 通过binlog恢复数据（恢复指定操作）
19. mysqlbinlog --start-position=156 --stop-position=300 /var/lib/mysql/mysql-bin.000001 | mysql -u root -p test_db

复制代码

四、数据库高可用：读写分离场景

当系统并发量提升，单库无法承载大量读写请求时，读写分离是常用的性能优化方案，核心是将读操作和写操作分配到不同的数据库节点，实现负载分流，提升系统吞吐量和可用性。

1. 核心原理

采用“主从架构”，主库（Master）负责处理所有写操作（INSERT、UPDATE、DELETE）和核心读操作，从库（Slave）负责处理大部分读操作（SELECT），主库通过二进制日志将数据变更同步到从库，保证主从数据一致。

2. 常见应用场景

读写分离的核心适用场景是“读多写少”，以下是典型场景：

1. 电商场景：商品详情页、商品列表、用户个人中心等，读请求量远大于写请求（下单、修改收货地址为写操作），可将读请求分流到从库，缓解主库压力；

2. 新闻/内容平台：文章浏览、评论查看为读操作，文章发布、评论提交为写操作，读请求并发量极高，适合读写分离；

3. 后台管理系统：日常查询（如订单查询、用户查询）为读操作，数据录入、修改为写操作，读多写少，可通过读写分离提升查询响应速度；

4. 高并发查询场景：如报表统计、数据查询接口，读请求密集，可部署多个从库分担读负载，支持水平扩展，应对百万级QPS需求。

3. 注意事项

- 数据一致性：主从同步存在一定延迟（毫秒级到秒级），若业务要求强一致性（如支付后立即查询订单状态），需将该读请求路由到主库；

- 路由实现：可通过中间件（如MyCat、ShardingSphere、ProxySQL）实现读写分离路由，自动将读请求分发到从库，写请求分发到主库；

- 从库扩容：当读请求进一步增加时，可新增从库节点，实现读负载的进一步分流。

五、数据库复制机制：异步复制与主从复制

数据库复制是实现读写分离、高可用的基础，核心是将主库的数据变更同步到从库，分为异步复制和主从复制（基于异步复制实现），以下详细梳理核心知识点。

1. 异步复制：常见复制类型

定义：主库完成数据变更操作后，无需等待从库确认接收日志，立即向客户端返回成功响应，从库异步拉取主库的二进制日志并回放，实现数据同步，是最常用的复制方式。

核心特点：主库性能不受影响，但可能存在主从数据延迟，极端情况下（主库宕机）可能丢失少量未同步的数据。

常见复制类型：

1. 基于二进制日志的异步复制（MySQL）：主库将变更记录到binlog，从库通过I/O线程拉取binlog，写入本地relay log，再通过SQL线程回放日志，实现同步；

2. 基于WAL日志的异步流复制（PostgreSQL）：主库将变更记录到WAL日志，从库异步拉取WAL日志并应用，实现数据同步；

3. 副本集异步复制（MongoDB）：副本集中的主节点处理写操作，从节点异步拉取主节点的oplog（操作日志）并应用，实现数据冗余和故障转移；

4. Kafka+Debezium异步复制：通过Debezium监听数据库变更，将变更事件发布到Kafka，从库或其他系统消费Kafka消息，实现跨异构数据源的异步复制，适用于微服务架构的数据同步。

2. 主从复制原理

主从复制本质是“日志传递与重放”的过程，核心分为三个步骤，以MySQL为例（基于异步复制实现），类比快递系统更易理解：主库写日志（快递单）、从库拉日志（快递中转）、从库回放日志（快递派送）。

具体步骤：

1. 主库生成二进制日志（binlog）：主库执行任何数据变更操作（INSERT、UPDATE、DELETE、DDL）后，都会将操作记录按指定格式（SBR、RBR、MIXED）写入binlog，binlog是主从复制的核心载体；

2. 从库拉取并保存中继日志（relay log）：从库启动I/O线程，连接主库，实时拉取主库的binlog，将其写入本地的relay log（中继日志），relay log相当于“中转仓”，避免直接操作binlog导致日志损坏；

3. 从库回放日志：从库启动SQL线程，读取relay log中的操作记录，逐条执行，将主库的变更同步到从库，最终实现主从数据一致。

关键补充：

- 线程机制：MySQL 5.7之前为“单I/O线程+单SQL线程”，存在同步延迟；5.7+引入并行复制，支持多个SQL线程并行回放日志，降低主从延迟；

- binlog格式：SBR（记录SQL语句）日志体积小但可能导致主从不一致；RBR（记录行变更）数据一致但日志体积大；MIXED（自动切换）折中方案，适合大部分场景