2. 首页 > 国产数据库教程 > openGauss教程 > 正文

opengauss教程FG030-openGauss DML语句实战与性能注意

教程整理：风哥教程  |  更新时间：2026-04-10  |  教程分类：openGauss教程  |  文档学习：23

内容简介

本篇文章详细介绍openGauss数据库的DML（数据操纵语言）语句，包括INSERT、UPDATE、DELETE、MERGE等操作的实战应用以及性能优化注意事项。风哥教程参考opengauss官方文档SQL参考手册和性能调优指南。

DML语句是数据库操作的核心，直接影响数据的增删改查效率，对于生产环境的性能至关重要。通过本文的学习，您将掌握DML语句的最佳实践和性能优化技巧。

本文通过实战案例，详细讲解DML语句的使用方法、性能影响因素以及优化策略，帮助您在生产环境中高效使用DML语句。

目录大纲

Part01-基础概念与理论知识

1.1 DML语句概述

DML（Data Manipulation Language）是用于操纵数据库中数据的语言，主要包括INSERT、UPDATE、DELETE和MERGE等语句。DML语句直接操作表中的数据，是数据库日常操作的核心部分。

DML语句的主要特点：

  • 修改表中的数据，但不修改表结构
  • 执行后会产生事务日志
  • 可能会锁定相关数据，影响并发性能
  • 执行结果可以通过ROLLBACK回滚

1.2 DML语句类型

openGauss支持的DML语句主要包括：

  1. INSERT：向表中插入新数据
  2. UPDATE：更新表中的现有数据
  3. DELETE：删除表中的数据
  4. MERGE：根据条件插入或更新数据

1.3 DML语句执行原理

DML语句的执行过程包括：

  1. 解析SQL语句
  2. 生成执行计划
  3. 获取必要的锁
  4. 执行数据修改操作
  5. 生成事务日志
  6. 释放锁
  7. 提交或回滚事务

了解DML语句的执行原理，有助于我们优化DML操作的性能。

Part02-生产环境规划与建议

2.1 DML操作性能影响因素

影响DML操作性能的主要因素包括：

  • 数据量：操作的数据量越大，执行时间越长
  • 索引：索引可以加速查询，但会减慢插入和更新操作
  • 事务大小：大事务会占用更多资源，影响并发性能
  • 锁竞争：并发DML操作可能导致锁竞争，影响性能
  • 日志写入：DML操作会产生事务日志，影响I/O性能
  • 约束：表上的约束（如外键、唯一性约束）会增加DML操作的开销

风哥提示：

2.2 事务管理与DML

事务是DML操作的基本单位，合理的事务管理可以提高DML操作的性能和可靠性：

  • 事务大小：尽量将大事务拆分为小事务，减少资源占用
  • 事务隔离级别：选择合适的隔离级别，平衡一致性和性能
  • 提交频率：合理设置提交频率，避免频繁提交或长时间不提交
  • 回滚段：确保回滚段有足够的空间，避免回滚失败

2.3 锁机制与DML

DML操作会获取不同类型的锁，锁机制对DML性能有重要影响：

  • 行级锁：锁定单行数据，并发性能高
  • 页级锁：锁定数据页，适用于批量操作
  • 表级锁：锁定整个表，并发性能低
  • 意向锁：表示事务对表或页的锁定意向

合理的锁策略可以减少锁竞争，提高并发性能。

Part03-生产环境项目实施方案

3.1 批量插入优化策略

批量插入是常见的DML操作，优化策略包括：

  • 使用多值INSERT：一次插入多条记录，减少网络往返
  • 使用COPY命令：对于大量数据，COPY命令比INSERT更高效
  • 禁用索引：插入前禁用索引，插入后重建

    • 学习交流加群风哥微信: itpux-com

  • 调整批量大小：根据服务器性能调整批量插入的大小
  • 使用并行插入：在合适的场景下使用并行插入

3.2 更新操作性能优化

更新操作的优化策略包括：

  • 使用索引：确保WHERE子句中的列有索引
  • 减少更新列数：只更新必要的列
  • 批量更新：对于大量数据，使用批量更新
  • 避免全表更新：使用WHERE子句限制更新范围
  • 合理使用触发器：避免复杂的触发器影响更新性能

3.3 删除操作性能优化

删除操作的优化策略包括：

  • 使用索引：确保WHERE子句中的列有索引
  • 批量删除：对于大量数据，使用批量删除
  • 使用TRUNCATE：对于清空表，使用TRUNCATE比DELETE更高效
  • 分区表删除：对于分区表，可以直接删除分区
  • 避免级联删除：复杂的级联删除会影响性能

Part04-生产案例与实战讲解

4.1 INSERT语句实战

单条插入

— 单条插入
fgedudb=> INSERT INTO fgedu_employee (id, name, age, department, salary) VALUES (6, ‘周八’, 26, ‘技术部’, 7000);

INSERT 0 1

批量插入

学习交流加群风哥QQ113257174
— 批量插入
fgedudb=> INSERT INTO fgedu_employee (id, name, age, department, salary) VALUES
fgedudb-> (7, ‘吴九’, 29, ‘市场部’, 6800),
fgedudb-> (8, ‘郑十’, 31, ‘财务部’, 8500),
fgedudb-> (9, ‘王十一’, 27, ‘技术部’, 7200),
fgedudb-> (10, ‘赵十二’, 33, ‘市场部’, 6900);

INSERT 0 4

使用COPY命令批量导入

— 创建数据文件
# vim /opengauss/data/import_data/fgedu_employee_import.csv

11,孙十三,28,技术部,7300
12,李十四,30,市场部,6700
13,周十五,32,财务部,8600
14,吴十六,25,技术部,6900
15,郑十七,34,市场部,7100
— 使用COPY命令导入
fgedudb=> COPY fgedu_employee FROM ‘/opengauss/data/import_data/fgedu_employee_import.csv’ WITH (FORMAT ‘csv’);

COPY 5

4.2 UPDATE语句实战

单条更新

— 单条更新
fgedudb=> UPDATE fgedu_employee SET salary = salary * 1.1 WHERE id = 1;

UPDATE 1

更多视频教程www.fgedu.net.cn

批量更新

— 批量更新技术部员工工资
fgedudb=> UPDATE fgedu_employee SET salary = salary * 1.05 WHERE department = ‘技术部’;

UPDATE 5

条件更新

— 条件更新，只更新工资低于7000的员工
fgedudb=> UPDATE fgedu_employee SET salary = salary + 500 WHERE salary < 7000;

UPDATE 4

4.3 DELETE语句实战

单条删除

— 单条删除
fgedudb=> DELETE FROM fgedu_employee WHERE id = 15;

DELETE 1

批量删除

— 批量删除市场部员工
fgedudb=> DELETE FROM fgedu_employee WHERE department = ‘市场部’;

DELETE 3

使用TRUNCATE清空表

更多学习教程公众号风哥教程itpux_com
— 创建测试表
fgedudb=> CREATE TABLE fgedu_test (id INTEGER, name VARCHAR(50));

CREATE TABLE

— 插入测试数据
fgedudb=> INSERT INTO fgedu_test VALUES (1, ‘测试1’), (2, ‘测试2’), (3, ‘测试3’);

INSERT 0 3

— 使用TRUNCATE清空表
fgedudb=> TRUNCATE TABLE fgedu_test;

TRUNCATE TABLE

4.4 MERGE语句实战

MERGE语句用于根据条件插入或更新数据，是一种高效的UPSERT操作。

— 创建目标表
fgedudb=> CREATE TABLE fgedu_employee_target (
fgedudb(> id INTEGER PRIMARY KEY,
fgedudb(> name VARCHAR(50),
fgedudb(> age INTEGER,
fgedudb(> department VARCHAR(50),from DB视频:www.itpux.com
fgedudb(> salary INTEGER
fgedudb(> );

CREATE TABLE

— 插入初始数据
fgedudb=> INSERT INTO fgedu_employee_target VALUES (1, ‘张三’, 30, ‘技术部’, 8000), (2, ‘李四’, 25, ‘市场部’, 6000);

INSERT 0 2

— 使用MERGE语句
fgedudb=> MERGE INTO fgedu_employee_target t
fgedudb-> USING fgedu_employee s ON (t.id = s.id)
fgedudb-> WHEN MATCHED THEN
fgedudb-> UPDATE SET t.name = s.name, t.age = s.age, t.department = s.department, t.salary = s.salary
fgedudb-> WHEN NOT MATCHED THEN
fgedudb-> INSERT (id, name, age, department, salary) VALUES (s.id, s.name, s.age, s.department, s.salary);

MERGE 10

— 查看结果
fgedudb=> SELECT * FROM fgedu_employee_target;

id | name | age | department | salary
—-+——+—–+————+——–
1 | 张三 | 30 | 技术部 | 8800
2 | 李四 | 25 | 市场部 | 6000
3 | 王五 | 35 | 财务部 | 9000
4 | 赵六 | 28 | 技术部 | 8025
5 | 孙七 | 32 | 市场部 | 6500
6 | 周八 | 26 | 技术部 | 7350
7 | 吴九 | 29 | 市场部 | 6800
8 | 郑十 | 31 | 财务部 | 8500
9 | 王十一 | 27 | 技术部 | 7560
10 | 赵十二 | 33 | 市场部 | 6900
11 | 孙十三 | 28 | 技术部 | 7665
12 | 李十四 | 30 | 市场部 | 6700
13 | 周十五 | 32 | 财务部 | 8600
14 | 吴十六 | 25 | 技术部 | 7245
(14 rows)

Part05-风哥经验总结与分享

5.1 DML语句性能优化最佳实践

  • 批量操作：使用批量插入、更新和删除，减少网络往返和事务开销
  • 合理使用索引：为WHERE子句中的列创建索引，加速数据查找
  • 控制事务大小：将大事务拆分为小事务，提高并发性能
  • 优化锁策略：使用行级锁，减少锁竞争
  • 使用合适的导入工具：对于大量数据，使用COPY命令或其他导入工具
  • 避免不必要的约束：只在必要时使用约束，减少DML操作的开销
  • 定期维护表：执行VACUUM和ANALYZE，保持表的健康状态

5.2 常见DML性能问题与解决方案

问题 原因 解决方案
插入速度慢 索引过多、日志写入慢、批量大小不合理 禁用非必要索引、调整批量大小、使用COPY命令
更新操作慢 WHERE子句无索引、更新列过多、触发器复杂 为WHERE列创建索引、只更新必要列、简化触发器
删除操作慢 WHERE子句无索引、数据量过大、级联删除复杂 为WHERE列创建索引、使用批量删除、避免级联删除
锁竞争严重 事务过大、并发操作多、锁粒度不合理 减小事务大小、使用行级锁、优化并发控制

5.3 生产环境DML操作建议

  • 高峰期避免大DML操作：在业务低峰期执行大量数据的DML操作
  • 监控DML操作：使用数据库监控工具，监控DML操作的执行情况
  • 备份数据：在执行重要DML操作前，备份相关数据
  • 测试DML语句：在测试环境中测试DML语句的性能和正确性
  • 使用事务管理：合理使用事务，确保数据一致性
  • 定期性能分析：定期分析DML操作的性能，找出优化空间

风哥提示：在生产环境中执行DML操作时，一定要注意操作的影响范围，避免误操作导致数据丢失或性能问题。同时，要根据实际情况选择合适的DML语句和优化策略。

事务管理是DML操作的重要组成部分，风哥提示：在执行大量DML操作时，要合理控制事务大小，避免占用过多资源。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息