划线反馈

优化器不使用索引首列的直方图信息进行估算

更新时间：2026-05-15 09:06

适用版本：V3.2.x内容类型：Troubleshoot

适用版本

OceanBase 数据库 V3.2 版本。

问题描述

因为表中的列存在数据倾斜，导致优化器不能选择最优的路径，即使对该列搜集了直方图的统计信息，依然不能选择想要的路径。

问题诊断

以下为示例化说明估行细节。

示例表结构以及示例数据如下。

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)

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，即存储层估行。

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 的直方图信息。

obclient [db_mysql]> analyze table t2 update histogram on c2 with 10 buckets;
Query OK, 0 rows affected (0.181 sec)

更新之后，估算的行数还是不对，没有使用到直方图，估行的算法为 basic_stat，即统计信息。

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)])

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: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)

查看系统表，相关统计信息、直方图数据都是正确的。

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 |
+-----------+------------------+--------------+-----------+--------------+----------------------------+----------------------------+-------------+---------------------------+----------------+------------------+---------------------+

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)

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 列上的索引后优化器使用了直方图信息来估行，此时估行就很准确。

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

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "db_mysql.t2"@"SEL$1")
      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:9, est_method:basic_stat, optimization_method=cost_based, avaiable_index_name[t2]
Parameters
-------------------------------------

1 row in set (0.002 sec)

obclient [db_mysql]>

问题原因

优化器估行不准。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) 版本。

应急规避方案

删除低效的存在数据倾斜的索引。
绑定 hint 来指定路径。

SQL 执行报错 -4119，RPC packet to send too long

MySQL 模式 Truncate/Drop 分区操作耗时很长的原因

OceanBase 数据库