COUNT(非空列)自动重写优化

操作场景

在SQL语义中，COUNT(column)统计的是column列中非NULL值的行数。当column被定义为NOT NULL时，COUNT(column)等价于COUNT(*)。

TaurusDB能够自动识别这种等价关系，将COUNT(非空列)重写为COUNT(0)（即COUNT(*)）。重写后，查询不再依赖该列的数据，因此优化器可以选择不包含该列的索引进行查询。这样既能使用更小的索引，又能避免回表操作，从而显著提升查询性能。

前提条件

• TaurusDB实例内核版本大于等于2.0.75.260300，支持COUNT非空列优化。内核版本的查询方法请参见如何查看云数据库 TaurusDB实例的版本号。
• COUNT优化开关simplify_count_not_null需处于开启状态（默认开启）。

约束与限制

参数说明

使用方法

• 开启和关闭优化

  通过optimizer_switch参数的simplify_count_not_null选项控制该特性的开启与关闭。详细内容请参见修改TaurusDB实例参数。

  您也可以通过SQL语句开启或关闭Count优化。

  • 查看当前状态

    SELECT @@optimizer_switch LIKE '%simplify_count_not_null=on%';

  • 开启优化（默认）

    SET optimizer_switch='simplify_count_not_null=on';

  • 关闭优化

    SET optimizer_switch='simplify_count_not_null=off';

• 确认优化是否生效

  使用EXPLAIN查看执行计划，可通过以下方式确认优化是否生效：

  • warnings信息：EXPLAIN输出的warnings中显示count(0)表示重写已生效。
  • Using index：Extra列中出现Using index表示使用了索引扫描。

    mysql> CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b(b));
    Query OK, 0 rows affected (0.05 sec)

    mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t1
       partitions: NULL
             type: index
    possible_keys: idx_b
              key: idx_b
          key_len: 5
              ref: NULL
             rows: 1
         filtered: 100.00
            Extra: Using where; Using index
    1 row in set, 1 warning (0.01 sec)

    mysql> show warnings;
    +-------+------+-------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                   |
    +-------+------+-------------------------------------------------------------------------------------------+
    | Note  | 1003 | /* select#1 */ select count(0) AS `COUNT(a)` from `test`.`t1` where (`test`.`t1`.`b` > 1) |
    +-------+------+-------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

    warnings中的`count(0)`表明`COUNT(a)`已被重写为`COUNT(0)`，Extra中的`Using index`表明使用了索引扫描。

  • 您也可以通过optimizer_trace确认重写是否发生。

    mysql> CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b(b));
    Query OK, 0 rows affected (0.07 sec)

    mysql> SET optimizer_trace='enabled=on';
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT COUNT(a) FROM t1 WHERE b > 1;
    +----------+
    | COUNT(a) |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.01 sec)

    mysql> SELECT trace LIKE '% count(0) %' FROM information_schema.optimizer_trace;
    +---------------------------+
    | trace LIKE '% count(0) %' |
    +---------------------------+
    |                         1 |
    +---------------------------+
    1 row in set (0.01 sec)

    上述结果表示已发生重写。

    mysql> SET optimizer_trace=default;
    Query OK, 0 rows affected (0.00 sec)

示例1：仅存在单列索引

CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b(b));
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(5,3),(8,3),(4,-1);
ANALYZE TABLE t1;

• 优化关闭时：优化器需要读取列a的值来检查是否为NULL，索引idx_b 中不包含 a 列，因此需要回表读取完整行数据来获取a列的值。

  mysql> SET optimizer_switch='simplify_count_not_null=off';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: range
  possible_keys: idx_b
            key: idx_b
        key_len: 5
            ref: NULL
           rows: 1
       filtered: 100.00
          Extra: NULL
  1 row in set, 1 warning (0.00 sec)

• 优化开启时：COUNT(a) 被重写为 COUNT(0)，不再需要访问 a 列的数据。优化器可以选择仅使用 idx_b 索引进行扫描，无需回表操作。

  mysql> SET optimizer_switch='simplify_count_not_null=on';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: index
  possible_keys: idx_b
            key: idx_b
        key_len: 5
            ref: NULL
           rows: 1
       filtered: 100.00
          Extra: Using where; Using index
  1 row in set, 1 warning (0.00 sec)

示例2：同时存在单列索引和复合索引

假设表t1中列a为NOT NULL，同时建有单列索引idx_b和复合索引idx_b_a。

CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b (b), KEY idx_b_a (b, a));
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(5,3),(8,3),(4,-1);
ANALYZE TABLE t1;

• 优化关闭时：优化器需要读取列 a 的值来检查是否为 NULL。由于 idx_b 不包含 a 列会导致回表，优化器为了避免回表，会选择复合索引 idx_b_a，直接通过索引完成查询。

  mysql> SET optimizer_switch='simplify_count_not_null=off';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: range
  possible_keys: idx_b,idx_b_a
            key: idx_b_a
        key_len: 5
            ref: NULL
           rows: 4
       filtered: 100.00
          Extra: Using index
  1 row in set, 1 warning (0.00 sec)

• 优化开启时：COUNT(a) 被重写为 COUNT(0)，不再需要访问 a 列的数据。此时优化器可以选择更小的单列索引 idx_b 进行扫描，无需回表，也无需使用更大的复合索引。

  mysql> SET optimizer_switch='simplify_count_not_null=on';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: range
  possible_keys: idx_b,idx_b_a
            key: idx_b
        key_len: 5
            ref: NULL
           rows: 4
       filtered: 100.00
          Extra: Using index
  1 row in set, 1 warning (0.00 sec)