记一个mysql分页查询优化试验

问题

分页查询的时候，如果直接 select 字段1，字段2，...，字段n from 表名 limit offset，pageSize，会随着offset增大越来越慢。

解决思路

通过查看执行计划是因为type是all，即全表扫描了，所以思路就是走索引。分页查询要求：

• 数据条数正确
• 单页和多页综合起来都没有重复数据

试验

新建表 product_config，有主键。 create table if not exists product_config( configuration_id varchar(50) not null primary key comment "配置模型id，主键"， product_id varchar(50) not null comment "产品id"， product_no varchar(50) not null comment "产品号"， product_desc varchar(300) comment "产品描述信息", product_group_id varchar(50) not null comment "产品族id", project_id varchar(30) not null comment "项目id", created_by varchar(50) not null comment "创建此行数据者id", created_time datetime default now() comment "数据入表时间", updated_time datetime default now() comment "数据跟新时间", maintain_by varchar(50) not null comment "此表的维护责任人")ENGINE=InnoDB default charset=utf8;

写入几百万条数据（友情提示，不要手动操作，晚上写一个定时任务批量往表里写，睡到第二天就有了，注意的是 mysql 默认一个sql写入最多写4M大小，所以每批量不要大于4M）。

全表扫描方式分页查询 select configuration_id,product_id,product_no,product_desc,product_group_id,project_id,created_by,created_time,updated_time,maintain_byfrom product_config limit 2000000, 10000;

取执行时间，基本上1000多毫秒，慢，查看执行计划是全表扫描，不可取。

用走索引的方法

• 5.1 直接主键索引

  select configuration_id,product_id,product_no,product_desc,product_group_id,project_id,created_by,created_time,updated_time,maintain_byfrom product_config where configuration_id > 2000000 order by configuration_id limit 10000;

  不超过500毫秒，看执行计划只有一步，而且是走的索引，很快，但是因为这种方法是先取出来再排序然后再limit，会漏掉，达不到分页查询的要求，不可取

• 5.2 用主键排序再取

  select configuration_id,product_id,product_no,product_desc,product_group_id,project_id,created_by,created_time,updated_time,maintain_byfrom product_config pcwhere pc.configuration_id >= (select configuration_id from product_config order by configuration_id limit 2000000,1) limit 10000;

  比5.1稍慢，比全表扫描的快，看执行计划分两步，都是走的索引，数据不重复，不遗漏，推荐

• 5.3 另一种走索引方式 join

  select pc.configuration_id,pc.product_id,pc.product_no,pc.product_desc,pc.product_group_id,pc.project_id,pc.created_by,pc.created_time,pc.updated_time,pc.maintain_byfrom product_config pcjoin (select configuration_id from product_config order by configuration_id limit 2000000,10000) aon pc.configuration_id = a.configuration_id;

  比5.1和5.2稍慢，看执行计划分三步，有两步走了索引，一步全表扫描（但是扫的时查出来的数据了），可取。

总结

以上是生活随笔为你收集整理的记一个mysql分页查询优化试验的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。