汕头门户网站,无锡网站备案,网站和服务器是什么关系,手机上能搭建网站吗目录 一、前言二、创建表三、编写存储过程插入数据四、高效插入数据方案4.1、插入数据时删除表中全部索引4.2、存储过程中使用统一事务插入#xff08;性能显著提升#xff09;4.3、调整MySQL系统配置#xff08;性能显著提升#xff0c;适合存储过程没有使用统一事务… 目录 一、前言二、创建表三、编写存储过程插入数据四、高效插入数据方案4.1、插入数据时删除表中全部索引4.2、存储过程中使用统一事务插入性能显著提升4.3、调整MySQL系统配置性能显著提升适合存储过程没有使用统一事务查看MySQL这两个配置默认值一般默认都是1修改MySQL配置文件插入10w数据测试 五、总结 一、前言
最近在做SQL索引优化的时候经常需要批量插入一些数据采用存储过程来进行批量插入是一个很好的选择但是在插入100w数据时我本地耗时需要24分钟有点顶不住本文会讲解如何通过存储过程批量插入数据并且提供两个提升插入速度的方法。
二、创建表
DROP TABLE IF EXISTS order_info;
CREATE TABLE order_info (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 订单ID,order_no varchar(100) NOT NULL COMMENT 订单编号,customer_id bigint(20) NOT NULL COMMENT 客户编号,goods_id bigint(20) NOT NULL COMMENT 商品ID,goods_title varchar(100) COLLATE utf8mb4_0900_as_cs DEFAULT NULL COMMENT 商品标题,order_status tinyint(4) NOT NULL DEFAULT 1 COMMENT 订单状态 1待支付 2已支付 3已发货 4、已收货,create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_as_cs COMMENT订单信息表;三、编写存储过程插入数据
在测试的时候插入数据量可以调小一点一次别插入太多如果存储过程不加事务插入10w条数据我本地耗时143秒插入100w数据我本地耗时24分钟太慢了可以先看下面高效插入数据方案。
## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure ()
BEGIN# 定义循环值DECLARE i INT DEFAULT 1;# 开启事务START TRANSACTION;# 开始循环插入WHILE ( i 1000000 ) DOINSERT INTO order_info(order_no,customer_id, goods_id, goods_title, order_status, create_time) VALUES (CONCAT(ON00000,i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT(笔记本电脑,i), MOD(i, 4)1, NOW());SET i i 1;END WHILE;
END;;
delimiter;# 调用存储过程插入数据
CALL insert_procedure ();四、高效插入数据方案
MySQL版本8.0.18
如果MySQL不做任何配置我本地固态盘使用MySQL8.0插入10w条数据耗时142s插入数据量越大可能等比耗时更长一般表中都会创建一些索引在插入数据的时候也会变更索引尤其是唯一索引会增长插入数据的时间要想加快插入速度有多种方法硬件上的优化就不说了这里只说三个方法够我们做SQL索引优化测试即可。
4.1、插入数据时删除表中全部索引
将表中索引全部删除包括主键索引尤其是自增主键索引还有唯一索引自己生成ID保证自增不重复即可这里以10w条数据做测试对比插入100w数据耗时太长。
我本地10w条数据插入有自增主键索引插入耗时142s删除主键索引改用自己生成ID值插入耗时139s这个数据量还比较小有兴趣可以加大数据量测试数据量越大差值越明显。
只需要把把存储过程中的SQL改一下把让 ID 使用 i 的值即可
INSERT INTO order_info(id,order_no,customer_id, goods_id, goods_title, order_status, create_time) VALUES (i, CONCAT(ON00000,i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT(笔记本电脑,i), MOD(i, 4)1, NOW());4.2、存储过程中使用统一事务插入性能显著提升
在存储过程中添加事务存储过程中的每次新增语句都会开启一个自己的事务控制所有新增都在一个事务中10w条数据插入耗时从142s提升到20s速度大大提升但是有个问题这20s其它插入操作需要等待线上业务需要考量一下本地SQL索引优化测试倒是一个很不错的选择。
给存储过程添加上统一事务
## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure ()
BEGIN# 定义循环值DECLARE i INT DEFAULT 1;#定义一个错误的变量,类型是整形默认是0DECLARE t_error INTEGER DEFAULT 0;#捕获到sql的错误就设置t_error为1DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error1;# 开启事务START TRANSACTION;# 开始循环插入WHILE ( i 1000000 ) DOINSERT INTO order_info(order_no,customer_id, goods_id, goods_title, order_status, create_time) VALUES (CONCAT(ON00000,i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT(笔记本电脑,i), MOD(i, 4)1, NOW());SET i i 1;END WHILE;#如果捕获到错误IF t_error1 THEN#回滚ROLLBACK;ELSE#提交COMMIT;END IF;
END;;
delimiter;# 调用存储过程插入数据
CALL insert_procedure ();4.3、调整MySQL系统配置性能显著提升适合存储过程没有使用统一事务
这种方案是适合存储过程没有使用统一事务插入每一次插入都需要开启事务然后提交对存储过程中使用了统一事务插入提升不大。
MySQL有两个配置是控制日志文件写入的在计算器中最耗时的操作就是IOMySQL默认是会同步写入redo日志和binlog日志的我们插入100w数据就需要同步写入100w次redo日志和100w次binlog日志这是非常耗时的如果能改成异步批量写入则可以大大加快新增数据的速度但是可能会导致数据库宕机时数据丢失问题这里不做详细说明。
innodb_flush_log_at_trx_commit 控制redo日志写入模式 等于0 log buffer每秒就会被刷写日志文件到磁盘提交事务的时候不做任何操作执行是由mysql的master thread线程来执行的。等于1 每次提交事务的时候都会将log buffer刷写到日志 默认等于2 表示在每次事务提交的时候会把log buffer刷到文件系统中去但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了由于文件系统没有问题那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下数据库的事务数据可能丢失1秒之类的事务数据。这样的好处减少了事务数据丢失的概率而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中一般只是从log buffer的内存转移的文件系统的内存缓存中对底层IO没有压力)。 sync_binlog 控制binlog日志写入模式 在提交n次事务后进行binlog的落盘0为不进行强行的刷新操作而是由文件系统控制刷新日志文件如果是在线交易和账有关的数据建议设置成1如果是其他数据可以保持为0即可
查看MySQL这两个配置默认值一般默认都是1
SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE sync_binlog;修改MySQL配置文件
我的MySQL是Linux版的配置文件在/etc/mysql/my.cnfwindow 上的 MySQL 配置文件默认是在 C:\Program Files\MySQL\MySQL Server 8.0\my-default.ini。
# 打开/etc/mysql/my.cnf
vi /etc/mysql/my.cnf在配置文件中的[mysqld]下添加如下配置
## 2表示在每次事务提交的时候会把log buffer刷到文件系统中去但并不会立即刷写到磁盘。
innodb_flush_log_at_trx_commit 2
## 0为不进行强行的刷新操作而是由文件系统控制刷新日志文件
sync_binlog 0重启MySQL
service mysqld restart
# 或
service mysql restart插入10w数据测试 修改前 修改后 插入速度还是比使用统一事务插入差很多。
五、总结
我的需求是为了做SQL索引优化测试需要批量插入一些数据这里最适合我的是4.2中添加统一事务来插入方案。
4.2方案存储过程中使用统一事务插入100w数据耗时217秒差不多3.6分钟没有调整前耗时24分钟插入速度提升6.6倍多。4.3方案调整MySQL配置插入100w数据耗时415秒差不多7分钟没有调整前耗时24分钟插入速度提升3.4倍多。
要想高效插入数据还有很多种方法我这里只是为了做SQL索引优化测试使用这个插入耗时我还可以接受有其它好的方法可以一起交流。
