如何编写高效的MySQL应用

时间：2008-05-12 来源：sdccf

来源：网海拾贝

借助诸如Apach、Perl、PHP和Python等工具，构建一个MySQL应用时很容易的。然而确保它们运行快速，则需要一点洞察力。本文就是你需要知道的东西。

MySQL对于成为一个非常快速的数据库服务器有着当之无愧的名声，它也非常容易设置和使用。随着它作为网站后端数据库得声望日增，其效果在去年开始有明显提高。但是很多MySQL用户更多地知道如何创建一个数据库并编写对它的查询。就像成千上万的人通过载闲暇时用Linux做实验来学习Unix那样，很多人通过玩MySQL学习关系数据库。这些MySQL新手的大多数既没有关系数据库理论的背景，又没有时间阅读MySQL手册全文。

因此，我们决定研究某些方法，你可以用针对优化性能来调节MySQL。在读完本文后，你将理解一些帮助你设计你的MySQL数据库和查询的技术，值得你的应用很有效率。我们将假定你熟悉MySQL和SQL基础，但不假定你有这两方面的广博知识。

只存储你需要的信息

这听上去是常识，但人们常常采取“厨房下水道”的方式进行数据库设计。他们认为可能项要得每样东西都要存储并设计数据库保存所有者这些数据。你需要对你的需求现实些，并确定取确实需要什么信息。你常常能随意产生一些数据而不把它存在数据库表中。在这种情况下，从一个应用开发者的角度看也有道理这样做。

例如，在线目录的产品表可能包含各种产品的名称、介绍、尺寸、重量和价格。除了价格，你可能想存储每个项目相关的税和运输成本。但实际上不必这样做。首先税和运输成本可以方便地（由你的应用或MySQL）计算出来。其次，如果税和运输成本改变了，你可能必须编写必要的查询更新每个产品记录中的税和运输的费率。

有时人们认为这太难不能在以后往数据库表中加入字段，所以他们感觉不得不定义尽可能多的列。这是明显的概念错误。在MySQL中，你可以用ALTER TABLE命令方便地修改表定义以适应你改变的需求。

例如，如果你突然认识到你需要给你的产品表增加一个级别列（可能你想允许用户在你的目录中给产品评级），你可以这样做：

ALTER TABLE products ADD rank INTEGER

这给你的产品表增加了一个整数类型的级别列，你能用ALTER TABLE做什么的完整介绍参见MySQL手册。

只要求你需要的东西--要清晰

就像说“只存储你需要的东西”那样，这可能看来是常识，但这一点常常被忽视，为什么呢？因为在一个应用开发时，需求经常改变，所以很多查询最终看来是这样：

SELECT * FROM sometable

当你不能肯定你将需要哪一列时，要求所有列明显是最省力的事情，然而随着你的表不断增大和修改，这可能变成一个性能问题。最好是在你的最初开发完成后再花些时间并确定你真正从你的查询中需要什么：

SELECT name, rank, description FROM products
　

这带来了一个相关的观点，即代码维护比性能更重要。大多数变成语言（Perl、Python、PHP、Java等）允许通过字段名和数字编号访问一条查询的结果，这意味着你可以访问命名字段或字段０都可以得到相同的数据。

长期看，最好使用列名而不是其编号位置，为什么？因为一个表中或一条查询中地列的相对位置可以改变。它们在表中可能因为重复使用ALTER TABLE而改变，它们在查询中将因重写了查询而忘记更新应用逻辑来匹配而改变。

当然，你仍然需要小心改变列名！但如果你使用列名而非标号位置，如列名改变，你可以用grep搜索源代码或使用编辑器的搜索能力查找你需要修改的代码。

规范化你的表结构

如果你以前从未听说过“数据规范化”，不要害怕。规范化可能是一个复杂的专题，你可以从只理解最基本的规范化概念中正真正获益。

理解它的最容易的方法是认为你的表是一个电子报表。如果你想以一个报表跟踪你的CD收藏，你可以如图１种那样进行设计：

图１

album track1 track2 track10
----- ------ ------ -------
Billboard Top Hits - 1984 Loverboy Shout St. Elmos Fire
(Billy Ocean) (Tears for Fears) (John Parr)

这看上去很合理。大多数CD只有10首曲子，对否？不尽然。如果你拥有一张有100首曲子的CD且几张超过20首改怎么办。这意味着用这种方法，在极端的情况下，你将需要一个非常宽的表格（或一个超过100个字段的表）来保存所有的数据。

规范化表结构的目标是使“空单元”的数量最少，在上述CD表的情况下，如果你允许CD可能包含100首曲子，你会有很多这样的空单元。不管你何时处理可能扩展到类似该CD表那样数量的字段列表，它是你需要将你的数据分割成2个或更多表的标志，然后你一起访问并获得你需要的数据。

很多关系数据库的新手不真正知道关系数据库管理系统中关系是什么。简单地说，就像一组信息存在可以基于共性数据联结（JOIN）在一起的不同表中，很不幸，这听上去更学术化和含糊，但CD数据库提出了一个具体情况，我们可以研究如何规范数据。

每个CD列表有一个固定的属性（标题、艺术家、年份、分类）集和一个不定的属性（曲目表）集的理解给了我们一些如何分成成能相互关联的表的思路。

你可以创建一个所有专辑及其固定属性的表，另一个包含这些专辑的所有曲目的表。这样不是水平思考（像表格），你垂直思考--就好像你创建列表而不是行--并建立一个如图2的表结构：

专辑的编号（MySQL镜自动为你生成，因为我们在列上使用了AUTO_INCREMENT属性）关联不同曲目到一给定专辑，tracks表中的album_id字段匹配专辑表中的一个id。这样要获得给定专辑的所有曲目，你应该用如下查询：

　SELECT tracks.num, tracks.name
FROM albums, tracks
WHERE albums.title = Billboard Top Hits - 1984
AND albums.id = tracks.album_id
　

该结构即灵活又有效。灵活性来自你可以在以后将数据加入系统而不必重新你已完整的工作的事实。例如，如果你想增加每一张专辑的艺术家信息，你可以床架一个artists表，关联到albums表，就像tracks那样。你无需修改现有的结构--只是增加它。

有效性来自于在你的数据中没有明显的数据重复且没有大量的空洞（空单元）的实施。这样MySQL在你的数据库表中既不存储多余的数据，也不比花额外的精力搜索大量空区域。

如果你对关系数据库是新手，规范化你的数据看起来有点奇怪，但在存储和检索数据时，它使MySQL非常有效，并给予你扩展和伸缩你的应用却不必多次重构你的数据库的灵活性。尽可能早的花时间想清楚数据库设计，并考虑你的需求怎样随时间增长，前期花的时间永远是值得的。

复合索引

复合索引（有时称组合索引）是急于多个列的单一索引。MySQL在处理一条查询时每个表只使用一个索引，这意味着如果你有多个经常出现在WHERE子句中的列，你可能要通过创建一个复合索引来加快这些查询。

考虑下列表结构片断：

　CREATE TABLE people (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
favorite_color VARCHAR(10) NOT NULL,
.
.
.
);

　

如果你常常基于last_name和first_name查询表，你可以从last_name和first_name的复合索引中获益：

INDEX last_first (last_name, first_name)

由于MySQL构建复合索引的方式，它可以使用last_first索引来回答基于last_name本身或last_name与first_name两者的索引。这是因为如果列涉及复合索引的“最左前缀”的形式，MySQL将只使用一个复合索引。

所以如果一个复合索引有多个列合成：

INDEX big_index (a, b, c, d, e, f, g, h, i)

MySQL可以用它来回答基于a、或a和b、或a和b和c、或a和b和c和d的查询。但它不能使用big_index处理基于e、或c和f、或g和i的查询，因为这些序列没有一个是从索引的最左边开始的。

复合索引尝被用于加快某些复杂查询，但你需要理解起局限，而且你永远应该进行一些测试，而不是简单地假设这样一个索引将会有帮助。

　
使用索引加快查询

当MySQL试图回达一条查询时，它查看有关你的数据的各种统计，并决定如何以最快的速度找出你想要的数据。对于前小节的查询，MySQL将读取albums表的所有titles并把它们与“Billboard Top Hits --1984”进行比较看是否匹配。它一旦找到一个匹配还不能停止，因为有相同曲目的专辑不止一个（如你可以有12张CD标有“Greatest Hits”），结果MySQL必须读取表中的每一行。这常称为“全表扫描”且可以避免。

你应该避免全表扫描，因为：

CPU开销：如果你没有很多专辑，检查所有这些标题的处理相对快些。但如果你需要在你的数据库中存储很多专辑呢？你有的专辑越多，花的时间越长。在专辑数量或检查它们所花的时间时间存在一种线性关系。
并发性：在MySQL正在从表中读取数据时，它锁定表使得没有其他人可以写入，但可以读取。当MySQL更新或删除表中的行时，它锁定表使得没有其他人可以从它读取。
磁盘开销：在一个大数据表上，一次全表扫描将消耗大量磁盘I/O。这可能明显地减慢你的数据库服务器 -- 特别是如果你的服务器是较慢的IDE驱动器。
最好是让全表扫描将到最少 --　特别是你的应用需要以规模或用户数伸缩。MySQL最新版确实有几个并发性方面的改善（BDB、InnoDB和Gemini表类型）。

在这里索引可以帮助你，简单地放一个，一个索引允许MySQL很快地确定任何给定值如“Billboard Top Hits -- 1984”是否将匹配表中的任何行。

怎样做到的呢？当你告诉MySQL索引一个特定列时，它在幕后创建另一个数据结构（索引）并用它存储关于被索引列中的值的某些额外信息（被索引的值常称为健码）。这是一种简化，MySQL将所有键码存储在一个树状数据结构中。该数据结构允许MySQL非常快速地找到特定键码。

当MySQL发现列上有一个索引，它将使用索引而不是执行一个全表扫描。这节省了CPU时间（不必读取所有可能的值）和磁盘I/O，而且它改善了并发性，因为MySQL只锁定表足够长的时间来获得所需的行（基于它在索引中找什么）。当你在表中有大量的数据，最终的改善可能非常明显。

对图3的albums表的CREATE TABLE语句的改进：

图3

CREATE TABLE albums (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(80)NOT NULL,

INDEX title_idx (title)
);

正如你所见的，语句只是简单地在定义后增加了一个INDEX行告诉MySQL在albums表中的title列上创建名为title_idx的索引。你可以给一个表增加多个索引，就像你可在表中有多个列一样。单个索引也可以有多个列合成。

要给现有的表加上一个索引而不是重建表，你可以用ALTER TABLE命令：

ALTER TABLE albums ADD INDEX title_idx (title)

查询处理

如果你的查询复杂，MySQL用于精确确定如何获取数据的原则可能变得难于理解。幸运的是，有几个一般原则和一条命令允许你获得正在做什么的更好的理解。首先，原则是：

如果MySQL确定了简单地扫描全表更快些，则它L将不使用索引。一般地，如果一个索引告诉MySQL访问表中大概30%的行，它放弃索引并简单地执行全表扫描。
如果多索引可以用来满足查询，MySQL将使用最严格的一个 -- 即导致最少的行被提取的那个。
如果你正在选择的列是一个索引的所有部分，MySQL可以直接从索引中读取锁需的数据，绝不接触（或锁定）表本身。
当联结几个表时，MySQL将首先从可能返回最少行的表中读取数据。你指定表的次序可能与MySQL使用它们的次序不同，这也影响到最终返回给你的行的次序，所以如果你需要行以特定的次序出现，要保证在你的查询中使用一个ORDER子句。
已经说了很多了，重要的是认识到MySQL所做的一些决策实际上是基于猜测，就像人类进行大量猜测一样，偶尔也会出错。

如果你怀疑已经发生或只是想理解MySQL怎样处理一条查询，你可以使用EXPLAIN命令。简单地在你的查询前面加上EXPLAIN这个字，并要求MySQL执行它，MySQL不执行查询，相反将报告有助于查询的候选索引列表和所知道的有关它们的一切。

EXPLAIN输出的完整讨论参见MySQL参考手册。

不要过分使用索引

已经知道索引使查询更快，你可能倾向于索引你表中的每个列。但是得益于索引的性能提高是有代价的，在表中每次执行INSERT、UPDATE、REPLACE或DELETE，MySQL不得不更新表上的每个索引以反映变化。

那么你如何确定何时食用它们呢？最常见的答案是“看情况”。它依赖你运行的查询类型和你运行它们的频度，它依赖于你的数据，它依赖于你的期望和需求。你得到了答案 -- 它依赖于很多事情。

在列上有索引的理由是MySQL缩窄其搜索范围以便尽可能会的匹配行（且避免全表扫描）。你可以认为索引是对列中的每个唯一值只包含一项。在索引中，MySQL必须考虑任何重复值，这些重复值稍微降低效率和索引的用途。

所以在索引一个列之前，考虑数据重复的百分比，如果该百分比太高，你可能觉察不到用索引带来的任何性能改善。

要以更简明的术语描述，它清楚地知道在albums表中索引曲目字段，因为有可能有大量不同的值，而且重复非常少。但如果在albums表中有一个分类列，索引它则可能价值不大，一般的CD收集包含多少不同的类型呢？将会有很多重复的类型值。

另一件要考虑的事情是你的查询可能使用的频度。MySQL只能对出现在一条查询种的WHERE子句中的特定列使用索引，试图回答查询：

SELECT * FROM albums WHERE id = 500

MySQL不能使用在title上的查询，该查询要求MySQL基于其id查找记录而不是其title。

如果你很少使用查询中WHERE子句中的一个列，它可能不值得索引该列。可能在极少的情况下容人全表扫描比要求MySQL在每次修改时保持索引更新总体上更有效些。.

有疑问时，进行测试。你总能运行某些到索引或不带索引的基准测试看哪一个更快，只要试图让你的基准测试逼真些。如果你的查询有20%是UPDATE，80%是SLEECT，肯定你的基准测试能发映出来，详见MySQL参考手册。

使用REPLACE查询

有可能你想往表中插入一条记录，除非它已经存在。如果记录已经存在，你想UPDATE它。不是重写代码中做这件事的逻辑，并需运行多个查询，而是MySQL使用REPLACE来做此项工作。

如果id是6的专辑假定有曲目“Shaking the Tree”，你可以这样写查询：

REPLACE INTO albums VALUES (6, Shaking the Tree)

重要的是理解REPLACE如何确定一条记录是否在表中存在。MySQL将在表上使用PRIMARY KEY或UNIQUE KEY来执行检查，如果都不存在，REPLACE效果上变成了INSERT。

使用临时表

当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。

创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字：

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

DROP TABLE tmp_table

如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在，临时表将有必要屏蔽（隐藏）非临时表tmp_table。

如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它：

　CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
　

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。然而，HEAP表与一般的表有些不同，且有自身的限制。详见MySQL参考手册。

正如前面的建议，你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引，临时表可能一点不快。

临时表在MySQL 3.23.0和更新版才有。

只用最新版的MySQL

2001年一月中旬，MySQL 3.23宣布稳定。除了后很多新功能外，它也比3.22系列更快和更具伸缩性。

MySQL的更新版不断推出。如果你控制着你的MySQL服务器，最好是尝试保持版本相对最新。除了有最新功能和错误修正，你常常会看到较新的MySQL发行有明显的性能提高。

其他资源

除了阅读MySQL手册，你也可以咨询：

你的DBA：如果你在一个共享服务器上使用MySQL，某个人被指定为数据库管理员（DBA），这是一个好机会。如果你的DBA非常熟悉MySQL，它可能可以在分析和优化你的表结构和查询上帮你一下。
MySQL用户邮件列表：有非常活跃的邮件列表，在其中MySQL用户经常彼此寻求帮助。新手和老手愿意分享其知识并彼此帮助解决共同的问题。实际上，有些MySQL开发团队成员很好地监视着邮件列表。
MySQL书籍：不要与MySQL参考手册混淆，Paul DuBois写了一本极好的书名为《MySQL》的书（有中文译本）