大家åºè¯¥ç¥éInnoDBååç´¢å¼é¿åº¦ä¸è½è¶
è¿767bytesï¼èåç´¢å¼è¿æä¸ä¸ªéå¶æ¯é¿åº¦ä¸è½è¶
è¿3072ã
mysql> CREATE TABLE `tb` (
-> `a` varchar(255) DEFAULT NULL,
-> `b` varchar(255) DEFAULT NULL,
-> `c` varchar(255) DEFAULT NULL,
-> `d` varchar(255) DEFAULT NULL,
-> `e` varchar(255) DEFAULT NULL,
-> KEY `a` (`a`,`b`,`c`,`d`,`e`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
å¯ä»¥çå°ï¼ç±äºæ¯ä¸ªå段å ç¨255*3, å æ¤è¿ä¸ªç´¢å¼ç大å°æ¯3825>3072ï¼æ¥éã
为ä»ä¹3072
æ们ç¥éInnoDBä¸ä¸ªpageçé»è®¤å¤§å°æ¯16kãç±äºæ¯Btreeç»ç»ï¼è¦æ±å¶åèç¹ä¸ä¸ä¸ªpageè³å°è¦å
å«ä¸¤æ¡è®°å½ï¼å¦åå°±éåé¾è¡¨äºï¼ã
æ以ä¸ä¸ªè®°å½æå¤ä¸è½è¶
è¿8kã
åç±äºInnoDBçèç°ç´¢å¼ç»æï¼ä¸ä¸ªäºçº§ç´¢å¼è¦å
å«ä¸»é®ç´¢å¼ï¼å æ¤æ¯ä¸ªå个索å¼ä¸è½è¶
è¿4k ï¼æ端æ
åµï¼pkåæ个äºçº§ç´¢å¼é½è¾¾å°è¿ä¸ªéå¶ï¼ã
ç±äºéè¦é¢çåè¾
å©ç©ºé´ï¼æ£æåä¸è½è¶
è¿3500ï¼å个âæ´æ°âå°±æ¯(1024*3)ã
ååç´¢å¼éå¶
ä¸é¢ææå°ååç´¢å¼éå¶767ï¼èµ·å æ¯256Ã3-1ãè¿ä¸ª3æ¯å符æ大å ç¨ç©ºé´ï¼utf8ï¼ãä½æ¯å¨5.5以åï¼å¼å§æ¯æ4个åèçuutf8ã255Ã4>767, äºæ¯å¢å äºä¸ä¸ªåæ°å«å innodb_large_prefixã
è¿ä¸ªåæ°é»è®¤å¼æ¯OFFãå½æ¹ä¸ºONæ¶ï¼å
许åç´¢å¼æ大达å°3072ã
å¯ä»¥çå°é»è®¤è¡ä¸ºæ¯å»ºè¡¨æåï¼æ¥ä¸ä¸ªwarningï¼å¹¶ä¸å°é¿åº¦é¶æ®µä¸º255ã
注æè¦çæéè¦å row_format=compressedæè
dynamic ã
å¦æç¡®å®éè¦å¨å个å¾å¤§çåä¸å建索å¼ï¼æè
éè¦å¨å¤ä¸ªå¾å¤§çåä¸å建èåç´¢å¼ï¼èåè¶
è¿äºç´¢å¼çé¿åº¦éå¶ï¼è§£å³åæ³æ¯å¨å»ºç´¢å¼æ¶éå¶ç´¢å¼prefixç大å°ï¼
ä¾å¦ï¼create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50));
è¿æ ·ï¼å¨å建索å¼æ¶å°±ä¼éå¶ä½¿ç¨çæ¯ä¸ªåçæ大é¿åº¦ãå¦ä¸çä¾åä¸ï¼å¨å建èåç´¢å¼æ¶ï¼æå¤ä½¿ç¨åflow_exec_idä¸å100个å符å建索å¼ï¼æå¤ä½¿ç¨another_columnä¸å
50个å符å建索å¼ãè¿æ ·åï¼å°±å¯ä»¥é¿å
ç´¢å¼é¿åº¦è¿å¤§çé®é¢ã
温馨提示:答案为网友推荐,仅供参考