Oracle数据库sql优化基本概念：基数与选择性

基数（Cardinality）与选择性（Selectivity）是数据库理论和SQL优化领域中的两个重要概念，特别是在关系型数据库管理系统（如Oracle、MySQL等）中用于评估查询效率和索引效果的关键指标。

1、基数（Cardinality）：

• 在数据库中，某列的基数指的是该列中不重复值的数量，即唯一键（Distinct Values）的数量。例如，假设一个表中有1000行数据，其中一列包含性别信息，只有“男”和“女”两种值，那么这一列的基数就是2。
• 对于索引而言，基数则指该索引所覆盖列组合的唯一键数量。索引基数越大，意味着通过索引区分的数据行更多，这有助于缩小查询范围。

2、选择性（Selectivity）：

• 选择性是衡量一个列或一组列对于过滤数据的有效程度，它是基于基数与表中总行数的比例计算得出的，公式为：选择性 = (基数 / 总行数) * 100%。
• 如果一个列的选择性较高，表示该列的值在整个表中分布得比较均匀，也就是说，通过该列筛选数据能有效地减少检索的数据量，这对于索引的设计和查询性能至关重要。
• 当选择性超过一定阈值（如20%），通常认为在这个列上创建索引对提高查询性能有利，因为它能帮助优化器更准确地估计查询的代价，引导其选择更快的执行计划。
  3、计算基数与选择性：
  在Oracle数据库中，获取某一列基数（Distinct Count）和选择性可以通过以下方式实现，但请注意，Oracle并没有直接提供查询选择性的内置函数，我们需要手动计算选择性。

首先，我们先查询列的基数，假设我们有一个名为employees的表，我们要查看department_id列的基数：

SELECT COUNT(DISTINCT department_id) as cardinality
FROM employees;

然后，我们可以计算department_id列的选择性，先获取表的总行数：

SELECT COUNT(*) as total_rows
FROM employees;

接着，将这两个查询结合起来，计算选择性：

WITH 
cardinality_data AS (
  SELECT COUNT(DISTINCT department_id) as distinct_count
  FROM employees
),
total_rows_data AS (
  SELECT COUNT(*) as total_rows
  FROM employees
)
SELECT 
  (distinct_count / total_rows) * 100.0 as selectivity
FROM 
  cardinality_data,
  total_rows_data;

上述脚本首先分别计算了department_id列的不重复值数量（基数）和表的总行数，然后计算出选择性百分比。在实际应用中，你可能需要根据你的数据库实际情况调整表名和列名。同时，由于Oracle并不直接支持在一个查询中直接计算列的选择性，所以这里采用了一个临时结果集（CTE）的方式来分步计算。

在数据库性能优化的过程中，理解并正确估计基数和选择性对于数据库管理员来说十分重要，它们直接影响到索引的创建和维护、查询计划的选择以及整个系统的性能表现。例如，如果表的数据发生变化而基数统计信息没有及时更新，可能导致优化器做出错误的执行计划选择，影响查询效率。因此，定期更新统计信息并让优化器获得准确的基数信息是优化工作的一部分。