背景和问题
1. 背景
- 业务系统通常有分页的需求，MySQL分页使用limit offset, pageSzie。
2. 问题
- 当数据库表记录很大的时候，我们如果需要用limit做分页，会发现分页越往后（通常叫深度分页）查询越慢。
- 例如：
select * from Product where createTime > "2024-01-01 12:00:00" limit 10000000,10解决方案
1. 原因分析
- 前置知识：
- MySQL底层使用的数据结构是B+树。
- B+树索引可以分为两大类：聚簇索引（也叫主键索引）和非聚集索引（普通索引）。
- 聚簇索引（也叫主键索引）：聚簇索引是将数据行存储在索引的叶子节点中，也就是说，数据行和索引是在一起的。在InnoDB存储引擎中，聚簇索引通常是按照主键进行排序的。如果表中没有主键，InnoDB会自动创建一个隐藏的主键作为聚簇索引。一个表只能有一个聚簇索引。
- 普通索引（非聚集索引）：普通索引是将数据行的主键存储在索引的叶子节点中。普通索引可以有多个，可以根据需要创建。
- 如上图，通过普通索引（name字段生成的索引）获取主键。然后再通过主键去聚簇索引里，获取其他字段数据。通过聚簇索引B+树找到完整的数据。该过程比较专业的叫法也被称为“回表”
- 原因：
- 分析该sql的执行计划
select * from Product where createTime > "2024-01-01 12:00:00" limit 10000000,10- 第一步：先通过createTime这个普通索引定位到第一条记录
- 第二步：通过主键，去取聚簇索引（主键索引）查询age字段、name字段
- 第三步：下一条记录，同样也会执行回表操作
-
-
- 第四步：游标一直走到10000000条，只取后面的10条。【这里之前的10000000条就会扔掉，所以可以看到之前的回表操作没有任何意义，因为数据都会被丢掉】
- 第四步：游标一直走到10000000条，只取后面的10条。【这里之前的10000000条就会扔掉，所以可以看到之前的回表操作没有任何意义，因为数据都会被丢掉】
-
- 总结：就是无用的回表操作太多了，优化思路就是减少这些无用的回表
2. 解决方案
思路：减少无用的回表操作（只查出id，不查其他字段，就不会触发回表）
a. 方案1-子查询
select * from product where id in(select p.id from Product p where p.createTime > "2024-01-01 12:00:00" limit 10000000,10)- 第一步：子查询，会根据createTime字段的索引，查询出符合要求的10条记录（这个过程由于只查id，所以不会有回表操作）
- 第二步：父查询，会根据主键索引，查询出10条记录
b. 方案2-innner join
思路和子查询类似，都是分页查询先不回表，只查出要的id
select * from Product p1 inner join (select p.id from Product p where p.createTime > "2024-01-01 12:00:00" limit 10000000,10) as p2 on p1.id = p2.id- 第一步：子查询，会根据createTime字段的索引，查询出符合要求的10条记录（这个过程由于只查id，所以不会有回表操作）
- 第二步：inner join的时候再查出要的数据
