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