当你在MySQL表中为一个或多个列创建索引时,数据库会创建一个名为B-tree的数据结构(假设您使用默认的索引设置),其中每个记录的关键字都是索引列中值的串联。
例如,假设您有一个定义如下的表:
CREATE TABLE mytable (
id int unsigned auto_increment,
column_a char(32) not null default '',
column_b int unsigned not null default 0,
column_c varchar(512),
column_d varchar(512),
PRIMARY KEY (id)
) ENGINE=MyISAM;
然后,让我们给它一些数据:
INSERT INTO mytable VALUES (1, 'hello', 2, null, null);
INSERT INTO mytable VALUES (2, 'hello', 3, 'hi', 'there');
INSERT INTO mytable VALUES (3, 'how', 4, 'are', 'you?');
INSERT INTO mytable VALUES (4, 'foo', 5, '', 'bar');
现在假设您决定向column_a
和column_b
添加一个键,如下所示:
ALTER TABLE mytable ADD KEY (column_a, column_b);
数据库将创建上述B树,其中将有四个键,每个键对应一行:
hello-2
hello-3
how-4
foo-5
当您执行引用
column_a
列或同时引用
column_a
和
column_b
列的搜索时,数据库将能够使用此索引来缩小需要检查的记录集。假设您有一个查询如下:
SELECT ... FROM mytable WHERE column_a = 'hello';
尽管上述查询并没有为column_b
列指定一个值,但仍然可以利用我们的索引来查找所有以“hello”开头的键。同样的原因,如果您有一个如下的查询:
SELECT ... FROM mytable WHERE column_b = '2';
这个查询无法使用我们的索引,因为它必须解析索引键本身来确定哪些键的第二个值与“2”匹配,这是非常低效的。
现在,让我们回答您最初的问题:最大长度。假设我们尝试创建一个跨越此表中所有四个非主键列的索引:
ALTER TABLE mytable ADD KEY (column_a, column_b, column_c, column_d);
您会收到一个错误提示:
ERROR 1071 (42000): Specified key was too long
在这种情况下,我们的列长度分别为32、10、512和512,在每个字符只有一个字节的情况下总共为1066,超过了1000的限制。假设它确实能工作,你将创建以下密钥:
hello-2-
hello-3-hi-there
how-4-are-you?
foo-5--bar
现在,假设您的
column_c
和
column_d
中有非常长的值 -- 每个值都有512个字符。即使在基本单字节字符集中,您的键现在也超过了1000个字节的长度,这就是MySQL抱怨的原因。在使用多字节字符集时,情况会更糟,即使看似“小”的列仍然可能将键推向极限。
如果你必须使用大复合键,一个解决方案是使用InnoDB表而不是默认的MyISAM表,它支持更大的键长度(3500个字节)-- 你可以通过在上面的声明中交换
ENGINE=InnoDB
而不是
ENGINE=MyISAM
来实现这一点。然而,通常来说,如果你使用的是长键,则可能存在表设计问题。
请记住,单列索引通常比多列索引提供更多的效用。当您经常/总是通过指定查询中所有必要的条件来利用它时,您需要使用多列索引。另外如其他人所提到的,不要为表的每一列建立索引,因为每个索引都会增加数据库的存储开销。您希望将索引限制在经常被查询使用的列上,如果看起来您需要太多的索引,您应该考虑将表拆分成更多逻辑组件。
WHERE
子句中有多个字段,而且通常添加到索引中的多个字段会引发此错误。 - bob-the-destroyer