数据库优化是一项很复杂的工作因为这最终需要对系统优化的很好理解才行尽管对系统或应用系统的了解不多的情况下优化效果还不错但是如果想优化的效果更好那么就需要对它了解更多才行
本章主要讲解了几种优化MySQL的方法并且给出了例子记着总有各种办法能让系统运行的更快当然了这需要更多的努力
优化概述
让系统运行得快得最重要因素是数据库基本的设计并且还必须清楚您的系统要用来做什么以及存在的瓶颈
最常见的系统瓶颈有以下几种
磁盘搜索它慢慢地在磁盘中搜索数据块对现代磁盘来说平时的搜索时间基本上小于毫秒因此理论上每秒钟可以做次磁盘搜索这个时间对于全新的新磁盘来说提高的不多并且对于只有一个表的情况也是如此加快搜索时间的方法是将数据分开存放到多个磁盘中
磁盘读/写当磁盘在正确的位置上时就需要读取数据对现代磁盘来说磁盘吞吐量至少是MB/秒这比磁盘搜索的优化更容易因为可以从多个媒介中并行地读取数据
CPU周期数据存储在主内存中(或者它已经在主内存中了)这就需要处理这些数据以得到想要的结果存在多个?硐啾饶诖嫒萘坷此蹈窍拗频囊蛩亍还孕砝此担俣韧ǔ皇俏侍狻?
内存带宽当CPU要将更多的数据存放在CPU缓存中时主内存的带宽就是瓶颈了在大多数系统中这不是常见的瓶颈不过也是要注意的一个因素
MySQL 设计的局限性
当使用MyISAM存储引擎时MySQL会使用一个快速数据表锁以允许同时多个读取和一个写入这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询如果这种情况在某个表中存在可以使用另一种表类型详情请看 MySQL Storage Engines and Table Types
MySQL可以同时在事务及非事务表下工作为了能够平滑的使用非事务表(发生错误时不能回滚)有以下几条规则
所有的字段都有默认值
如果字段中插入了一个错误的值比如在数字类型字段中插入过大数值那么MySQL会将该字段值置为最可能的值而不是给出一个错误数字类型的值是最小或者最大的可能值字符串类型不是空字符串就是字段所能存储的最大长度
所有的计算表达式都会返回一个值而报告条件错误例如 / 返回 NULL
这些规则隐含的意思是不能使用MySQL来检查字段内容相反地必须在存储到数据库前在应用程序中来检查详情请看 How MySQL Deals with Constraints 和 INSERT Syntax
应用设计的可移植性
由于各种不同的数据库实现了各自的SQL标准这就需要我们尽量使用可移植的SQL应用查询和插入操作很容易就能做到可移植不过由于更多的约束条件的要求就越发困难想要让一个应用在各种数据库系统上快速运行就变得更困难了
为了能让一个复杂的应用做到可移植就要先看这个应用运行于哪种数据库系统之上然后看这些数据库系统都支持哪些特性
每个数据库系统都有某些不足也就是说由于设计上的一些妥协导致了性能上的差异
可以用MySQL的 crashme 程序来看选定的数据库服务器上可以使用的函数类型限制等crashme 不会检查各种可能存在的特性不过这仍然是合乎情理的理解大约做了次测试
一个 crashme 的信息类型的例子就是它会告诉您如果想使用Informix 或 DB的话就不能使字段名长度超过个字符
crashme 程序和MySQL基准使每个准数据库都实现了的可以通过阅读这些基准程序是怎么写的自己就大概有怎样做才能让程序独立于各种数据库这方面的想法了这些程序可以在MySQL源代码的 `sqlbench 目录下找到他们大部分都是用Perl写的并且使用DBI接口由于它提供了独立于数据库的各种访问方式因此用DBI来解决各种移植性的问题
想要看到 crashme 的结果可以访问访问 可以看到基准的结果
如果您想努力做到独立于数据库这就需要对各种SQL服务器的瓶颈都有一些很好的想法例如MySQL对于 MyISAM 类型的表在检索以及更新记录时非常快但是在有并发的慢速读取及写入记录时却有一定的问题作为Oracle来说它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好因为在这种情况下行记录锁几乎没用
为了能让应用程序真正的做到独立于数据库就必须把操作数据的接口定义的简单且可扩展由于C++在很多系统上都可以使用因此使用C++作为数据库的基类结果很合适
如果使用了某些数据库独有的特定功能(比如 REPLACE 语句就只在MySQL中独有)这就需要通过编写替代方法来在其他数据库中实现这个功能尽管这些替代方法可能会比较慢但是它能让其他数据库实现同样的功能
在MySQL中可以在查询语句中使用 /*! */ 语法来增加MySQL特有的关键字然而在很多其他数据库中/**/ 却被当成了注释(并且被忽略)
如果有时候更高的性能比数据结果的精确更重要就像在一些Web应用中那样这可以使用一个应用层来缓存结果这可能会有更高的性能通过让旧数据在一定时间后过期来合理的更新缓存这是处理负载高峰期时的一种方法这种情况下可以通过加大缓存容量和过期时间直到负载趋于正常
这种情况下建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率
一个实现应用层缓存的可选方案是使用MySQL的查询缓存(query cache)启用查询缓存后数据库就会根据一些详情来决定哪些结果可以被重用它大大简化了应用程序详情请看 The MySQL Query Cache
我们都用MySQL来做什么
本章描述了一个MySQL的早期应用
在MySQL最开始的开发过程中MySQL本来是要准备给大客户用的他们是瑞典的个最大的零售商他们用于货物存储数据管理
我们每周从所有的商店中得到交易利润累计结果以此给商店的老板提供有用的信息帮助他们分析如果更好的打广告以影响他们的客户
数据量相当的大(每个月的交易累计结果大概有百万)而且还需要显示年间的数据我们每周都得到客户的需求他们要求能瞬间地得到数据的最新报表
我们把每个月的全部信息存储在一个压缩的交易表中以解决这个问题我们有一些简单的宏指令集它们能根据不同的标准从存储的交易表中根据字段分组(产品组客户id商店等等)取得结果我们用一个小Perl脚本动态的生成Web页面形式的报表这个脚本解析Web页面执行SQL语句并且插入结果我们还可以用PHP或者mod_perl来做这个工作不过当时还没有这个工具
为了得到图形数据我们还写了一个简单的C语言工具用于执行SQL查询并且将结果做成GIF图片这个工具同样是Perl脚本解析Web页面后动态执行的
很多情况下只要拷贝现有的脚本简单的修改里面的SQL查询语句就能产生新的报表了有时候就需要在现存的累计表中增加更多的字段或者新建一个这个操作十分简单因为我们在磁盘上存储有所有的交易表(总共大概有G的交易表以及G的其他客户资料)
我们还允许客户通过ODBC直接访问累计表这样的话那些高级用户就可以自己利用这些数据做试验了
这个系统工作的很好并且在适度的Sun Ultra SPARC工作站(xMHz)上处理数据没有任何问题最终这个系统移植到了Linux上
MySQL 基准套件
本章本来要包括MySQL基准套件(以及 crashme)的技术描述的但是至今还未写现在您可以通过查看MySQL发布源代码 `sqlbench 目录下的代码以及结果有一个更好的想法
基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差
请注意这个基准是单线程的因此它度量了操作执行的最少时间我们未来打算增加多线程测试的基准套件
想要使用基准套件必备以下几个条件
基准套件在MySQL的发布源代码中就有可以去 下载发布版或者使用现有开发代码树(详情请看 Installing from the Development Source Tree)
基准脚本是用Perl写的它用Perl的DBI模块来连接数据库因此必须安装DBI模块并且还需要每个要做测试的服务器上都有特定的BDB驱动程序例如为了测试MySQLPostgreSQL和DB就必须安装 DBD::mysql DBD::Pg 及 DBD::DB 模块详情请看 Perl Installation Note
取得MySQL的分发源代码后就能在 `sqlbench 目录下看到基准套件想要运行这些基准测试请先搭建好服务然后进入 `sqlbench 目录执行 runalltests 脚本
shell> cd sqlbench
shell> perl runalltests server=server_name
server_name 可以是任何一个可用的服务想要列出所有的可用选项和支持的服务只要调用以下命令
shell> perl runalltests help
crashme 脚本也是放在 `sqlbench 目录下crashme 通过执行真正的查询以试图判断数据库都支持什么特性性能表现以及限制例如它可以判断
都支持什么字段类型
支持多少索引
支持什么样的函数
能支持多大的查询
VARCHAR 字段类型能支持多大
可以从 上找到各种不同数据库 crashme 的结果更多的信息请访问
使用您自己的基准
请确定对您的数据库或者应用程序做基准测试以发现它们的瓶颈所在解决这个瓶颈(或者使用一个假的模块来代替)之后就能很容易地找到下一个瓶颈了即使应用程序当前总体的表现可以接受不过还是至少要做好找到每个瓶颈的计划说不定某天您就希望应用程序能有更好的性能
从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了详情请看 The MySQL Benchmark Suite您可以从基准套件中的任何一个程序做适当的修改以适合您的需要通过整个方式您就可以有各种不同的办法来解决问题知道哪个程序才是最快的
另一个基准套件是开放源码的数据库基准可以在 上找到
当系统负载十分繁重的时候通常就会发生问题我们就有很多客户联系我们说他们有一个(测试过的)生产系统也遭遇了负载问题在很多情况下性能问题归结于数据库的基本设计(例如在高负载下扫描数据表的表现不好)操作系统或者程序库等因素很多时候这些问题在还没有正式用于生产前相对更容易解决
为了避免发生这样的问题最好让您的应用程序在可能的最差的负载下做基准测试!可以使用Super Smack在 可以找到从它名字的意思就能想到只要您愿意它就能让您的系统死掉因此确认只在开发系统上做测试
优化 SELECT 语句及其他查询
首先影响所有语句的一个因素是您的权限设置越复杂那么开销就越大
使用比较简单的 GRANT 语句能让MySQL减少在客户端执行语句时权限检查的开销例如如果没有设定任何表级或者字段级的权限那么服务器就无需检查 tables_priv 和 columns_priv 表的记录了同样地如果没有对帐户设定任何资源限制的话那么服务器也就无需做资源使用统计了如果有大量查询的话花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的
如果问题处在一些MySQL特定的表达式或者函数上则可以通过 mysql 客户端程序使用 BENCHMARK() 函数做一个定时测试它的语法是BENCHMARK(loop_countexpression)例如
mysql> SELECT BENCHMARK(+);
++
| BENCHMARK(+) |
++
| |
++
row in set ( sec)
上述结果是在Pentium II MHz的系统上执行得到的它告诉我们MySQL在这个系统上可以在秒内执行 次简单的加法运算
所有的MySQL函数都应该被最优化不过仍然有些函数例外BENCHMARK() 是一个用于检查查询语句中是否存在问题的非常好的工具
