mysql导入导出性能优化

现在好多的人都在借助比如navicat之类的来实现MYSQL操作。

其实在运维阶段,最麻烦的就是数据的导出导入工作。

我们先来看,针对非原生命令SQL导出的数据记录集:

CREATE TABLE `msg_price` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,
`min` int(11) NOT NULL COMMENT ‘最小数量’,
`max` int(11) NOT NULL COMMENT ‘最大数量’,
`money` decimal(10,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘成本价’,
`price` decimal(10,2) DEFAULT ‘0.00’ COMMENT ‘销售价’,
`del` tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘0未删除,1已删除’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=232 DEFAULT CHARSET=utf8 COMMENT=’费用阶梯’;

— —————————-
— Records of msg_price
— —————————-
INSERT INTO `msg_price` VALUES (‘221’, ‘1’, ‘5000’, ‘0.88’, ‘0.98’, ‘1’);
INSERT INTO `msg_price` VALUES (‘222’, ‘5001’, ‘10000’, ‘0.77’, ‘0.87’, ‘1’);
INSERT INTO `msg_price` VALUES (‘223’, ‘10001’, ‘20000’, ‘0.66’, ‘0.76’, ‘1’);
INSERT INTO `msg_price` VALUES (‘224’, ‘20001’, ‘50000’, ‘0.55’, ‘0.65’, ‘1’);
INSERT INTO `msg_price` VALUES (‘226’, ‘50001’, ‘50002’, ‘0.50’, ‘0.60’, ‘1’);
INSERT INTO `msg_price` VALUES (‘227’, ‘50001’, ‘50002’, ‘0.50’, ‘0.60’, ‘1’);
INSERT INTO `msg_price` VALUES (‘228’, ‘1’, ‘1000’, ‘0.08’, ‘0.07’, ‘0’);
INSERT INTO `msg_price` VALUES (‘229’, ‘1001’, ‘5000’, ‘0.07’, ‘0.06’, ‘0’);
INSERT INTO `msg_price` VALUES (‘230’, ‘5001’, ‘10000’, ‘0.06’, ‘0.05’, ‘0’);
INSERT INTO `msg_price` VALUES (‘231’, ‘10001’, ‘20000’, ‘0.05’, ‘0.04’, ‘0’);

 

再来看看,mysqldump命令导出的

CREATE TABLE `msg_price` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,
`min` int(11) NOT NULL COMMENT ‘最小数量’,
`max` int(11) NOT NULL COMMENT ‘最大数量’,
`money` decimal(10,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘成本价’,
`price` decimal(10,2) DEFAULT ‘0.00’ COMMENT ‘销售价’,
`del` tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘0未删除,1已删除’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=232 DEFAULT CHARSET=utf8 COMMENT=’费用阶梯’;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `msg_price`

LOCK TABLES `msg_price` WRITE;
/*!40000 ALTER TABLE `msg_price` DISABLE KEYS */;
INSERT INTO `msg_price` VALUES (221,1,5000,0.88,0.98,1),(222,5001,10000,0.77,0.87,1),(223,10001,20000,0.66,0.76,1),(224,20001,50000,0.55,0.65,1),(226,50001,50002,0.50,0.60,1),(227,50001,50002,0.50,0.60,1),(228,1,1000,0.08,0.07,0),(229,1001,5000,0.07,0.06,0),(230,5001,10000,0.06,0.05,0),(231,10001,20000,0.05,0.04,0);
/*!40000 ALTER TABLE `msg_price` ENABLE KEYS */;
UNLOCK TABLES;

 

问题很明显了

虽然我们在执行插入SQL的语句的时候,1条记录也就0.00几秒内完成,但是由于数量多办,在资源的读、写中就把时间耗损掉了。

如果针对记录集几百万甚至更高的,如果使用第一种方式处理,结局悲剧。

或者是作者依赖原生的关系,对软件操作类型比较少操作,或者有更好的办法。

但其实不管归根到底用哪种软件,最终的结果都是一样的

INSERT 一条语句,结果写入一行和INSERT一条语句,结果写入X行是完全不同的执行概念

发表评论

电子邮件地址不会被公开。 必填项已用*标注