【作者】 孙辉；

【导师】 吴恒山；

【作者基本信息】 华中科技大学 ， 计算机软件与理论， 2007， 硕士

【摘要】 查询是数据库系统中最基本也是最常用的一种操作,因此查询是否具有较快的执行速度,已成为数据库用户和设计者极其关心的问题。在研究开源数据库管理系统MySQL查询优化技术的基础上,主要从MySQL配置参数调优,MySQL查询重用功能,MySQL查询重写的相关规则,MySQL计划优化四个方面展开工作。针对配置参数调优问题,主要从数据缓冲区和日志缓冲区两方面详细介绍了MySQL相关的调优参数。然后研究了MySQL调优的两种方法:人工调优和基于案例的调优,并在此基础上提出了一种动态的自调优算法-爬山法,该方法有效解决了MySQL自身两种调优方法存在的不足。对于查询重用问题,主要针对MySQL重用实现存在的两个问题:重用性不高和不能合理处理大数据集,做出了改进。对于前者,通过增加规范SQL语句关键字和消除多余无效字符的模块来解决。对于后者,通过增加缓存查询执行计划模块来解决,使用缓存查询计划模块来代替MySQL原本的缓存查询结果模块。对于查询重写问题,在研究MySQL现有查询重写技术的基础上,以带IN谓词的子查询为例归纳了其子查询合并的算法,然后提出了两个查询重写规则,NOT操作符重写和外连接转换为一般连接。通过重写,确实提高了MySQL查询的速度。针对计划优化问题,主要研究了MySQL基于规则的优化和基于代价的优化这两种方法。对于前者,详细的介绍了MySQL预定义的一些连接类型及其优先级,对于后者,研究了MySQL在决定表连接顺序时所采用的贪婪算法的具体实现。所有的实验都是采用TPCH标准测试,数据量为10M,实验对改进工作进行了验证,实验结果表明我们的改进工作确实提高了MySQL的查询速度。更多还原

【Abstract】 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.更多还原