索引需要在多大程度上缩小搜索结果才能有助于加快查询速度?
各个方面的一些例子:
- 用于存储 true/false 值的列显然只有两个唯一值。
- “姓氏”列可能有许多唯一值(尽管可能没有)。
- 主键列具有所有唯一值。
我认为索引的目标是快速将搜索范围缩小到几行,因此,最后一种情况最好,第二种可以,第一种没用。
我对么?如果是这样,有用的线大致在哪里?例如,如果一个索引可以将结果缩小到 1% 的行,这有用吗?10% 或 25% 呢?
索引需要在多大程度上缩小搜索结果才能有助于加快查询速度?
各个方面的一些例子:
我认为索引的目标是快速将搜索范围缩小到几行,因此,最后一种情况最好,第二种可以,第一种没用。
我对么?如果是这样,有用的线大致在哪里?例如,如果一个索引可以将结果缩小到 1% 的行,这有用吗?10% 或 25% 呢?
列数据的多样性称为选择性。在确定索引是否有用时,了解选择性很有用,但它并不是决定速度优势的唯一因素。其他因素包括与表相比索引在存储上的速度有多快、表/索引的多少已经被缓存、索引与表相比有多大,以及其他一些事情。
知道列的数据类型不一定能帮助我们确定列上索引的选择性。即使一个列被限制为两个值,也可能只在几行中使用这些值,而其余部分为 NULL。另一方面,可能有许多不同值的列可能在每一行中都具有相同的值。即使您的
id
列中的所有行都具有唯一值,如果您正在搜索id
>= 10 的行,索引可能不会有用,即使它具有高度选择性。您不能单独使用选择性来确定索引是否有用,因为即使它返回 100% 的行,如果索引包含查询所需的所有数据,它也会比使用表更快。另一方面,对于一个小表,查询整个表可能会更快,即使被查找的行只占总数的 1%。
确定应该创建哪些索引与其说是查看表结构,不如说是查看重要的查询以及它们需要检索的数据。
总的来说,是的,你是对的。当 B 树索引允许您识别表中需要返回的行的相对较小部分时,它会更有用。
至于那个截止点是什么,这取决于。几乎所有现代企业关系数据库都会有一个基于成本的优化器,它会尝试根据大量有关数据分布、不同操作的相对开销等的统计数据来制定最佳查询计划。不同的数据库和不同的版本同一个数据库将依赖于相当广泛的设置、统计数据和其他信息来确定临界点在哪里。我的粗略猜测是,当您检索超过 10-15% 的行时,您可能处于表扫描效率更高的范围内。但是很容易想出这样的情况:如果索引只为您提供 5% 的数据或者您的数据,您最好使用表扫描
取决于索引的类型!我假设您正在考虑“普通”二叉树索引。当可能需要检索表中 10% 的行时,“计划者”通常会使用这些。为什么百分比这么低?不要忘记 DBMS 必须检索索引块,而不是行,而且检索到的大部分数据不会针对正在查找的行进行扫描(即开销)。此外,索引本身使用空间,它肯定远不及“免费”或什至恒定时间(因此空间)操作。
请参阅http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm#autoId25以了解“计划者”考虑因素的良好概述(至少,那些在合理的当前甲骨文版本)。
某些基于磁盘的操作的速度也会影响 Oracle 决定使用索引扫描还是全表扫描。
到目前为止,在我的回答中,我假设我们正在谈论一个普通的索引查找值。例如,Oracle 中使用了许多其他索引扫描(请参阅我提供的链接)。
然后还有位图索引:这些索引为每个唯一值存储一个位图(即 0 和 1,每行 1)。这些是布尔值和其他低基数列的理想选择(但在其他情况下也很有用,如某些论文所示)。
位图索引的缺点是它们更新起来非常昂贵,因此对于大多数只读数据库(如决策支持系统、数据仓库等)最有用。
很好的答案,两个。大多数数据库引擎将优化查询以最小化磁盘 I/O,因此如果索引没有将选择范围缩小到表的相当小的百分比,查询优化器将使用全表扫描来代替。该百分比因数据库引擎而异,但通常约为 2-10%。
二进制列通常不能单独使用一个好的索引,但可以与另一个列结合使用。
“id”列(对于那些可能不熟悉 Ruby/Rails 的人,将其视为一个自动递增的、唯一的、主键整数列)由于其使用的性质,通常必须被索引。如果它被声明为主键,你不需要声明一个索引......它无论如何都会得到一个。
Varchar 列可能会或可能不会成为一个好的索引,这取决于内容。许多数据库引擎将允许您仅索引 varchar 的第一列。很好的例子:姓氏的前 8 位加上名字的前 4 位。
大多数规则都有例外;阅读您正在使用的特定数据库。
我最近参加了 Paul Randall 和 Kimberly Tripp 举办的 SQL Skills immersion (IE1) 活动,Kimberly 是索引和 SQL Server 内部结构方面的权威。
我无法从理论上告诉您什么时候使用索引是理想的,我相信可以创建一个数学模型来告诉您这一点。在 SQL Server 中的实际术语中,在他们的演示中,如果可以避免扫描 90% 的表,则使用索引。结果各不相同。有时它会以低得多的百分比使用表扫描。这也与你统计中的肉欲有关。
如果您认为它对您有用,我可以提取信息并进行审核。