物料管理系统数据查询优化技术的研究

杨盛岚 桂林电子科技大学

引言

一个物料管理系统，在面对大量的数据时，如何高效的存取数据是一个非常巨大的挑战。随着数据量的增大，如果不注重数据查询的优化，将会严重得影响到系统的运行效率及用户使用的体验，因此，数据的查询优化在管理系统的开发和维护过程中，都是非常重要的一部分。

1 研究对象

物料管理系统部署在服务器上，同时我们在客户端进行系统的操作从而进行测试，我们对其型号、CPU、内存、硬盘等硬件环境和运行物料管理系统的软件和版本分别进行了说明，如表1-1和表1-2 所示。

表1-2 客户端环境

2 查询效率的影响因素

很多因素都会影响到数据查询效率，总体上看主要涉及到物理层、应用层和数据库层相关的信息。在物理层，内存，服务器性能和操作系统性能都会对数据查询效率产生影响。在数据库层，索引、数据存储等因素和查询效率存在密切关系。在应用层，查询程序和SQL 语句是主要的影响因素。

3 查询效率的优化技术

3.1 物理层优化策略

物理层的优化方案，主要体现在硬件方面的优化，可采用以下方法：

(1）提高服务器内存：服务器内存是直接关系到程序运行速度的因素，而数据的查询期间需要进行预读取、物理读取相关的操作，过程如图3-1 所示。预读取主要是在生成相应的数据查询计划期间，通过估计的信息来读取硬盘中的数据；物理读取则是在建立起相应的查询计划基础上，如果缓存中相关的数据信息缺失，则将数据读入到缓存中；逻辑读取即直接从缓存中读取数据。纵观整个过程，我们可以看出：缓存的容量是整个数据读取过程的关键。我们知道，在数据读取过程中，对比分析可知从缓存中读取数据的速度显著高于从硬盘中的。所以缓存容量越大，数据从硬盘中读取的次数就会减少，从而使数据读取的速度更快。

图3-1 服务器数据查询过程

(2）提升处理器性能：CPU功能可对计算指令进行分解和执行，据此可看出服务器的性能很大程度上由CPU 性能决定，我们可以在预算允许的情况下为服务器配置更高性能的CPU。

(3）减少建立数据库连接代价：可以通过使用连接池，来减少建立数据库连接的消耗。当进行一次数据查询时，必须经过建立数据库连接、打开数据库、读取数据库、关闭数据库等一系列操作，既耗费时间又耗费服务器资源，如果每次都要经过这些步骤，这对系统性能也会产生明显的影响。使用数据库连接池则可有效的避免此种问题。这种连接池在处理过程中建立了缓冲池进行数据库连接，在处理过程中需要先在其中设置相应的连接，在需要进行数据库连接情况下，单纯的从“缓冲池”中取出，结束后则放回。在此操作过程中，为避免无限的数据库连接问题出现，则应该确定出相应的最大连接数。

3.2 数据库层优化策略

数据库层的优化方法主要从数据库表结构的设计上出发，可以采用以下优化方法：

(1）为数据库表建立索引：数据库中的数据存储结构和索引密切相关，可将其看作为数据库表中的结构排序。索引的优势表现为可有效的提高数据库的查询速度。数据量保持一致条件下，通过实验测试在主键和非主键字段上建立聚集索引和非聚集索引的查询速度，讨论在主键和非主键字段上建立索引的原则。以查询某厅仓下所有仓库的所有物资出入库记录为例，近一年内的记录数据量约540 万条。在主键id上建立聚集索引，查询近一年的出入库记录，用时2129 毫秒。而在created_at 字段上添加非聚集索引之后，查询相同时间段的数据，用时1208 毫秒。用时缩减了将近50%，因此，选取伙食的字段简历索引，将会对数据查询速度产生很大的影响。

（2）适当增加数据冗余。为更好的满足数据处理性能要求，需要数据表中不会出现数据冗余的现象。不过一些情况下需要降低范式标准，设置一定量的数据冗余，这样可提高查询效率。具体分析可知数据冗余包括字段、表冗余。前者也就是设置冗余字段，降低相应的数据计算比例。比如可从身份证号中获取用户的身份信息，不过为满足查询效率相关的要求，也应该设置性别和出生日期相关信息。表冗余则基于增加冗余表而进行高效查询。比如对出入库日志表，想要获取长时间段内的出入库数据并计算展示最后剩余物料的数量，这个表中只有物料的id 号而并没有物料名称等相关信息，当我们在边查询和计算大量的日志，边匹配物料信息时，查询效率就会降低。此时，可以考虑在该表中将物料名称字段冗余，这样就能大大降低查询时的耗时。

3.3 应用层优化策略

这种优化具体表现为优化查询语句和程序。因为物料系统中的数据展示，多是展示物料的统计数据，即对于出库入库日志数据进行聚合统计展示，这就涉及到大量数据的多表查询策略。

对于mysql 数据库多表查询，我们通常会使用union 方法来进行处理：首先根据查询条件扫描全部数据表，确定出满足要求的记录，接着基于union 合成一张临时表，此表为满足要求的数据，再在这张临时表的基础上做查询和聚合统计。在这个查询过程中，时间主要消耗在了扫描全表和union 操作上，那么我们可以从这两个方向下手，即减少分表扫描的个数，同时查询的速度也提高。

在经过实验和数据分析后，本文归纳总结了一个优化算法，本算法通过逻辑判断精确地找到数据所在的表,使每次查询只需要查询一张表，算法流程图如图3-2 所示。以下是优化算法的步骤：

(1）根据客户端给出查询条件得出查询所涉及的表为t1、t2、t3。

(2）根据查询条件从各个表中查询出符合条件的记录数，记为c1、c2、c3，并且求出三个表中符合条件的记录总数为total =c1+c2+c3。

(3）假设页面大小为pageSize，可求出总页码数量pageCount = total / pageSize.

(4）根据客户端传过来的页码数page 求出偏移量offset=(page-1)*pageSize。

(5）创建一个集合Set。

(6）如果offset + pageSize＜= c1，则结果集全部落在t1 中，然后查询t1将所有符合条件的数据存入Set 中。否则继续（7）步骤。

(7) 如果offset＜c1且offset+pageSize＞c1 且offset+pageSize＜c1+c2，结果集部分落在t1 中部分落在t2 中，查询t1,t2 将符合条件的数据存入结果集Set 中。否则继续（8）步骤。

(8）如果offset＜count1 且offset+pageSize＞c1+c2，结果集分别落在 t1,t2,t3 中，查询t1,t2,t3 将符合条件的数据存入结果集Set 中。否则继续（9）步骤。

(12）最后返回总页数pageCount 和数据集Set.

图3-2 算法流程图

4 结语

信息爆炸式地增长，给我们系统数据查询处理带来了更多的挑战，我们需要关注信息数据处理的方式及优化，并且更加深入研究数据查询的相关技术，这样可提高系统的快速响应性能，用户的满意度提升。本文从物理层、数据库层等角度对数据查询效率的影响因素进行具体分析，在此基础上建立了一个在应用层方面的合理的优化算法，使得数据库查询效率大大提高。相关的优化方法还有很多，我们还需要在保证系统正常运行的同时不断优化，使用户获得更佳的体验。