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

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