MYSQL数据库表设计与优化(一)

优化糟糕设计的表结果或者索引能很大程度改进mysql的性能。 如果需要高性能， 那么就需要根据不同的操作需求精心设计表结构和索引， 这当然需要对各种查询做出性能需求评估， 因为改变一个查询或者一部分表结构设计会在其他地方引的性能。 优化通常需要做出取舍， 例如， 通过增加索引来加速查询速度会减慢修改速度; 类似的， 一个非完全按范式设计的表结构能使某些查询速度提高， 但可能会导致其他的效率降低; 增加计数器或者汇总表能有效的优化一些查询， 但是维护代价也是不菲的。

有时候， 作为开发人员的你需要做超出开发人员职责的事情去弄清楚递到你面前的开发任务， 有时候， 那些不太了解数据库系统原理的人提出的需求可能不会考虑需求的性能影响， 如果你能清楚的告诉他们这些对数据库造成大压力的小功能需要导致硬件成本成倍增加的话， 他们可能会考虑砍掉一些需求。

表设计和索引优化要求设计开发人员了解系统构架和实现细节， 设计人员需要去理解整个系统和可能的各种影响因素。 这一章依次重点讨论数据类型， 索引策略和范式化， 最后对一些数据引擎做一些简单的介绍。

Choosing Optimal Data Types

选择正确的数据类型

Mysql 提供多种数据类型的支持，选择正确的数据类型对能否得到高性能的设计影响很大。 以下一些简单的指导规则能帮助您对各种数据类型的设计决策做出更好的选择：

小的比大的好

一般来说， 在保证正确性的前提下， 尽量使用最小的数据类型来存储和表示数据。 小的数据类型一般比大的更快， 因为小的数据类型占用的磁盘空间， 内存和cup缓存都相对小， 需要的cpu处理也要相对少; 这个原则很重要， 但是设计的时候也不要低估需要存储的数据的数据范围， 毕竟， 如果后来发现需要在多个地方改变某个数据的数据类型的话， 将是一件痛苦耗时的事情。 如果正在多个可选数据类型之间犹豫不绝的话， 就选那个能够满足数据范围需求的最小的数据类型吧(如果系统比较简单， 数据量不大，或者正处于早期设计阶段， 后面是很容易修改的)

简单的就是好的

简单的数据类型需要的cup处理周期更少， 比如， 对整数的处理比字符串处理更容易， 因为字符集和排序规则使得字符串比较复杂化了， 两个典型例子是： 使用mysql的内嵌数据类型来存储日期和时间而不是字符串， 使用整数存储Ip地址而不是字符串。 后面会对更详细的讨论这条规则。

尽量避免NULL数据类型

如果可能， 尽可能把字段定义成NOT NULL。 许多表包含一些字段允许空的字段， 即使应用需求不需要存储null的数据， 这样做的原因知识因为允许字段为NULL是缺省的。您应该注意把字段设计生非NULL的， 除非真的是需要存储NULL值。 在查询优化时， mysql很难优化需要引用到能包含NULL值的字段的查询， 因为允许为null的字段使得索引， 索引统计和值比较更复杂。

允许NULL的字段会占用掉更多的存储空间和花掉更多的cpu处理， 当为一个可为空的字段建立索引时， 需要为每项分配一个额外的字节， 在myisam中， 甚至会使得一个固定大小的索引(比如建立在单个整数的缩影)变成一个变长大小的索引。 即使遇到不需要存储值到某个字段， 也应当考虑不使用NULL， 而是考虑使用0， 一个特殊值或者空串来代替。 把字段从NULL改成NOT NULL的性能改善通常不是很大， 所以， 除非确实证明NULL字段在引起性能问题， 一般不需要把超找并修改NULL字段为NOT NULL字段当做一件大事来对待; 但是， 在做设计的时候， 注意尽量把需要索引的字段设计成不允许为空的。