一闽巩、myisam存儲(chǔ)引擎
1 . 測(cè)試的表結(jié)構(gòu)信息
mysql> show create table tb2
Table: tb2
Create Table: CREATE TABLE `tb2` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
- 測(cè)試加索引
（1）添加單列索引钧舌，能夠添加成功（報(bào)出warning），但實(shí)際添加的是前綴索引涎跨。
mysql> alter table tb2 add index idx1 (d1);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
2 rows in set (0.00 sec)
表結(jié)構(gòu)信息：
mysql> show create table tb2
Table: tb2
Create Table: CREATE TABLE `tb2` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL,
KEY `idx1` (`d1`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（2）添加組合索引洼冻，會(huì)執(zhí)行失敗。
mysql> alter table tb2 add index idx1 (a1,b1);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
- 分析
myisam存儲(chǔ)引擎在創(chuàng)建索引的時(shí)候隅很，索引鍵長(zhǎng)度是有一個(gè)較為嚴(yán)格的長(zhǎng)度限制的撞牢，所有索引鍵最大長(zhǎng)度總和不能超過(guò)1000，而且不是實(shí)際數(shù)據(jù)長(zhǎng)度的總和叔营，而是索引鍵字段定義長(zhǎng)度的總和屋彪。
主要字符集的計(jì)算方式：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
二、innodb存儲(chǔ)引擎
- 表結(jié)構(gòu)信息：
mysql> create table tb1 (a1 varchar(255), b1 varchar(255), c1 varchar(255), d1 varchar(1000));
Query OK, 0 rows affected (0.01 sec)
2. 添加組合索引绒尊，報(bào)出waring畜挥，實(shí)際在某個(gè)單列上添加的是前綴索引
mysql> alter table tb1 add index idx1(a1,b1,c1,d1);
*Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0*
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)
3. 添加單列索引，報(bào)出waring婴谱，實(shí)際添加的是前綴索引
mysql> alter table tb1 add index idx2(d1);
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table tb1
Table: tb1
Create Table: CREATE TABLE `tb1` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL,
KEY `idx1` (`a1`,`b1`,`c1`,`d1`(255)),
KEY `idx2` (`d1`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
- 分析：
默認(rèn)情況下蟹但，InnoDB 引擎單一字段索引的長(zhǎng)度最大為 767 字節(jié)，同樣的谭羔，前綴索引也有同樣的限制华糖。當(dāng)使用 UTF-8 字符集，每一個(gè)字符使用 3 字節(jié)來(lái)存儲(chǔ)瘟裸，在 TEXT 或者 VARCHAR 類型的字段上建立一個(gè)超過(guò) 255 字符數(shù)的前綴索引時(shí)就會(huì)遇到問(wèn)題客叉。可以啟用服務(wù)器選項(xiàng) innodb_large_prefix 使得這個(gè)限制增加到 3072 字節(jié)景描，而且表的 row_format 需要使用 compressed 或者 dynamic十办。
三、使用前綴索引帶來(lái)的風(fēng)險(xiǎn)：
INNODB的索引會(huì)限制單獨(dú)Key的最大長(zhǎng)度為767字節(jié)超棺，超過(guò)這個(gè)長(zhǎng)度必須建立小于等于767字節(jié)的前綴索引。
此外呵燕，BLOB和TEXT類型的列只能創(chuàng)建前綴索引棠绘。
前綴索引能提高索引建立速度和檢索速度，但是下面情況是無(wú)法使用前綴索引的：
- 索引覆蓋掃描
- 通過(guò)索引的排序（order by, group by）
還是在上面的測(cè)試表上：
mysql> explain select * from tb1 order by d1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql> explain select * from tb1 group by d1;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
