《mysql数据库设计原则》由会员分享，可在线阅读，更多相关《mysql数据库设计原则（10页珍藏版）》请在金锄头文库上搜索。
1、MySQLMySQL 数据库设计原则数据库设计原则MySQL 对于成为一个非常快速的数据库服务器有着当之无愧的名声，它也非常容易设置和使用。随着它作为网站后端数据库得声望日增，其效果在去年开始有明 显提高。但是很多 MySQL 用户更多地知道如何创建一个数据库并编写对它的查询。就像成千上万的人通过载闲暇时用 Linux 做实验来学习 Unix 那样，很 多人通过玩 MySQL 学习关系数据库。这些 MySQL 新手的大多数既没有关系数据库理论的背景，又没有时间阅读 MySQL 手册全文。 因此，我们决定研究某些方法，你可以用针对优化性能来调节MySQL。在读完本文后，你将理解一些帮助你设计你的
2、 MySQL 数据库和查询的技术，值得你的应用很有效率。我们将假定你熟悉 MySQL和 SQL 基础，但不假定你有这两方面的广博知识。只存储你需要的信息这听上去是常识，但人们常常采取“厨房下水道”的方式进行数据库设计。他们认为可能项要得每样东西都要存储并设计数据库保存所有者这些数据。你需要对你的需 求现实些，并确定取确实需要什么信息。你常常能随意产生一些数据而不把它存在数据库表中。在这种情况下，从一个应用开发者的角度看也有道理这样做。例 如，在线目录的产品表可能包含各种产品的名称、介绍、尺寸、重量和价格。除了价格，你可能想存储每个项目相关的税和运输成本。但实际上不必这样做。首先税 和运输成本可
3、以方便地（由你的应用或 MySQL）计算出来。其次，如果税和运输成本改变了，你可能必须编写必要的查询更新每个产品记录中的税和运输的费 率。有时人们认为这太难不能在以后往数据库表中加入字段，所以他们感觉不得不定义尽可能多的列。这是明显的概念错误。在MySQL 中，你可以用 ALTER TABLE 命令方便地修改表定义以适应你改变的需求。例如，如果你突然认识到你需要给你的产品表增加一个级别列（可能你想允许用户在你的目录中给产品评级） ，你可以这样做：ALTER TABLE products ADD rank INTEGER 这给你的产品表增加了一个整数类型的级别列，你能用 ALTER TABLE
4、做什么的完整介绍参见 MySQL 手册。只要求你需要的东西-要清晰就像说“只存储你需要的东西”那样，这可能看来是常识，但这一点常常被忽视，为什么呢？因为在一个应用开发时，需求经常改变，所以很多查询最终看来是这样：SELECT * FROM sometable 当你不能肯定你将需要哪一列时，要求所有列明显是最省力的事情，然而随着你的表不断增大和修改，这可能变成一个性能问题。最好是在你的最初开发完成后再花些时间并确定你真正从你的查询中需要什么：SELECT name, rank, description FROM products 这带来了一个相关的观点，即代码维护比性能更重要。大多数变成语言（P
5、erl、Python、PHP、Java 等）允许通过字段名和数字编号访问一条查询的结果，这意味着你可以访问命名字段或字段都可以得到相同的数据。长期看，最好使用列名而不是其编号位置，为什么？因为一个表中或一条查询中地列的相对位置可以改变。它们在表中可能因为重复使用 ALTER TABLE 而改变，它们在查询中将因重写了查询而忘记更新应用逻辑来匹配而改变。当然，你仍然需要小心改变列名！但如果你使用列名而非标号位置，如列名改变，你可以用 grep 搜索源代码或使用编辑器的搜索能力查找你需要修改的代码。规范化你的表结构如果你以前从未听说过“数据规范化” ，不要害怕。规范化可能是一个复杂的专题，你可以从
6、只理解最基本的规范化概念中正真正获益。理解它的最容易的方法是认为你的表是一个电子报表。如果你想以一个报表跟踪你的 CD 收藏，你可以如图 1 种那样进行设计：图 1引用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 首改怎么办。这意味着用这种方法，在极端的情况
7、下，你将需要一个非常宽的表格（或一个超过 100 个字段的表）来保存所有的数据。规 范化表结构的目标是使“空单元”的数量最少，在上述 CD表的情况下，如果你允许 CD 可能包含 100 首曲子，你会有很多这样的空单元。不管你何时处理可能扩 展到类似该 CD 表那样数量的字段列表，它是你需要将你的数据分割成 2 个或更多表的标志，然后你一起访问并获得你需要的数据。很多关系数据库的新手不真正知道关系数据库管理系统中关系是什么。简单地说，就像一组信息存在可以基于共性数据联结（JOIN）在一起的不同表中，很不幸，这听上去更学术化和含糊，但 CD 数据库提出了一个具体情况，我们可以研究如何规范数据。每个
8、 CD 列表有一个固定的属性（标题、艺术家、年份、分类）集和一个不定的属性（曲目表）集的理解给了我们一些如何分成成能相互关联的表的思路。你可以创建一个所有专辑及其固定属性的表，另一个包含这些专辑的所有曲目的表。这样不是水平思考（像表格） ，你垂直思考-就好像你创建列表而不是行-并建立一个如图 2 的表结构：专辑的编号（MySQL 镜自动为你生成，因为我们在列上使用了AUTO_INCREMENT 属性）关联不同曲目到一给定专辑，tracks 表中的album_id 字段匹配专辑表中的一个 id。这样要获得给定专辑的所有曲目，你应该用如下查询：SELECT tracks.num, tracks.n
9、ameFROM albums, tracksWHERE albums.title = Billboard Top Hits - 1984AND albums.id = tracks.album_id该结构即灵活又有效。灵活性来自你可以在以后将数据加入系统而不必重新你已完整的工作的事实。例如，如果你想增加每一张专辑的艺术家信息，你可以床架一个 artists 表，关联到 albums 表，就像 tracks 那样。你无需修改现有的结构-只是增加它。有效性来自于在你的数据中没有明显的数据重复且没有大量的空洞（空单元）的实施。这样 MySQL 在你的数据库表中既不存储多余的数据，也不比花额外的精力搜
10、索大量空区域。如果你对关系数据库是新手，规范化你的数据看起来有点奇怪，但在存储和检索数据时，它使 MySQL 非常有效，并给予你扩展和伸缩你的应用却不必多次重构你的数据库的灵活性。尽可能早的花时间想清楚数据库设计，并考虑你的需求怎样随时间增长，前期花的时间永远是值得的。复合索引复合索引（有时称组合索引）是急于多个列的单一索引。MySQL在处理一条查询时每个表只使用一个索引，这意味着如果你有多个经常出现在 WHERE 子句中的列，你可能要通过创建一个复合索引来加快这些查询。考虑下列表结构片断：CREATE TABLE people (last_name VARCHAR(50) NOT NULL,
11、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
12、将只使用一个复合索引。所以如果一个复合索引有多个列合成：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 试图回达一条查询时，它查看有关你的数据的各种统计，并决定如
13、何以最快的速度找出你想要的数据。对于前小节的查询，MySQL 将读取 albums 表的所有 titles 并把它们与“Billboard Top Hits -1984”进行比较看是否匹配。它一旦找到一个匹配还不能停止，因为有相同曲目的专辑不止一个（如你可以有 12 张 CD 标有“Greatest Hits” ） ，结果 MySQL 必须读取表中的每一行。这常称为“全表扫描”且可以避免。你应该避免全表扫描，因为：引用 CPU 开销：如果你没有很多专辑，检查所有这些标题的处理相对快些。但如果你需要在你的数据库中存储很多专辑呢？你有的专辑越多，花的时间越长。在专辑数量或检查它们所花的时间时间存在
14、一种线性关系。 并发性：在 MySQL 正在从表中读取数据时，它锁定表使得没有其他人可以写入，但可以读取。当 MySQL 更新或删除表中的行时，它锁定表使得没有其他人可以从它读取。 磁盘开销：在一个大数据表上，一次全表扫描将消耗大量磁盘I/O。这可能明显地减慢你的数据库服务器 - 特别是如果你的服务器是较慢的 IDE 驱动器。 最好是让全表扫描将到最少 - 特别是你的应用需要以规模或用户数伸缩。MySQL 最新版确实有几个并发性方面的改善（BDB、InnoDB 和 Gemini 表类型） 。在这里索引可以帮助你，简单地放一个，一个索引允许 MySQL很快地确定任何给定值如“Billboard
15、Top Hits - 1984”是否将匹配表中的任何行。怎样做到的呢？当你告诉 MySQL 索引一个特定列时，它在幕后创建另一个数据结构（索引）并用它存储关于被索引列中的值的某些额外信息（被索引的值常称为健 码） 。这是一种简化，MySQL 将所有键码存储在一个树状数据结构中。该数据结构允许 MySQL 非常快速地找到特定键码。当 MySQL 发 现列上有一个索引，它将使用索引而不是执行一个全表扫描。这节省了 CPU 时间（不必读取所有可能的值）和磁盘I/O，而且它改善了并发性，因为 MySQL 只锁定表足够长的时间来获得所需的行（基于它在索引中找什么） 。当你在表中有大量的数据，最终的改善可
16、能非常明显。对图 3 的 albums 表的 CREATE TABLE 语句的改进：图 3CREATE 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
