likes
comments
collection
share

为什么索引没被采用

作者站长头像
站长
· 阅读数 5

简单来说,数据库没用你的索引有两种情况:

  1. 你的索引不合适
  2. 查询优化器认为不使用索引更快

接下来我们来探讨一下在创建了合适的索引的情况下,为什么查询优化器会认为不使用索引更快。

数据库如何读取数据

在讨论之前,我们先来了解一下数据库是怎么读取数据的。数据库读取数据的方式有以下两种:

  • 使用索引:通过索引获取符合条件的行在文件上的位置,然后从文件中依次读取。因为这些行在文件的不同位置,所以每读取一行就需要跳转到文件的不同位置。这种读取方式称为随机读取。
  • 不适用索引:从表的文件中顺序读取所有行,筛选出符合条件的行。这种读取方式称为顺序读取。

顺序读取的速度要比随机读取快得多。特别是在机械硬盘上,硬盘移动磁头的速度非常慢。虽然现在 SSD 硬盘的随机读取速度已经很快了,但顺序读取速度仍然要比随机读取快。

为什么不使用索引更快

读取的行数较多

假设我们有一张表,这张表有 100 行数据。硬盘随机读取一行数据需要的时间为 4,硬盘顺序读取一行数据需要的时间为 1。以下是使用索引和不使用索引的情况下,数据库读取 n 行数据所需的时间:

  • 使用索引:4 * n
  • 不使用索引:1 * 100

为什么索引没被采用

不难看出,当 n 小于 25 时,使用索引更快;当 n 大于 25 时,不使用索引更快。即当查询的行数占表的比例较大时,不使用索引会更快。这个比例的大小取决于硬盘的随机读取速度和顺序读取速度,并不是一个固定的值,我们通常认为这个比例在 10% 到 30% 之间。

💡 如果你使用的是速度较快的 SSD 硬盘,可以给数据库设置一个较低的随机读取开销,让查询优化器更倾向于使用索引。在 PostgreSQL 中,可以将 random_page_cost 设置为 1.1。

统计信息过时

查询优化器是怎么知道符合条件的行数占表的比例的呢?数据库会统计表的一些信息,包括表的行数、索引列的值的分布等。优化器会根据这些信息估算使用索引和不使用索引的开销。如果统计信息过时了,也会导致优化器做出错误的决策。

比如符合条件的行数实际占比 5%,但统计信息显示占比 50%,优化器就会错误的认为不使用索引更快。

💡 建议在进行大量更改之后使用 ANALYZE TABLE 重新计算统计数据,或者定时重新计算。

转载自:https://juejin.cn/post/7346897828709204005
评论
请登录