MySQL - 学习笔记 - 写入优化

用于优化的参数

参数	说明
innodb_log_buffer_size	需要修改 my.ini：设定 innodb 设置数据库引擎日志缓存区。单位Byte。默认值为1M，调整为16M
innodb_log_file_size	需要修改 my.ini：设定 innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。单位Byte。默认值为8M，调整为128M
unique_checks	禁止对唯一索引(逐行)检查
foreign_key_checks	禁止对外键的检查
autocommit	禁止自动事务的自动提交
innodb_flush_log_at_trx_commit	默认值为1；设置为0，日志缓存规则: 0可以提高写入速度
innodb_autoextend_increment	当 tablespace 空间已经满了后，系统需要自动扩展多少空间。单位M。默认值为8M，调整为128M

查看当前值

SELECT
	@@unique_checks AS unique_checks,
	@@FOREIGN_KEY_CHECKS AS FOREIGN_KEY_CHECKS,
	@@autocommit AS autocommit,
	@@innodb_flush_log_at_trx_commit AS innodb_flush_log_at_trx_commit,
	@@innodb_autoextend_increment AS innodb_autoextend_increment,
	@@innodb_log_buffer_size / 1024 / 1024 AS innodb_log_buffer_size,
	@@innodb_log_file_size / 1024 / 1024 AS innodb_log_file_size

例子

-- ----------------------------
-- 适配新老系统 `temp_data`（728.961s）
-- ----------------------------
-- ----------------------------
-- 写入优化【开启】 （40.545s）
-- ----------------------------
SET GLOBAL unique_checks=0;
SET GLOBAL foreign_key_checks=0;
SET GLOBAL autocommit=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;
SET GLOBAL innodb_autoextend_increment=128;

-- ----------------------------
-- 创建表
-- ----------------------------
DROP TABLE IF EXISTS `temp_data`;
CREATE TABLE `temp_data` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `eid` varchar(32) NOT NULL COMMENT '事件id',
-- 	,PRIMARY KEY (`id`) 这里先不加主键。写入完成后再添加
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='合并结果表';

-- ----------------------------
-- 插入数据 （多环节拼接成事件流程表）
-- ----------------------------
INSERT INTO `temp_data`
SELECT * 
FROM (
	-- 登记上报
	SELECT UPPER( MD5( UUID() ) ) AS `id`, `eid` AS `eid`
	FROM `event_add`
	-- 派遣
	UNION ALL
	SELECT `id` AS `id`, `eid` AS `eid`
	FROM `event_send`
	-- 处理
	UNION ALL
	SELECT  `id` AS `id`, `eid` AS `eid`
	FROM `event_deal`
	-- 复核
	UNION ALL
	SELECT `id` AS `id`, `eid` AS `eid`
	FROM `event_check`
	-- 评价
	UNION ALL
	SELECT `id` AS `id`, `eid` AS `eid`
	FROM `evente_valuate`		
) as td
ORDER BY  rd.`eid` asc;

-- ----------------------------
-- 写入优化【关闭】
-- ----------------------------
ALTER TABLE `temp_data` ADD PRIMARY KEY (`id`); -- 添加主键
SET GLOBAL unique_checks=1;
SET GLOBAL foreign_key_checks=1;
SET GLOBAL autocommit=1;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
SET GLOBAL innodb_autoextend_increment=64;

参考资料

官方文档：MySQL 5.7 Reference Manual / The InnoDB Storage Engine / InnoDB Startup Options and System Variables