mysql設計表結構資料類型的選擇

標籤：缺點   type   結構   否則   mysq   模式   mysql   alt   進位   

選擇合適的資料類型

在使用MySQL建立資料表的時候會遇到一個問題,如何為欄位選擇合適的資料類型.比如建立一個員工資訊表,每個欄位都可以用很多種類型來定義,

int,char,float等等.

char和varchar

char和varchar都是用來儲存字串類型的資料,但是他們儲存和檢索的方式不一樣.char屬於固定長度的字元類型,二varchar屬於可變成的字元類型

值	char(4)	儲存需求	varchar(4)	儲存需求
‘‘	‘   ‘	4個位元組	‘‘	1個位元組
‘ab‘	‘ab ‘	4個位元組	‘ab‘	3個位元組
‘abcd‘	‘abcd‘	4個位元組	‘abcd‘	5個位元組
‘abcdefg‘	‘abcd‘	4個位元組	‘abcd‘	5個位元組

 

由於char是固定長度的,所以它的處理速度比varchar快得多,但是其缺點是浪費儲存空間,程式需要對尾行空格進行處理,所以對那些變化不打併且查詢速度有較高的要求的資料可以考慮使用char類型來儲存

　　在mysql中,不同的儲存引擎對char和varchar的使用原則有所不同

• MyISAM儲存引擎
  •  建議使用固定長度的數列代替可變長度的資料列
• InnoDB儲存引擎
  • 建議使用varchar類型,對於InnnoDB資料表,內部的行儲存格式沒有區分固定長度和可變長度,因此使用char列不一定比可變長度的varchar效能好
  • 由於char平均佔用空間多餘varchar,因此varchar來UI消化需要處理的資料航的儲存總量和磁碟I/O是比較好的.

 

TEXT和BLOB介紹

在選擇大文本的時候我們會優先選擇text類型或者blob比如文章.

那麼TEXT和BLOB最主要的區別是 BLOB能用來儲存位元據比如照片.而text智能儲存字串資料,比如文章和日記.

根據儲存的文本長度不同和儲存的位元組不同我們可以使用

MEDIUMTEXT,LONGTEXT和MUDIUMBLOB,LONGBLOB

常見問題空洞問題

BLOB和text在執行了大量的刪除操作時,會留下很大的‘空洞‘,以後填入這些‘空洞‘的記錄在插入的效能上回有影響.為了提高效能,建議使用OPTIMIZE TABLE功能對類表進行磁碟重組,

避免空洞帶來的效能問題

空洞例子:

CREATE TABLE t (id VARCHAR(100),context TEXT);INSERT INTO t VALUES(1,repeat(‘haha‘,100));INSERT INTO t VALUES(2,repeat(‘haha‘,100));INSERT INTO t VALUES(3,repeat(‘haha‘,100));insert into t select * from t;insert into t select * from t;.....insert into t select * from t;

這時候檔案的大小為:

刪除id=1的資料,那麼就是刪除了1/3的資料:

mysql> delete from t where id=1;Query OK, 32768 rows affected (0.63 sec)

再看檔案大小,我們可以看到檔案大小還是96MB,這就形成了空洞.

我們用OPTIMIZE進行最佳化:

mysql> optimize table t;

此時我們再看檔案,已經變成了60MB,檔案大大的縮小了,說明‘空洞被收回了‘

檔案索引

使用合成索引來提高大文字欄位的查詢效能:

合成索引就是根據大文本的欄位的內容建立一個散列值,並且把這個值儲存在單獨的資料列中,接下來可以通過檢索散列值來找到資料.

但是,要注意這種技術只能用於精確匹配(對於< >=等範圍搜尋是沒有用的)

可以使用MD5()函數來產生散列值.

下面介紹一下合成索引的方法:

CREATE TABLE t (id VARCHAR(100),context BLOB,hash_value char(32));INSERT INTO t VALUES(1,repeat(‘beijing‘,2),MD5(context));INSERT INTO t VALUES(1,repeat(‘beijing2008‘,2),MD5(context));mysql> select * from t where hash_value=md5(repeat(‘beijing2008‘,2));+------+------------------------+----------------------------------+| id   | context                | hash_value                       |+------+------------------------+----------------------------------+| 1    | beijing2008beijing2008 | 0fe88accc8741a9d1bc323bd286866bb |+------+------------------------+----------------------------------+

由於這種技術只能用於精確匹配,從一定程度上減少了I/O,提高了查詢效率.如果需要對BLOB欄位進行模糊查詢,MYsql提供了首碼索引,也就是只為欄位的前n列建立索引

create index idx_blob on t(context(100));

mysql> desc select * from t where context like "beijing%" \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t         type: rangepossible_keys: idx_blob          key: idx_blob      key_len: 103          ref: NULL         rows: 2        Extra: Using where1 row in set (0.04 sec)

對context的前100個字元進行模糊查詢,就可以用首碼索引.

注意這裡的%不能放在前面,否則不能命中索引

避免使用select *

不要用select * 檢索大型的BLOB或者TEXT值

除非能夠確定約束條件where只會找到需要的資料,否則很可能毫無目的在網路上傳輸大量的值.

使用者可以用搜尋索引列,決定需要的那些資料行,然後從合格資料中檢索BLOB或者TEXT

分表

水平分表,在某些環境中,如果把這些大型的列資料移動到第二章表資料中,那麼把原資料表中的資料列轉換成固定長度的資料行格式,

那麼它就是有意義的.這會減少表中的片段,可以得到固定長度的效能優勢.

 

浮點數和定點數

在MYSQL中用float,double來標識浮點數.當一個欄位被定義浮點類型後,如果插入的資料精度超過該列定義的實際精度,那麼會採取四捨五入的辦法來得到實際的值.

定點數不同於浮點數,他是用字串的形式存存放的,所以插入的實際值精度大於實際定義的精度,如果在傳統模式下,會直接報錯,不能插入資料.

CREATE TABLE test(c1 float(10,2),c2 decimal(10,2));INSERT INTO text VALUES(131072.32,131072.32);mysql> select * from test;+-----------+-----------+| c1        | c2        |+-----------+-----------+| 131072.31 | 131072.32 |+-----------+-----------+

可以看到c1列的值從131072.32 變成了131072.31,這是上面的數值在使用單精確度浮點數表示時,產生了誤差.

注意:

• 浮點數存在誤差問題
• 對貨幣等對精度要求比較高的資料,應該用定點數表示或儲存
• 在變成中,如果用到浮點數,要特別注意誤差問題,盡量避免做浮點數比較

 

日期類型的選擇

• 根據實際需要選擇能夠滿足的最小儲存的日期類型,如果只需要記錄年,那麼用一個位元組來儲存的YEAR類型就而已滿足.而不需要用4個位元組的date,不僅節省空間的,還提高查詢效率
• 如果要記錄年月日時分秒,並記錄比較久遠,那麼最好使用datetime,不要使用timestamp
• 如果記錄的日期需要不同的時區的使用者使用,那麼最好使用timestamp,因為日期類型中,只有他能夠和實際時區相對應

 

mysql設計表結構資料類型的選擇