SQL查询优化：通过选择性提高数据库性能

选择性（Selectivity）

概念

1. 选择性定义：

   • 选择性是指某列的基数与总行数的比值，再乘以100%。公式如下：
     选择性=(
     总行数/
     基数
     ​
     )×100%

   • 选择性用于衡量一个列在查询中的区分能力。高选择性意味着一个列能在很大程度上过滤数据，低选择性则表示数据分布比较均匀，可能需要更多的扫描。

2. 应用场景：

   • 在SQL优化时，单独看列的基数没有意义，必须将基数与总行数对比才有实际意义。因此，引入选择性这一概念来帮助优化查询性能。

收集统计信息

1. 收集统计信息：

   • 为了查看选择性，必须先收集表的统计信息。在Oracle数据库中，可以通过DBMS_STATS.GATHER_TABLE_STATS过程来完成。

   BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(
         ownname       => 'SCOTT',
         tabname       => 'TEST',
         estimate_percent => 100,
         method_opt    => 'for all columns size 1',
         no_invalidate => FALSE,
         degree        => 1,
         cascade       => TRUE
      );
   END;
   /
   

   • 上述过程收集了SCOTT用户下的TEST表的完整统计信息。这包括每列的基数、选择性等。

2. 实际操作：

   • 收集统计信息非常重要，它影响查询优化器的决策，确保执行计划的效率和准确性。

检查列的基数与选择性

1. 查询各列基数与选择性：

   SELECT a.column_name,
          b.num_rows,
          a.num_distinct Cardinality,
          ROUND(a.num_distinct / b.num_rows * 100, 2) selectivity,
          a.histogram,
          a.num_buckets
     FROM dba_tab_col_statistics a, dba_tables b
    WHERE a.owner = b.owner
      AND a.table_name = b.table_name
      AND a.owner = 'SCOTT'
      AND a.table_name = 'TEST';
   

   • 这段SQL脚本用于查询TEST表中各列的基数和选择性。结果展示了列名、行数、基数、选择性、直方图信息和桶数（Buckets）。

2. 结果分析：

   • OBJECT_ID列的选择性为100%，表明每个值都是唯一的，非常适合用于索引。
   • OWNER列的选择性为0.04%，说明数据分布不均匀，大部分数据可能集中在少数值中。

数据分布与选择性应用

1. 分析数据分布：

   SELECT *
     FROM (SELECT object_name, COUNT(*)
             FROM test
            GROUP BY object_name
            ORDER BY 2 DESC)
    WHERE ROWNUM <= 10;
   

   • 这段查询用于查看object_name列的数据分布，输出前10行数据的分布情况。

2. 示例结果：

   • OBJECT_NAME列的数据选择性为61.05%，分布相对均衡，适合使用索引。
   • 但也需注意DBMS_REPCAT_AUTH的计数仅为5，显示有些数据分布不均匀，选择性策略需要结合实际情况调整。

列在查询中的作用

1. 查看列的查询参与情况：

   BEGIN
      DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
   END;
   /
   

   • 通过刷新数据库监控信息，确保最新的统计数据反映在优化决策中。

2. 查看WHERE条件中出现的列：

   SELECT r.name owner,
          o.name table_name,
          c.name column_name,
          equality_preds,
          equijoin_preds,
          nonequijoin_preds,
          range_preds,
          like_preds,
          null_preds,
          timestamp
     FROM sys.col_usage$ u,
          sys.obj$ o,
          sys.col$ c,
          sys.user$ r
    WHERE o.obj# = u.obj#
      AND c.obj# = u.obj#
      AND c.col# = u.intcol#
      AND r.name = 'SCOTT'
      AND o.name = 'TEST';
   

   • 此查询显示了哪些列出现在WHERE子句中，及其参与的过滤条件类型（等值、范围、LIKE等）。

3. 实际应用：

   • 确定某列是否常用作查询条件，可以帮助决定是否为其创建索引。

执行一个示例查询

1. 执行选择性分析：

   SELECT object_id, owner, object_type
     FROM test
    WHERE owner = 'SYS'
      AND object_id < 100
      AND ROWNUM <= 10;
   

   • 此查询用于快速获取owner为SYS且object_id小于100的前10个对象，结合选择性信息判断查询效率。

详细举例

例子1：高选择性列的索引应用

有一个客户表（customers），其中的email字段是唯一的，选择性为100%。

-- 为高选择性字段创建索引
CREATE INDEX idx_customers_email ON customers(email);

-- 执行查询
SELECT * FROM customers WHERE email = 'example@example.com';

分析：

• 由于email字段具有高选择性，为其创建索引能够大大提升查询效率，尤其是在执行等值查询时。

例子2：低选择性列的索引决策

在订单表（orders）中，status字段表示订单状态，可能有以下几种状态：'Pending', 'Shipped', 'Delivered'。假设选择性仅为5%。

-- 不建议为低选择性字段创建索引
SELECT * FROM orders WHERE status = 'Pending';

分析：

• 由于status字段选择性较低，不适合使用索引，因为数据分布不均匀，索引效率不高，全表扫描可能更合适。

例子3：结合选择性和基数优化查询

在一个产品表（products）中，分析category_id字段的选择性和基数以优化查询。

-- 查询选择性
SELECT category_id, COUNT(*)
  FROM products
 GROUP BY category_id
 ORDER BY COUNT(*) DESC;

-- 判断是否创建索引
-- 如果某个category_id的选择性超过20%，考虑创建索引
CREATE INDEX idx_products_category ON products(category_id);

分析：

• 当某个category_id的选择性显著高于其他值（大于20%），创建索引能提高针对特定类别的查询效率。

---

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

让AI工具成为你的得力助手，感受AI工具的无限可能，让复杂的任务变得简单，让你的工作更加轻松和高效。