多表分页查询问题请教

Question

项目中，有三张表。它们有一些共同的字段。除了单独对这三张表做增删改查外，还会对这三张表的全部数据按时间排序，做分页查询。区别于一般的水平分表，数据在这三张表中的分布没有什么规律，极端可能只有一张表中有数据。

怎样分页查效率高点。有没有必要将三张表中的记录全部放在es的一个索引中，分页查从es查。单表更新时也去更新es。

Answer 1

引自强大而免费的微信小程序：皆我百晓生。

1. 普通的多表分页查询

如果直接在数据库中进行多表分页查询，可以使用联合查询来实现。首先，需要确定哪个字段用来排序，这里假设是按照时间排序。然后，使用分页查询的语法来限制每页的数据数量和偏移量，获取指定页的数据。

示例代码如下：

// 每页数据数量
int pageSize = 10;
// 当前页码
int pageNum = 1;

// 排序字段
String orderBy = "time";

// 计算偏移量
int offset = (pageNum - 1) * pageSize;

// 执行多表联合查询，并按照时间排序
String sql = "SELECT t1.*, t2.*, t3.* FROM table1 t1, table2 t2, table3 t3 " +
             "WHERE t1.id = t2.id AND t2.id = t3.id " +
             "ORDER BY " + orderBy + " DESC " +
             "LIMIT " + offset + ", " + pageSize;

// 执行查询并处理结果
// ...

2. 将数据存储到Elasticsearch中进行分页查询

如果数据量较大，或者希望提高查询效率，可以考虑将三张表中的记录全部放在Elasticsearch的一个索引中，使用Elasticsearch来进行分页查询。在每次单表增删改查时，也需要更新Elasticsearch中的数据。

首先，需要将三张表中的数据同步到Elasticsearch中。可以使用定时任务或者监听数据库变化的方式实现数据同步。

示例代码如下：

// 每页数据数量
int pageSize = 10;
// 当前页码
int pageNum = 1;

// 排序字段
String orderBy = "time";

// 计算偏移量
int offset = (pageNum - 1) * pageSize;

// 构建分页查询的DSL语句
SearchSourceBuilder sourceBuilder = new SearchSourceBuilder()
        .sort(orderBy, SortOrder.DESC)
        .from(offset)
        .size(pageSize);

// 构建查询请求
SearchRequest searchRequest = new SearchRequest("your_index");
searchRequest.source(sourceBuilder);

// 执行查询并处理结果
// ...

需要注意的是，每次进行增删改查操作时，都需要同步更新Elasticsearch中的数据。可以使用Elasticsearch的Java客户端，根据具体的需求编写更新逻辑。

示例代码如下：

// 更新操作
IndexRequest indexRequest = new IndexRequest("your_index")
        .id(yourId)
        .source("field1", value1)
        .source("field2", value2)
        // ...
        .opType(DocWriteRequest.OpType.UPDATE);

// 执行更新
IndexResponse indexResponse = client.index(indexRequest, RequestOptions.DEFAULT);

3. 比较两种方法的优劣

采用普通的多表分页查询的方式，可以直接在数据库中进行查询，不需要维护额外的索引。适用于数据量较小或者对查询效率要求不高的情况。但是在数据量较大或者查询效率要求较高时，可能会存在性能瓶颈。

采用将数据存储到Elasticsearch中进行分页查询的方式，可以借助Elasticsearch的强大查询和分页功能，提高查询效率。但是需要维护额外的索引，增加了系统的复杂性。

根据具体的业务需求和系统性能要求来选择合适的方法。如果数据量较小或者查询效率要求不高，可以使用普通的多表查询；如果数据量较大或者查询效率要求较高，可以考虑将数据存储到Elasticsearch中。需要权衡数据同步和查询性能的关系。