--- title: 优化器不使用索引首列的直方图信息进行估算-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 优化器不使用索引首列的直方图信息进行估算相关的常见问题和使用技巧，帮助您快速解决 优化器不使用索引首列的直方图信息进行估算的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 优化器不使用索引首列的直方图信息进行估算 更新时间：2026-05-15 09:06 适用版本： V3.2.x 内容类型：Troubleshoot ## 适用版本 OceanBase 数据库 V3.2 版本。 ## 问题描述 因为表中的列存在数据倾斜，导致优化器不能选择最优的路径，即使对该列搜集了直方图的统计信息，依然不能选择想要的路径。 ## 问题诊断 以下为示例化说明估行细节。 示例表结构以及示例数据如下。 ```shell obclient [db_mysql]> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int(11) NOT NULL, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `t2_i_c2` (`c2`) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'lz4_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 partition by hash(c1) (partition p0, partition p1, partition p2, partition p3, partition p4, partition p5) 1 row in set (0.002 sec) ``` ```shell obclient [db_mysql]> select * from t2; +----+------+ | c1 | c2 | +----+------+ | 2 | 1 | | 8 | ab | | 5 | ab | | 11 | 1 | | 1 | 1 | | 7 | ab | | 4 | 1 | | 10 | 1 | | 6 | 1 | | 12 | 1 | | 3 | 1 | | 9 | 1 | +----+------+ 12 rows in set (0.015 sec) ``` 对列 c2 进行检索，查看执行计划结果：估算的行数不对，使用的估行算法为 local_storage，即存储层估行。 ```shell obclient [db_mysql]> explain extended select * from t2 where c2 = '1'\G; *************************** 1. row *************************** Query Plan: ======================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------- |0 |PX COORDINATOR | |12 |47 | |1 | EXCHANGE OUT DISTR |:EX10000 |12 |46 | |2 | PX PARTITION ITERATOR| |12 |46 | |3 | TABLE SCAN |t2(t2_i_c2)|12 |46 | ======================================================= Outputs & filters: ------------------------------------- 0 - output([INTERNAL_FUNCTION(t2.c1(0x7f7ca82212b0), t2.c2(0x7f7ca8222280))(0x7f7ca8233660)]), filter(nil) 1 - output([INTERNAL_FUNCTION(t2.c1(0x7f7ca82212b0), t2.c2(0x7f7ca8222280))(0x7f7ca8233660)]), filter(nil), dop=1 2 - output([t2.c1(0x7f7ca82212b0)], [t2.c2(0x7f7ca8222280)]), filter(nil), force partition granule. 3 - output([t2.c1(0x7f7ca82212b0)], [t2.c2(0x7f7ca8222280)]), filter(nil), access([t2.c1(0x7f7ca82212b0)], [t2.c2(0x7f7ca8222280)]), partitions(p[0-5]), is_index_back=false, range_key([t2.c2(0x7f7ca8222280)], [t2.c1(0x7f7ca82212b0)]), range(1,MIN ; 1,MAX), range_cond([t2.c2(0x7f7ca8222280) = '1'(0x7f7ca8221b60)]) Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "db_mysql.t2"@"SEL$1" "t2_i_c2") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t2:table_rows:12, physical_range_rows:12, logical_range_rows:12, index_back_rows:0, output_rows:12, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t2_i_c2], pruned_index_name[t2], estimation info[table_id:1100611139454044, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:2, physical_rc:2)] Parameters ------------------------------------- 1 row in set (0.003 sec) obclient [db_mysql]> ``` 手动搜集列 c2 的直方图信息。 ```shell obclient [db_mysql]> analyze table t2 update histogram on c2 with 10 buckets; Query OK, 0 rows affected (0.181 sec) ``` 更新之后，估算的行数还是不对，没有使用到直方图，估行的算法为 basic_stat，即统计信息。 ```shell obclient [db_mysql]> explain extended select * from t2 where c2 = '1'\G; *************************** 1. row *************************** Query Plan: ======================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------- |0 |PX COORDINATOR | |6 |3 | |1 | EXCHANGE OUT DISTR |:EX10000 |6 |3 | |2 | PX PARTITION ITERATOR| |6 |3 | |3 | TABLE SCAN |t2(t2_i_c2)|6 |3 | ======================================================= Outputs & filters: ------------------------------------- 0 - output([INTERNAL_FUNCTION(t2.c1(0x7f7c7ec212b0), t2.c2(0x7f7c7ec22280))(0x7f7c7ed008e0)]), filter(nil) 1 - output([INTERNAL_FUNCTION(t2.c1(0x7f7c7ec212b0), t2.c2(0x7f7c7ec22280))(0x7f7c7ed008e0)]), filter(nil), dop=1 2 - output([t2.c1(0x7f7c7ec212b0)], [t2.c2(0x7f7c7ec22280)]), filter(nil), force partition granule. 3 - output([t2.c1(0x7f7c7ec212b0)], [t2.c2(0x7f7c7ec22280)]), filter(nil), access([t2.c1(0x7f7c7ec212b0)], [t2.c2(0x7f7c7ec22280)]), partitions(p[0-5]), is_index_back=false, range_key([t2.c2(0x7f7c7ec22280)], [t2.c1(0x7f7c7ec212b0)]), range(1,MIN ; 1,MAX), range_cond([t2.c2(0x7f7c7ec22280) = '1'(0x7f7c7ec21b60)]) t2:table_rows:12, physical_range_rows:6, logical_range_rows:6, index_back_rows:0, output_rows:6, est_method:basic_stat, optimization_method=cost_based, avaiable_index_name[t2_i_c2], pruned_index_name[t2] Parameters ------------------------------------- 1 row in set (0.018 sec) ``` 查看系统表，相关统计信息、直方图数据都是正确的。 ```shell obclient [oceanbase]> select * from __all_virtual_histogram_stat_v2 where table_id = '1100611139454043'; +-----------+------------------+--------------+-----------+--------------+----------------------------+----------------------------+-------------+---------------------------+----------------+------------------+---------------------+ | tenant_id | table_id | partition_id | column_id | endpoint_num | gmt_create | gmt_modified | object_type | endpoint_normalized_value | endpoint_value | b_endpoint_value | endpoint_repeat_cnt | +-----------+------------------+--------------+-----------+--------------+----------------------------+----------------------------+-------------+---------------------------+----------------+------------------+---------------------+ | 1001 | 1100611139454043 | -1 | 17 | 9 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 1 | -1 | '1' | 16022DFF013100 | 9 | | 1001 | 1100611139454043 | -1 | 17 | 12 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 1 | -1 | 'ab' | 16022DFF02616200 | 3 | +-----------+------------------+--------------+-----------+--------------+----------------------------+----------------------------+-------------+---------------------------+----------------+------------------+---------------------+ ``` ```shell obclient [oceanbase]> select * from __all_virtual_column_stat_v2 where table_id = '1100611139454043'; +-----------+------------------+--------------+-----------+----------------------------+----------------------------+-------------+----------------------------+--------------+----------+-----------+------------------+-----------+----------------+---------+----------------------------------------------+----------------------------+-------------+----------+------------+----------------+--------------+------------+--------+--------+--------+--------+--------+--------+ | tenant_id | table_id | partition_id | column_id | gmt_create | gmt_modified | 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 | +-----------+------------------+--------------+-----------+----------------------------+----------------------------+-------------+----------------------------+--------------+----------+-----------+------------------+-----------+----------------+---------+----------------------------------------------+----------------------------+-------------+----------+------------+----------------+--------------+------------+--------+--------+--------+--------+--------+--------+ | 1001 | 1100611139454043 | -1 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 1 | 2023-08-15 xx:xx:xx.xxxxx | 2 | 0 | 'ab' | 16022DFF02616200 | '1' | 16022DFF013100 | 17 | 789C63601805A38069A01D3048C0C80B070009D80005 | 44 | 12 | 0.041667 | 2 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 0 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 1 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 2 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 3 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 4 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 5 | 17 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | 0 | 0 | NULL | 00063FFF | NULL | 00063FFF | 0 | 789C63601805A360148C54000004000001 | 34 | -1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | +-----------+------------------+--------------+-----------+----------------------------+----------------------------+-------------+----------------------------+--------------+----------+-----------+------------------+-----------+----------------+---------+----------------------------------------------+----------------------------+-------------+----------+------------+----------------+--------------+------------+--------+--------+--------+--------+--------+--------+ 7 rows in set (0.030 sec) ``` ```shell obclient [oceanbase]> select * from __all_virtual_table_stat_v2 where table_id = '1100611139454043'; +-----------+------------------+--------------+----------------------------+----------------------------+-------------+----------------------------+-----------------+---------------------+---------------+---------------+------------------+----------------------+---------+-------------+--------------+------------+-----------------+-------------+--------+--------+--------+--------+--------+--------+ | tenant_id | table_id | partition_id | gmt_create | gmt_modified | 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 | +-----------+------------------+--------------+----------------------------+----------------------------+-------------+----------------------------+-----------------+---------------------+---------------+---------------+------------------+----------------------+---------+-------------+--------------+------------+-----------------+-------------+--------+--------+--------+--------+--------+--------+ | 1001 | 1100611139454043 | -1 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 1 | 2023-08-15 xx:xx:xx.xxxxx | -1 | -1 | 0 | 0 | -1 | -1 | 12 | 33 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 0 | 2023-08-15 xx:xx:xx.xxxxx | xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 1 | 2023-08-15 1xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 10:14:49.156106 | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 2 | 2023-08-15 1xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 3 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 4 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | | 1001 | 1100611139454043 | 5 | 2023-08-15 xx:xx:xx.xxxxx | 2023-08-15 xx:xx:xx.xxxxx | 2 | 2023-08-15 xx:xx:xx.xxxxx | -1 | -1 | 0 | 0 | -1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | +-----------+------------------+--------------+----------------------------+----------------------------+-------------+----------------------------+-----------------+---------------------+---------------+---------------+------------------+----------------------+---------+-------------+--------------+------------+-----------------+-------------+--------+--------+--------+--------+--------+--------+ 7 rows in set (0.087 sec) ``` 删除 c2 列上的索引后优化器使用了直方图信息来估行，此时估行就很准确。 ```shell obclient [db_mysql]> alter table t2 drop key t2_i_c2; Query OK, 0 rows affected (0.420 sec) obclient [db_mysql]> explain extended select * from t2 where c2 = '1'\G; *************************** 1. row *************************** Query Plan: ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |9 |6 | |1 | EXCHANGE OUT DISTR |:EX10000|9 |5 | |2 | PX PARTITION ITERATOR| |9 |5 | |3 | TABLE SCAN |t2 |9 |5 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([INTERNAL_FUNCTION(t2.c1(0x7f7cd1f4b2b0), t2.c2(0x7f7cd1f4c280))(0x7f7cd1f5d660)]), filter(nil) 1 - output([INTERNAL_FUNCTION(t2.c1(0x7f7cd1f4b2b0), t2.c2(0x7f7cd1f4c280))(0x7f7cd1f5d660)]), filter(nil), dop=1 2 - output([t2.c1(0x7f7cd1f4b2b0)], [t2.c2(0x7f7cd1f4c280)]), filter(nil), force partition granule. 3 - output([t2.c1(0x7f7cd1f4b2b0)], [t2.c2(0x7f7cd1f4c280)]), filter([t2.c2(0x7f7cd1f4c280) = '1'(0x7f7cd1f4bb60)]), access([t2.c1(0x7f7cd1f4b2b0)], [t2.c2(0x7f7cd1f4c280)]), partitions(p[0-5]), is_index_back=false, filter_before_indexback[false], range_key([t2.c1(0x7f7cd1f4b2b0)]), range(MIN ; MAX)always true Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "db_mysql.t2"@"SEL$1") END_OUTLINE_DATA */ t2:table_rows:12, physical_range_rows:12, logical_range_rows:12, index_back_rows:0, output_rows:9, est_method:basic_stat, optimization_method=cost_based, avaiable_index_name[t2] Parameters ------------------------------------- 1 row in set (0.002 sec) ``` ## 问题原因 优化器估行不准。OceanBase 数据库的优化器估行时依赖的统计信息可以从以下 3 个地方获取，其优先级为：存储层估行 > 直方图统计信息（V3.2 版本手工搜集）> 基础统计信息（合并时搜集）。 只有当谓词条件是索引首列时，存储层才能进行估行。对于索引首列，优化器是用存储层估行还是基于统计信息估行，其规则是： - case 1：没有直方图的统计信息，优化器优先使用存储层估行。 - 对于分区表且查询无法裁剪分区为某一个特定分区时，优化器会随机选择一个分区来进行存储层估行。 - case 2：如果搜集了直方图统计信息，则根据查询是否能裁剪为某一个特定分区来决定用何种方式估行。 - case 2.1：当表为非分区表，或者查询通过分区裁剪只匹配一个分区时，优化器会优先使用存储层估行。 - case 2.2：当表为分区表，查询需要访问多个分区时，优化器会优先使用基础统计信息。 所以，当索引首列上搜集了直方图信息时，目前的逻辑会无视直方图的统计信息，要么使用存储层估行，要么使用合并搜集的统计信息估行。 ## 解决方法和规避方式 ### 解决方法 OceanBase 数据库代码对于 case 2.2 进行了优化，当存在直方图信息时，优先使用直方图信息而不是基础统计信息来进行估行。 升级至问题已修复版本。目前已修复的版本 OceanBase 数据库企业版 V3.2.3 BP9 Hotfix1 (oceanbase-3.2.3.3-109010012023072721) 版本。 ### 应急规避方案 1. 删除低效的存在数据倾斜的索引。 2. 绑定 hint 来指定路径。 上一篇 [SQL 执行报错 -4119，RPC packet to send too long](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000210005) 下一篇 [MySQL 模式 Truncate/Drop 分区操作耗时很长的原因](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207709) ![有帮助](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) 咨询热线