OB的索引选择效果和pdf不符

[AntTech_QXSWJC]

执行explain extended select * from t1 where a = 1 order by b;时和给的pdf不一样，我执行的没有pruned_index_name[idx2]

[秃蛙]

我使用4.0版本，空表执行符合你文档上执行计划，我看你是填充数据了，优化器可能会选择更优的执行策略，你试试空表验证下。

[AntTech_QXSWJC]

@秃蛙

可能会选择更优的执行策

表里面没有数据，我是复制的上面的创表和查询语句

[秃蛙]

我看截图扫描到10w行数据。

[AntTech_QXSWJC]

那我的表中确实没数据，我该如何解决

[秃蛙]

新建个t2表，测试下，如果还是扫描10W行，可以提供下执行语句信息

[AntTech_QXSWJC]

新建 Microsoft Word 文档.docx (15.2 KB)

[秃蛙]

系统租户执行
select * from oceanbase.__all_server；

select * from oceanbase.CDB_TABLES where table_name =‘t2’;

[AntTech_QXSWJC]

[AntTech_QXSWJC]

[秃蛙]

我们测试最新版本是正常的，这个版本我需要联系下企业版人员帮确认下。稍等

[AntTech_QXSWJC]

好的，麻烦了

[秃蛙]

已确认，4.x之前版本会有建表后统计信息不准的情况，导致你这边执行计划不符合预期。
可以插入一条数据后执行 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a, b, c WITH 30 BUCKETS;进行修正。你试下

[AntTech_QXSWJC]

MySQL [mydb]> drop table t1;
Query OK, 0 rows affected (1.316 sec)

MySQL [mydb]> drop table t2;
Query OK, 0 rows affected (0.208 sec)

MySQL [mydb]> create table t1(a int, b int, c int, unique key idx1(a, b), key idx2(b));
Query OK, 0 rows affected (0.833 sec)

MySQL [mydb]> insert into t1 values (1,1,1);
Query OK, 1 row affected (0.164 sec)

MySQL [mydb]> ANALYZE TABLE t1 UPDATE HISTOGRAM ON a, b, c WITH 30 BUCKETS;
Query OK, 0 rows affected (0.391 sec)

MySQL [mydb]> explain extended select * from t1 where a = 1 order by b;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =======================================
|ID|OPERATOR |NAME |EST. ROWS|COST|

|0 |TABLE SCAN|t1(idx2)|1 |4 |

Outputs & filters:

0 - output([t1.a(0x2b835acc0d40)], [t1.b(0x2b835acc10f0)], [t1.c(0x2b835acc13e0)]), filter([t1.a(0x2b835acc0d40) = 1(0x2b835acc0620)]),
access([t1.a(0x2b835acc0d40)], [t1.b(0x2b835acc10f0)], [t1.c(0x2b835acc13e0)]), partitions(p0),
is_index_back=true, filter_before_indexback[false],
range_key([t1.b(0x2b835acc10f0)], [t1.__pk_increment(0x2b835accb7b0)]), range(MIN,MIN ; MAX,MAX)always true

Used Hint:

/*+
*/

Outline Data:

/*+
BEGIN_OUTLINE_DATA
INDEX(@“SEL$1” “mydb.t1”@“SEL$1” “idx2”)
END_OUTLINE_DATA
*/

Plan Type:

LOCAL

Optimization Info:

t1:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx1,idx2], unstable_index_name[t1], estimation info[table_id:1101710651081566, (table_type:1, version:0-1680011591365848-1680011591365848, logical_rc:0, physical_rc:0), (table_type:7, version:1680011591365848-1680011591365848-1680011897491767, logical_rc:1, physical_rc:1), (table_type:5, version:1680011591365848-1680011591365848-1680011897491767, logical_rc:0, physical_rc:0), (table_type:0, version:1680011897491767-1680011897491767-9223372036854775807, logical_rc:0, physical_rc:0)]
Parameters

|
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.265 sec)

[AntTech_QXSWJC]

数据确实修正了，但是结果还是不一样。

[秃蛙]

@AntTech_QXSWJC

vaiable_index_name[idx1,i

收到，明天我找下对应开发同学看下。

[AntTech_QXSWJC]

好的，麻烦了

[秃蛙]

你可以试试使用4.x版本，这个可能是老版本的bug问题。

[AntTech_QXSWJC]

那如果公司现在用这个版本，是要换成4.x吗？

[秃蛙]

你这个是企业版本的，问题偶现，可以找企业版的交付咨询评估下。