基于MySQL数据库性能优化的实验报告

广州中医药大学医学信息工程学院实验报告课程名称：网络数据库编程专业班级：计算机科学与技术(2012)级学生姓名：张鹏燕2012081076薛丽梅2012081080杨晓珠2012081018翁浩彬2012081007实验名称：数据库性能优化实验成绩：课程类别：□限选公选□其它□数据库系统性能优化（基于MySQL数据库，采用一定的查询优化方案，用MySQL的内部数据说明优化前与优化后CPU的情况）一、实验背景数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）以及联机分析处理(OLAP)是银行、企业、政府等部门最为重要的计算机应用之一。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大，而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句。举例来说，如果数据的量积累到一定的程度，比如一个银行的账户数据库表信息积累到上百万甚至上千万条记录，全表扫描一次往往需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟，由此可见查询优化技术的重要性。小组通过不少的科研文档中发现，许多程序员在利用一些前端数据库开发工具（如PowerBuilder、Delphi等）开发数据库应用程序时，只注重用户界面的华丽，并不重视查询语句的效率问题，导致所开发出来的应用系统效率低下，资源浪费严重。因此，如何设计高效合理的查询语句就显得非常重要。通过调查得出许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的SQL语句关系不大，这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。查询计划是用户所提交的SQL语句的集合，查询规划是经过优化处理之后所产生的语句集合。本实验以应用实例为基础，结合数据库理论，介绍查询优化技术在现实系统中的运用。二、实验优化方案DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。在实际的数据库产品(如Oracle、Sybase等)的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表所得到的信息来估计不同的查询规划的代价，然后选择一个较优的规划。虽然现在的数据库产品在查询优化方面已经做得越来越好，但由用户提交的SQL语句是系统优化的基础，很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效，因此用户所写语句的优劣至关重要。本实验中，系统所做查询优化我们暂不讨论，下面的实验过程我们小组将重点说明改善用户查询计划的解决方案。归纳总结，MySQL数据库查询优化的方法主要分为以下五类：1）使用索引，“CREATEINDEX”。MySQL允许对数据库进行索引，以此能迅速查找记录，从而无需一开始就扫描整个表，由此显著的加快查询速度。每个表最多可以做到16个索引，此外MySQL还支持多列索引及全文检索。2）使用“LIMIT1”取得唯一行。此方法可以用于查询数据量较少的数据表，在已知所查询的结果仅有一条记录时，在SELEST语句条件下加上“LIMIT1”限制条件可以直接加快查询速度。3）尽量少使用“SELECT*”语句中的通配符*，明确写出查询内容。4）调整内部变量。MySQL的性能开放，因而用户可以轻松地进一步调整其缺省设置以获得更优的性能及稳定性。可变缺省设置值：1改变缓冲区长度（key_buffer）2改变表长（read_buffer_size）3设定打开表的数目的最大值（table_cache）4对缓长查询设定一个时间限制（long_query_time）5）用连接查询替代子查询通过以上五种方法可以对MySQL数据库的查询操作作出优化，以提高用户的体验。三、实验过程实验准备：建立两个测试数据表：citycountry（如下）建立city表：建立country表：注：showprofile是由JeremyCole捐献给MySQL社区版本的。默认的是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统计报表,把profiling设为1。说明：具体为大家解释以下三种方法，说明MySQL查询优化的结果。1.建立索引。MySQL允许对数据库进行索引，以此能迅速查找记录，从而无需一开始就扫描整个表，由此显著的加快查询速度。每个表最多可以做到16个索引，此外MySQL还支持多列索引及全文检索。例：查询city表中的Ottawa的信息优化前：语句：SELECT*FROMsakila.citywherecity=‘Ottawa’;结果截屏：CPU使用情况：Io使用情况：优化后：语句：altertablesakila.cityaddindex(city);SELECT*FROMsakila.citywherecity=‘Ottawa’;结果截屏：CPU使用情况：Io使用情况：优化前后对比：2.尽量不使用通配符*。尽量不用通配符*来进行查询。由于使用通配符*会取出所有的列，加大了数据库的工作量。所以优化的原则为：需要哪列就取哪列，不要为了方便而加大数据库的压力。例：查询表country有哪些国家优化前：语句：SELECT*FROMsakila.country;结果截屏：CPU使用情况：Io使用情况：优化后：语句：SELECTcountryFROMsakila.country;结果截屏：CPU使用情况：Io使用情况：优化前后执行时间对比，截图如下：3.用连接查询替代子查询。因为子查询会多次遍历表中所有的数据（视你的子查询的层次而定），而连接查询只会遍历一次。例：查找中国的所有城市的信息优化前：语句：Select*fromsakila.citywherecountry_idin(Selectcountry_idfromsakila.countrywherecountry=‘China’);结果截屏：CPU使用情况：Io使用情况：优化后：语句：Select*fromsakila.cityinnerjoin(Selectcountry_idfromsakila.countrywherecountry='China')ascountryoncity.country_id=country.country_id;结果截屏：CPU使用情况：Io使用情况：优化前后对比：四、实验总结实现查询优化是取得良好执行性能并简化管理的关键因素。MySQL查询不是一个无序的查询，不同语句的使用和使用顺序将直接影响其查询速度。凭着对具体数据库特征的了解，对MySQL语句进行语法的重新构造，开发人员能够帮助查询优化器获得更好的执行计划，而这些执行计划是查询优化器无法靠自身独立工作产生的。无论如何在对于MySQL数据库查询优化仍旧是一个可探究的课题。市面上不同的公司采取共享资源的方法提高程序员对数据库本身开发水平的提高。但是，从目前的应用和开发现状来说，如何像软件辅助工具CASE一样更加方便地提升自动化程度，简化MySQL数据库查询优化的过程仍然是一个要点问题。在另一方面，实验结果虽未明显显示实验过程中为系统增加的负荷，但我们作为开发人员需明确，在进行数据库查询优化过程中，应当衡量该优化方案的优缺点，作出正确的选择。五、心得体会通过本次实验，我们感受到CPU运行时间的微妙变化，认识到时间的微妙差距，从而意识到数据库作出微小的调整，便可以加快CPU的运行速度，从更深的层面认识开发语句的魅力。基于MySQL数据库的查询，采用索引和通配符可以节约CPU的运行时间。同时，小组间的合作与交流成为实验高效完成的关键，这也是我们在本次实验中能够总结的经验与收获。