数据库性能优化方案

高级数据库技术——期末论文

基于SQL查询的MySQL数据库性能优化研究

姓名：XX

学号:2014XXXXX

学院：计算机学院

摘要：

查询是数据库系统中最基本也是最常用的一种操作，是否具有较快的执行速度，已成为数据库用户和设计者极其关心的问题。在研究开源数据库管理系统

MySQL 查询优化技术的基础上，主要结合传统SQL操作优化、深度分析 MySQL源代码、现代数据库发展几方面进行诸如参数调优，MySQL关联查询，重写相关规则等内容展开优化分析研究。

关键词： 查询优化，查询重用，查询重写，计划优化

一、传统SQL查询优化操作

1.选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT

NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2.使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

实验项目名称：数据库监视与性能优化实验学时： 4

同组学生姓名：实验地点：

实验日期：实验成绩：

批改教师：批改时间：

一、实验目的和要求

1、利用索引优化查询性能、优化SQL语句。

2、了解通过对SQL profiler跟踪系统运行数据。

二、实验仪器和设备

设备：奔腾Ⅳ或奔腾Ⅳ以上计算机；

环境：WINDOWS 7 或WINDOWS XP、Microsoft SQL Server 2008。

三、实验过程

1、完成以下的实验。

1）使用对象资源管理器创建、管理索引

①为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80 % 。

②重命名索引，将索引“emp_id”重命名为“员工表_员工号”。

③删除索引“员工表_员工号”。

2）使用T-SQL语句创建、管理索引

①为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80 % 。

②重命名索引，将索引“emp_id”重命名为“员工表_员工号”。

③为员工参与项目表创建一个索引名为“员工_项目_index”的非聚集复合索引，索引关键字为“员工号”，升序，项目编号，降序，填充因子50%。

④删除索引“员工表_员工号”和“员工_项目_index”。

3）索引前后的执行计划

①删除员工表中员工号上的主键。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表和员工参与项目表中的员工号都没有索引）

②为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序；按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号没索引，员工参与项目表中的员工号有非聚集索引）

③重建员工表中员工号上的主键，删除“员工参与项目_员工号”的非聚集索引。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号没有非聚集索引）

第９卷第４期　２０１０年４月　软件导刊　Ｓｏｆｔｗａｒｅ　Ｇｕｉｄｅ　Ｖｏ１．９　Ｎｏ．４　Ａｐｒ．２０１０　

Ｏｒａｃｌｅ数据库性能优化方法　

李晓丽　（南通纺织职业技术学院信息系，江苏南通２２６００７）　摘　要：作为一种大型数据库，Ｏｒａｃｌｅ能广泛应用于金融、邮电、电力、民航等国民经济重要部门。其设计的结构提供　了完整、简单的操作及可靠度相当高的数据备份和恢复机制（ｂａｃｋｕｐ　ａｎｄ　ｒｅｃｏｖｅｒｙ）。对于数据库管理员来讲，如何提　高数据库性能，显得尤为重要。　关键词：数据备份及恢复；联机重建；内置合并　中图分类号：ＴＰ３１１．１３　文献标识码：Ａ　文章编号：１６７２—７８００（２０１０）０４—０１６２—０２　

１建立索引　表格是关系型数据库的基础，数据库中的所有信息都是以　表格的形式来存放的。由于Ｏｒａｃｌｅ数据库是一个大型的数据　库，存储着大量的数据信息。它使用一个内置的优化器来决定　检索数据的最快方法。在Ｏｒａｃｌｅ的性能优化中。适当的建立索　引是很关键的。　（１）低选中率的列建立位映射索引：为那些唯一度很低的　列创建位映射索引，位映射索引能够极大地帮助改善读性能。　位映射索引所创建的是包含每个特定关键字０、１值的二进制　位映射流。由于这里需要的是位级二进制算术运算，因此大量　的行可以被高速地处理。此外，位映射索引所占用的空间仅仅　是常规Ｂ　树索引空间开销的一小部分。同时，只有在事实／维　集合中创建了位映射索引的情况下，才能使用新的ＳＴＡＲ＿ＴＲＡ　ＮＳＦＯＲＭＡＴＩＯＮ访问路径。　（２）为不平衡的Ｂ　树索弓ｌ创建反转关键字索引：反向键　索引是一种Ｂ％树索引。在这种索引方式中，关键字值的字节　是按照相反的顺序存储的。通过将关键字反向，可以使得索引　在Ｂ　树上更为均匀的分布，这对于ＯＰＳ（Ｏｒａｃｌｅ并行服务器）　环境非常有用。在这种情况下，对相同叶节点块的竞争将会导　致实例间“ｐｉｎｇ”动作的发生。针对叶节点块的竞争使用或叶节　点块的不均匀密度问题，主要应该使用反向键索引。　要建立合适的索引，还必须考察ｓｅｌｅｃｔ和ｗｈｅｒｅ子句，因为　这是查询优化的首要焦点。当索引包括多列时，就构成了复合　索引。此时，进行查询时会对索引的顺序十分敏感。例如，在　ｚｇｄａ表上创建一个复合索引（ｄｍ，ｘｍ）。查询语句为：　ｓｅｌｅｃｔ＊ｆｒｏｍ　ｚ　ａ　ｗｈｅｒｅ　ｄｍ＝　０００１　ａｎｄ　ｘｍ＝＇ｉｏｈｎ　而下面的查询不会采用此索引　ｓｅｌｅｃｔ　ｆｒｏｍ　ｚｇｄａ　ｗｈｅｒｅ　ｘｍ＝＇ｊｏｈｎ　ａｎｄ　ｄｉｎ＝＂０００１　这在Ｏｒａｃｌｅ中称为“前部说明方式”，即只有当ｗｈｅｒｅ条件　中的列在复合索引列的前部时，索引才会被查询优化采用。使　用索引可以提高检索数据的效率，但并不是说只要有索引，检　索的效率就会提高，添加索引是以增大存储量和降低插入性能　为代价的　这里存在着“百分之二十”的规则，当从表格中选取　的行超过总行数的百分之二十时．表上的索引不会提高检索的　速度，因此在创建索引时要考虑到这一点。不合适的索引将会　导致查询性能的严重降低，应在ＳＱＬ语句中将这索引隐藏。　２优化ＳＱＬ语句　ＳＱＬ是一种非过程化语言，它一次处理的是一个记录集　合，对数据提供自动导航。ＳＱＬ允许用户在高层的数据结构上　工作．而不是对单个记录进行操作。ＳＱＬ不要求用户指定对数　据的存取方法，而是使用查询优化器，由系统决定对指定数据　存取的最快速手段。当关系数据库的设计者在关系表上定义了　索引之后，系统会自动地利用索引进行快速检索，用户不需知　道表上是否有索引以及表有什么类型的索引等细节。　在ＳＱＬ语句中，应注意以下几点：①在ｗｈｅｒｅ子句中，若使　用ａｎｄ条件．应将最有可能导致查询失败的条件放前面；若使　用ｏｒ条件．应将最有可能致查询失败的条件放在后面；②进行　多表查询时，应将返回行少的表放在ｆｒｏｍ子句的后面，在　ｗｈｅｒｅ子句中，应将记录多的表的字段写在左边。值得注意的　是，对那些写得不好的小ＳＱＬ语句进行优化更为重要，因为这　些小的ＳＱＬ语句每天可能被访问成千上万次。　３碎片问题　碎片是由于同一磁盘的各个部分分散在磁盘的不同区域　产生的。在删除磁盘上的文件和添加新文件时会产生碎片。碎　作者简介：李晓丽（１９７７一），女，江苏南通人，南通纺织职业技术学院信息系讲师，研究方向为软件技术。

1、 硬件调整性能

最有可能影响性能的是磁盘和网络吞吐量,解决办法

扩大虚拟内存，并保证有足够可以扩充的空间；把数据库服务器上的不必要服务关闭掉

把数据库服务器和主域服务器分开

把SQL数据库服务器的吞吐量调为最大

在具有一个以上处理器的机器上运行SQL

2、 调整数据库

若对该表的查询频率比较高，则建立索引；建立索引时，想尽对该表的所有查询搜索操作， 按照where选择条件建立索引，尽量为整型键建立为有且只有一个簇集索引，数据在物理上按顺序在数据页上，缩短查找范围，为在查询经常使用的全部列建立非簇集索引，能最大地覆盖查询；但是索引不可太多，执行UPDATE DELETE INSERT语句需要用于维护这些索引的开销量急剧增加；避免在索引中有太多的索引键；避免使用大型数据类型的列为索引；保证每个索引键值有少数行。

3、 使用存储过程

应用程序的实现过程中，能够采用存储过程实现的对数据库的操作尽量通过存储过程来实现，因为存储过程是存放在数据库服务器上的一次性被设计、编码、测试，并被再次使用，需要执行该任务的应用可以简单地执行存储过程，并且只返回结果集或者数值，这样不仅可以使程序模块化，同时提高响应速度，减少网络流量，并且通过输入参数接受输入，使得在应用中完成逻辑的一致性实现。

4、 应用程序结构和算法

建立查询条件索引仅仅是提高速度的前提条件，响应速度的提高还依赖于对索引的使用。因为人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，特别是对数据量不是特别大的数据库操作时，是否建立索引和使用索引的好坏对程序的响应速度并不大，因此程序员在书写程序时就忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在数据量特别大时或者大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！