Java教程

数据库实战总结

本文主要是介绍数据库实战总结,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、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条件

这篇关于数据库实战总结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!