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

Bertha 。 2022-08-14 04:36 24阅读 0赞

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

# 1      各种批量操作方式比较 #

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

# 2      XML批量操作实例 #

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

## 2.1    景场说明 ##

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

![Image 1][]

![Image 1][]![Center][]

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

## 2.2     流程图如下： ##

![Center 1][]

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

## 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调用存储过程示例如下：

![Image 1][]

-- 初始化数据，查看学生的状态和审批信息
 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中也提供了这些操作，但低版本仍需要自己编写转换函数，不过这些函数可以通用（其它类似的情况可重复公用），代码如下，如在真实环境应用请考虑通用性和注入等安全性问题：

![Image 1][]

-- ----------------------------
 -- 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。示例过程说明如下：

![Image 1][]

![Image 1][]![Center 2][]

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

[Image 1]: 
[Center]: /images/20220810/382a28400001476ba2a6275d2fdba554.png
[Center 1]: /images/20220810/f7671809ab984430bdbc637613dec52f.png
[Center 2]: /images/20220810/42f8fe1bd2834d68961eff52bf9c5819.png