首页 » MySQL, OceanBase, ORACLE 9i-23c, PostgreSQL/GaussDB, 达梦 » Oracle、MySQL、PostgreSQL/openGauss、达梦、OceanBase数据库比较系列(十六): Index scan MIN/MAX
Oracle、MySQL、PostgreSQL/openGauss、达梦、OceanBase数据库比较系列(十六): Index scan MIN/MAX
在关系数据库中常见的一种需求统计表的记录的最大值或最小值，SQL中使用max min，为了最佳效率通常希望可以在列上创建索引，减少表段的IO量，如果可以可以使用更佳的执行计划如直接访问索引的头和尾（btree index的有序结构），减少index 块的访问，我们对比一下几款数据库在该方面的能力。
Oracle
SQL> create table test100 (id int,name varchar2(100), addr varchar2(1000));
Table created.
SQL> insert into test100 select rownum,'anbob.com',lpad('x',800,'x') from dual connect by rownum<=10000; 10000 rows created. SQL> commit;
Commit complete.
SQL> create index idx_test100_id on test100(id);
Index created.
SQL> @gts test100
Gather Table Statistics for table test100...
SQL> explain plan for select min(id),max(id) from test100;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3286768851
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 341 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| TEST100 | 10000 | 40000 | 341 (0)| 00:00:01 |
------------------------------------------------------------------------------
9 rows selected.
SQL> alter table test100 modify id not null;
Table altered.
SQL> explain plan for select min(id),max(id) from test100;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 473523357
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FAST FULL SCAN| IDX_TEST100_ID | 10000 | 40000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
9 rows selected.
SQL> explain plan for select min(id) from test100;
Explained.
SQL_ID: 0060hdbv1mrhv
SQL> @x2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 1205458100
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_TEST100_ID | 1 | 4 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
9 rows selected.
SQL_ID: ff5spha9pjuyu
SQL> explain plan for select max(id) from test100;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1205458100
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_TEST100_ID | 1 | 4 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
9 rows selected.
SQL_ID: ff5spha9pjuyu
Note:
在oracle中使用max和min的sql语法当同一查询时，使用的是全索引快扫（多块读无序）,但是分别查询max或min时可以使用INDEX FULL SCAN (MIN/MAX)一种多块读但是使用了(MIN/MAX)字样区分，有做索引的快速定位取头或尾(可以配合其它event跟踪io读)，MAX与min执行计划一样。
MySQL
MYSQL_root@127.0.0.1 [anbob]> create table test100 (id int,name varchar(100), addr varchar(1000));
Query OK, 0 rows affected (0.21 sec)
MYSQL_root@127.0.0.1 [anbob]> insert into test100
-> WITH RECURSIVE cte (n) AS
-> (
-> SELECT 1
-> UNION ALL
-> SELECT n + 1 FROM
-> cte WHERE n < 10000) -> SELECT n,concat('anbob',n),lpad(n,800,'*') name FROM cte;
Query OK, 10000 rows affected (2.21 sec)
Records: 10000 Duplicates: 0 Warnings: 0
MYSQL_root@127.0.0.1 [anbob]> create index idx_test100_id on test100(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
MYSQL_root@127.0.0.1 [anbob]> analyze table test100;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| anbob.test100 | analyze | status | OK |
+---------------+---------+----------+----------+
1 row in set (0.06 sec)
MYSQL_root@127.0.0.1 [anbob]> select min(id),max(id) from test100 ;
+---------+---------+
| min(id) | max(id) |
+---------+---------+
| 1 | 10000 |
+---------+---------+
1 row in set (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> explain select min(id),max(id) from test100 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> explain format=tree select min(id),max(id) from test100;
+-----------------------------------+
| EXPLAIN |
+-----------------------------------+
| -> Rows fetched before execution
|
+-----------------------------------+
1 row in set (0.00 sec)
Note：
MySQL算是一个另类，在无条件max与min时都有优化，执行计划中并未显示对象而是Select tables optimized away ，用内部统计信息直接取代了扫描直接返回.
OpenGauss DB
openGauss=# create table test100 (id int,name varchar2(100), addr varchar2(1000));
CREATE TABLE
openGauss=# insert into test100 select x,'anbob.com',lpad('x',800,'x') from generate_series(1,10000) a(x);
INSERT 0 10000
openGauss=# create index idx_test100_id on test100(id);
CREATE INDEX
openGauss=# explain select min(id),max(id) from test100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Result (cost=0.95..0.96 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.48 rows=1 width=4)
-> Index Only Scan using idx_test100_id on test100 (cost=0.00..4742.38 rows=9950 width=4)
Index Cond: (id IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit (cost=0.00..0.48 rows=1 width=4)
-> Index Only Scan Backward using idx_test100_id on test100 (cost=0.00..4742.38 rows=9950 width=4)
Index Cond: (id IS NOT NULL)
(9 rows)
openGauss=# explain select max(id) from test100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Result (cost=0.48..0.49 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.48 rows=1 width=4)
-> Index Only Scan Backward using idx_test100_id on test100 (cost=0.00..4742.38 rows=9950 width=4)
Index Cond: (id IS NOT NULL)
(5 rows)
openGauss=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
(openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
Note:
在opengauss数据库中对于max与min同时查询时，自动拆成了2个子计划，并且可以看到有LIMIT关键字使用了索引的头/尾快速返回。 max的执行计划与min有Backward 索引方向的区分。朋友说之前基于pg_class创建的临时表，查询name列没有使用这种索引的优化访问，下面测试一下是否是字符串有限制?
openGauss=# \d test100
Table "public.test100"
Column | Type | Modifiers
--------+-------------------------+-----------
id | integer |
name | character varying(100) |
addr | character varying(1000) |
Indexes:
"idx_test100_id" btree (id) TABLESPACE pg_default
openGauss=# create index idx_test100_name on test100(name);
CREATE INDEX
openGauss=# explain select max(name) from test100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Result (cost=0.15..0.16 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.15 rows=1 width=10)
-> Index Only Scan Backward using idx_test100_name on test100 (cost=0.00..1454.25 rows=10000 width=10)
Index Cond: (name IS NOT NULL)
(5 rows)
openGauss=# \d pg_class
Table "pg_catalog.pg_class"
Column | Type | Modifiers
------------------+------------------+-----------
relname | name | not null
relnamespace | oid | not null
reltype | oid | not null
reloftype | oid | not null
relowner | oid | not null
relam | oid | not null
relfilenode | oid | not null
openGauss=# alter table test100 add newname NAME;
ALTER TABLE
openGauss=# update test100 set newname=name;
UPDATE 10000
openGauss=# create index idx_test100 on test100(newname);
CREATE INDEX
openGauss=# explain select max(newname) from test100;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=2662.00..2662.01 rows=1 width=96)
-> Seq Scan on test100 (cost=0.00..2562.00 rows=20000 width=64)
(2 rows)
openGauss=# \d test100
Table "public.test100"
Column | Type | Modifiers
---------+-------------------------+-----------
id | integer |
name | character varying(100) |
addr | character varying(1000) |
newname | name |
Indexes:
"idx_test100" btree (newname) TABLESPACE pg_default
"idx_test100_id" btree (id) TABLESPACE pg_default
"idx_test100_name" btree (name) TABLESPACE pg_default
openGauss=# \dT+ NAME
List of data types
Schema | Name | Internal name | Size | Elements | Access privileges | Description
------------+------+---------------+------+----------+-------------------+---------------------------------------------
pg_catalog | name | name | 64 | | | 63-byte type for storing system identifiers
(1 row)
Note:
字符串类型的索引max min也是可以使用索引的优化访问，但是对于“NAME”这种pg的特殊数据类型，虽然是一组64位的字符串，但在使用max时默认没有使用索引。
Postgresql
[local]:5432 postgres@postgres=# create table test100 (id int,name varchar(100), addr varchar(1000));
CREATE TABLE
[local]:5432 postgres@postgres=# insert into test100 select x,'anbob.com',lpad('x',800,'x') from generate_series(1,10000) a(x);
INSERT 0 10000
[local]:5432 postgres@postgres=# create index idx_test100_id on test100(id);
CREATE INDEX
[local]:5432 postgres@postgres=# explain select min(id),max(id),max(id) from test100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Result (cost=1.52..1.53 rows=1 width=12)
InitPlan 1 (returns $0)
-> Limit (cost=0.29..0.76 rows=1 width=4)
-> Index Only Scan using idx_test100_id on test100 (cost=0.29..4742.41 rows=9950 width=4)
Index Cond: (id IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit (cost=0.29..0.76 rows=1 width=4)
-> Index Only Scan Backward using idx_test100_id on test100 test100_1 (cost=0.29..4742.41 rows=9950 width=4)
Index Cond: (id IS NOT NULL)
(9 rows)
[local]:5432 postgres@postgres=# alter table test100 add newname NAME;
ALTER TABLE
[local]:5432 postgres@postgres=# update test100 set newname=name;
UPDATE 10000
[local]:5432 postgres@postgres=# create index idx_test100 on test100(newname);
CREATE INDEX
[local]:5432 postgres@postgres=# explain select max(newname) from test100;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=2512.00..2512.01 rows=1 width=32)
-> Seq Scan on test100 (cost=0.00..2462.00 rows=10000 width=64)
(2 rows)
[local]:5432 postgres@postgres=# select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39.0.1), 64-bit
(1 row)
Note:
postgresql的表现与opengauss相同，或因为og是pg的衍生品。
达梦8
SQL> create table test200 as select * from dba_objects; 操作已执行 已用时间: 273.462(毫秒). 执行号:501. SQL> create index idx_test200 on test200(object_id); 操作已执行 已用时间: 106.390(毫秒). 执行号:502. SQL> explain select min(object_id) from test200; 1 #NSET2: [1, 1, 30] 2 #PRJT2: [1, 1, 30]; exp_num(1), is_atom(FALSE) 3 #FAGR2: [1, 1, 30]; sfun_num(1), IDX_TEST200 已用时间: 0.971(毫秒). 执行号:0. SQL> explain select min(object_name) from test200; 1 #NSET2: [1, 1, 48] 2 #PRJT2: [1, 1, 48]; exp_num(1), is_atom(FALSE) 3 #AAGR2: [1, 1, 48]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 4 #CSCN2: [1, 869, 48]; INDEX33555575(TEST200) 已用时间: 0.445(毫秒). 执行号:0. SQL> explain select min(object_id),max(object_id) from test200; 1 #NSET2: [1, 1, 30] 2 #PRJT2: [1, 1, 30]; exp_num(2), is_atom(FALSE) 3 #FAGR2: [1, 1, 30]; sfun_num(2), IDX_TEST200 已用时间: 1.159(毫秒). 执行号:0.
Note:
达梦的数据库也可以在min和max时执行索引优化，并且是一步的子查询， 但是要吐槽一下，它的执行计划阅读性太差，不熟悉的还要对照”密码本”.
达梦的执行计划列表:
NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。
PRJT 是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。
SLCT 是关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。
AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。
FAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。表示可利用索引或统计数据快速求出结果
HAGR 用于分组列没有索引只能走全表扫描的分组聚集。
SAGR 用于分组列是有序的情况下，可以使用流分组聚集，SAGR2 性能优于 HAGR2
BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。
CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。
SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。
CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。
SSCN 是索引全扫描，不需要扫描表。
MERGE JOIN 归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并
LKUP：2 级索引在主索引中查找字段，配合 SEEK 使用
NLIJ: NEST LOOP INDEX JOIN
HI：HASH 连接，计算代价时，用于所有的 HASH，包括 HASH 半连接HI_SEARCH：这是在 hash 表探测的代价
NL：NEST LOOP JOIN
FLT：过滤，FLT_CPU 也用于任何表达式计算的代价评估
每个计划的操作符都是一个三元组。
1．第一个数字代表的是该操作需要的代价；
2．第二个数字代表估算该操作输出的行数；
3．第三个数字表示每行记录的字节数。
Oceanbase for ORACLE租户
obclient [ANBOB]> create table test100 (id int,name varchar2(100), addr varchar2(1000));
Query OK, 0 rows affected (0.102 sec)
obclient [ANBOB]> insert into test100 select rownum,'anbob.com',lpad('x',800,'x') from dual connect by rownum<=10000;
Query OK, 10000 rows affected (0.282 sec)
Records: 10000 Duplicates: 0 Warnings: 0
obclient [ANBOB]> create index idx_test100_id on test100(id);
Query OK, 0 rows affected (0.961 sec)
obclient [ANBOB]> explain select min(id),max(id) from test100;
+------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| ===========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------------
|0 |SCALAR GROUP BY| |1 |4482|
|1 | TABLE SCAN |TEST100(IDX_TEST100_ID)|10000 |3869|
===========================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_MIN(TEST100.ID)], [T_FUN_MAX(TEST100.ID)]), filter(nil),
group(nil), agg_func([T_FUN_MIN(TEST100.ID)], [T_FUN_MAX(TEST100.ID)])
1 - output([TEST100.ID]), filter(nil),
access([TEST100.ID]), partitions(p0)
|
obclient [ANBOB]> insert into test100 select rownum,'anbob.com',lpad('x',800,'x') from dual connect by rownum<=100000;
Query OK, 100000 rows affected (3.081 sec)
Records: 100000 Duplicates: 0 Warnings: 0
obclient [ANBOB]> desc test100;
+-------+----------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+----------------+------+-----+---------+-------+
| ID | NUMBER(38) | YES | MUL | NULL | NULL |
| NAME | VARCHAR2(100) | YES | NULL | NULL | NULL |
| ADDR | VARCHAR2(1000) | YES | NULL | NULL | NULL |
+-------+----------------+------+-----+---------+-------+
3 rows in set (0.005 sec)
obclient [ANBOB]> explain select min(id),max(id) from test100;
+-------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------+
| ============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------
|0 |SCALAR GROUP BY| |1 |43161|
|1 | TABLE SCAN |TEST100|10000 |42548|
============================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_MIN(TEST100.ID)], [T_FUN_MAX(TEST100.ID)]), filter(nil),
group(nil), agg_func([T_FUN_MIN(TEST100.ID)], [T_FUN_MAX(TEST100.ID)])
1 - output([TEST100.ID]), filter(nil),
access([TEST100.ID]), partitions(p0)
|
+---------------------------------------------
obclient [ANBOB]> explain select id from test100 where id=1;
+------------------------------------------------------+
| Query Plan |------------------------------------------------------------+
| ======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
------------------------------------------------------
|0 |TABLE SCAN|TEST100(IDX_TEST100_ID)|1 |46 |
======================================================
Outputs & filters:
-------------------------------------
0 - output([TEST100.ID]), filter(nil),
access([TEST100.ID]), partitions(p0)
|
+------------------------------------------------------
obclient [ANBOB]> explain select min(id),max(id) from test100 \G
*************************** 1. row ***************************
Query Plan: ===========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------------
|0 |SCALAR GROUP BY| |1 |4482|
|1 | TABLE SCAN |TEST100(IDX_TEST100_ID)|10000 |3869|
===========================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_MIN(TEST100.ID)], [T_FUN_MAX(TEST100.ID)]), filter(nil),
group(nil), agg_func([T_FUN_MIN(TEST100.ID)], [T_FUN_MAX(TEST100.ID)])
1 - output([TEST100.ID]), filter(nil),
access([TEST100.ID]), partitions(p0)
1 row in set (0.138 sec)
obclient [ANBOB]> explain select max(id) from test100 \G
*************************** 1. row ***************************
Query Plan: ===================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-------------------------------------------------------------------
|0 |SCALAR GROUP BY| |1 |46 |
|1 | SUBPLAN SCAN |VIEW1 |1 |46 |
|2 | TABLE SCAN |TEST100(IDX_TEST100_ID,Reverse)|1 |46 |
===================================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_MAX(VIEW1.ID)]), filter(nil),
group(nil), agg_func([T_FUN_MAX(VIEW1.ID)])
1 - output([VIEW1.ID]), filter(nil),
access([VIEW1.ID])
2 - output([TEST100.ID]), filter(nil),
access([TEST100.ID]), partitions(p0),
limit(1), offset(nil)
1 row in set (0.066 sec)
obclient [ANBOB]> explain select min(id) from test100 \G
*************************** 1. row ***************************
Query Plan: ===========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------------
|0 |SCALAR GROUP BY| |1 |46 |
|1 | SUBPLAN SCAN |VIEW1 |1 |46 |
|2 | TABLE SCAN |TEST100(IDX_TEST100_ID)|1 |46 |
===========================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_MIN(VIEW1.ID)]), filter(nil),
group(nil), agg_func([T_FUN_MIN(VIEW1.ID)])
1 - output([VIEW1.ID]), filter(nil),
access([VIEW1.ID])
2 - output([TEST100.ID]), filter(nil),
access([TEST100.ID]), partitions(p0),
limit(1), offset(nil)
1 row in set (0.074 sec)
Note：
在oceanbase中如果同时查询max和min也是没有使用index 优化访问，不确认什么原因开始是全表扫，后来又变成了索引扫。 但是把max或min分别查询是可以使用索引优化访问，如可以看到limit 1 offset关键字， 同时max有reverse关键字区分。
TiDB
在tidb中select max(a) from t，会 转换为
select max(a) from (select a from t where a is not null order by a desc limit 1) t;
而同时出现max和min,如select max(a) – min(a) from t,会和pg一样转换为2个子查询的笛卡尔积,同使分别使用上面的优化规则,最终重写为
select max_a - min_a
from
(select max(a) as max_a from (select a from t where a is not null order by a desc limit 1) t) t1,
(select min(a) as min_a from (select a from t where a is not null order by a asc limit 1) t) t2;
最后得到的执行计划：
mysql> explain select max(a)-min(a) from t; +------------------------------------+---------+-----------+-------------------------+-------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+---------+-----------+-------------------------+-------------------------------------+ | Projection_17 | 1.00 | root | | minus(Column#4, Column#5)->Column#6 | | └─HashJoin_18 | 1.00 | root | | CARTESIAN inner join | | ├─StreamAgg_45(Build) | 1.00 | root | | funcs:min(test.t.a)->Column#5 | | │ └─Limit_49 | 1.00 | root | | offset:0, count:1 | | │ └─IndexReader_59 | 1.00 | root | | index:Limit_58 | | │ └─Limit_58 | 1.00 | cop[tikv] | | offset:0, count:1 | | │ └─IndexFullScan_57 | 1.00 | cop[tikv] | table:t, index:idx_a(a) | keep order:true, stats:pseudo | | └─StreamAgg_24(Probe) | 1.00 | root | | funcs:max(test.t.a)->Column#4 | | └─Limit_28 | 1.00 | root | | offset:0, count:1 | | └─IndexReader_38 | 1.00 | root | | index:Limit_37 | | └─Limit_37 | 1.00 | cop[tikv] | | offset:0, count:1 | | └─IndexFullScan_36 | 1.00 | cop[tikv] | table:t, index:idx_a(a) | keep order:true, desc, stats:pseudo | +------------------------------------+---------+-----------+-------------------------+----------------------
https://docs.pingcap.com/zh/tidb/stable/max-min-eliminate
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。