不超過范圍的情況下，數(shù)據(jù)類型越小越好

應(yīng)該盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型，更小的數(shù)據(jù)類型通常更快，因為它們占用更少的磁盤、內(nèi)存和CPU緩存，并且處理時需要的CPU周期更少。

但是要確保選擇的存儲類型范圍足夠用，如果無法確認哪個數(shù)據(jù)類型，就選擇你認為不會超過范圍的最小類型。

看一個案例，下面是兩張字段相同，字段類型相同，只是 id 字段 emp1 是 smallint 類型， emp2 的 id 是 bigint 類型，分別向兩個表插入 5000 條記錄，觀察一下表容量大小。

CREATE TABLE `mytest`.`emp1`  (  `id` smallint(5) NULL,  `name` varchar(255) NULL);
CREATE TABLE `mytest`.`emp2`  (  `id` bigint(5) NULL,  `name` varchar(255) NULL);

兩個表的初始大小是一致的，都是 96K ：

PS：可以用如下命令查看數(shù)據(jù)文件的存放位置：

> mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

為了方便，寫個 shell 腳本分別向兩個表插入 5000 條記錄：

#!/bin/bash
i=1
while [ $i -le 5000 ]
do
    mysql -uroot -p123456 mytest -e "insert into emp2 (id,name) values ($i,'n$i');"
    i=$(($i+1))
done

注意表名，emp1 和 emp2 分別執(zhí)行一遍。

執(zhí)行完畢，確認兩個表都是 5000 條記錄：

mysql> select count(*) from emp1;
+----------+
| count(*) |
+----------+
|    5000 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from emp2;
+----------+
| count(*) |
+----------+
|    5000 |
+----------+
1 row in set (0.01 sec)

來，見證一下奇跡先：

[root@node1 mytest]# ll -h | grep emp1.ibd && ll -h | grep emp2.ibd
-rw-r-----. 1 mysql mysql 272K 8月   9 09:33 emp1.ibd
-rw-r-----. 1 mysql mysql 304K 8月   9 09:37 emp2.ibd

可以發(fā)現(xiàn)，兩個表占用的空間竟然不一樣，表 emp1 id字段類型 smallint(5) 插入 5000 條記錄后占用空間為 272K ，而 emp2 id字段類型 bigint(5) 插入同樣的數(shù)據(jù)后占用空間大小為 304K 。

這就是所謂 不超過范圍的情況下，數(shù)據(jù)類型越小越好 。

簡單就好

簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期

• 1、整型比字符操作代價更低，因為字符集和校對規(guī)則是字符比較比整型比較更復(fù)雜；
• 2、使用 MySQL 自建類型而不是字符串來存儲日期和時間；
• 3、用整型存儲IP地址。

我們拿日期數(shù)據(jù)類型來舉個例子，同樣建兩張表：

CREATE TABLE `tab1` (
  `id` smallint(5) NULL,
  `name` varchar(255) NULL,
  `ctime` date NULL
);

CREATE TABLE `tab2` (
  `id` smallint(5) NULL,
  `name` varchar(255) NULL,
  `ctime` datetime NULL
);

tab1 的 ctime 字段類型為 date ，tab2 的 ctime 字段類型為 datetime ，同樣，執(zhí)行 shell 腳本，插入 20000 條記錄：

#!/bin/bash
i=1
while [ $i -le 20000 ]
do
    mysql -uroot -p123456 test -e "insert into tab1 (id,name,ctime) values ($i,'n$i',now());"
    i=$(($i+1))
done

改下腳本，再向表 tab2 插入 20000 條記錄。

數(shù)據(jù)準備完畢后，我們來分別查詢一下這兩個表：

look，看到了，查詢兩個表的 SQL 語句執(zhí)行速度不一樣（樣本量可能還有點小）！

盡量避免 null

如果查詢中包含可為 NULL 的列，對 MySQL 來說很難優(yōu)化，因為可為 null 的列使得 索引 、 索引統(tǒng)計 和 值比較 都更加復(fù)雜。

通常情況下 null 的列改為 not null 帶來的性能提升比較小，所有沒有必要將所有的表的 schema 進行修改，但是應(yīng)該盡量避免設(shè)計成可為 null 的列。

一切以實際情況為準 。

一些細則

整數(shù)類型

可以使用的幾種整數(shù)類型：

• TINYINT 8 bit，
• SMALLINT 16 bit，
• MEDIUMINT 24 bit，
• INT 32 bit，
• BIGINT 64 bit

盡量使用滿足需求的最小數(shù)據(jù)類型。前文有述。

字符和字符串類型

varchar ：根據(jù)實際內(nèi)容長度保存數(shù)據(jù)。

使用最小的符合需求的長度：

varchar(n) ：n小于等于255使用額外一個字節(jié)保存長度，n>255使用額外兩個字節(jié)保存長度。

varchar(5) 與 varchar(255) 保存同樣的內(nèi)容，硬盤存儲空間相同，但內(nèi)存空間占用不同，是指定的大小 。

varchar在 MySQL 5.6 之前變更長度，或者從255一下變更到255以上時，都會導(dǎo)致 鎖表 。

varchar應(yīng)用場景：

存儲長度波動較大的數(shù)據(jù)，如：文章，有的會很短有的會很長；

字符串很少更新的場景，每次更新后都會重算并使用額外存儲空間保存長度；

適合保存多字節(jié)字符，如：漢字，特殊字符等。

char：固定長度的字符串

最大長度：255；

會自動刪除末尾的空格；

檢索效率、寫效率 會比varchar高，以空間換時間。

char 使用場景：

存儲長度波動不大的數(shù)據(jù)，如：md5摘要；

存儲短字符串、經(jīng)常更新的字符串。

BLOB 和 TEXT 類型

MySQL 把每個 BLOB 和 TEXT值當作一個獨立的對象處理。

兩者都是為了存儲很大數(shù)據(jù)而設(shè)計的字符串類型，分別采用二進制和字符方式存儲。

日期時間

datetime

• 占用8個字節(jié)；
• 與時區(qū)無關(guān)，數(shù)據(jù)庫底層時區(qū)配置，對 datetime 無效；
• 可保存到毫秒；
• 可保存時間范圍大；
• 不要使用字符串存儲日期類型，占用空間大，損失日期類型函數(shù)的便捷性。

timestamp

• 占用4個字節(jié)；
• 時間范圍：1970-01-01到2038-01-19；
• 精確到秒；
• 采用整形存儲；
• 依賴數(shù)據(jù)庫設(shè)置的時區(qū)；
• 自動更新timestamp列的值。

date

• 占用的字節(jié)數(shù)比使用字符串、datetime、int存儲要少，使用date類型只需要3個字節(jié)；
• 使用date類型還可以利用日期時間函數(shù)進行日期之間的計算；
• date類型用于保存1000-01-01到9999-12-31之間的日期。

使用枚舉代替字符串類型

有時可以使用 枚舉 類型代替常用的字符串類型，MySQL 存儲枚舉類型會非常緊湊，會根據(jù)列表值的數(shù)據(jù)壓縮到一個或兩個字節(jié)中，MySQL 在內(nèi)部會將每個值在列表中的位置保存為整數(shù)，并且在表的 .frm 文件中保存“數(shù)字-字符串”映射關(guān)系的查找表。

特殊類型數(shù)據(jù)

曾經(jīng)我使用 varchar(15) 來存儲 ip 地址，然而，ip 地址的本質(zhì)是 32 位無符號整數(shù)不是字符串，可以使用 INET_ATON 和 INET_NTOA 函數(shù)在這兩種表示方法之間轉(zhuǎn)換。

比如：

mysql> select inet_aton('192.168.134.119');
+------------------------------+
| inet_aton('192.168.134.119') |
+------------------------------+
|                   3232269943 |
+------------------------------+
1 row in set (0.03 sec)

mysql> select inet_ntoa('3232269943');
+-------------------------+
| inet_ntoa('3232269943') |
+-------------------------+
| 192.168.134.119         |
+-------------------------+
1 row in set (0.03 sec)

到此這篇關(guān)于原來MySQL 數(shù)據(jù)類型也可以優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL 數(shù)據(jù)類型 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論