limit 100,10
扫描符合条件的110条数据只要后10条记录,当偏移量100数值还不是很大的时候并不会产生性能问题。
limit 1000000,10的
但是当偏移量越大就会查询的速度越慢。因为需要扫描的数据也会越来越多,这样会直接导致磁盘IO飙升速度急剧下降。
优化思路:
方案一:
select xx,xx,xx from table_name where (id >= 10000) limit 10
记录每次取出的最大id, 然后where id >= 最大id
但是这种方案局限性很大只应用于特定条件下,没有任何的附加查询条件,如果我需要一些额外的查询条件,比如我只要某个用户的数据 ,这种方法就行不通了。其次必须要有自增索引列,而且数据在逻辑上必须是连续的,同时你还必须知道特征值。如此苛刻的要求,在实际应用中是不可能满足的。
方案二:
子查询优化法 ,先找出第一条数据,然后大于等于这条数据的id就是要获取的数据
缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性
select ixx,xx,xx from table_name where id >= (select id from table_name order by id limit 1000000,10) limit 10;
如果limit的offset值过大,用户也会翻页疲劳,你可以设置一个offset最大的,超过了可以另行处理,一般连续翻页过大,用户体验很差,则应该提供更优的用户体验给用户。
方案三:
select * from table_name where id in (select id from table_name where ( id > 1000 )) limit 1000000, 10;
select xx,xx,xx from table_name where id in (select id from table_name where ( ?条件)) limit 1000000, 10;
先查找出需要数据的索引列(假设为 id),再通过索引列查找出需要的数据。(不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据)
原理:
- 子查询只用到了索引列,没有取实际的数据,所以不涉及到磁盘IO,即使是比较大的 offset 查询速度也不会太差。
- 利用子查询的方式,把原来的基于 其他条件的搜索转化为基于主键(id)的搜索,主查询因为已经获得了准确的索引值,所以查询过程也相对较快。
方案四:
select xx,xx,xx from table_name inner join ( select id from table_name where (?条件) limit 1000000,10)
b using (id)
# using等价于join操作中的on,例如a和b根据id字段关联,那么以下等价using(id)
在数据量大的时候 in 操作的效率就不怎么样了,我们需要把 in 操作替换掉,使用 join 就是一个不错的选择。
如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!
创建索引:alter table table_name add index idx_pid_id(where条件, 主键id)
方案五:
通过前端业务控制,类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了。或者可以直接把limit偏移量限制低于某个数,超过这个数等于没数据。