1、背景
mysql使用select * limit offset, rows分页在深度分页的情况下。性能急剧下降。
例如:select * 的情况下直接⽤limit 600000,10 扫描的是约60万条数据,并且是需要回表60W次,也就是说⼤部分性能都耗在随机访问上,到头来只⽤到10条数据(总共取600010条数据只留10条记录)
2、limit 语法解读
limit用于数据的分页查询,当然也会用于数据的截取,下面是limit的用法:
SELECT *
FROM
table
LIMIT [
offset,]
rows |
rows
OFFSET
offset
变形
第一种:SELECT * FROM table LIMIT offset, rows # 常用形式
-- 从0开始,截取5条记录,即检索行为1到5
select *
from
table
limit
0,
5
-- 注意: 关键字limit后面的两个参与用逗号分割
第二种:SELECT * FROM table LIMIT rows OFFSET offset
-- 从0开始,截取5条记录,即检索行为1到5
select *
from tb_account
limit
5
offset
0
-- 注意: 使用limit和offset两个关键字,并且各带一个参数,中间没有逗号分割
第三种:SELECT * FROM table LIMIT rows
-- 截取记录的前五行数据,可以理解为offset的默认值为0
select *
from tb_account
limit
5
3、优化方式
1. 模仿百度、谷歌方案(前端业务控制)
类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了
2. 记录每次取出的最大id, 然后where id > 最大id
select *
from table_name
Where
id > 最大
id
limit
10000,
10;
这种方法适用于:除了主键ID等离散型字段外,也适用连续型字段datetime等最大id由前端分页pageNum和pageIndex计算出来。
3. IN获取id
select *
from table_name
where
id
in (
select
id
from table_name
where (
user = xxx ))
limit
10000,
10;
4. join方式 + 覆盖索引(推荐)
select *
from table_name
inner
join (
select
id
from table_name
where (
user = xxx)
limit
10000,
10) b
using (
id)
如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!
select
id
from
test
where pid =
1
limit
100000,
10;
创建索引:
alter
table
test
add
index idx_pid_id(pid,
id)
4、案例
1. jdbcpagingReader使用方式
//MySqlPagingQueryProvider#
public static String
generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider,
boolean remainingPageQuery,
String limitClause) {
StringBuilder sql =
new StringBuilder();
sql.append(
"SELECT ").append(provider.getSelectClause());
sql.append(
" FROM ").append(provider.getFromClause());
buildWhereClause(provider, remainingPageQuery, sql);
buildGroupByClause(provider, sql);
sql.append(
" ORDER BY ").append(buildSortClause(provider));
sql.append(
" " + limitClause);
return sql.toString();
}
❝
解读:jdbcPageingreader中使用了limit 10 这种写法。默认是查出10条记录。等价于 limit 0,10
❞
2. db索引分区器使用方式
入参1:表名 如test_table
入参2:排序索引字段 可以是主键,也可以是其他索引。需要保证是唯一索引即可。如:id
入参3:主键可手动传入,也可以根据表名计算出来:现在只支持单列主键的。如:id
入参4: 具体表 要分多少块。如:4
-- 使用过程 1. 先统计多少数据
select
count(
1)
as countAllNumber
from test_table;
-- countAllNumber=200
-- 2. 在 根据需要分多少块,算出每块需要包含的数据量,即limit
-- countAllNumber /4 =200/4 =50; 也就是每块的数据量需要包含50个数据。需要算这50个数据的开始节点和结束节点
-- 3. 循环遍历按照主键自增的拍寻方式算出第一块。
-- 3.1 第一块开始节点为0
select
id
from test_table
where
id >=
0
order
by
id
limit
50,
1;
-- 算出第51个元素 如就51;那第一块的范围为【0,51);左闭右开
-- 3.2 第二块 开始节点为51
select
id
from test_table
where
id >=
51
limit
50,
1;
-- 算出第101个元素 如101;那第二块的范围为【51,101);左闭右开
-- 3.3 第三块类似,算出第三块的边界点为151.
select
id
from test_table
where
id>=
151 ;
-- 算出第四块的范围为 【151,+∞);左闭右开
使用:拿到每块的分块边界值。进行主键查找接口。
如第一块,已经有边界值为【0,51);
那么拼接的查询sql为 。需要的入参为表名,索引名,分区开始,分区结束
select
id
from test_table
where
id >=
0
and
id <
51
order
by
id