事情是酱紫的

业务背景

某App上，有一个业务模块，包含：主题、帖子。

主题：字面意思，比如“吃货”，“滑雪”，“摄影”等等，都是一个单独的主题，用于聚合一些相关的话题，由用户建立（所以主题可以非常多）。用户可以关注主题。主题可以设置公开/私有。对应的两张表subject, subject_member

CREATE TABLE `subject` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `create_time` bigint NOT NULL DEFAULT '0',
  `update_time` bigint NOT NULL DEFAULT '0',
  `public_type` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `subject_member` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `subject_id` int NOT NULL,
  `create_time` bigint NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `subject_member_user_id_IDX` (`user_id`, `subject_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

帖子：属于某一个主题，用于分享某一件具体的事情，比如“吃货”主题下，发一篇“XX路的卤煮特别赞”的帖子（为什么是“卤煮”，因为最近在看三体）

CREATE TABLE `subject_post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `content` varchar(100) DEFAULT NULL,
  `subject_id` int NOT NULL,
  `create_time` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `subject_post_create_time_IDX` (`create_time`,`id`,`subject_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

业务

有一个单独的帖子列表页

过滤条件：用户关注了对应的主题，或者主题本身是公开的(public_type=1)

排序：一级排序帖子的创建时间(create_time)倒序，二级排序 id倒序

简单总结一下业务特点：有表A和B，是一对多的关系，A是一方，B是多方，用户和表A直接关联。现在要查用户能查看到的B列表。

代码（Sql）怎么写

select sp.*
from subject_post sp 
left join subject s on s.id = sp.subject_id 
where s.public_type = 1 or sp.subject_id  in (select subject_id from subject_member where user_id = ?)
order by sp.create_time desc, sp.id desc limit ?,?

简单解释下：先子查询查出用户关注过的所有主题id，作为条件，对帖子和主题的join结果做in过滤

性能问题

首先明确一点，排序如果要做到性能好，就要尽量避免文件排序和临时表(都是遍历整张表)，也就是要争取做到用索引来排序。

所以这里优化的一个思路，既然排序规则是（一级帖子的创建时间(create_time)倒序，二级 id倒序），那么就在subject_post表上，建一个（create_time, id）开头的索引，并且因为要和subject表做join，为了避免扫描二级索引过程中频繁回表去找subject_id，因此把subject_id也加上，所以最终方案就是建立这样一个索引(create_time, id, subject_id)

我对此的预期是，mysql去扫描subject_post的二级索引，并在扫描过程中，逐渐对subject做过滤，等找到足够多的数据后，就直接返回。这样虽然会对二级索引做非常多的扫描，但是相比较于文件排序扫描整张表的聚集索引，开销仍然要小很多

我在本地做了一个测试，往这三张表插入了一些数据

经过explain后发现，当分页较浅时，确实是符合自己的期望，但当进行深度分页时，就会出现filesort，mysql竟然认为filesort更快，实际执行时间也由1秒内，提高到了3秒多。

加上force index，强制其走索引后，耗时又降了下来，但深度分页仍然会有接近1秒的耗时，在实际生产中，这个耗时是肯定不行的

解决方案：解决提出问题的人

这种问题对于Mysql来说，很棘手。所以，作为开发，PK产品是必备技能，既然从技术上解决不了，那就从“武力”上去解决，这种场景，用户很少会去翻很深的页，所以可以直接限制分页条数最多只能是1w条（ElasticSearch默认分页限制也是这个数，所以1W不是拍脑袋想的，是有依据的）。

技术研究

当然，作为技术人员，除了能解决产品经理外，还是需要对技术抱有深入研究的兴趣。那么问题来了，这种场景，如何能够达到正常分页，且能够保障性能。

保底方案

这个方案初看有点离谱，但仔细分析后，也不是那么的离谱：所有帖子和主题数据放内存，然后通过一定的机制来监听数据变化，在内存中做同步（比如zk，消息队列广播都可以做到实时监听）

您先别急着骂，听我慢慢道来

首先，所有帖子放内存，并不是把整张表都放进来，而是只放关键信息，并且用基本类型代替包装类型，以尽量减少对象的体积，例如帖子，需要的属性就是id，subjectId，createTIme，那么对应的类就是

/**
 * 内存中的帖子情况
 * 一个对象的大小：header=8，id=4，subjectId=4，createTime=8，总和=24
 *
 * 100 0000个帖子，对象本身：100 0000 * 24 /1024/1024 = 22M
 * 还需要一个100 0000长度的List：100 0000 * 4 /1024/1024 = 4M
 */
@Data
public class PostProperty {
    private int id;
    private int subjectId;
    private long createTime;
}

所以，100W的数据，经过合理设计，在内存中，其实只需要不到30M的内存。

那么这个列表页的流程就是，先查出用户关注的所有主题id，然后遍历内存中的这个集合，进行过滤，并找到对应的分页数据。拿到最终这一页的帖子id后，再去数据库查这几条帖子详细信息即可，数据库的压力就非常小了

打消疑虑

大家一般的思维里，对于“数据常驻内存”，都是有一些抵触的，但是仔细想想，在某些场景下，其实可以适当用用这种技巧。

第一个问题：数据太大，占用内存，导致别的请求无法拿到内存，最终OOM

经过上述分析，100W数据，需要不到30M内存，保守点，扩大5倍，也就是150M。那么在原有jvm参数的基础上，将堆扩大150M，并且都给到老年代，那OOM这个问题便不是问题了

第二个问题：影响GC

这些数据，变化的可能性不大，所以，经过若干次gc后，它们便会进入老年代，然后再经过若干次fullgc，这些数据就会进入老年代“底部”（空间整理的方向，如果是G1那就是那种老顽固对象所在的region），并且在后续的空间整理中，因为它已经在底部了，所以也无需再进行移动。并且，这些对象，也都不会产生对新生代的跨代引用，所以对ygc的扫描也没有影响。

结论，对于GC的影响，在服务初期，比较明显（会造成频繁ygc），但服务运行一段时间后，当这些数据进入老年代“底部”后，影响就很小了（fullgc的扫描阶段会增加部分工作量，但不会增加空间整理/复制的工作量），并且一个调优良好的jvm进程，fullgc周期是以天为单位的，所以这种影响微乎其微。

综上，分析后发现，其实这些数据常驻内存，也没有什么大的影响，但对性能的提升确实显而易见的。

锦上添花

如果觉得上百万数据放内存，还是不靠谱，那么可以和产品PK，比如要求只展示最近半年的帖子

sql优化

对于排序的优化，可以采用inner的形式，因为深度分页，可能会让mysql优化器放弃索引，所以这种情况下，可以采取先走索引查出id，然后再用inner join关联主表的方式，具体sql如下

elect post.* from subject_post post inner join (
    select sp.id id
    from subject_post sp 
    left join subject s on s.id = sp.subject_id 
    where s.public_type = 1 or sp.subject_id  in (select subject_id from subject_member where user_id = ?)
    order by sp.create_time desc, sp.id desc limit ?,?
) sub on sub.id = post.id

因为内层查询sub的select只包含一个id字段，所以可以使用覆盖索引，mysql就有充足的理由去选择索引，而不是选择filesort。当利用索引，查到分页数据的那些id去和subject_post表做关联，获取到最终数据。

经过测试，limit 20000, 10，采用inner join优化后，执行时间从3900ms，降低到了75ms，可以说是非常大的提升了

写在最后

学无止境，其实我所在项目，数据量根本达不到这个量级，即使有filesort，响应时间也不差，但做技术，还是要对性能有敬畏之心，不然永远都只能是一个低端劳动力，共勉（卷）！

一个Mysql的性能问题