Oracle 之索引，妙不可言（五）——函数索引

函数索引

• CREATE INDEX index_name ON table_name (FUNCTION(expression));
  • index_name：函数索引的名称；
  • table_name：函数索引所针对的表名；
  • FUNCTION(expression)：指定要创建索引的函数及其参数。
  • 案例：创建一个对表 employees 上的 UPPER 函数进行索引的语句：
    • CREATE INDEX idx_employees_upper ON employees (UPPER(last_name));
    • 该语句将创建一个名为 idx_employees_upper 的函数索引，用于加速对 employees 表中 last_name 字段的 UPPER 函数的调用。
• 列运算让函数索引失效
  • 对索引列做运算导致索引无法使用，因函数索引是基于列值的而不是基于列运算的。
  • 当对某个列进行运算时，该列的值已经被修改，因此原有的函数索引不能正确地反映列值的变化。此外，对列进行运算也会使得数据库无法预测索引的值，从而无法使用索引来提高查询的效率。
  • 因此，尽可能避免列运算可以提高查询效率和索引的使用。如果无法避免，可以使用其他技术来优化查询性能，例如使用物化视图来存储计算结果并加速查询。
• 函数索引如何运用
  • 函数索引可以用于加快对表达式或函数的查询。
  • 创建函数列索引，而不是创建索引列在语句中加函数。
  • 函数索引适用于对大数据集进行复杂计算后的查询。但是需要注意的是，过于复杂的函数可能会导致查询性能下降，所以应该谨慎使用。
• 缺点
  • 函数索引需要对每个索引列进行单独查询，会增加索引的大小，占用存储空间；
  • 当查询中使用了多个函数时，函数索引可能不起作用并导致性能下降；
  • 如果函数参数发生更改，索引也需要相应更新，增加了开销和复杂性；
  • 只能在函数参数方面做出相对固定的假设，如果函数参数的数据分布发生变化，查询优化可能会失效。
• 优点
  • 可以提高查询的性能，特别是当函数在查询中使用较频繁时；
  • 可以处理大量数据，即使涉及到复杂的计算或文本操作也能提高查询速度；
  • 意味着可以避免冗余操作，不需要对数据进行额外的计算或处理；
  • 帮助优化SQL语句，改进查询。
• 避免列运算案例
  • 假设有一个表格employee，其中包含 id、name、department 和 salary 四个列。现在需要经常进行 计算所有员工的税后薪资 (salary - salary*0.1)。
  • 查询语句：SELECT id, name, department, salary, (salary - (salary * 0.1)) AS after_tax_salaryFROM employee;
  • 则每次查询时都需要进行一次运算。如果表格数据量很大，查询很频繁，这会影响查询效率。
  • 为了避免每次计算，在 Oracle 中，可以使用函数索引来减少运算次数。例如，可以为 salary 列创建一个 after_tax_function 函数索引，使查询时可以直接从索引中获取每个员工的税后薪资。
  • 创建函数索引：CREATE INDEX after_tax_salary_idx ON employee ((salary - (salary * 0.1)));
  • 在查询时，只需将索引列加入 SELECT 语句即可：SELECT id, name, department, salary, (salary - (salary * 0.1)) AS after_tax_salaryFROM employeeWHERE (salary - (salary * 0.1)) >= 5000;
  • 这样可以有效减少运算次数，提高查询效率。

此文章为个人的笔记，如有写的不对或你对这方面还有什么高见，评论区见。