前言
为提高查询效率,现提出以下优化思路,但是这种思路做排序算法(置顶、置底、手动录入排序号)影响效率,慎重使用,仅供参考。
库中添加page、index字段用以分割数据组,以便抽取执行ddl语句的公式,模拟:初始page为1 index为1,以600为一组,index值为600时,page加1 index从1开始计算
1.查询
参数定义:
p页面传递的页数
s页面传递的条数
1.1 获取当前page下起始条数
select (p * s - FLOOR(p * s /600 )* 600)
1.2. 获取当前page
select FLOOR(p*s/600) + 1
例: 其中x为通过 1.1 中 获取的起始条数
查询 第1页 每页20条
select * from employee_test where page = FLOOR(1*20/600) + 1 limit x,20 order by index_num
查询 第31页 每页20条
select * from employee_test where page = FLOOR(31*20/600) + 1 limit x,20 order by index_num
1.3 性能
查询第500页,每页20条
12000条数据时
-- 执行结果0.015 0.020 0.020 0.017 0.016 0.022 0.016 0.029 0.024 0.018
10次平均 0.01973
select * from employee_test where page = 17 limit 400,20
--执行结果0.023 0.019 0.016 0.019 0.022 0.023 0.018 0.017 0.020 0.015
10次平均 0.0192
select * from employee_test limit 10000,20
24000条数据时
-- 执行结果0.019 0.016 0.016 0.017 0.020 0.023 0.021 0.024 0.025 0.018
10次平均 0.0199
select * from employee_test where page = 17 limit 400,20
--执行结果0.018 0.018 0.017 0.025 0.018 0.023 0.024 0.020 0.019 0.024
10次平均 0.0206
select * from employee_test limit 10000,20
可见 在数据量较少时,添加page和index字段的性能与正常全表分页性能相比,执行效果略高,但是不明显,当数据达到一定量级,执行效率开始体现。继续测试
在对page和index建立联合索引后,10次执行效果
0.004 0.006 0.005 0.006 0.005 0.005 0.004 0.004 0.004 0.006
10次平均 0.0049在加上page和index联合索引,数据24000条,条件不变,执行效率有很大提高
需要注意的事这种思路,只适用于删除较少的业务,如果中间某条数据操作被删除,page和index维护起来会增加一定的工作量。慎重使用
2.排序
原算法做数据上移下移排序,没有问题但是如果涉及到置顶、置底操作可能涉及到全表更新,所以如果需要排序,可另外加一个排序字段,进行排序号的操作。