mysql索引長度tips innodb和myisam引擎
由於開發人員對索引認識不深或忽略,還有版本不同等問題,在生產環境中創建表失敗,引發了一些問題。歸納了一下
測試環境
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.31-log |
+------------+
1 row in set (0.01 sec)
innodb 引擎
mysql> CREATE TABLE `meta_topic_scan` ( `domain` varchar(257) NOT NULL, `topic_name` varchar(200) NOT NULL, `topic_url` varchar(200) NOT NULL, `topic_pv` int(11) DEFAULT'0', `topic_uv` int(11) DEFAULT '0', PRIMARY KEY (`domain`,`topic_url`) ) ENGINE=innodb DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
innodb 單列索引長度不能超過767 bytes,聯合索引限制是3072 bytes 。對於創建innodb的組合索引中,如果各個列中的長度有單個超過767 bytes,也會創建失敗;
myisam 引擎
創建復合索引:
mysql> CREATE TABLE `meta_topic_scan` (
-> `domain` varchar(200) NOT NULL,
-> `topic_name` varchar(200) NOT NULL,
-> `topic_url` varchar(200) NOT NULL,
-> `topic_pv` int(11) DEFAULT '0',
-> `topic_uv` int(11) DEFAULT '0',
-> PRIMARY KEY (`domain`,`topic_name`,`topic_url`)
-> ) ENGINE=myisam DEFAULT CHARSET=utf8 ;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
創建單列索引:
mysql> CREATE TABLE `meta_topic_scan` (
-> `domain` varchar(334) NOT NULL,
-> `topic_name` varchar(200) NOT NULL,
-> `topic_url` varchar(200) NOT NULL,
-> `topic_pv` int(11) DEFAULT '0',
-> `topic_uv` int(11) DEFAULT '0',
-> PRIMARY KEY (`domain`)
-> ) ENGINE=myisam DEFAULT CHARSET=utf8 ;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
由此可知:myisam 單列索引長度、所創建的復合索引長度和都不能超過1000 bytes,否則會報錯,創建失敗。
另外不同字符集占用不同字節:latin一個字符占1 bytes,utf8存儲一個字符占3 bytes, gbk存儲一個字符2 bytes
擴展: innodb復合索引長度為什麼是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。
如下效果(5.5):