3. 数据库结构优化和建索引

3.       数据库结构优化和建索引

优化一个设计得很糟糕或者索引建得很差的数据库可以成数量级的提高性能。如果你想得到高性能的话，你就必须根据你即将运行的SQL来设计数据库表结构和索引。同时你应该对于不同类型的查询性能的需求有一个预估，因为对于一类查询的修改或者表结构的改变可能会影响其他的行为。比如说，为了提高查询效率而增加的索引会降低更新的速度。同样的，一个非归一化的表结构可能在提高一些类型的查询效率的同时降低另外一些的效率。添加计数和汇总表是一个优化查询的好路子，但是它也会带来维护上的问题。

有时候你需要超越一个开发者的职能，去质疑提到你面前的业务需求。那些不懂数据库系统的人通常会提出一些会影响性能的业务需求。如果你告诉他们，一个很小的功能可能会要求两倍于现在的硬件时，他们很可能砍掉这个需求。

表结构优化以及建立索引不仅需要对系统有一个整体的把握，同时也要求对细节有足够的关注。你需要理解整个系统以期明白系统的各个部分之间是如何相互影响的。本章从数据类型的讨论开始，接下来到索引策略以及归一化。最后针对存储引擎提前了一些注意点。

3.1.             选择最优的数据类型

MySQL支持许多种数据类型，因此选取正确的数据类型来存储你的数据是提高性能一个很重要的指标。下面是一些很简单的指标，它可以帮助你来作出正确的选择，不管你正在存储的是什么类型的数据。

n 越小越好

      一般来说，尽量使用可以正确存储和表示你的最小数据类型。数据越小一般来说也就越快，因为他们占用的磁盘空间、内存以及CPU缓存更小。他们一般也会使用更小的CPU周期来处理。

确保你没有低估你需要存储的数据的范围，因为如果你确实需要增加数据类型的范围的话，你会发现这是一个很费时费力的事情。如果你对于选择哪一种数据类型有疑惑，那么就选择你认为不会超过数据范围的最小类型（如果系统负载不是很重，或者没有存储很多数据的话，或者你正在处于设计的前期的话，你可以在以后很容易的修改它）。

n 简单即美

越简单的数据类型一般也就需要越少的CPU周期去处理。比如说，整数就比字符串更容易比较大小，因为字符以及它的比较规则使得字符串比较变得更加复杂。这里有两个例子：在MySQL中你应该使用它的内置类型而不是字符串来存储日期和时间，另外你也应该使用整数来存储IP地址。这些内容我们会在后面的章节中详细讨论。

尽可能避免NULL

如果可能的话，你要将所有的列都定义为NOT NULL的。许多表包括了一些可以为NULL的列，甚至应用中根本就不需要存储NULL（即没有数据），这一切仅仅是因为它的默认值是NULL。除非你确认需要使用NULL，否则你都要把那些列设置为NOT NULL。

在MySQL中优化一个包括NULL的列是很困难的，因为它会使得建索引，索引统计以及值比较变得更加困难。一个可以为NULL的列在MySQL中需要使用更多的空间以及特殊的处理方式。当一个可以为NULL的列在建索引时，每条记录需要一个额外的byte，甚至可能导致一个定长的索引（比如在单个整数列上的索引）在MyISAM上被转换成一个变长的索引。

即使你的确需要存储一个NULL在数据表中，你可能也不需要NULL。可能考虑用0或者其他一些特殊的值，或者空字符串来表示。将一个NULL列变成一个NOT NULL的列并不会带来性能上多大的提升，因为不必要花时间去查找并将已经使用的表结构中的一些NULL列转换成NOT NULL的，除非你确定这些NULL列造成了问题。然而，如果你打算对这些列建索引，那么还是尽量避免使用NULL吧。

对一个给定的列决定使用何种数据类型的第一步是确定它的基本类型到底是什么：数字，字符串，临时变量等等。这一般会很直观，但是我们也会注意到在某些特定的情况下选择并不明朗。

第二步就是选择一个特定的类型。MySQL的许多数据类型可以存储同一种类型的数据，但是它们的存储范围，精度，或者物理空间会有所不同。一些数据类型可能还有一些特殊的表现或者属性。

比如说，一个DATETIME和一个TIMESTAMP列都可以存储精确到秒的日期和时间。然而，TIMESTAMP只使用DATETIME的一半空间，并且是时区自适应的，同时也有一些自动更新的功能。另一方面，它只有一个很小的范围，有些时间它的特殊功能会变成障碍。

接下来我们将会讨论一些基础数据类型。MySQL为了兼容性支持许多别名，如INTEGER, BOOL和NUMERIC。这些只是别名而已，他们可能会引起歧义，但是并不会影响性能。