MySQLSchema设计（二）

茹志鹃在《妯娌》中说，再看红英自己，那是连半个钟头的工都不肯耽误的，也从没见她吃过一口零食，一看就知道是个会精打细算、会过日子的人。曾有人调侃，已婚身份最是适合DBA，毕竟，不当家不知柴米贵，年底的资源容量订购，那一分钱都是心头肉啊，会吃的吃

       茹志鹃在《妯娌》中说，“再看红英自己，那是连半个钟头的工都不肯耽误的，也从没见她吃过一口零食，一看就知道是个会精打细算、会过日子的人。”曾有人调侃，已婚身份最是适合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)是相同的。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

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: 0

 

mysql> 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: 0

 

mysql> 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: 0

 

mysql> 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: 0

 

mysql> 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: 0

 

mysql> 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长度可用，然而：

1

2

3

4

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 BLOBs

mysql> 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提提神：

1

2

3

4

5

6

7

8

9

10

11

12

13

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)