應(yīng)該盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型，更小的數(shù)據(jù)類型通常更快，因?yàn)樗鼈冋加酶俚拇疟P、內(nèi)存和CPU緩存，并且處理時(shí)需要的CPU周期更少。

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

看一個(gè)案例，下面是兩張字段相同，字段類型相同，只是 id 字段 emp1 是 smallint 類型， emp2 的 id 是 bigint 類型，分別向兩個(gè)表插入 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);

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

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

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

為了方便，寫個(gè) shell 腳本分別向兩個(gè)表插入 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í)行完畢，確認(rèn)兩個(gè)表都是 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)，兩個(gè)表占用的空間竟然不一樣，表 emp1 id字段類型 smallint(5) 插入 5000 條記錄后占用空間為 272K ，而 emp2 id字段類型 bigint(5) 插入同樣的數(shù)據(jù)后占用空間大小為 304K 。

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

簡(jiǎn)單就好

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

1、整型比字符操作代價(jià)更低，因?yàn)樽址托?duì)規(guī)則是字符比較比整型比較更復(fù)雜；
2、使用 MySQL 自建類型而不是字符串來存儲(chǔ)日期和時(shí)間；
3、用整型存儲(chǔ)IP地址。

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

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ù)準(zhǔn)備完畢后，我們來分別查詢一下這兩個(gè)表：

look，看到了，查詢兩個(gè)表的 SQL 語句執(zhí)行速度不一樣（樣本量可能還有點(diǎn)?。?/p>

盡量避免 null

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

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

一切以實(shí)際情況為準(zhǔn) 。

一些細(xì)則

整數(shù)類型

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

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

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

字符和字符串類型

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

使用最小的符合需求的長(zhǎng)度：

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

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

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

varchar應(yīng)用場(chǎng)景：

存儲(chǔ)長(zhǎng)度波動(dòng)較大的數(shù)據(jù)，如：文章，有的會(huì)很短有的會(huì)很長(zhǎng)；

字符串很少更新的場(chǎng)景，每次更新后都會(huì)重算并使用額外存儲(chǔ)空間保存長(zhǎng)度；

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

char：固定長(zhǎng)度的字符串

最大長(zhǎng)度：255；

會(huì)自動(dòng)刪除末尾的空格；

檢索效率、寫效率會(huì)比varchar高，以空間換時(shí)間。

char 使用場(chǎng)景：

存儲(chǔ)長(zhǎng)度波動(dòng)不大的數(shù)據(jù)，如：md5摘要；

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

BLOB 和 TEXT 類型

MySQL 把每個(gè) BLOB 和 TEXT值當(dāng)作一個(gè)獨(dú)立的對(duì)象處理。

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

日期時(shí)間

datetime

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

timestamp

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

date

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

使用枚舉代替字符串類型

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

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

曾經(jīng)我使用 varchar(15) 來存儲(chǔ) ip 地址，然而，ip 地址的本質(zhì)是 32 位無符號(hào)整數(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: