批量获取创建列存索引的DDL语句

某些场景中,需要为某个业务或某个模块创建列索引,而不仅仅是为一条或者几条SELECT语句中的列创建列索引。在这种场景下,为了避免重复创建列索引,可以先调用存储过程dbms_imci.columnar_advise_begin(),然后再批量调用存储过程dbms_imci.columnar_advise(),批量调用完成后,再调用存储过程dbms_imci.columnar_advise_show()批量获取DDL语句。

前提条件

  • PolarDB集群版本需为PolarDB MySQL版8.0.1且修订版本为8.0.1.1.30及以上。

  • 您需要具有所操作的表的读(SELECT)权限。

语法

  • dbms_imci.columnar_advise_begin()

    存储过程dbms_imci.columnar_advise_begin()用于开启批量获取DDL语句模式。该存储过程调用后,调用存储过程dbms_imci.columnar_advise()时,不会立即输出DDL语句,而是缓存在内存中,缓存过程中不会记录重复的表名和列名。最后通过调用存储过程dbms_imci.columnar_advise_show()dbms_imci.columnar_advise_show_by_columns()显式获取DDL语句。

    说明

    调用存储过程dbms_imci.columnar_advise_begin()后,再调用存储过程dbms_imci.columnar_advise_by_columns()的效果与调用存储过程dbms_imci.columnar_advise()的效果相同。

  • dbms_imci.columnar_advise_show()

    按表展示存储过程dbms_imci.columnar_advise()所获取的DDL语句,且DDL语句不会重复覆盖相同的表。

  • dbms_imci.columnar_advise_show_by_columns()

    按列展示存储过程dbms_imci.columnar_advise()获取的DDL语句,DDL语句不会重复覆盖相同的列。

  • dbms_imci.columnar_advise_end()

    结束批量获取DDL语句模式并清理缓存。调用存储过程dbms_imci.columnar_advise_end()前可以重复调用dbms_imci.columnar_advise_show()dbms_imci.columnar_advise_show_by_columns()dbms_imci.columnar_advise_end()调用后,再调用dbms_imci.columnar_advise_show()会返回错误信息。

注意事项

  • 用于缓存dbms_imci.columnar_advise()中间结果的最大内存受imci_columnar_advise_buffer_size参数控制,默认为8 MB,一般情况下,能够缓存几千张表的DDL语句。如果需要缓存更多的DDL语句,可以使用SET命令适当调整imci_columnar_advise_buffer_size参数值,如SET imci_columnar_advise_buffer_size = 16777216;

  • 即使没有显式调用存储过程dbms_imci.columnar_advise_end(),当链接断开时,也会清理 dbms_imci.columnar_advise()缓存。

示例

t1t2表为例,批量获取创建列存索引的DDL语句。

  1. 执行如下命令,切换至test库。

    use test;
  2. 执行如下命令,创建t1t2表。

    create table t1 (a int, b int) engine = innodb;
    create table t2 (a int, b int) engine = innodb;
  3. 调用存储过程,开启批量获取DDL语句模式。

    call dbms_imci.columnar_advise_begin();
  4. 批量执行存储过程dbms_imci.columnar_advise()

    call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
    call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
    call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
    call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
  5. 调用存储过程,来显示获取DDL语句。

    • 按表展示存储过程dbms_imci.columnar_advise()所获取的DDL语句。

      call dbms_imci.columnar_advise_show();

      执行结果如下:

      +-------------------------------------------+
      | DDL_STATEMENT                             |
      +-------------------------------------------+
      | ALTER TABLE test.t1 COMMENT='COLUMNAR=1'; |
      | ALTER TABLE test.t2 COMMENT='COLUMNAR=1'; |
      +-------------------------------------------+
      2 rows in set (0.00 sec)
    • 按列展示存储过程dbms_imci.columnar_advise()所获取的DDL语句。

       call dbms_imci.columnar_advise_show_by_columns();

      执行结果如下:

      +-------------------------------------------------------------------------------------------------------------------------------------------+
      | DDL_STATEMENT                                                                                                                             |
      +-------------------------------------------------------------------------------------------------------------------------------------------+
      | ALTER TABLE test.t1 MODIFY COLUMN a int(11) DEFAULT NULL COMMENT 'COLUMNAR=1', MODIFY COLUMN b int(11) DEFAULT NULL COMMENT 'COLUMNAR=1'; |
      | ALTER TABLE test.t2 MODIFY COLUMN a int(11) DEFAULT NULL COMMENT 'COLUMNAR=1';                                                            |
      +-------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
  6. 批量获取DDL语句完成后,调用以下存储过程,结束批量获取DDL语句模式并清理缓存。

    call dbms_imci.columnar_advise_end();

    执行结果如下:

    Query OK, 0 rows affected (0.11 sec)
阿里云首页 云原生关系型数据库 PolarDB 相关技术圈