MySQL（二）Schema与数据类型优化

在数据库中，schema（发音 “skee-muh” 或者“skee-mah”，中文叫模式）是数据库的组织和结构，schemas 和schemata都可以作为复数形式。模式中包含了schema对象，可以是表(table)、列(column)、数据类型(data type)、视图(view)、存储过程(stored procedures)、关系(relationships)、主键(primary key)、外键(foreign key)等。数据库模式可以用一个可视化的图来表示，它显示了数据库对象及其相互之间的关系

1. 选择优化的数据类型

选择正确的数据类型应遵循如下几个原则：

1. 更小的通常更好

更小的数据类型更快，占用更少的磁盘、内存和cpu缓存。

2. 简单就好

简单的数据类型操作需要的cpu周期更少。

3. 尽量避免NULL

NULL是列的默认属性，一般情况下最好指定列为NOT NULL。NULL列会使得索引、索引统计和值比较更加复杂。

1.1整数类型

TINYINT，SMALLINT,  MEDIUMINT,  INT,  BIGINT  分别使用

8            ， 16            ， 24              ，32  ， 64        位存储空间

1.2 实数类型

带有小数部分的数字；也可以使用DECIMAL存储比BIGINT还大二点整数，FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算

1.3 字符串类型

从MySQL开始每个字符串可以定义自己的字符集和排序规则，校对规则

1.3.1VARCHAR和CHAR

是最主要的两种字符串类型，但是这些值在磁盘内存中的存储非常难解释。前者存储可变长字符串，后者是定长的。

VARCHAR和CHAR在存和读的区别：

1. 存储的时候，CHAR总是会补足空格后再存储，不管用户插入数据时尾部有没有包含空格。

2. 存储的时候，VARCHAR不会先补足空格后再存储，但如果是用户在插入时特地加了空格那就会如实存储，而不会给删除。

3. 读取数据时，CHAR总是会删除尾部空格（哪怕是写入时包含空格）。

4. 读取数据时，VARCHAR总是如实取出之前存入的值（如果存储时尾部包含空格，就会继续保留着，不会像CHAR那样删除尾部空格）。

VARCHAR和CHAR的优缺点

对于MyISAM表，经常需要修改而容易形成碎片的myisam和isam数据表尽量使用Char， 它的缺点就是占用磁盘空间 ； 对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分，所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间， 所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利.

但是char也不是一无是处，如存储很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。 固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。十分频繁改变的column，因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。还有一个关于varchar的问题是，varchar他既然可以自动适应存储空间，那我varchar(8)和varchar(255)存储应该都是一样的，那每次表设计的时候往大的方向去好了，免得以后不够用麻烦。这个思路对吗？答案是否定的。mysql会把表信息放到内存中，这时内存的申请是按照固定长度来的，如果varchar很大就会有问题。所以还是应该按需索取。 

1.3.2 BLOT和TEXT类型

为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

字符类型是TINYTEXT， SMALLTEXT，TEXT （和前者同义） ，MEDIUMTEXT ， LONGTEXT；对应的二进制类型是TINYBLOB ， SMALLBLOB  ，BLOB（和前者同义） ， MEDIUMBLOB ， LONGBLOB。

1.3.3 使用枚举（ENUM）代替字符串类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

优点：简单安全、稳定、有内置方法、代码更优雅。

缺点：不可继承，无法扩展，但是一般常量在构件时就定义完毕了，不需要扩展。

1.4 日期和时间类型

没有最佳选择，只需要注意怎么保存日期和时间

DATETIME：该类型能保存大范围的值，精度为秒

TIMESTAMP（时间戳）：只使用4个字节的存储空间，空间效率更高，优先使用

存储比秒更小粒度的时间和日期时，可以使用自己的存储格式：BIGINT类型存储微妙级别的时间戳等。

1.5位数据类型

在MySQL5.0之前。BIT是TINYINT的同义词，但是之后，BIT有了新的行为特性；

bit称为位数据类型，其数据有两种取值：0和1，长度为1位。在输入0以外的其他值时，系统均把它们当1看待。这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等二值选择。

1.6选择标识符（identifier）

为标识列（identifier column）选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较或者寻找其他列。也可能在其他表中作为外键使用（需要与关联表中一样的类型）。

当心自动生成的schema，可能会导致严重的性能问题

1.7特殊类型数据

某些数据类型并不直接与内置类型一致，例如低于秒级精度的时间戳，ipv4地址。

2. MySQL schema 设计 

除了设计原则的好坏以外，MySQL实现机制也会在设计中带来一些问题：   

1.太多的列：MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过缓冲格式拷贝数据，然后再服务器层将缓冲内容解码成各个列，再转换成行数据结构中的操作代价时非常高的。

2. 太多的关联：所谓的"实体-属性-值"（EAV）设计模式是一种常见的糟糕的设计模式。 如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

3. 全能得枚举：防止过度使用枚举

4. 变相得枚举：枚举列允许在列中存储一组定义值的单个值，集合SET列则允许在列中存储一组定义值中的一个或多个值，容易导致混乱。

3.范式和反范式       

范式要求数据表中不存在任何的传递函数依赖。  如果Alex换人了，在反范式表中更换就很麻烦，需要改至少两条。

1.反范式

 

2. 范式

 

 3.1范式的优点和缺点

优点

范式化的更新操作通常比反范式要快

当数据较好的范式化时就只有很少或者没有重复数据

范式化的表通常更小，更好地放在内存中，执行操作更快

很少有多余地数据

缺点

需要关联，代价昂古，可能使一些索引策略无效

3.2 反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。

3.3 混用范式化和反范式化

在实际应用中一般都是混用，可能使用部分范式化地schema、缓存表、以及其他技巧。

在5.0版本后，可以使用触发器更新缓存值。

在网站实例中，可以在user表和message表中存储account_type 字段，而不用完全反范式化。

缓存衍生值也是有用的。

4 缓存表和汇总表

有时提升性能最好的办法就是在同一张表中保存衍生的冗余数据（指的是一个字段在多个表里重复出现）。有时候也需要创建一张完全独立的汇总表或缓存表。

缓存表表示存储那些可以比较简单地从schema其他表获取数据的表（如逻辑上冗余的数据）。

汇总表表示保存使用GROUP BY语句聚合数据的表（不是逻辑冗余的数据）

在使用缓存表和汇总表时必须决定是实时维护数据还是定期重建。当重建汇总表和缓存表时，通常需要保存数据在操作时依然可用，这就需要使用影子表——一张在真是表背后创建的表。

4.1物化视图

预先计算并且存储在磁盘上的表，可以通过各种策略刷新更新。

4.2 计数器表

计数器表在web应用中很常见，可以用来缓存一个用户的朋友数，文件下载次数等，使用独立的表可以帮助避免查询缓存失败。

5. 加快ALTER TABLE操作的速度

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。一般而言会导致MySQL服务中断。

只修改.frm 文件

快速创建MyISAM索引：有个小技巧，即先禁用索引，载入数据，再重启索引，使构建索引的工作延后到数据载入后（对唯一索引无效）。在现代版本的InnoDB中，技巧类似，先删除所有的非唯一索引，增加新列，再重建删除掉的索引。

 6. 总结

良好的schema设计原则是普遍适用的，但要注意MySQL的实现细节，尽可能保持任何东西小而简单。