數據庫設計規范（MYSQL、WEB)

4. 適度的空間換時間

　　比如一個查詢頻繁的表，如果大部分字都時靜態長度的；可以全部都換成靜態的長度的（靜態表），以提高查詢效率。

注意:

CHAR[M] 屬於靜態長度類型, 存放長度完全以字符數來計算, 所以最終的存儲長度是基於字符集的, 如 latin1 其最大存儲長度為 255 字節, 但是如果使用 gbk 則最大存儲長度為 510 (255x2) 字節. CHAR 類型的存儲特點是不管實際存放的數據多長, 在數據庫中都會存放 M 個字符, 不夠通過空格補上, M 默認為1. 雖然 CHAR 會通過空格補齊存放空間, 但是在訪問數據時, MYSQL會忽略最的的所有空格, 所以如果實數據在最後確實需要空格, 則不能使用 CHAR 類型來存放. 在MYSQL 5.03 之前的版本中, 如果定義 CHAR時 M值超過 255, MYSQL 會自動將 CHAR 類型轉換為可以存入對應數據量的 TEXT類型, 如 CHAR(1000) 會自動轉換為 TEXT, CHAR(10000) 則會轉為 MEDIUMTEXT. 而從 MYSQL 5.0.3 開始, 所有超過 255 的定義 MySQL 都會直接拒絕並給出錯誤信息, 不再自動轉換.

VARCHAR[M] 屬於動態存儲長度類型, 僅存儲占用實際存儲數據的長度. 其存放的最大長度與 MYSQL 版本有關, 在 5.0.3 之前的版本 VARCHAR 以字符數控制存儲的最大長度, 最大只能存放 255 個字符, 占用存儲空間的實際大小與字符集有關. 但是從 5.0.3 開始, VARCHAR 的最大存儲限制已經更改為字節數限制了, 擴展到可以存放 65535 字節的數據, 不同的字符集可能存放的字符數並不一樣. 也就是說, 在 MYSQL 5.0.3 之前的版本, M 所代表的是字符數, 而從 5.0.3 版本開始, M 代表字節數了. VARCHAR 的存儲特點是不管設定 M 為多大值, 真正占用的存儲空間只有存入的實際數據的大小, 和 CHAR 不同的是 VARCAHR 會保留存入數據最後的空格, 也就是說我們存入什麼, MYSQL 返回的就是什麼. 在 VARCHAR 類型字段的數據中, MySQL 會在每個 VARCHAR 數據中使用 1 到 2 個字節來存放 VARCHAR 數據的實際長度, 當實際數據在 255 字節之內時, 會使用 1 字節來存放實際長度, 而大於 255 字節時, 則需要使用 2 字節來存放.

TINYTEXT, TEXT, MEDIUMTEXT 和 LONGTEXT 這 4 種類型同屬於一種存儲方式, 即動態存儲長度類型, 不同的僅是最大長度的限制. 4 種類型的定義都是通過最大字符數來限制, 但它們的字符數限制實際上是可以理解為字節數限制, 因為當使用多字節字符集時, 實際能存放的字符數並沒最大字符數那麼多, 而是以單字節字符來計算的字符數. 此外, 由於是動態存儲長度類型, 所以和 VARCHAR 一樣, 每個字段數據之前都需要一個存放實際長度的空間. TINYTEXT 需要 1 個字節來存放, TEXT 需要 2 個字節, MEDIUMTEXT 和 LONGTEXT 則分別需要 3 個和 4 個字節來存放實際數據長度. 實際上, 除了 MySQL 內嵌的最大長度限制之外, 它們還受到客戶端與服務器端的網絡通信緩沖區最大值 (max_allowed_packet 默認為 1M, 也就是說, MEDIUMTEXT 和 LONGTEXT 在默認情況可能存不進去值) 的限制.

這 4 種 TEXT 類型和 CHAR 及 VARCHAR 在實際使用中存在幾個不一樣的地方:

1. 不能設置默認值.
2. 只有 TEXT 可以使用 TEXT[M] 這樣的方式通過 M 設置大小.
3. 基於這 4 種類型的索引必須指定前綴長度.

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分別占用的字節為 1, 2, 3, 4, 8. INT 類型的值就上億了.
對於 INT[M] 中 M 值的解釋: 以前我遇到很多人他們認為 INT(4), INT(10) 其取值范圍分別是 (-9999 到 9999), (-9999999999 到 9999999999). 這種理解是錯誤的. 其實對整型中的 M 值, 與 ZEROFILL 屬性結合使用時, 可以實現列值等寬. 不管 INT[M] 中 M 值是多少, 其取值范圍還是 (-2147483648 到 2147483647 有符號時), (0 到 4294967295 無符號時). 官方文檔說明: 顯示寬度並不限制可以在列內保存的值的范圍, 也不限制超過列的指定寬度的值的顯示. 當結合可選擴展屬性ZEROFILL使用時, 默認補充的空格用零代替. 例如: 對於聲明為INT(5) ZEROFILL的列, 值4檢索為00004. 請注意如果在整數列保存超過顯示寬度的一個值, 當MySQL為復雜聯接生成臨時表時會遇到問題, 因為在這些情況下MySQL相信數據適合原列寬度.如果為一個數值列指定ZEROFILL, MySQL自動為該列添加UNSIGNED屬性.

a. 較頻繁的作為查詢條件的字段應該創建索引.

b. 唯一性太差的字段不適合單獨創建索引, 即使頻繁作為查詢條件.

唯一性太差的字段主要是指哪些呢? 如狀態字段, 類型字段等這些字段中存放的數據可能總共就那麼幾個或幾十個值重復使用, 每個值都會存在於成千上萬或更多的記錄中. 對於這類字段, 完全沒有必要創建單獨的索引. 因為即使創建了索引, MYSQL QUERY OPTIMIZER 大多數時候也不會去選擇使用, 如果什麼時候 MySQL QUERY OPTIMIZER 選擇了這各索引, 那麼非常遺憾地告訴你, 這可能會帶來極大的性能問題. 由於索引字段中每個值都會含有大量的記錄, 那麼存儲引擎在根據索引訪問數據的時候會帶來大量的隨機 IO, 甚至有些時候還會出現大量的重復 IO.

c. 更新頻繁的字段不適合創建索引

索引中的字段被更新的時候, 不僅要更新表中的數據, 還要更新索引數據, 以確保索引信息是准確. 這個問題致使 IO 訪問量較大增加, 不僅僅影響了更新 Query 的響應時間, 還影響了整個存儲系統資源消耗, 加大了整個存儲系統負載.

d. 不會出現在 WHERE 子句中的字段不該創建索引.

注意:
MySQL 中索引的限制
1. MYISAM 存儲引擎索引長度的總和不能超過 1000 字節.
2. BLOB 和 TEXT 類型的列只能創建前綴索引.
3. MySQL 目前不支持函數索引.
4. 使用不等於 (!= 或者 <>) 的時候, MySQL 無法使用索引.
5. 過濾字段使用函數運算 (如 abs (column)) 後, MySQL無法使用索引.
6. Jion 語句 中 Jion 條件字段類型不一致的時候, MySQL無法使用索引.
7. 使用 LIKE 操作的時候如果條件以通配符開始 (如 '%abc...')時, MySQL無法使用索引.
8. 使用非等值查詢的時候, MySQL 無法使用 Hash 索引.

建表 SQL 語句示例：

CREATE TABLE `admin` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`role_id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '所屬組ID',
`username` char(16) NOT NULL COMMENT '用戶名',
`passWord` char(33) NOT NULL COMMENT '密碼',
`acl` text NOT NULL COMMENT '資源控制',
`ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創建時間',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用戶表';