深分页问题

SELECT id, user_id, `status`, type, created_at 
FROM t_order 
ORDER BY created_at 
LIMIT 250000, 10;
----------------------------------------------------------
200w数据耗时：1898ms
----------------------------------------------------------
EXPLAIN
id   select_type    table      type      rows     filtered       Extra
1	   SIMPLE	   t_order		ALL  	1993917	   100.00	    Using filesort

created_at
有索引，但是数据量过大，直接走ALL全表扫描；
LIMITE m, n
：查询m+n条记录，再过滤掉前m条记录；回表数据量过大

深分页问题核心：需要从头扫描数据，再过滤掉，取出需要的数据；往往数据量过大导致全表扫描；

解决目标：快速锁定分页的起始位置；减少查询数据量；前提：查询条件是可排序的；

1. 子查询减少回表数据量

子查询锁定索引位置

SELECT t.id, t.user_id, t.status, t.type, t.created_at
FROM t_order t
WHERE t.created_at >= (SELECT o.created_at FROM t_order o ORDER BY o.created_at LIMIT 250000, 1) 
ORDER BY t.created_at LIMIT 10;
----------------------------------------------------------
200w数据耗时：121ms
----------------------------------------------------------
EXPLAIN
id  select_type   table  type    possible_keys        key          rows    filtered    Extra
1   PRIMARY     t       range	 tdx_created_at	tdx_created_at	  996958   100.00   Using where
2   SUBQUERY    o	index	                tdx_created_at	  250001   100.00   Using index

子查询不需要回表，先锁定
created_at
查询条件的最小值，再执行LIMIT；
回表的数量就只有LIMIT的个数；并且是全要的，不需要再过滤，所以
filtered
都是100；

借助主键

SELECT t.id, t.user_id, t.status, t.type, t.created_at
FROM t_order t, (SELECT o.id, o.created_at FROM t_order o ORDER BY o.created_at LIMIT 250000, 10) o
WHERE t.id = o.id ORDER BY t.created_at;
----------------------------------------------------------
200w数据耗时：109ms
----------------------------------------------------------
EXPLAIN
id  select_type   table    type    possible_keys      key      ref   rows  filtered    Extra
1    PRIMARY	<derived2>  ALL				                        250010	 100.00	 Using temporary; Using filesort
1    PRIMARY    t         eq_ref      PRIMARY    PRIMARY       o.id      1	 100.00	
2    DERIVED    o          index                 tdx_created_at	    250010	 100.00	 Using index

o表使用
created_at
索引，直接获取10条数据，不回表，不过滤；
t表使用主键索引；
derived2：派生表，表示FROM中使用子查询；

2. 保存上页查询条件(游标)

游标的选择：尽可能简单；但是要根据具体的业务决定；

游标能够走索引，是一个前提；
必须是可排序的，通常是根据业务场景可排序的；

EXPLAIN
SELECT id, user_id, `status`, type, created_at 
FROM t_order 
WHERE created_at >= '2023-04-04 21:45:00' 
ORDER BY created_at LIMIT 10;
----------------------------------------------------------
200w数据耗时：39ms

当有具体的查询条件时，效率最高；
必须是连续分页，需要业务配合
需要保存上一页的最后一条数据的查询条件；比如不提供跳页，只有下一页的选项的场景；不太现实，已经是深分页了，只有下一页，要翻1w页吗；

3. 业务上避免深分页

遇到可能存在深分页问题，首先是业务上是否可以直接避免，既然是深分页了，这么大的数据量，对用户来说几乎没有需求；京东、淘宝的商品搜索，也只是提供最大100页的查询；从业务上直接不支持深分页；

4. 模糊总数，减少count压力

模糊显示某个搜索条件下的总数，在数据量超过某个值则直接显示1000+、10000+，而不显示具体数量；

-- 原sql
select count(*) from table where xxx=yyy;

-- 模糊显示
-- 如果返回的结果>1000，则直接显示1000+
-- <1000，则显示具体数值；
select count(*) from (select count(*) from table where xxx=yyy limit 1000);

MySQL实际业务方案