MySQL数据库优化（一）

　　对于许多访问量大的网站来说，数据库优化是性能提升、负载提升的至关重要的一个环节。本篇是由Sams Publishing出版的<MySQL Administrator's Guide>中的第六章"MySQL Optimization"翻译过来，诣在做个学习的笔记，以及为有需要的人作个参考。在内容中可能穿插有我的一些注解，但都会明显地标识出来的。由于是一节节地翻译出来，所以就以一篇篇日记的方式发布吧。另，由于水平有限，难免有错漏或意思表达欠妥的地方，还请各位能指出，以能进一步修改。

内容主要有：

* 优化概览

* SELECT语句以及其它查询的优化

* 关于"锁"的问题

* 数据库结构优化

* MySQL 数据库服务器的优化

* 关于磁盘

---------

　　基础设计是让一个系统更快速运行的最重要的因素。你必须知道你的系统将要进行什么工作并且知道它的瓶颈在哪里。

　　绝大多数的瓶颈在以下几个方面：

　　磁盘寻址。寻址需要耗费时间，在近代的磁盘里，每次的寻址耗费平均为10ms，这样，一秒内，磁盘可进行大约100次的寻址。这个时间由于磁盘的更新换代，正正慢慢地减小，然而对于单一数据源来说，这个寻址时间已经很难优化了。将数据源置于多磁盘中，是优化寻址时间的方法之一。

　　磁盘读写。当磁盘指针位于合适的位置时，我们需要读取数据，当前的磁盘都有10~20MB/s的吞吐能力。相较之下，这个指标比磁盘寻址时间更容易优化，因为我们可以同时从多个磁盘并行地读取数据。

　　CPU时钟周期。当我们将数据读入内存中（或数据早已在内存），我们必须处理它以期得到我们想要的结果。此数据量小的表的数量与内存数量的比率是比较常见的限制因素，但对于小表来说，速度通常不是问题所在。

　　内存带宽。当CPU需要更多的数据以填入CPU的缓存里时，主存带宽就成了瓶颈----在多数系统里，这个通常不是瓶颈，但我们应该意识到有这个瓶颈的存在。

 * MySQL设计的限制和权衡

　　当使用MyISAM存储引挚时，MySQL极快地将表锁定以让多个客户端进行读取数据或只一个数据写入的客户端。此类型的数据引挚最大的问题是，当你持续不断地对某一数据表进行数据更新时，对那个表操作（如查询）的其它客户端就显得过于慢了。当你能确定是哪个表进行的操作过于频繁时，我们可以对那个表单独使用其它的存储引挚，如InnoDB等。

　　MySQL可以对混合类型的表进行操作，即一个数据库内包含不同的数据存储引挚，事务型或非事务型的。为了能更好地运行非事务型的表，MySQL进行了以下的约定：

　　- 所有的字段都有默认值
　　- 如果你插入"错误"的值，比如，插入的整型过大，MySQL将不会报错，代之以插入其认为最合适的值。对数值类型来说，这些"最合适的值"通常为0、最小的值、最大的值。对字符串类型一说，是空值或者该字段能存储的最长的字符串。
　　- 所有的计算式都会返回值，即使是错误的计算，也会返回一个"合适"的值而不会报错。如 1/0将返回NULL

　　根据以上的约定，很显然，MySQL的本意是你必须在你的程序中检查插入的值是否正确，而不要等MySQL将你的值检查再转换成合法的值。

* 设计具可移植性的应用程序

　　因为所有的数据库服务器都实现了标准SQL的不同部分，所以要写一个具可移植性的SQL程序也是一个相当耗力的工作。写出具有可移植性的简单查询或插入SQL语句是件相当容易的事，但是，当你的查询越复杂时，要实现复杂查询的同时具有良好的可移植性，就是件难事了。

　　写个具有可移植性的复杂程序前，你需要确定你的程序所使用的数据库以及该种数据库所具有的各种功能。所有的数据库系统都有一个弱点，即是，它们不同的设计让步从而导致不同的行为后果。

　　你可以使用MySQL crash-me程序在一个已选择的数据库中查询函数、类型或者限制。crash-me并不查询所有可能支持的功能，但它支持的功能已相当多，大约在450个左右。比如，当你使用Informix或者DB2时，crash-me可以告诉你，字段的名称的长度不能超过18个字符。

　　crash-me和MySQL benchmarks都是无关数据库系统的。看看它们是怎么写成的，你可以对写具有可移植性的数据库程序有个大概的了解。程序可以在MySQL源码中的sql-bench目录找到。它们是用Perl写成的，使用了DBI的数据库接口。使用DBI可以解决其本身的可移植性问题，因为DBI提供了一些无关数据库类型的访问方法。

　　查找关于crash-me和benchmarks的信息，请访问http://dev.mysql.com/tech-resources/crash-me.php和http://dev.mysql.com/tech-resources/benchmarks/。

　　如果你想设计无关平台的数据库，你需要对每个数据库服务器的瓶颈有所了解。比如，MySQL在MyISAM类型的表中读写都相当的快，但是，如果对同一个表同时进行读取和写入数据操作时，它将会显得慢了。Oracle，当你访问刚刚更新的数据行时，将会有问题。事务型数据库在从日志表生成汇总表通常都不是很好，因为如果这样，行锁基本是没什么大的作用了。

　　为了让你的程序真正做到无关数据库系统平台，你需要定义一个简单的扩展接口以对你的数据进行操作。因为C++在许多系统中都兼容，就用C++的类作为数据库的操作接口吧。

　　如果你用了某些指定数据库才具有的应用(如，MySQL中的REPLACE)，你必须为其它的数据库系统也实现一个兼容的动作。虽然会慢一点，但至少可在其它的数据库系统运行相同的任务。

　　MySQL中，你可以使用/*! */来加入一个MySQL已定义的关键字到查询。但是，/**/就是注释了，这个是大多数数据库系统都支持的注释语法。

　　如果高性能远比精确度更具重要性（比如在Web项目中），那么，你可以建立一个应用层以缓存所有记录集，这样可以提高不少的性能。对结果的过期时间进行设置后，你可以在相当程度上保持缓存的记录为最新的。你可以动态增加缓存并且合理设置过期时间，以有效地提高负荷。

　　因此，一个表的信息应该包含缓存的初始大小以及更新的频率等信息。

　　使用MySQL查询缓存是应用程序缓存的替换方法。启用查询缓存后，数据库服务器会监控详细的查询信息以决定一个查询是否使用缓存。这个会简化你的应用程序。参看章节4.10 "MySQL查询缓存"。

* 我们用MySQL做了什么

　　本节我们讨论MySQL的早期。

　　在MySQL开发的初期，MySQL主要的功能是满足于我们的一个最大的客户。其使用了数据仓库来处理在瑞典的两个最大的分销商的数据。

　　我们对每个店面每周出一收益概要报表，并且提供一些有用的信息以让店主清楚如何进行对他们的客户进行有效的广告运作。

　　数据的容量是非常巨大的（大概每月700万条交易数据），并且有4~10年的数据要向客户提供查询。我们每周从这些数据中为有需要的客户生成报表。

　　为了解决这一问题，我们每月将所有的信息压缩存进交易表中。我们有一系列简单的宏来按不同的需要(产品、客户id、商店等等)从交易表中生成汇总表。报告是Perl脚本动态生成的WEB页面。

　　对于图片数据，我们使用C写了个简单的工具，能处理SQL查询请求并根据查询结果生成GIF图像。这个工具也能通过WEB页的Perl脚本进行动态地调用。

　　多数情况下，一个新的报表能通过复制和更改现有的脚本进行产生。少数情况我们需要为现有的报表添加更多的字段信息，但这也是很简单的事，因为我们已经把所有的数据都存在硬盘上了。

* MySQL Benchmark套件

　　本节包含一些MySQL Benchmark(以及crash-me)的尚未编写的技术描述。当前你可以通过sql-bench目录的源码获取一些好的解决办法。

　　MySQL Benchmark是用来测试MySQL的SQL语句的性能情况的。需要提醒的是，当前benchmark是单线程的，这样它能很好地测试SQL操作的最小时间。我们在往后的时间里将转成多线程的。

　　要使用Benchmark套件，以下必需条件是要满足的：
　　- Benchmark套件已经在源码中提供。你当然也可从 http://dev.mysql.com/downloads/ 那单独下载来自行编译使用。
　　- Benchmark脚本是使用Perl以及Perl的DBI模块来写的，所以DBI是必须安装的。如果需要测试其它类型的数据库，你还需要指定类型的DBD模块。

　　当你取得MySQL源码后，你会在sql-bench目录下找到benchmark，要运行benchmark，只需运行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类型支持多大的数据量