Research And Improve On The Query Optimize Of MySQL

Query is one of the basic and commonly using operations in DBMS. So whether the query has the fast execution speed has become a core problem for the users and designers of DBMS. We mainly focus on four main problems: MySQL parameters self-tuning, realization of MySQL query reuse, MySQL query transform and the optimization of query execution plan in the base of the research on the open source DBMS of MySQL.For the problem of parameters tuning, we focus on the data buffer and log buffer to introduce some tuning parameters of MySQL. And we introduce two tuning ways of MySQL:manual tuning and based on cases tuning, Then we will propose a new dynamic self-tuning methods—mountain algorithm and it solves the former fault.In the problem of query reuse, we aim at two problems—poorly reuse and unable to deal with big result situation in the MySQL and do some improvements. For the former, we add two functions to normalize the key words and remove some invalid and redundant characters in the SQL statements. For the latter, we buffer the query execution plan instead of buffering the query result by adding execution plan buffer module.For the problem of query rewrite, we deduce the algorithm of sub-query merging.Then, we add two rewrite rules—NOT operation rewrite and Outer join transforms to normal join. By the transforming, the query execution speed really becomes fast.For the problem of plan optimization, we mainly focus on two problems—base on rule optimization and base on cost optimization. For the former, we introduce some redefine join types and their priority. For the latter, we present the greedy algorithm which decides the order of tables.All the experiments base on the benchmark test—TPCH and the test data size is 10M. The experimental results obtained from the tests indicate that our work really effectly improve the speed of queries in MySQL.