mysql存储过程写入数据

一、执行过程

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命令