知识卡片
索引失效的两种常见原因:低散列度与函数包裹列
建了索引不代表查询一定会用上它,两种常见原因会让计划器主动放弃索引。第一种是列本身的散列程度(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 条
还没有人提交反馈。