-- CREATE TABLE CREATE TABLE `test` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- 关闭索引造数据 -- 关闭： ALTER TABLE `test` DISABLE KEYS ; -- 开启： ALTER TABLE `test` ENABLE KEYS; -- 过程造数据 create procedure my_procedure() begin DECLARE n int DEFAULT 1; WHILE n < 1000000 DO insert into test (id) value (n); set n = n + 1; END WHILE; end -- 调用过程 call my_procedure(); -- 删除过程 drop procedure if exists my_procedure; -- 查看数量 select MAX(id) from test;
想到有 子查询，延迟关联 ， IN ，BETWEEN AND。具体效果看执行计划的EXPLAIN 。
另外可以做逻辑分页，例如百万数据前提下，10%可以用正常分页，50% 设置正反双向，然后加上对应的分页优化。
先看看阿里推荐的
利用延迟关联或者子查询优化超多分页场景 说明：MYSQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。 正例：先快速定位需要获取的ID段，然后再关联 select a* from table1 a ,(select id from table1 where 条件 limit 100000,20)b where a.id=b.id;
-- 子查询方式，索引扫描 SELECT id FROM test WHERE id >= (SELECT id FROM test ORDER BY id LIMIT 500000 , 1) LIMIT 5;
-- 阿里 -- 利用延迟关联或者子查询优化超多分页场景 select a* FROM test a ,(select id from test where limit 100000,20)b where 1.id=b.id; -- in SELECT id FROM test WHERE id >= (SELECT id FROM test where id in (500000) LIMIT 5; SELECT id FROM test WHERE id in (500000,500001,500002,500003,500004)
-- join EXPLAIN SELECT id FROM test a INNER JOIN (select id from test where id >500000 limit 10) b USING(id); -- BETWEEN AND EXPLAIN SELECT id FROM test WHERE id BETWEEN 500000 AND 500004;
BETWEEN AND 如果int不连续，无法使用
IN 使用in会先查询出来一条id，之后再去和下面进行匹配，会进行表进行全表扫描！，但是如果知道确定的具体的对应的5个id，这个应该是最快的。
综合来讲阿里的方案是最有效，最符合实际开发的，效率也相对高。
小编最近又得到一种方式，假设是这样的，我们的表数据都有对应的add_time（创建时间），1百万的数据，分页的话记录最后一条数据的add_time，那么sql就变成以下这样：
SELECT id FROM test where add_time > 记录的时间 LIMIT 10根据对应条件的索引，快速找到位置，并只有10条检索信息，对于这种情况，每次都是检索10条数据，大大加快分页效率
