MySQL 大DML操作建议

优化大数据量操作：INSERT、UPDATE、DELETE技巧
最新推荐文章于 2024-06-13 22:00:00 发布
最新推荐文章于 2024-06-13 22:00:00 发布
阅读量2k 收藏 1
点赞数 1
CC 4.0 BY-SA版权
分类专栏： MySQL 操作与管理 MySQL 优化与深入
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/dba_waterbin/article/details/9473263
本文提供了一系列优化策略，针对大数据量的INSERT、UPDATE和DELETE操作进行优化，包括使用多行插入、LOAD DATA INFILE、临时表、分散事务提交等方法。对于大数据量的UPDATE操作，先disable索引和相关约束，再通过SELECT变更字段、写入临时表、创建复合索引等步骤实现高效更新。此外，还提供了大数据量DELETE操作的优化技巧，如分区表和通过唯一标识批量删除。
     ㈠ 大数据量INSERT
     
     ⑴ 使用多行插入代替单行：insert into t values (),(),(),...
     
     ⑵ LOAD DATA INFILE ... INTO TABLE t
     
     ⑶ 写入临时表、在 INSERT ... SELECT 
     
     ⑷ 取消索引、约束，等数据加载完后在重建
     
     ⑸ INSERT时指定DELAYED属性
     
     ⑹ 对于InnoDB、可以分散INSERT到多个事务、批量提交
     
     
     ㈡ 大数据量UPDATE
        
        这之前也应该disable掉索引和相关约束
        
     ① SELECT 唯一字段,变更字段
     ② 把结果集写入临时表
     ③ 为临时表创建复合索引
     ④ 循环临时表的每条记录
     ⑤ 依据临时表的唯一字段、更新目标表的变更字段
     
     
     ㈢ 大数据量DELETE
     
     ① 分区表

     ② 先把需要删除的数据的唯一标识取到临时表、游标循环批量delete



By DBA_WaterBin

2013-07-25

Good Luck

mysql并行dml_Oracle DML并行 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜...
weixin_33011357的博客
02-08 185
1、UPDATE 操作SQL> conn chf/xifenfeiConnected.SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei';Explained.SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL,...
MySQL数据库基本操作-DDL和DML
Starry__Sky222的博客
07-08 3179
DDL解释 对数据库的常用操作 对表结构的常用操作-创建表 对表结构的常用操作—其他操作 对表结构的常用操作-修改表结构格式 DML基本介绍 数据插入 数据修改 数据删除 MySQL的使用演示 MySQL的编码设置 MySQL图形化管理工具 MySQL目录结构与源码 主要目录结构 MySQL 源代码获取 常见问题的解决 重置密码 MySQL 字符集
Mysql批量执行dml语句(PHP)
weixin_30911809的博客
06-07 151
View Code 1 <?php 2 3 $mysqli=new MySQLi("localhost","root","root","database"); 4 if($mysqli->connect_error){ 5 die($mysqli->connect_error); 6 } 7 ...
MySQL 大表在线DML神器--pt-online-schema-change
yb223731的博客
10-23 999
https://blog.youkuaiyun.com/mchdba/article/details/76253016 一个朋友问我在线对大表进行ddl操作，如何做能尽量避免主从延迟以及不影响在线dml操作呢？我想到一个开源的pt-online-schema-change工具，测试了吧，效果还可以。 pt-online-schema-change原理 1、如果存在外键，根据alter-foreign-ke...
mysql并行dml_dml并行
weixin_36131079的博客
02-02 375
Enabling Parallel DMLA DML statement can be parallelized only if you have explicitly enabled parallel DML in the session, as in the following statement:关于dml并行必须在会话层面明确指定，不指定都是位并行ALTER SESSION ENABLE ...
mysql中的DML
weixin_43054790的博客
07-26 573
– 添加数据INSERT INTO – 添加指定列数据 INSERT INTO player(pname,page) VALUES (‘唐荣’,25); – 添加所有列，可以省略列名 INSERT INTO player VALUES (3,‘唐荣’,25,NULL); – 所有列值都可以使用’’,字符串和日期类型则必须使用’’； INSERT INTO player VALUES (‘7’,‘张...
【数据库技术】MySQL单表查询DML语句详解：常用操作与聚合函数应用实例汇总
最新发布
08-20
内容概要：本文档主要介绍MySQL DML语言中的单表查询操作。文档详细讲解了select语句的完整语法及其多种用法，包括查询特定字段、使用distinct去重、带条件的查询(where)、排序(order by)、模糊查询(like)、分组查询...
python操作Mysql数据库 和 Mysql数据库DDL和DML基础
m0_64489692的博客
06-21 1200
本次博客主要讲解通过`pymysql`来操作数据库，以及Mysql数据库DDL和DML基础
MySQL数据操作与查询- DDL和DML
ArrayAda的博客
06-13 967
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件表达式。insert into 表名(字段列表) values(值列表1,值列表2,...,值列表n)SQL也是数据库脚本文件的扩展。将客户表（customers）中的“客户业务”字段进行内容更新，长度更新为100，将客户表（customers）中编号为1001的客户名称修改为“华为技术有限公司”将客户表（customers）中客户姓名为“腾讯”的记录删除。将专业表（major）中名称为“电子商务”的记录删除。
MySQL进阶十：DML-更新
01-20
在MySQL数据库管理系统中，DML（Data Manipulation Language）用于处理数据，包括插入、更新和删除等操作。本文将深入探讨DML中的一个重要命令——UPDATE，用于更新已存在的记录。UPDATE语句允许我们修改数据库表中...
mysql并行dml_MySql的回顾九DML表数据的增,删,改,与DDL库表的增删改，约束
weixin_31463861的博客
02-28 141
雷霆空霹雳，云雨竟虚无。炎赫衣流汗，低垂气不苏。杜圣人热的时候不知是怎么解暑的？？？但这边这几天天气好像一直都不是太好哎！不过无所谓了，都过了这么长时间，也习惯了，我还是继续带各位看官复习MySql的DML与DDL语言。DML语言1.插入方式一语法:INSERTINTO表名(字段名,...)VALUES(值,...);特点:①要求值得类型和字段的类型要一直或兼容。兼容【‘123’=123】...
mysql dml ddl优先级_mysql DDL与DML的并发关系
weixin_32734143的博客
02-26 461
概念一、修改列1、修改列数据类型，还包括修改长度如varchar(10)==>varchar(20) 是不允许dml并发的2、修改列名并且同时设置它为外键的时候是不允许dml并发的，若只是单纯修改列名就可以3、添加是自增列的时候不允许和dml并发，若只是添加列则可以！4、指定字符集/修改列的字符编码时不允许和dml并发5、设置null、not null；设置、删除列默认值 都是允许dml并发...
mysql使用DML语句操作数据
热门推荐
sikh_0529的博客
08-30 1万+
mysql使用DML语句操作数据
Mysql数据库DML汇总
Mr_Maxiansheng的博客
11-24 207
DML 添加数据到表中的两种方式 只给部分列的数据： insert into 表名（列名1，列名2.....列名n）values （列名1数据，列名2 数据,......列名n数据 ); 给出所有列的 数据： insert into 表名 values （列名1数据，.......列名n 数据）； 删除表中数据 删除表中的数据： delet...
mysql中设置支持中文和批量DML语句
自由理想的足迹
06-02 268
1.mysql中设置功能中设置为GBK支持中文。 2.UPDATE语句报错(用法问题）： [SQL]  UPDATE tb_stuinfo SET stuName='王维' AND stuSex='男'AND stuHomeAddr='山西祁县'WHERE id=1; [Err] 1292 - Truncated incorrect DOUBLE value: '王维' 百度发现129...
数据库-MySQL-基础（3）-DML操作
莫浅子的博客
09-27 1127
王道课程，DML,用来对数据库中的表的数据记录进行增删改操作。DML介绍 DML-添加数据 1、给指定字段添加数据 2、给全部字段添加数据 3、批量添加数据 4、DML-修改数据 5、DML-删除数据
如何优雅的实现DML批量操作
杨建荣的学习笔记
09-19 329
这是学习笔记的第2105篇文章 昨天处理了一个业务同学的数据需求，简单来说就是对一张大表做一下数据清理，数据量在8千万左右，需要保留近一个月的数据，大概是4...
高性能MySQL —— 并发控制
GGGGGINALEX的博客
08-18 455
当执行一个UPDATE或DELETE操作并且命中了索引（无论是唯一索引还是非唯一索引），InnoDB会尝试仅仅对满足条件的那些行加锁。对于非唯一索引，会对查询结果范围左侧的间隙和右侧的间隙加间隙锁（避免重复值的插入）bin log是实现主从复制的核心，主服务器上的所有数据更改（在Binary Log中记录）会被复制到从服务器。脏页是指还未来的及刷新到磁盘的buffer pool中的数据。update读的是当前（最新的）。正是MVCC的存在，MySQL在可重复读的隔离级别解决了幻读问题。
MySQL基础DML（三）
时长两年半的Bug制造者
08-29 213
二、DML语句—数据操作语言—data manipulation language insert into增---插入数据 show 查---显示 update改-- 更新/修正 delete （from） 删---从....上删除 truncate删---被删节---缩短 1.插入记录 1.1 插入全部字段 所有的字段名都写出来 INSERT INTO 表名 (字段名1, 字段名2, 字段名...
mysql dml 转 oracle
04-26
<think>好的，我现在需要帮助用户将MySQL的DML语句迁移到Oracle数据库。首先，我得回顾一下用户提供的引用内容，看看里面有哪些有用的信息。引用3提到了MySQL和Oracle在数据类型、自增主键、分页等方面的差异，这些是转换DML语句时需要注意的关键点。引用5提到没有主键的表在迁移时可能遇到的问题，但用户现在的问题是关于DML语句的转换，所以主键的问题可能暂时不用考虑，除非用户的语句涉及自增主键。 用户的需求是将MySQL的DML语句转换为Oracle兼容的。DML主要包括INSERT、UPDATE、DELETE和SELECT语句。我需要分步骤考虑每个部分可能存在的差异。 首先是INSERT语句。MySQL使用AUTO_INCREMENT来生成自增主键，而Oracle需要使用序列和触发器。例如，MySQL中的表可能有一个自增ID，而Oracle中需要先创建序列，然后在插入时使用序列的NEXTVAL。引用3提到这一点，所以需要指导用户如何修改INSERT语句，将自增字段替换为序列。 然后是UPDATE和DELETE语句，这部分可能差异不大，但需要注意WHERE子句中的限制。比如，Oracle不支持LIMIT子句，所以如果用户的UPDATE或DELETE语句中有LIMIT，需要改用其他方法，比如子查询或ROWNUM。引用3提到分页使用ROWNUM，但这里可能涉及到修改WHERE条件。 接下来是SELECT语句，特别是分页。MySQL使用LIMIT，而Oracle使用ROWNUM或者12c之后的OFFSET-FETCH。用户可能需要将LIMIT 10 OFFSET 20转换为Oracle的写法，比如使用子查询和ROWNUM，或者更现代的语法。另外，引用4中的日期转换函数不同，MySQL的DATE_FORMAT在Oracle中是TO_CHAR，而STR_TO_DATE对应TO_DATE，需要注意格式字符串的差异。 数据类型方面，引用3提到数字类型和字符串类型的不同，比如MySQL的INT对应Oracle的NUMBER，VARCHAR对应VARCHAR2。在DML语句中，如果涉及到类型转换，可能需要调整字段类型或者使用转换函数。 还有连表查询，MySQL使用JOIN，而Oracle可以使用+符号进行外连接，但用户可能更倾向于使用ANSI标准的JOIN语法，Oracle也支持，所以这里可能不需要修改，除非用户使用了特定的旧式语法。 此外，还需要考虑函数和操作符的差异，比如MySQL的CONCAT()在Oracle中可以用||运算符，或者CONCAT函数但参数限制不同。日期函数如NOW()要换成SYSDATE，引用4中的例子显示Oracle使用TO_DATE，所以日期字符串的转换需要注意格式。 事务处理方面，Oracle默认不会自动提交，而MySQL的autocommit可能是开启的，但这对DML语句本身影响不大，除非用户有特定的提交或回滚需求。 还有引号的处理，Oracle使用单引号，和MySQL一样，但标识符的处理可能不同，比如Oracle默认是大写的，而MySQL是大小写敏感的，不过这在DML语句中可能影响不大，除非用户使用了带引号的标识符。 接下来，我需要整理这些差异点，并给出具体的转换示例。比如，插入语句中的自增处理，分页的修改，日期函数的替换等。同时，要提醒用户注意测试和验证，因为某些语法差异可能在复杂查询中导致意外错误。 可能还需要提到工具的使用，比如使用SQL转换工具或者ORM框架来减少手动修改的工作量，但用户的问题主要是手动转换，所以重点放在语法差异上。 最后，检查是否有遗漏的部分，比如存储过程或触发器的转换，但用户明确提到DML语句，所以可能不需要涉及。不过如果用户有使用特定于MySQL的函数，如GROUP_CONCAT，可能需要找到Oracle中的替代方案，比如LISTAGG。 总结步骤应该是：识别DML类型，逐项检查语法差异，提供转换示例，并建议测试和注意事项。同时，参考引用中的信息来支持这些转换步骤，确保回答的准确性。</think>将MySQL的DML语句迁移到Oracle数据库需要关注语法差异、函数兼容性和特性实现方式。以下是关键转换步骤及示例： ### 一、INSERT语句转换 1. **自增主键处理** MySQL使用`AUTO_INCREMENT`，Oracle需通过`SEQUENCE`+`TRIGGER`实现： ```sql -- MySQL INSERT INTO users(id, name) VALUES (NULL, '张三'); -- Oracle CREATE SEQUENCE users_seq; INSERT INTO users(id, name) VALUES (users_seq.NEXTVAL, '张三'); ``` 2. **日期格式转换** MySQL允许隐式字符串转换，Oracle需显式使用`TO_DATE`： ```sql -- MySQL INSERT INTO orders(order_date) VALUES ('2023-10-01'); -- Oracle INSERT INTO orders(order_date) VALUES (TO_DATE('2023-10-01', 'YYYY-MM-DD')); ``` ### 二、UPDATE/DELETE语句转换 **LIMIT子句处理** Oracle不支持`LIMIT`，需用`ROWNUM`子查询： ```sql -- MySQL UPDATE products SET stock=0 WHERE category='电子' LIMIT 10; -- Oracle UPDATE products SET stock=0 WHERE product_id IN ( SELECT product_id FROM ( SELECT product_id FROM products WHERE category='电子' ORDER BY product_id ) WHERE ROWNUM <= 10 ); ``` ### 三、SELECT语句转换 1. **分页实现** MySQL的`LIMIT`需改为Oracle的`ROWNUM`分层查询： ```sql -- MySQL SELECT * FROM employees LIMIT 10 OFFSET 20; -- Oracle SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM employees ORDER BY hire_date ) t WHERE ROWNUM <= 30 ) WHERE rn > 20; ``` 2. **函数替换** 常见函数对照： ```sql -- 字符串连接 SELECT CONCAT(first_name, last_name) FROM contacts; -- MySQL SELECT first_name || last_name FROM contacts; -- Oracle -- 当前时间 SELECT NOW(); -- MySQL SELECT SYSDATE FROM DUAL; -- Oracle -- 条件判断 SELECT IF(status=1, '激活', '冻结') FROM accounts; -- MySQL SELECT CASE WHEN status=1 THEN '激活' ELSE '冻结' END FROM accounts; -- Oracle ``` ### 四、其他注意事项 1. **数据类型映射** - `TINYINT` → `NUMBER(3)` - `TEXT` → `CLOB` - `DATETIME` → `DATE` 2. **事务控制差异** Oracle默认需要显式提交（`COMMIT`），且`ROLLBACK`作用范围不同 3. **大小写敏感性** Oracle对象名称默认大写，字符串比较区分大小写： ```sql -- MySQL SELECT * FROM users WHERE username = 'Admin'; -- 不区分大小写 -- Oracle SELECT * FROM users WHERE username = 'ADMIN'; -- 需统一大小写或使用函数 ``` ### 五、验证工具推荐 1. 使用Oracle SQL Developer的**迁移工作台** 2. 通过`EXPLAIN PLAN`分析执行计划 3. 利用`DBMS_OUTPUT.PUT_LINE`调试存储过程
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值