数据库实战总结
2022/2/18 19:12:00
本文主要是介绍数据库实战总结,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、MySql插入大量数据
1、建表
CREATE TABLE `t_test`( `id` BIGINT(20) not null AUTO_INCREMENT, `content` VARCHAR(255) DEFAULT null, PRIMARY KEY(`id`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
这里选择数据库引擎是MYISAM。
2、建立存储过程
因为要插入100万条数据,不可能通过一条条的insert去做,这里采用存储过程:
delimiter$$ DROP PROCEDURE IF EXISTS proc_batch_insert; CREATE PROCEDURE proc_batch_insert() BEGIN DECLARE i INT; SET i=1; WHILE i<1000000 DO INSERT INTO t_test(`content`)VALUES(SUBSTRING(MD5(RAND()) FROM 1 FOR 6)); SET i=i+1; END WHILE; END$$ delimiter;
调用存储过程:
CALL proc_batch_insert;
从下图可以看出插入100万条数据需要2284.025s,大约38分钟。
上边演示了插入100万条数据,使用的数据库引擎是MYISAM。
3、变更数据库引擎
删除上面插入的100万条数据:可参考删除大量数据模块。
将MYISAM改为INNODB:
ALTER TABLE t_test ENGINE=INNODB;
重新调用插入100万数据的存储过程:
二、删除大量数据
1、方案一:使用delete
-
delete执行速度与索引量成正比,若表中索引量较多,使用delete会耗费数小时甚至数天的时间
-
执行大批量删除的时候注意要使用上limit。因为如果不用limit,删除大量数据很有可能造成死锁lock wait timeout exceed。
- 删除的条件where尽量命中索引
DELETE FROM t_test LIMIT 100000
或者建立存储过程:
delimiter $$ DROP PROCEDURE IF EXISTS proc_batch_delete; CREATE PROCEDURE proc_batch_delete() BEGIN DECLARE tcount BIGINT; SELECT count(0) INTO tcount FROM t_test; WHILE tcount>0 DO DELETE FROM t_test LIMIT 1000; END WHILE; SELECT tcount; END $$ //调用存储过程 delimiter ; CALL proc_batch_delete;
2、方案二:使用drop
- 创建一个新表,表结构与原表结构相同
CREATE TABLE `t_test_new`( `id` BIGINT(20) not null AUTO_INCREMENT, `content` VARCHAR(255) DEFAULT null, PRIMARY KEY(`id`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
- 将需要保留的数据插入至新表中(耗时相对较多)
INSERT INTO t_test_new (id,content) SELECT id,content FROM t_test;
- drop原表
DROP TABLE t_test;
- 将新表改名为原表名
ALTER TABLE t_test_new RENAME t_test;
3、方案三:使用truncate
-
truncate 是整体删除 (速度较快),delete是逐条删除 (速度较慢)
-
truncate 不写服务器 log,delete 写服务器 log,也就是 truncate 效率比 delete高的原因
-
truncate 不激活trigger (触发器),但是会重置Identity (标识列、自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的 ID数。而 delete 删除以后,identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录,只能使用 DELETE语句配合 where条件
这篇关于数据库实战总结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15鸿蒙生态设备数量超8亿台
- 2024-05-13TiDB + ES:转转业财系统亿级数据存储优化实践
- 2024-05-09“2024鸿蒙零基础快速实战-仿抖音App开发(ArkTS版)”实战课程已上线
- 2024-05-09聊聊如何通过arthas-tunnel-server来远程管理所有需要arthas监控的应用
- 2024-05-09log4j2这么配就对了
- 2024-05-09nginx修改Content-Type
- 2024-05-09Redis多数据源,看这篇就够了
- 2024-05-09Google Chrome驱动程序 124.0.6367.62(正式版本)去哪下载?
- 2024-05-09有没有大佬知道这种数据应该怎么抓取呀?
- 2024-05-09这种运行结果里的10.100000001,怎么能最快改成10.1?