按id增序 导出t_order_detail表数据,由于数据量过多,防止一次查询数据量大多导致异常,批量查询数据,每次查询200条数据,数据量50万,查询出的数据量5万多条。
SQL如下
Explain结果
《高性能MySql第三版》章节6.7.5 优化Limit分页中提到,在偏移量非常大的时候,例如可能是LIMIT 1000,20 这样的查询,这时候MySQL需要查询10020条记录然后只返回最后20条,前面10000条记录都将被抛弃,这样的代价非常高。要优化此种查询,要么在页面中限制分页数量,要么是优化大偏移量的性能。使用“延迟关联”,它让MySQL扫描尽可能少的页面,获取需要要访问的记录后再根据关联列回原表查询需要的所有列。
Explain结果
也没看不出来区别,直接用SQL执行看消耗的时间
这个延迟关联蛮简单的(自我感觉),为啥MySQL不直接内部实现优化呢?
延迟关联到底节省了哪部分动作消耗的时间,如果只是如下的SQL,那就根本没必要关联,在查询了其他的字段后,才需要延迟关联。所以是节省了获取其他字段的消耗的时间?还是排序时多个字段后更加耗时?
当前SQL使用id排序,可以直接使用上一页数据最后一条数据的Id做筛选,这样直接筛选出需要的数据,查询查第49999条数据的order_id为707352,SQL如下
Explain结果
此种优化方法要求 使用唯一的字段排序。
高性能MySql
MySQL ORDER BY _ LIMIT performance_ late row lookups at EXPLAIN EXTENDED
1. 对order by使用复合索引order by和limit一起使用,避免引起全表扫描和数据排序是非常重要的,因此借助合适的索引提高查询效率。
使用联合索引
联合索引又叫复合索引,是由表中的几个列联合组成的索引。联合索引生效需满足最左前缀原则,即如果联合索引列为a,b,c三列,a,b,c 、a,b 、a生效,b,c、a,c、b、c等不生效(此处的顺序不是where条件后面的先后顺序,而是where条件中是否存在这些列,如果where中只存在a,c列,则不生效)。
索引生效,与where条件的顺序无关:
索引失效,与where条件的列是否存在有关:
带IN条件的联合索引失效
in的参数个数为1个,联合索引生效,大于1个,索引失效。所以使用了强制索引使联合索引生效。
原因分析:
第一、取决于B树的数据结构,单参数的IN只会得到一颗基于model子树,该子树的code本身是有序的,所以索引生效,查询效率高;多参数的IN会得到多颗基于model的子树,每颗子树的code字段是有序的,但是总体上可能不是有序的,所以索引失效,查询效率低。
第二、使用强制索引后,理论上无法保证order by的顺序,但是如果数据本身的特性,比如时间递增的这类数据,总体上还是有序的,笔者试过多中途径想要迫使强制索引得到错误的结果,结果都对了。强制索引需进一步研究。
2. 大数据量limit慎用
limit常用于分页中,有两种用法,三种写法:
偏移量offset较大的优化
limit偏移量较小时性能优秀,分页越到后面,偏移量递增,limit的性能会逐渐下降。
此时,通过子查询优化limit,效果如下:
以上数据来自一张超过2000万的MySQL单表,仅供参考,能够说明子查询明显能够提升效率,笔者开始尝试把子查询的order by去掉,发现查询效率又提升2倍,但是对比发现数据不正确,explain后发现查询优化器给出的子查询索引并不是id(此表建有多个索引,id是主键,区分度最高),这一点比较困惑。
ps:在sql语句中,limt关键字是最后才用到的。以下条件的出现顺序一般是:where->group by->having-order by->limit
mysql中查询第几行到第几行的记录
1、查询前n行
查询第一行
2、查询第n行到第m行
查询第4行到 第6行
3、查询后n行
查询最后一行
4、查询一条记录的下一条记录
查询一条记录的上一条记录
准备数据是20000000条数据
在分页场景下,使用limit start end,我们分别看下从10000, 100000, 1000000开始分页的执行时间(每页取10条),如下图
当start较小时,查询没有性能问题,但是如上图查询时间所示,随着start增大,查询消耗时间也在递增,在start=10000000时,分页竟然消耗了2秒多,这是不能忍受的。
由此引出对limit分页的优化,首先来explain该语句,看到查询没有使用到任何的索引,进行的是全表扫描,假如limit分页用到了索引是不是会快很多呢!
explain分析一下,第一行是select * from user_innodb形成的临时表使用的是全表扫描,第二行是 (SELECT id FROM user_innodb LIMIT 10000000, 10)形成的,使用的是eq_ref,第三行是全表扫描a和bjoin形成的派生表,使用到的是index,所以速度也会快很多
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)