关于数据库:您如何知道什么是好的索引?

关于数据库:您如何知道什么是好的索引?

How do you know what a good index is?

在Oracle中使用表时,如何知道要设置好索引还是坏索引?


这取决于您所说的"好"和"坏"。基本上,您需要意识到,添加的每个索引都会提高该列进行任何搜索的性能(因此,向人员表的" lastname"列添加索引将提高其中具有" where lastname ="的查询的性能),但是降低整个表的写入性能。

原因是在添加或更新行时,它必须同时添加或更新表本身以及该行所属的每个索引。因此,如果您在一个表上有五个索引,则每个添加项都必须写入六个位置(五个索引和该表),并且在最坏的情况下,更新可能会占用多达六个位置。

索引创建是查询速度和写入速度之间的平衡。在某些情况下,例如一个数据集市在一个通宵的工作中每周仅加载一次数据,但每天要查询数千次,因此有意义的是过载索引并尽可能加快查询速度。但是,对于在线交易处理系统,您想尝试在两者之间找到平衡。

因此,简而言之,将索引添加到在选择查询中经常使用的列,但是要避免添加过多的列,因此请首先添加最常用的列。

之后,需要进行负载测试以了解性能在生产条件下的反应,并进行大量调整以找到可以接受的平衡。


多样,高度特定或唯一的字段可以作为良好的索引。例如日期和时间戳记,唯一的递增编号(通常用作主键),人的姓名,车牌号等。

一个反例是" gender"(性别)-只有两个公共值,因此索引实际上并没有帮助减少必须扫描的行数。

全长描述性自由格式字符串的索引很差,因为执行查询的人很少知道字符串的确切值。

线性排序的数据(例如时间戳或日期)通常用作聚簇索引,这会强制以索引顺序存储行,并允许按顺序访问,从而极大地加快了范围查询的速度(例如,"给我所有销售订单"在十月和十二月之间)。在这种情况下,DB引擎可以简单地查找范围指定的第一条记录,并按顺序开始读取,直到命中最后一条。


@臭名昭著的母牛-您必须考虑主键,而不是索引。

@Xenph Yan-
其他人还没有涉及的是选择要创建哪种索引。某些数据库确实没有给您太多选择,但是某些数据库具有多种可能的索引。 B树是默认索引,但并非始终是最佳索引。选择正确的结构取决于您期望使用的类型。您最需要哪种查询支持?您处于以读为主或写为主的环境中吗?您的写入内容是由更新还是附加内容主导?等等

有关不同类型的索引及其优缺点的说明,请参见:http://20bits.com/2008/05/13/interview-questions-database-indexes/。


这是一篇很棒的SQL Server文章:
http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx

尽管这些机制在Oracle上不起作用,但是这些技巧非常恰当(减去关于聚簇索引的内容,这在Oracle中的工作方式并不完全相同)。


如果您想改善特定查询,则应遵循一些经验法则。

对于特定的表(您认为Oracle应该从那里开始),请尝试索引WHERE子句中使用的每个列。 首先将等号的列,然后是具有范围或类似值的列。

例如:

1
WHERE CompanyCode = ? AND Amount BETWEEN 100 AND 200

如果列的大小非常大(例如,您要存储XML或其他内容),最好将它们放在索引之外。 假设您必须转到表行以满足选择列表,这将使索引更小以进行扫描。

或者,如果SELECT和WHERE子句中的所有值都在索引中,则Oracle无需访问表行。 因此,有时最好将所选值放在索引的最后,并避免将表访问全部放在一起。

您可以写一本关于最佳索引方法的书-寻找作家乔纳森·刘易斯。


一个好的索引是您可以依赖的,它对于特定的表行而言是唯一的。

一种常用的索引方案是对表中的每一行使用数字递增1。每行最终将具有不同的数字索引。


推荐阅读