7、limit分页

无论是否有索引，limit分页是一个值得关注的问题

假如一张表有5000000条数据

-- 不可取SELECT * FROM usr LIMIT 2000000,5;   -- 从两百万条，开始取5条，那么会全表扫描到两百万条的位置开始取5条，效率很低-- 这个办法也不可取，比上面好SELECT * FROM usr id > (SELECT id FROM usr LIMIT 2000000,1) LIMIT 5; -- 先从ID找两百万条的位置取一条id，id有索引只查ID会走索引到两百万的位置，得到ID后将ID作为查询条件，会直接从这个ID查找，不在全表扫描

分页优化方案

每页显示10条：当前 118 120， 125倒序：            大      小            980    970  7 6  6 5  54  43  3221 19 98     下一页：        -- 有页面    select         *     from         tb1     where         nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)      order by         nid desc     limit 10;        -- 无页面    select         *     from         tb1     where         nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)      order by         nid desc     limit 10;上一页：        -- 有页面    select         *     from         tb1     where         nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)      order by         nid desc     limit 10;        -- 无页面    select         *     from         tb1     where         nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)      order by         nid desc     limit 10;