--- title: OceanBase 数据库 MySQL 模式手工收集统计信息的方法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 OceanBase 数据库 MySQL 模式手工收集统计信息的方法相关的常见问题和使用技巧，帮助您快速解决 OceanBase 数据库 MySQL 模式手工收集统计信息的方法的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库 MySQL 模式手工收集统计信息的方法 更新时间：2026-05-14 09:21 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x 内容类型：How-to 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) ``` Previous [开启 SPM 功能影响 DDL 执行或导致 schema 内存占用大或影响 OBServer 重启](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000008223) Next [only px support this plan 报错处理方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209996) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线