mysql下limit分页优化思路

mysql的分页查询是开发人员工作经常会遇到的问题，这里稍写几种简单优化方法。

#表结构
MySQL [test]> show create table house\G;
*************************** 1. row ***************************
       Table: house
Create Table: CREATE TABLE `house_backup` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `address` varchar(500) COLLATE utf8mb4_bin DEFAULT '',
  `area` varchar(100) COLLATE utf8mb4_bin DEFAULT '',
  `aspect` varchar(10) COLLATE utf8mb4_bin DEFAULT '',
  `house_type` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `price` varchar(10) COLLATE utf8mb4_bin DEFAULT '',
  `add_time` int(11) unsigned DEFAULT '0',
  `test_column` varchar(12) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2379248 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

ERROR: No query specified

#总数
MySQL [test]> select count(1) from house;
+----------+
| count(1) |
+----------+
|  1456960 |
+----------+
1 row in set (0.45 sec)

#初始位置查询
MySQL [test]> select id,title from house_backup limit 10\G;
#查询的数据忽略
10 rows in set (0.00 sec)

ERROR: No query specified

#10w的偏移量查询
MySQL [test]> select id,title from house limit 100000,10\G;
#查询的数据忽略
10 rows in set (0.03 sec)

ERROR: No query specified

#100w的偏移量查询
MySQL [test]> select id,title from house limit 1000000,10\G;
#查询数据忽略
10 rows in set (0.37 sec)

ERROR: No query specified

#随着偏移量增大，查询越来越耗时

 

这里用100w偏移量分页sql做优化

 

一. 使用 IN 关键字

#这里先查询id，再用in关键字查询，正常情况id是主键索引，效率很高，如果是其他字段需要先创建索引(查询id的sql不做打印)
MySQL [test]> select id,title from house_backup where id in (1000001,1000002,10000003,1000004,1000005,1000006,1000007,1000008,1000009,1000020)\G;
#查询数据忽略
Empty set (0.00 sec)

ERROR: No query specified

 

二. 使用 BETWEEN ... AND ...

MySQL [test]> select id,title from house_backup where id between 2172904 and 2172913; 
#查询数据忽略
11 rows in set (0.00 sec)

 

上面两种查询均需要保证id是有序。