mysql中ROW_FORMAT的選擇問題

更新時(shí)間：2023年10月18日 15:00:34 作者：蠟津

這篇文章主要介紹了mysql中ROW_FORMAT的選擇問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

CHAR 與VARCHAR 之間的特點(diǎn)與選擇

CHAR和VARCHAR的區(qū)別如下：

1）、CHAR是固定長(zhǎng)度字符， VARCHAR是可變長(zhǎng)度字符;

2）、CHAR會(huì)自動(dòng)刪除插入數(shù)據(jù)的尾部空格， VARCHAR不會(huì)刪除尾部空格。

CHAR是固定長(zhǎng)度，所以它的處理速度比VARCHAR的速度要快，但是它的缺點(diǎn)就是浪費(fèi)存儲(chǔ)空間。所以對(duì)存儲(chǔ)不大，但在速度上有要求的可以使用CHAR類型，反之可以使用VARCHAR類型來實(shí)現(xiàn)。

存儲(chǔ)引擎對(duì)于選擇CHAR和VARCHAR的影響

對(duì)于MyISAM存儲(chǔ)引擎：最好使用固定長(zhǎng)度的數(shù)據(jù)列代替可變長(zhǎng)度的數(shù)據(jù)列。這樣可以使整個(gè)表靜態(tài)化，從而使數(shù)據(jù)檢索更快，用空間換時(shí)間。

對(duì)于InnoDB存儲(chǔ)引擎：使用可變長(zhǎng)度的數(shù)據(jù)列，因?yàn)镮nnoDB數(shù)據(jù)表的存儲(chǔ)格式不分固定長(zhǎng)度和可變長(zhǎng)度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照實(shí)際的長(zhǎng)度存儲(chǔ)，比較節(jié)省空間，所以對(duì)磁盤I/O和數(shù)據(jù)存儲(chǔ)總量比較好。

在MySQL中，所謂Row_Format行格式是指數(shù)據(jù)記錄(或者稱之為行)在磁盤中的物理存儲(chǔ)方式。

MyISAM行存儲(chǔ)

MyISAM有3種行存儲(chǔ)格式：fixed/dynamic/compressed　　

1、fixed：為默認(rèn)格式，只有當(dāng)表不包含變長(zhǎng)字段(varchar/varbinary/blob/text)時(shí)使用，該每行都是固定的，所以很容易獲取行在頁(yè)上的具體位置，存取效率比較高，但是占用磁盤空間較多

InnoDB行存儲(chǔ)

Innodb plugin新引入Barracuda，其包含compressed/dynamic兩種行格式，而之前的compact/redundant統(tǒng)屬于antelope；

目前可選值為Antelope和Barracuda，低版本默認(rèn)為Antelope，高版本默認(rèn)為Barracuda。

1. Antelope：它支持兩種行格式：COMPACT 和 REDUNDANT。MySQL5.6的默認(rèn)文件格式?？梢耘c早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。

2. Barracuda：新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC。

在 msyql 5.7.9 及以后版本，默認(rèn)行格式由innodb_default_row_format變量決定，它的默認(rèn)值是DYNAMIC，也可以在 create table 的時(shí)候指定ROW_FORMAT=DYNAMIC。用戶可以通過命令 SHOW TABLE STATUS LIKE'table_name' 來查看當(dāng)前表使用的行格式，其中 row_format 列表示當(dāng)前所使用的行記錄結(jié)構(gòu)類型。

如果要修改現(xiàn)有表的行模式為compressed或dynamic，必須先將文件格式設(shè)置成Barracuda：set global innodb_file_format=Barracuda;，再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效。

InnoDB存儲(chǔ)引擎Compact、Redundant、Dynamic、Compressed主要區(qū)別

1）、compact、Dynamic、Compressed 行格式下，字段為NULL值是不會(huì)在記錄的數(shù)據(jù)內(nèi)容中占用存儲(chǔ)空間，冗余存儲(chǔ)的；

2）、redundant行格式下，如果值為NULL的字段類型為變長(zhǎng)數(shù)據(jù)類型，不會(huì)在記錄的數(shù)據(jù)內(nèi)容中占用任何空間來進(jìn)行存儲(chǔ)的，

如果值為NULL的字段類型為定長(zhǎng)數(shù)據(jù)類型，其使用0x00來填充該字段所需占用的空間。

例如char(10)類型的字段，在ascii、utf8字符集中其Maxlen值分別為1、3。即該字段在數(shù)據(jù)內(nèi)容部分會(huì)分別占用的10、30個(gè)字節(jié)。故當(dāng)該字段為NULL值時(shí)，會(huì)使用0x00來填充這10、30個(gè)字節(jié)的位置。

行溢出

compact、redundant行格式中，如果該記錄某字段中數(shù)據(jù)量過多時(shí)，則在該記錄的數(shù)據(jù)內(nèi)容的相應(yīng)字段處只存儲(chǔ)該字段值前768個(gè)字節(jié)的數(shù)據(jù)和一個(gè)指向存儲(chǔ)剩余數(shù)據(jù)的其他頁(yè)（即所謂的溢出頁(yè)）的地址，該地址通常占用20個(gè)字節(jié)。

Dynamic、Compressed行格式中，會(huì)把記錄中數(shù)據(jù)量過大的字段值全部存儲(chǔ)到溢出頁(yè)中，而不會(huì)在該記錄的數(shù)據(jù)內(nèi)容的相應(yīng)字段處存儲(chǔ)該字段值前768個(gè)字節(jié)的數(shù)據(jù)了。

而compressed相比較dynamic行格式來說，前者會(huì)使用壓縮算法對(duì)所有頁(yè)面(自然也包括溢出頁(yè))進(jìn)行壓縮以減少存儲(chǔ)占用。

遇到過這樣一個(gè)問題，原因是InnoDB不支持ROW_FORMAT為FIXED。

mysql>  create table tmp11(id varchar(10)) ROW_FORMAT=FIXED;
ERROR 1031 (HY000)： Table storage engine for 'tmp11' doesn't have this option
mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)