MySQL数据库优化资源-CSDN文库

MySQL

数据库优化

需积分: 1 75 浏览量 2012-12-10 21:16:16 上传评论收藏 383KB DOC 举报

资源推荐

资源详情

资源评论

MySQL 数据库优化（一）

作者叶金荣　出处 专家网　责任编辑李书琴　



　　数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不

多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

　　数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不

多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

　　本章主要讲解了几种优化 MySQL 的方法，并且给出了例子。记着，总有各种办法能让系统运行的更快，当然了，

这需要更多的努力。

　　1 优化概述

　　让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。

　　最常见的系统瓶颈有以下几种：

　　磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于  毫秒，因此理论上每

秒钟可以做  次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加

快搜索时间的方法是将数据分开存放到多个磁盘中。

　　磁盘读写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是 秒。这比

磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。

　　 周期。数据存储在主内存中或者它已经在主内存中了，这就需要处理这些数据以得到想要的结果。存在多

个硐啾饶诖嫒萘坷此蹈窍拗频囊蛩亍  还孕砝此担俣韧  皇俏侍狻

　　内存带宽。当  要将更多的数据存放在  缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是

常见的瓶颈，不过也是要注意的一个因素。

　　1.1 MySQL 设计的局限性

　　当使用  存储引擎时， 会使用一个快速数据表锁以允许同时多个读取和一个写入。这种存储引擎的

最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。如果这种情况在某个表中存在，可以使用另一

种表类型。详情请看 !"#" $"%&' ( $。

　　 可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表发生错误时不能回滚，有以下几条规

则：

 　　所有的字段都有默认值

 　　如果字段中插入了一个错误的值，比如在数字类型字段中插入过大数值，那么  会将该字段值置为最可能的

值而不是给出一个错误。数字类型的值是 ，最小或者最大的可能值。字符串类型，不是空字符串就是字段所能存储的最大长度。

 　　所有的计算表达式都会返回一个值而报告条件错误，例如返回)。

　　这些规则隐含的意思是，不能使用  来检查字段内容。相反地，必须在存储到数据库前在应用程序中来检查。

详情请看*+*,-./ '$.#0"$#"$和1**1)!2"3。

1.2 应用设计的可移植性

　　由于各种不同的数据库实现了各自的  标准，这就需要我们尽量使用可移植的  应用。查询和插入操作很容

易就能做到可移植，不过由于更多的约束条件的要求就越发困难。想要让一个应用在各种数据库系统上快速运行，就

变得更困难了。

　　为了能让一个复杂的应用做到可移植，就要先看这个应用运行于哪种数据库系统之上，然后看这些数据库系统都

支持哪些特性。

　　每个数据库系统都有某些不足。也就是说，由于设计上的一些妥协，导致了性能上的差异。

　　可以用  的4$05 程序来看选定的数据库服务器上可以使用的函数，类型，限制等。4$05 不会检查

各种可能存在的特性，不过这仍然是合乎情理的理解，大约做了 1 次测试。

　　一个4$05 的信息类型的例子就是，它会告诉您如果想使用 "65#3或/ 的话，就不能使字段名长度超过

+ 个字符。

　　4$05 程序和  基准使每个准数据库都实现了的。可以通过阅读这些基准程序是怎么写的，自己就大概

有怎样做才能让程序独立于各种数据库这方面的想法了。这些程序可以在  源代码的7$8'& "409目录下找到。

他们大部分都是用  ' 写的，并且使用 / 接口。由于它提供了独立于数据库的各种访问方式，因此用 / 来解决各

种移植性的问题。

　　想要看到4$05 的结果，可以访问：0(% :*5$8'*45 40 $;4 $4$05 *(0(*访问

0(% :*5$8'*45 40 $;4 $& "405<$可以看到基准的结果。

　　如果您想努力做到独立于数据库，这就需要对各种  服务器的瓶颈都有一些很好的想法。例如， 对于

类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。作为

=4' 来说，它在访问刚刚被更新的记录时有很大的问题直到结果被刷新到磁盘中。事务数据库一般地在从日志表中

生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。

　　为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于 >>在很多系

统上都可以使用，因此使用 >>作为数据库的基类结果很合适。

　　如果使用了某些数据库独有的特定功能比如2!!语句就只在  中独有，这就需要通过编写替代方法

来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。

　　在  中，可以在查询语句中使用?@?语法来增加  特有的关键字。然而在很多其他数据库中，??却

被当成了注释并且被忽略。

　　如果有时候更高的性能比数据结果的精确更重要，就像在一些 A & 应用中那样，这可以使用一个应用层来缓存结

果，这可能会有更高的性能。通过让旧数据在一定时间后过期，来合理的更新缓存。这是处理负载高峰期时的一种方

法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常。

　　这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。

　　一个实现应用层缓存的可选方案是使用  的查询缓存8; 440 。启用查询缓存后，数据库就会根据一些

详情来决定哪些结果可以被重用。它大大简化了应用程序，详情请看*0 ; 40 。

1.3 我们都用 MySQL 来做什么

　　本章描述了一个  的早期应用。

　　在  最开始的开发过程中， 本来是要准备给大客户用的，他们是瑞典的  个最大的零售商，他们用

于货物存储数据管理。

　　我们每周从所有的商店中得到交易利润累计结果，以此给商店的老板提供有用的信息，帮助他们分析如果更好的

打广告以影响他们的客户。

　　数据量相当的大每个月的交易累计结果大概有 B 百万，而且还需要显示 1 年间的数据。我们每周都得到客户

的需求，他们要求能‘瞬间’地得到数据的最新报表。

　　我们把每个月的全部信息存储在一个压缩的‘交易’表中以解决这个问题。我们有一些简单的宏指令集，它们能根据

不同的标准从存储的‘交易’表中根据字段分组产品组、客户 #%、商店等等取得结果。我们用一个小  ' 脚本动态的生

成 A & 页面形式的报表。这个脚本解析 A & 页面，执行  语句，并且插入结果。我们还可以用 - 或者

5%C( ' 来做这个工作，不过当时还没有这  个工具。

　　为了得到图形数据，我们还写了一个简单的  语言工具，用于执行  查询并且将结果做成 DE 图片。这个工具

同样是  ' 脚本解析 A & 页面后动态执行的。

　　很多情况下，只要拷贝现有的脚本简单的修改里面的  查询语句就能产生新的报表了。有时候，就需要在现存

的累计表中增加更多的字段或者新建一个。这个操作十分简单，因为我们在磁盘上存储有所有的交易表总共大概有

D 的交易表以及 D 的其他客户资料。

　　我们还允许客户通过 =/ 直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了。

　　这个系统工作的很好，并且在适度的 ;"'2 工作站3-F上处理数据没有任何问题。最终这个系

统移植到了 #";3 上。

　　1.4 MySQL 基准套件

　　本章本来要包括  基准套件以及4$05 的技术描述的，但是至今还未写。现在，您可以通过查看

 发布源代码7$8'& "409目录下的代码以及结果有一个更好的想法。

　　基准套件就是想告诉用户执行什么样的  查询表现的更好或者更差。

　　请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。

　　想要使用基准套件，必备以下几个条件：

　　基准套件在  的发布源代码中就有。可以去0(% :*5$8'*45%."'%$下载发布版或者使用现有开发

代码树详情请看*G*G"$''#"650 / : '(5 ";4  。

　　基准脚本是用  ' 写的，它用  ' 的 / 模块来连接数据库，因此必须安装 / 模块。并且还需要每个要做测试

的服务器上都有特定的 / 驱动程序。例如，为了测试 、$  和 /，就必须安装//5$8'

//及///模块。详情请看*B '"$''#") 。

取得 MySQL 的分发源代码后，就能在`sql-bench' 目录下看到基准套件。想要运行这些基

准测试，请先搭建好服务，然后进入`sql-bench' 目录，执行run-all-tests 脚本：





　　server_name 可以是任何一个可用的服务。想要列出所有的可用选项和支持的服务，只

要调用以下命令：



　　4$05 脚本也是放在7$8'& "409目录下。4$05 通过执行真正的查询以试图判断数据库都支持什么特性、

性能表现以及限制。例如，它可以判断：

 　　都支持什么字段类型

 　　支持多少索引

 　　支持什么样的函数

 　　能支持多大的查询

 　　H2-2字段类型能支持多大

　　可以从0(% :*5$8'*45 40 $;4 $4$05 *(0(上找到各种不同数据库4$05 的结果。更多的信息

请访问0(% :*5$8'*45 40 $;4 $& "405<$。

　　1.5 使用您自己的基准

　　请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈或者使用一个假的模块来

代替之后，就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个

瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。

　　从  的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看B**10  "405<

;# 。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的

办法来解决问题，知道哪个程序才是最快的。

　　另一个基准套件是开放源码的数据库基准，可以在0($%&*$;4 6 *" 上找到。

　　当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个测试过的生产系统也

遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计例如，在高负载下扫描数据表的表现不好、操作

系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。

　　为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试@可以使用 ;( 54<，在

0(I  5*F.%"*455$8'$;( $54<可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系

统死掉，因此确认只在开发系统上做测试。

2 优化 SELECT 语句及其他查询

　　首先，影响所有语句的一个因素是：您的权限设置越复杂，那么开销就越大。

　　使用比较简单的D2)语句能让  减少在客户端执行语句时权限检查的开销。例如，如果没有设定任何表

级或者字段级的权限，那么服务器就无需检查&' $C(#:和4';5"$C(#:表的记录了。同样地，如果没有对帐户设定

任何资源限制的话，那么服务器也就无需做资源使用统计了。如果有大量查询的话，花点时间来规划简单的授权机制

以减少服务器权限检查的开销是值得的。

　　如果问题处在一些 MySQL 特定的表达式或者函数上，则可以通过mysql 客户端程序

使用BENCHMARK() 函数做一个定时测试。它的语法是：

BENCHMARK(loop_count,expression)。例如：

 !"#$!%&'()*+,+-+./

--

0#$!%&'()*+,+-+.0

--

01111111111111111111110

--

+234*5.

　　上述结果是在  "#;51-F 的系统上执行得到的。它告诉我们： 在这个系统上可以在 *G 秒内执行

次简单的加法运算。

　　所有的  函数都应该被最优化，不过仍然有些函数例外。!)-2J是一个用于检查查询语句中是否

存在问题的非常好的工具。

MySQL 数据库优化（二）

作者叶金荣　出处 专家网　责任编辑李书琴　

++66

　　!K)语句可以被当作/!2!的同义词来用，也可以用来获取一个  要执行的!!语句的相

关信息。!K)&'C"5 语法和/!2!&'C"5 或-=A=)E2=&'C"5 一样。

　　 数据库优化（一）　

　　1. EXPLAIN 语法(得到 SELECT 的相关信息)　

!K)L&'C"5

　　或者:　　

剩余51页未读，继续阅读

评论收藏

内容反馈

IT系统集成专家

粉丝: 16
资源: 310

MySQL数据库优化

Mysql数据库优化

MySQL数据库的优化

mysql数据库优化

mysql数据库优化的学习心得

MySQL数据库优化SQL篇PPT课件.pptx

mysql数据库优化方案（值得学习）

mysql数据库优化经验

MYSQL数据库优化

MySQL数据库优化实践

MySQL数据库优化方案及应用

MYSQL数据库优化秘籍

MySQL数据库优化SQL篇.ppt

Mysql数据库的优化

优化MySql数据库

MySQL数据库优化SQL篇PPT学习教案.pptx

Mysql数据库优化总结

MySQL数据库优化攻略

一种深入解决MySQL数据库优化方案.pdf

最新资源