云数据库OceanBase入门教程 OceanBase LIMIT

2024-02-26 开发教程云数据库OceanBase入门教程匿名 3℃

LIMIT 算子用于限制数据输出的行数，与 MySQL 的 LIMIT 算子功能相同。

在 OceanBase 数据库的 MySQL 模式中处理含有 LIMIT 的 SQL 时，SQL 优化器都会为其生成一个 LIMIT 算子，但在一些特殊场景不会给与分配，例如 LIMIT 可以下压到基表的场景，就没有分配的必要性。

而对于 OceanBase 数据库的 Oracle 模式，以下两种场景会为其分配 LIMIT 算子：

ROWNUM 经过 SQL 优化器改写生成
为了兼容 Oracle12c 的 FETCH 功能

MySQL 模式含有 LIMIT 的 SQL 场景

示例 1：OceanBase 数据库的 MySQL 模式含有 LIMIT 的 SQL 场景

obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)
obclient>CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)
Q1:
obclient>EXPLAIN SELECT t1.c1 FROM t1,t2 LIMIT 1 OFFSET 1\G;
*************************** 1. row ***************************
Query Plan:
| =====================================================
|ID|OPERATOR                   |NAME|EST. ROWS|COST |
-----------------------------------------------------
|0 |LIMIT                      |    |1        |39   |
|1 | NESTED-LOOP JOIN CARTESIAN|    |2        |39   |
|2 |  TABLE SCAN               |t1  |1        |36   |
|3 |  TABLE SCAN               |t2  |100000   |59654|
=====================================================
Outputs & filters:
-------------------------------------
  0 - output([t1.c1]), filter(nil), limit(1), offset(1)
  1 - output([t1.c1]), filter(nil),
      conds(nil), nl_params_(nil)
  2 - output([t1.c1]), filter(nil),
      access([t1.c1]), partitions(p0)
  3 - output([t2.__pk_increment]), filter(nil),
      access([t2.__pk_increment]), partitions(p0)
Q2:
obclient>EXPLAIN SELECT * FROM t1 LIMIT 2\G;
*************************** 1. row ***************************
Query Plan:
| ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |2        |37  |
===================================
Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0),
      limit(2), offset(nil)

上述示例中，Q1 查询的执行计划展示中的 outputs & filters 详细列出了 LIMIT 算子的输出信息如下：

信息名称	含义
output	该算子输出的表达式。
filter	该算子上的过滤条件。由于示例中 LIMIT 算子没有设置 filter，所以为 nil。
limit	限制输出的行数，是一个常量。
offset	距离当前位置的偏移行数，是一个常量。由于示例中的 SQL 中不含有 offset，因此生成的计划中为 nil。

Q2 查询的执行计划展示中，虽然 SQL 中含有 LIMIT，但是并未分配 LIMIT 算子，而是将相关表达式下压到了 TABLE SCAN 算子上，这种下压 LIMIT 行为是 SQL 优化器的一种优化方式，详细信息请参见 TABLE SCAN。

Oracle 模式含有 COUNT 的 SQL 改写为 LIMIT 场景

由于 Oracle 模式含有 COUNT 的 SQL 改写为 LIMIT 场景在 COUNT 算子章节已经有过相关介绍，详细信息请参见 COUNT。

Oracle 模式含有 FETCH 的 SQL 场景

示例 2：OceanBase 数据库的 Oracle 模式含有 FETCH 的 SQL 场景

obclient>CREATE TABLE T1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)
obclient>CREATE TABLE T1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)
Q3:
obclient>EXPLAIN SELECT * FROM t1,t2 OFFSET 1 ROWS
           FETCH NEXT 1 ROWS ONLY\G;
*************************** 1. row ***************************
Query Plan:
| =====================================================
|ID|OPERATOR                   |NAME|EST. ROWS|COST |
-----------------------------------------------------
|0 |LIMIT                      |    |1        |238670   |
|1 | NESTED-LOOP JOIN CARTESIAN|    |2        |238669   |
|2 |  TABLE SCAN               |T1  |1        |36   |
|3 |  MATERIAL               |    |100000  |238632   |
|4 |  TABLE SCAN               |T2  |100000   |64066|
=====================================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C1], [T1.C2], [T2.C1], [T2.C2]), filter(nil), limit(?), offset(?)
  1 - output([T1.C1], [T1.C2], [T2.C1], [T2.C2]), filter(nil),
      conds(nil), nl_params_(nil)
  2 - output([T1.C1], [T1.C2]), filter(nil),
      access([T1.C1], [T1.C2]), partitions(p0)
  3 - output([T2.C1], [T2.C2]), filter(nil)
  4 - output([T2.C1], [T2.C2]), filter(nil),
      access([T2.C1], [T2.C2]), partitions(p0)
 Q4:
obclient>EXPLAIN SELECT * FROM t1 FETCH NEXT 1 ROWS ONLY\G;
*************************** 1. row ***************************
Query Plan:
 | ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|T1  |1        |37  |
===================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C1], [T1.C2]), filter(nil),
      access([T1.C1], [T1.C2]), partitions(p0),
      limit(?), offset(nil)
 Q5:
obclient>EXPLAIN SELECT * FROM t2 ORDER BY c1 FETCH NEXT 10
            PERCENT ROW WITH TIES\G;
*************************** 1. row ***************************
Query Plan:
| =======================================
|ID|OPERATOR    |NAME|EST. ROWS|COST  |
---------------------------------------
|0 |LIMIT       |    |10000    |573070|
|1 | SORT       |    |100000   |559268|
|2 |  TABLE SCAN|T2  |100000   |64066 |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([T2.C1], [T2.C2]), filter(nil), limit(nil), offset(nil), percent(?), with_ties(true)
  1 - output([T2.C1], [T2.C2]), filter(nil), sort_keys([T2.C1, ASC])
  2 - output([T2.C1], [T2.C2]), filter(nil),
      access([T2.C1], [T2.C2]), partitions(p0)

上述示例中，Q3 和 Q4 的查询的执行计划展示中，与之前 MySQL 模式的 Q1 和 Q2 查询基本相同，这是因为 Oracle 12c 的 FETCH 功能和 MySQL 的 LIMIT 功能类似，两者的区别如 Q5 执行计划展示中所示。

执行计划展示中的 outputs & filters 详细列出了 LIMIT 算子的输出信息如下：

信息名称	含义
output	该算子输出的表达式。
filter	该算子上的过滤条件。由于示例中 LIMIT 算子没有设置 filter，所以为 nil。
limit	限制输出的行数，是一个常量。
offset	距离当前位置的偏移行数，是一个常量。
percent	按照数据总行数的百分比输出，是一个常量。
with_ties	是否在排序后的将最后一行按照等值一起输出。例如，要求输出最后一行，但是排序之后有两行的值都为 1，如果设置了最后一行按照等值一起输出，那么这两行都会被输出。

以上 LIMIT 算子的新增的计划展示属性，都是在 Oracle 模式下的 FETCH 功能特有的，不影响 MySQL 模式计划。关于 Oracle12c 的 FETCH 语法的详细信息，请参见 Oracle 12c Fetch Rows。

上一节： OceanBase SORT

下一节： OceanBase FOR UPDATE

"云数据库OceanBase入门教程"的目录

云数据库OceanBase入门教程 OceanBase LIMIT

MySQL 模式含有 LIMIT 的 SQL 场景

Oracle 模式含有 COUNT 的 SQL 改写为 LIMIT 场景

Oracle 模式含有 FETCH 的 SQL 场景