MySQL分区技术在海量系统日志中的应用_陈江.pdf

2023.4电脑编程技巧与维护1概述在数字经济时代，随着信息技术的不断发展，许多企业每个月都产生巨大的日志，短时间内海量日志查询面临着极大的挑战。因为传统的MySQL数据库存储技术处理大数据量的能力不足，MySQL数据库表是以文件方式存储到磁盘中，对于大数据量的情况通过索引的存储结构搜索数据，显然非常耗时。如果想搜索和实时分析日志，传统的MySQL单表单区的方式无法在一个合理的响应时间内处理大量的数据，因此MySQL分区技术应运而生。2MySQL 分区技术MySQL分区是使用MyISAM引擎的一张表主要对应3个文件，（1）frm存放表结构；（2）myd存放表数据；（3）myi存表索引。如果一张表的数据量太大，那么myd、myi也会变得很大，查找数据就会变得很慢。这时可以利用MySQL的分区功能，在物理上将这一张表对应的3个文件，分割成许多个小块，这样在查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在对应位置查找。如果表的数据太大，可能一个磁盘放不下，就可以把数据分配到不同的磁盘里。数据库分区技术就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。通俗地讲，表分区是将一张大表，根据条件分割成若干张小表。假设某日志表的记录超过了700万条，为了更好地体现分区的优势，在进行表分区时，可以优先选择日志表的一些特性作为分区的条件，例如，记录时间、日志类型等。分区类别主要有RANGE分区、LIST分区、HASH分区、KEY分区、子分区。在此主要探讨RANGE分区在海量日志中的研究与应用。在某物联网软件运行过程中，需要每隔20 min或30 min检测终端设备是否通联，每检测一个终端设备需要记录下该设备的运行状态、设备IP、Mac等信息。在上百个设备中，平均每天能产生上千条设备运行日志，一个月便能产生数万条日志。用户需要每天不定时查看这些运行日志，以便观测设备的运行状态。用户可以通过日期条件查询每天设备运行状态并以表格的形式显示出来。随着软件业务的不断扩展，设备也逐渐增多，人工检测的机会也越来越多。随着时间的推移，设备日志数量与日俱增，总数量超过40万条时，数据库服务器的压力骤增，进而造成用户检索设备日志的时间延长，大大降低了用户使用的频率，影响了系统的工作效率，并且影响与设备日志表有关联的其他功能，导致其功能响应速度越来越慢。为了能够更好地提高系统的性能，节省查询的时间，提高设备日志的查询效率，保障设备日志功能正常运行，更好地为用户提供便利，在此对系统进行优化，改变了数据库单表的结构，采用分区管理数据的模式，极大改善了系统的性能。3设计方案与实现为了更直观地展示此次需求改造方案，利用Visio工具，制作改造方案，如图1所示。主要对设备日志表进行优化和改造，针对设备日志表，按照提前约定的分区规则，利用MySQL数据库分区技术对表中的数据进行分区存储，使其存储到不同的分区文件中。对于与设备日志记录表有关联的功能模块，采用数据层面的优化方式，减少从数据量较大的表中查询数据的次数。MySQL 分区技术在海量系统日志中的应用陈江，王政山，隋福宁（武警指挥学院，天津300000）摘要：随着信息技术的飞速发展，系统的业务功能不断扩大，产生的日志与日俱增，导致应用软件的运行速度越来越慢，不能很好地满足用户对软件性能的需求。基于此，重点研究了 MySQL 分区技术在大数据量软件日志中的应用，通过 MySQL 数据库的分区技术提升数据库的性能，从而保障软件的稳定运行，为大数据量的系统提供优质服务。关键词：分区；MySQL 分区；日志；数据库优化图1改造方案说明分区数据查询分区数据入库定时检测任务用户检测需求其他模块间调用设备日志表 设备日志分区存储p20200731p20200630每日设备日志汇总表存储汇总数据自动从分区表中获取数据每日日志汇总97DOI:10.16184/prg.2023.04.0462023.4电脑编程技巧与维护3.1数据库结构改造通过观察，分析设备日志记录表的数据可知，从2020年系统部署使用以来，数据量与日俱增，总计50多万条，相当于每天产生600多条数据，平均每月的数据量2万条左右。针对这种情况，为了不增加维护成本，采用对数据表进行分区处理的方式，并按照数据量级进行分割数据。当数据量规模较小时，以5万为单位级进行数据分割；当数据量规模较大时，以10万为单位级进行数据分割。分区表具有较强的可维护性，在面对数以万计的数据时，能够非常容易地将分区合并、新增和删除，使数据更容易被管理和维护。在数据查询方面，能够加快数据的查询速度、提高查询的效率，但分区技术不能够提高全表检索的速度，只能通过条件查询来加快查询的速度。设备日志记录表（device_record_log）的字段设计包含自增主键字段（device_id）、设备名称（device_ti-tle）、设备类别（device_type）、设备序号（device_in-dex）、设备型号（device_model）、设备（sn）、设备Mac（device_mac）、设备网址（device_ip）、子网掩码（de-vice_netmask）、设备网关（device_gateway）、设备检测日期（device_checkin_date）、检测类型（checkin_type）、故障类型（error_type）、故障备注（error_remark）、备注（error_remark1）。采用水平分区的方式对设备日志记录表进行优化。首先，查询device_id的最大值，计算需要分区的最小数量；然后，调整单表结构，将单表文件拆分成多份文件，成为分区表，使得一张单表具有多张表的存储功能，在应对存储大数据量时，不致于让单表的压力过大，数据的查询和存储效率明显提高。详细步骤如下：打开命令框，登录MySQL，打开指定数据库，输入以下SQL语句，使device_record_log表具有分区结构。alter table device_error_log partition by RANGE(device_id)(PARTITION PART01 values less than(50000)PARTITION PART02 values less than(100000)PARTITION PART03 VALUES less than(200000)PARTITION PART04 VALUES less than(300000)PARTITION PART05 VALUES less than(400000)PARTITION PART06 VALUES less than(500000)PARTITION PART07 VALUES less than(600000)PARTITION PART08 VALUES less than(700000)PARTITION PART09 VALUES less than(800000)PARTITION PART10 VALUES less than(900000);device_record_log表中的数据是device_id小于5万的数据，全部分割到P1这个分区中；大于5万并小于10万的数据存储到P2这个分区中；大于10万并小于20万的数据存储到P3这个分区中；大于20万并小于30万的数据存储到P4这个分区中。以此类推，将原有单表的数据分别存储到对应的分区中，将原有的单表对应单文件存储模式，改为单表多文件存储模式，在大规模数据下，减轻单文件存储的压力。在指定条件下的查询，数据库分区搜索引擎会根据索引在相应的表分区中搜索。例如，需要查询的是某月的数据，MySQL数据库会先通过分区层打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，如果可以，则调用对应的存储引擎接口访问对应分区的数据；否则，异步读取各分区的数据。由于分区数据量远小于只有单表存储的数据量，相当于小文件操作，从而极大提高了读取效率，节省了查询时间，加快了数据库的响应速度，实现了数据查询速度的优化。3.2功能模块改造海量日志查询是一个非常耗性能的过程，对数据库的性能要求非常高。如果能够在结合MySQL数据库分区技术的基础上，合理地改造查询功能，使其不要在非必要的时候进行全表、全区数据扫描，就能够加快查询的速度，实现对系统功能的优化。假如需要得到最近一个月的日志数据，以每月2万多条数据计算，一个分区至少存储5万条，一个月的数据在同一个分区中，这样查询数据不用跨区扫描，节省时间；假如需要查询近一年的日志数据量，此时日志数据已经存储在不同分区中，跨区扫描的时间将比在同一个分区的时间长很多，需要将查询功能优化处理，利用单分区查询的优势，按照分区段的限制，先查询一个分区内的数据，再根据条件查询另一个分区内的数据，最后分页展示给用户，达到快速显示的效果，提高系统的性能。示例SQL语句如下。SELECT a.*FROMdevice_error_log a wherea.id 50000and b.id 100000 and b.id 500000 and b.id 6000004实验及结果分析4.1实验环境硬件环境：实验均在笔记本电脑上进行、Windows 1164位操作系统、Intel(R)Core(TM)i7-10875H处理982023.4电脑编程技巧与维护4LI Q,WANG H,WANG L C.Research of P2P baseddistributed network management J.Computer engi-neering,2006,32(13):1-13.5谢松.基于蚁群算法的无线传感器网络节点定位算法研究D.武汉:华中师范大学,2014.6DRAGOS N,BADRI N.DV based positioning in AdHoc networks J.Telecommunication systetns,2003,22(1-4):267-280.7丛珊,陈桂芬.无线传感器网络DV-Hop定位算法J.长春理工大学学报（自然科学版）,2018,41(2):105-109.器、16G内存、500G硬盘。软件环境：实验选用MySQL5.6版本。4.2实验结果及对比分析在相同的实验环境下，针对同一张表，模拟不同数量级数据，将具有表分区功能的表和原生表做对比实验。在同样的联合多张表SQL语句下，分两种情况测试数据查询的效率：一种具有表分区；另一种不具有表分区。从表1中可以看出随着数据量不断增大，改造前与改造后所需时间差距明显拉大，改造后所需时间比改造前缩短很多，查询效率明显提高。5结语MySQL分区技术将数据库的优势引入处理海量日志数据的项目中，降低了系统项目使用后期更换数据库的风险，满足了企业对系统开发的需求，减少了系统维护的成本，延长了系统项目的使用寿命。从MySQL分区技术的概念理论入手，概述了数据库分区技术的使用场景，深入讲解了MySQL分区的分类及分区的使用方法，通过实验数据验证数据库分区技术的可行性和优越性。测试结果显示，将MySQL数据库分区技术应用到海量日志的系统项目中，成功解决了企业系统应对海量数据时存在的性能问题，达到了预期的效果。参考文献1王宏伟.MySQL分区技术在传统项目中的应用J.信息与电脑（理论版）,2021,33(9):10-13.2白俊,郭贺彬.基于ElasticSearch的大日志实时搜索的软件集成方案研究J.吉林师范大学学报（自然科学版）,2014,35(1):85-87.3何江银,黄岷,李端肃,等.基于FEK的大数据网络日志分析J.信息记录材料,2019,20(5):149-150.4邹燕飞.云计算技术在海量数据挖掘中的应用研究J.自动化与仪器仪表,2017(6):185-186.5鲜征征,叶嘉祥.一种改进的ELK日志采集与分析系统J.软件导刊,2019,18(8):105-110.6谭敏,谭家兴.基于大数据日志的智能运维系统设计J.长江工程职业技术学院学报,2019,36(1):10-12.7单康康,王佶,常晓洁,等.基于大数据的校园网海 量 日 志 分 析 平 台 研 究J.信 息 通 信,2017(3):154-155.8姜承尧.MySQL技术内幕:SQL编程M.北京:机械工业出版社,2012.9叶云霜,林伟华,刘福江,等.关系数据库中海量要素存储的分区优化研究J.计算机技术与发展,2020,30(1):167-173.表1改造前后的速度对比功能对比类别改造前所需时间改造后所需时间5万条数据1.3430.0910万条数据1.7560.7820万条数据2.8170.8830万条数据4.2880.9650万条数据6.3891.0260万条数据9.0141.87单位：s(上接第69页)99