基于K-means的SAMP系统数据库查询性能优化策略
|
摘要
中科院仪器设备共享管理平台(以下简称为SAMP)系统有效解决了各科研单位间仪器设备管理封闭、共享困难和运行效率低的棘手问题.同时,可以及时了解各类仪器的使用情况、共享情况,为各级业务主管部门展开科学高效的管理工作提供良好的决策依据.当SAMP系统应用数据库中存储的数据量达到百万级时,对数据库中预约表和用户表(或仪器表)进行连接查询时,将导致数据表查询性能的下降,从而影响整个SAMP系统的性能.目前主流的解决方案是采用Hash取模算法对数据表进行水平切分,但预约表中的主键为自动递增的整数,并没有实际意义,所以优化效果不理想.由于预约的用户和被预约的仪器在地理区域上呈现一定的聚集性,因此本文提出了一种基于K-means聚类算法的分表策略,采用该策略能够将预约表的查询性能提升至少70%.
Instrument and equipment sharing management platform for Chinese Academy of Sciences can solve the problems of closed management, difficult sharing, and low operation efficiency of instruments and equipment among scientific units effectively. Meanwhile, users can learn the using and the sharing situation of various instruments through the system. The SAMP system can provide decent decision-making basis for scientific and efficient management work of competent business departments at all levels. So when the data, which belong to the apparatus apply info table, reaches the million scale, the query performance will decline quickly because of using joining query. At present, the solution is using sharding, just like Hash fetching algorithm. Because of the meaningless ID, this way is ineffective. There is a certain degree of aggregation in geographical areas between users and instruments, so a strategy that based on the K-means algorithm is used in this study. The result shows it can improve the query performance at least 70%.
Instrument and equipment sharing management platform for Chinese Academy of Sciences can solve the problems of closed management, difficult sharing, and low operation efficiency of instruments and equipment among scientific units effectively. Meanwhile, users can learn the using and the sharing situation of various instruments through the system. The SAMP system can provide decent decision-making basis for scientific and efficient management work of competent business departments at all levels. So when the data, which belong to the apparatus apply info table, reaches the million scale, the query performance will decline quickly because of using joining query. At present, the solution is using sharding, just like Hash fetching algorithm. Because of the meaningless ID, this way is ineffective. There is a certain degree of aggregation in geographical areas between users and instruments, so a strategy that based on the K-means algorithm is used in this study. The result shows it can improve the query performance at least 70%.
引文
1韦美雁,段华斌,周新林.大数据环境下的MySQL优化技术探讨.现代计算机,2018,(10):68-72.
2 Herodotou H,Borisov N, Babu S. Query optimization techniques for partitioned tables. Proceedings of the 2011ACM SIGMOD International Conference on Management of Data. Athens, Greece. 2011. 49-60.
3刘阳娜.大数据下的MySQL数据库的效率优化.信息通信2017,(12):111-112.[doi:10.3969/j.issn.1673-1131.2017.12.049]
4刘晓光.基于MySQL的分布式SQL数据库的设计与实现[硕士学位论文].北京:中国科学院大学,2016.
5韩兵,李晶晶,方英兰.基于JDBC数据管理与查询优化的研究.计算机技术与发展,2018,28(9):176-180.[doi:10.3969/j.issn. 1673-629X.2018.09.036]
6赵曦,李颖,徐江.利用垂直分割技术的分布式数据库设计研究.控制工程,2018, 25(1):154-159.
7 Khan M, Khan MNA. Exploring query optimization techniques in relational databases. International Journal ofDatabase Theory and Application,2013, 6(3):11-20.
8董献伦.基于关系型数据库的数据切分问题研究[硕士学位论文].济南:山东大学,2016.
9王照清.大数据环境下数据查询优化技术应用研究[硕士学位论文].北京:北方工业大学,2016.
10韩兵,王照清,廖联军.基于MySQL多表分页查询优化技术.计算机系统应用,2016, 25(8):171-175.
11任满杰,何文义,付华.数据库分割技术及其对数据库系统的影响.阜新矿业学院学报(自然科学版),1994, 13(4):102-105.
12孙伟东,夏秀峰,马宗民.利用数据库实现分布式任务的程序和数据存储.航空电子技术,2009, 40(1):16-19.[doi:10.3969/j.issn.1006-141X.2009.01.004]
13梁双,周丽华,杨培忠.基于聚类分析分库策略的社交网络数据库查询性能与数据迁移.计算机应用,2017, 37(3):673-679.
14孙辉.MySQL查询优化的研究和改进[硕士学位论文].武汉:华中科技大学,2017.
15吴金朋.一种大数据存储模型的研究与应用[硕士学位论文].北京:北京邮电大学,2013.
2 Herodotou H,Borisov N, Babu S. Query optimization techniques for partitioned tables. Proceedings of the 2011ACM SIGMOD International Conference on Management of Data. Athens, Greece. 2011. 49-60.
3刘阳娜.大数据下的MySQL数据库的效率优化.信息通信2017,(12):111-112.[doi:10.3969/j.issn.1673-1131.2017.12.049]
4刘晓光.基于MySQL的分布式SQL数据库的设计与实现[硕士学位论文].北京:中国科学院大学,2016.
5韩兵,李晶晶,方英兰.基于JDBC数据管理与查询优化的研究.计算机技术与发展,2018,28(9):176-180.[doi:10.3969/j.issn. 1673-629X.2018.09.036]
6赵曦,李颖,徐江.利用垂直分割技术的分布式数据库设计研究.控制工程,2018, 25(1):154-159.
7 Khan M, Khan MNA. Exploring query optimization techniques in relational databases. International Journal ofDatabase Theory and Application,2013, 6(3):11-20.
8董献伦.基于关系型数据库的数据切分问题研究[硕士学位论文].济南:山东大学,2016.
9王照清.大数据环境下数据查询优化技术应用研究[硕士学位论文].北京:北方工业大学,2016.
10韩兵,王照清,廖联军.基于MySQL多表分页查询优化技术.计算机系统应用,2016, 25(8):171-175.
11任满杰,何文义,付华.数据库分割技术及其对数据库系统的影响.阜新矿业学院学报(自然科学版),1994, 13(4):102-105.
12孙伟东,夏秀峰,马宗民.利用数据库实现分布式任务的程序和数据存储.航空电子技术,2009, 40(1):16-19.[doi:10.3969/j.issn.1006-141X.2009.01.004]
13梁双,周丽华,杨培忠.基于聚类分析分库策略的社交网络数据库查询性能与数据迁移.计算机应用,2017, 37(3):673-679.
14孙辉.MySQL查询优化的研究和改进[硕士学位论文].武汉:华中科技大学,2017.
15吴金朋.一种大数据存储模型的研究与应用[硕士学位论文].北京:北京邮电大学,2013.