仓储管理系统的数据库查询优化策略研究

数据库技术自上世纪60年代诞生以来, 经历了数十年的发展目前已经成为现代计算机系统的核心技术之一.通常情况下, 数据库中的数据经过一段时间的积累会达到一定的数据存量, 比如一个企业的仓储管理系统后台数据库累计到数百万条甚至更多数据时, 执行查询操作若进行一次全表扫描往往需要数分钟, 甚至更久.此时必须采取一定的查询优化策略, 取代效率低下的全表扫描工作, 降低查询操作的执行时间.

目前的数据库查询优化策略主要分布在两个环节:物理层优化和逻辑层优化.其中涉及到了数据库的物理结构设计、体系结构设计、逻辑设计以及数据库管理系统设计等多个方面[1].而针对具体的数据库应用系统查询优化实现方案主要从以下两个环节展开:一是借助于数据库管理系统现有的查询优化器合理完善计算机系统的查询功能, 提高查询效率, 查询设计器针对整个系统的开发流程, 常规性的从数据库设计、系统实现等多个方面考虑查询效率;二是基于SQL查询语句的各类查询优化, 如合理建立和使用索引、避免或简化排序、消除对大型表行数据的顺序存、避免相关子查询、避免困难的正规表达式、使用临时表加速查询、用排序来取代非顺序存取等.本文将以某企业仓储管理系统为例, 从数据库设计和SQL查询语句两个方面着手, 实现对数据库进行基本的查询优化的验证.

本设计以某企业仓库管理系统数据库为例, 为该数据库总共设计了7张数据表, 分别是:物资信息表 (Material) 、物资库存信息表 (Material_kc) 、物资入库登记表、 (Material_rk_1) 、物资入库表 (Material_rk_2) 、物资出库登记表 (Material_ck_1) 、物资出库表 (Material_ck_2) 、仓储信息表 (Store) , 图1是各个数据表之间的关系图.

通过上文分析得知一个好的数据库结构可以大大的提高查询效率, 数据库结构的优化是数据库查询优化的基础.

在数据库中为数据表建立索引的首要目的就是提高系统查询性能, 目前数据库产品中最常用的索引结构是由IBM最先提出的ISAM结构.在进行索引的设计和使用时为达到良好的优化效果, 需遵循一定的设计原则, 本设计中验证与测试的索引使用原则如下:

(1) 将索引建立在连接需求量大且没有被定义为外键的字段上, 而对于不经常连接使用的字段则由查询优化器自动生成索引.以下的例子以My E-clipse作为测试工具, 测试在Material_rk_1表的stor_no字段上建立索引前后执行查询语句的时间及效果.测试中要查询仓库号=4的负责人号, 并查询该仓库接收的入库单号.在Material_rk_1表中共有原始数据12421条记录, 在Store表中有20条记录.在stor_no字段上建立索引之前测试结果如图2所示, 由测试运行结果可得在Material_rk_1表的Stor_no字段上建立索引之前, 运行程序后系统执行SQL语句用了31ms.在stor_no字段上建立索引之后测试结果如图3所示, 由测试运行结果可得在Material_rk_1表的Stor_no字段上建立索引之后, 运行程序后系统执行SQL语句用了15ms.比较在Material_rk_1表的Stor_no字段上建立索引前后执行代码得到的结果, 可以很清楚的看到, 在建立索引之后的程序运行时间比建立索引之前的程序运行时间节省了16ms.因此可以知道在经常进行连接, 但是没有指定为外键的列上建立索引是可以节省查询时间的.

(2) 尽量不在不同值少的列上建立索引, 在这样的列上建立索引不但提高不了查询速度, 在面临更新操作时将会严重降低运行速度, 这样的索引毫无意义可言.例如在material_ck_1表的has_audited列上只有“1”和“0”两个属性值分别表示审核与否, 在此列上如果人为建立了索引将不会产生优化效果.以下通过测试在Material_ck_1表的has_audited列上建立索引前后的查询执行时间来证实这一点.在Material_ck_1表中插入了17395条记录.现在要查询Material_ck_1表中已经审核过的信息, 在Material_ck_1表的has_audited字段上建立索引前后, 执行SQL语句的时间并没有改变, 都为31ms.因此可知在不同值少的列上建立索引对优化查询语句在本例中并没有节省查询时间, 即该查询优化是无效的.

(3) 使用系统工具.随着数据库中的数据不断扩充与更新, 之前建立的有效索引可能会出现因为访问操作频繁而执行效率降低或失效的情况.此时可以采用一些监控ONLINE系统后动情况的工具来检查索引是否有效, 必要时删除、修复或重建被损坏的索引.

建立了一个合理的数据库结构以后并不等于所有优化工作都已完成.系统运行时还有大量的查询、统计要通过SQL查询语句来实现.

* (星号) 通常作为通配符用来实现模糊搜索.在SQL语句里也不例外, 在我们检索某些特定的数据时, 我们不需要知道具体列的信息, *号就能起到它的作用了.*号主要用在以下两个方面:

(1) 列出指定数据表中的所有列的数据.其语法是:SELECT*FROM表格名

(2) 在Count中, 获得数据的行数.其语法是:SELECT COUNT (*) FROM表格名

第二种应用的情况是很常用到的, 而第一种应用, 则需视情况而定.在设计查询时, 开发人员往往为了减少代码的量, 会使用*号代替表的所有字段, 但这样实现SQL查询的结果一方面由于当数据的列变动后, Select*的结果也会变动, 可能有不可预见的情况出现;另一方面也是尤为重要的是这样一来虽然减少了代码, 却降低了查询的执行效率.数据库服务器在解析SQL语句时, 如果碰到*号, 会首先分析数据表的结构, 然后把数据表的所有字段名再罗列出来, 这样就增加了系统查询的时间.所以一般情况下, 还是应尽量列出列名清单.以My Eclipse作为测试工具, 测试在Material表中执行是否使用*号的查询语句的时间及效果.测试中在Material表中插入22381条记录, 由测试结果得到使用*号查询Material表信息用了62ms, 而将字段名列出进行查询所用时间可忽略不计.

在编写处理字符串的程序或网页时, 经常会有查找符合某些复杂规则的字符串的需要.正则表达式就是用于描述这些规则的工具.和通配符类似, 正则表达式也是用来进行文本匹配的工具, 比起通配符, 正则表达式能更精确地描述用户的需求.预定义的元字符是整个正则表达式的强大功能的基础, 它们的基本功能是利用符合基本规则的非常小的模式空间来表达模式.不同的系统实现了常用正则表达式的各种扩展.在SQL语句中, 形成正则表达式的基础是采用LIKE和MATCHES等关键字实现通配符的匹配.但采用正则表达式的匹配在系统运行时尤其损耗运行时间.例如以下SQL查询语句:

在本查询实例中, 即使设计数据表时在material_no字段上建立了索引, 执行查询时仍然需要对全表进行顺序扫描.如果将其中的条件表达式修改为“WHERE material_no>=10000 and material_no<10100”, 则执行查询请求时就会借助索引来完成, 从而显著提高查询效率.测试在Material表中采用上述两种方式实现查询材料号以100开头且是五位数的材料信息的执行时间及效果.在Material中共有原始数据22831条记录.由测试结果得到使用包含通配符的正则表达式进行查询所用时间为47ms, 而采用条件查询时, 执行查询语句的时间可以忽略不计.

在数据库的存储过程中有时会建立一些临时表, 是为了处理方便, 用于存储一些中间处理结果, 临时表建立后, 可与其它数据表同样的使用, 但与服务器的一次连接会话完成后, 就会自动被系统清除.为了避免出现相对于同一组数据对象的多重排序, 可以在查询过程中将表的目标子集进行排序后的结果生成临时表, 并针对临时表进行下一轮查询操作, 从而提升查询速度.在执行SQL查询语句时, 按照SQL语句的解析顺序, 最先执行的将会是From语句, 如果要对多表进行连接查询时, 我们可以把其中涉及到的单独的数据表先查询并排序出目标临时表结果, 再去连接其它表.例如在该企业仓库管理系统中有以下的功能需求:需查询出库信息还未审核的材料的信息和数量.该查询涉及到Material表、Material_ck_1表、Material_ck_2表的三表连接查询.一种实现方法是不采用临时表直接采用From三表的连接查询;另一种优化查询方法是在From语句中先从Material表、和Material_ck_1表中选出还未审核的出库单号, 建成临时表, 再与Material_ck_2表进行连接获取目标数据.以My E-clipse作为测试工具, 测试在Material表、Material_ck_1表、Material_ck_2表中采用上述两种方式实现查询出库信息还未审核的材料的信息和数量的执行时间及效果.在Material表中共有原始数据记录22381条, Material_ck_1表中共有原始数据记录17395条, Material_ck_2表中共有原始数据记录20条.由测试结果得到使用首先建立临时表进行查询所用时间为47ms, 而不使用了临时表的查询策略系统会先查询出还没有审核的materialckd_no, 再去与material_ck_2连接, 如果只有1/5未审核, 也就是说只有1/5的记录需要连接, 这样一来就大大的节省了连接的时间.

本文以仓储管理系统样例数据库为对象, 以提高数据库查询语句执行效率为目的进行了典型查询优化策略研究.解析了作为测试样例的仓库管理系统数据库的结构, 深入分析了针对该数据库采用的查询语句的若干优化方法并对优化效果进行了测试, 通过测试总结出对提高查询语句效率有用的方法.希望在今后的研究中能够通过对各项关键技术进行整合, 设计和实现一个基于My SQL的通用数据库查询优化的模型框架, 从而节省各类资源, 提高查询效率.