limit 订阅
LIMIT是一个英文词语,名词、动词,作名词时意思是“限制;限度;界线”,作及物动词时意思是“限制;限定”。 展开全文
LIMIT是一个英文词语,名词、动词,作名词时意思是“限制;限度;界线”,作及物动词时意思是“限制;限定”。
信息
外文名
LIMIT
词    性
名词、动词
LIMIT单词发音
英[ˈlɪmɪt];美[ˈlɪmɪt] [1] 
收起全文
精华内容
下载资源
问答
  • limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据. 例如: ## 返回符合条件的前10条语句 select * from user limit 10 ## 返回符合条件的第11-20条数据 select * from user limit 10,...
  • nginx可以使用ngx_http_limit_req_module模块的limit_req_zone指令进行限流访问,防止用户恶意攻击刷爆服务器。ngx_http_limit_req_module模块是nginx默认安装的,所以直接配置即可。 首先,在nginx.conf文件中的...
  • 今天小编就为大家分享一篇layui table 获取分页 limit的方法,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
  • 下面小编就为大家带来一篇在JPA的@Query注解中使用limit条件(详解)。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • PostgreSQL LIMIT 子句

    2020-12-16 17:27:44
    PostgreSQL LIMIT 子句 PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。 语法 带有 LIMIT 子句的 SELECT 语句的基本语法如下: SELECT column1, column2, columnN FROM table_name LIMIT [no ...
  • 主要介绍了MYSQL分页limit速度太慢的优化方法,需要的朋友可以参考下
  • 本文档针对mysql分页之limit慢的问题,使用联合索引在大数据量的情况下优化limit分页的性能
  • p-limit, 运行多个保证并发的&异步函数 限制 运行多个保证并发的&异步函数安装$ npm install p-limit用法const pLimit = require('p-limit');const limit = pLim
  • NULL 博文链接:https://smallbee.iteye.com/blog/1633842
  • order by id desc limit 10 按照id的倒序排序 取出前10条 order by id desc limit 0,10 按照id的倒序排序 取出前10条 order by id limit 5,10 按照id的正序排序 从第5条开始取10条 代码如下: SELECT cat_id FROM ...
  • 您可能感兴趣的文章:Mysql limit 优化,百万至千万级快速分页 复合索引的引用并应用于轻量级框架mysql limit分页优化方法分享使用Limit参数优化MySQL查询的方法mysql limit查询优化分析Mysql中limit的用法方法详解...
  • java后台springboot技术的小实例项目,浏览器地址访问http://localhost:8080/dialog/selectAllLaw
  • 要想通过面试,MySQL的Limit子句底层原理你不可不知

    万次阅读 多人点赞 2021-10-12 17:02:22
    MySQL的Limit子句底层原理如何分析,Limit子句在哪一步骤才执行?这一篇,我们得从从server层和存储引擎层进行分析...

    1.老样子,建个表

    还是这张表,表里我创建了近10W条数据

    CREATE TABLE demo_info(
        id INT NOT NULL auto_increment,
        key1 VARCHAR(100),
        key2 INT,
        key3 VARCHAR(100),
        key_part1 VARCHAR(100),
        key_part2 VARCHAR(100),
        key_part3 VARCHAR(100),
        common_field VARCHAR(100),
        PRIMARY KEY (id),
        KEY idx_key1 (key1),
        UNIQUE KEY uk_key2 (key2),
        KEY  idx_key3 (key3),
        KEY idx_key_part(key_part1, key_part2, key_part3)
    )ENGINE = INNODB CHARSET=utf8mb4;
    

    id列是主键,key1列是二级索引列。


    2.从sql执行计划看Limit的影响

    分析一下sql执行计划

    explain select * from demo_info order by key1 limit 1;
    

      在二级索引idx_key1中,key1列是有序的,查找按key1列排序的第1条记录,MySQL只需要从idx_key1中获取到第一条二级索引记录,然后直接回表取得完整的记录即可,这个很容易理解。

      如果我们把上边语句的limit 1换成limit 10000, 1,则却需要进行全表扫描,并进行filesort,执行计划如下:

    explain select * from demo_info order by key1 limit 10000, 1;
    

      有的同学就很不理解了:limit 10000, 1也可以使用二级索引idx_key1呀,我们可以先扫描到第10001条二级索引记录,对第10001条二级索引记录进行回表操作就好了啊。

      由于MySQL实现缺陷,不会出现上述的理想情况,它只会全表扫描+filesort,下边我们分析一下。


    3. 从server层和存储引擎层分析Limit执行过程

    MySQL其实是分为server层和存储引擎层的:

    • server层负责处理一些通用的事情,诸如连接管理、SQL语法解析、分析执行计划之类的东西

    • 存储引擎层负责具体的数据存储,诸如数据是存储到文件上还是内存里,具体的存储格式是什么样的之类的。我们现在基本都使用InnoDB存储引擎,其他存储引擎使用的非常少了,所以我们也就不讨论其他存储引擎了。

      MySQL中一条SQL语句的执行是通过server层和存储引擎层的多次交互才能得到最终结果的。先不用Limit子句举一个简单例子分析:

    SELECT * FROM demo_info WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';
    

    server层会分析到上述语句可以使用下边两种方案执行:

    • 方案一:使用全表扫描

    • 方案二:使用二级索引idx_key1,此时需要扫描key1列值在('a', 'b')之间的全部二级索引记录,并且每条二级索引记录都需要进行回表操作。

      server层会分析上述两个方案哪个成本更低,然后选取成本更低的那个方案作为执行计划。然后就调用存储引擎提供的接口来真正的执行查询了。

    这里假设采用方案二,也就是使用二级索引idx_key1执行上述查询。那么server层和存储引擎层的执行过程如下:

      server层:“去查查idx_key1二级索引的('a', 'b')区间的第一条记录,然后把回表后把完整的记录返给我”

      InnoDB层:InnoDB就通过idx_key1二级索引对应的B+树,快速定位到扫描区间('a','b')的第一条二级索引记录,然后进行回表,得到完整的聚集索引记录返回给server层。 server层收到完整的聚集索引记录后,继续判断common_field!='a'条件是否成立,如果不成立则舍弃该记录,否则将该记录发送到客户端。然后对存储引擎说:“请把下一条记录给我”

    注意:

      此处将记录发送给客户端其实是发送到本地的网络缓冲区,缓冲区大小由net_buffer_length控制,默认是16KB大小。等缓冲区满了才真正发送网络包到客户端。

      InnoDB层:InnoDB找到idx_key1('a', 'b')区间的下一条二级索引记录,然后进行回表操作,将得到的完整的聚集索引记录返回给server层。

    注意:

      不论是聚集索引记录还是二级索引记录,都包含一个称作next_record的属性,各个记录根据next_record连成了一个链表,并且链表中的记录是按照键值排序的(对于聚集索引来说,键值指的是主键的值,对于二级索引记录来说,键值指的是二级索引列的值)。

      server层收到完整的聚集索引记录后,继续判断common_field!='a'条件是否成立,如果不成立则舍弃该记录,否则将该记录发送到客户端。然后对存储引擎说:“请把下一条记录给我哈”

      … 然后就不停的重复上述过程。

      直到InnoDB发现根据二级索引记录的next_record获取到的下一条二级索引记录不在('a', 'b')区间中,就跟server层说:“('a', 'b')区间没有下一条记录了”

      server层收到InnoDB说的没有下一条记录的消息,就结束查询。

    现在大家就知道了server层和存储引擎层的基本交互过程了。

    limit在哪里起作用呢?

    MySQL是在server层准备向客户端发送记录的时候才会去处理limit子句中的内容。 举个例子:

    select * from demo_info order by key1 limit 10000, 1;
    

    如果使用idx_key1执行上述查询,那么MySQL会这样处理:

    • server层向InnoDB要第1条记录,InnoDBidx_key1中获取到第一条二级索引记录,然后进行回表操作得到完整的聚集索引记录,然后返回给server层。server层准备将其发送给客户端,此时发现还有个limit 10000, 1的要求,意味着符合条件的记录中的第10001条才可以真正发送给客户端,所以在这里先做个统计,我们假设server层维护了一个称作limit_count的变量用于统计已经跳过了多少条记录,此时就应该将limit_count设置为1

    • server层再向InnoDB要下一条记录,InnoDB再根据二级索引记录的next_record属性找到下一条二级索引记录,再次进行回表得到完整的聚集索引记录返回给server层。server层在将其发送给客户端的时候发现limit_count才是1,所以就放弃发送到客户端的操作,将limit_count1,此时limit_count变为了2

    • … 重复上述操作

    • 直到limit_count等于10000的时候,server层才会真正的将InnoDB返回的完整聚集索引记录发送给客户端。

      从上述过程中我们可以看到,MySQL中是在实际向客户端发送记录前才会去判断limit子句是否符合要求,所以如果使用二级索引执行上述查询的话,意味着要进行10001次回表操作。server层在进行执行计划分析的时候会觉得执行这么多次回表的成本太大了,还不如直接全表扫描+filesort快呢,全表扫描+filesort就是把聚集索引中的记录都依次与给定的搜索条件进行比较,把符合搜索条件的记录再进行排序,MySQL认为这样操作的成本比多次回表成本低,所以就选择了后者执行查询。

    MySQL是根据成本来选择对应索引查询的,如果你不知道成本怎么计算,可以看我前一篇MySQL查询为什么选择使用这个索引?——基于MySQL8.0.22索引成本计算

    注意:
      有一个点很容易混淆,走PRIMARY索引和全表扫描有什么区别呢?他们其实都是在聚集索引上操作的(聚集索引B+树的叶子结点是根据主键排好序的完整的用户记录,包含表里的所有字段),区别就在于

      全表扫描将聚集索引B+树的叶子结点依次顺序扫描并判断条件,在以下几种情况会走全表扫描:

    • select * from demo_info这种无条件的查询语句
    • select * from demo_info where common_field != 'a'这种条件字段common_field没有建索引的情况
    • select * from demo_info order by key1 limit 10000, 1条件字段key1建了索引但是MySQL认为走二级索引的成本比全表扫描成本高的情况。

      PRIMARY索引是利用二分思想将聚集索引B+树到指定范围区间进行扫描,比如select * from demo_info where id in (1, 2)这种条件字段是主键id,可以很好的利用PRIMARY索引进行二分的快速查询。

    怎么解决这个问题?

      由于MySQL实现limit子句的局限性,在处理诸如limit 10000, 1这样的语句时就无法通过使用二级索引来加快查询速度了么?其实也不是,只要把上述语句改写成:

    select * from demo_info d, 
    (select id from demo_info order by key1 limit 10000, 1) t 
    WHERE d.id = t.id;
    -- 或者这么写
    select * from demo_info d
    join 
    (select id from demo_info order by key1 limit 10000, 1) t
    on d.id = t.id
    

      这样,select id from demo_info order by key1 limit 10000, 1作为一个子查询单独存在,由于该子查询的查询列表只有一个id列,MySQL可以通过仅扫描二级索引idx_key1的叶子结点不用回表,然后再根据子查询中获得到的主键值去表demo_info中进行查找。这样就省去了前10000条记录的回表操作,从而大大提升了查询效率!



    欢迎一键三连~

    有问题请留言,大家一起探讨学习

    ----------------------Talk is cheap, show me the code-----------------------
    展开全文
  • Nginx访问控制,限速limit_conn, limit_req

    千次阅读 2019-10-31 12:09:36
    说明:首先用limit_conn_zone定义了一个内存区块索引aming,大小为10m,它以$binary_remote_addr作为key。 该配置只能在http里面配置,不支持在server里配置。 limit_conn 定义针对aming这个zone,并发连接为10个...

    Nginx访问控制 —— deny_allow

    • Nginx的deny和allow指令是由ngx_http_access_module模块提供,Nginx安装默认内置了该模块。

    • 除非在安装时有指定 --without-http_access_module。

    • 语法:allow/deny address | CIDR | unix: | all

    它表示,允许/拒绝某个ip或者一个ip段访问.如果指定unix:,那将允许socket的访问。
    注意:unix在1.5.1中新加入的功能。

    • 在nginx中,allow和deny的规则是按顺序执行的。
    • 示例1
    location /
    {
        allow 192.168.0.0/24;
        allow 127.0.0.1;
        deny all;
    }
    

    说明:这段配置值允许192.168.0.0/24网段和127.0.0.1的请求,其他来源IP全部拒绝。

    • 示例2:
    location ~ "admin"
    {
        allow 110.21.33.121;
        deny all
    }
    

    说明:访问的uri中包含admin的请求,只允许110.21.33.121这个IP的请求。

    基于location的访问控制

    • 在生产环境中,我们会对某些特殊的请求进行限制,比如对网站的后台进行限制访问。这就用到了location配置。
    • 示例1
    location /aming/
    {
        deny all;
    }
    

    说明:针对/aming/目录,全部禁止访问,这里的deny all可以改为return 403.

    • 示例2
    location ~ ".bak|\.ht"
    {
        return 403;
    }
    

    说明:访问的uri中包含.bak字样的或者包含.ht的直接返回403状态码。

    测试链接举例:

    1. www.aminglinux.com/123.bak
    2. www.aminglinux.com/aming/123/.htalskdjf
    • 示例3
    location ~ (data|cache|tmp|image|attachment).*\.php$
    {
        deny all;
    }
    

    说明:请求的uri中包含data、cache、tmp、image、attachment并且以.php结尾的,全部禁止访问。

    测试链接举例:

    1. www.aminglinux.com/aming/cache/1.php
    2. www.aminglinux.com/image/123.phps
    3. www.aminglinux.com/aming/datas/1.php

    基于$document_uri的访问控制

    • 这就用到了变量$document_uri,根据前面所学内容,该变量等价于$uri,其实也等价于location匹配。
    • 示例1
    if ($document_uri ~ "/admin/")
    {
        return 403;
    }
    

    说明:当请求的uri中包含/admin/时,直接返回403.

    • if结构中不支持使用allow和deny。

    测试链接:

    1. www.aminglinux.com/123/admin/1.html 匹配
    2. www.aminglinux.com/admin123/1.html 不匹配
    3. www.aminglinux.com/admin.php 不匹配
    • 示例2
    if ($document_uri = /admin.php)
    {
        return 403;
    }
    

    说明:请求的uri为/admin.php时返回403状态码。

    测试链接:
    4. www.aminglinux.com/admin.php 匹配
    5. www.aminglinux.com/123/admin.php 不匹配

    • 示例3
    if ($document_uri ~ '/data/|/cache/.*\.php$')
    {
        return 403;
    }
    

    说明:请求的uri包含data或者cache目录,并且是php时,返回403状态码。

    测试链接:
    6. www.aminglinux.com/data/123.php 匹配
    7. www.aminglinux.com/cache1/123.php 不匹配

    基于$request_uri访问控制

    • $request_uri$docuemnt_uri多了请求的参数。
      主要是针对请求的uri中的参数进行控制。
    • 示例
    if ($request_uri ~ "gid=\d{9,12}")
    {
        return 403;
    }
    

    说明:\d{9,12}是正则表达式,表示9到12个数字,例如gid=1234567890就符号要求。

    测试链接:

    1. www.aminglinux.com/index.php?gid=1234567890&pid=111 匹配
    2. www.aminglinux.com/gid=123 不匹配
    • 背景知识:
      曾经有一个客户的网站cc攻击,对方发起太多类似这样的请求:/read-123405150-1-1.html
      实际上,这样的请求并不是正常的请求,网站会抛出一个页面,提示帖子不存在。
      所以,可以直接针对这样的请求,return 403状态码。

    基于$user_agent的访问控制

    • user_agent大家并不陌生,可以简单理解成浏览器标识,包括一些蜘蛛爬虫都可以通过user_agent来辨识。
    • 通过观察访问日志,可以发现一些搜索引擎的蜘蛛对网站访问特别频繁,它们并不友好。
    • 为了减少服务器的压力,其实可以把除主流搜索引擎蜘蛛外的其他蜘蛛爬虫全部封掉。
    • 另外,一些cc攻击,我们也可以通过观察它们的user_agent找到规律。
    • 示例
    if ($user_agent ~ 'YisouSpider|MJ12bot/v1.4.2|YoudaoBot|Tomato')
    {
        return 403;
    }
    

    说明:user_agent包含以上关键词的请求,全部返回403状态码。

    测试:

    1. curl -A “123YisouSpider1.0”
    2. curl -A “MJ12bot/v1.4.1”

    基于$http_referer的访问控制

    • 之前说过这个变量,当时实现了防盗链功能。
      其实基于这个变量,我们也可以做一些特殊的需求。
    • 示例
      背景:网站被黑挂马,搜索引擎收录的网页是有问题的,当通过搜索引擎点击到网站时,却显示一个博彩网站。
      由于查找木马需要时间,不能马上解决,为了不影响用户体验,可以针对此类请求做一个特殊操作。
      比如,可以把从百度访问的链接直接返回404状态码,或者返回一段html代码。
    if ($http_referer ~ 'baidu.com')
    {
        return 404;
    }
    

    或者

    if ($http_referer ~ 'baidu.com')
    {
        return 200 "<html><script>window.location.href='//$host$request_uri';</script></html>";
    }
    

    Nginx的限速

    • 可以通过ngx_http_limit_conn_module和ngx_http_limit_req_module模块来实现限速的功能。

    ngx_http_limit_conn_module

    该模块主要限制下载速度,针对每个IP的限速;
    $binary_remote_addr获取到IP作为一个参数;

    1. 并发限制

    配置示例

    http
    {
        ...
        limit_conn_zone $binary_remote_addr zone=aming:10m;	#这三句直接放到http里(nginx.conf);
        limit_conn_status 503;	#限制时返回503; 
        limit_conn_log_level error;		#模块的错误日志级别;
        ...
        server
        {
            ...
            limit_conn aming 2;
            ...   
        }
    }
    

    说明:首先用limit_conn_zone定义了一个内存区块索引aming,大小为10m,它以$binary_remote_addr作为key。
    该配置只能在http里面配置,不支持在server里配置。

    limit_conn 定义针对aming这个zone,并发连接为10个。在这需要注意一下,这个10指的是单个IP的并发最多为10个。

    测试方式:
    具体虚拟主机配置:

    server {
        listen 80;
        server_name www.aaa.com;
            root /data/wwwroot/www.aaa.com/;
            index index.html;
    limit_conn aming 2;	#并发数量为2;
    access_log /data/logs/aaa.log main;
            }
    

    使用ab压力测试,需要安装httpd使用;

    yum install -y httpd
    ab -n 5 -c 5 http://www.aaa.com/tom.tar  #命令后查看日志;-n是请求的数量,-c是并发数量;
    日志结果:(由于上面并发数量为2,其他返回503;)
    192.168.87.141 - - [25/Oct/2019:20:12:05 +0800] "GET /tom.tar HTTP/1.0" 503 197 "-" "ApacheBench/2.3" "-" "192.168.87.141" 
    192.168.87.141 - - [25/Oct/2019:20:12:05 +0800] "GET /tom.tar HTTP/1.0" 503 197 "-" "ApacheBench/2.3" "-" "192.168.87.141" 
    192.168.87.141 - - [25/Oct/2019:20:12:05 +0800] "GET /tom.tar HTTP/1.0" 503 197 "-" "ApacheBench/2.3" "-" "192.168.87.141" 
    192.168.87.141 - - [25/Oct/2019:20:12:05 +0800] "GET /tom.tar HTTP/1.0" 200 9672042 "-" "ApacheBench/2.3" "-" "192.168.87.141" 
    192.168.87.141 - - [25/Oct/2019:20:12:05 +0800] "GET /tom.tar HTTP/1.0" 200 9672042 "-" "ApacheBench/2.3" "-" "192.168.87.141" 
    
    1. 速度限制

    location ~ /download/ {

    limit_rate_after 512k;
    limit_rate 150k;

    }

    说明:limit_rate_after定义当一个文件下载到指定大小(本例中为512k)之后开始限速;
    limit_rate 定义下载速度为150k/s。

    注意:这两个参数针对每个请求限速。
    ngx_http_limit_req_module

    具体虚拟主机配置:

    server {
        listen 80;
        server_name www.aaa.com;
            root /data/wwwroot/www.aaa.com/;
            index index.html;
    #limit_conn aming 2;
    limit_rate 10k;
    access_log /data/logs/aaa.log main;
            }
    

    设置后直接使用浏览器下载服务器目录里的文件,设置不同速度,需要的时间不一样。

    ngx_http_limit_req_module

    该模块主要用来限制请求数。请求是连接后的发起的,一个连接可以有多个请求。

    1. limit_req_zone

    语法: limit_req_zone $variable zone=name:size rate=rate;
    默认值: none
    配置段: http

    • 设置一块共享内存限制域用来保存键值的状态参数。 特别是保存了当前超出请求的数量。

    • 键的值就是指定的变量(空值不会被计算)。还是以IP作为参数,对每个IP请求数和请求速度作出限制;
      如limit_req_zone $binary_remote_addr zone=one:10m rate=1r/s;

    • 说明:
      区域名称为one,大小为10m,平均处理的请求频率不能超过每秒一次,键值是客户端IP。
      使用$binary_remote_addr变量, 可以将每条状态记录的大小减少到64个字节,这样1M的内存可以保存大约1万6千个64字节的记录。
      如果限制域的存储空间耗尽了,对于后续所有请求,服务器都会返回 503 (Service Temporarily Unavailable)错误。
      rate速度可以设置为每秒处理请求数和每分钟处理请求数,其值必须是整数,所以如果你需要指定每秒处理少于1个的请求,2秒处理一个请求,可以使用 “30r/m”。

    1. limit_req

    语法: limit_req zone=name [burst=number] [nodelay];
    默认值: —
    配置段: http, server, location

    • 设置对应的共享内存限制域和允许被处理的最大请求数阈值。

    • 如果请求的频率超过了限制域配置的值,请求处理会被延迟,所以所有的请求都是以定义的频率被处理的。

    • 超过频率限制的请求会被延迟,直到被延迟的请求数超过了定义的阈值,这时,这个请求会被终止,并返回503 (Service Temporarily Unavailable) 错误。
      这个阈值的默认值为0。如:
      limit_req_zone $binary_remote_addr zone=aming:10m rate=1r/s;
      server {
      location /upload/ {
      limit_req zone=aming burst=5;
      }
      }
      限制平均每秒不超过一个请求,同时允许超过频率限制的请求数不多于5个。

    • 如果不希望超过的请求被延迟,可以用nodelay参数,马上返回503, 如:
      limit_req zone=aming burst=5 nodelay;

    • 示例

    http {
        limit_req_zone $binary_remote_addr zone=aming:10m rate=2r/s; #把这行放在http里;
    
        server {
            location  ^~ /download/ {  
                limit_req zone=aming burst=5;  #生产中burst建议足够大;
            }
        }
    }
    
    • 测试配置
    server {
        listen 80;
        server_name www.aaa.com;
            root /data/wwwroot/www.aaa.com/;
            index index.html;
     limit_req zone=aming burst=5;
    access_log /data/logs/aaa.log main;
            }
    
    ab测试命令:
    ab -n 10 -c 10 www.aaa.com/tom.tar
    
    日志结果:
    发送了10个请求,六个200状态,4个503状态,除去第一个被处理的请示,同时处理了五个请求,余下超时返回503;
    
    展开全文
  • select * from test a inner join (select id from test where val=4 limit 300000,5) b>为了防止上次试验的影响,我们需要清空buffer pool,重启mysql。 mysqladmin shutdown /usr/local/bin/mysqld_safe & mysql >...

    点击上方“朱小厮的博客”,选择“设为星标”

    后台回复”加群“加入公众号专属技术群

    来源:rrd.me/fe8TT

    一,前言 

    首先说明一下MySQL的版本:

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.17 |
    +-----------+
    1 row in set (0.00 sec)

    表结构:

    mysql> desc test;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | val    | int(10) unsigned    | NO   | MUL | 0       |                |
    | source | int(10) unsigned    | NO   |     | 0       |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)

    id为自增主键,val为非唯一索引。

    灌入大量数据,共500万:

    mysql> select count(*) from test;
    +----------+
    | count(*) |
    +----------+
    | 5242882 |
    +----------+
    1 row in set (4.25 sec)

    我们知道,当limit offset rows中的offset很大时,会出现效率问题:

    mysql> select * from test where val=4 limit 300000,5;
    +---------+-----+--------+
    | id      | val | source |
    +---------+-----+--------+
    | 3327622 |   4 |      4 |
    | 3327632 |   4 |      4 |
    | 3327642 |   4 |      4 |
    | 3327652 |   4 |      4 |
    | 3327662 |   4 |      4 |
    +---------+-----+--------+
    5 rows in set (15.98 sec)

    为了达到相同的目的,我们一般会改写成如下语句:

    mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
    +---------+-----+--------+---------+
    | id | val | source | id |
    +---------+-----+--------+---------+
    | 3327622 | 4 | 4 | 3327622 |
    | 3327632 | 4 | 4 | 3327632 |
    | 3327642 | 4 | 4 | 3327642 |
    | 3327652 | 4 | 4 | 3327652 |
    | 3327662 | 4 | 4 | 3327662 |
    +---------+-----+--------+---------+
    5 rows in set (0.38 sec)

    时间相差很明显。

    为什么会出现上面的结果?我们看一下select * from test where val=4 limit 300000,5;的查询过程:

    查询到索引叶子节点数据。
    根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。


    类似于下面这张图:

    像上面这样,需要查询300005次索引节点,查询300005次聚簇索引的数据,最后再将结果过滤掉前300000条,取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上,而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

    肯定会有人问:既然一开始是利用索引的,为什么不先沿着索引叶子节点查询到最后需要的5个节点,然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O,类似于下面图片的过程:

    其实我也想问这个问题。

    证实

    下面我们实际操作一下来证实上述的推论:

    为了证实select * from test where val=4 limit 300000,5是扫描300005个索引节点和300005个聚簇索引上的数据节点,我们需要知道MySQL有没有办法统计在一个sql中通过索引节点查询数据节点的次数。我先试了Handler_read_*系列,很遗憾没有一个变量能满足条件。

    我只能通过间接的方式来证实:

    InnoDB中有buffer pool。里面存有最近访问过的数据页,包括数据页和索引页。所以我们需要运行两个sql,来比较buffer pool中的数据页的数量。预测结果是运行select * from test a inner join (select id from test where val=4 limit 300000,5) b>之后,buffer pool中的数据页的数量远远少于select * from test where val=4 limit 300000,5;对应的数量,因为前一个sql只访问5次数据页,而后一个sql访问300005次数据页。

    select * from test where val=4 limit 300000,5

    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    Empty set (0.04 sec)

    可以看出,目前buffer pool中没有关于test表的数据页。

    mysql> select * from test where val=4 limit 300000,5;
    +---------+-----+--------+
    | id      | val | source |
    +---------+-----+--------+
    | 3327622 |   4 |      4 |
    | 3327632 |   4 |      4 |
    | 3327642 |   4 |      4 |
    | 3327652 |   4 |      4 |
    | 3327662 |   4 |      4 |
    +---------+-----+--------+
    5 rows in set (26.19 sec)

    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    +------------+----------+
    | index_name | count(*) |
    +------------+----------+
    | PRIMARY    |     4098 |
    | val        |      208 |
    +------------+----------+
    2 rows in set (0.04 sec)

    可以看出,此时buffer pool中关于test表有4098个数据页,208个索引页。

    select * from test a inner join (select id from test where val=4 limit 300000,5) b>为了防止上次试验的影响,我们需要清空buffer pool,重启mysql。
    mysqladmin shutdown
    /usr/local/bin/mysqld_safe &
    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    Empty set (0.03 sec)

    运行sql:

    mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
    +---------+-----+--------+---------+
    | id | val | source | id |
    +---------+-----+--------+---------+
    | 3327622 | 4 | 4 | 3327622 |
    | 3327632 | 4 | 4 | 3327632 |
    | 3327642 | 4 | 4 | 3327642 |
    | 3327652 | 4 | 4 | 3327652 |
    | 3327662 | 4 | 4 | 3327662 |
    +---------+-----+--------+---------+
    5 rows in set (0.09 sec)

    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    +------------+----------+
    | index_name | count(*) |
    +------------+----------+
    | PRIMARY | 5 |
    | val | 390 |
    +------------+----------+
    2 rows in set (0.03 sec)

    我们可以看明显的看出两者的差别:第一个sql加载了4098个数据页到buffer pool,而第二个sql只加载了5个数据页到buffer pool。符合我们的预测。也证实了为什么第一个sql会慢:读取大量的无用数据行(300000),最后却抛弃掉。


    而且这会造成一个问题:加载了很多热点不是很高的数据页到buffer pool,会造成buffer pool的污染,占用buffer pool的空间。

    遇到的问题

    为了在每次重启时确保清空buffer pool,我们需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup,这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载入在磁盘上备份buffer pool的数据。

    参考资料:

    1.https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

    2.https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html

    想知道更多?描下面的二维码关注我

    怎么加群?:>>>Learn More<<

    怎么免费加入知识星球:>>>Free<<<

    免费资料入口:后台回复“666”

    朕已阅 

    展开全文
  • 更优美的limit使用方法(limit 对mysql 数据查询的性能影响) 本文在zhangyachen的基础上重新排版 来源:zhangyachen https://github.com/zhangyachen/zhangyachen.github.io/issues/117 一,前言 首先说明...

    更优美的limit使用方法(limit 对mysql 数据查询的性能影响)

    本文在zhangyachen的基础上重新排版
    来源:zhangyachen

    一,前言

    首先说明一下MySQL的版本:

    mysql> select version();
    
    +-----------+
    | version() |
    +-----------+
    | 5.7.17    |
    +-----------+
    1 row in set (0.00 sec)
    

    表结构:

    mysql> desc test;
    
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | val    | int(10) unsigned    | NO   | MUL | 0       |                |
    | source | int(10) unsigned    | NO   |     | 0       |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    • id: 自增主键
    • val: 非唯一索引。

    数据量,共500多万

    mysql> select count(*) from test;
    
    +----------+
    | count(*) |
    +----------+
    |  5242882 |
    +----------+
    1 row in set (4.25 sec)
    

    当limit offset rows中的offset很大时,会出现效率问题:

    mysql> select * from test where val=4 limit 300000,5;
    
    +---------+-----+--------+
    | id      | val | source |
    +---------+-----+--------+
    | 3327622 |   4 |      4 |
    | 3327632 |   4 |      4 |
    | 3327642 |   4 |      4 |
    | 3327652 |   4 |      4 |
    | 3327662 |   4 |      4 |
    +---------+-----+--------+
    5 rows in set (15.98 sec)
    

    我们可以改写成如下语句(效率优化版)达到相同的目的:

    mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
    
    +---------+-----+--------+---------+
    | id      | val | source | id      |
    +---------+-----+--------+---------+
    | 3327622 |   4 |      4 | 3327622 |
    | 3327632 |   4 |      4 | 3327632 |
    | 3327642 |   4 |      4 | 3327642 |
    | 3327652 |   4 |      4 | 3327652 |
    | 3327662 |   4 |      4 | 3327662 |
    +---------+-----+--------+---------+
    5 rows in set (0.38 sec)
    

    时间相差很明显。

    为什么会出现上面的结果?
    我们看一下select * from test where val=4 limit 300000,5;的查询过程:

    1. 查询到索引叶子节点数据。

    2. 根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

    类似于下面这张图:

    像上面这样,

    1. 需要查询300005次索引节
    2. 查询300005次聚簇索引的数据
    3. 最后再将结果过滤掉前300000条,取出最后5条
      MySQL耗费了大量随机I/O在查询聚簇索引的数据上,而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

    肯定会有人问:既然一开始是利用索引的,为什么不先沿着索引叶子节点查询到最后需要的5个节点,然后再去聚簇索引中查询实际数据。

    这样只需要5次随机I/O,类似于下面图片的过程:

    证实

    下面我们实际操作一下来证实上述的推论:

    为了证实select * from test where val=4 limit 300000,5是扫描300005个索引节点和300005个聚簇索引上的数据节点,我们需要统计MySQL在一个sql中通过索引节点查询数据节点的次数。
    我先试了Handler_read_*系列,很遗憾没有一个变量能满足条件。

    通过间接的方式来证实:

    InnoDB中有buffer pool
    里面存有最近访问过的数据页,包括数据页和索引页

    所以我们需要运行两个sql,来比较buffer pool中的数据页的数量

    预测结果:
    select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;使用的buffer pool中的数据页的数量远远少于select * from test where val=4 limit 300000,5;对应的数量

    测试普通的limit查询

    检查buffer pool的使用量

    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    Empty set (0.04 sec)
    

    可以看出,目前buffer pool中没有关于test表的数据页。

    执行查询语句

    mysql> select * from test where val=4 limit 300000,5;
    
    +---------+-----+--------+
    | id      | val | source |
    +---------+-----+--------+
    | 3327622 |   4 |      4 |
    | 3327632 |   4 |      4 |
    | 3327642 |   4 |      4 |
    | 3327652 |   4 |      4 |
    | 3327662 |   4 |      4 |
    +---------+-----+--------+
    5 rows in set (26.19 sec)
    

    查看buffer pool的使用量

    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    
    +------------+----------+
    | index_name | count(*) |
    +------------+----------+
    | PRIMARY    |     4098 |
    | val        |      208 |
    +------------+----------+
    2 rows in set (0.04 sec)
    

    可以看出,此时buffer pool中关于test表有4098个数据页,208个索引页。

    清空buffer pool

    防止上次试验的影响,我们需要清空buffer pool,重启mysql。

    执行语句

    mysqladmin shutdown /usr/local/bin/mysqld_safe &
    
    

    检查buffer pool的使用量

    mysql> select index_name,count(*) from  information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    Empty set (0.03 sec)
    

    测试优化版的limit查询

    执行查询语句

    mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
    
    +---------+-----+--------+---------+
    | id      | val | source | id      |
    +---------+-----+--------+---------+
    | 3327622 |   4 |      4 | 3327622 |
    | 3327632 |   4 |      4 | 3327632 |
    | 3327642 |   4 |      4 | 3327642 |
    | 3327652 |   4 |      4 | 3327652 |
    | 3327662 |   4 |      4 | 3327662 |
    +---------+-----+--------+---------+
    5 rows in set (0.09 sec)
    

    查看buffer pool的使用量

    mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    
    +------------+----------+
    | index_name | count(*) |
    +------------+----------+
    | PRIMARY    |        5 |
    | val        |      390 |
    +------------+----------+
    2 rows in set (0.03 sec)
    

    我们可以看明显的看出两者的差别:
    第一个sql加载了4098个数据页到buffer pool
    第二个sql只加载了5个数据页到buffer pool
    符合我们的预测, 也证实了为什么第一个sql会慢:读取300000无用数据行,最后却抛弃掉。

    而且这会造成一个问题:浪费了buffer pool的空间

    遇到的问题

    为了在每次重启时确保清空buffer pool,我们需要关闭innodb_buffer_pool_dump_at_shutdown
    innodb_buffer_pool_load_at_startup
    这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载入在磁盘上备份buffer pool的数据。

    参考资料:

    1.https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
    2.https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html

    展开全文
  • limit以及limit offset注意事项

    千次阅读 2020-10-23 15:27:51
    limit 用法: limit m, n; # 表示从跳过 m 条数据开始取n行数据 # m 可选,表示跳过 m 条数据(默认为0) # n 必选,表示取几行数据 -- 查询前 10 条数据 select * from table_name limit 10; -- 以上写法等同于...
  • ​​ 在某些面试题中会遇到这样的问答或笔试题:“limit 0,1 和 limit 1有什么区别?” 要准确回答这个问题就等深入明白limit一个参数和两个参数的本质区别。 limit n,m 中的第一次参数n表示的游标的偏移量,初始...
  • 增加一个显示更多的功能 附上代码:使用实例在附件 代码如下: /** * demo: * 1.$(“#limittext”).limittext(); * 2.$(“#limittext”).limittext({“limit”:1}); * 3.$(“#limittext”).limittext({“limit”:1,”...
  • SAP MM PIR里的Lower Limit & Upper Limit

    千次阅读 2018-08-23 15:25:30
    SAP MM PIR里的Lower Limit &amp; Upper Limit 在PIR的价格的detail数据里,有2个字段:Lower Limit和Upper Limit。在今天之前,笔者从未注意过这2个字段,也没有用过它们。 今天某同行在微信上问我是否可以...
  • 因为在第二个示例SQL中,order by id desc中的id是主键,即是limit子句会影响排序结果,也是在相同排序列值的结果受影响,而id是唯一的,也就是说排序结果也是唯一的,是不受limit影响的。 所以,对于select ...
  • MySql:分页查询limit的正确用法

    千次阅读 2019-10-28 22:44:55
    MySql:分页查询limit的正确用法 -- 原始分页查询 -- 4500 ms -- 慢的原因:1.* 2.limit太大 select * from post_history limit 2000000,10; -- 用明确字段代替* -- 1600ms select id,uid,media_id,...
  • limit语法: select <列名>,<列名>,...from <表名> limit <参数值>; select * from product limit 3; +------------+--------------+--------------+------------+----------------+---...
  • k8s资源限制——LimitRange使用

    千次阅读 2020-09-04 18:47:41
    一、简介 前面我们介绍了k8s资源限制在容器中的使用: ... 在k8s集群中为了能够使系统正常稳定运行,通常会限制Pod的... k8s常见的资源管理方式:计算资源管理(Compute Resources)、资源的配置范围管理(LimitRange)...
  • MySQL中limitlimit_offset的区别

    千次阅读 2019-07-20 16:02:22
    今天上班写SQL的时候发现需要调用的方法需要传递limit参数和offset参数,而之前已经习惯于单纯使用limit的我瞬间感到极不适应,哈哈哈哈。于是就在网上找了一些资料查了一下,发现这俩还确实挺绕的。下面先来说说我...
  • delete后加 limit是个好习惯么

    万次阅读 多人点赞 2020-07-22 18:12:32
    在业务场景要求高的数据库中,对于单条删除和更新操作,在delete和update后面加limit 1绝对是个好习惯。比如,在删除执行中,第一条就命中了删除行,如果SQL中有limit 1;这时就return了,否则还会执行完全表扫描才...
  • mybatis-plus QueryWrapper 添加limit

    万次阅读 2021-06-23 18:06:42
    其实是在sql的最后加上 limit 相关的语句。 QueryWrapper wrapper = new QueryWrapper<>(); wrapper.last("limit 1");
  • 这条查询语句中 Group By 是按照 model 做分组, Having color_count>1 限定了车型颜色种类大于1,ORDER BY color_count desc 限定结果按照颜色种类倒序排列,而 LIMIT 2 限定只返回前3条数据。 那么在 Elastic...
  • nginx limit配置参数解读

    千次阅读 2019-07-23 11:25:00
    序 本文主要解析一下ngx_http_core_module、ngx_http_limit_conn_module以及ngx_...limit_rate 名称 默认配置 作用域 官方说明 中文解读 模块 limit_rate limit_rate 0; http, server, ...
  • mysql通过limit实现分页功能

    千次阅读 2019-06-18 13:55:42
    注:通过sql语句查询实现java类的分页效果,主要可以分为以下步骤:登录界面--->...访问dao层通过limit实现分页查询--->返回所需分页的页面。下面请参考具体代码! 1、创建PagBean类,分页所用到的一些基...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,084,419
精华内容 433,767
关键字:

limit