mysql 表设计陷阱

       虽然有一些普遍的好或坏的设计原则,但也有一些问题是由 MYSQL的实现机制导致的,这意味着有可能犯一些只在 MYSQL下发生的特定错误。

 

1.太多的列

           MYSQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。 MYISAM的定长行结构实际上与服务器层的行结构正好匹配,所以不需要转换。然而, MYISAM的变长行结构和 INNODB的行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非常高的案例时,发现客户使用了非常宽的表(数千个字段),然而只有一小部分列会实际用到,这时转换的代价就非常高。如果计划使用数千个字段,必须意识到服务器的性能运行特征会有一些不同。

 

2. 太多的关联

        所谓的“实体 - 属性 - 值”(EAV)设计模式是一个常见的槽糕设计模式，尤其是在MYSQL下不能靠谱地工作。 MYSQL限制了每个关联操作最多只能有61张表,但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。事实上在许多关联少于61张表的情况下，解析和优化査询的代价也会成为 MYSQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

 

3. 全能的枚举

     注意防止过度使用枚举(ENUM)。下面是我们见过的一个例子:
    
           CREATE TABLE..(

                 country enum('','0','1,'2',.,'31")
     
    这种模式的 表设计非常凌乱。这样使用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案,这里应该用整数作为外键关联到字典表或者查找表来查找具体值。但是在 MYSQL中,当需要在枚举列表中增加一个新的国家时就要做一次 ALTER TABLE 操作。在 MYSQL5.0以及更早的版本中 ALTER TABLE是一种阻塞操作；即使在5.1和更新版本中,如果不是在列表的末尾增加值也会一样需要 ALTER TABLE。

 

4. 变相的枚举

       枚举(ENUM)列允许在列中存储一组定义值中的单个值，集合(SET)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。这是一个例子：

CREATE TABLE ... (

       is default set('Y','N") NOT NULL default 'N'

如果这里真和假两种情况不会同时出现,那么毫无疑问应该使用枚举列代替集合列。

 

5.无可替代的NULL

    我们之前写了避免使用NULL的好处,并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时,也不一定非得使用NULL。也许可以使用0、某个特殊值，或者空字符串作为代替。

   但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL在一些场景中,使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择个不可能的值,例如用-1代表一个未知的整数,可能导致代码复杂很多,并容易引入bug,还可能会让事情变得一团槽。处理NULL确实不容易,但有时候会比它的替代方案更好。

 

下面是一个我们经常看到的例子:

CREATE TABLE..(

           dt DATETIME NOT NULL DEFAULT '0000-00-00 00: 00: 00'

 

伪造的全0值可能导致很多问题(可以配置 MYSQL的SQL_MD0E来禁止不可能的日期,对于新应用这是个非常好的实践经验,它不会让创建的数据库里充满不可能的值)。值得一提的是, MYSQL会在索引中存储NULL值,而 Oracle则不会。