ç®å½
Â
4.1 ç´¢å¼ç»ç»è¡¨
4.2 InnoDBé»è¾åå¨ç»æ
4.2.1 表空é´
4.2.2 段
4.2.3 åº
4.2.4 页
4.2.5 è¡
4.3 è¡è®°å½æ ¼å¼
4.3.1 Compactè¡è®°å½æ ¼å¼
4.3.2 è¡æº¢åºæ°æ®
4.3.4 CHARçè¡ç»æåå¨
4.4 InnoDBæ°æ®é¡µç»æ
4.4.1 InfimumåSupremum Record
4.4.2 Page Directory
4.1 ç´¢å¼ç»ç»è¡¨
ç´¢å¼ç»ç»è¡¨ï¼è¡¨é½æ¯æ ¹æ®ä¸»é®é¡ºåºç»ç»åæ¾çã
æ¯å¼ 表é½æä¸ä¸ªä¸»é®ï¼å¦æå建表æ¶æ²¡ææ¾å¼å°å®ä¹ä¸»é®ï¼InnoDBé¦å ä¼å¤ææ¯å¦æé空çå¯ä¸ç´¢å¼ï¼æï¼å该å为主é®ï¼å¦åèªå¨å建ä¸ä¸ª6åè大å°çæé_rowidã
4.2 InnoDBé»è¾åå¨ç»æ
æææ°æ®é½è¢«é»è¾å°åæ¾å¨ä¸ä¸ªç©ºé´ï¼tablespaceï¼ä¸ï¼ç§°ä¹ä¸ºè¡¨ç©ºé´ã表空é´åç±æ®µï¼segmentï¼ãåºï¼extentï¼ã页ï¼pageï¼ç»æã
4.2.1 表空é´
æææ°æ®é½åæ¾å¨è¡¨ç©ºé´ä¸ï¼é»è®¤ibdata1ï¼å¦æå¯ç¨innodb_file_per_tableï¼åæ¯ä¸ªè¡¨å çæ°æ®å¯ä»¥åç¬æ¾å°ä¸ä¸ªè¡¨ç©ºé´ï¼.ibdï¼å ã
注æï¼å¦æå¯ç¨äºinnodb_file_per_tableï¼æ¯å¼ 表ç表空é´å åæ¾çåªæ¯çæ°æ®ãç´¢å¼åæå ¥ç¼å²BITMAP页ï¼å ¶ä»ç±»çä¿¡æ¯ï¼å¦åæ»æ¥å¿ãæå ¥ç¼å²ãç´¢å¼é¡µãç³»ç»äºå¡ä¿¡æ¯ãäºæ¬¡ç¼å²åçè¿æ¯åæ¾å¨åæ¥çå ±äº«è¡¨ç©ºé´å ã
4.2.2 段
常è§ç段ææ°æ®æ®µãç´¢å¼æ®µãåæ»æ®µçã
InnoDBåå¨å¼æ表æ¯ç´¢å¼ç»ç»çï¼å æ¤æ°æ®å³ç´¢å¼ãç´¢å¼å³æ°æ®ãæ°æ®æ®µå³ä¸ºB+æ çå¶åèç¹ï¼ç´¢å¼å³ä¸ºB+æ çéç´¢å¼èç¹ãåæ»æ®µæ¯è¾ç¹æ®ï¼åé¢åç¬ä»ç»ã
4.2.3 åº
ç±è¿ç»é¡µç»æç空é´ï¼å¨ä»»ä½æ åµä¸æ¯ä¸ªåºç大å°é½ä¸º1MBã为äºä¿è¯åºä¸é¡µçè¿ç»æ§ï¼InnoDB åå¨å¼æä¸æ¬¡ä»ç£çç³è¯·4~5个åºãå¨é»è®¤æ åµä¸ï¼InnoDBåå¨å¼æ页ç大å°ä¸º16KBï¼å³ä¸ä¸ªåºä¸ä¸å ±æ64个è¿ç»ç页ã
å¯ç¨innodb_file_per_tableåï¼å 使ç¨32个页大å°çç¢ç页æ¥åæ¾æ°æ®ï¼å¨ä½¿ç¨å®è¿äºé¡µä¹åææ¯64个è¿ç»é¡µçç³è¯·ãç®çï¼å¨å¼å§æ¶ç³è¯·è¾å°ç空é´ï¼è约ç£ç容éçå¼éã
4.2.4 页
ç£ç管ççæå°åä½ï¼é»è®¤16KBã
常è§ç页类åæï¼
 â æ°æ®é¡µ
 â undo页
 â ç³»ç»é¡µ
 â äºå¡æ°æ®é¡µ
 â æå ¥ç¼å²ä½å¾é¡µ
 â æå ¥ç¼å空é²å表页
 â æªå缩çäºè¿å¶å¤§å¯¹è±¡é¡µ
 â å缩çäºè¿å¶å¤§å¯¹è±¡é¡µ
4.2.5 è¡
InnoDBåå¨å¼ææ¯é¢åè¡çï¼æ°æ®æ¯æç §è¡è¿è¡åæ¾çãæ¯ä¸ªé¡µæå¤åæ¾
16KB/2-200=7992
è¡è®°å½ã
4.3 è¡è®°å½æ ¼å¼
éè¿
SHOW TABLE STATUS like 'table_name'
æ¥æ¥çå½å表使ç¨çè¡æ ¼å¼ã
è¡è®°å½æ ¼å¼æï¼CompactãRedundantãCompressãDynamicã
mysql> show table status like 'user'\G;
************************* 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 30
Avg_row_length: 546
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1037
Create_time: 2019-09-11 04:27:05
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: row_format=COMPACT
Comment:
1 row in set (0.00 sec)
4.3.1 Compactè¡è®°å½æ ¼å¼
MySQL 5.0å¼å ¥ï¼è®¾è®¡ç®æ æ¯é«æå°åå¨æ°æ®ãä¸ä¸ªé¡µåæ¾çè¡æ°æ®è¶å¤ï¼å ¶æ§è½å°±è¶1é«ã
1. åæå段é¿åº¦å表
éNULLï¼è®°å½å段çå®é é¿åº¦ï¼æç §åç顺åºéåºæ¾ç½®ï¼è¥åçé¿åº¦å°äº255åèï¼ç¨1åè表示ï¼è¥å¤§äº255个åèï¼ç¨2åè表示ãåæå段çé¿åº¦æ大ä¸è½è¶ è¿2åèï¼å 为VARCHARç±»åçæ大é¿åº¦éå¶ä¸º65535ã
2. NULLæ å¿ä½
æ è¯è¡æ°æ®æ¯å¦æNULLå¼ï¼æåç¨1表示
3. è®°å½å¤´ä¿¡æ¯
4. åæ°æ®
å®é åå¨æ¯ä¸ªåçæ°æ®ã
注æï¼NULLä¸å 该é¨åä»»ä½ç©ºé´ï¼å³NULLé¤äºå æNULLæ å¿ä½ï¼å®é åå¨ä¸å ç¨ä»»ä½ç©ºé´ã
5. éèå
äºå¡IDåååæ»æéï¼å¦æ没æå®ä¹ä¸»é®ï¼æ¯è¡è¿ä¼å¢å ä¸ä¸ª6åèçrowidåã
æ¡ä¾ï¼
1. æ°å»ºè¡¨å¹¶ä¸å表ä¸æå ¥æ°æ®
2. åæç»æ
4.3.2 è¡æº¢åºæ°æ®
å°ä¸æ¡è®°å½ä¸çæäºæ°æ®åå¨å¨çæ£çæ°æ®é¡µé¢ä¹å¤ã
VARCHARæå¤å¯ä»¥åå¨65535åèï¼ä½æ¯ççå¯ä»¥åï¼
mysql> create table test(
-> a varchar(65535)
-> )charset=latin1 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
说ææå ¶ä»å¼éï¼ä¸»é®ãäºå¡idãåæ»æéï¼ï¼å®é è½åæ¾65532ã
å¦æ设置å符类å为UFT-8åGBKï¼ç»æå¦ä½ï¼
è¡æº¢åºæ°æ®çåå¨ï¼æ°æ®é¡µä¿åå768åèçåç¼æ°æ®ï¼ä¹åæ¯å移éï¼æåè¡æº¢åºé¡µã
InnoDB 1.0.xçæ¬å¼å§å¼å ¥äºæ°çæä»¶æ ¼å¼ï¼CompressedåDynamicï¼éç¨å®å ¨çè¡æº¢åºæ¹å¼ï¼å¨æ°æ®é¡µåªåæ¾20个åèçæéï¼å®é åæ¾çæ°æ®é½å¨Off Pageä¸ã
4.3.4 CHARçè¡ç»æåå¨
å¨å¤åèå¤å符çæ åµä¸ï¼CHARåVARCHARçå®é è¡åå¨åºæ¬æ¯æ²¡æåºå«çã
4.4 InnoDBæ°æ®é¡µç»æ
ç±ä¸ä¸ªé¨åç»æï¼å¦ä¸ï¼
å称 | åèæ° | ä½ç¨ |
---|---|---|
File Headerï¼æä»¶å¤´ï¼ | 38 | è®°å½é¡µçä¸äºå¤´ä¿¡æ¯ |
Page Headerï¼é¡µå¤´ï¼ | 56 | è®°å½æ°æ®é¡µçç¶æä¿¡æ¯ |
InfimumåSupremum Record | -- | èæçè¡è®°å½ï¼ç¨æ¥éå®è®°å½çè¾¹ç |
User Recordï¼ç¨æ·è®°å½ï¼å³è¡è®°å½ï¼ | -- | å®é åå¨è¡è®°å½å 容 |
Free Spaceï¼ç©ºé²ç©ºé´ï¼ | -- | ä¸æ¡è®°å½è¢«å é¤åï¼è¯¥ç©ºé´ä¼è¢«å å ¥å°ç©ºé²åè¡¨ä¸ |
Page Directoryï¼é¡µç®å½ï¼ | -- | åæ¾è®°å½çç¸å¯¹å°å |
File Trailerï¼æ件ç»å°¾ä¿¡æ¯ï¼ | 8 | æ£æµé¡µæ¯å¦å·²ç»å®æ´å°åå ¥ç£ç |
4.4.1 InfimumåSupremum Record
èæçè¡è®°å½ï¼ç¨æ¥éå®è®°å½çè¾¹çã
Infimumè®°å½æ¯æ¯è¯¥é¡µä¸ä»»ä½ä¸»é®å¼é½è¦å°çå¼ï¼Supremumææ¯ä»»ä½å¯è½å¤§çå¼è¿è¦å¤§çå¼ãè¿ä¸¤ä¸ªå¼å¨é¡µå建æ¶è¢«å»ºç«ï¼å¹¶ä¸å¨ä»»ä½æ åµä¸ä¸ä¼è¢«å é¤ã
4.4.2 Page Directory
åæ¾è®°å½çç¸å¯¹ä½ç½®ï¼æ³¨æï¼è¿éåæ¾çæ¯é¡µç¸å¯¹ä½ç½®ï¼èä¸æ¯å移éï¼ï¼ææ¶åè¿äºè®°å½æé称为Slots(槽)æè ç®å½æ§½(Directory Slots)ãä¸ä¸ªæ§½ä¸å¯è½æå¤ä¸ªè®°å½ãç±äºPage Directoryæ¯ç¨çç®å½ï¼äºåæ¥æ¾çç»æåªæ¯ä¸ä¸ªç²ç¥çç»æï¼å æ¤å¿ é¡»éè¿record headerä¸çnext_recordæ¥ç»§ç»æ¥æ¾ç¸å ³è®°å½ã
å¦ä¸å¾ï¼15ï¼18ç»æä¸ä¸ªæ§½ï¼æ¾å°æ§½ä¹åï¼è¿éè¦éè¿æéæ¾å°è®°å½ã