精华内容
下载资源
问答
  • 转:http://hi.baidu.com/dbaeyes/blog/item/eaf04a878bc6f326c65cc3c3.html小测一下 mysqllimit扫描行数 和order by和索引的关系 <br /> --查询条件字段都在索引里,数据都已经在数据库cache中...
    小测一下 mysql的limit 的扫描行数 和order by和索引的关系


    --查询条件字段都在索引里,数据都已经在数据库cache中
    索引如下
    KEY `IDX_USER_ID` USING BTREE (`user_id`,`do_date`,`SUS`,`r_type`,`rat`)

    root@my_db 11:25:04>explain SELECT t2.* from
    -> (select id
    ->    FROM my_test_table FF
    ->   WHERE user_id = 888
    ->     AND SUS = 0
    ->     AND r_type = 0
    ->     AND do_date > '2009-09-21 23:59:59'
    ->     AND do_date <= '2010-5-17 23:59:59'
    ->     AND rat = 1
    ->   ORDER BY do_date DESC
    ->   LIMIT 8000, 20) t1, my_test_table t2
    ->   where t1.id = t2.id/G
    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY
    table: <derived2>
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 20
    Extra:
    *************************** 2. row ***************************
    id: 1
    select_type: PRIMARY
    table: t2
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 8
    ref: t1.id
    rows: 1
    Extra:
    *************************** 3. row ***************************
    id: 2
    select_type: DERIVED
    table: FF
    type: range
    possible_keys: IDX_FDATE,IDX_USER_ID
          key: IDX_USER_ID
    key_len: 24
    ref: NULL
    rows: 1187276
      Extra: Using where; Using index
    3 rows in set (0.01 sec)

    --
    | Sending data       | 0.007728 |
    --

    root@my_db 11:27:14>
    root@my_db 11:27:27>explain SELECT t2.* from
    -> (select id
    ->    FROM my_test_table FF
    ->   WHERE user_id = 888
    ->     AND SUS = 0
    ->     AND r_type = 0
    ->     AND do_date > '2009-09-21 23:59:59'
    ->     AND do_date <= '2010-5-17 23:59:59'
    ->     AND rat = 1
    ->   ORDER BY do_date DESC
    ->   LIMIT 80000, 20) t1, my_test_table t2
    ->   where t1.id = t2.id/G
    *************************** 1. row ***************************
    。。。。省略。。。。
    *************************** 3. row ***************************
    id: 2
    select_type: DERIVED
    table: FF
    type: range
    possible_keys: IDX_FDATE,IDX_USER_ID
    key: IDX_USER_ID
    key_len: 24
    ref: NULL
    rows: 1187278
    Extra: Using where; Using index
    3 rows in set (0.08 sec)

    --
    | Sending data       | 0.076469 |
    --


    可以看到 在使用 LIMIT 8000, 20 和 LIMIT 80000, 20 的执行计划完全一样,都使用索引扫描并用索引进行排序。虽然在执行计划中看到的扫描行数都是相同的 1187263,但从实际测试来看,当使用LIMIT 8000, 20 用的时间(0.007728)是 LIMIT 80000, 20 (0.076469)的10倍

    所以可以确定使用limit 扫描数据的时候,当扫描到满足条件的记录之后,不会再继续扫描下去,而是直接返回数据结果
    --这个是和oracle的rownum < 的执行计划是类似的

    -------------------------
    --再看一种情况,

    root@my_db 11:55:51>explain SELECT t2.* from
    -> (select id
    ->    FROM my_test_table FF
    ->   WHERE user_id = 888
    ->     AND SUS = 0
    ->     AND r_type = 0
        ->     AND do_date > '2010-4-21 23:59:59'
    ->     AND do_date <= '2010-5-17 23:59:59'

    ->     AND rat = 1
    ->   ORDER BY id DESC  --使用ID排序
    ->   LIMIT 8000, 40) t1, my_test_table t2
    ->   where t1.id = t2.id/G
    *************************** 1. row ***************************
    ...
    *************************** 3. row ***************************
    id: 2
    select_type: DERIVED
    table: FF
    type: index
    possible_keys: IDX_FDATE,IDX_USER_ID
         key: PRIMARY 
    key_len: 8
    ref: NULL
    rows: 246140
    Extra:
    3 rows in set (0.15 sec)

    --索引直接走错,
    | Sorting result     | 0.152575 |
    | Sending data       | 0.002491 |


    相同的查询条件如果根据do_date排序,那么时间在0.001以下

    --去掉时间范围
    SELECT t2.* from
    (select id
    FROM my_test_table FF
    WHERE user_id = 888
    AND SUS = 0
    AND r_type = 0
    AND rat = 1
    ORDER BY id DESC
      LIMIT 8000, 40) t1, my_test_table t2
    where t1.id = t2.id;

    ... 
    *************************** 3. row ***************************
    id: 2
    select_type: DERIVED
    table: FF
    type: ALL
    possible_keys: IDX_USER_ID
       key: IDX_USER_ID
    key_len: 9
    ref:
    rows: 1188230
    Extra: Using filesort 
    ...
    3 rows in set (0.92 sec)

    | Sorting result     | 0.931782 |
    | Sending data       | 0.002151 | 


    虽然走对了索引,但是主键ID从索引组织结构看,在索引的最后一位,所以排序消耗了相当大的资源
    而且因为ID是主键,所以在存在其他查询条件(存在索引)的情况下,也可能也会走上主键索引导致错误的执行计划

    ----------

    对于数据量比较大,而且执行量很高的分页sql,尽可能将所有的查询字段包括在索引中,同时使用索引来消除排序。

    -- 关于mysql limit的具体信息可以参考手册
    http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

    7.2.17. LIMIT Optimization

    In some cases, MySQL handles a query differently when you are using LIMIT row_count and not using HAVING:
    • If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

    • If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

    • When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

    • In some cases, a GROUP BY can be resolved by reading the key in order (or doing a sort on the key) and then calculating summaries until the key value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.

    • As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS.

    • LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. (This trick does not work in the MySQL Monitor (the mysql program), which merely displays Empty set in such cases; you should instead use SHOW COLUMNS or DESCRIBE for this purpose.)

    • When the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

     

    展开全文
  • 原因是 偏移量越大扫描的行数越多 ,比如 limit 100000 , 10 ,意思是扫描符合条件的 1000010 只要后 10条记录,这样偏移量越大扫描行数越多,就越慢。改为关联子查询会较快些,把多表筛选 limit 改为 单表。与之前...

    1.limit 分页优化

    如果数据量很大页数较多,limit 偏移量越大 就会查询的速度越慢。

    原因是 偏移量越大扫描的行数越多 ,比如 limit 100000 , 10 ,意思是扫描符合条件的 1000010 只要后 10条记录,这样偏移量越大扫描行数越多,就越慢。

    改为关联子查询会较快些,把多表筛选 limit 改为 单表。与之前相比 会快些。

    如下sql.

    原sql: SELECT auc.CITY_ID,auc.CITY_NAME,fa.CREATE_TIME

    FROM BOSS_FINANCE_APPLY fa

    INNER JOIN BOSS_FINANCE_AUCTION auc ON fa.AUCTION_ID=auc.AUCTION_ID

    WHERE fa.APPLY_BACK_STATUS=0 AND fa.CITY_ID =2

    ORDER BY APPLY_STATUS,ID DESC LIMIT 800,20;

    优化后sql: SELECT auc.CITY_ID,auc.CITY_NAME,fa.CREATE_TIME

    FROM BOSS_FINANCE_APPLY fa

    INNER JOIN BOSS_FINANCE_AUCTION auc ON fa.AUCTION_ID=auc.AUCTION_ID

    INNER JOIN (SELECT ID FROM BOSS_FINANCE_APPLY WHERE APPLY_BACK_STATUS=0 and CITY_ID =2 ORDER BY APPLY_STATUS,ID DESC LIMIT 800,20) TFA //新增子查询优化

    WHERE fa.ID=TFA.ID ;

    优化后 查询时间会缩短原来的 几十倍到几百倍不等。

    2,查询数据时候 发现加上了 limit 1 ,sql不会使用之前优化后的索引,处理方法是,在sql语句中指定 使用某个索引。

    网上说 加limit不是导致 mysql使用不同索引的原因,这还是和mysql的 语法解析器有关,还有与mysql的版本有关系。

    强制索引是一种简单的解决这类问题的办法。

    如下sql:

    SELECT

    a.ID,

    a.AUCTION_ID

    FROM

    BOSS_FINANCE_APPLY a FORCE INDEX(IDX_STATUS_TYPE) //指定使用某个索引

    INNER JOIN BOSS_FINANCE_AUCTION b ON a.AUCTION_ID = b.AUCTION_ID

    AND a.APPLY_BACK_STATUS = 0

    AND a.ALLOCATION_TYPE = 0

    AND a.APPLY_STATUS < 2

    WHERE

    NOT EXISTS ( SELECT ( 1 ) FROM BOSS_FINANCE_CHANNEL_NO_TASK c WHERE c.CHANNEL_ID = b.SOURCE_ID OR c.CHANNEL_ID = b.CHANNEL_ID )

    ORDER BY

    a.ID limit 1;

    IDX_STATUS_TYPE 索引是:APPLY_STATUS和ALLOCATION_TYPE 的组合索引。

    展开全文
  • MYSQL有时会对没有having子句,带有limit关键字的查询进行优化:1:如果用limit子句选择很少的行数据集,mysql会利用索引来代替全表扫描;2:如果组合使用limit 和order by 查询,mysql会在满足limit数量限制时停止...

    如果要指定查询的数据行数,在查询语句中使用limit子句,而不是获取所有数据行结果集,然后去掉没用的数据。

    MYSQL有时会对没有having子句,带有limit关键字的查询进行优化:

    1:如果用limit子句选择很少的行数据集,mysql会利用索引来代替全表扫描;

    2:如果组合使用limit 和order by 查询,mysql会在满足limit数量限制时停止sort,而不是对所有数据sort.如果利用index来进行排序,过程很快,但如果走的是filesort,所有 匹配该查询的结果集(except limit)都会被获取,在满足limit子句数据行找到之前,大部分(全部)数据都会被sort(即找到所有满足条件的数据行,然后排序找到满足limit的前几条数据,然后再停止)。

    3:一个order by查询带有和不带有limit的返回集可能以不同的顺序,下面有介绍:

    4:如果组合使用limit和distinct关键字,mysql在找到row_count的唯一数据行时,立刻停止。

    5:一些情况下,group by(order by)可以通过读取key的顺序,此时,limit row_count可以限制不必要计算的group by值。

    6: 当客户端收到指定行数的时候,会中断查询,除非使用了SQL_CALC_FOUND_ROWS。

    7: limit 0直接返回空集,可以用来检查查询是否合法。

    8:当服务使用临时表,会使用limit子句来计算需要多少空间。

    当在order by列中具有相同值的很多行时,mysql server会不确定的以任何顺序返回这些行数据,换句话说,排序结果的顺序对非order by列来说是不确定的。

    mysql> SELECT * FROM ratings ORDER BYcategory;+----+----------+--------+

    | id | category | rating |

    +----+----------+--------+

    | 1 | 1 | 4.5 |

    | 5 | 1 | 3.2 |

    | 3 | 2 | 3.7 |

    | 4 | 2 | 3.5 |

    | 6 | 2 | 3.5 |

    | 2 | 3 | 5.0 |

    | 7 | 3 | 2.7 |

    +----+----------+--------+

    mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;+----+----------+--------+

    | id | category | rating |

    +----+----------+--------+

    | 1 | 1 | 4.5 |

    | 5 | 1 | 3.2 |

    | 4 | 2 | 3.5 |

    | 3 | 2 | 3.7 |

    | 6 | 2 | 3.5 |

    +----+----------+--------+

    如果要确定不适用limit情况下返回集的顺序(最好加上一个唯一列),如下:如果id 列为unique,可以这么使用:

    mysql> SELECT * FROM ratings ORDER BYcategory, id;+----+----------+--------+

    | id | category | rating |

    +----+----------+--------+

    | 1 | 1 | 4.5 |

    | 5 | 1 | 3.2 |

    | 3 | 2 | 3.7 |

    | 4 | 2 | 3.5 |

    | 6 | 2 | 3.5 |

    | 2 | 3 | 5.0 |

    | 7 | 3 | 2.7 |

    +----+----------+--------+

    mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;+----+----------+--------+

    | id | category | rating |

    +----+----------+--------+

    | 1 | 1 | 4.5 |

    | 5 | 1 | 3.2 |

    | 3 | 2 | 3.7 |

    | 4 | 2 | 3.5 |

    | 6 | 2 | 3.5 |

    +----+----------+--------+

    SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

    如果排序的个数N刚好能在sort buffer里面,那么服务就可以避免执行文件合并和并且把sort buffer当做一个优先级队列来处理:

    1:  扫描表,把选中的行都插入队列中,如果队列满了把最后一个剔除掉。

    2:  然后返回前N行,如果有跳过M,那么先跳过M行,然后返回之后的N行记录。

    之前使用的处理方法:

    1:  扫描表,重复下面的步骤直到结束

    2:输入select row直到sort buffer满。

    3:写入前N行到buffer,然后把前N行合并到文件。

    4:排序合并文件并返回前N行。

    扫描表的花费和队列和文件合并一样,所以优化器在选择的时候是根据其他花费的:

    1: 队列的方法会使用很多cpu来插入到队列。

    2: 合并文件会使用IO来读写文件,cpu来排序。

    优化器在行数和不同值N之间平衡。

    展开全文
  • 问题分析:1,explain当估计行数时,不考虑limit,因此可能会对查询估计过多的检查行数。2,类似于SELECT ... FROM TBL LIMIT N这样的全表扫描的查询因为用不到索引将要报告为慢查询,如果--log-queries-not-using-...

    问题分析:

    1,explain当估计行数时,不考虑limit,因此可能会对查询估计过多的检查行数。

    2,类似于SELECT ... FROM TBL LIMIT N这样的全表扫描的查询因为用不到索引将要报告为慢查询,如果--log-queries-not-using-indexes被开启的话;

    可以在配置文件中使用min-examined-row-limit=Num of Rows来设置,如果要检查的行数大于等于这个量的查询,才会被报告为慢查询。

    有关mysql慢查询的内容,可以参考:mysql打开慢查询日志的方法

    有关 mysql 慢查询日志

    mysql开启慢查询实例演练(图文)

    mysql开启慢查询日志的方法

    MySQL 5.1.6以上版本动态开启慢查询日志的方法

    mysql 慢查询的原因分析点滴

    学习开启mysql慢查询与分析查询语句

    mysql开启慢查询以检查查询慢的语句

    3,类似于这样形式的SELECT ... FROM TBL WHERE KEY_PART1=CONST ORDER BY KEY_PART2 LIMIT N,mysql也要估计出过多的检查行数。

    有关slow-query的一些参数:

    log-slow-queries -- 开启慢查询

    long_query_time=N -- 大于N秒的查询为慢查询,并且要满足min-examined-row-limit的要求

    log-queries-not-using-indexes  -- 记录不使用索引的为慢查询,并且要满足min-examined-row-limit的要求

    min-examined-row-limit=N -- 要检查的行数大于等于N时才记录为慢查询,前提是必须满足long_query_time和log-queries-not-using-indexes约束

    就介绍这些吧,希望对大家有所帮助。

    展开全文
  • 每条SQL都要进行limit 分页 1000 ,每次SQL扫描的时候都会多扫描出来1000依次类似 每个SQL1+1000+SQl n 1000扫描行数越来越多,SQL执行越来越慢那么我们这个时候应该如何进行优化呢?1.查看表结构:接下来看一下...
  • 如果要指定查询的数据行数,在查询语句中使用limit子句,而不是获取所有数据... 1:如果用limit子句选择很少的行数据集,mysql会利用索引来代替全表扫描; 2:如果组合使用limit 和order by 查询,mysql会在满足l...
  • 问题分析:1,explain当估计行数时,不考虑limit,因此可能会对查询估计过多的检查行数。2,类似于SELECT ... FROM TBL LIMIT N这样的全表扫描的查询因为用不到索引将要报告为慢查询,如果--log-queries-not-using-...
  • MySQL 优化Limit分页

    2013-08-03 22:23:00
    很多时候、我们需要选择出从指定位置开始的指定行数、此时、limit笑了 对于limit的定义是: limit x,y 表示从第x行开始选择y条记录 在业务需要分页操作的时候、我们通常采用limit+order by这对洗剪吹组合、高端洋气...
  • MySQLLIMIT与分页优化(续)

    千次阅读 2013-12-07 13:02:18
    但实际上,MySQL只有在扫描了所有满足条件的行以后,才会知道行数,所以加上这个提示以后,不管是否需要,MySQL都会扫描所有满足条件的行,然后再抛弃掉不需要的行,而不是在满足LIMIT行数后就终止扫描。...
  • 背景 一个行数为4亿条的表。...为什么mysql没有选择索引,而是全表扫描呢? 分析 mysql select 语法 SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL
  • 其中,rows用来表示在SQL执行过程中会被扫描行数,该数值越大,意味着需要扫描行数,相应的耗时更长。但是需要注意的是EXPLAIN中输出的rows只是一个估算值,不能完全对其百分之百相信,如EXPLAIN中对LIMITS的...
  • 原文地址 **待优化SQL: ** ... 每条SQL都要进行limit 分页 1000 ,每次SQL扫描的时候都会多扫描出来1000依次类似 每个SQL1+1000+SQl n 1000扫描行数越来越多,SQL执行越来越慢 那么我们...
  • mysql慢日志详解一 参数slow_query_log 是否打开慢日志 默认为1slow_query_log_file 设置慢日志的具体路径和文件 默认为 datadir/slow.loglong_query_time 语句执行时间记录阈值,默认1Smin_examined_row_limit 扫描...
  • 正确的显示随机消息

    2019-06-26 23:08:56
    mysql> select word from words order by rand() limit 3; 扫描行数2003,自己看扫描行数 三种rand方法
  • 一 参数 slow_query_log 是否打开慢日志 默认为1 slow_query_log_file 设置慢日志的具体路径和文件 默认为 datadir/slow.log long_query_time 语句执行时间记录阈值,默认1S min_examined_row_limit 扫描行数阈值,...
  • mysql优化技巧

    2017-11-24 18:13:00
    mysql索引优化explaintype 建议range级别key null时建议强制使用索引key_len 越少越好rows 扫描行数 extra using filesort 和 using temporary 不建议出现其它优化技巧in值不宜包含超过20个数值select *尽量指出对应...
  • MySQL优化系列

    2018-04-21 16:28:49
    (4)尽量使用limit进行对行数的打印。二.对其SQL自身优化1.对其自身的优化,在mysql中有my.cnf文件,对其配置的优化。例如SQL最大连接数量。2.进行拆表优化(1)垂直拆分:把常使用的列和主键放在一张表...
  • 慢查询日志是 MySQL 提供的一种日志记录,用于记录查询时间超过 long_query_time 的 SQL,并且还要对 min_examined_row_limit 进行校验 (若扫描行数小于该参数,则不会记录到慢查询日志中) 慢查询日志可用于查找执行...
  • MYSQL优化方法1.慢查询基础:优化数据访问 1.1确认是否检索了大量超过需要的数据; 如:查询不需要的记录,可添加... 1.2确认是扫描了额外的记录 最简单的衡量查询开销的三个指标:响应时间,扫描行数,返回行数;...
  • 一般优化器选择索引考虑的因素有:扫描行数,是否排序,是否使用临时表。 使用explain分析sql explain是很好的自测命令,勤于使用explain有助于我们写出更合理的sql语句以及建立更合理的索引: mysql> explain ...
  • MySQL查询性能优化

    2019-01-26 16:44:51
    文章目录优化数据访问减少请求的数据量减少服务器端扫描行数重构查询方式切分查询分解关联查询查询执行优化 Limit 分页延迟关联书签参考资料 优化数据访问 减少请求的数据量 只返回必要的行:使用LIMIT语句来...
  • MySQL11:Sql分页效率

    2018-10-17 17:54:41
    原理:记录当前页的最大值和最小值,计算跳转页和当前页的跳转偏移,由于页面相近这个偏移量不会很大,这样的话m值较小,大大减小扫描行数。其实传统的limit m ,n 相对偏移一直是第一页。这样的话,越往后效率越低。...
  • MySQl优化数据访问

    2020-12-10 17:13:26
    MySQl优化数据访问减少请求的数据量减少服务器端扫描行数切分大查询分解大连接查询 减少请求的数据量 1.只返回必要的列:最好不要使用 SELECT * 语句。 2.只返回必要的行:使用 LIMIT 语句来限制返回的数据。 3....
  • mysql查看执行计划

    2021-02-03 11:35:36
    mysql 的执行计划 1.mysql 是使用 explain +sql 语句 查看执行计划的. ...rows : mysql 预估为了找到所需的行而要读取的行数 limit limit 匹配后就不会继续进行扫描 mysql> SELECT * FROM user
  • mysql文档摘要续2

    2015-08-13 23:21:00
    mysql优化续: 1:show index from table_name查看索引使用情况其中...2:mysql评价索引扫描数据量非常大时可能会放弃使用,当使用limit时,情况可以改变,mysql会采用索引 3:针对mysql的行格式row format...
  • Mysql 查询性能优化

    2020-05-15 22:53:09
    注:本篇是《高性能Mysql》第三版的读书笔记 查询性能优化 查询性能低下的基本原因就是访问的数据量太多,而有些数据根本就用不着,或者mysql在进行分析... 扫描行数 返回的行数 响应时间是两个的和:服务时间.
  • 首先想到的,一般分页都是用 “limit 起始, 行数” 这样的方式。 上面的含义是:即使我们只要取得从第 2个开始的3 行数据,也要扫描前面的 5行并且丢弃前面的2行最终返回后面的 3行。 可以想象,当起始非常大而...

空空如也

空空如也

1 2 3
收藏数 59
精华内容 23
关键字:

limitmysql扫描行数

mysql 订阅