logo标签列表

MySQL - 为什么不对每个字段建立索引？

mysqlsqlindexing
139

最近我学会了索引的奇妙之处，性能有了显著提升。然而，尽管我学到了很多，但似乎找不到答案。

索引很好用，但为什么不能对所有字段建立索引，以使表格极快呢？我相信有一个不错的理由不这么做，但是在30个字段的表格中选择3个字段呢？在30个字段中选择10个呢？应该在哪里划线，为什么？

7
尝试向一个有超过1万条目且已被索引的表中插入数值，由于插入/删除操作，所有条目都必须得到更新，这会导致巨大的时间开销和某种程度上的内存开销，特别是如果每个数值都有一个索引。 - Jesus Ramos
5
除了空间和写性能之外，还有一个原因：对于单个表访问使用多个索引非常低效。这意味着，即使每列都有一个索引，在WHERE子句中访问多个列时，选择性能也不是很好。在这种情况下，最好使用多列索引。 - Markus Winand
1
如果你有一个拥有30个字段的表格，你应该真正关注你的表结构。它们可能非常难以处理。 - webs
因为索引会减缓写入速度。 - Janac Meena
7个回答
155

索引占用内存空间（RAM）; 索引太多或太大会导致数据库不得不在磁盘和内存之间进行交换。它们也会增加插入和删除时间（每个索引必须针对每个插入/删除/更新的数据进行更新）。

您没有无限的内存。确保所有索引都适合RAM = 好。

您没有无限的时间。仅为需要索引的列建立索引可最小化插入/删除/更新的性能影响。

16
简洁回答可以帮助理解概念，但不能确定在索引上划定何时为宜。如何确定呢？只需将它们添加到常见的WHERE字段中，然后希望一切顺利吗？ - Andrew
2
@Andrew 一年半后，你找到了你的问题的答案吗？ - Sinjai
3
将它们添加到常见的列中可能是个好方法，但如果你想成为索引方面的专家，还需要阅读大量相关内容。例如：https://dev59.com/4XA75IYBdhLWcg3w49UR - Andrew
2
不要忘记磁盘空间。 - jpmc26
31

请记住，每次更新、插入或删除行时都必须更新每个索引。因此，您拥有的索引越多，写操作的性能就会越慢。

此外，每个索引占用更多的磁盘空间和内存空间（调用时），因此对于大型表，它可能会潜在地减缓读取操作。 点击这里查看

6
这个链接是关于 MS SQL Server 的；而这个问题是关于 MySQL 的。 - OMG Ponies
6
@OMG 链接中的大部分观点适用于所有主要关系型数据库管理系统。 - RichardTheKiwi
5
@Richard aka cyberkiwi: ANSI标准并未涵盖索引 - 每个供应商使用类似的术语已经是一个奇迹了。但即使如此，只有SQL Server和MySQL使用“clustered”和“non-clustered”索引术语 - 它在SQL Server中的含义比在MySQL中更为重要。没有什么可以保证适用于一个供应商的建议也适用于另一个供应商。 - OMG Ponies
3
前6个点适用于任何数据库管理系统。跳过非聚集和非聚集索引，然后下面是更多关于一般索引的点，也很准确。如果您有特定要指出的事情，请指出。否则，这看起来就像您否定了所有答案，从评论（包括您已删除的答案）可以看出，没有人同意您的评估。 - RichardTheKiwi
11

需要平衡CRUD需求。写入表变得很慢。至于在哪里划界限，这取决于数据的访问方式（排序、过滤等）。

每个索引都占用一定的数据库空间。 - Acanthus
@Acanthus：目前最小的硬盘容量以千兆字节计算。 - OMG Ponies
4
正如Brian所指出的那样，存储比你需要的更多通常不是一个好主意。将数据/索引缓存到RAM中，备份介质（每个磁带可以容纳的版本等）都会受到无用索引的影响。 - RichardTheKiwi
11
资源丰富并不意味着可以浪费或低效利用。 - Smandoli
6
没错，但限制条件已经不同于10年前了。 - OMG Ponies
2
索引会占用更多的磁盘和内存空间，但也会大大提高性能。不幸的是，当它达到内存限制时，系统将放弃磁盘空间并冒着性能风险。实际上，您不应该为任何您认为不涉及任何数据遍历算法（插入或搜索WHERE子句）的字段建立索引。但如果需要，您应该这样做。默认情况下，必须为所有字段建立索引。您应该考虑取消索引的字段是仅由管理员使用的查询，除非他们也需要速度。
此外，人们可以考虑所谓的覆盖索引。这种索引包含查询返回的列以及查询用于WHERE子句、分组或查询的任何其他部分的列。 - OSGI Java
1

在表中索引所有列并不是一个好主意。虽然这将使读取表格非常快，但写入速度会变得更慢。对于已经索引了每一列的表进行写入操作，需要将新记录放入该表中，然后将每一列的信息放入其自己的索引表中。

我不确定这是否会使读取表格变得非常快，特别是如果数据表只有100MB，但索引表达到300MB或更多。 - David
1
你说的每一句话都已经被提过了。 - Vael Victus
1
这个答案是我基于我的数学逻辑得出的个人意见。
第二个问题是关于何时停止边界的，首先让我们做一些数学计算。假设我们有一个N行L字段的表，如果我们对所有字段进行索引，我们将得到L个新的索引表，在每个表中，索引字段的数据都会以有意义的方式排序。乍一看，如果您的表是W重量，则它将变成W*2（1 tera将变成2 tera）。如果您有100个大型表格（我已经参与过其中表格数量约为1800个的项目），则您将浪费100倍的空间（100 tera），这远非明智之举。
如果我们在所有表格中应用索引，我们就必须考虑索引更新，其中一个更新将触发所有索引的更新，这相当于选择所有无序的时间。
因此，我得出结论，如果在这种情况下您要失去时间，最好是在选择而不是更新中失去时间，因为如果您选择未索引的字段，则不会触发对所有未索引字段的选择。
什么需要进行索引？
外键：根据 必须进行索引。

主键：我还不确定，也许如果有人阅读这篇文章能够在这个问题上提供帮助。

其他字段：第一个自然的答案是剩余字段数量的一半。为什么呢？如果你应该建立更多索引，那么你离最佳答案就不远了；如果你应该建立更少的索引，那么你也不会差太远，因为我们知道没有索引很糟糕，所有的索引也很糟糕。

从这三点可以得出结论：如果我们有由K个键组成的L个字段，那么极限值应该在((L-K)/2)+K左右，或者大约是L/10。

这个答案基于我的逻辑和个人实践。

0

首先，在SAP - ABAP和后台数据库中，我们可以为所有所需索引字段创建一个索引表，我们将只拥有它们的地址。因此，其他与SQL相关的软件-数据库系统也可以使用一个表来索引所有字段。

其次，写入性能是什么？例如，一家公司一天记录50个销售订单。假设有一个名为VBAK销售订单头表，例如每个表具有30个字段，每个字段长度为20个字符..

我可以在几秒钟内写入实际表格，但其他索引表可以在后台工作，同时尝试运行报告，对于这个报告，当搜索索引表时，可以有一个逻辑-用于数据库编程-一个索引写入过程正在进行并等待它结束（例如同时记录5个销售订单，并且可能需要5秒钟）..因此，正在运行的报告可以等待5秒钟，然后运行5秒钟，总计10秒钟..

没有索引，运行报告不会等待5秒钟的写入性能..但可能要运行40秒钟...

那么，写入性能的意义是什么，没有人同时写入数千条记录。但是读取它们。

读取第二个表格意味着：所有字段都已经排序好了。我选择了3个字段，可以找到需要搜索这些数据的排序集，然后将它们带出来...这不是RAM，也不是内存，只是一个复制的索引表，每个字段只有一个数据-地址数据..什么内存？

我认为，这是软件公司对客户隐藏的秘密之一，不要让他们醒悟过来，否则他们将不再需要未来价格昂贵的另一个系统。

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