B 树深度解析：从底层原理到数据库实战，彻底掌握索引核心

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jam.demo</groupId>
    <artifactId>b-tree-demo</artifactId>
    <version>1.0.0</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.boot.version>3.2.0</spring.boot.version>
        <lombok.version>1.18.30</lombok.version>
        <fastjson2.version>2.0.48</fastjson2.version>
        <guava.version>32.1.3-jre</guava.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>${fastjson2.version}</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.9.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring.boot.version}</version>
            </plugin>
        </plugins>
    </build>
</project>

package com.jam.demo.btree;

import lombok.Getter;
import lombok.Setter;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * B树节点类
 * @author ken
 * @param <K> 关键字类型（需实现Comparable接口）
 */
@Getter
@Setter
public class BTreeNode<K extends Comparable<K>> {
    /**
     * 最小度数（t=⌈m/2⌉，m为阶数）
     */
    private final int t;
    /**
     * 关键字列表
     */
    private List<K> keys;
    /**
     * 孩子节点列表
     */
    private List<BTreeNode<K>> children;
    /**
     * 是否为叶子节点
     */
    private boolean leaf;

    /**
     * 构造函数
     * @param t 最小度数
     * @param leaf 是否为叶子节点
     */
    public BTreeNode(int t, boolean leaf) {
        this.t = t;
        this.leaf = leaf;
        this.keys = new ArrayList<>(2 * t - 1);
        this.children = new ArrayList<>(2 * t);
    }

    /**
     * 在节点中查找关键字的位置
     * @param key 目标关键字
     * @return 第一个大于等于key的索引，或keys.size()
     */
    public int findKey(K key) {
        int idx = 0;
        while (idx < keys.size() && keys.get(idx).compareTo(key) < 0) {
            idx++;
        }
        return idx;
    }

    /**
     * 插入关键字到非满节点
     * @param key 要插入的关键字
     */
    public void insertNonFull(K key) {
        int i = keys.size() - 1;

        if (leaf) {
            // 叶子节点直接插入，保持有序
            keys.add(null);
            while (i >= 0 && keys.get(i).compareTo(key) > 0) {
                keys.set(i + 1, keys.get(i));
                i--;
            }
            keys.set(i + 1, key);
        } else {
            // 非叶子节点，找到子节点位置
            while (i >= 0 && keys.get(i).compareTo(key) > 0) {
                i--;
            }
            i++;

            // 若子节点满，先分裂
            if (children.get(i).getKeys().size() == 2 * t - 1) {
                splitChild(i, children.get(i));
                if (keys.get(i).compareTo(key) < 0) {
                    i++;
                }
            }
            // 递归插入到子节点
            children.get(i).insertNonFull(key);
        }
    }

    /**
     * 分裂满的子节点
     * @param i 子节点在children中的索引
     * @param y 要分裂的子节点
     */
    public void splitChild(int i, BTreeNode<K> y) {
        // 创建新节点z，存储y的后t-1个关键字
        BTreeNode<K> z = new BTreeNode<>(y.t, y.leaf);
        for (int j = 0; j < t - 1; j++) {
            z.getKeys().add(y.getKeys().get(j + t));
        }

        // 若y非叶子节点，复制后t个子节点到z
        if (!y.leaf) {
            for (int j = 0; j < t; j++) {
                z.getChildren().add(y.getChildren().get(j + t));
            }
        }

        // 移除y中已转移到z的关键字和子节点
        y.getKeys().subList(t - 1, y.getKeys().size()).clear();
        if (!y.leaf) {
            y.getChildren().subList(t, y.getChildren().size()).clear();
        }

        // 将z插入到当前节点的children列表
        children.add(i + 1, z);
        // 将y的中间关键字上移到当前节点
        keys.add(i, y.getKeys().get(t - 1));
    }

    /**
     * 遍历节点及其子节点（中序遍历）
     */
    public void traverse() {
        int i;
        for (i = 0; i < keys.size(); i++) {
            if (!leaf) {
                children.get(i).traverse();
            }
            System.out.print(keys.get(i) + " ");
        }

        // 遍历最后一个子节点
        if (!leaf) {
            children.get(i).traverse();
        }
    }

    /**
     * 查找关键字
     * @param key 目标关键字
     * @return 包含关键字的节点及索引，无则返回null
     */
    public BTreeSearchResult<K> find(K key) {
        int i = findKey(key);
        if (i < keys.size() && keys.get(i).compareTo(key) == 0) {
            return new BTreeSearchResult<>(this, i);
        }
        if (leaf) {
            return null;
        }
        return children.get(i).find(key);
    }

    /**
     * 删除关键字
     * @param key 要删除的关键字
     */
    public void delete(K key) {
        int i = findKey(key);

        if (i < keys.size() && keys.get(i).compareTo(key) == 0) {
            // 情况1：关键字在当前节点
            if (leaf) {
                // 子情况1a：叶子节点，直接删除
                keys.remove(i);
            } else {
                // 子情况1b：非叶子节点，需替换为后继关键字
                BTreeNode<K> successorNode = getSuccessor(i);
                keys.set(i, successorNode.getKeys().get(0));
                successorNode.delete(successorNode.getKeys().get(0));
            }
        } else {
            // 情况2：关键字不在当前节点
            if (leaf) {
                // 关键字不存在
                return;
            }

            boolean isLastChild = (i == keys.size());
            // 若子节点关键字数不足t-1，先补充
            if (children.get(i).getKeys().size() < t - 1) {
                fill(i);
            }

            // 补充后可能合并了子节点，需重新确定索引
            if (isLastChild && i > keys.size()) {
                children.get(i - 1).delete(key);
            } else {
                children.get(i).delete(key);
            }
        }
    }

    /**
     * 获取后继关键字节点（右子树最小节点）
     * @param idx 关键字在当前节点的索引
     * @return 后继关键字所在节点
     */
    private BTreeNode<K> getSuccessor(int idx) {
        BTreeNode<K> current = children.get(idx + 1);
        while (!current.leaf) {
            current = current.children.get(0);
        }
        return current;
    }

    /**
     * 补充子节点的关键字（借或合并）
     * @param idx 子节点在children中的索引
     */
    private void fill(int idx) {
        if (idx > 0 && children.get(idx - 1).getKeys().size() >= t) {
            // 左兄弟有多余关键字，借左兄弟的
            borrowFromLeft(idx);
        } else if (idx < keys.size() && children.get(idx + 1).getKeys().size() >= t) {
            // 右兄弟有多余关键字，借右兄弟的
            borrowFromRight(idx);
        } else {
            // 兄弟节点均无多余关键字，合并
            if (idx < keys.size()) {
                merge(idx);
            } else {
                merge(idx - 1);
            }
        }
    }

    /**
     * 从左兄弟借关键字
     * @param idx 子节点在children中的索引
     */
    private void borrowFromLeft(int idx) {
        BTreeNode<K> child = children.get(idx);
        BTreeNode<K> leftSibling = children.get(idx - 1);

        // 父节点中间关键字下移到子节点
        child.getKeys().add(0, keys.get(idx - 1));
        if (!child.leaf) {
            // 左兄弟最后一个子节点移到子节点
            child.getChildren().add(0, leftSibling.getChildren().remove(leftSibling.getChildren().size() - 1));
        }

        // 左兄弟最后一个关键字上移到父节点
        keys.set(idx - 1, leftSibling.getKeys().remove(leftSibling.getKeys().size() - 1));
    }

    /**
     * 从右兄弟借关键字
     * @param idx 子节点在children中的索引
     */
    private void borrowFromRight(int idx) {
        BTreeNode<K> child = children.get(idx);
        BTreeNode<K> rightSibling = children.get(idx + 1);

        // 父节点中间关键字下移到子节点
        child.getKeys().add(keys.get(idx));
        if (!child.leaf) {
            // 右兄弟第一个子节点移到子节点
            child.getChildren().add(rightSibling.getChildren().remove(0));
        }

        // 右兄弟第一个关键字上移到父节点
        keys.set(idx, rightSibling.getKeys().remove(0));
    }

    /**
     * 合并子节点与右兄弟节点
     * @param idx 子节点在children中的索引
     */
    private void merge(int idx) {
        BTreeNode<K> child = children.get(idx);
        BTreeNode<K> rightSibling = children.get(idx + 1);

        // 父节点中间关键字下移到子节点
        child.getKeys().add(keys.get(idx));
        // 合并右兄弟的关键字
        child.getKeys().addAll(rightSibling.getKeys());
        // 合并右兄弟的子节点（若有）
        if (!child.leaf) {
            child.getChildren().addAll(rightSibling.getChildren());
        }

        // 移除父节点的中间关键字和右兄弟节点
        keys.remove(idx);
        children.remove(idx + 1);
    }

    /**
     * 查找结果封装类
     * @param <K> 关键字类型
     */
    @Getter
    public static class BTreeSearchResult<K extends Comparable<K>> {
        private final BTreeNode<K> node;
        private final int index;

        public BTreeSearchResult(BTreeNode<K> node, int index) {
            this.node = node;
            this.index = index;
        }
    }
}

package com.jam.demo.btree;

import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ObjectUtils;

/**
 * m阶B树实现类（基于最小度数t，m=2t）
 * @author ken
 * @param <K> 关键字类型（需实现Comparable接口）
 */
@Slf4j
public class BTree<K extends Comparable<K>> {
    /**
     * 最小度数（t≥2）
     */
    private final int t;
    /**
     * 根节点
     */
    private BTreeNode<K> root;

    /**
     * 构造函数（指定阶数m）
     * @param m 阶数（m=2t，需为≥2的整数）
     * @throws IllegalArgumentException 阶数不合法时抛出
     */
    public BTree(int m) {
        if (m < 2) {
            throw new IllegalArgumentException("B树阶数m必须≥2");
        }
        this.t = (m + 1) / 2; // t=⌈m/2⌉
        this.root = new BTreeNode<>(t, true);
        log.info("初始化{}阶B树（最小度数t={}）", m, t);
    }

    /**
     * 插入关键字
     * @param key 要插入的关键字（非null）
     * @throws IllegalArgumentException 关键字为null时抛出
     */
    public void insert(K key) {
        if (ObjectUtils.isEmpty(key)) {
            throw new IllegalArgumentException("关键字不能为空");
        }

        BTreeNode<K> rootNode = this.root;
        // 若根节点满，分裂根节点（树高+1）
        if (rootNode.getKeys().size() == 2 * t - 1) {
            BTreeNode<K> newRoot = new BTreeNode<>(t, false);
            this.root = newRoot;
            newRoot.getChildren().add(rootNode);
            newRoot.splitChild(0, rootNode);
            newRoot.insertNonFull(key);
            log.info("根节点分裂，树高+1，插入关键字：{}", key);
        } else {
            rootNode.insertNonFull(key);
            log.info("插入关键字：{}", key);
        }
    }

    /**
     * 删除关键字
     * @param key 要删除的关键字（非null）
     * @throws IllegalArgumentException 关键字为null时抛出
     */
    public void delete(K key) {
        if (ObjectUtils.isEmpty(key)) {
            throw new IllegalArgumentException("关键字不能为空");
        }

        root.delete(key);
        // 若根节点无关键字且非叶子节点，更新根节点
        if (root.getKeys().isEmpty() && !root.isLeaf()) {
            this.root = root.getChildren().get(0);
            log.info("根节点无关键字，更新根节点，删除关键字：{}", key);
        } else {
            log.info("删除关键字：{}", key);
        }
    }

    /**
     * 查找关键字
     * @param key 目标关键字（非null）
     * @return 查找结果（包含节点和索引），无则返回null
     * @throws IllegalArgumentException 关键字为null时抛出
     */
    public BTreeNode.BTreeSearchResult<K> search(K key) {
        if (ObjectUtils.isEmpty(key)) {
            throw new IllegalArgumentException("关键字不能为空");
        }

        BTreeNode.BTreeSearchResult<K> result = root.find(key);
        if (ObjectUtils.isEmpty(result)) {
            log.info("未找到关键字：{}", key);
            return null;
        }
        log.info("找到关键字：{}，所在节点关键字：{}，索引：{}",
                key, result.getNode().getKeys(), result.getIndex());
        return result;
    }

    /**
     * 遍历B树（中序遍历，升序输出）
     */
    public void traverse() {
        log.info("B树中序遍历结果：");
        if (ObjectUtils.isNotEmpty(root)) {
            root.traverse();
            System.out.println();
        } else {
            log.info("B树为空");
        }
    }

    /**
     * 获取B树高度
     * @return 树高（根节点为1层）
     */
    public int getHeight() {
        if (root.getKeys().isEmpty() && root.isLeaf()) {
            return 0;
        }
        int height = 1;
        BTreeNode<K> current = root;
        while (!current.isLeaf()) {
            current = current.getChildren().get(0);
            height++;
        }
        log.info("B树高度：{}", height);
        return height;
    }
}

package com.jam.demo.btree;

import com.google.common.collect.Lists;
import org.junit.jupiter.api.Test;
import org.springframework.util.ObjectUtils;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

/**
 * B树功能测试类（JDK17+JUnit5）
 * @author ken
 */
public class BTreeTest {
    /**
     * 测试3阶B树（t=2）的插入、查找、删除功能
     */
    @Test
    public void test3OrderBTree() {
        // 初始化3阶B树（m=3，t=2）
        BTree<Integer> bTree = new BTree<>(3);
        List<Integer> insertKeys = Lists.newArrayList(10, 20, 5, 6, 12, 30, 7, 17);

        // 测试插入
        insertKeys.forEach(bTree::insert);
        assertEquals(2, bTree.getHeight()); // 插入8个关键字后，3阶B树高度应为2

        // 测试遍历（中序遍历结果应为升序）
        System.out.print("3阶B树遍历：");
        bTree.traverse(); // 预期输出：5 6 7 10 12 17 20 30

        // 测试查找存在的关键字
        BTreeNode.BTreeSearchResult<Integer> result1 = bTree.search(17);
        assertNotNull(result1);
        assertEquals(1, result1.getIndex());
        assertEquals(Lists.newArrayList(12, 17), result1.getNode().getKeys());

        // 测试查找不存在的关键字
        BTreeNode.BTreeSearchResult<Integer> result2 = bTree.search(25);
        assertNull(result2);

        // 测试删除叶子节点关键字（存在且节点关键字数>t-1）
        bTree.delete(5);
        assertNull(bTree.search(5));
        System.out.print("删除5后遍历：");
        bTree.traverse(); // 预期输出：6 7 10 12 17 20 30

        // 测试删除非叶子节点关键字
        bTree.delete(10);
        assertNull(bTree.search(10));
        System.out.print("删除10后遍历：");
        bTree.traverse(); // 预期输出：6 7 12 17 20 30

        // 测试删除后树高是否正确
        assertEquals(2, bTree.getHeight());
    }

    /**
     * 测试4阶B树（t=2）的分裂与合并
     */
    @Test
    public void test4OrderBTree() {
        // 初始化4阶B树（m=4，t=2）
        BTree<String> bTree = new BTree<>(4);
        List<String> insertKeys = Lists.newArrayList("A", "B", "C", "D", "E", "F", "G", "H", "I");

        // 插入9个关键字，测试根节点分裂
        insertKeys.forEach(bTree::insert);
        assertEquals(2, bTree.getHeight());

        // 查找关键字F
        BTreeNode.BTreeSearchResult<String> result = bTree.search("F");
        assertNotNull(result);
        assertTrue(result.getNode().getKeys().contains("F"));

        // 删除关键字C，测试借关键字
        bTree.delete("C");
        assertNull(bTree.search("C"));

        // 删除关键字D，测试合并节点
        bTree.delete("D");
        assertNull(bTree.search("D"));

        System.out.print("4阶B树删除后遍历：");
        bTree.traverse(); // 预期输出：A B E F G H I
    }

    /**
     * 测试异常场景（空关键字、非法阶数）
     */
    @Test
    public void testExceptionScenarios() {
        // 测试阶数<2的异常
        assertThrows(IllegalArgumentException.class, () -> new BTree<>(1));

        // 测试插入空关键字
        BTree<Integer> bTree = new BTree<>(3);
        assertThrows(IllegalArgumentException.class, () -> bTree.insert(null));

        // 测试查找空关键字
        assertThrows(IllegalArgumentException.class, () -> bTree.search(null));

        // 测试删除空关键字
        assertThrows(IllegalArgumentException.class, () -> bTree.delete(null));
    }
}

-- 创建订单表（100万条测试数据）
CREATE TABLE `order_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_no` varchar(64) NOT NULL COMMENT '订单号（唯一）',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `status` tinyint NOT NULL COMMENT '订单状态：0-待支付，1-已支付，2-已取消',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) COMMENT '聚簇索引（B+树）',
  UNIQUE KEY `idx_order_no` (`order_no`) COMMENT '订单号唯一索引（B+树）',
  KEY `idx_user_create_time` (`user_id`,`create_time`) COMMENT '用户ID+创建时间联合索引（B+树）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电商订单表';

-- 插入测试数据（存储过程）
DELIMITER //
CREATE PROCEDURE insert_order_data(IN num INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE user_id BIGINT;
  DECLARE order_no VARCHAR(64);
  DECLARE amount DECIMAL(10,2);
  DECLARE status TINYINT;
  DECLARE create_time DATETIME;
  
  WHILE i <= num DO
    SET user_id = FLOOR(1 + RAND() * 10000); -- 模拟1万用户
    SET order_no = CONCAT('ORDER_', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '_', LPAD(i, 6, '0'));
    SET amount = ROUND(RAND() * 10000, 2); -- 订单金额0-10000元
    SET status = FLOOR(RAND() * 3); -- 0-2随机状态
    SET create_time = DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY); -- 近一年随机时间
    
    INSERT INTO `order_info` (`order_no`, `user_id`, `amount`, `status`, `create_time`, `update_time`)
    VALUES (order_no, user_id, amount, status, create_time, create_time);
    
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- 调用存储过程插入100万条数据
CALL insert_order_data(1000000);

-- 查看索引信息
SHOW INDEX FROM `order_info`;

<!-- MyBatisPlus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.5</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
    <scope>runtime</scope>
</dependency>
<!-- Swagger3 -->
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-boot-starter</artifactId>
    <version>3.0.0</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.20</version>
</dependency>

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: root
    password: root123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.jam.demo.entity
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto
      logic-delete-field: isDeleted
      logic-delete-value: 1
      logic-not-delete-value: 0
springdoc:
  swagger-ui:
    path: /swagger-ui.html
    operationsSorter: method
  api-docs:
    path: /v3/api-docs
  packages-to-scan: com.jam.demo.controller

package com.jam.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * 订单实体类
 * @author ken
 */
@Data
@TableName("order_info")
@ApiModel(value = "OrderInfo对象", description = "电商订单表")
public class OrderInfo {
    @ApiModelProperty(value = "主键ID")
    @TableId(type = IdType.AUTO)
    private Long id;

    @ApiModelProperty(value = "订单号（唯一）", required = true)
    private String orderNo;

    @ApiModelProperty(value = "用户ID", required = true)
    private Long userId;

    @ApiModelProperty(value = "订单金额", required = true)
    private BigDecimal amount;

    @ApiModelProperty(value = "订单状态：0-待支付，1-已支付，2-已取消", required = true)
    private Integer status;

    @ApiModelProperty(value = "创建时间", required = true)
    private LocalDateTime createTime;

    @ApiModelProperty(value = "更新时间", required = true)
    private LocalDateTime updateTime;
}

package com.jam.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jam.demo.entity.OrderInfo;
import io.swagger.annotations.ApiOperation;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

/**
 * 订单Mapper
 * @author ken
 */
@Repository
public interface OrderInfoMapper extends BaseMapper<OrderInfo> {
    /**
     * 按订单号查询订单（命中唯一索引idx_order_no）
     * @param orderNo 订单号
     * @return 订单详情
     */
    @ApiOperation("按订单号查询订单")
    OrderInfo selectByOrderNo(@Param("orderNo") String orderNo);

    /**
     * 按用户ID+创建时间范围查询订单（命中联合索引idx_user_create_time）
     * @param page 分页参数
     * @param userId 用户ID
     * @param startCreateTime 开始时间
     * @param endCreateTime 结束时间
     * @return 分页订单列表
     */
    @ApiOperation("按用户ID+创建时间范围查询订单")
    IPage<OrderInfo> selectByUserIdAndCreateTimeRange(
            Page<OrderInfo> page,
            @Param("userId") Long userId,
            @Param("startCreateTime") LocalDateTime startCreateTime,
            @Param("endCreateTime") LocalDateTime endCreateTime
    );
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jam.demo.mapper.OrderInfoMapper">
    <select id="selectByOrderNo" resultType="com.jam.demo.entity.OrderInfo">
        SELECT id, order_no, user_id, amount, status, create_time, update_time
        FROM order_info
        WHERE order_no = #{orderNo}
    </select>

    <select id="selectByUserIdAndCreateTimeRange" resultType="com.jam.demo.entity.OrderInfo">
        SELECT id, order_no, user_id, amount, status, create_time, update_time
        FROM order_info
        WHERE user_id = #{userId}
          AND create_time BETWEEN #{startCreateTime} AND #{endCreateTime}
        ORDER BY create_time DESC
    </select>
</mapper>

package com.jam.demo.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.OrderInfo;
import io.swagger.annotations.ApiOperation;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;

/**
 * 订单服务接口
 * @author ken
 */
public interface OrderInfoService extends IService<OrderInfo> {
    /**
     * 按订单号查询订单
     * @param orderNo 订单号
     * @return 订单详情
     */
    @ApiOperation("按订单号查询订单")
    OrderInfo getByOrderNo(String orderNo);

    /**
     * 按用户ID+创建时间范围分页查询订单
     * @param userId 用户ID
     * @param startCreateTime 开始时间
     * @param endCreateTime 结束时间
     * @param pageNum 页码
     * @param pageSize 每页条数
     * @return 分页订单列表
     */
    @ApiOperation("按用户ID+创建时间范围分页查询订单")
    IPage<OrderInfo> pageByUserIdAndCreateTimeRange(
            Long userId,
            LocalDateTime startCreateTime,
            LocalDateTime endCreateTime,
            Integer pageNum,
            Integer pageSize
    );
}

package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jam.demo.entity.OrderInfo;
import com.jam.demo.mapper.OrderInfoMapper;
import com.jam.demo.service.OrderInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;

/**
 * 订单服务实现类
 * @author ken
 */
@Slf4j
@Service
public class OrderInfoServiceImpl extends ServiceImpl<OrderInfoMapper, OrderInfo> implements OrderInfoService {

    @Override
    public OrderInfo getByOrderNo(String orderNo) {
        // 字符串判空
        StringUtils.hasText(orderNo, "订单号不能为空");
        
        long startTime = System.currentTimeMillis();
        OrderInfo orderInfo = baseMapper.selectByOrderNo(orderNo);
        long costTime = System.currentTimeMillis() - startTime;
        
        log.info("按订单号查询订单，orderNo={}，耗时={}ms，结果：{}",
                orderNo, costTime, ObjectUtils.isEmpty(orderInfo) ? "无" : orderInfo.getOrderNo());
        return orderInfo;
    }

    @Override
    public IPage<OrderInfo> pageByUserIdAndCreateTimeRange(
            Long userId,
            LocalDateTime startCreateTime,
            LocalDateTime endCreateTime,
            Integer pageNum,
            Integer pageSize
    ) {
        // 参数校验
        if (ObjectUtils.isEmpty(userId)) {
            throw new IllegalArgumentException("用户ID不能为空");
        }
        if (ObjectUtils.isEmpty(startCreateTime)) {
            throw new IllegalArgumentException("开始时间不能为空");
        }
        if (ObjectUtils.isEmpty(endCreateTime)) {
            throw new IllegalArgumentException("结束时间不能为空");
        }
        if (ObjectUtils.isEmpty(pageNum) || pageNum < 1) {
            pageNum = 1;
        }
        if (ObjectUtils.isEmpty(pageSize) || pageSize < 1 || pageSize > 100) {
            pageSize = 10;
        }
        if (startCreateTime.isAfter(endCreateTime)) {
            throw new IllegalArgumentException("开始时间不能晚于结束时间");
        }
        
        long startTime = System.currentTimeMillis();
        Page<OrderInfo> page = new Page<>(pageNum, pageSize);
        IPage<OrderInfo> orderPage = baseMapper.selectByUserIdAndCreateTimeRange(
                page, userId, startCreateTime, endCreateTime
        );
        long costTime = System.currentTimeMillis() - startTime;
        
        log.info("按用户ID+时间范围查询订单，userId={}，时间范围=[{}~{}]，分页={}/{}，总条数={}，耗时={}ms",
                userId, startCreateTime, endCreateTime, pageNum, pageSize, orderPage.getTotal(), costTime);
        return orderPage;
    }
}

package com.jam.demo.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.jam.demo.entity.OrderInfo;
import com.jam.demo.service.OrderInfoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;

/**
 * 订单控制器（Swagger3文档）
 * @author ken
 */
@Slf4j
@RestController
@RequestMapping("/order")
@Api(tags = "订单管理接口", description = "基于B树索引优化的订单查询接口")
public class OrderInfoController {

    @Autowired
    private OrderInfoService orderInfoService;

    @GetMapping("/getByOrderNo")
    @ApiOperation(value = "按订单号查询订单", notes = "命中唯一索引idx_order_no，查询效率极高")
    @ApiImplicitParam(name = "orderNo", value = "订单号", required = true, dataType = "String", paramType = "query")
    public OrderInfo getByOrderNo(String orderNo) {
        return orderInfoService.getByOrderNo(orderNo);
    }

    @GetMapping("/pageByUserIdAndCreateTime")
    @ApiOperation(value = "按用户ID+创建时间范围分页查询订单", notes = "命中联合索引idx_user_create_time，支持高效范围查询")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "userId", value = "用户ID", required = true, dataType = "Long", paramType = "query"),
            @ApiImplicitParam(name = "startCreateTime", value = "开始时间（格式：yyyy-MM-dd HH:mm:ss）", required = true, dataType = "String", paramType = "query"),
            @ApiImplicitParam(name = "endCreateTime", value = "结束时间（格式：yyyy-MM-dd HH:mm:ss）", required = true, dataType = "String", paramType = "query"),
            @ApiImplicitParam(name = "pageNum", value = "页码（默认1）", dataType = "Integer", paramType = "query"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数（默认10，最大100）", dataType = "Integer", paramType = "query")
    })
    public IPage<OrderInfo> pageByUserIdAndCreateTime(
            Long userId,
            String startCreateTime,
            String endCreateTime,
            Integer pageNum,
            Integer pageSize
    ) {
        // 字符串转LocalDateTime（实际项目建议用全局转换器）
        LocalDateTime start = LocalDateTime.parse(startCreateTime.replace(" ", "T"));
        LocalDateTime end = LocalDateTime.parse(endCreateTime.replace(" ", "T"));
        return orderInfoService.pageByUserIdAndCreateTimeRange(userId, start, end, pageNum, pageSize);
    }
}

package com.jam.demo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * MyBatisPlus配置类（分页插件）
 * @author ken
 */
@Configuration
public class MyBatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加MySQL分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

-- 按订单号查询（唯一索引）
EXPLAIN SELECT id, order_no, user_id, amount, status, create_time, update_time FROM order_info WHERE order_no = 'ORDER_20240101120000_000001';

-- 按用户ID+时间范围查询（联合索引）
EXPLAIN SELECT id, order_no, user_id, amount, status, create_time, update_time FROM order_info WHERE user_id = 1001 AND create_time BETWEEN '2023-01-01' AND '2024-01-01' ORDER BY create_time DESC;