引言

工作中有人反馈 where limit 语句的执行计划中显示的扫描行数非常大，问为什么不等于 limit row_count。

一开始以为先执行 where 后执行 limit，因此如果满足 where 条件的数据很多时，扫描行数就会很多，也就是说和 limit 没有关系。

然后很快就被打脸了，测试显示 where limit 语句的执行计划中显示的扫描行数与实际执行时差异很大，因此就有了这篇文章。

现象

时间：20231026

域名：mysql-cn-north-1.rds.jdcloud.com

版本：5.7.24

问题：where limit 语句的执行计划中扫描行数远大于 limit row_count

分析

limit 语法

语法：[LIMIT {[offset,] row_count | row_count OFFSET offset}]

显示 limit 支持三种格式：

limit row_count，其中 count 指定要返回的最大行数；
limit offset, row_count，其中 offset 偏移量，指定从哪一行开始查询，默认值为 0，表示从第一行开始查询；
limit row_count OFFSET offset，该语法用于兼容 PG 语法，实际上很少用。

offset 和 limit 的值都不能为负数，在源码里这两个属性定义的是无符号整数，并且在解析阶段就做了限制，如果为负数，直接报语法错误了。

表结构

mysql> show create table line_node_flow G
*************************** 1. row ***************************
       Table: line_node_flow
Create Table: CREATE TABLE `line_node_flow` (
  `line_node_flow_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `business_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '唯一性ID',
  `line_code` varchar(32) DEFAULT NULL COMMENT '线路编码',
  `start_node_code` varchar(32) DEFAULT NULL COMMENT '始发网点编码',
  `end_node_code` varchar(32) DEFAULT NULL COMMENT '目的网点编码',
  `product_code` varchar(32) DEFAULT NULL COMMENT '产品类型编码',
  `product_name` varchar(32) DEFAULT NULL COMMENT '产品类型名称',
  `country_id` varchar(8) DEFAULT NULL COMMENT '全国',
  `org_id` int(11) DEFAULT NULL COMMENT '机构ID',
  `province_id` int(11) DEFAULT NULL COMMENT '省份ID',
  `city_id` int(11) DEFAULT NULL COMMENT '城市ID',
  `node_code` varchar(32) DEFAULT NULL COMMENT '网点编码',
  `stowage_code` varchar(32) DEFAULT NULL COMMENT '可达网点配载编码',
  `stowage_name` varchar(32) DEFAULT NULL COMMENT '配载名称',
  `enable_time` datetime DEFAULT NULL COMMENT '失效时间',
  `disable_time` datetime DEFAULT NULL COMMENT '生效时间',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人id',
  `operator_erp` varchar(32) DEFAULT NULL COMMENT '操作人ERP',
  `operator_name` varchar(32) DEFAULT NULL COMMENT '操作人姓名',
  `operator_time` datetime DEFAULT NULL COMMENT '用户操作时间',
  `site_type` int(11) DEFAULT NULL COMMENT '配载类型',
  `role_type` int(11) DEFAULT NULL COMMENT '名单可用类型，1-可用，0-不可用',
  `yn` int(11) DEFAULT NULL COMMENT 'YesOrNo',
  `departure_frequency` varchar(32) DEFAULT NULL COMMENT '发车频次(发车频次都是1234567，代表每天都有发车 周一到周日r',
  `line_resource_id` bigint(20) DEFAULT NULL COMMENT '线路id',
  `business_relation_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联线路表的唯一性ID',
  `current_hash` bigint(20) DEFAULT NULL COMMENT '当前记录标识',
  `parent_hash` bigint(20) DEFAULT NULL COMMENT '父记录标识',
  `batch_version` bigint(20) DEFAULT NULL COMMENT '批量更新版本标识',
  `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间戳',
  PRIMARY KEY (`line_node_flow_id`),
  KEY `idx_stnode_prodcode_stowage` (`start_node_code`,`product_code`,`stowage_code`),
  KEY `idx_line_code` (`line_code`),
  KEY `idx_ids_union` (`country_id`,`org_id`,`province_id`,`city_id`,`node_code`) USING BTREE,
  KEY `idx_line_resource_id` (`line_resource_id`) USING BTREE,
  KEY `idx_stowage_code` (`stowage_code`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`),
  KEY `idx_business_relation_id` (`business_relation_id`),
  KEY `idx_business_id` (`business_id`)
) ENGINE=InnoDB AUTO_INCREM

其中：

line_node_flow_id，业务主键索引

where limit

SQL，limit 500

select 
  line_node_flow_id, 
  business_relation_id, 
  line_code, 
  start_node_code, 
  end_node_code, 
  product_code, 
  product_name, 
  create_time, 
  update_time, 
  yn, 
  operator_id, 
  operator_erp, 
  operator_name, 
  operator_time, 
  stowage_code, 
  stowage_name, 
  departure_frequency, 
  enable_time, 
  disable_time, 
  site_type, 
  org_id, 
  province_id, 
  city_id, 
  country_id, 
  node_code, 
  role_type 
from 
  line_node_flow 
where 
  line_node_flow_id > 7996682 
limit 
  500

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: line_node_flow
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2736693
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

其中：

扫描行数 2736693

查看符合条件的数据量。

mysql> select count(*) from line_node_flow where line_node_flow_id > 7996682;
+----------+
| count(*) |
+----------+
|  4211092 |
+----------+
1 row in set (1.25 sec)

mysql> select count(*) from line_node_flow ;
+----------+
| count(*) |
+----------+
|  5485622 |
+----------+
1 row in set (0.82 sec)

其中：

全表 5485622 条数据中有 4211092 条满足条件。

甚至有 limit 与没有 limit 的执行计划中的扫描行数相同，显然这是不合理的，否则 limit 就没用了。

mysql> explain select line_node_flow_id from line_node_flow where line_node_flow_id > 7996682 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: line_node_flow
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2736693
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

其中：

rows: 2736845，实际符合条件的行数 4211092，实际扫描行数 4211092，差异的原因是统计信息。

发现查看执行计划时有警告信息，怀疑发生 SQL 改写，因此查看警告。

mysql> show warnings G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `vrsbasic`.`line_node_flow`.`line_node_flow_id` AS `line_node_flow_id` from `vrsbasic`.`line_node_flow` where (`vrsbasic`.`line_node_flow`.`line_node_flow_id` > 7996682)
1 row in set (0.00 sec)

其中：

查看执行计划时有警告，但是 SQL 没有改写，实际上 SELECT 语句的执行计划都有警告信息，因此有警告不代表 SQL 改写。

for SELECT statements, EXPLAIN generates extended information that can be displayed with SHOW WARNINGS following the EXPLAIN.

那么，where limit 中的扫描行数准确吗？下面进行测试验证。

limit 测试

首先是准备工作，将慢日志阈值修改为 0 用于查看慢日志中的扫描行数，并创建测试表。

mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)

数据表

mysql> show create table t1 G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_t1_bcd` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t1;
+---+------+------+------+------+
| a | b    | c    | d    | e    |
+---+------+------+------+------+
| 1 |    1 |    1 |    1 | a    |
| 2 |    2 |    2 |    2 | b    |
| 3 |    3 |    2 |    2 | c    |
| 4 |    3 |    1 |    1 | d    |
| 5 |    2 |    3 |    5 | e    |
| 6 |    6 |    4 |    4 | f    |
| 7 |    4 |    5 |    5 | g    |
| 8 |    8 |    8 |    8 | h    |
+---+------+------+------+------+
8 rows in set (0.00 sec)

测试 where，没有 limit

mysql> explain select * from t1 where a>2 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1 where a>2;
+---+------+------+------+------+
| a | b    | c    | d    | e    |
+---+------+------+------+------+
| 3 |    3 |    2 |    2 | c    |
| 4 |    3 |    1 |    1 | d    |
| 5 |    2 |    3 |    5 | e    |
| 6 |    6 |    4 |    4 | f    |
| 7 |    4 |    5 |    5 | g    |
| 8 |    8 |    8 |    8 | h    |
+---+------+------+------+------+
6 rows in set (0.00 sec)

其中：

符合条件的 6 条记录
执行计划显示的扫描行数也等于 6

慢日志文件中显示实际扫描行数也是 6

# Time: 2023-10-26T20:30:35.174501+08:00
# User@Host: admin[admin] @  [127.0.0.1]  Id:   155
# Query_time: 0.000497  Lock_time: 0.000181 Rows_sent: 6  Rows_examined: 6
SET timestamp=1698323435;
select * from t1 where a>2;

因此没有 limit 时执行计划显示的扫描行数与实际扫描行数相等。

测试 where limit

mysql> explain select * from t1 where a>2 limit 1 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1 where a>2 limit 1;
+---+------+------+------+------+
| a | b    | c    | d    | e    |
+---+------+------+------+------+
| 3 |    3 |    2 |    2 | c    |
+---+------+------+------+------+
1 row in set (0.00 sec)

其中：

符合条件的 1 条记录
执行计划显示的扫描行数等于 6

慢日志文件中显示实际扫描行数等于 1

# Time: 2023-10-26T20:44:58.394513+08:00
# User@Host: admin[admin] @  [127.0.0.1]  Id:   155
# Query_time: 0.000527  Lock_time: 0.000235 Rows_sent: 1  Rows_examined: 1
SET timestamp=1698324298;
select * from t1 where a>2 limit 1;

因此有 limit 时执行计划显示的扫描行数与实际扫描行数不相等。

下面分析原因。

原理

SQL 书写顺序和执行顺序

(7) SELECT
(8) DISTINCT <select_list>
(1) FROM  <main_table>
(3) <join_type> JOIN <join_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

书写顺序从上向下依次书写，即：

SELECT → FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY→ LIMIT

而执行顺序按照左侧编号进行。即：

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT →DISTINCT → ORDER BY→ LIMIT

其中：

where 在 select 之前执行，因此 where 子句中不可以使用 select 中的别名；

mysql> select name as b from t3 group by b limit 1;
+------+
| b    |
+------+
| test |
+------+
1 row in set (0.01 sec)

根据官方文档，标准 SQL 规范中不允许 where 中使用别名的原因是 where 在 select 之前执行，别名绑定的列值可能还没确定。

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

group by 也在 select 之前执行，但是 group by 字句中可以使用 select 中的别名。

mysql> select name as b from t3 where b>'a' limit 1;
ERROR 1054 (42S22): Unknown column 'b' in 'where clause'

判断原因与数据库在标准以外的具体实现有关，比如 oracle 23C 开始支持 GROUP BY ALIAS，在此之前也不支持。

limit，存储引擎层查询到全部数据后逐条发送给 server 层，其中进行与客户端的交互判断。

如果已经跳过的记录数小于 offset，不需要给客户端发送；
如果已发送记录数量大于等于需要发送的记录数量，则结束查询。

因此尽管 SQL 的执行顺序是先执行 where，后执行 limit，但是 limit 可以用于在引擎层限制扫描行数，而不是等到全部扫描完成后再执行 limit。

limit 实际执行

MySQL 社区版在 8.0.16 之前，通过函数 JOIN::should_send_current_row() 判断是否需要将数据返回，如果 offset 大于 0，就会忽略数据不返回给客户端。

  class JOIN {
  /**
    Returns whether one should send the current row on to the output,
    or ignore it. (In particular, this implements OFFSET handling
    in the non-iterator executor.)
   */
  bool should_send_current_row() {
    if (!do_send_rows) {
      return false;
    }
    if (unit->offset_limit_cnt > 0) {
      --unit->offset_limit_cnt;
      // 丢弃
      return false;
    } else {
      return true;
    }
  }
  };

社区版 8.0.16 版本中引入LimitOffsetIterator 类，基于 Iterator 迭代器实现，用于处理 SQL 语句中 LIMIT 和 OFFSET 关键字。

除了处理 offset 逻辑之外，LimitOffsetIterator::Read() 每次只读取一条记录，核心逻辑为：

从存储引擎读取返回给客户端的第 1 条记录之前，会先循环读取 offset 条记录并丢弃，然后从第 offset + 1 条记录开始返回给客户端。；
从存储引擎读取第从第 offset + 2 ~limit + offset 条记录时，每读取一条记录，都返回给 Query_expression::ExecuteIteratorQuery()，由该方法把记录返回给客户端；
读取limit + offset条记录之后，返回 -1 表示读取流程正常结束。

从执行流程中可以发现 limit 的局限：

OFFSET，深度分页越往后执行越慢（offset 非常大），原因是扫描大量数据行后直接丢弃；
LIMIT，对于深度分页的最后一页，当表中满足条件的数据量小于 limit_rows 时，将持续遍历到末尾，导致深度分页最后一页可能出现查询超时。

具体代码如下所示。

bool LimitOffsetIterator::Init() {
  if (m_source->Init()) {
    return true;
  }
  if (m_offset > 0) {
    m_seen_rows = m_limit;
    m_needs_offset = true;
  } else {
    m_seen_rows = 0;
    m_needs_offset = false;
  }
  return false;
}

int LimitOffsetIterator::Read() {
  // 【重点】只有读取第一条和最后一条记录时才会进入这个 if 分支
  if (m_seen_rows >= m_limit) {
    // We either have hit our LIMIT, or we need to skip OFFSET rows.
    // Check which one.
    // m_needs_offset = true
    // 表示 SQL 语句中指定了 offset
    if (m_needs_offset) {
      // We skip OFFSET rows here and not in Init(), since performance schema
      // batch mode may not be set up by the executor before the first Read().
      // This makes sure that
      //
      //   a) we get the performance benefits of batch mode even when reading
      //      OFFSET rows, and
      //   b) we don't inadvertedly enable batch mode (e.g. through the
      //      NestedLoopIterator) during Init(), since the executor may not
      //      be ready to _disable_ it if it gets an error before first Read().
      // m_needs_offset = true，表示 SQL 语句中指定了 offset
      // 循环从存储引擎读取 m_offset 条记录
      // 每读取到一条记录，直接丢弃
      for (ha_rows row_idx = 0; row_idx < m_offset; ++row_idx) {
        // 读取一条记录之后
        // 如果没有出错，就接着读取下一条记录
        int err = m_source->Read();
        // 读取出错，直接返回错误码
        if (err != 0) {
          // Note that we'll go back into this loop if Init() is called again,
          // 命令中了LIMIT(或者在OFFSET完成后立即击中LIMIT)，
          // and return the same error/EOF status.
          return err;
        }
        if (m_skipped_rows != nullptr) {
          ++*m_skipped_rows;
        }
        // 释放锁
        m_source->UnlockRow();
      }
      // 读取 m_offset 条记录并丢弃之后
      // 把 m_seen_rows 设置为已读取记录数
      m_seen_rows = m_offset;
      // 然后把 m_needs_offset 设置为 false
      // 表示不需要再处理 offset 逻辑了（因为已处理完成）
      // 下次读取时也就不需要再跳过 m_offset 条记录了
      m_needs_offset = false;

      // Fall through to LIMIT testing.
    }

    // 如果已经读取了 m_limit 条记录
    // 就返回 -1，表示读取结束
    // m_limit = SQL 中的 limit + offset
    if (m_seen_rows >= m_limit) {
      // We really hit LIMIT (or hit LIMIT immediately after OFFSET finished),
      // so EOF.
      if (m_count_all_rows) {
        // Count rows until the end or error (ignore the error if any).
        while (m_source->Read() == 0) {
          ++*m_skipped_rows;
        }
      }
      return -1;
    }
  }

  // 读取需要返回给客户端的记录
  const int result = m_source->Read();
  if (m_reject_multiple_rows) {
    if (result != 0) {
      ++m_seen_rows;
      return result;
    }
    // We read a row. Check for scalar subquery cardinality violation
    if (m_seen_rows - m_offset > 0) {
      my_error(ER_SUBQUERY_NO_1_ROW, MYF(0));
      return 1;
    }
  }

  // 已读取记录数加 1
  ++m_seen_rows;
  // 返回当前读取的记录
  // 给 Query_expression::ExecuteIteratorQuery() 方法
  return result;
}

经测试，5.7.24 与 8.0.31 中执行相同的 where limit 时，执行用时相同，handler 也相同，表明LimitOffsetIterator并没有做性能优化，只是将之前的过程式代码统一封装为类。

因此在 MySQL 社区版中，无论哪个版本，存储引擎层都需要将 limit + offset 数据返回给 Server 层，然后由 Server 层过滤无效数据即 offset。

在这个过程中，引擎需要读取每一行数据转换为 MySQL 数据格式，有些场景访问索引还需要回主表获取所有需要的列数据，导致系统资源开销很大，极为耗时。

而在 PolarDB MySQL中实现了 limit offset 优化。

优化器会识别能够将 limit offset 下推到引擎的场景，然后在优化阶段将 limit offset 下推到引擎层。执行时，引擎会快速扫描路径上的数据行，过滤掉用户客户端不需要的 offset 数据，然后将用户需要的 limit 数据返回，如下图所示。

当使用 limit offset 优化时，执行计划的 extra 列中会展示 Using limit-offset pushdown。

因此，如果 where 条件都能被使用索引下推到 InnoDB，然后 server 层相当于没有 where 条件了，这种情况下，limit offset 也能下推到 InnoDB，由 InnoDB 直接判断是不是可以丢弃，从而可以使用 limit offset 优化。

否则就需要将记录返回给 server 层，由 server 层判断是不是要跳过。

个人理解 limit offset 优化类似于 ICP，目标都是减少发送给 server 层的数据量。

limit 执行计划

limit 不影响执行计划，因此有没有 limit 时执行计划相同，但是并不代表实际执行也相同。

LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number.

原因是实际执行之前无法判断 limit 什么时候退出，退出条件与实际数据分布有关。

实际执行时当满足 limit 后执行将提前结束。

As soon as MySQL has sent the required number of rows to the client, it aborts the query

如果不确定 explain 的结果是否准确，可以在实际执行后查看 handler。

If you suspect EXPLAIN is lieing you you can use SHOW STATUS “Handler” statistics to see if number of operations match.

如下所示，执行计划显示扫描行数 2695453

mysql> explain select line_node_flow_id from line_node_flow where line_node_flow_id > 7996682 limit 500 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: line_node_flow
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2695453
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

执行后查看 handler

mysql> select line_node_flow_id from line_node_flow where line_node_flow_id > 7996682 limit 500;
...
500 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE "Handler%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 499   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

其中：

Handler_read_key = 1，表明基于索引初次定位 1 行数据；
Handler_read_next = 499，表明索引顺序扫描 499 行；
因此实际执行扫描行数为 1 + 499 = 500，与执行计划中显示的 2695453 差异较大。

结论

现象是 where limit 语句的执行计划中扫描行数远大于 limit row_count。

如果根据 SQL 执行顺序中先 where 后 limit 进行判断，可能认为存储引擎层会将全部满足 where 条件的数据发送给 server 层然后由 limit 过滤。

然而测试显示：

没有 limit 时执行计划显示的扫描行数与实际扫描行数相等
有 limit 时执行计划显示的扫描行数与实际扫描行数不相等

经分析，原因是 limit 执行时将数据逐条发送给 server 层，其中进行与客户端的交互判断：

如果已经跳过的记录数小于 offset，不需要给客户端发送，也就是 offset 的作用；
如果已发送记录数量大于等于需要发送的记录数量，则结束查询，也就是 limit 的作用。

因此当满足 limit row_count 时，就结束查询，不需要扫描全部满足 where 条件的数据。

不过从中也可以发现 limit 的局限：

OFFSET，深度分页越往后执行越慢（offset 非常大），原因是存储引擎层会将limit + offset数据返回给 Server 层，然后由 Server 层过滤无效数据即 offset；
LIMIT，对于深度分页的最后一页，当表中满足条件的数据量小于 limit_rows 时，将持续遍历到末尾，导致深度分页最后一页可能出现查询超时。

其中针对 limit offset，PolarDB MySQL 进行了优化。具体是由优化器识别能够将 limit offset 下推到引擎的场景，然后在优化阶段将 limit offset 下推到引擎层。执行时，引擎会快速扫描路径上的数据行，过滤掉用户客户端不需要的 offset 数据，然后将用户需要的 limit 数据返回。

因此，MySQL 中 limit 语句的执行计划中显示的扫描行数与实际不符，实际上有没有 limit 时执行计划相同。

原因是实际执行之前无法判断 limit 什么时候退出，而退出条件与实际数据分布有关。