全表扫描，速度极慢

  • limit 语句的查询时间与起始记录的位置成正比
  • MySQL 的 limit 语句是很方便，但是对记录很多的表并不适合直接使用   创建测试表：
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `test`.`t_user`( 
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`username` VARCHAR(200) NOT NULL DEFAULT '', 
PRIMARY KEY (`id`)
);

# 常用的分页查询SQL
    SELECT id,`username` FROM t_user LIMIT 100,100;
    SELECT id,`username` FROM t_user LIMIT 10000,100;
    SELECT id,`username` FROM t_user LIMIT 1000000,100;
    SELECT id,`username` FROM t_user LIMIT 5000000,100;

执行结果：

分析结论

  • 全表扫描，速度极慢
limit 语句的查询时间与起始记录的位置成正比
MySQL 的 limit 语句是很方便，但是对记录很多的表并不适合直接使用

优化办法（一） 利用主键索引来加速分页查询

SELECT * FROM t_user WHERE id > 1000000 LIMIT 100;
SELECT * FROM t_user WHERE id > 1000000 AND id <= 5000000+100;

分析结果：

分析语句执行：

EXPLAIN SELECT id,`name` FROM t_test LIMIT 1000000,100;
EXPLAIN SELECT * FROM t_user WHERE id > 1000000 LIMIT 100;
EXPLAIN SELECT * FROM t_user WHERE id > 1000000 AND id <= 5000000+100;

结果  

如果主键值不连续，该怎么分页？

  • 使用逻辑删除，不会造成主键不连续 使用上面的优化方式即可
SELECT * FROM t_user WHERE id > 1000000 LIMIT 100;
SELECT * FROM t_user WHERE id > 1000000 AND id <= 5000000+100;
  • 利用主键索引加速，再做表连接查询
SELECT 
  t.id,
  t.name,
FROM
  t_user AS t 
  JOIN 
    (SELECT 
      id 
    FROM
      t_user 
    LIMIT 1000000, 100) tmp 
    ON t.id = tmp.id ;

执行结果对比