mysql大数据量的分页优化

在开发过程中,相信大家都遇到过分页,几万条数据时分页可能没什么,加入有上千万或上亿条时,问题就随之而来。废话不多说,加入有会员表 members,

select * from members where uid = 10000 and is_disable = 0 order by uid asc limit 150000, 100;这条sql大家应该很熟悉,MySQL 执行此类SQL时需要先扫描到150000行，然后再去取 100行。对于此类操作，取前面少数几行数据会很快，但是扫描的记录数越多，SQL的性能就会越差，因为150000越大，MySQL需要扫描越多的数据来定位到具体的150000行，这样耗费大量的IO 成本和时间成本。海量数据情况下,就算加了索引也会很慢,那么有什么办法优化呢？

针对Limit优化方法：1 前端加缓存,减少落到数据库的查询.2 优化SQL 访问数据的方式，直接快速定位到要访问的数据行 3 记录上次查询最新/大的id值。

第一种方式不用多说,直接说第二种方式，快速定位利用延迟关联的方式，如下:

优化前:select * from members as m where uid = 10000 and is_disable = 0  and reg_time >='2014-05-29' order by uid asc limit 150000, 100;

优化后: select m.* from members as m,(select id from members where uid = 10000 and is_disable = 0  and reg_time >='2014-05-29' order by uid asc limit 150000, 100) as bm where m.id = bm.id;

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。

第三种方式优化：索引 kid(kid,type)

首先要获取复合条件的记录的最大 id和最小id(默认id是主键):select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;

其次 根据id 大于最小值或者小于最大值 进行遍历。

select xx,xx from t where      kid=2333 and type=1 and id >=min_id order by id asc  limit 100;

select xx,xx from t where      kid=2333 and type=1 and id <=max_id order by id  desc limit 100;

 

 

附加:优化分页有多种,这里仅仅说了两种,其他方式例如,限制分页数,不允许查询靠后的数据。或者查询下一页时把上一页的id传给下一页(类似第三种方式)等