m6米乐安卓版下载-米乐app官网下载
暂无图片
3
暂无图片
暂无图片
暂无图片

mysql limit实现解读 -m6米乐安卓版下载

原创 kevincui 2023-05-12
1393

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函数。

最后修改时间:2023-05-15 08:54:08
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

网站地图