更新時(shí)間:2018-11-16 來(lái)源:黑馬程序員技術(shù)社區(qū) 瀏覽量:
最近,在工作中遇到了MySQL中如何存儲(chǔ)長(zhǎng)度較長(zhǎng)的字段類(lèi)型問(wèn)題,于是花了一周多的時(shí)間抽空學(xué)習(xí)了一下,并且記錄下來(lái)。
MySQL大致的邏輯存儲(chǔ)結(jié)構(gòu)在這篇文章中有介紹,做為基本概念:InnoDB 邏輯存儲(chǔ)結(jié)構(gòu)
注:文中所指的大數(shù)據(jù)指的是長(zhǎng)度較長(zhǎng)的數(shù)據(jù)字段,包括varchar/varbinay/text/blob。
Compact行格式我們首先來(lái)看一下行格式為Compact是如何存儲(chǔ)大數(shù)據(jù)的:
mysql> select version();+-----------+| version() |+-----------+| 5.1.73 |+-----------+1 row in set (0.01 sec)mysql> show table status like 'row'\G;*************************** 1. row *************************** Name: row Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 81920 Data_length: 81920Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-01-04 21:46:02 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)我們建立一張測(cè)試表,插入數(shù)據(jù):
CREATE TABLE `row` ( `content` varchar(65532) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=latin1mysql> insert into row(content) select repeat('a',65532);Query OK, 1 row affected (0.03 sec)Records: 1 Duplicates: 0 Warnings: 0我們使用py_innodb_page_info.py工具來(lái)查看表中的頁(yè)分布:
[root@localhost mysql]# python py_innodb_page_info.py -v com/row.ibd page offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>page offset 00000006, page type <Uncompressed BLOB Page>page offset 00000007, page type <Uncompressed BLOB Page>Total number of page: 8:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 4File Space Header: 1B-tree Node: 1File Segment inode: 1可以看出,第4頁(yè)的<B-tree Node>, page level <0000>格式為數(shù)據(jù)頁(yè),存放著MySQL的行數(shù)據(jù)。<Uncompressed BLOB Page>可以理解為MySQL存放大數(shù)據(jù)的地方,暫且叫作外部存儲(chǔ)頁(yè)。Compact格式?jīng)]有將大數(shù)據(jù)全部放在數(shù)據(jù)頁(yè)中,而是將一部分?jǐn)?shù)據(jù)放在了外部存儲(chǔ)頁(yè)中。那么,是全部數(shù)據(jù)在外部存儲(chǔ)頁(yè)中,還是一部分?jǐn)?shù)據(jù)。假如是一部分?jǐn)?shù)據(jù),這一部分是多少呢?
我們使用hexdump -Cv row.ibd查看一下數(shù)據(jù)頁(yè)<B-tree Node>, page level <0000>,也就是第4頁(yè):
3073 0000c000 8c 25 17 57 00 00 00 03 ff ff ff ff ff ff ff ff |.%.W....????????|3074 0000c010 00 00 00 00 00 07 3a b8 45 bf 00 00 00 00 00 00 |......:?E?......|3075 0000c020 00 00 00 00 00 02 00 02 03 a6 80 03 00 00 00 00 |.........?......|3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|3077 0000c040 00 00 00 00 00 00 00 00 00 13 00 00 00 02 00 00 |................|3078 0000c050 00 02 00 f2 00 00 00 02 00 00 00 02 00 32 01 00 |...?.........2..|3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|3080 0000c070 73 75 70 72 65 6d 75 6d 14 c3 00 00 10 ff f1 00 |supremum.?...??.|3081 0000c080 00 00 00 04 03 00 00 00 00 13 12 80 00 00 00 2d |...............-|3082 0000c090 01 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |..aaaaaaaaaaaaaa|3083 0000c0a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3084 0000c0b0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3085 0000c0c0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|........3128 0000c370 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3129 0000c380 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa...........&..|3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |....??..........|3132 0000c3b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3133 0000c3c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3134 0000c3d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|......4093 0000ffc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|4094 0000ffd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|4095 0000ffe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|4096 0000fff0 00 00 00 00 00 70 00 63 01 a1 6c 2b 00 07 3a b8 |.....p.c.?l+..:?|我們可以看出,數(shù)據(jù)頁(yè)中存儲(chǔ)了一部分?jǐn)?shù)據(jù),算下來(lái)一共是768字節(jié),然后剩余部分存儲(chǔ)在外部存儲(chǔ)頁(yè)中。那么數(shù)據(jù)頁(yè)與外部存儲(chǔ)頁(yè)、外部存儲(chǔ)頁(yè)與外部存儲(chǔ)頁(yè)是如何連接在一起的呢?
我們觀察這一行:
3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa...........&..|3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |................|這一行是前綴768字節(jié)的結(jié)尾。注意最后的20個(gè)字節(jié):
00 00 00 02:4字節(jié),代表外部存儲(chǔ)頁(yè)所在的space id00 00 00 04:4字節(jié),代表第一個(gè)外部頁(yè)的Page no00 00 00 26:4字節(jié),值為38,指向blob頁(yè)的header00 00 00 00 00 00 fc fc:8字節(jié),代表該列存在外部存儲(chǔ)頁(yè)的總長(zhǎng)度。此處的值為64764,加上前綴768正好是65532。(注意一點(diǎn),雖然表示BLOB長(zhǎng)度的是8字節(jié),實(shí)際只有4個(gè)字節(jié)能使用,所有對(duì)于BLOB字段,存儲(chǔ)數(shù)據(jù)的最大長(zhǎng)度為4GB。)驗(yàn)證下第一個(gè)外部存儲(chǔ)頁(yè)的頭部信息:
4097 00010000 cd c3 b6 8e 00 00 00 04 00 00 00 00 00 00 00 00 |??.............|4098 00010010 00 00 00 00 00 06 b8 a2 00 0a 00 00 00 00 00 00 |......??........|4099 00010020 00 00 00 00 00 02 00 00 3f ca 00 00 00 05 61 61 |........??....aa|4100 00010030 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|......前38個(gè)字節(jié)為File Header(關(guān)于InnoDB數(shù)據(jù)頁(yè)的詳細(xì)結(jié)構(gòu)請(qǐng)參見(jiàn)《MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎》4.4),這個(gè)簡(jiǎn)單提一下:
cd c3 b6 8e:4字節(jié),該頁(yè)的checksum。00 00 00 04:4字節(jié),頁(yè)偏移,此頁(yè)為表空間中的第5個(gè)頁(yè)。00 00 00 00:4字節(jié),當(dāng)前頁(yè)的上一個(gè)頁(yè)。此頁(yè)為<Uncompressed BLOB Page>,所以沒(méi)有上一頁(yè)。00 00 00 00:4字節(jié),當(dāng)前頁(yè)的下一個(gè)頁(yè)。此頁(yè)為<Uncompressed BLOB Page>,所以沒(méi)有下一頁(yè)。00 00 00 00 00 06 b8 a2:8字節(jié),該頁(yè)最后被修改的日志序列位置LSN。00 0a:2字節(jié),頁(yè)類(lèi)型,0x000A代表BLOB頁(yè)。00 00 00 00 00 00 00 00:8字節(jié),略過(guò)。00 00 00 02:頁(yè)屬于哪個(gè)表空間,此處指表空間的ID為2。之后是4字節(jié)的00 00 3f ca,這里的值為16330,代表此BLOB頁(yè)的有效數(shù)據(jù)的字節(jié)數(shù)。00 00 00 05代表下一個(gè)BLOB頁(yè)的page number。
我們看最后一個(gè)<Uncompressed BLOB Page>,第8個(gè)頁(yè):
7169 0001c000 fa 78 9b 27 00 00 00 07 00 00 00 00 00 00 00 00 |?x.'............|7170 0001c010 00 00 00 00 00 07 3a b8 00 0a 00 00 00 00 00 00 |......:?........|7171 0001c020 00 00 00 00 00 02 00 00 3d 9e ff ff ff ff 61 61 |........=.????aa|7172 0001c030 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|7173 0001c040 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|......最后一頁(yè)的有效數(shù)據(jù)大小為0x00003d9e=15774,768+16330*3+15774 = 65532字節(jié),符合初始插入數(shù)據(jù)的大小。
由于這是最后一個(gè)<Uncompressed BLOB Page>,所以指向下一個(gè)<Uncompressed BLOB Page>的指針為ff ff ff ff。
由此我們可以很清晰的看出數(shù)據(jù)頁(yè)與BLOB頁(yè)的連接關(guān)系(引用淘寶數(shù)據(jù)庫(kù)月報(bào)上的一張圖):
我們來(lái)再看一個(gè)比較有意思的例子。:
CREATE TABLE `testblob` ( `blob1` blob NOT NULL, `blob2` blob NOT NULL, `blob3` blob NOT NULL, `blob4` blob NOT NULL, `blob5` blob NOT NULL, `blob6` blob NOT NULL, `blob7` blob NOT NULL, `blob8` blob NOT NULL, `blob9` blob NOT NULL, `blob10` blob NOT NULL, `blob11` blob NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;mysql> insert into testblob select repeat('a',1000),repeat('b',1000),repeat('c',1000),repeat('d',1000),repeat('e',1000),repeat('f',1000),repeat('g',1000),repeat('h',1000),repeat('i',1000),repeat('j',1000),repeat('k',1000);ERROR 1030 (HY000): Got error 139 from storage engine我們建立一張新表,有11個(gè)blob字段。然后向每個(gè)字段插入1000字節(jié)的數(shù)據(jù),MySQL會(huì)提示ERROR 1030 (HY000): Got error 139 from storage engine,什么意思呢?
InnoDB是以B+樹(shù)來(lái)組織數(shù)據(jù)的,假如每一行數(shù)據(jù)都占據(jù)一整個(gè)Page頁(yè),那么B+樹(shù)將退化為單鏈表,所以InnoDB規(guī)定了一個(gè)Page必須包含兩行數(shù)據(jù)。也就是一行數(shù)據(jù)存儲(chǔ)在Page上的大小大概為8000字節(jié)。
而上面的例子,一行數(shù)據(jù)有11個(gè)1000字節(jié)的數(shù)據(jù),Page層肯定放不下,所以在Page層留下768*11=8448字節(jié),已經(jīng)超過(guò)了8000字節(jié),所以MySQL會(huì)提示ERROR 1030 (HY000): Got error 139 from storage engine。我們很輕松的定義一個(gè)字段,來(lái)存儲(chǔ)11000個(gè)字節(jié),但是卻無(wú)法將他們分成11個(gè)字段來(lái)存儲(chǔ),有點(diǎn)意思!
那么如何解決上面的問(wèn)題呢?
將行格式轉(zhuǎn)為接下來(lái)要說(shuō)的Dynamic格式。此種格式只用20字節(jié)指向外部存儲(chǔ)空間。將多個(gè)blob字段轉(zhuǎn)為一個(gè)blob字段。多個(gè)字段可以用數(shù)組存儲(chǔ),然后json_encode打包進(jìn)blob。我們向表中插入一條有效記錄:
mysql> insert into testblob(blob1,blob2,blob3,blob4,blob5,blob6,blob7,blob8,blob9) select repeat('a',8000),repeat('b',8000),repeat('c',8000),repeat('d',8000),repeat('e',8000),repeat('f',8000),repeat('g',8000),repeat('h',8000),repeat('i',8000);Query OK, 1 row affected (0.12 sec)Records: 1 Duplicates: 0 Warnings: 0[root@localhost mysql]# python py_innodb_page_info.py -v com/testblob.ibdpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>page offset 00000006, page type <Uncompressed BLOB Page>page offset 00000007, page type <Uncompressed BLOB Page>page offset 00000008, page type <Uncompressed BLOB Page>page offset 00000009, page type <Uncompressed BLOB Page>page offset 0000000a, page type <Uncompressed BLOB Page>page offset 0000000b, page type <Uncompressed BLOB Page>page offset 0000000c, page type <Uncompressed BLOB Page>Total number of page: 13:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 9File Space Header: 1B-tree Node: 1File Segment inode: 1我們可以看出這一行數(shù)據(jù)有9個(gè)外部存儲(chǔ)頁(yè),而我們一共就插入了9列數(shù)據(jù),是不是當(dāng)每一列的數(shù)據(jù)在page頁(yè)放不下,都單獨(dú)申請(qǐng)一個(gè)外部存儲(chǔ)頁(yè),而互相之前不共享外部存儲(chǔ)頁(yè)。我們看一下page頁(yè)的結(jié)構(gòu)就知道了:
3130 0000c390 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa| 3131 0000c3a0 61 61 61 61 00 00 00 05 00 00 00 04 00 00 00 26 |aaaa...........&|...... 3180 0000c6b0 62 62 62 62 62 62 62 62 00 00 00 05 00 00 00 05 |bbbbbbbb........| 3181 0000c6c0 00 00 00 26 00 00 00 00 00 00 1c 40 63 63 63 63 |...&.......@cccc|...... 3229 0000c9c0 63 63 63 63 63 63 63 63 63 63 63 63 00 00 00 05 |cccccccccccc....| 3230 0000c9d0 00 00 00 06 00 00 00 26 00 00 00 00 00 00 1c 40 |.......&.......@|......根據(jù)前面的分析,我們現(xiàn)在可以看出,外部存儲(chǔ)頁(yè)是不共享的,即使一個(gè)列的數(shù)據(jù)多出一個(gè)字節(jié),這一個(gè)字節(jié)也是獨(dú)占一個(gè)16KB空間的大小,這很浪費(fèi)存儲(chǔ)空間。(當(dāng)然,這對(duì)現(xiàn)代計(jì)算機(jī)可能不是問(wèn)題,呵呵)。
說(shuō)了這么多,總結(jié)下Compact格式存儲(chǔ)大數(shù)據(jù)的缺點(diǎn):
由于存在768字節(jié)的前綴在Page頁(yè),所以會(huì)存在能定義一個(gè)字段,存儲(chǔ)11000字節(jié),但是不能定義11個(gè)字段,每個(gè)字段存儲(chǔ)1000字節(jié)的"bug"。外部存儲(chǔ)頁(yè)不共享,即使多余一個(gè)字節(jié)也是獨(dú)享16KB的頁(yè)面。接著我們首先看一下行格式為Dynamic是如何存儲(chǔ)大數(shù)據(jù)的:
mysql> select version();+-----------+| version() |+-----------+| 5.7.14 |+-----------+1 row in set (0.00 sec)mysql> show table status like 'row'\G;*************************** 1. row *************************** Name: row Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-01-03 22:45:16 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment:1 row in set (0.00 sec)創(chuàng)建和compact格式一樣的表:
CREATE TABLE `row` ( `content` varchar(65532) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=latin1insert into row(content) select repeat('a',65532);Query OK, 1 row affected (0.03 sec)Records: 1 Duplicates: 0 Warnings: 0看下頁(yè)分布:
[root@localhost mysql]# python py_innodb_page_info.py -v row.ibd page offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>page offset 00000006, page type <Uncompressed BLOB Page>page offset 00000007, page type <Uncompressed BLOB Page>page offset 00000008, page type <Uncompressed BLOB Page>Total number of page: 9:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 5File Space Header: 1B-tree Node: 1File Segment inode: 1第4頁(yè)是數(shù)據(jù)頁(yè),第5-9頁(yè)是二進(jìn)制頁(yè)。我們直接看磁盤(pán)中第4頁(yè)的數(shù)據(jù):
3073 0000c000 dc 2d b0 f5 00 00 00 03 ff ff ff ff ff ff ff ff |.-..............|3074 0000c010 00 00 00 00 00 a3 4b 59 45 bf 00 00 00 00 00 00 |......KYE.......|3075 0000c020 00 00 00 00 00 36 00 02 00 a6 80 03 00 00 00 00 |.....6..........|3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|3077 0000c040 00 00 00 00 00 00 00 00 00 64 00 00 00 36 00 00 |.........d...6..|3078 0000c050 00 02 00 f2 00 00 00 36 00 00 00 02 00 32 01 00 |.......6.....2..|3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|3080 0000c070 73 75 70 72 65 6d 75 6d 14 c0 00 00 10 ff f1 00 |supremum........|3081 0000c080 00 00 00 02 00 00 00 00 00 07 07 a7 00 00 01 1b |................|3082 0000c090 01 10 00 00 00 36 00 00 00 04 00 00 00 26 00 00 |.....6.......&..|3083 0000c0a0 00 00 00 00 ff fc 00 00 00 00 00 00 00 00 00 00 |................|3084 0000c0b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3085 0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3086 0000c0d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3087 0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|.........和Compact格式有著明顯的不同,當(dāng)大數(shù)據(jù)在Page頁(yè)存放不下時(shí),Dynamic行格式不會(huì)留768字節(jié)在Page頁(yè),并且將全部大數(shù)據(jù)都放在外部存儲(chǔ)頁(yè)。具體的數(shù)據(jù)頁(yè)和外部存儲(chǔ)頁(yè)的連接關(guān)系同Compact格式一樣。
我們?cè)倏纯碊ynamic格式的外部存儲(chǔ)頁(yè)是不是每一個(gè)列獨(dú)享外部存儲(chǔ)空間,還是同Compact格式實(shí)驗(yàn)過(guò)程一樣:
CREATE TABLE `testblob` ( `blob1` blob NOT NULL, `blob2` blob NOT NULL, `blob3` blob NOT NULL, `blob4` blob NOT NULL, `blob5` blob NOT NULL, `blob6` blob NOT NULL, `blob7` blob NOT NULL, `blob8` blob NOT NULL, `blob9` blob NOT NULL, `blob10` blob NOT NULL, `blob11` blob NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;mysql> insert into testblob(blob1,blob2,blob3,blob4,blob5,blob6,blob7,blob8,blob9,blob10,blob11) select repeat('a',8000),repeat('b',8000),repeat('c',8000),repeat('d',8000),repeat('e',8000),repeat('f',8000),repeat('g',8000),repeat('h',8000),repeat('i',8000),repeat('j',8000),repeat('k',8000);Query OK, 1 row affected (0.10 sec)Records: 1 Duplicates: 0 Warnings: 0看一下外部存儲(chǔ)頁(yè)數(shù)據(jù):
4599 00011f60 61 61 61 61 61 61 61 61 61 61 61 61 61 61 00 00 |aaaaaaaaaaaaaa..| 4600 00011f70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|好的,可以不用向下看其他列的了,Dynamic的外部存儲(chǔ)頁(yè)也不是共享的。
但是MySQL為什么要這么設(shè)計(jì)呢?可能是為了實(shí)現(xiàn)簡(jiǎn)單吧,沿著鏈表通過(guò)有效數(shù)據(jù)大小就能讀取blob的全部數(shù)據(jù)。假如多個(gè)字段的blob混在一起,可能設(shè)計(jì)更復(fù)雜,要更新每個(gè)字段的偏移量之類(lèi)的,更新的話頁(yè)數(shù)據(jù)管理也比較麻煩。我的個(gè)人猜測(cè),呵呵。
總結(jié)下Dynamic格式存儲(chǔ)大數(shù)據(jù)的特點(diǎn):
當(dāng)數(shù)據(jù)頁(yè)放不下時(shí),MySQL會(huì)將大數(shù)據(jù)全部放在外部存儲(chǔ)頁(yè),數(shù)據(jù)頁(yè)只留指向外部存儲(chǔ)頁(yè)的指針。外部存儲(chǔ)頁(yè)不共享,即使多余一個(gè)字節(jié)也是獨(dú)享16KB的頁(yè)面。當(dāng)一行中的數(shù)據(jù)不能在數(shù)據(jù)頁(yè)中放下,需要申請(qǐng)外部存儲(chǔ)頁(yè)時(shí),MySQL需要決定將哪一列的數(shù)據(jù)放到外部存儲(chǔ)頁(yè),遵循的規(guī)則如下:
長(zhǎng)度固定的字段不會(huì)被放到外部存儲(chǔ)頁(yè)(int、char(N)等)長(zhǎng)度小于20字節(jié)的字段不會(huì)被放到外部存儲(chǔ)頁(yè)。(假如放到外部存儲(chǔ)頁(yè),不僅會(huì)單獨(dú)占據(jù)16KB,還要額外的20字節(jié)指針,沒(méi)有必要)對(duì)于Compact和REDUNDANT格式的行數(shù)據(jù),長(zhǎng)度小于768字節(jié)的字段不會(huì)被放到外部存儲(chǔ)頁(yè)。(這個(gè)原因很顯然,本來(lái)就不夠768字節(jié)的前綴,總不能生搬硬湊吧)。當(dāng)有多個(gè)大數(shù)據(jù)字段滿足上面條件,需要被放到外部存儲(chǔ)頁(yè)時(shí),MySQL會(huì)優(yōu)先選擇大的字段放到外部存儲(chǔ)頁(yè),因?yàn)檫@樣可以最大限度的省下數(shù)據(jù)頁(yè)的空間,使得更多的字段能夠被放到數(shù)據(jù)頁(yè)。
由于有較多的實(shí)驗(yàn)過(guò)程,所以顯得比較亂,建議看到這篇文章人自己實(shí)踐一遍,畢竟自己動(dòng)手會(huì)思考更多的問(wèn)題與細(xì)節(jié),理解的也比較深刻,哈哈哈。
作者:黑馬程序員大數(shù)據(jù)培訓(xùn)學(xué)院
首發(fā):http://web.itheima.com/
大數(shù)據(jù)離線階段Day9之HDFS安全模式
2018-10-24大數(shù)據(jù)離線階段Day9之HDFS元數(shù)據(jù)管理機(jī)制
2018-10-24大數(shù)據(jù)離線階段Day8之MapReduce優(yōu)化參數(shù)
2018-10-24大數(shù)據(jù)離線階段Day8之MapReduce并行度機(jī)制
2018-10-24云計(jì)算-基于KVM的H3C云計(jì)算平臺(tái)CAS的一些運(yùn)維經(jīng)驗(yàn)
2018-08-09搭建Python獨(dú)立環(huán)境:virtualenv的應(yīng)用
2018-07-27