数据库实战总结

2021-11-25 03:16:37

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

码农公寓