一、执行过程
1、创建数据库表
DROP TABLE IF EXISTS `code_info`;
CREATE TABLE `code_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL DEFAULT 0,
`code` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`) USING BTREE,
INDEX `uid`(`uid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37093 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
2、写入存储过程
DROP PROCEDURE if exists insert_code;
DELIMITER ;;
CREATE PROCEDURE `insert_code`()
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 100000000 DO
INSERT INTO code_info(id, uid, code) VALUES (i, floor(1+rand()*1000), floor(1+rand()*100));
SET i = i + 1;
END WHILE;
END;;
DELIMITER ;
3、执行
call insert_code()
二、优化处理
1、创建的数据库表引擎先转换成MyISAM,数据导入完后再转为InnoDB,转innodb时需要一点时间
2、更改mysql配置文件配置详情如下:
innodb_log_file_size = 1024M
日志组中的每个日志文件的大小,设置较大的值可以减少脏数据刷新到磁盘的次数
innodb_log_buffer_size = 256M
将日志写入日志磁盘文件前的缓冲大小,默认是16M,设置较大的值可以使日志在缓冲时不需要被保存到磁盘,减少磁盘读取次数
innodb_flush_log_at_trx_commit = 0
将日志中的缓冲数据写入磁盘,默认为1,即每次提交事务都会写入磁盘,改为0后将减少写入磁盘的次数
innodb_buffer_pool_size = 4G
该值用来高速缓冲数据和索引内存缓冲大小,也是减少磁盘IO来增加速度,一般设置为物理存储的60%~70%
innodb_buffer_pool_instances = 4
增加多个缓冲池,提高并行内存读写能力
innodb_write_io_threads = 8 && innodb_read_io_threads = 8
增加IO线程数,提高读写能力,根据机器的处理器来设置
innodb_io_capacity = 500
提高刷新脏页数量和合并插入数量,改善磁盘I/O处理能力,默认200
以上参数修改都是通过减少对磁盘的IO操作来实现的,包括关闭了MySQL默认的事务存储到磁盘的操作,增大缓冲区的容量,这样子容易造成MySQL服务器宕机的时候,数据大量丢失,建议不要在生产环境下修改这些配置,根据实际情况修改
3、合并多条 insert 为一条
4、其他
Myisam : 对于Myisam类型的表,可以通过以下方式快速的导入大量的数据。
ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时,通过设置这两个命令,可以提高导入的效率。对于导入大量数据到一个空的Myisam表,默认就是先导入数据然后才创建索引的,所以不用进行设置
Innodb : 对于Innodb类型的表,有以下几种方式可以提高导入的效率:
1、因为Innodb类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果Innodb表没有主键,那么系统会默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高导入数据的效率。
2、在导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
3、如果应用使用自动提交的方式,建议在导入前执行SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。
备注:如果需要中断执行中的存储过程
1、输入show processlist;命令查看正在执行的任务,
2、查看Info找到任务对应的Id,执行kill命令