OceanBase 数据库自 V3.2.x 版本开始对普通租户下单表进行手工统计信息的收集。对于 Oracle 租户和 MySQL 租户，收集统计信息命令有些差异。本文描述在 MySQL 租户下如何对表进行统计信息的收集。
适用版本
OceanBase 数据库 V2.x 和 V3.x 版本。
操作步骤
MySQL 模式下的 ANALYZE 语法。
analyze_stmt:
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list
WITH intnum BUCKETS
当 Session 配置项 enable_sql_extension 为 TRUE 的时候，可以使用 Oracle 模式的语法，即 OceanBase 数据库为 MySQL 模式提供的扩展语法。
analyze_stmt:
ANALYZE TABLE table_name
[ PARTITION (parition_name [,partition_name...])
| SUBPARTITION(subpartition_name [,subpartition_name...])
]
COMPUTE STATISTICS [analyze_for_clause]
| ESTIMATE STATISTICS [analyze_for_clause] [SAMPLE INTNUM {ROWS | PERCENTAGE}]
analyze_for_clause:
FOR TABLE
| FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
| FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
MySQL 模式下 ANALYZE 语句统计信息收集示例如下。
收集表 t1 的统计信息，列的桶个数为 3 个。
obclient [db_mysql]> create table t1(id int primary key, r1 int) partition by hash(id) partitions 3; Query OK, 0 rows affected (0.923 sec) obclient [db_mysql]> insert into t1 values (1,10),(2,30),(3,40); Query OK, 3 rows affected (0.250 sec) Records: 3 Duplicates: 0 Warnings: 0 obclient [db_mysql]> select * from oceanbase.__all_column_stat_v2; Empty set (0.042 sec) obclient [db_mysql]> select * from oceanbase.__all_table_stat_v2; Empty set (0.032 sec) obclient [db_mysql]> analyze table t1 update histogram on id,r1 with 3 buckets; Query OK, 0 rows affected (0.440 sec)__all_table_stat_v2保存表的统计信息，__all_column_stat_v2保存列的统计信息。收集完成后，查看统计信息。
obclient [db_mysql]> select * from oceanbase.__all_table_stat_v2; +----------------------------+----------------------------+-----------+----------+--------------+-------------+----------------------------+-----------------+---------------------+---------------+---------------+------------------+----------------------+---------+-------------+--------------+------------+-----------------+-------------+--------+--------+--------+--------+--------+--------+ | gmt_create | gmt_modified | tenant_id | table_id | partition_id | object_type | last_analyzed | sstable_row_cnt | sstable_avg_row_len | macro_blk_cnt | micro_blk_cnt | memtable_row_cnt | memtable_avg_row_len | row_cnt | avg_row_len | global_stats | user_stats | stattype_locked | stale_stats | spare1 | spare2 | spare3 | spare4 | spare5 | spare6 | +----------------------------+----------------------------+-----------+----------+--------------+-------------+----------------------------+-----------------+---------------------+---------------+---------------+------------------+----------------------+---------+-------------+--------------+------------+-----------------+-------------+--------+--------+--------+--------+--------+--------+ | 2023-07-07 09:26:49.672195 | 2023-07-07 09:26:49.672195 | 0 | 50524 | -1 | 1 | 2023-07-07 09:26:49.670654 | -1 | -1 | 0 | 0 | -1 | -1 | 3 | 32 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.672195 | 2023-07-07 09:26:49.672195 | 0 | 50524 | 0 | 2 | 2023-07-07 09:26:49.670654 | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.672195 | 2023-07-07 09:26:49.672195 | 0 | 50524 | 1 | 2 | 2023-07-07 09:26:49.670654 | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.672195 | 2023-07-07 09:26:49.672195 | 0 | 50524 | 2 | 2 | 2023-07-07 09:26:49.670654 | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | +----------------------------+----------------------------+-----------+----------+--------------+-------------+----------------------------+-----------------+---------------------+---------------+---------------+------------------+----------------------+---------+-------------+--------------+------------+-----------------+-------------+--------+--------+--------+--------+--------+--------+ 4 rows in set (0.007 sec) obclient [db_mysql]> select * from oceanbase.__all_column_stat_v2; +----------------------------+----------------------------+-----------+----------+--------------+-----------+-------------+----------------------------+--------------+----------+-----------+-------------+-----------+-------------+---------+----------------------------------------------------------------+----------------------------+-------------+----------+------------+----------------+--------------+------------+--------+--------+--------+--------+--------+--------+ | gmt_create | gmt_modified | tenant_id | table_id | partition_id | column_id | object_type | last_analyzed | distinct_cnt | null_cnt | max_value | b_max_value | min_value | b_min_value | avg_len | distinct_cnt_synopsis | distinct_cnt_synopsis_size | sample_size | density | bucket_cnt | histogram_type | global_stats | user_stats | spare1 | spare2 | spare3 | spare4 | spare5 | spare6 | +----------------------------+----------------------------+-----------+----------+--------------+-----------+-------------+----------------------------+--------------+----------+-----------+-------------+-----------+-------------+---------+----------------------------------------------------------------+----------------------------+-------------+----------+------------+----------------+--------------+------------+--------+--------+--------+--------+--------+--------+ | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | -1 | 16 | 1 | 2023-07-07 09:26:49.693145 | 3 | 0 | 3 | 04053F0003 | 1 | 04053F0001 | 16 | 789C636018057800F3403B607003C68176006D01D3403B80F60000102B0007 | 62 | 3 | 0.166667 | 3 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | -1 | 17 | 1 | 2023-07-07 09:26:49.693145 | 3 | 0 | 40 | 04053F0028 | 10 | 04053F000A | 16 | 789C63601805A3807E80112B93481DA380EA000008730004 | 48 | 3 | 0.166667 | 3 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | 0 | 16 | 2 | 2023-07-07 09:26:49.693145 | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | 0 | 17 | 2 | 2023-07-07 09:26:49.693145 | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | 1 | 16 | 2 | 2023-07-07 09:26:49.693145 | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | 1 | 17 | 2 | 2023-07-07 09:26:49.693145 | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | 2 | 16 | 2 | 2023-07-07 09:26:49.693145 | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 2023-07-07 09:26:49.753303 | 2023-07-07 09:26:49.753303 | 0 | 50524 | 2 | 17 | 2 | 2023-07-07 09:26:49.693145 | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | +----------------------------+----------------------------+-----------+----------+--------------+-----------+-------------+----------------------------+--------------+----------+-----------+-------------+-----------+-------------+---------+----------------------------------------------------------------+----------------------------+-------------+----------+------------+----------------+--------------+------------+--------+--------+--------+--------+--------+--------+ 8 rows in set (0.001 sec)使用兼容 Oracle 模式下的语法收集表 t1 的统计信息
obclient [db_mysql]> alter system set enable_sql_extension = true; Query OK, 0 rows affected (0.159 sec) obclient [db_mysql]> analyze table t1 compute statistics for all columns size 3; Query OK, 0 rows affected (0.384 sec)
