MySql性能优化-通过使用XML传参将多次循环处理变为一次集合处理

数据库批量操作有很多这样的应用场景，如批量通过选中的审请信息、批量插入采购单的采购清单信息等。一个公认的实事，即关系型数据库在结构化的集合处理方面表现优秀，而数据库与编程语言之间通常通过各种参数来进行交互，而XML刚好是它们之间的一个桥梁，把编程语中向面对象的数据序列化为XML，传递到数据库，数据库再转换为平面表，实现高效的集合操作。本文将深入的分析各种解决方案，并重点介绍使用XML的方式批量更新操作。

1      各种批量操作方式比较

 

实现方案	方案分析
在开发语言（如C#、Java）中使用循环的方式（如for、while）每次处理一条更新或插入操作	实现简单，符合面向对象的开发思想，由于每条操作都需要向数据库建立连接、发送操作SQL、接收数据库返回处理结果，然后继续开始下一条操作，还要考虑事务问题，大量的时间都消耗在建立连接、网络开销上，如果更新或插入的表上有索引，每次操作数据库还必须重新整理索引文件，开销非常大，并发量大时容易造成死锁、超时等异常情况
同样采用开发语言，通过循环生成批量更新的sql语句，多条更新插入语句通过“;”号隔开，组成一个较长的字符串，通过传递这些sql参数来实现批量操作，有些数据库本身支持批量操作（如ADO.NET操作SQL Server时使用的SqlBulkCopy）	这种方案可以实现一次提交所有执行SQL到数据库，只与建立连接一次，但传递的是SQL，拼凑时的逻辑复杂，代码风格和维护性差，容易导致SQL注入，还需要在网络上传递额外的信息（不仅仅传递元信息），由于没有参数化，这样的大块SQL语句很难生成非常优越的执行计划，但相比单条操作有一个大的性能提升
采用XML的方式，把需要传递的所有参数序列化或接接成XML字符串，把它作为一个参数传递给存储过程，存储过程通过约定好的结构再转换为一个平面表（通常为一个内存临时表），然后通过集合的方式来完成更新或插入操作	对技术要求略高，由于不同的数据库厂商对XML有不同的实现和支持程度，但本人使用过的mysql、oracle、sql server都有相关支持，mysql在6.0以后的版本对XML有了更全面的支持。本人认为该方案提供了最灵活的处理、最强大的功能和最好的性能，不妨在实际开发中试试

 

 

2      XML批量操作实例

XML批量操作，实用于各种编程语言和主流的数据库，使用的原理和方式一致，本文将以mysql为例，通过一个实例来讨论批量操作过程。

 

2.1    景场说明

本例以审批学生信息（更新学生的状态、审批评语字段信息），用户首先查看到一个待审批学生的列表，可以在每一行的后面填写各自的评语及勾选是否通过，然后批量提交。界面原型如下：

 

上图管理员勾选一批记录，在每一行后面填写评语，然后点击批量通过或不通过，完成这批记录的审批，下面将详细描述通过XML传参，并采用集合处理方式的处理流程。

 

2.2    流程图如下：

 

上图中的执行步骤流程图中，把主要的节点标注为黑色字体，其它过程按常规操作即可，接下来将重点分析这几个执行节点的实现。

 

2.3    实例代码

正如以上流程图强调，只有部分步骤需要特殊处理，为了直观且突出重点，下面将直接以调用一个接收XML的mysql存储过程来描述此实例，关于网页HTML、提交表单、用编程语言生成XML字符串并调用存储过程这部分代码不作分析。

l  创建示例表、插入两条记录：

-- ----------------------------
--  Table structure for `userinfo`
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `UserName` varchar(64) NOT NULL COMMENT '姓名',
  `ClassId` int(11) DEFAULT NULL COMMENT '班级ID',
  `UserIdCard` varchar(32) DEFAULT NULL COMMENT '学号_工号',
  `Photo` varchar(50) DEFAULT NULL,
  `TelephoneNumber` varchar(32) DEFAULT NULL COMMENT '电话号码',
  `Email` varchar(128) DEFAULT NULL COMMENT '邮箱',
  `UserType_Id` int(11) NOT NULL COMMENT '角色ID',
  `LoginName` varchar(64) DEFAULT NULL COMMENT '登录名',
  `LoginPassword` varchar(64) DEFAULT NULL COMMENT '密码',
  `RecordStatus` int(11) NOT NULL COMMENT '记录状态:记录状态：0--待审批，1--审批通过，4--审批不通过',
  `CreatedTime` datetime NOT NULL COMMENT '创建时间',
  `CreaterID` int(11) NOT NULL COMMENT '创建的用户ID',
  `ModifiedTime` datetime DEFAULT NULL COMMENT '修改时间',
  `ModifierID` int(11) DEFAULT NULL COMMENT '修改的用户ID',
  `Money` double DEFAULT NULL COMMENT '实验币',
  `MajorId` int(11) DEFAULT NULL,
  `AuditMessage` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `FK_Reference_13` (`ClassId`),
  KEY `FK_Reference_14` (`UserType_Id`),
  CONSTRAINT `FK_Reference_13` FOREIGN KEY (`ClassId`) REFERENCES `classinschool` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=210 DEFAULT CHARSET=utf8 COMMENT='用户信息';
-- ----------------------------
--  Records 
-- ----------------------------
INSERT INTO `userinfo` VALUES ('174','管理员',NULL,NULL,NULL,NULL,NULL,'1','001','gta11111','1','2013-11-13 18:54:01','1',NULL,NULL,'90',NULL,'很好，审批通过'), ('175','王小一','44','s001',NULL,NULL,NULL,'3','s001','000000','1','2014-02-20 10:46:02','174',NULL,NULL,'80','100','不错，审批通过');

l  拼接的XML调用存储过程示例如下：

-- 初始化数据，查看学生的状态和审批信息
update userinfo set RecordStatus=0,AuditMessage='' where id in (174,175);
select id,RecordStatus,AuditMessage from userinfo where id in (174,175);

-- 通过XML方式批量审批
call sp_updateScore('<rows>
				<struct>
				<field Field="id" Type="int(10)" />
				<field Field="RecordStatus" Type="int(10)"/>
				<field Field="AuditMessage" Type="varchar(200)"/>
				</struct>
				<row><id>174</id><AuditMessage>很好，审批通过</AuditMessage><RecordStatus>1</RecordStatus></row>
				<row><id>175</id><AuditMessage>不错，审批通过</AuditMessage><RecordStatus>1</RecordStatus></row>
	</rows>');

-- 再次查看效果
select id,RecordStatus,AuditMessage from userinfo where id in (174,175);

l  调用的存储过程代码如下：

BEGIN
	/*
	修改日期：2014-12-1
	作者：陈鹏
	功能描述：接收XML参数，把参数转换为表，集合批量完成审批操作
	*/

	-- 1.创建一个临时内存表
	CREATE temporary TABLE IF NOT EXISTS tempUser (
				id INT,
				AuditMessage varchar(200),
				RecordStatus int
			) ENGINE=Memory;

	-- 2.清空数据
	TRUNCATE TABLE tempUser;

	-- 调用通用的过程，把xml数据转换为平面表，这里传入表名和XML参数即可
	call xmldump_load(p_xml, 'tempUser');

	-- 调试信息
	-- select * from tempUser;

	-- 3.使用两个集合操作来批量更新数据库
	update 
		userinfo u, tempUser t
	set 
		u.RecordStatus = t.RecordStatus,u.AuditMessage=t.AuditMessage
	where 
		u.id=t.id;
	
	-- 4.释放临时表空间
	drop table tempUser;
END

l  上述过程中用到了一个转换函数，这个在其它数据库如sql server等已内置了这些功能，在mysql6.0中也提供了这些操作，但低版本仍需要自己编写转换函数，不过这些函数可以通用（其它类似的情况可重复公用），代码如下，如在真实环境应用请考虑通用性和注入等安全性问题：

-- ----------------------------
--  Procedure definition for `xmldump_load`
-- ----------------------------
DROP PROCEDURE IF EXISTS `xmldump_load`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `xmldump_load`(xmlstring VARCHAR(8000),
                          table_name VARCHAR(128))
BEGIN
	DECLARE xml TEXT;
	DECLARE nrows INT;
	DECLARE rownum INT DEFAULT 1;
	DECLARE ncols INT;
	DECLARE colindex INT DEFAULT 1;
	DECLARE colnum INT DEFAULT 1;
	DECLARE ins_list TEXT DEFAULT '';
	DECLARE val_list TEXT DEFAULT '';
	DECLARE tmp VARCHAR(255);
	DECLARE colName VARCHAR(255);

	# 将XML文件的内容载入到字符串中
	SET xml = xmlstring;
	# 获得这个表中<row>的数量
	SET nrows = ExtractValue(xml, 'count(/rows/row)');
	-- SELECT nrows;
	# 获得这个表中<filed>的数量
	SET ncols = ExtractValue(xml, 'count(/rows/struct/field)');
	-- SELECT ncols;
	# 对于每一个 <row>
	WHILE colindex <= ncols DO
		set tmp = ExtractValue(xml, '/rows/struct/field[$colindex]/@Field');
		SET ins_list = CONCAT(ins_list, tmp, IF(colindex<ncols, ',', ''));
		SET colindex = colindex + 1;
		-- select ins_list;
	END WHILE;

  WHILE rownum <= nrows DO
		# 对于每一个 <field> (列)
		WHILE colnum <= ncols DO
			SET colName = ExtractValue(xml, '/rows/struct/field[$colnum]/@Field');
			SET tmp = ExtractValue(xml, CONCAT('/rows/row[$rownum]/',colName));
			SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));
			SET colnum = colnum + 1;
			-- select val_list;
		END WHILE;
		SET @ins_text = CONCAT('INSERT INTO ',table_name,' (', ins_list, ') VALUES (', val_list, ')');
		SET val_list = '';
		PREPARE stmt FROM @ins_text;
		EXECUTE stmt;
		SET rownum = rownum + 1;
		SET colnum = 1;
  END WHILE;
END
;;
DELIMITER ;

2.4   实例源码下载

为了你能方便快速的调试，我把相关的函数、表及数据都打包成一个sql脚本，你只需创建一个空的数据库，在此数据库上运行脚本即可立即运行DEMO。示例过程说明如下：

 

本来想上传代码的，但博客后台好像不能上传文件功能，如有需要请发送邮件到363642626@qq.com,我将回复邮件中附加代码。