程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQLSchema設計（二）

MySQLSchema設計（二）

編輯：MySQL綜合教程

茹志鵑在《妯娌》中說，“再看紅英自己，那是連半個鐘頭的工都不肯耽誤的，也從沒見她吃過一口零食，一看就知道是個會精打細算、會過日子的人。”曾有人調侃，已婚身份最是適合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)

因為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展現

? 1 2 3 4 5 6 7 8 9 10 11 12 13 create table t (col timestamp); 5.1表現為： `col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 5.5 層現是： `col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 5.6 則為： `col` timestamp NULL DEFAULT NULL

可見，timestamp在5.6版本的變化是翻天覆地的。

隨著經濟全球化日益激烈，跨時區倒數據已是家常便飯。創建數據和schema的邏輯備份最常見的選擇還是mysqldump，但當我們打開dump文件頭會發現" /*!40103 SET TIME_ZONE='+00:00' */; "這麼一行。而我們的客戶端默認時區是：

? 1 2 3 4 5 6 7 mysql> select @@time_zone; +-------------+ | @@time_zone | +-------------+ | SYSTEM | +-------------+ 1 row in set (0.00 sec) ? 1 2 3 4 5 6 7 8 9 10 11 12 13 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

方法二用轉換函數處理

? 1 2 3 4 5 6 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');