知识卡片

结构图卡

索引失效的两种常见原因:低散列度与函数包裹列

理解等级:未标注

建了索引不代表查询一定会用上它,两种常见原因会让计划器主动放弃索引。第一种是列本身的散列程度(distinct value的丰富程度)过低:散列程度是列中值唯一性的指标,低散列程度意味着大量重复值(比如性别列),高散列程度意味着值很分散(比如主键列);若一个字段散列程度低,即使对它施加等值过滤,仍会命中表中的大部分记录(可选择性差),此时索引扫描带来的大量随机读取,开销反而可能超过直接顺序扫描整张表——这解释了为什么”表中大多数人都叫Sylvia”这种场景下,即便对name列建了索引,计划器仍然会选择顺序扫描。第二种是过滤条件里对列施加了函数,比如WHERE UPPER(last_name) = 'SMITH'或WHERE date(trans_date) = '2008-07-15':PostgreSQL默认的B树索引存储的是列的原始值本身,并直接指向该值所在的记录,而UPPER('Julia')和'Julia'在索引结构里是两个不同的值——计划器不会自动推断”f(x)其实等于对x做了个变换”,因此索引里查不到UPPER(last_name)这个计算结果,只能放弃索引改用顺序扫描。这个问题有两种解决方式:一是把数据存储规范化(比如统一存大写)从根源上避免运行时转换;二是创建基于表达式的索引(直接对UPPER(last_name)这个表达式建索引),但这种索引维护成本较高——插入、更新时都要重新计算一次函数值;对于日期截断这类场景,更简单的办法是把等值条件改写成范围条件(trans_date >= '2008-07-15' AND trans_date < '2008-07-16'),从而完全避开对列施加函数,原文案例中这个改写把估计开销从705.15降到了157.38。

结构图:

flowchart TB
  Q["索引为何未被使用?"]
  Q --> A["原因1:列散列程度低<br/>(大量重复值,可选择性差)"]
  A --> A1["索引随机读取开销>顺序扫描整表<br/>→计划器主动放弃索引"]
  Q --> B["原因2:过滤条件对列施加了函数<br/>如UPPER(col)/date(col)"]
  B --> B1["B树索引存原始值,f(x)≠x<br/>→索引查不到计算结果"]
  B1 --> C1["解法1:数据存储规范化(如统一大写)"]
  B1 --> C2["解法2:基于表达式建索引(维护成本高)"]
  B1 --> C3["解法3:等值条件改写为范围条件(如日期截断场景)"]
这张卡片看不懂?

已有反馈 · 0 条

    还没有人提交反馈。

    原书出处

    参考来源

    - 位置:《数据架构师的PostgreSQL修炼:高效设计、开发与维护数据库应用》第7章《SQL调优》之"7.4.6 在FILTER子句中使用函数"与"7.4.9 不使用索引的情况"(源文件:_epub-src/OEBPS/text00086.html、text00089.html) - 结论依据:原文说明"PostgreSQL决定是否使用索引取决于结果集的可选择性,也就是散列程度……如果一个字段的散列程度较低……相比于索引扫描后的随机读取,针对该表的顺序扫描可能是更有效的……B-树索引,默认创建的索引,是一种存储列值和并指向记录(包括了这些值的)的数据结构……PostgreSQL不知道f(x)和x是一样的。所以,它决定不使用索引", 直接支撑本卡关于两种索引失效原因的结构图。 - 原始内容:把函数移开后,PostgreSQL则使用了索引。估计的开销值从705.15下降到157.38。