mysql中innodb utf8字符集varchar索引長度問題

在mysql中innodb為uft8字符集時發現text,varchar這些字符索引會無效了，因類innodb utf8字符集下索引的長度限制單一字段的索引長度限制為767 bytes

索引總長度的限制是:

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)

在utf8字符集下，varchar(255) 的[數據部分]占用 255*3=765 bytes，最接近767bytes， 256*3 = 768bytes，已經超過767。

[BIGHD](root@localhost) [cm]> CREATE TABLE `temp_2` (
    ->   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `pn` VARCHAR(128) DEFAULT '',
    ->   `first_path` VARCHAR(256) DEFAULT '',
    ->   `dir` VARCHAR(255) DEFAULT '',
    -> `a` text,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ->
    -> ;
Query OK, 0 ROWS affected (0.15 sec)
 
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (first_path);
Query OK, 0 ROWS affected, 1 warning (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
[BIGHD](root@localhost) [cm]> SHOW warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes |
+---------+------+---------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (dir);
Query OK, 0 ROWS affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (a(767));
Query OK, 0 ROWS affected, 1 warning (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
[BIGHD](root@localhost) [cm]> SHOW warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes |
+---------+------+---------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
[BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2;
| temp_2 | CREATE TABLE `temp_2` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pn` VARCHAR(128) DEFAULT '',
  `first_path` VARCHAR(256) DEFAULT '',
  `dir` VARCHAR(255) DEFAULT '',
  `a` text,
  PRIMARY KEY (`id`),
  KEY `first_path` (`first_path`(255)),
  KEY `dir` (`dir`),
  KEY `a` (`a`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
所以key裡面a(255)，表示 255個字符(一個utf8字符占3字節)。

再看一下聯合索引的情況：

[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (pn, first_path);
Query OK, 0 ROWS affected, 1 warning (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]> SHOW warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes |
+---------+------+---------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
[BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2;
| temp_2 | CREATE TABLE `temp_2` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pn` VARCHAR(128) DEFAULT '',
  `first_path` VARCHAR(256) DEFAULT '',
  `dir` VARCHAR(255) DEFAULT '',
  `a` text,
  PRIMARY KEY (`id`),
  KEY `first_path` (`first_path`(255)),
  KEY `dir` (`dir`),
  KEY `a` (`a`(255)),
  KEY `pn` (`pn`,`first_path`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

即使是聯合索引，767的限制也是針對字段的，而不是聯合索引的總長度。

CREATE TABLE `temp_2` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `a` text,
  `b` text,
  `c` text,
  `d` text,
  `e` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
255 * 3 = 765 * 4 = 3060  + 4*3 = 3072
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(4));
Query OK, 0 ROWS affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(5));
ERROR 1071 (42000): Specified KEY was too long; MAX KEY LENGTH IS 3072 bytes