客户让我给她写个爬虫-dml语言原理（1）客户让我给她写个爬虫 SQL 数据操作原理：INSERT、UPDATE、DEL

SQL 数据操作原理：INSERT、UPDATE、DELETE 的背后

引言

在使用 MySQL 数据库时，我们经常使用 INSERT、UPDATE、DELETE 语句来操作数据。这些看似简单的操作背后，数据库系统进行了大量的工作。本文将深入解析这些操作的工作原理。

一、INSERT 操作原理

1.1 基本流程

语法解析
- 解析 SQL 语句
- 检查表结构
- 验证字段类型
数据验证
- 检查主键约束
- 验证唯一性约束
- 检查外键约束
- 验证非空约束
数据写入
- 分配新的数据页
- 写入数据记录
- 更新索引
- 写入事务日志

1.2 具体示例

INSERT INTO mall_info (mall_id, name, address)
VALUES ('123456', '万达广场', '北京市朝阳区');

执行过程：

检查 mall_id 是否已存在（唯一约束）
验证 name 和 address 的类型
在数据页中分配空间
写入数据记录
更新相关索引
记录事务日志

二、UPDATE 操作原理

2.1 基本流程

数据定位
- 使用索引定位记录
- 读取原始数据
- 验证更新条件
数据修改
- 创建新版本数据
- 更新索引
- 记录旧版本
- 写入事务日志
提交处理
- 更新统计信息
- 清理旧版本
- 释放空间

2.2 具体示例

UPDATE mall_info
SET address = '北京市朝阳区建国路88号'
WHERE mall_id = '123456';

执行过程：

通过 mall_id 索引定位记录
读取原始 address 值
创建新的数据版本
更新索引（如果 address 是索引字段）
记录事务日志
提交更改

三、DELETE 操作原理

3.1 基本流程

数据定位
- 使用索引定位记录
- 读取要删除的数据
- 验证删除条件
删除处理
- 标记记录为删除
- 更新索引
- 记录事务日志
- 维护引用完整性
空间回收
- 标记空间可重用
- 更新统计信息
- 可能的空间整理

3.2 具体示例

DELETE FROM mall_info
WHERE mall_id = '123456';

执行过程：

通过 mall_id 索引定位记录
检查外键约束
标记记录为删除
更新索引
记录事务日志
提交删除操作

四、事务处理

4.1 事务特性

原子性（Atomicity）
- 操作要么全部成功
- 要么全部失败
- 保证数据一致性
一致性（Consistency）
- 数据满足约束条件
- 保持业务规则
- 维护数据完整性
隔离性（Isolation）
- 并发事务互不影响
- 防止数据混乱
- 保证数据正确性
持久性（Durability）
- 事务提交后永久保存
- 系统故障不丢失
- 保证数据可靠性

4.2 事务日志

记录内容
- 操作类型
- 修改前的数据
- 修改后的数据
- 时间戳
日志作用
- 事务回滚
- 数据恢复
- 主从复制

五、性能考虑

5.1 批量操作

批量插入
- 减少事务开销
- 提高写入效率
- 优化日志记录
批量更新
- 减少索引更新
- 优化空间利用
- 提高并发性能
批量删除
- 优化空间回收
- 减少日志记录
- 提高删除效率

5.2 优化建议

插入优化
- 使用批量插入
- 禁用不必要的索引
- 选择合适的提交方式

更新优化

避免全表更新

使用 WHERE 条件限制更新范围

-- 不推荐
UPDATE mall_info SET status = 0;

-- 推荐
UPDATE mall_info SET status = 0 WHERE city = '北京';

通过索引字段定位记录

-- 不推荐
UPDATE mall_info SET address = '新地址' WHERE name LIKE '%广场%';

-- 推荐
UPDATE mall_info SET address = '新地址' WHERE mall_id = '123456';

分批更新大量数据

-- 不推荐
UPDATE mall_info SET status = 1 WHERE create_time < '2024-01-01';

-- 推荐
UPDATE mall_info SET status = 1
WHERE create_time < '2024-01-01'
LIMIT 1000;

使用 JOIN 替代子查询

-- 不推荐
UPDATE mall_info
SET status = 0
WHERE mall_id IN (SELECT mall_id FROM mall_log WHERE type = 'closed');

-- 推荐
UPDATE mall_info m
JOIN mall_log l ON m.mall_id = l.mall_id
SET m.status = 0
WHERE l.type = 'closed';

避免使用函数和运算

-- 不推荐
UPDATE mall_info
SET update_time = DATE_ADD(update_time, INTERVAL 1 DAY);

-- 推荐
UPDATE mall_info
SET update_time = '2024-01-02'
WHERE update_time = '2024-01-01';

使用合适的索引
控制更新范围

删除优化
- 使用批量删除
- 考虑使用 TRUNCATE
- 注意外键约束

结语

理解 SQL 数据操作的原理对于优化数据库性能至关重要。通过了解 INSERT、UPDATE、DELETE 操作的工作机制，我们可以更好地设计数据库结构，编写高效的 SQL 语句，提高系统的整体性能。