Schema

发表于   |   分类于   |  

选择优化的数据类型

选择数据类型设计schema通常有以下几个简单的原则：

  1. 更小的通常更好。
    例如如果只需要存0~200，那么tinyint unsigned更好。因为更小的数据类型，占用的更少的磁盘、内存和cpu缓存，处理所需的cpu周期也更少。
  2. 简单就好。
    简单的数据类型操作通常需要更少的cpu周期。如，整形比字符操作代价更低，因为字符集和排序校对规则使字符比较比整形复杂。
    例子：日期应该使用date,time,datetime而不是字符串；IP地址应该使用整形。
  3. 尽量避免NULL。
    可为null的列使得索引、索引统计和值比较更复杂。这样的列需要占据额外的存储空间，每个索引记录需要一个额外的字节。因此如果打算在列上建索引，最好加上not null限制。

第一步需要确定大类型：数字、字符串、时间等；第二步，确定存储范围精度等。eg. TIMESTAMP（4B）比DATETIME（8B）少一半存储空间，可以根据时区变化，具有自动更新的能力；但同时允许的时间范围也小的多。

整数

TINYINT(8b),SMALLINT(16b),MEDIUMINT(24b),INT(32b),BIGINT(63b)，有可选的UNSIGNED属性，表示不允许负值，该属性不影响性能和存储空间。

INT(11)括号中的11表示字符的显示宽度，并不是实际存储的范围，对大多数程序这个设置并没有太大的意义。

实数

浮点数，FLOAT（4B）和DOUBLE（8B）由cpu直接支持原生浮点计算；但cpu不支持DECIMAL的直接计算，所以由mysql服务器自身实现其高精度的精确计算。因此速度更慢。应尽量在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。当数据量较大的时候可选择替代方案，将小数乘以相应的倍数，使用BIGINT存储，就可以避免浮点数存储和计算不精确和DECIMAL计算代价高的问题。

字符串

VARCHAR和CHAR在不同的存储引擎中存储在磁盘和内存中的实现方式是不同的。

VARCHAR

变长比定长更节省空间，除非设置了ROW_FORMAT=FIXED。varchar需要1~2个字节存储字符串长度，当长度小于255时，使用1B，否则使用2B。所以varchar(10)占11个字节，varchar(1000)占1002个字节，其中多出来的1和2个字节用来存储长度。

varchar的更新可能使行变长，导致页内没有更多的存储空间存储，这是InnoDB需要分裂页使行可以存储进来。

有一些场景适合使用varchar：最大长度比平均长度大很多（节约空间）；列更新少；是用UTF-8这种变长字符集等。

CHAR

定长的，存储时会删除末尾的空格，然后根据需要采用空格进行填充以便比较。

适合存储很短的字符或者所有字符长度接近的。例如非常适合存储MD5，因为它是定长的。更适合存储经常变更的数据，不易产生碎片。

与char和varchar类似的还有BINARY和VARBINARY，用来存储二进制字符串，存储的是字节码而不是字符。

BLOB和TEXT

存储很大的数据，采用二进制和字符方式存储。当值太大的时候，InnoDB会使用外部存储区域，每个值内部存储1~4个字节的指针，指向外部的实际存储区域。两者的区别仅在于，BLOB是二进制，没有排序规则和字符集。

日期和时间

DATETIME

从1001到9999，精度是秒。把日期和时间封装到YYYMMDDHHMMSS的整数中，与时区无关，占8字节。

TIMESTAMP

保存从1970年1月1日午夜依赖的秒数，和UNIX时间戳相同。从1970~2038，占4个字节。每行数据的第一个TIMESTAMP值会被mysql自动更新。通常比DATETIME效率更高。

ipv4地址
    10.10.132.11存储在表中，将字段设置为int unsigned
    update table set ipv4=INET_ATON('10.10.132.11') where id=1;  #存储结果是168461323
    select INET_NTOA(ipv4) from table where id=1;    #查询结果为10.10.132.11

MySQL schema设计中的陷阱

太多的列

服务器层和存储引擎层需要通过行缓冲格式拷贝数据，再在服务器层将缓冲内容解码成各个列。解码过程的操作代价是非常高的。MyISAM变长行结构和InnoDB行结构总是需要这种转换，并且依赖于列的数量。

太多的关联

最多关联61张表，但最好在12个表以内做关联。

过渡的枚举

当枚举数量讲多，且容易变更时，可以使用整数外键关联到字典表。

尽量不适用NULL，但有些时候使用NULL比奇怪的数字更好

比如当时间为空的时候，不必存入0000-00-00 00:00:00。

关于ALTER TABLE

alter table对大表来说，性能是个大问题。mysql执行大部分修改表结构的操作方法是用心的结构创建一个空表，从就表中查出所有数据插入新表，然后删除旧表。一般而言，大部分alter bable操作将导致mysql服务中断。常用的技巧有两种：

  1. 现在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换；

  2. “影子拷贝”。创建新表，通过重命名和删表操作交换两张表。