介绍

Limit句常用于约束行的匹配数。目前MySQL8.0版本中支持在 SELECT（TABLE命令）, DELETE, UPDATE, WITH窗口函数里配合使用Limit语句。

用法也比较简单，Limit接受一个或两个数字参数，必须都是非负整数常量。普遍的用法中Limit的row_count来限制行匹配的范围。一旦找到满足WHERE子句的row_count行，无论是否实际被更改，该语句就会停止。

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

• 使用两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。
• offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
• row_count ：返回具体行数。
• 在预处理语句中，LIMIT参数可以使用?占位符标记。
• 在存储程序中，可以使用整数值例程参数或局部变量指定LIMIT参数。

使用方式

Limit一般用于分页场景和查看是否存在数据的场景。特别是大数据量下，非常有效。如只需要结果集中指定返回的行数，在查询中使用LIMIT子句，而不是获取整个结果集并丢弃额外的数据。

• 如果使用Limit只选择几行，MySQL在某些情况下会使用索引，而通常情况下它更愿意进行全表扫描。
• 一旦MySQL向客户端发送了所需的行数，它就会终止查询，除非使用的是SQL_CALC_FOUND_ROWS。在这种情况下，可以使用SELECT FOUND_ROWS()检索行数。
• 如果把Limitrow_count和DISTINCT结合起来，MySQL一旦发现row_count唯一的行就会停止。
• Limit 0快速返回一个空集合。这对于检查查询的有效性非常有用。它还可以用于在使用MySQL API的应用程序中获取结果列的类型，该API使结果集元数据可用。
• 如果优化器使用临时表来解析查询，它会使用LIMIT row_count子句来计算需要多少空间。
• 在某些情况下，可以通过按顺序读取索引(或对索引进行排序)，然后计算摘要，直到索引值发生变化来解决GROUP BY。在这种情况下，LIMIT row_count不会计算任何不必要的GROUP BY值。
• 如果索引没有用于ORDER BY，但也存在LIMIT子句，则优化器可能能够避免使用合并，并使用内存中的文件排序操作对内存中的行进行排序。
• 如果将Limit row_count与ORDER BY结合使用，MySQL在找到已排序结果的第一个row_count行后立即停止排序，而不是对整个结果进行排序。如果排序是通过使用索引来完成的，这是非常快的。如果必须进行排序，则选择所有不带Limit子句的与查询匹配的行，并在找到第一个row_count之前对大部分或全部进行排序。在找到初始行之后，MySQL不会对结果集的任何剩余部分进行排序。

对于Limit具体操作，可以从慢日志记录的Rows_examined中了解是否按照上面所说进行筛选。

验证1：
单存的limit是否获取对应的row_count就会停止。

mysql> select * from t1 ;
+----+--------------+------+------+-------+------+------+------+
| id | name         | age  | addr | addr1 | t0   | t1   | t2   |
+----+--------------+------+------+-------+------+------+------+
|  1 | CCC          |   10 | C    | NULL  | NULL |    3 |    9 |
|  2 | BBB          |   10 | NULL | NULL  | NULL | NULL | NULL |
|  3 | AAA          |   10 | NULL | NULL  | NULL | NULL | NULL |
|  4 | DDD          |   10 | NULL | NULL  |    2 | NULL | NULL |
|  5 | EEEE         |    0 | NULL | NULL  |    2 | NULL | NULL |
|  6 | FFFFF        |    0 | NULL | NULL  | NULL | NULL | NULL |
|  7 | GGGGG        |    1 | NULL | NULL  |    2 | NULL | NULL |
|  8 | WWWWW        |   10 | NULL | NULL  |    2 | NULL | NULL |
|  9 | QQQQ         |   30 | NULL | NULL  |    2 | NULL | NULL |
| 10 | PPPPPPPPPPPP |   39 | NULL | NULL  |    2 | NULL | NULL |
+----+--------------+------+------+-------+------+------+------+
10 rows in set (0.00 sec)

#扫描一行
mysql> select * from t1 limit 1;
+----+------+------+------+-------+------+------+------+
| id | name | age  | addr | addr1 | t0   | t1   | t2   |
+----+------+------+------+-------+------+------+------+
|  1 | CCC  |   10 | C    | NULL  | NULL |    3 |    9 |
+----+------+------+------+-------+------+------+------+
1 row in set (0.01 sec)

#慢日志记录:扫描一行之后就停止
# Time: 2023-05-06T10:01:55.510632+08:00
# User@Host: root[root] @ localhost []  Id:    29
# Query_time: 0.000297  Lock_time: 0.000005 Rows_sent: 1  Rows_examined: 1
SET timestamp=1683338515;
select * from t1 limit 1;

验证2：
Limit中offset组合使用方式，是获取对应所有行之后再进行抽取row_count 。

#从第8行开始之后 获取1行数据
mysql> select * from t1 limit 8,1;
+----+------+------+------+-------+------+------+------+
| id | name | age  | addr | addr1 | t0   | t1   | t2   |
+----+------+------+------+-------+------+------+------+
|  9 | QQQQ |   30 | NULL | NULL  |    2 | NULL | NULL |
+----+------+------+------+-------+------+------+------+
1 row in set (0.00 sec)

#慢日志记录：扫描9行只有在获取1行
# Time: 2023-05-06T10:02:17.041044+08:00
# User@Host: root[root] @ localhost []  Id:    29
# Query_time: 0.000291  Lock_time: 0.000005 Rows_sent: 1  Rows_examined: 9
SET timestamp=1683338537;
select * from t1 limit 8,1;

验证3：
对于无索引字段排序属于全表扫描之后，获取row_count。

#name字段排序获取行
mysql> select * from t1 order by name  limit 1;
+----+------+------+------+-------+------+------+------+
| id | name | age  | addr | addr1 | t0   | t1   | t2   |
+----+------+------+------+-------+------+------+------+
|  3 | AAA  |   10 | NULL | NULL  | NULL | NULL | NULL |
+----+------+------+------+-------+------+------+------+
1 row in set (0.00 sec)

#慢日志记录信息：全表扫描之后，在获取1行
# Time: 2023-05-06T10:05:53.534291+08:00
# User@Host: root[root] @ localhost []  Id:    29
# Query_time: 0.000380  Lock_time: 0.000007 Rows_sent: 1  Rows_examined: 11
SET timestamp=1683338753;
select * from t1 order by name  limit 1;

验证4：
在MySQL8.0里 optimizer_switch里prefer_ordering_index对任何有LIMIT子句的ORDER BY或GROUP BY查询使用有序索引，覆盖优化器所做的任何其他选择，只要它确定这会导致更快的执行。

mysql> SET optimizer_switch = "prefer_ordering_index=off";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 order by id  limit 1;
+----+------+------+------+-------+------+------+------+
| id | name | age  | addr | addr1 | t0   | t1   | t2   |
+----+------+------+------+-------+------+------+------+
|  1 | CCC  |   10 | C    | NULL  | NULL |    3 |    9 |
+----+------+------+------+-------+------+------+------+
1 row in set (0.01 sec)

#慢日志记录信息：全表扫描之后，在取1行
# Time: 2023-05-08T11:20:19.210539+08:00
# User@Host: root[root] @ localhost []  Id:    31
# Query_time: 0.000353  Lock_time: 0.000004 Rows_sent: 1  Rows_examined: 11
SET timestamp=1683516019;
select * from t1 order by id  limit 1;

分页场景

在MySQL中Limit分页查询是最常用的场景之一。数据库分页技术指的是在页面进行展示时，对数据进行按页面请求来展示，从而减少数据库的数据查询量，减轻数据库的压力。但在MySQL中通常也是最容易出问题的地方。
比如，如下简单的语句：

SELECT * 
FROM   employees
WHERE  first_name = 'Kevin'  AND last_name= 'Cui' 
ORDER  BY hire_date 
LIMIT  1000000, 10;

一般DBA会想到的办法是在first_name ，last_name，hire_date字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。

可能90%以上的DBA解决该问题就到此为止。但当 Limit子句变成 “LIMIT 1000000,10” 时，应用仍然会抱怨：只取10条记录为什么还是慢。
但实际底层实现方式是结合需要扫描100多万的行之后筛选10条数据。最终导致LIMIT分页存在很严重的性能问题。

应对这种分页场景，因为要取出所有字段内容，所以按照积累经验通过直接根据索引字段定位后，才取出相应内容，就是说，不直接使用Limit，而是首先获取到offset的id，然后对应id直接使用limit size来获取数据，效率自然大大提升。

上诉语句可以改成如下：

SELECT * 
FROM   employees
WHERE  enp_no IN 
(SELECT emp_no  FROM   employees  
   WHERE first_name = 'Kevin' AND last_name= 'Cui'  
   ORDER  BY hire_date LIMIT  1000000, 10);

对于Limit的优化，原则是能够在最大程度的减少无效数据的访问和传输代价，大大提升执行效率。

Limit分页场景，可以按照数据量分三种：
方法1: 直接使用数据库提供的SQL语句
语句样式: SELECT * FROM 表名称 LIMIT M,N
适应场景: 适用于数据量较少的情况(万行以内)
原因/缺点: 全表扫描速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)，Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。

方法2: 基于索引+再排序
语句样式: SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M,N
适应场景: 适用于数据量多的情况(万行以内). 最好ORDER BY后的列对象是组合索引，主键或唯一，没有索引排序有可能出现不同的结果。
原因: 索引扫描,速度会很快。还是需要对应的数据，全部取得之后，在返回row_count。

方法3: 基于主键进行筛选，之后再次关联
语句样式: SELECT * FROM 表名称 WHERE id_pk in(SELECT id_pk FROM 表名称 WHERE 列名称=‘条件’ LIMIT M,N)
适应场景: 适用于数据量多的情况(万行以上)
原因: 索引扫描，速度会快。

FOUND_ROWS

SQL_CALC_FOUND_ROWS查询修饰符和相应的FOUND_ROWS()函数已从MySQL 8.0.17起弃用;预计将在MySQL的未来版本中被删除。作为替换，考虑执行带有LIMIT的查询，然后执行带有COUNT(*)但不带LIMIT的第二个查询，以确定是否有额外的行。仅作为参考。

SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE id > 5 LIMIT 10;
SELECT FOUND_ROWS();
Use these queries instead:

# 改成如下：
SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM tbl_name WHERE id > 100;

源码实现

Limit中逻辑比较简单。比如offset 方式主要逻辑是一个 for 循环，会循环 offset 次，每次读取一条记录，到limit_rows为止。通过实现有Iterator迭代器实现。代码中体现有以下2个类。

• sql/iterators/basic_row_iterators.cc的TableScanIterator::Read
• sql/iterators/composite_iterators.cc的LimitOffsetIterator::Read

int LimitOffsetIterator::Read() {
  if (m_seen_rows >= m_limit) {
    // We either have hit our LIMIT, or we need to skip OFFSET rows.
    // Check which one.
    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_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_seen_rows = m_offset;
      m_needs_offset = false;

      // Fall through to LIMIT testing.
    }

    // 如果已经读取了 m_limit 条记录
    // 就返回 -1，表示读取结束
    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;
  return result;
}

总结

至此对于Limit有一定的了解之后，MySQL的中使用Limit语句的时候，要合理使用，避免全表扫描。也希望后续看到像oracle的rownum函数。