简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Home > Database > Mysql Tutorial > body text

MySQL大DML操作建议_MySQL

WBOY
Release： 2016-06-01 13:31:35
Original
880 people have browsed it

bitsCN.com

MySQL大DML操作建议

 

     ㈠ 大数据量INSERT

     

     ⑴ 使用多行插入代替单行：

 

insert into t values (),(),(),...
Copy after login

⑵

 LOAD DATA INFILE ... INTO TABLE t
Copy after login

 

     

     ⑶ 写入临时表、在 INSERT ... SELECT 

     

     ⑷ 取消索引、约束，等数据加载完后在重建

     

     ⑸ INSERT时指定DELAYED属性

     

     ⑹ 对于InnoDB、可以分散INSERT到多个事务、批量提交

     

     ㈡ 大数据量UPDATE

        

        这之前也应该disable掉索引和相关约束

        

     ① SELECT 唯一字段,变更字段

     ② 把结果集写入临时表

     ③ 为临时表创建复合索引

     ④ 循环临时表的每条记录

     ⑤ 依据临时表的唯一字段、更新目标表的变更字段

     

     ㈢ 大数据量DELETE

     

     ① 分区表

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

bitsCN.com
Related labels：
操作建议
source：php.cn
Previous article：mysql实现增量备份_MySQL Next article：MySQL和sqlserver中判断表是否存在语句_MySQL
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1361
Sudoku checker not working? Can anyone help me identify the error? I tried everything on the online editor but still get the error. But when I do this on VSC...
From 2024-04-06 21:21:07
0
1
474
How to solve the mysterious white space at the bottom of the page in CSS I'm trying to build a simple webpage using Bootstrap and D3, but I don't know how to get r...
From 2024-04-06 20:22:15
0
1
454
What are the best practices for displaying version information in web applications? I'm developing a web application. What are the best practices for displaying version infor...
From 2024-04-06 19:13:16
0
2
476
Show hidden side panels: checked I would like to have a navigation menu in the side panel with the ability to switch to vie...
From 2024-04-06 15:49:33
0
1
319
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！