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;
- 这样可以有效减少运算次数，提高查询效率。
此文章为个人的笔记，如有写的不对或你对这方面还有什么高见，评论区见。