MySQL索引:它们如何工作?

3
我对MySQL索引完全不了解。我有几个MyISAM表,每个表都有100k+记录,采用utf8字符集和排序规则。主键通常是整数。每个表上的许多列可能具有重复值。
我需要快速计算、求和、平均或以其他方式执行任意数量字段的自定义计算,可以在任何数量的表上连接使用。
我找到了这个页面,提供了MySQL索引使用的概述:http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html,但我仍然不确定我是否正确使用索引。当我认为我已经从我想要计算的一组字段中制作出了完美的索引时,我会收到“索引必须小于1000字节”的错误信息。
有人能解释如何最有效地创建和使用索引来加速查询吗?
注意:在这种情况下无法升级Mysql。使用Navicat Light进行数据库管理,但此应用程序不是必需的。

只在你想要在 where 子句中进行搜索的字段上放置索引,而不是那些你想要求和或平均的字段。 - dqhendricks
@dqhendricks:这是我从上面链接的MySQL文档页面中得到的印象。但是,我经常会在WHERE子句中有多个字段,而且通常添加到索引中的多个字段会引发此错误。 - bob-the-destroyer
如果您有太多的where字段,那么可能是您的表结构有误。例如,添加了一堆属性字段,而不是使用外键和JOIN查询将其链接到主表的单独属性表中。 - dqhendricks
此外,将主搜索字段索引化通常比全部索引化更好。 - dqhendricks
"5.0已经过时了,我建议不要阅读这个问答。" - Rick James
4个回答

8
当你在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_acolumn_b添加一个键,如下所示:

ALTER TABLE mytable ADD KEY (column_a, column_b);

数据库将创建上述B树,其中将有四个键,每个键对应一行:

hello-2
hello-3
how-4
foo-5

当您执行引用column_a列或同时引用column_acolumn_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; max key length is 1000 bytes

在这种情况下,我们的列长度分别为32、10、512和512,在每个字符只有一个字节的情况下总共为1066,超过了1000的限制。假设它确实能工作,你将创建以下密钥:
hello-2-
hello-3-hi-there
how-4-are-you?
foo-5--bar

现在,假设您的 column_ccolumn_d 中有非常长的值 -- 每个值都有512个字符。即使在基本单字节字符集中,您的键现在也超过了1000个字节的长度,这就是MySQL抱怨的原因。在使用多字节字符集时,情况会更糟,即使看似“小”的列仍然可能将键推向极限。
如果你必须使用大复合键,一个解决方案是使用InnoDB表而不是默认的MyISAM表,它支持更大的键长度(3500个字节)-- 你可以通过在上面的声明中交换 ENGINE=InnoDB 而不是 ENGINE=MyISAM来实现这一点。然而,通常来说,如果你使用的是长键,则可能存在表设计问题。
请记住,单列索引通常比多列索引提供更多的效用。当您经常/总是通过指定查询中所有必要的条件来利用它时,您需要使用多列索引。另外如其他人所提到的,不要为表的每一列建立索引,因为每个索引都会增加数据库的存储开销。您希望将索引限制在经常被查询使用的列上,如果看起来您需要太多的索引,您应该考虑将表拆分成更多逻辑组件。

感谢您的出色解释。您认为@bill-karvin在这里提供的_prefix indexes_解决方案怎么样?:https://dev59.com/gGoy5IYBdhLWcg3wJKvR#8747703 您认为这样的索引会如何工作? - rineez

1

索引通常不适用于自定义计算,其中用户能够构建自己的查询。通常,您选择与要运行的特定查询匹配的索引,使用 EXPLAIN 查看是否正在使用该索引。

如果您完全不知道可能执行哪些查询,则通常最好为每个列创建一个索引-而不是覆盖所有列的索引。

如果您很清楚哪些查询经常运行,可以为这些特定查询创建额外的索引。如果您的用户抱怨某些类型的查询运行太慢,也可以稍后添加索引。

此外,索引通常对于计算计数、总和和平均值并不是非常有用,因为这些类型的计算需要查看每一行。


使用EXPLAIN查看索引是否被使用。谢谢。但是在预期的查询中,您认为我应该如何最好地形成索引?我假设通常是根据WHERE中引用的字段,但我不知道为什么会遇到字节限制以及如何避免它。通常情况下,这会阻止我将多个字段添加到索引中。 - bob-the-destroyer

1

看起来你试图将太多的字段放入索引中。限制可能是编码所有字段所需的字节数。

索引用于查找记录,因此您要选择在“WHERE”上使用的字段。在这些字段之间进行选择时,您要选择能够最快缩小结果范围的字段。

例如,对男/女进行过滤通常不会有太大帮助,因为您只能节省约50%的时间。但是,对州进行过滤可能很有用,因为您将分解成更多类别。但是,如果数据库中几乎所有人都在一个州,那么这种方法就行不通。


限制可能是编码所有字段所需的字节数。您是指编码字段名称、所有列唯一值还是其他内容? - bob-the-destroyer
1
@bob-the-destroyer:指的是值,例如整数需要4个字节,11个字符的文本字段需要11个字节等。 - Winston Ewert
如果你达到了这个限制,那么你的索引中可能有太多的字段。 - Winston Ewert
"the values" 然后当我面对 255 个字符变量时,这就有了意义。我认为 MySQL 索引现在开始变得更加明智了。 - bob-the-destroyer

1

记住索引是用于排序和查找行的。

你得到的错误信息听起来像是在谈论 MyISAM 表索引的 1000 字节前缀限制。从 http://dev.mysql.com/doc/refman/5.0/en/create-index.html 中可以看到:

这里显示的语句创建了一个使用 name 列的前 10 个字符的索引:

CREATE INDEX part_of_name ON customer (name(10)); 如果列中的名称通常在前 10 个字符上不同,则此索引应该不比从整个 name 列创建的索引慢太多。此外,对于索引使用列前缀可以使索引文件更小,这可能会节省大量磁盘空间,并且还可能加快 INSERT 操作。

前缀支持和前缀长度(如果支持)取决于存储引擎。例如,对于 MyISAM 表,前缀可以长达 1000 字节,而对于 InnoDB 表,前缀长度为 767 字节。

也许你可以尝试在有问题的列上使用 FULLTEXT 索引。


ntag:最终我将每个索引命名为'a'、'b'、'c'等,但仍然导致了这个大小错误。这就是我对索引大小限制感到困惑的原因。 - bob-the-destroyer

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接