煤矿安全监控系统中模拟量查询算法优化.doc

煤矿安全监控系统中模拟量查询算法优化 摘 要为了解决当前煤矿安全监控系统中，模拟量最值及最值时间的查询与统计方法存在 的缺点与不足，文章分析并提出了一种新的最值与最值时间合并算法，优化了SQL 语句， 减少了表的扫描次数和数据库服务器的执行时间。通过实验对比，证明了新算法在效率上有 了很大的提高。 关键词数据库, 查询算法优化, SQL, 监控系统 中图分类号TP311 1．引言 煤矿安全监控系统，是煤炭科学研究总院重庆研究院数字化矿山安全生产综合信息平 台软件CQ0911项目的一部分，主要负责实时监测矿井各项环境指标，确保安全生产。该 系统井上部分采用Microsoft SQL Server 2005 作为数据库服务器，使用B/S 开发模式，在 ASP.NET2.0 平台上开发实现[1]。系统建成以来，在煤矿安全生产中，发挥了重要作用。 在此系统中，除了对实时数据的收集和存储之外，根据国家安全生产标准[2]的要求，需 要统计模拟量数据每5 分钟的最大值、此最大值产生时的时间即最大值时间、最小值、最小 值时间和此5 分钟时间内的平均值，且存放时间不少于半年。其中模拟量数据主要包括瓦斯 浓度、一氧化碳浓度、井下通风质量指数风速、温度、湿度以及电压等参数，并保留小数 点后4 位有效数字。在此基础上，统计每个传感器某个时间段的最大值、最大值时间、最小 值、最小值时间和平均值，并生成模拟量统计值报表Analog Statistics ReportASR；以及统 计某个时间段内的每个传感器的每半小时、１小时、1 天或其它时间隔内最大的最大值、此 最大值产生的时间、最小的最小值、此最小值产生的时间、此间隔时间内所有平均值的平均 值，并生成模拟量间隔时间报表Analog Interval ReportAIR。这些报表，在各部门安全生 产检查中使用频率非常高，已经成为煤矿瓦斯预防处理措施及效果的评价标准。 本文根据当前系统的表结构，分析了当前的查询方法，提出了最值与最值时间合并算法， 不但解决了生成AIR 报表所存在的难题，也提高了生成ASR 报表的速度，减少表的扫描次 数和服务器执行时间，提高了查询效率。 2．现有模拟量查询方法 2.1 模拟量5分钟数据表结构 对于一般的大型煤矿来说，其模拟量传感器一般在180 个左右，每月的模拟量５分钟数 据就达到120 万条以上。考虑到查询时间段一般在一个月以内，因此，模拟量5 分钟数据按 月存放，每月生成一个表，其名称为M年月，如2009 年9 月份，表名为M200909。 其表结构如表１所示。 其中，pointId 表示每个传感器的编号，它引用自传感器定义信息表。scopeTime 列表示 此条记录属于哪个5 分钟时间段，例如其值为2009-09-01 001000，则表示此条记录属于 2009-09-01 001000～2009-09-01 001459 时间段，maxValue、minValue、avgValue 分别表示此时间段内的最大值、最小值和平均值。同时为表建立非聚集索引，由pointId 列 中国科技论文在线 - 2 - 和scopeTime 列组成。 表1 模拟量5 分钟数据表设计 Tab.1 Table structure of 5’ analog data 列名 数据类型 备注 Id INT 标识列PK pointId INT 传感器编号FK maxValue FLOAT 最大值 maxTime DATETIME 最大值时间 minValue FLOAT 最小值 minTime DATETIME 最小值时间 avgValue FLOAT 平均值 scopeTime DATETIME 所属时间段 下面的SQL 语句中，我们定义stakeTime 表示标准时间，beginTime 表示查询的开始 时间，endTime 表示查询的结束时间，imr 表示用户查询的间隔时间。 2.2 现有获取最值与最值时间查询方法 我们使用如下方法①，将所有点的最大值与最大值时间同时取出。SQL 语句如下 SELECT MI.pointid , MI.maxvalue , MAXMO.maxTime AS maxTime FROM SELECT pointid , MAXmaxValue AS maxValue FROM M200909 WHERE scopeTime BETWEEN beginTime AND endTime GROUP BY pointid AS MI LEFT JOIN M200909 AS MO ON MI.pointid MO.pointid AND MI.maxValue MO.maxValue WHERE scopeTime BETWEEN beginTime AND endTime GROUP BY MI.pointid , MI.maxValue ORDER BY MI.pointid 上面SQL 语句中，斜体部分表示从5 分钟表中取出所有传感器的最大值，然后根据传 感器编号和最大值，再与此5 分钟左表连结。这样便生成了包含传感器编号、最大值和最 大值时间所组成的记录集合，再从中选择传感器编号、最大值和最近的最大值时间。最小 值和最小值时间选取也和此方法类似，而平均值的选择，使用AVG 函数，也可以一次取出。 此方法只需要扫描5 分钟表两次，就可以将所有点的最大值和最大值时间取出。如果 我们将最大值、最大值时间，最小值、最小值时间，平均值查询放在一个存储过程中查 询，把三次查询的中间结果，再根据传感器编号pointid来连结，那么整个ASR 报表数据 查询开销，只需要扫描5 次5 分钟表，连结1 次数据库即可完成整个报表查询。 3.最值与最值时间合并算法 3.1 现有方法中存在的问题 方法①的工作对象是生成ASR 报表的数据，每个传感器只返回一条数据。而在AIR 报 表中，要求根据查询间隔，每个传感器可能返回多条记录，因此上面的方法无法满足AIR 报表的查询要求。由于可能存在服务器宕机和程序异常事件的发生这在实际生产过程中是 - 3 - 不可避免的，并且某个地点传感器的使用时间也有一定期限，因此可能存在某个时间段内， 某些传感器5 分钟数据的缺失或不存在。如果将所有符合条件的5 分钟数据表读出，交由应 用程序分析合并统计间隔时间内的最值和最值时间，将使得以下问题难以避免 一、数据库服务器发送到应用程序服务器数据量过大，增加服务器负担和网络传输压 力。如果生成半个月的AIR 报表，180 个传感器则可能要传输60 万左右的记录，约有3.6107 字节，如此多的数据也占据了应用程序服务器很大的内存； 二、统计算法复杂。由于5 分钟记录可能存在缺失，因此统计时，要判断最值时间，以 确定此条记录是否属于相应的间隔区间内；如果用户查找多个点的时候，还要在不同点之间 记录并切换； 三、分页处理比较困难。如果将60 万记录按1 小时间隔合并分析后，约有5 万条最终 结果，分页显示是必须的，这时就要求我们必须把所有的记录统计完毕，否则便无法确定统 计的最终结果及条数，无法完成分页和翻页。如果每页显示200 条，那么对其它48000 条 的统计资源消耗是很大的浪费。 四、如果将每天每个传感器的数据，进行统计分析，得出一天中的最大值、最大值时间、 最小值、最小值时间和平均值，存放到另外的统计表中，以便统计每天的最值及最值时间， 以及查询每周、每月的最值记录。但是，由于服务器可能出现停机，我们无法使用作业进行 定时统计，需要另外编写软件统计这些最值信息，它在增加了系统工作量的同时，也增加了 维护的难度。并且当某些数据因为某种原因缓存在井下分站中，过了一段时间之后才写入数 据库，这就容易造成数据的不一致性。再者，如果用户的统计间隔时间小于1 天的时候，这 种统计分析工作也就无能为力了。 3.2 最值与最值时间合并算法 为了解决以上问题，本文提出了最值与最值时间合并算法。其主要步骤如下 步骤1将5 分钟表中每条记录的最大小值乘以104，转换为一个整数a； 步骤2设置标准时间stakeTime，计算得到最大小值时间与标准时间的时间差单位 秒，并将得到的时间差乘以10-10，将其转换为一个小于等于0，并且有10 位有效数字的小 数b； 步骤3将1、2 中得到的整数和小数相加，得到合并后的小数dab，分别用dmax 表 示合并后的最大值与最大值时间，dmin 表示合并后的最小值与最小值时间； 步骤4计算每条记录所属时间段scopeTime 与标准时间stakeTime 之间的时间差t单 位分钟，并将此时间差整除用户输入的查询间隔时间i单位分钟，得到间隔时间比rt/i。； 步骤5根据传感器编号pointid 和时间间隔比r，对符合查询时间段的5 分钟数据分组， 选择最大的dmaxmax MAXdmax和最小的dminminMINdmin以及平均值vAVGavgValue； 步骤6分别对dmaxmax、dminmin 下取整，并乘以10-4 还原最大值和最小值； 步骤7截取dmaxmax、dminmin 的小数部分，并采用与步骤2 相反的操作，将最值时间还 原。 算法完毕。 其SQL 语句如下 SELECT pointid , FLOORmaxValue * 0.0001 AS maxValue ,--提取最大值 DATEADDSECOND,CASTmaxValue - FLOORmaxValue AS DECIMAL20, 10 * 10000000000, stakeTime AS maxTime , --提取最大值时间 FLOORminValue * 0.0001 AS minValue , --提取最小值 DATEADDSECOND,CAST minValue - FLOORminValue AS DECIMAL20, 10 * 10000000000, - 4 - stakeTime AS minTime , --提取最小值时间 ROUNDavgValue, 4 AS avgValue, --提取并格式化平均值 DATEADDminute,ts,stakeTime AS scopeTime –获取时间合并统计后的所属时间段 FROM SELECT pointId , MAXmaxValue * 10000 DATEDIFF SECOND, stakeTime, maxTime * 0.0000000001 AS maxValue ,--合并最大值、最大值时间 MINminValue * 10000 DATEDIFF SECOND, stakeTime, minTime * 0.0000000001 AS minValue ,--合并最小值、最小值时间 AVGavgValue AS avgValue ,--计算平均值 DATEDIFFminute, stakeTime, maxTime / imr AS ts --取得所属查询间隔组 FROM M200909 WHERE scopeTime BETWEEN beginTime AND endTime GROUP BY pointId , DATEDIFFminute, stakeTime, maxTime / imr AS tempTable ORDER BY pointid 上面SQL 语句中的斜体部分，是按照传感器编号和时间间隔比分组，取得最值和最值 时间合并后的最大值和最小值，以及计算后平均值。其中使用了DATEDIFF 函数计算最值 时间与标准时间的时间差。 在外部非斜体SQL 语句中，使用下取整函数FLOOR，再乘以10-4，将最大小值还原， 在当前设计中使用浮点数来表示最值，由于它是一个近似值，并非数据类型范围内的所有值 都能精确地表示，这也是当前5 分钟表设计中的不足之处。因此在转换过程中，要首先将其 转换为带固定精度和小数位数的数值数据类型DECIMAL20,10，这样才能保证时间的精确 还原，其允许存放的时间长度多于60 年，完全可以满足实际需要，并且可以根据实际最大小 值的大小，增加DECIMAL 数据类型的有效位数，它只是计算的中间结果，并不增加数据存 储空间。 从以上算法中，我们可以看出，取得一次AIS 报表的数据，只扫描了一次5 分钟表， 并且所有的计算全部都交由数据库服务器来完成，即减少了大数据量的传输给网络带来的压 力，也避免了使用应用程序进行统计的负担。当然我们也可以在存储过程中实现分页[3]，根 据用户的请求，只回传每个请求页的数据，这将确保数据库服务器每次只传输有用的数据。 对于上面SQL 语句，之所以使用中间查询的方法，是为了便于书写和理解的方便。但 是此处不建议将合并和提取方法写成数据库标量值函数，然后在SQL 语句中调用这些函数。 虽然这样的写法使得SQL 语句更简洁方便，但是在实际使用过程中，它的效率相对较低。 SQL 语句中代码的执行越接近DBMS 核心，则执行速度越快，反之则比较慢，特别是在需 要处理一个大数据量的表的每一行时，不要使用外部函数，如果要使用函数，始终应首选 DBMS 自带的函数[4]。 如果我们把间隔时间imr 设定为查询开始时间与查询结束时间相差的分钟数，则每个 传感器在查询时间段内只返回一条记录，那么上面最值与最值时间合并算法还可以用在ASR 报表中，而且它更有效，因为相比之前的方法，它扫描5 分钟表的次数减少了4/5，并且减 少了表之间的连结。 4．实验结果 实验中数据库服务器配置如下操作系统是Windows Server 2003 SP2，CPU 是Intel Core - 5 - Duo T5870 2.00GHz，内存容量2G，数据库服务器是Microsoft SQL Server 2005 SP1。 我们设定用户查询开始时间为2009-09-01 000000，查询结束时间为2009-09-15 235959，查询当前所有的传感器。测试用到两个5 分钟数据表。其中一个表有传感器210 个，同时存放107 万条记录，另外一个表400 个传感器，存放210 万条记录。分别根据现有 算法和合并算法，比较生成ASR 报表数据服务器的执行时间。实验结果如表2 所示。 同时我们选择所有传感器，使用最值和最值时间合并算法，选择统计间隔时间为30 分 钟的日报表、间隔4 小时的周报表和间隔1 天的月报表，分别在以上两个表中，比较数据库 服务器执行时间时间单位毫秒。实验结果如表3 所示。 表2 生成ASR 报表数据的服务器执行时间 Tab 2. Server cution time of ASR 执行时间 生成ASR 报表数据 测试数据量 现有方法 最值与最值时间合并算法 消耗时间比 107 万条记录 3240.1 ms 593.3 ms 5.461 210 万条记录 6536.9 ms 1199.8 ms 5.456 表3 生成AIR 报表的服务器执行时间 Tab 3. Server cution time of AIR 生成AIR 报表最值与最值合并算法 执行时间 测试数据量 间隔30 分钟 的AIR 日报表 间隔4 小时 的AIR 周报表 间隔24 小时 的AIR 月报表 107 万条记录 175.9 ms 468.5 ms 413.5 ms 210 万条记录 359.0 ms 915.1 ms 808.8 ms 由实验结果可以看出，使用最值和最值时间合并算法，与现有方法相比，服务器执行时 间降低了不少，查询效率有了很大的提高，取得了比较理想的效果。 5．总结 优化数据库表的结构设计可以提高数据库的性能，对于包含大量记录的数据库表，除了 建立索引，使用存储过程代替SQL 语句之外，优化查询算法也比较重要，不仅可以提高查 询响应速度，还可以减少服服务器负担，提高查询的效率。 参考文献 [1] 鲁远祥,樊荣. 煤矿安全监控系统体系架构技术的发展[J]. 矿业安全与环保，200936177-179. [2] 国家质量监督检验检疫总局计理司．矿山安全计量常用法律法规及标准[M]．北京中国计量出版社， 2007.8 [3] 勾成图,张璟, 李军怀． 海量数据分页机制在Web 信息系统中的应用研究[J]． 计算机应用, 2005251926-1929. [4] Stephane Faroult, Peter Robson. SQL 语言艺术[M]．温昱，靳向阳．北京电子工业出版社，2008.3 Optimization Algorithm for Querying Analog in Coal Mine Safety Monitoring System LI wenyan1, CHEN yunqi2 1 School of Computer, Chongqing University, Chongqing 400030 6 2 Chongqing Institute of Coal Science Research Institute, Chongqing 400037 Abstract In order to resolve the shortcomings and inadequacies of the current Coal Mine Safety Monitoring System while querying and counting maximum and its generating time, this paper brings forward a new merging algorithm, it optimizes SQL statements, reduces the times on scanning table and cution time. Compared with the current by experiment, it shows that the new algorithm’s efficiency has been greatly improved. Keywords Database; Query Optimization Algorithm; SQL; Monitoring System