MySQLSchema设计（二）精打细算，油盐不断_MySQL

bitsCN.com

茹志鹃在《妯娌》中说，“再看红英自己，那是连半个钟头的工都不肯耽误的，也从没见她吃过一口零食，一看就知道是个会精打细算、会过日子的人。”曾有人调侃，已婚身份最是适合DBA，毕竟，不当家不知柴米贵，年底的资源容量订购，那一分钱都是心头肉啊，会吃的吃千顿，不会吃的吃一顿。而且，故障诊断以及性能调优时，OS层的APP直接拖垮DB的案例也是家珍如数啊。所以，思前顾后，吃穿常有。谓之，DBA以俭德辟难。

活在大数据时代下，勤俭节约更是DBA的传统美德。慎重选择数据类型很重要，对类型当持有斤斤计较的心思，理由如下：

● 计算、进而减负CPU负载

㈠ 3种数据类型

1. INT（M） 到底有多M？

M 默认是11，最大有效显示宽度是255。无论M多大，INT一定是4 bytes。M仅表示显示宽度，与存储大小或类型包含的值的范围无关。离了zerofill这个属性，M是毫无意义的，硬说有呢、大概也是为了显示字符的个数、人性化点。对于存储和计算而言，INT(11)和INT(255)是相同的。

mysql> create table t (id int(2));Query OK, 0 rows affected (0.08 sec)mysql> insert into t select 10086;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from t;+-------+| id |+-------+| 10086 |+-------+1 row in set (0.01 sec)mysql> alter table t change column id id int(16);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t;+-------+| id |+-------+| 10086 |+-------+1 row in set (0.00 sec)mysql> alter table t change column id id int(16) zerofill;Query OK, 1 row affected (0.19 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from t;+------------------+| id |+------------------+| 0000000000010086 |+------------------+1 row in set (0.00 sec)mysql> alter table t change column id id int(5) zerofill;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t;+-------+| id |+-------+| 10086 |+-------+1 row in set (0.00 sec)mysql> alter table t change column id id int(6) zerofill;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t;+--------+| id |+--------+| 010086 |+--------+1 row in set (0.00 sec)

2 计算VARCHAR（N）N的最大值

今有道面试题：若一张表中只有一个字段VARCHAR(N)类型，utf8编码，则N最大值为多少?

我们不急着计算，先来看几个注意事项：

● 最大行长度是65535，不过NDB引擎除外。这个限制了列的数目，比如char(255) utf8，那么列的数目最多有65535/(255*3)=85，列的数目可以从这里得到依据

● 字符集问题

latin1：占用一个字节

gbk：每个字符最多占用2个字节

utf8：每个字符最多占用3个字节

● 长度列表

需要额外地在长度列表上存放实际的字符长度：小于255为1个字节，大于255则要2个字节

● 1byte/row开销

在字符集选用latin1情况下，依据限制3，应该有65533长度可用，然而：

mysql> create table max_len_varchar(col varchar(65533) charset latin1);ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table max_len_varchar(col varchar(65532) charset latin1);Query OK, 0 rows affected (0.16 sec)

所以，MySQL中，实际存储应该是从第2个字节开始

至此，我们便可以从容得出开头的答案：(65535-1-2)/3。有始有终，再以一道面试题结束本小节：

create table t (col1 int(11), col2 char(50), col3 varchar(N)) charset=utf8;这里的N最大值？有兴趣的朋友可自行算下。

3 timestamp那些事

先看个MySQL datetime的bug提提神：

mysql> create table t (start_time datetime,stop_time datetime);Query OK, 0 rows affected (0.12 sec)mysql> insert into t (start_time, stop_time) values ("2014-01-19 21:46:18", "2014-01-20 00:21:31");Query OK, 1 row affected (0.02 sec)mysql> select start_time, stop_time, stop_time - start_time from t;+---------------------+---------------------+------------------------+| start_time | stop_time | stop_time - start_time |+---------------------+---------------------+------------------------+| 2014-01-19 21:46:18 | 2014-01-20 00:21:31 | 787513 |+---------------------+---------------------+------------------------+1 row in set (0.00 sec)

因为datetime类型不支持直接计算，时间转化为了数字来相减了才得到此结果的。除了这个bug之外，通常也应该尽可能使用timestamp，毕竟从存储上看，timestamp 仅占 4 个字节，比datetime(8字节)和date(8字节)的空间效率都要高。而且，有的人习惯用 INT UNSIGNED 来存储一个转换成Unix时间戳的时间值，但这不会带来任何收益，MySQL提供的from_unixtime()把Unix时间戳转换成日期，unix_timestamp()把日期转换成Unix时间戳，所以我们没有必要坚持这个习惯，因为timestamp实际上是4个字节的INT值，都用系统默认的时区，相同的字符串值会得到不同的时间戳，反而更加不好处理。

timestamp的行为规则比较复杂，并且不同版本的MySQL会有变动，那么有时候"经验主义"便会让人踢到铁板，所以我们应该验证数据库的行为是你需要的，比较好的做法是，修改完timestamp列后用show create table命令检查输出，以下是同一个DDL语句在不同版本的timestamp展现

create table t (col timestamp);5.1表现为：`col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP5.5 层现是： `col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP5.6 则为： `col` timestamp NULL DEFAULT NULL

可见，timestamp在5.6版本的变化是翻天覆地的。

随着经济全球化日益激烈，跨时区倒数据已是家常便饭。创建数据和schema的逻辑备份最常见的选择还是mysqldump，但当我们打开dump文件头会发现" /*!40103 SET TIME_ZONE='+00:00' */; "这么一行。而我们的客户端默认时区是：

mysql> select @@time_zone;+-------------+| @@time_zone |+-------------+| SYSTEM |+-------------+1 row in set (0.00 sec)

mysql> drop table if exists t;mysql> create table t (col timestamp);mysql> insert into t select now();mysql> select * from t;+---------------------+| col |+---------------------+| 2014-01-25 10:42:44 |+---------------------+1 row in set (0.00 sec)

$ mysqldump -uroot -poracle testdb t --where='col="2014-01-25 10:42:44"' | grep INSERT

返回空，导不出数据？下面给出2种解决方案

方法一 加上参数 --tz-utc

$ mysqldump -uroot -p testdb t --tz-utc=0 --where='col="2014-01-25 10:42:44"' | grep INSERT

方法二 用转换函数处理

mysql> select unix_timestamp(col) from t;+---------------------+| unix_timestamp(col) |+---------------------+| 1390617764 |+---------------------+

$ mysqldump -uroot -p testdb t --where='col=from_unixtime(1390617764)' | grep INSERT

INSERT INTO `t` VALUES ('2014-01-25 02:42:44');

㈡ 数据类型转换

基本原则：

① 不要在字段前增加函数

如：

② 不要把字段嵌入到表达式中

举个例子吧、假设我在字符列上建立个索引、然后：

㈢ 数据类型优化

下面总结我认为优化数据类型的几条通用原则为：

1、数据类型更小通常更好，数据类型越简单越好

5、多使用enum，set

6、IP用int存：inet_aton()、inet_ntoa()

7、使用decimal而不是float & double

8、MyISAM多使用char、InnoDB多使用varchar

bitsCN.com