（计算机软件与理论专业论文）mysql查询优化的研究和改进.pdf

i 摘 要 摘 要 查询是数据库系统中最基本也是最常用的一种操作，因此查询是否具有较快的 执行速度，已成为数据库用户和设计者极其关心的问题。在研究开源数据库管理系 统 mysql 查询优化技术的基础上，主要从 mysql 配置参数调优，mysql 查询重 用功能，mysql 查询重写的相关规则，mysql 计划优化四个方面展开工作。 针对配置参数调优问题，主要从数据缓冲区和日志缓冲区两方面详细介绍了 mysql 相关的调优参数。然后研究了 mysql 调优的两种方法：人工调优和基于案 例的调优，并在此基础上提出了一种动态的自调优算法爬山法，该方法有效解决 了 mysql 自身两种调优方法存在的不足。 对于查询重用问题，主要针对 mysql 重用实现存在的两个问题：重用性不高和 不能合理处理大数据集，做出了改进。对于前者，通过增加规范 sql 语句关键字和 消除多余无效字符的模块来解决。对于后者，通过增加缓存查询执行计划模块来解 决，使用缓存查询计划模块来代替 mysql 原本的缓存查询结果模块。 对于查询重写问题，在研究 mysql 现有查询重写技术的基础上，以带 in 谓词 的子查询为例归纳了其子查询合并的算法，然后提出了两个查询重写规则，not 操 作符重写和外连接转换为一般连接。通过重写，确实提高了 mysql 查询的速度。 针对计划优化问题， 主要研究了 mysql 基于规则的优化和基于代价的优化这两 种方法。对于前者，详细的介绍了 mysql 预定义的一些连接类型及其优先级，对于 后者，研究了 mysql 在决定表连接顺序时所采用的贪婪算法的具体实现。 所有的实验都是采用 tpch 标准测试，数据量为 10m，实验对改进工作进行了 验证，实验结果表明我们的改进工作确实提高了 mysql 的查询速度。 关键词：关键词： 查询优化，查询重用，查询重写，计划优化 ii abstract 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 methodsmountain algorithm and it solves the former fault. in the problem of query reuse, we aim at two problemspoorly 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 rulesnot 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 problemsbase 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 testtpch 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. key words： query optimize, query reuse, query transform, plan optimization 独创性声明 本人声明所呈交的学位论文是我个人在导师指导下进行的研究工作及取得的 研究成果。尽我所知，除文中已经标明引用的内容外，本论文不包含任何其他个 人或集体已经发表或撰写过的研究成果。对本文的研究做出贡献的个人和集体， 均已在文中以明确方式标明。本人完全意识到，本声明的法律结果由本人承担。 学位论文作者签名： 日期：2007 年 6 月 6 日 学位论文版权使用授权书学位论文版权使用授权书 本学位论文作者完全了解学校有关保留、使用学位论文的规定，即：学校有 权保留并向国家有关部门或机构送交论文的复印件和电子版，允许论文被查阅和 借阅。本人授权华中科技大学可以将本学位论文的全部或部分内容编入有关数据 库进行检索，可以采用影印、缩印或扫描等复制手段保存和汇编本学位论文。 保密 ，在_年解密后适用本授权书。 本论文属于 不保密。 （请在以上方框内打“” ） 学位论文作者签名： 指导教师签名： 日期： 2007 年 6 月 6 日 日期：2007 年 6 月 6 日 1 1 绪论 1 绪论 1.1 课题背景课题背景 本课题来源于国家 863 数据库重大专项 “大型通用数据库管理系统 dm5 的研究 开发及其应用”中的一个子项目。该子项目要求对 mysql 的查询优化器及其实现技 术进行分析和改进，指导国产数据库管理系统 dm5 的查询优化器的设计和实现。 高效地管理大批量数据对于大多数的计算机应用领域都是至关重要的。数据库 管理系统(database management system， dbms)是使得单个或多个用户可以方便有效 地管理并操纵大量数据的系统软件，它作为数据处理和管理的标准工具，已经逐步 成为现代信息系统的基础和核心。 数据库管理系统软件是数据处理的核心，国防、政府、金融等要害部门对自主 安全的数据库管理系统提出了非常迫切的要求。因此发展国产数据库管理系统软件， 将对我国软件产业及其相关产业发挥重大影响。在此背景下，武汉华工达梦数据库 有限公司经过努力，研发出拥有完全自主版权的 dm 系列数据库管理系统软件。 关系数据库系统的一个主要功能就是使用户通过强大的关系查询语言访问和修 改数据库中的数据。查询是数据库系统中最基本和常用的一种操作，查询是否具有 较高的执行速度，已成为数据库用户和设计者极其关心的问题。为了提高数据库系 统的性能，对查询进行优化是必不可少的，现今大型商业数据库管理系统的成功很 大程度上要归功于查询优化技术的发展和应用。查询优化是数据库系统中一个极为 关键的技术问题，历来都是研究的热点问题1, 2。 mysql 是一个基于 sql 的 c/s 模式关系数据库管理系统，凭借其易用性，可 移植性，高可靠性，优异的联网和安全性得到越来越多用户的青睐3, 4。更难能可贵 的是 mysql 是一个完全开源的系统，任何人和组织都可以获得它用于非商业用途。 其公开的源代码和较为详细的用户手册为数据库技术的进一步研究提供了便利。 本课题的目的和意义在于通过对 mysql 查询优化器的研究， 特别是针对其查询 优化的实现技术进行分析、改进和实验，从而为 dm5 等国产数据库管理系统中查询 优化器的设计和实现提供一定的指导和参考。 2 1.2 国内外概况国内外概况 1.2.1 查询优化相关技术的研究概况查询优化相关技术的研究概况 目前查询优化研究的主要内容有以下几个方面： 1数据库配置参数调优方法的研究 传统数据库系统参数的调优主要采用人工调优的方法。数据库系统要求数据库 管理员(database administrators, dba)和应用开发人员做大量的人工调节，才能使数 据库的性能保持良好。主要是由数据库管理系统向 dba 提供一些可调的资源参数， dba 根据自己的经验和当前的负载特征调整这些参数，以达到优化性能的目的5, 6。 但这种方法具有低效率和盲目性的缺点， 并且对 dba 和应用开发人员的水平提出了 比较高的要求。某些数据库厂商在经过大量实验和具体实际应用案例的基础上，总 结了一些典型应用案例情况下数据库参数的推荐配置值，从而为用户的调优工作提 供了一定的参考和借鉴，这种方法称为基于案例的调优方法7, 8。基于案例的方法不 是利用以前解决问题得出的规则，而是运用以前解决问题的实际案例结果来解决问 题。但它过于模板化，忽略了实际应用项目之间的差异，不容易推广。针对上述两 种方法存在的缺点，目前有人正在进行动态自调优方法的研究9。利用 dbms 提供 的一些可调参数和选项(如同时使用数据库的用户数目，缓冲区分配参数、工作线程 的数目、锁的数目等)来驱动动态自调优的算法。优化的配置是在调整过程中动态找 到的，期望能够用高效的自动调优方法代替传统的人工调优和基于案例调优的方法。 2查询重用方法的研究 查询重用对于优化查询性能的意义非常重大。相同模式的查询语句一般具有相 同的优化结果，相同模式的查询语句只需作一次语法分析和优化，甚至只需要执行 一次(若前后两次的查询语句完全相同)。为了避免多次的语法分析和优化过程，有必 要对这些查询语句进行缓冲，查询重用缓冲机制有效的解决了这个问题10,11,12。 目前查询重用主要有两类方法的研究： (1) 查询执行结果的重用 查询重用缓冲区缓存了查询语句的文本字符串和执行结果集，当数据库服务器 接收到相同的 sql 语句时，直接将缓存的该 sql 语句的执行结果集发送给用户。 (2) 查询执行计划的重用 查询重用缓冲区缓存的是查询的语法树和执行计划，当数据库服务器接收到相 3 同的 sql 语句时，从缓存中取出该语句的语法树和查询执行计划，然后执行该语句 并将查询结果发送给用户。 3查询重写规则的研究 查询重写是现代数据库系统在查询处理方面所采用的一项先进技术，它将一个 查询语句根据一定的规则，转换为另一个效率更高或更易于优化的查询语句。八十 年代 ibm almaden 研究中心开发的可扩充数据库原型系统 starburst13，首次采用了 这一新的查询优化技术。 目前的研究工作主要集中于以下四大方面14,15： (1) 视图重写技术 视图重写就是将对视图的引用重写为对视图基本表的引用，可以方便优化器做 进一步的优化处理。若没有此重写功能，查询优化器所能生成执行计划的唯一选择 就是先执行视图定义，再将视图的查询结果作为临时表参与查询的其余处理，这种 处理方式在绝大多数情况下效率极低。 (2) 子查询的合并技术 子查询的合并就是将某些特定的子查询重写为多表的连接查询。一般来讲，计 划优化只对同一层次上的查询所生成的计划进行评估和优化。子查询合并的作用在 于将查询语句的层次尽可能的减少，从而方便计划优化的处理。 (3) 等价谓词重写技术 由于具体数据库执行引擎对各种谓词的处理方法不同，某些谓词处理效率要高 于其它谓词，因此把逻辑表达式重写成等价的且效率更高的形式，是提高查询执行 效率的有效方法。 (4) where 和 having 条件化简技术 由于 where 和 having 条件经常由许多表达式组成，而这些表达式在某些时候并 不是孤立的，它们彼此之间存在一定的联系。因此利用等式和不等式的性质，可以 将 where 和 having 条件化简。 如何改进这些模块现有查询重写规则的算法效率，如何发现更多更有效的重写 规则，是当前查询优化的主要热点研究内容之一16,17。 4查询优化算法的研究 对于优化算法的研究仍然是查询优化研究的一个难点和重点。在关系数据库系 统中，最难处理和优化的一个逻辑操作符是连接 join18,19。由于多个关系连接时可 4 以有很多不同的连接次序，因此对应于查询的执行计划的数目会随着该查询包含的 关系个数呈指数级增长，当关系个数很多时，将会导致搜索空间极度膨胀。传统的 数据库管理系统大多采用的是基于 system r 的空间搜索算法或其变形算法，这些 都是近乎于穷举的搜索算法。在决策支持系统中，由于查询所涉及的关系数目通常 都非常大， 使得这类穷举的搜索算法往往无能为力。 一个典型的例子就是 postgres20， 德国弗来堡矿业及技术大学自动控制系的研究员在试图将 postgres dbms 作为用于 电力网维护中做决策支持的知识库系统的后端时，遇到了上面的问题。现在的查询 优化通常都引入了启发式搜索算法或随机算法处理关系数目比较多的情况21,22。 5扩宽查询优化的研究对象 传统的联机事务处理(on-line transaction processing，oltp)应用中，大量使用基 于选择(select)，投影(project)，连接(join)三种基本操作相结合的查询，我们 称作 spj 查询。因此，过去的研究重点大部分都集中于 spj 查询的优化上23。但伴 随着在线分析处理(on-line analytical processing，olap)和决策支持系统(decision support system，dss)等广泛应用，越来越多的非 spj 查询出现在这些应用之中。例 如在决策支持系统中使用的查询，很大一部分都是带有 group by 子句的查询，因 此高效合理地处理带有 group by 聚组函数的查询具有特别的意义。而传统的基于 spj 的查询在处理此类问题时没有考虑到聚组等非 spj 查询的特征，往往不太有效， 当和一个由非 spj 查询构成的视图连接时，处理起来就更加低效24。现在的研究表 明， 可以对非 spj 查询进行转换， 将非 spj 操作提前或推迟处理(称为 group by 节 点的 push down, pull up 技术)，由于非 spj 操作可以大幅度的减少中间结果的 大小，这种处理方式可以极大的提高效率。采用这种技术，同时还可以处理一些涉 及到非 spj 定义的视图的合并操作24,25。 6并行查询的优化 现代数据库应用的发展，企业组织不断增长的大量数据，使得高性能的并行机 或分布式系统逐步取代传统的大型机进行大型数据库的管理和事务处理。但传统的 查询优化技术并没有考虑到并行特性，从而不能最大限度的发挥并行机或分布式系 统的能力。因此当前的查询优化也把并行查询的优化问题作为研究的重点，并行查 询引入了以下的问题： (1) 代价模型的评估。由于查询是并行执行的，因此代价模型会更复杂，因为不 仅要考虑到传统的 cpu+i/o 代价，而且还要将划分的代价，偏斜和竞争资源等代价 5 都要考虑进去26。 (2) 计划空间搜索算法的改进。 并行执行计划的引入将大大增加执行计划的计划 空间，因此，通过考虑所有的候选计划来优化并行查询比优化串行查询的代价要高 得多。所以，通常采用启发式的方法来减少需要考虑的并行执行计划的数目27。 (3) 并行执行计划的表示。 并行执行计划的表示在一定程度上也决定了执行计划 是怎样被并行执行的。例如，对于共享内存和无共享的这两种不同的并行数据库管 理系统体系结构，在某种条件下，可以考虑其表示是否可以合并在一起。 1.2.2 国内外数据库产品查询优化技术的实现概况国内外数据库产品查询优化技术的实现概况 本小节介绍目前国内外商用数据库管理系统中的查询优化技术。 1oracle 的查询优化技术 在配置参数调优方面，除了传统的人工调优和基于案例的调优方法，oracle 还 在数据库服务器上引入了一套复杂的自管理系统，该系统允许数据库自我收集数据， 并利用这些数据自动调节环境参数或者自动修复潜在的问题， 这个特性是使得 oracle 成为目前唯一一个智能化的自管理和自调优的产品。 在查询重用方面，oracle 采用了查询执行结果的重用和查询执行计划的重用相 结合的方式29,30。 oracle 支持的一些主要的查询重写和转换有： (1) 视图重写合并：即将查询中视图的引用用视图定义所代替，但这种转换并不 对所有的视图适用。 (2) 子查询的整平： 即通过一系列的转换将不同种类的子查询转变为连接半连接 或者反连接。 (3) 物化视图的重写： oracle 具有自动重写查询的能力， 以利用物化视图的优点。 若查询中的某个部分可以与一个已存在的物化视图匹配，那么 oracle 能够用建立该 物化视图的基表的应用来替代这个部分。 (4) 星形转换：即 oracle 支持依据星形模式的查询估算。 oracle 支持的计划优化为： oracle 有一个基于代价的优化器决定连接顺序， 连接 方法以及访问路径。优化器选取的每个操作都有一个相应的代价函数，优化器总是 试图产生总体代价最小的操作组合。在优化器的代价模型中，oracle 同时使用 cpu 磁盘 i/o 的代价模型。为了平衡这两者，优化器保存了 cpu 速度和磁盘 i/o 性能 6 的度量，作为优化器统计数据的一部分，oracle 收集优化器统计数据的软件包负责 计算这些度量的标准。 2ibm db2 的查询优化技术 在配置参数调优方面， db2 主要实现了传统的人工调优和基于案例的调优方法， 它在动态自调优方面目前技术还不成熟，没有成型的产品。 在查询重用方面，ibm db2 也采用了查询执行结果的重用和查询执行计划的重 用相结合的方式29,30。 ibm db2 支持的一些主要的查询重写和转换如下：(1) 子查询变为连接的转换； (2) 分组操作下推到连接以下；(3) 利用物化视图或汇总表(使用聚集的物化视图)来 重写查询。 db2 有一个基于代价的优化器决定连接顺序，连接方法以及访问路径。优化器 可以配置成能在不同级别的复杂度上进行操作。在最高级，它使用一个动态程序设 计算法来考虑所有的查询技术，并选择其中代价最小的查询计划。在中级，优化器 不考虑特定的计划或访问方法(或索引)，也不考虑一些重写规则。在最低级中，优化 器采用贪婪算法来选择一个好的但不一定最佳的查询计划。db2 也是同时使用 cpu 磁盘 i/o 的代价模型。优化器使用查询处理的细节模型(考虑内存大小和预取这样 的细节)来获取对 cpu 和 i/o 的开销的准确估计。 3国产数据库查询优化技术的现状 由于国内对数据库技术的研究起步较晚，国产的数据库系统比较少，对查询技 术的研究和应用相对国外的技术来说还不是成熟，但也有一些国产数据库产品已经 使用了查询优化技术31-34， 其中最有代表的产品是东软的 openbase 系列和武汉华 工达梦数据库有限公司的 dm 系列。在配置参数调优方面，两者仅仅实现了传统的 人工调优方法。在查询重用方面，两者采用了查询执行计划的重用的方式，而没有 实现查询结果重用方式。openbase 没有考虑查询重写，针对计划优化在搜索执行 计划时采用了近乎穷举的方法，因此在处理大量表连接时效率往往会比较低下。dm 采用了查询重写和计划优化相结合的方式，但 dm 查询重写模块比较简单，仅仅实 现了一些基本的查询重写功能，另外计划优化的代价评估模型仅仅只考虑了 i/o 开 销，还不完善，因此在处理一些比较复杂的查询时效率可能会很低。 7 1.3 课题主要研究工作课题主要研究工作 本课题将在研究 mysql 源代码的基础上， 主要从配置参数的调优， 查询重用技 术， 查询重写技术和计划优化这四个方面来展开工作。在研究 mysql 查询优化技术 实现的基础上，做出一定的改进，从而为 dm5 等国产数据库管理系统查询优化器的 设计和实现提供一定的参考。本课题的主要工作包括下面几个方面。 1mysql 配置参数调优的研究和改进 主要准备从 mysql 的调优参数和调优方法两方面来展开研究工作。前者，主要 从数据缓冲区和日志缓冲区两方面详细介绍相关的 mysql 调优参数。后者，介绍 mysql 参数调优的两种方法，人工调优和基于案例的调优方法，并在此基础上将提 出一种动态自调优算法爬山法， 期望解决 mysql 自身调优方法存在的不足。 最后， 将通过实验验证人工调优和动态自调优对 mysql 查询执行速度的影响。 2mysql 查询重用的研究和改进 在研究现有 mysql 查询重用部分具体实现的基础上，针对 mysql 实现存在的 两个问题：重用性不高和不能合理处理大数据集，准备进行改进。前者，通过增加 规范化 sql 语句关键字和消除多余的无效字符模块解决。后者，通过增加缓存查询 执行计划功能模块解决，使用缓存查询计划代替 mysql 原本的缓存查询结果模块。 这两个模块均将给出相应的算法实现。最后，将通过实验验证改进后的 mysql 查询 重用模块对查询执行速度的影响。 3mysql 查询重写的研究和改进 在介绍 mysql 查询重写的基础上，以带 in 谓词的子查询为例，归纳 mysql 的子查询合并算法，然后提出两个查询重写规则，not 操作符重写和外连接转换为 一般连接的重写，并给出这两个重写规则的算法实现。最后，将通过实验验证这两 个重写规则对 mysql 查询执行速度的影响。 4mysql 计划优化的研究 这部分准备从基于规则的优化和基于代价的优化两方面来展开工作。前者，通 过详细介绍 mysql 预定义的一些连接类型及其优先级，后者，将归纳 mysql 在决 定表连接顺序时所采用的贪婪算法的具体实现。 所有的实验都将采用 tpch 标准测试，数据量为 10m，实验机器为 p3 800hz,256mb 内存。通过实验将验证改进工作对于 mysql 的查询执行速度的影响。 8 2 mysql 配置参数调优的研究和改进2 mysql 配置参数调优的研究和改进 本章研究 mysql 配置参数调优的问题。首先简要介绍配置参数调优的意义。 然后从数据缓冲区和日志缓冲区两方面介绍 mysql 调优参数，研究了其调优的 两种方法：人工调优和基于案例的调优，并在此基础上提出一种动态自调优算法 爬山法。最后利用实验验证人工调优和动态自调优对 mysql 查询性能的影响。 2.1 配置参数调优的意义配置参数调优的意义 影响 mysql 数据库系统性能效率的因素很多，涉及服务器硬件、网络结构、操 作系统、mysql 数据库系统实现本身采用的算法和数据结构、应用系统、并发用户 数和系统环境配置等3。 而其中最简单和直观改进数据库性能的方法就是调整数据库 的配置参数。 mysql 的配置参数在安装到系统后使用的都是默认值。有些默认值不能充分利 用系统的资源，如 join_buffer_size(执行笛卡尔积连接操作时分配给表的缓冲区)，在 执行没有索引的大表连接应用时，该变量的默认值 128kb 就显得太小了。还有一些 参数的默认值设置不合理，如参数 query_cache_size(存放查询执行结果的缓冲区)缺 省的默认值为 0，表示禁用查询重用的功能，即不缓存查询执行结果。服务器再次接 收到相同的查询时，就不能利用查询重用的优点来提高查询的速度。因此根据实际 数据库系统运行的负载情况，进行相应的数据库配置参数调优，可以有效的减少磁 盘 i/o 操作，对于优化查询执行的速度有重大的意义。 2.2 调优参数的选择和介绍调优参数的选择和介绍 选取适当的调整参数是数据库性能调优的关键一环。首先要选择对系统性能影 响较大的参数，同时要选择彼此间影响较小的参数，如果有的参数选取不当，造成 调整抖动，将不利于性能的调整。 对于参数调优研究的许多成果为我们如何进行调整提供了依据和思路。这些文 章虽各有侧重点，但都不约而同的将影响 dbms 性能的问题集中在：内存管理、缓 冲区分配、锁控制、索引的建立和物理视图的设计等方面。 brown 等人研究了自动内存管理35。重点说明了用户事务模型和缓冲池的内存 9 分配之间的关系，并实现了当所有负载和调整目标都明确时，发现缓冲池配置问题 的方法。 xu 等人将内存自动管理问题当做缓冲池的配置36。 他们认为内存管理的关键是 如何将表和索引放入缓冲池，因此着重讲述如何合理分配缓冲区以放置表和索引的 方法。 chaudhuri 和narasayya通过观测dbms查询优化的自动统计来进行动态资源分 配37。dbms 根据数据的存储位置来决定其查询计划，重点讲述数据的存放对查询 优化的影响。 本文主要选择和介绍 mysql 内存缓冲区相关调优参数。mysql 的内存缓冲区 从结构上主要可分为数据缓冲区和日志缓冲区两大部分。 2.2.1 数据缓冲区数据缓冲区 数据缓冲区是 mysql 在将数据块(包含数据表，索引和数据字典等)写入磁盘之 前以及从磁盘块读取数据之后，数据块所存储的地方。这是 mysql 至关重要的内存 区域之一，若将其设置的太小，会导致缓冲块命中率低，磁盘 i/o 操作特别频繁；若 设置的太大，又会造成与操作系统本身的内存争用，导致系统效率低下，因此合理 的设置其大小非常重要。下面是几个在数据缓冲区中影响查询性能的参数： 1key_buffer_size 索引缓冲区，它的空间为所有数据库共享，最大值可以设置为4gb，实际值最 好设置为空闲内存的25%左右。该变量的缺省默认值为8mb。 2join_buffer_size 全连接缓冲区。在执行笛卡尔积全连接操作时，系统为每个参与连接的表分配 的缓冲区大小。若应用涉及到大量的多表连接操作，且没有适合的索引可用，要进 行笛卡尔积操作时，应该增大该缓冲区的大小。该变量的缺省默认值为128kb。 3read_buffer_size 全表扫描缓冲区。若要对数据库表进行全表扫描操作，数据库系统此时就为每 张表分配该缓冲区。该参数的默认值为60kb。 4sort_buffer_size 排序缓冲区。系统为order by和group by操作分配的缓冲区大小。该参数 的默认值为256kb。 10 5tmp_table_size 临时表缓冲区。针对生成物理查询计划或排序归并连接时，可能要用到一些临 时表。该参数的默认值为20mb。 6query_cache_size 重用查询执行结果缓冲区，用来控制mysql查询重用功能。若启动该功能， mysql服务器接收到相同查询时，可以直接将缓存在缓冲区中的查询结果发送给客 户。该参数的默认值为0，表示禁用该缓存查询结果的功能。显然这是不合理的，需 要根据实际情况进行调整。 2.2.2 日志缓冲区日志缓冲区 日志缓冲区是mysql专门开辟的一段内存用来存放日志文件的。当日志写满了 后，它会要求i/o操作将日志内容写回磁盘中，因此为了减少不必要的磁盘i/o操作， 适当的调整日志缓冲区的大小是非常有意义的。下面我们介绍几个在日志缓冲区中 与查询性能相关的几个参数： 1binlog_cache_size 更新日志缓存区大小。若应用涉及到包含许多复杂sql语句的更新事务时，增 大其大小，可以有效的避免多次的i/o操作。该参数的默认值为32kb。 2sync_binlog 日志同步的频率。一般为了避免频繁的日志同步更新，都将该值尽可能的设置 为比较大的常数n，表明在写了n次更新日志时再将其写回磁盘中。该参数的默认值 为0，表示只有当内存中的日志文件写满后，再将其写回磁盘中保此同步。 当然，影响数据库性能的参数远远不止这些。例如还有应用程序的堆栈分配空 间，线程空间分配，连接数据库的句柄数目，检查点时间间隔的选择，锁的数目等， 但是它们对mysql数据库查询优化方面的影响不如上面详细介绍的几个因素。 2.3 mysql 参数调优方法的研究和改进 参数调优方法的研究和改进 2.3.1 调优方法的研究调优方法的研究 mysql 实现了人工调优和基于案例的调优这两种方法3,4。 1mysql 的人工调优 mysql 提供了一些可调参数，由 dba 在 dbms 的维护中或在应用程序运行过 11 程中手动进行调整。一般情况下通过调整资源配置参数，可以达到性能调优的目的。 但由于调整负载不同、软硬件平台不同以及 dba 水平的千差万别，这种手动调整难 以推广，效率低，需要 dba 具有较高的水平，但就目前而言，这种调优方法仍然是 一种最为常见且应用较为广泛的方法。 2mysql 的基于案例的调优 基于案例的方法是一种利用已成功解决问题的经验解决新问题的方法，基于案 例的方案不是利用以前解决问题得出的规则，而是运用了以前解决问题的实际案例 结果来解决问题。mysql 官方通过大量实验和实际应用案例，提供了在五种标准情 况下，相应的 mysql 服务器配置参数的推荐值。 2.3.2 调优方法的改进调优方法的改进 在实际的 dbms 系统中， 有太多的资源需要考虑， 它们之间的关系也过于复杂， 现实的应用系统往往也千差万别。人工调优主要依赖于人，效率往往比较低下，基 于案例的调优忽略了系统的动态性和不同系统间存在的差异，单纯的依赖人工调优 和基于案例的优化方法是不够的。因此这里提出一种动态的自调优算法爬山法来 解决前两种方法存在的不足。 设h(h1,h2,h3)是一个参数向量，其中h1，h2，h3分别是影响数据库服务器性能 的三个可调节参数。每个参数都应该有一个最小值和最大值，因此对该参数进行调 优时只能在这两个值范围之间进行调整。假设每一次参数的变化调整都取当前参数 的一个相邻值，即只能将该参数上调一个单位或者下调一个单位。并且假定一次只 调整一个参数，而不能同时调整多个参数。下面通过一个例子来说明这一点。 设有参数向量v=(c1,c2,c3)，参数c1的取值范围为0，1，2；参数c2的取值范围 为1，2；参数c3的取值范围为9，10，11。假设参数向量v的当前取值为v=(1,2,11)， 那么能够调整的方法有：调整参数c1时，可以将参数向量调整为v=(0,2,11)或 v=(2,2,11)；调整参数c2时，可以将参数向量调整为v=(1,1,11)；调整参数c3时，可 以将参数向量调整为v=(1,2,10)或v=(1,2,9)。 在不断调整参数的过程中， 我们也在不断的评估此时数据库服务器的性能。 一种 最简单的方法就是让数据库服务器在每调整一次参数完毕后就执行相同的sql语 句，通过观察sql语句执行时间的变化来评价数据库服务器性能。一般服务器的配 置列表涉及到的参数非常多，可以从中选择几个对查询执行影响最大的参数组成待 12 调整参数向量，进行相关的自调优，下面是该算法的具体实现： 算法 2.1： 动态调优爬山法 输入： 当前配置列表，待调整参数向量和向量中相应每个参数的取值范围 输出： 最优配置列表 算法描述： step 1: 用当前配置列表初始化最优配置列表； step 2： 设数据库服务器性能提高标志flag为否； step 3： do 若性能提高标志flag为否; 最高评估性能 = 当前的评估性能; step 4： for(i = 1;i 最高评估性能) 最高评估性能= 当前评估性能； 将当前配置列表相应的该参数值下移一个单位； 最优配置列表 = 当前配置列表； 性能提高标志flag赋为真； step 6： else if (第i个待调整参数向上移动一个单位还没越界) if(移动后的评估性能最高评估性能) 最高评估性能= 当前评估性能； 将当前配置列表相应的该参数值上移一个单位； 13 最优配置列表 = 当前配置列表； 性能提高标志flag赋为真； step 7： else ; step 8: while(已经达到了最高性能) step 9: return 最优配置列表; 这种动态的自调优方法， 由于在运行过程中不需要人工的干预， 解决了人工调优 低效的问题，并且它是根据当前服务器的性能进行动态调优的，解决了基于案例的 调优忽略系统动态性的问题。因此它解决了 mysql 自身两种调优方法存在的不足。 2.4 实验测试和分析 实验测试和分析 在这里进行调优实验测试，验证参数调优前后对于 mysql 查询执行速度的影 响。本实验选取 tpch 标准测试的 q4，q7 和 q12 三个语句进行测试，做三个实验。 实验 1 人工调整参数的默认值，为了简化调优过程和便于观察结果，这里只选择 key_buffer_size，read_buffer_size 和 sort_buffer_size 三个参数进行调整，验证人工自 调优对查询执行速度的影响。实验 2 和实验 1 类似，只不过我们采用了算法 2.1 对参 数进行动态调整，验证动态自调优爬山法对查询执行速度的影响。实验 3 中只调 整参数 query_cache_size 的值，即单独测试缓存查询结果对查询优化速度的影响。其 中每个测试用例都单独执行 5 次，这里显示的执行时间表示 5 次执行时间的平均值。 具体测试用例的详细说明可以参考附录。 1实验 1 结果及其分析 表 2.1 实验 1 配置参数的取值 参数 key_buffer_size read_buffer_size sort_buffer_size 缺省默认值 8mb 64kb 256kb 人工调优后的值 30mb 8mb 16mb 14 表 2.2 实验 1 人工自调优前后语句执行时间对比 测试用例 q4 q7 q12 调优前 0.0425s 0.0575s 0.6900s 调优后 0.0325s 0.0350s 0.4050s 分析：由上面实验1结果分析可知，经过人工调优后q4、q7和q12语句的查询执 行速度都有不同程度的提高。因此即使在数据量不大(本实验测试数据仅为10m)的情 况下，通过调整配置参数，可以达到比较好的优化查询的效果。 2实验 2 结果及其分析 实验 2 利用算法 2.1 来动态调整配置参数的值， 假设参数 key_buffer_size 取值为 4mb,32mb，调整单位为 512kb，read_buffer_size 取值为64kb,4mb，调整单位 为 256kb，sort_buffer_size 取值范围256kb,2mb，调整单位为 64kb。 这三个参数的缺省配置值即为动态自调优过程的初始值。实际上，这些参数的取值 范围和调整单位可以根据实际的应用情况进行进一步的调整。为了保证能够退出动 态自调优过程，算法 2.1 在实现时设置了一个计数器。 表 2.3 实验 2 配置参数的取值 参数 key_buffer_size read_buffer_size sort_buffer_size 缺省默认值 8mb 64kb 256kb 动态调优后的值 4mb 832kb 320kb 表 2.4 实验 2 动态自调优前后语句执行时间对比 测试用例 q4 q7 q12 调优前 0.0425s 0.0575s 0.6900s 调优后 0.0240s 0.0320s 0.4050s 分析：由上面实验2结果分析可知，经过动态自调优后q4、q7和q12语句的查询 执行速度都有不同程度的提高。因此即使在数据量不大(本实验测试数据仅为10m) 的情况下，通过动态调整配置参数，可以达到比较好的优化查询的效果。将实验2和 实验1进行比较，我们发现动态自调优可以根据查询语句实际的情况和当前系统的动 15 态负载，合理地调整相应的配置参数值，避免了dba调整时的盲目性和低效率。而 且实验2的结果同时显示，动态自调优对查询速度的提升往往好于人工自调优，而且 使用更少的系统资源就能达到较好的优化效果。 3实验3结果及其分析 在进行实验3之前，假设mysql数据库服务器已经缓存了q4，q7，q12这三个 语句的文本字符串和相应的查询结果集。 表2.5 实验3中参数的取值 表2.6 实验3调优前后语句执行时间对比 q4语句 q7语句 q12语句 测试用例 测试1 测试2 测试1 测试2 测试1 测试2 执行时间 0.04s 0.00s 0.06s 0.00s 0.69s 0.00s 分析：由实验3结果分析可知，测试1将query_cache_size参数设置为0，分别执行 q4、q7和q12语句时，由于禁用了查询重用功能，因此不能利用已经缓存的执行结 果，故执行时间不为0。测试2中将query_cache_size参数设置为30m，打开查询重用 功能，因此可以利用缓存了的q4、q7和q12语句及其执行结果集，故查询执行时间 为0。因此在mysql服务器启动时，开启查询重用功能，对于某些需要多次重复执行 相同语句的应用，可以达到比较好的优化查询的效果。 通过上面的分析和实验比较可知，合理调整mysql的配置参数，对于查询优化 可以起到比较好的效果，而且这种方法也比较容易和直观。具体实施时还可以利用 windows操作系统自带的任务管理器和mysql自带的性能诊断工具如mysqlshow等 来帮助我们分析此时mysql服务器的相关状态和各个相关配置参数的使用情况。因 此这种方法，应该作为我们进行查询优化时最先采取的措施和手段，也是比较有效 和最直接的手段。 参数 query_cache_size 缺省默认值 0 改变后的值 30mb 16 2.5 本章本章小小结结 本章主要研究 mysql 配置参数调优问题。 首先介绍了配置参数调优的意义。 接 着主要从数据缓冲区，日志缓冲区两方面详细介绍了 6 个 mysql 的相关调优参数。 然后研究了 mysql 调优的两种方法，人工调优和基于案例的调优，分析了各自的优 缺点，并在此基础上提出了一种动态自调优算法爬山法，给出了该算法的具体实 现，该算法有效的解决了人工调优和基于案例的调优方法存在的不足。通过实验验 证了人工调优和动态自调优对 mysql 查询执行的影响， 实验结果表明进行参数调优 可以优化查询执行的速度，并且动态自调优的方法在优化查询性能方面要好于人工 调优的方法。因此这种方法，应该作为我们进行查询优化时最先采取的措施和手段， 也是比较有效和最直接的手段。 17 3 mysql 查询重用的研究和改进 3 mysql 查询重用的研究和改进 本章重点研究和改进 mysql 的查询重用功能。 首先简要介绍 mysql 查询重 用的实现，并针对其实现存在的两个问题：重用性不高和不能合理处理大数据集， 做出改进。前者，通过增加规范化 sql 语句关键字和消除多余无效字符的模块解 决。后者，通过增加缓存查询执行计划模块解决。最后用实验验证改进后的查询 重用模块对 mysql 查询执行速度的影响。 3.1 mysql 查询重用的实现及问题查询重用的实现及问题 mysql 仅仅实现了查询重用的第