Research On Query Optimization Of Distributed Database Middleware Mycat

With the development of big data,huge amounts of data are generated.But the single-node database has storage and processing bottlenecks.Therefore,distributed database middleware has become a solution to the massive data storage and processing.Based on previous researches,this thesis studies the query optimization of distributed database middleware Mycat.Aiming at the phenomenon that the efficiency of shard key query in Mycat is higher than that of non-shard key query,a shard key selection strategy based on hot and cold data was proposed,which makes the query operation hit the shard key query with a high probability,thereby improving system performance.In the two-table cross-database equivalence join query,for the data broadcast stage,the large amount of broadcast data affects the query efficiency,this thesis proposed a two-table cross-database equivalence join query optimization strategy based on improved BloomFilter.It filtered the join broadcast data,reduced the amount of broadcast data and improved the efficiency of the two-table cross-database query;In multi-table cross-database non-equivalent join query,the execution sequence of the two-table join query directly affects the efficiency of multi-table query,for this problem,this thesis proposed a multi-table cross-database non-equivalent join query optimization strategy based on the ant colony algorithm.It improved the multi-table cross-database query efficiency.The work done by this thesis is as follows:1)For the shard key selection strategy,the smoothing coefficient of the exponential smoothing method was researched,and a fast rate calculation method was given.The predecessor cost estimation model was optimized to make it more consistent with the selection strategy.And the thesis applied the strategy to the test data set of intelligent hardware Internet of things for experimental analysis,then selected the optimal shard key.2)For the two-table join optimization,the BloomFilter algorithm was improved,and an extended BloomFilter was proposed.The extended BloomFilter has a lower misjudgment rate and higher scalability than the traditional BloomFilter and K-divided BloomFilter.And through experimental analysis,compared with the index query optimization,BloomFilter algorithm had the advantages of maintenance and migration.3)For the multi-table join optimization,the ant colony algorithm was applied,and an uneven initial pheromone distribution strategy was proposed.The join transmission cost and the join time cost were considered comprehensively to perform multi-objective optimization.The experiment verified that in the optimization of multi-table join,the path derived by the ant colony algorithm was an optimal solution or an approximate solution to the optimal solution.4)The optimized Mycat was applied to the actual intelligent hardware Internet of Things project,and the experiment was conducted to verify that Mycat can improve the query efficiency in the actual project after optimization.The query optimization strategy proposed in this thesis,verified by experiments,can improve the query efficiency of each complex query,and can improve Mycat's ability to deal with massive data.