日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

基于索引的單表查詢，是 MySQL 正確打開方式！

基于 QueryObject 的聲明式查詢，是簡單查詢的正確使用方式！

1、應用場景

單表查詢在業務開發中占比最大，是所有 CRUD Boy 的入門必備，所有人在 JAVABean 和 SQL 之間樂此不疲。

整體架構如下圖所示：

這是一個簡單的分層架構，主要有：

• 接入層：接收用戶或其他服務的請求，對參數進行基本驗證。
• 服務層：執行簡單的業務邏輯，比如業務驗證、數據轉換、數據組裝等。
• 數據訪問層。在 ORM 框架基礎之上完成對數據庫的訪問。
• 數據庫層。負責數據存儲和查詢。

其中 ORM 框架尤為重要，幫我們完成 對象 與 關系數據 間的相互轉換。因此，不少人認為玩好 ORM 就成為了高級開發人員。而實際情況是：該部分是最枯燥、最沒有技術含量的“技能”。

目前，最常見的 ORM 便是 MyBatis 和 JPA，以一個簡單的分頁查詢 User 為例做一個簡短介紹。

按照用戶狀態分頁查詢 User 信息：

• 用戶狀態和分頁參數必填。
• 其他參數手機號、生日區間選填。

查詢入參如下：

@Data
public class QueryUserByStatus {
    private Integer status;
    private String mobile;
    private Date birthAfter;
    private Date birthBefore;
    private Pageable pageable;
}

接口簽名如下：

Page<User> queryByStatus(QueryUserByStatus queryByStatus);

這個是最簡單的 case，分別使用 MyBatis 和 Jpa 進行實現。

（1）MyBatis

MyBatis是一款基于 Java 語言的持久層框架，它為SQL映射、數據處理和事務管理提供了優秀的支持。MyBatis已成為使用最廣泛的ORM框架之一，它支持極為靈活的自定義SQL，同時也提供了與Spring Framework和Spring Boot等流行框架的集成方案，為Java程序員提供了極大的便利。

基于MyBatis實現的核心代碼如下：

@Autowired
private MyBatisUserMApper userMapper;
public Page<MyBatisUser> queryByStatus(QueryUserByStatus query){
    // 狀態不填
    if (query.getStatus()  null){
        throw new IllegalArgumentException("status can not null");
    }
    // 分頁必填
    if (query.getPageable()  null){
        throw new IllegalArgumentException("pageable can not null");
    }
    MyBatisUserExample userExample = new MyBatisUserExample();
    MyBatisUserExample.Criteria criteria = userExample.createCriteria();
    // 添加狀態過濾
    criteria.andStatusEqualTo(query.getStatus());
    // 添加手機號過濾
    if (query.getMobile() != null){
        criteria.andMobileEqualTo(query.getMobile());
    }
    // 添加生日過濾
    if (query.getBirthAfter() != null){
        criteria.andBirthAtGreaterThan(query.getBirthAfter());
    }
    // 添加生日過濾
    if (query.getBirthBefore() != null){
        criteria.andBirthAtLessThan(query.getBirthBefore());
    }
    // 添加分頁信息
    userExample.setOffset(query.getPageable().offset());
    userExample.setRows(query.getPageable().getPageSize());
    // 查詢數據
    long totalItems = this.userMapper.countByExample(userExample);
    List<MyBatisUser> users = this.userMapper.selectByExample(userExample);
    // 封裝結果
    return new Page<>(users, query.getPageable(), totalItems);
}

（2）Jpa

JPA是Java Persistence API（Java持久化API）的簡稱，它是Sun官方提供的一套標準的ORM框架（對象關系映射框架）。JPA提供了一種以面向對象方式來管理關系型數據庫的方法，使開發人員可以使用對象而不是SQL來操作數據庫。JPA提供了一套公共的API，使開發人員可以在不同的ORM實現（如Hibernate、EclipseLink等）中自由切換。

基于Jpa實現的核心代碼如下：

@Autowired
private JpaUserRepository jpaUserRepository;
public Page<JpaUser> queryByStatus(QueryUserByStatus queryByStatus){
    // 狀態必填
    if (queryByStatus.getStatus()  null){
        throw new IllegalArgumentException("status can not null");
    }
    // 分頁必填
    if (queryByStatus.getPageable()  null){
        throw new IllegalArgumentException("pageable can not null");
    }
    // 構建分頁參數
    Pageable pageable = PageRequest.of(queryByStatus.getPageable().getPageNo(), queryByStatus.getPageable().getPageSize());
    // 構建過濾條件
    Specification<JpaUser> spec = Specification.where((root, query, cb) -> {
        List<Predicate> predicates = Lists.newArrayList();
        // 添加狀態過濾
        Predicate statusPredicate = cb.equal(root.get("status"), queryByStatus.getStatus());
        predicates.add(statusPredicate);
        // 添加手機過濾
        if (queryByStatus.getMobile() != null){
            Predicate mobilePredicate = cb.equal(root.get("mobile") , queryByStatus.getMobile());
            predicates.add(mobilePredicate);
        }
        // 添加生日過濾
        if (queryByStatus.getBirthAfter() != null){
            Predicate birthAfterPredicate = cb.greaterThan(root.get("birthAt") , queryByStatus.getBirthAfter());
            predicates.add(birthAfterPredicate);
        }
        // 添加生日過濾
        if (queryByStatus.getBirthBefore() != null){
            Predicate birthBeforePredicate = cb.lessThan(root.get("birthAt") , queryByStatus.getBirthBefore());
            predicates.add(birthBeforePredicate);
        }
        // 組合過濾條件
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    });
    // 查詢數據
    org.springframework.data.domAIn.Page<JpaUser> all = this.jpaUserRepository.findAll(spec, pageable);
    // 封裝結果
    return new Page<>(all.getContent(), queryByStatus.getPageable(), all.getTotalElements());
}

（3）問題分析

通常情況下，使用哪個 ORM 框架，都是由公司規范規定，一般人沒辦法左右。但，無論使用哪個框架，面對的問題基本是一致的。

這種開發模型，存在以下幾個問題：

• 過于繁瑣，開發效率低：一個簡單的查詢請求，包括參數驗證、ORM API調用、數據轉換等工作，涉及多個層次多個類的協調一致，常見問題包括：
• 重復性勞動：沒有什么技術含量，首先是使用 “字段” 或 “屬性” 調用各種 API，然后是各種類型間的轉化，枯燥無味。
• 容易出錯：涉及參數和字段較多，容易設置錯位，比如參數設置錯誤、對象轉換時字段設置錯誤等。
• 性能瓶頸：實際開發中，性能瓶頸并沒有在 ORM 框架本身，主要是對 MySQL 使用不當，特別是沒有發揮索引的優勢，常見問題包括：
• 沒有合適索引：設計之初并未考慮索引，或者對索引缺乏有效的管理。
• 參數丟失導致無法使用索引：參數丟失導致最左匹配原則被破壞，無法高效的使用索引。
• 返回結果過多導致性能低下：一次性返回大量數據，增加 DB 和 應用程序的負載，最終導致性能低下。

2、MySQL 查詢正確打開方式

MySQL 常見的查詢優化手段非常多：

1. 索引優化：分析表數據和查詢需求，創建合適的索引來提高查詢效率。
2. SQL語句優化：優化SQL語句的寫法，避免使用子查詢、聯合查詢、多層嵌套等耗費資源的操作。
3. 數據庫結構優化：合理設計數據庫結構，避免冗余數據以及過多分表分庫導致性能低下。
4. 控制結果集大小：查詢的結果集越大，查詢時間就越長。盡量限制結果集大小，避免不必要的計算。
5. 數據庫連接池優化：通過優化數據庫連接池的配置，避免連接池滿載以及連接超時等問題，提高數據庫處理效率。
6. 數據庫批量操作優化：通過批量操作來減少單次與數據庫的交互次數，提高執行效率。

在眾多優化方式中選擇最主要的一項便是：索引優化：

1. 提升基于 WHERE 條件的查詢性能：在 WHERE 條件中使用了索引，可以更快地定位到匹配行，避免全表掃描。
2. 提升基于范圍查詢的查詢性能：如果僅需要一個范圍，而不是整個表的數據，索引可以提高查詢效率。
3. 提升排序和分組查詢性能：索引可以讓 MySQL 更快地執行排序和聚合，快速定位數據，而不是遍歷整個表。

（1）B+Tree 與 高效查詢

B+Tree 在 MySQL 中極為重要，它既是一種數據的組織結構，比如聚簇索引。又是查詢優化最重要的一種手段，比如非聚簇索引。

B+Tree

B+Tree 在 MySQL 中是如此重要，它是 MySQL 使用的默認索引。B+Tree 索引不僅可以加速單個鍵值查詢，還可以支持范圍查找并為查詢結果排序。此外，B+Tree 還可以支持高效的插入和刪除操作，當在一個 B+Tree 索引中插入或刪除記錄時，B+Tree 索引通過特定規則進行拆分和合并來實現重新平衡。
在 MySQL 中，B+Tree 索引不僅適用于普通表，還適用于主鍵索引、唯一索引、輔助索引等。因此，了解 B+Tree 索引的設計和原理對于開發高效、可擴展的 MySQL 應用程序至關重要。

以下是一個 B+Tree 的示意圖：

B+Tree作為一種數據組織方式，有以下幾個特點：

• 非葉子節點只存儲關鍵字和頁碼，而不保存數據。這也是B+Tree和B-Tree的主要區別，這種特性使得B+Tree可以更快的查找特定關鍵字。
• 葉子節點包含所有數據和關鍵字，形成一個有序鏈表。這種結構使得B+Tree在范圍查詢時更高效。
• 支持高效范圍查找，基于在葉子節點形成的有序鏈表，可以更快地查找滿足查詢條件的數據。
• 支持快速的插入和刪除，基本上所有的操作都可以在O(log n)的時間復雜度內完成。
• 分級結構可以支持多級索引查找，充分利用磁盤I/O緩存和預取來提高查詢效率。

索引

MySQL 中最常見的索引包括：

• 聚簇索引（主鍵索引）：一個表只能有一個聚簇索引，對應表的數據存儲方式，即數據按照聚簇索引來排序和存儲，葉節點存儲了完整的數據行。在使用聚簇索引進行查找時，只需查找一次聚簇索引就能找到需要的數據行。
• 非聚簇索引（輔助索引）：一個表可以有多個非聚簇索引，節點存儲了完整的索引和指向數據行信息（指針或主鍵）。查詢時需要查找兩次索引，第一次查詢索引信息，第二次查找數據行。

如下圖所示：

這種先查輔助索引再查主鍵索引的行為，我們稱之為“回表”。

看一個回表的例子：

table: id, category, publisher, status, title
index: idx_categity(category,status)

查詢語句：select * from tb_news where category = 2 and publisher = 14

執行邏輯如圖所示：

• 索引中存在 category 列，category = 2 的過濾在引擎層完成，返回數據的主鍵。
• 引擎完成 category = 2 過濾后，需要 publisher 和全部數據，所以進行回表操作。
• 從主鍵索引表中獲取全部數據，在內存中執行 publisher = 14 的過濾。
• 將滿足條件的數據放入到 Result 中進行返回。

一般情況下，回表的性能損失還是可接受的，可以在發現問題后進行處理。可將更多精力放在提升研發效率上。

（2）高性能查詢

基于 B+Tree 數據結構的特點，在以下場景可以高效使用索引：

• 全值匹配：與索引中的所有列進行匹配；
• 匹配最左前綴：并非與索引中的所有列進行匹配，從索引左側進行匹配。
• 匹配列前綴：匹配某一列的開頭部分（like ‘aaa%’）。
• 匹配范圍值: 大于、等于、小于等。
• 精確匹配列然后范圍匹配：先精確匹配，然后進行范圍匹配。
• 只訪問索引查詢：如果索引中存在查詢所需所有數據，就沒有必要追溯原數據。
• 支持查詢中的order by、group by：order by、group by 與 where 條件組合，如果符合最左匹配，及可提升性能。

以下幾種情況無法使用索引：

• 不是從最左列開始查詢，無法使用索引。
• 不能跳過索引中的列對后面的列進行查詢。
• 如果索引使用范圍查詢，則后面所有列無法使用索引進行優化。

（3）查詢規范

在了解 MySQL B+Tree 的內部實現之后，可以推導出一套規范，來對查詢性能進行保障。

原則

• 僅使用 MySQL 的單表查詢，避免多表 Join 引入的性能問題（多表查詢解決方案見：內存Join）。
• 每個查詢，必須有對應的索引對性能進行保障，也就是所有的查詢必須走索引。
• 謹慎處理入參和返回值。

1. 對入參進行嚴格驗證，避免因為參數丟失或參數過多造成的性能問題。
2. 對返回值進行驗證，避免一次性返回過多數據操作性能問題。

規范

對于一個查詢請求，需要具備：

• 統一使用 Query Object 模式，對入參進行封裝，以便接口的升級和擴展。
• 每一組查詢，可以存在： get（單條返回值）、list（多條返回值）、count（統計計數）、page（分頁）開頭的多個方法，操作后面緊跟 By + 維度。
• 維度結構應該與表的索引結構保持一致，以保障所有的查詢，都能應用索引。
• 索引維度體現在方法簽名中，并且保障滿足最左匹配原則。
• 多維索引，可以基于最左匹配原則生成多組方法；索引列(A，B)，可以生成 A、AandB 兩組方法。

假如在order表中存在一個索引（user_id, status)，那么可以存在以下查詢：

// 可以支持多組高效查詢
// User維度查詢對象
@Data
public class QueryOrderByUser {
    // user id 不能為 null，不然無法使用索引
    @NotNull
    private Long userId;
    private Integer status;
    private Pageable pageable;
}
// User 和 Status 維度查詢
@Data
public class QueryOrderByUserAndStatus {
    // user id 不能為 null，不然無法使用索引
    @NotNull
    private Long userId;
    // status 不能為 null，不然無法使用索引
    @NotNull
    private Integer status;
    private Pageable pageable;
}
// 查詢服務如下
public interface OrderService {
    // User 維度查詢
    List<Order> listByUser(QueryOrderByUser query);
    Long countByUser(QueryOrderByUser query);
    Page<Order> pageByUser(QueryOrderByUser query);
    // User 和 Status 維度查詢
    List<Order> listByUserAndStatus(QueryOrderByUserAndStatus query);
    Long countByUserAndStatus(QueryOrderByUserAndStatus query);
    Page<Order> pageByUserAndStatus(QueryOrderByUserAndStatus query);
}

這樣便可以在性能和擴展性間找到一個良好的平衡點。

• 性能。由 MySQL 的索引進行保障，可能不是最優解（存在回表）但絕對不是最差情況。
• 擴展性。默認查詢維度（get、list、count、page）基本能滿足日常業務開發；查詢條件也可基于 Query Object 進行擴展；

3、框架與標準化

我們需要一個框架，在滿足原則和規范前提下，靈活的定制簡單數據查詢，但又不能過于靈活，需要對使用方式進行嚴格限制。

靈活定制，快速開發，提升效率，降低bug；對使用進行限制，是為了將掌控權控制在開發，不會因為使用不當造成線上問題。因此，對框架有如下要求：

• 支持靈活的查詢定義，無需手寫 SQL。
• 支持常見的查詢，包括過濾、排序、分頁等。
• 多 ORM 支持，提供對 MyBatis 和 Jpa 框架支持。

框架整體流程如下：

該模式下，開發查詢功能只需：

• 根據業務需求定義 QueryObject，主要包括過濾、排序、分頁等。
• 使用 QueryObject 調用 QueryRepository 相關接口完成查詢，常見功能包括：單條查詢、列表查詢、計數查詢、分頁查詢等。

只需在QueryObject上進行定義，無需編寫 SQL，由框架對 QueryObject 進行解析，完成動態查詢。

核心功能全部在 QueryRepository 中，其核心流程如下：

流程如下：

• 驗證參數：基于 Spring Validate 框架完成基本的參數校驗。
• 解析QueryObject：從 QueryObject 中提取信息，轉為為 ORM 的查詢對象。
• 設置最大返回值：【可配】設置最大返回值，避免結果太多造成性能低下。
• 執行查詢：調用 ORM 框架的查詢接口執行查詢命令。
• 處理查詢結果：【可配】對查詢結果進行處理打印日志 or 異常中斷。

為了支持多個 ORM 框架，整體結構設計如下：

核心模塊包括：

• API：提供統一的接口和配置能力，對使用方式進行規范；。
• MyBatis 實現：基于 MyBatis 實現 API 中定義的全部功能，完成與 MyBatis 框架的集成。
• JPA 實現：基于 JPA 實現 API 中定義的全部功能，完成與 JPA 框架的集成。

（1）統一 API

提供統一的接口和配置能力，對使用方式進行規范。其中包括兩大部分：

1. 注解：使用注解在 QueryObject 的字段上添加配置信息，使其具備過濾能力；
2. QueryRepository接口：提供一組標準的 API，實現常見的 get、list、count 和 page 查詢；

注解

注解配置于 QueryObject 之上，以聲明化的方式，對過濾功能進行描述。

常見注解如下：

注解	含義
FieldEqualTo	等于
FieldGreaterThan	大于
FieldGreaterThanOrEqualTo	大于等于
FieldIn	in 操作
FieldIsNull	是否為 null
FieldLessThan	小于
FieldLessThanOrEqualTo	小于等于
FieldNotEqualTo	不等于
FieldNotIn	not in
EmbeddedFilter	嵌入查詢對象

針對之前的 User 查詢實例，對應的 查詢對象定義如下：

@Data
public class QueryUserByStatus {
    // 狀態相等
    @FieldEqualTo("status")
    @NotNull
    private Integer status;
    // 手機號相等
    @FieldEqualTo("mobile")
    private String mobile;
    // 生日比該值大
    @FieldGreaterThan("birthAt")
    private Date birthAfter;
    // 生日比該值小
    @FieldLessThan("birthAt")
    private Date birthBefore;
    // 自動具備分頁能力
    private Pageable pageable;
}

接口

有了 QueryObject 之后，需要一組查詢 API 以滿足各個場景需求，標準的 API 接口定義如下：

public interface QueryObjectRepository<E> {
    // 檢查查詢對象的有效性
    void checkForQueryObject(Class cls);
    // 單條查詢
    <Q> E get(Q query);
    // 分頁查詢
    default <Q, R> R get(Q query, Function<E, R> converter) {
        E entity = this.get(query);
        return entity  null ? null : converter.apply(entity);
    }
    // 統計查詢
    <Q> Long countOf(Q query);
    // 列表查詢
    default <Q, R> List<R> listOf(Q query, Function<E, R> converter) {
        List<E> entities = this.listOf(query);
        return CollectionUtils.isEmpty(entities) ? Collections.emptyList() : (List)entities.stream().filter(Objects::nonNull).map(converter).filter(Objects::nonNull).collect(Collectors.toList());
    }
    // 列表查詢
    <Q> List<E> listOf(Q query);
    // 分頁查詢
    default <Q, R> Page<R> pageOf(Q query, Function<E, R> converter) {
        Page<E> entityPage = this.pageOf(query);
        return entityPage  null ? null : entityPage.convert(converter);
    }
    // 分頁查詢
    <Q> Page<E> pageOf(Q query);
}

集成示例

有了 QueryObject 和 API 之后，便可以輕松完成各種查詢：

public class SingleQueryService {
    @Autowired
    private QueryObjectRepository<JpaUser> repository;
    public List<JpaUser> listByStatus(QueryUserByStatus query){
        return repository.listOf(query);
    }
    public Long countByStatus(QueryUserByStatus query){
        return this.repository.countOf(query);
    }
    public Page<JpaUser> pageByStatus(QueryUserByStatus query){
        return this.repository.pageOf(query);
    }
}

萬事具備，只欠最后的 QueryObjectRepository 實現，針對不同的 ORM 提供不同的實現。

（2）MyBatis 支持

基于 MyBatis Generator 的 Example 機制實現，需要配置相關的 Generator 以生成 EntityExample 對象。

直接繼承BaseReflectBasedExampleSingleQueryRepository，注入 Mapper 實現，指定好 Example 類即可，具體如下：

@Service
public class MyBatisBasedQueryRepository extends BaseReflectBasedExampleSingleQueryRepository {
    // 注入 MyBatis 的 Mapper 類
    public MyBatisBasedQueryRepository(MyBatisUserMapper mapper) {
        // 指定查詢所需的 Example 類
        super(mapper, MyBatisUserExample.class);
    }
}

整體架構如下：

核心流程如下：

• ExampleConverter 將輸入的 QueryObject 轉換為 XXXExample 實例。
• 使用 XXXExample 實例 調用 XXXMapper 的 selectByExample 方法獲取返回值。
• 返回值通過 ResultConverter 將 Entity 轉換為最終結果。

其中，從 QueryObject 到 Example 實例的轉換為框架的核心，主要包括如下幾部分：

• Pageable。從 QueryObject 中讀取 Pageable 屬性，并設置 Example 對象的 offset 和 rows 屬性。
• Sort。從 QueryObject 中讀取 Sort 屬性，并設置 Example 對象的 orderByClause 屬性。
• 過濾注解。遍歷 QueryObject 中屬性，根據注解查找到注解處理器，由注解處理器為 Example 添加 Criteria，以進行數據過濾。

（3）Jpa 支持

基于 JPA 框架的 JpaSpecificationExecutor 實現，EntityRepository 需繼承 JpaSpecificationExecutor 接口。

直接繼承BaseSpecificationQueryObjectRepository，注入 JpaSpecificationExecutor 和 實體對象即可，具體如下：

public class JpaBasedQueryRepository extends BaseSpecificationQueryObjectRepository {
    // 注入 JpaUserRepository 和 specificationConverterFactory（框架自動生成）
    public JpaBasedQueryRepository(JpaUserRepository userRepository,
                                   SpecificationConverterFactory
                                           specificationConverterFactory) {
        // 指定實體對象 JpaUser
        super(userRepository, JpaUser.class, specificationConverterFactory);
    }
}

整體架構如下：

核心流程如下：

• SpecificationConverter 將輸入的 QueryObject 轉換為 Specification、Pageable、Sort等實例。
• 使用 SpecificationExecutor 實例的查詢方法獲取返回值。
• 返回值通過 ResultConverter 將 Entity 轉換為最終結果。

其中，從 QueryObject 到相關輸入參數的轉換為框架的核心，主要包括如下幾部分：

• Pageable。從 QueryObject 中讀取 Pageable 屬性，并轉化為 Spring data 的 Pageable 實例。
• Sort。從 QueryObject 中讀取 Sort 屬性，并轉換為Spring data 的 Sort 實例。
• 過濾注解。遍歷 QueryObject 中屬性，根據注解查找到注解處理器，由注解處理器將其轉化為 Predicate 實例，最終封裝為 Specification 示例。

4、小結

本文從一個日常開發場景出發，提出兩個關鍵問題：

• 代碼過于繁瑣，容易出錯，同時開發效率低下。
• 對性能設計關注不足，容易遺漏，產生性能問題。

對于性能問題，從 MySQL B+Tree 進行推演，總結出該場景下的最佳使用實踐，并將其提取為規范。

對于代碼繁瑣問題，提出通過在 QueryObject 上增加注解的方式來實現簡單查詢。

兩者相結合，便形成了 Single Query 框架：

• 提供一套注解，應用于 QueryObject 之上，以聲明化的方式完成查詢定義。
• 提供一套API，以 QueryObject 作為參數，提供 單條、批量、統計、分頁等查詢。
• 提供MyBatis和Jpa兩套實現，快速實現 QueryObjectRepository，以提升開發速度。