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 tset u.RecordStatus = t.RecordStatus,u.AuditMessage=t.AuditMessagewhere 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))BEGINDECLARE 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 DOset 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 DOSET 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。示例过程说明如下：

 

本来想上传代码的，但博客后台好像不能上传文件功能，如有需要请发送邮件到[e-mail],我将回复邮件中附加代码。