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)
适应场景: 适用于数据量多的情况(万行以上)
原因: 索引扫描,速度会快。
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函数。