MySQL优化:order by和limit
发布网友
发布时间:2022-09-09 21:39
我来回答
共1个回答
热心网友
时间:2023-03-03 16:42
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、查询一条记录的下一条记录
查询一条记录的上一条记录