芝法酱学习笔记（2.1）——sql性能优化1

一、前言

做程序员这行，相信大家都会接触到性能优化这个概念。并且多数程序员，为自己能参与到性能优化的工作而感到自豪。
在普通应用系统的业务开发中，程序的性能瓶颈往往在数据库查询的IO上，所以优化数据库查询则是重中之重的工作。
本章以sql性能优化为引，将逐步展开Java开发各中间件的学习。

二、最基础的优化

实际上多数的性能瓶颈，并不需要搞高大上的分库分表，也不需要做缓存。仅仅修正表的字段类型；合理设置表的索引；优化查询方式，减少不必要连表。就可以大幅提升数据库查询性能。
我们还是以销售单报表查询这个简单的业务背景，做本节内容的讲解。

2.1 一个真实的惨痛教训

小编毕业是做游戏开发的，Java开发属于半路跳船，2020年才开始涉足。所以我接触的Java相关的知识相对比较新。在我实际工作中，遇到了非常多匪夷所思的表设计，即什么字段都喜欢搞成varchar。比如订单的时间，商品的关联ID。这些不科学的设计，无疑是性能的巨大隐患，将会在你想不到的地方埋上大坑。
为什么会有这样的现象呢？很有可能是一些程序员在学校里没认真学习，出门后跟着一些老程序员（80后）写代码，形成了一些恶习。一些团队十分守旧，坚持用早期版本的mysql（5.7以前），并且对技术过于敬畏，不敢升级mysql驱动。这样把各字段定成varchar，就避免了大量变量转化的问题。毕竟在项目在开始阶段，性能问题并不是大家关注的，大家更关注是否能快速实现。
我上家公司的工作经历，就遇到了一个这样的坑。我们的业务是这样的，一个商品对应着多套价格，有一个商品表，有一个商品价格表（1对多）。而商品价格表中，商品的关联id使用了varchar。在商品报表页面，需要把商品表和商品价格表连表查询，展示商品的所有价格。我们的sql大致是这样写的：

SELECT it.id,it.name,ipc.price1, ipc.price2 ... FROM
item it JOIN item_price ipc ON it.id = ipc.item_id and it.enp_code = ipc.enp_code
WHERE it.enp_code = #{In.enp_code}
LIMIT 0,10

我们一家企业，商品数量最多也就小几万（多数不到1万），就这样一个看上去简单到不行的连表查询，每次页面刷新都得等1秒以上。
使用explain发现，连边并没有走索引，可我们明明在item_price建了关于enp_code和item_id的索引。
后来我们发现，因为item_price的表商品id使用了varchar，在sql实际执行的时候，发生了类型转换，相当于对索引列使用了函数，自然就不会走索引。于是数量级从万就变成了万乘万。
用下面这个抽象的的做法，反倒使效率提升了。

SELECT it.id,it.name,ipc.price1, ipc.price2 ... FROM
item it JOIN item_price ipc ON CAST(it.id,CHAR) = ipc.item_id and it.enp_code = ipc.enp_code
WHERE it.enp_code = #{In.enp_code}
LIMIT 0,10

最后在老板万般纠结下，拖了2个月，终于下决心把线上数据库的索引改了。唉~
有些时候，只要按照规范来设计表，写代码，就不会遇到这些问题。实际上不是每一个技术点都需要深究的。

2.2 一些其他遇到的存疑问题

2.2.1 join关联不使用主键

我上家公司由于做了按照企业分库，每个表都有个enp_code的字段作为企业的唯一标识。强制要求拼上 left.enp_code = right.enp_code，并且join表用另一个非主键的字段关联。

2.2.2 时间用char

有时，时间直接用char，而不用datetime

2.2.3 order by id

由于上家公司的id用的是自增id，那么理论上说，id越靠后是不是时间就越晚。于是公司的大聪明在写排序字段时，就用ORDER BY id DESC，看上去是不是很美妙。
而实际explain一下，这样做不走索引了，直接走主键索引，反倒更慢了。

2.2.4 联合索引最左匹配原则

如果明确知道两个或多个查询条件会同时出现，那不如创建联合索引。实际查询时，mysql往往只会选择一个索引。
还是拿销售单查询举例，如果建一个企业id索引，建一个订单时间索引，在查询时并不会两个索引同时使用。

三、实验环境搭建

下面，我们来建一个项目来实现各种不规范建表建索引带来的性能损失

3.1 数据库表展示

本次主要为了验证sql性能问题，对业务做大幅简化

3.1.1 企业表

索引：

• uidx_code: code

3.1.2 业务员表

索引：

• idx_enp_id: enp_id

3.1.3 门店表

索引：

• idx_enp_id: enp_id

3.1.4 商品表

这个表索引估计建抽象点，方便验证
索引：

• idx_rel_id: enp_id;rel_id

3.1.5 销售单头表

索引：

• uidx_bill_no: enp_id;bill_no
• idx_time_key: enp_id;bill_time_key
• idx_time: enp_id;bill_time
• idx_time_str: enp_id;bill_time_str
• idx_sales: enp_id;sales_id;bill_time_key
• idx_customer: enp_id;customer_id;bill_time_key

3.1.6 销售单

索引：

• idx_bill_date_key：enp_id;bill_time_key
• idx_header_id: header_id
• idx_enp_id_bill_no: enp_id;bill_no

3.2 模拟生成数据优化

在执行init前，先临时关掉mysql的binlog，加速执行
位置在/etc/mysql/my.cnf

[mysqld]
skip-log-bin

注意，改了配置要重启mysql
在application.yml配置里，关掉sql的log，并在连接中加入如下参数

url: jdbc:mysql://192.168.0.64:3306/study2024-class008-busy001?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&useServerPrepStmts=false

3.3 模拟代码

本处，我们忽略掉一些工具类和生成类的代码，只展示核心内容。具体代码看最后的代码展示。

@Slf4j
@RequiredArgsConstructor
@Component
public class ConsignServiceImpl implements IConsignService {

    final ConsignMapper mConsignMapper;

    final IGenEnterpriseDbService mEnterpriseDbService;
    final IGenSalesManDbService mSalesManDbService;
    final IGenCustomerDbService mCustomerDbService;
    final IGenItemDbService mItemDbService;
    final IGenConsignDbService mConsignDbService;
    final IGenConsignHeaderDbService mConsignHeaderDbService;
    final BillNoUtil mBillNoUtil;
    final PlatformTransactionManager mTransactionManager;


    @Override
    public void initTestData() {
        final int ENP_CNT = 10;
        final int SALES_CNT_MIN = 5;
        final int SALES_CNT_MAX = 20;
        final int CUSTOMER_CNT_MIN = 20;
        final int CUSTOMER_CNT_MAX = 50;
        final int ITEM_CNT_MIN = 2000;
        final int ITEM_CNT_MAX = 10000;

        final long ITEM_COST_MIN = 500;
        final long ITEM_COST_MAX = 19800;

        final int ITEM_PROFIT_MIN = 10;
        final int ITEM_PROFIT_MAX = 50;

        // 每个门店售卖某个商品的概率
        final int CUSTOMER_ITEM_SELECT_PERCENT = 30;
        // 每个售货员当天来特定门店的概率
        final int CUSTOMER_SALES_SELECT_PERCENT = 15;
        // 售货员来该门店，每样商品送货的概率，万分制
        // 这个概率远小于真实情况，只不过我为了导入快点。正常概率，我们认为一周送2次货，拍2800更合适
        final int ITEM_SALES_SELECT_PERCENT = 250;

        final int ITEM_SALES_CNT_MIN = 10;
        final int ITEM_SALES_CNT_MAX = 1000;
        final String DAY_BEGIN = "2018-01-01";
        final String DAY_END = "2024-12-31";

        // 清空测试数据
        mEnterpriseDbService.clearTest();
        mSalesManDbService.clearTest();
        mCustomerDbService.clearTest();
        mItemDbService.clearTest();
        mConsignDbService.clearTest();
        mConsignHeaderDbService.clearTest();


        // 生成企业
        List<GenEnterpriseEntity> entList = new ArrayList<>();
        for(int i = 1; i <= ENP_CNT; i++) {
            GenEnterpriseEntity enterpriseEntity = new GenEnterpriseEntity();
            enterpriseEntity.createInit();
            enterpriseEntity.setCode(String.format("enp_%03d", i));
            enterpriseEntity.setName(String.format("enp_%03d", i));
            enterpriseEntity.setTestData(true);
            entList.add(enterpriseEntity);
        }

        mEnterpriseDbService.saveBatch(entList);
        Map<Long,List<GenSalesManEntity>> salseManMap = new HashMap<>();
        Map<Long,List<GenCustomerEntity>> customerMap = new HashMap<>();
        Map<Long,List<GenItemEntity>> itemMap = new HashMap<>();
        Map<Long,List<GenItemEntity>> customerItemMap = new HashMap<>();

        // 循环每个企业，用局部变量，节省内存
        for(GenEnterpriseEntity enterpriseEntity : entList) {
            // 为每个企业生成售货员
            int salesCnt = RandomUtil.randomInt(SALES_CNT_MIN, SALES_CNT_MAX);
            List<GenSalesManEntity> salesManEntityList = new ArrayList<>();
            for(int i = 1; i <= salesCnt; i++) {
                GenSalesManEntity salesManEntity = new GenSalesManEntity();
                salesManEntity.createInit();
                salesManEntity.setName(String.format("%s_sales_%03d", enterpriseEntity.getName(),i));
                salesManEntity.setTestData(true);
                salesManEntity.setEnpId(enterpriseEntity.getId());
                salesManEntity.setEnpCode(enterpriseEntity.getCode());
                salesManEntityList.add(salesManEntity);
            }
            mSalesManDbService.saveBatch(salesManEntityList);
            salseManMap.put(enterpriseEntity.getId(), salesManEntityList);
            // 为每个企业生成门店
            int customerCnt = RandomUtil.randomInt(CUSTOMER_CNT_MIN, CUSTOMER_CNT_MAX);
            List<GenCustomerEntity> customerEntityList = new ArrayList<>();
            for(int i = 1; i <= customerCnt; i++) {
                GenCustomerEntity customerEntity = new GenCustomerEntity();
                customerEntity.createInit();
                customerEntity.setName(String.format("%s_customer_%03d", enterpriseEntity.getName(),i));
                customerEntity.setTestData(true);
                customerEntity.setEnpId(enterpriseEntity.getId());
                customerEntity.setEnpCode(enterpriseEntity.getCode());
                customerEntityList.add(customerEntity);
            }
            mCustomerDbService.saveBatch(customerEntityList);
            customerMap.put(enterpriseEntity.getId(), customerEntityList);
            // 为每个企业生成商品
            int itemCnt = RandomUtil.randomInt(ITEM_CNT_MIN, ITEM_CNT_MAX);
            List<GenItemEntity> itemEntityList = new ArrayList<>();
            for(int i=1;i<=itemCnt;i++){
                GenItemEntity itemEntity = new GenItemEntity();
                itemEntity.createInit();
                itemEntity.setRelId(String.valueOf(itemEntity.getId()));
                itemEntity.setName(String.format("%s_item_%03d", enterpriseEntity.getName(),i));
                long cost = RandomUtil.randomLong(ITEM_COST_MIN, ITEM_COST_MAX);
                itemEntity.setCost(cost);
                itemEntity.setTestData(true);
                itemEntity.setEnpId(enterpriseEntity.getId());
                itemEntity.setEnpCode(enterpriseEntity.getCode());
                itemEntityList.add(itemEntity);
            }
            mItemDbService.saveBatch(itemEntityList);
            itemMap.put(enterpriseEntity.getId(), itemEntityList);

            // 确定每个门店的商品列表
            for(GenCustomerEntity customerEntity : customerEntityList) {
                List<GenItemEntity> itemList = new ArrayList<>();
                for(GenItemEntity itemEntity : itemEntityList) {
                    int rd =RandomUtil.randomInt(0,100);
                    if(rd < CUSTOMER_ITEM_SELECT_PERCENT){
                        itemList.add(itemEntity);
                    }
                }
                customerItemMap.put(customerEntity.getId(), itemList);
            }
        }

        LocalDate startDate = LocalDate.parse(DAY_BEGIN);
        LocalDate endDate = LocalDate.parse(DAY_END);

        final ExecutorService executorService = Executors.newFixedThreadPool(20);

        while (!startDate.isAfter(endDate)) {
            log.info("==================>>导入日期"+startDate+"====================");
            // 遍历每个企业
            for(GenEnterpriseEntity enterpriseEntity : entList) {
                List<GenSalesManEntity> salesManEntityList = salseManMap.get(enterpriseEntity.getId());
                List<GenCustomerEntity> customerEntityList = customerMap.get(enterpriseEntity.getId());
                List<GenConsignHeaderEntity> consignHeaderEntityList = new ArrayList<>();
                List<GenConsignEntity> consignEntityList = new ArrayList<>();

                log.info("导入企业"+enterpriseEntity.getName());
                // 遍历旗下所有销售
                for(GenSalesManEntity salesManEntity : salesManEntityList) {
                    // 遍历旗下所有门店
                    for(GenCustomerEntity customerEntity : customerEntityList) {
                       int rd = RandomUtil.randomInt(0,100);
                       // 售货员不来该门店
                       if(rd >= CUSTOMER_SALES_SELECT_PERCENT){
                           continue;
                       }

                       //创建头表
                        GenConsignHeaderEntity consignHeaderEntity = new GenConsignHeaderEntity();
                        consignHeaderEntity.createInit();
                        consignHeaderEntity.setEnpId(enterpriseEntity.getId());
                        consignHeaderEntity.setEnpCode(enterpriseEntity.getCode());
                        consignHeaderEntity.setBillNo(mBillNoUtil.getBillNo("csn"));

                        int hour = RandomUtil.randomInt(0,24);
                        int minute = RandomUtil.randomInt(0,59);
                        int second = RandomUtil.randomInt(0,59);
                        LocalDateTime billTime = startDate.atTime(hour, minute, second);
                        long billTimeKey = CommonUtil.LocalDateTimeToSecond(billTime);
                        String billTimeStr = LocalDateTimeUtil.formatNormal(billTime);

                        consignHeaderEntity.setBillTime(billTime);
                        consignHeaderEntity.setBillTimeKey(billTimeKey);
                        consignHeaderEntity.setBillTimeStr(billTimeStr);

                        consignHeaderEntity.setCustomerId(customerEntity.getId());
                        consignHeaderEntity.setCustomerName(customerEntity.getName());
                        consignHeaderEntity.setSalesId(salesManEntity.getId());
                        consignHeaderEntity.setSalesName(salesManEntity.getName());

                        consignHeaderEntity.setDescription(billTimeStr+" "+customerEntity.getName()+" "+salesManEntity.getName());
                        consignHeaderEntity.setTestData(true);
                        consignHeaderEntityList.add(consignHeaderEntity);

                       List<GenItemEntity> itemEntityList = customerItemMap.get(customerEntity.getId());
                       for(GenItemEntity itemEntity : itemEntityList) {
                           rd = RandomUtil.randomInt(0,10000);
                           if(rd >= ITEM_SALES_SELECT_PERCENT){
                               continue;
                           }
                           int saleCnt = RandomUtil.randomInt(ITEM_SALES_CNT_MIN,ITEM_SALES_CNT_MAX);
                           GenConsignEntity consignEntity = new GenConsignEntity();
                           consignEntity.createInit();
                           consignEntity.setEnpId(enterpriseEntity.getId());
                           consignEntity.setEnpCode(enterpriseEntity.getCode());
                           consignEntity.setCustomerId(customerEntity.getId());
                           consignEntity.setCustomerName(customerEntity.getName());
                           consignEntity.setItemId(itemEntity.getId());
                           consignEntity.setItemStrId(String.valueOf(itemEntity.getId()));
                           consignEntity.setItemName(itemEntity.getName());
                           consignEntity.setItemCnt(saleCnt);
                           consignEntity.setSalesId(salesManEntity.getId());
                           consignEntity.setSalesName(salesManEntity.getName());

                           consignEntity.setBillTime(billTime);
                           consignEntity.setBillTimeKey(billTimeKey);
                           consignEntity.setHeaderId(consignHeaderEntity.getId());
                           consignEntity.setBillNo(consignHeaderEntity.getBillNo());

                           int profitPercent = RandomUtil.randomInt(ITEM_PROFIT_MIN,ITEM_PROFIT_MAX);
                           long cost = (long)(itemEntity.getCost() * (1 + profitPercent/100.0));
                           consignEntity.setPrice(cost);
                           consignEntityList.add(consignEntity);
                       }
                    }
                }
                // 多线程反而更慢，因为插入的顺序问题
//                executorService.submit(()->{
//                    TransactionTemplate template = new TransactionTemplate(mTransactionManager);
//                    template.execute(status ->{
//                        mConsignHeaderDbService.saveBatch(consignHeaderEntityList);
//                        mConsignDbService.saveBatch(consignEntityList);
//                        return true;
//                    });
//                });

                TransactionTemplate template = new TransactionTemplate(mTransactionManager);
                template.execute(status ->{
                    mConsignHeaderDbService.saveBatch(consignHeaderEntityList);
                    mConsignDbService.saveBatch(consignEntityList);
                    return true;
                });

                log.info("本轮销售单已提交导入");
            }
            startDate = startDate.plusDays(1l);
        }
    }
}

四、测试代码

4.1 测试接口

public interface IConsignService {
    void initTestData();
    Page<ConsignMapperOut> pageGroupByItemId(ConsignPageRequest pConsignPageRequest);
}

impl文件相应函数

    @Override
    public Page<ConsignMapperOut> pageGroupByItemId(ConsignPageRequest pConsignPageRequest) {
        Page<ConsignMapperOut> pageCfg = new Page<>(pConsignPageRequest.getCurrent(),pConsignPageRequest.getSize());
        ConsignMapperIn consignMapperIn = pConsignPageRequest.toMapperIn();
        long curTime = System.currentTimeMillis();
        Page<ConsignMapperOut> consignMapperOutPage = mConsignMapper.page(pageCfg, consignMapperIn);
        long endTime = System.currentTimeMillis();
        log.info("cost time "+(endTime - curTime)/1000.0 + "s");
        return consignMapperOutPage;
    }

4.2 request类

request类

@Data
public class ConsignPageRequest extends BasePageRequest {
    List<Long> itemIdList;
    List<Long> customerIdList;
    List<Long> salesmanIdList;
    Long enterpriseId;
    LocalDate billTimeBeg;
    LocalDate billTimeEnd;

    EHeaderJoinMode headerJoin;
    EItemJoinMode itemJoin;
    EOrderByMode orderBy;
    EBillTimeMode billTimeMode;

    public ConsignMapperIn toMapperIn(){
        ConsignMapperIn consignMapperIn = new ConsignMapperIn();
        consignMapperIn.setItemIdList(itemIdList);
        consignMapperIn.setCustomerIdList(customerIdList);
        consignMapperIn.setSalesmanIdList(salesmanIdList);
        consignMapperIn.setEnterpriseId(enterpriseId);

        LocalDateTime billTimeBegT = billTimeBeg.atTime(0,0,0);
        LocalDateTime billTimeEndT = billTimeEnd.atTime(23,59,59);

        consignMapperIn.setBillTimeBeg(billTimeBegT);
        consignMapperIn.setBillTimeEnd(billTimeEndT);

        consignMapperIn.setBillTimeKeyBeg(CommonUtil.LocalDateTimeToSecond(billTimeBegT));
        consignMapperIn.setBillTimeKeyEnd(CommonUtil.LocalDateTimeToSecond(billTimeEndT));

        consignMapperIn.setBillTimeStrBeg(LocalDateTimeUtil.formatNormal(billTimeBegT));
        consignMapperIn.setBillTimeStrEnd(LocalDateTimeUtil.formatNormal(billTimeEndT));
        
        consignMapperIn.setGroup(Arrays.asList("item_id"));
        consignMapperIn.setHeaderJoin(headerJoin);
        consignMapperIn.setItemJoin(itemJoin);
        consignMapperIn.setOrderBy(orderBy);
        consignMapperIn.setBillTimeMode(billTimeMode);
        return consignMapperIn;
    }
}

mapper request类

@Data
public class ConsignMapperIn {
    List<Long> itemIdList;
    List<Long> customerIdList;
    List<Long> salesmanIdList;
    Long enterpriseId;
    List<String> group;

    LocalDateTime billTimeBeg;
    LocalDateTime billTimeEnd;

    String billTimeStrBeg;
    String billTimeStrEnd;

    Long billTimeKeyBeg;
    Long billTimeKeyEnd;

    EHeaderJoinMode headerJoin;
    EItemJoinMode itemJoin;
    EOrderByMode orderBy;
    EBillTimeMode billTimeMode;
}

4.3 一些枚举

我们把一些查询模式封装到枚举里，方便做测试

@RequiredArgsConstructor
@EnumDesc
public enum EBillTimeMode {

    BILL_TIME_KEY(1,"bill_time_key","使用时间换算的秒，bill_time_key作为查询键","bill_time_key"),
    BILL_TIME(2,"bill_time","使用datetime bill_time作为查询键","bill_time"),
    BILL_TIME_STR(3,"bill_time_str","使用字符串bill_time_str做查询键","bill_time_str");

    @EnumValue
    @Getter
    private final int code;
    @Getter
    private final String name;
    @Getter
    private final String desc;
    @Getter
    private final String col;
}

@RequiredArgsConstructor
@EnumDesc
public enum EHeaderJoinMode {

    NONE(0,"不连表","不连表，直接使用consign表做查询",null,null),
    ID_JOIN(1,"id关联","consign_header的id与consign的header_id做关联","id","header_id"),
    BILL_NO_JOIN(2,"订单号关联","header表的enp_id和bill_no与consin相应字段关联","bill_no","bill_no");


    @EnumValue
    @Getter
    private final int code;
    @Getter
    private final String name;
    @Getter
    private final String desc;
    @Getter
    private final String headerCol;
    @Getter
    private final String consignCol;
}

@RequiredArgsConstructor
@EnumDesc
public enum EItemJoinMode {

    NONE(0,"不连接","不连接item表",null,null),
    ID_JOIN(1,"id连接","使用id链接item","item_id","id"),
    STR_ID_JOIN(2,"字符串id连接","使用字符串id做连接","item_str_id","id"),
    REL_ID_JOIN(3,"关联id连接","不但使用字符串id做连接，item表也不用主键","item_str_id","rel_id");

    @EnumValue
    @Getter
    private final int code;
    @Getter
    private final String name;
    @Getter
    private final String desc;
    @Getter
    private final String consignCol;
    @Getter
    private final String itemCol;
}

@RequiredArgsConstructor
@EnumDesc
public enum EOrderByMode {

    PROFIT(1,"利润","按照利润排序","total_profit"),
    AMOUNT(2,"总金额","按总金额排序","total_amount"),
    CNT(3,"总数量","按总数量排序","total_cnt");

    @EnumValue
    @Getter
    private final int code;
    @Getter
    private final String name;
    @Getter
    private final String desc;
    @Getter
    private final String col;
}

4.4 xml的sql

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="indi.zhifa.study2024.consigntest.logic.busy.consign.mapper.ConsignMapper">

    <sql id="select">
        SELECT c.item_id,c.item_name,SUM(c.item_cnt) AS total_cnt, SUM(c.price * c.item_cnt) AS total_amount,
        <if test="IN.itemJoin.name() != 'NONE'">
            SUM((c.price - i.cost) * c.item_cnt) AS total_profit
        </if>
        FROM
        <choose>
            <when test="IN.headerJoin.name() == 'ID_JOIN'">
                consign_header h JOIN consign c ON h.${IN.headerJoin.headerCol} = c.${IN.headerJoin.consignCol}
            </when>
            <when test="IN.headerJoin.name() == 'BILL_NO_JOIN'">
                consign_header h JOIN consign c ON h.${IN.headerJoin.headerCol} = c.${IN.headerJoin.consignCol} AND h.enp_id = c.enp_id
            </when>
            <otherwise>
                consign c
            </otherwise>
        </choose>
        <choose>
            <when test="IN.itemJoin.name() == 'ID_JOIN'">
                JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol}
            </when>
            <when test="IN.itemJoin.name() == 'STR_ID_JOIN'">
                JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol}
            </when>
            <when test="IN.itemJoin.name() == 'REL_ID_JOIN'">
                JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol} AND c.enp_id = i.enp_id
            </when>
        </choose>

        <where>
            <if test="IN.itemIdList != null and IN.itemIdList.size > 0">
                <choose>
                    <when test="IN.itemIdList.size == 1">item_id = #{IN.itemIdList[0]}</when>
                    <otherwise>
                        AND c.item_id IN
                        <foreach collection="IN.itemIdList" item="item" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </otherwise>
                </choose>
            </if>
            <if test="IN.customerIdList != null and IN.customerIdList.size > 0">
                AND
                <choose>
                    <when test="IN.customerIdList.size == 1">
                        <choose>
                            <when test="IN.headerJoin.name() == 'NONE'">
                                c.customer_id
                            </when>
                            <otherwise>
                                h.customer_id
                            </otherwise>
                        </choose>
                        = #{IN.customerIdList[0]}</when>
                    <otherwise>
                        <choose>
                            <when test="IN.headerJoin.name() == 'NONE'">
                                c.customer_id
                            </when>
                            <otherwise>
                                h.customer_id
                            </otherwise>
                        </choose>
                        IN
                        <foreach collection="IN.customerIdList" item="item" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </otherwise>
                </choose>
            </if>
            <if test="IN.salesmanIdList != null and IN.salesmanIdList.size > 0">
                <choose>
                    <when test="IN.salesmanIdList.size == 1">
                        <choose>
                            <when test="IN.headerJoin.name() ==  'NONE'">
                                c.sales_id
                            </when>
                            <otherwise>
                                h.sales_id
                            </otherwise>
                        </choose>
                        = #{IN.salesmanIdList[0]}</when>
                    <otherwise>
                        AND
                        <choose>
                            <when test="IN.headerJoin.name() == 'NONE'">
                                c.sales_id
                            </when>
                            <otherwise>
                                h.sales_id
                            </otherwise>
                        </choose>
                        IN
                        <foreach collection="IN.customerIdList" item="item" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </otherwise>
                </choose>
            </if>
            <if test="IN.enterpriseId != null">
                AND
                <choose>
                    <when test="IN.headerJoin.name() != 'NONE'">h.enp_id = #{IN.enterpriseId}</when>
                    <otherwise>c.enp_id = #{IN.enterpriseId}</otherwise>
                </choose>

            </if>
            <if test="IN.billTimeBeg != null and IN.billTimeEnd != null">
                AND
                <choose>
                    <when test="IN.headerJoin.name() != 'NONE'">h.${IN.billTimeMode.col} BETWEEN </when>
                    <otherwise>c.${IN.billTimeMode.col} BETWEEN </otherwise>
                </choose>
                <choose>
                    <when test="IN.billTimeMode.name() == 'BILL_TIME_KEY'">#{IN.billTimeKeyBeg} AND #{IN.billTimeKeyEnd}</when>
                    <when test="IN.billTimeMode.name() == 'BILL_TIME'">#{IN.billTimeBeg} AND #{IN.billTimeEnd}</when>
                    <when test="IN.billTimeMode.name() == 'BILL_TIME_STR'">#{IN.billTimeStrBeg} AND #{IN.billTimeStrEnd}</when>
                </choose>
            </if>
        </where>
        <if test="IN.group != null and IN.group.size > 0">
            GROUP BY
            <foreach collection="IN.group" item="item" open=" " separator="," close=" ">
                ${item}
            </foreach>
        </if>
        ORDER BY ${IN.orderBy.col}
    </sql>

    <select id="page"
            resultType="indi.zhifa.study2024.consigntest.logic.busy.consign.entity.mapperOut.ConsignMapperOut">
        <include refid="select"></include>
    </select>
    <select id="list"
            resultType="indi.zhifa.study2024.consigntest.logic.busy.consign.entity.mapperOut.ConsignMapperOut">
        <include refid="select"></include>
    </select>
</mapper>

五、实验

我们开始逐步实验，首先，我们检验一下之前生成的数据概况：

5.1、实验数据概况

如果用workbench，记得把最大查询时间改长
Edit->Preferences->SQL Editor

-- 10
select count(*) from enterprise
-- 126
select count(*) from sales_man
-- 377
select count(*) from customer
-- 58218
select count(*) from item
-- 1829939
select count(*) from consign_header
-- 80,505,624，查询时间40.9s
select count(*) from consign

select h.enp_id,h.enp_code,count(*) AS cnt from consign_header h group by h.enp_id

机器配置：
操作系统是ubuntu

lscpu
# 结果是4核，4500MHZ
free -h
# 内存32G

5.2、实验条目

case1 预期最慢的查询

我们和item连表，不使用主键，和header连表，也不使用主键，时间查询使用字符串
请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "BILL_NO_JOIN",
  "itemJoin": "REL_ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "bill_time_key"
}

生成sql：

explain SELECT c.item_id,c.item_name,
SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign_header h JOIN consign c 
ON h.bill_no = c.bill_no AND h.enp_id = c.enp_id 
JOIN item i ON c.item_str_id = i.rel_id AND c.enp_id = i.enp_id 
WHERE h.enp_id = 1869035491194941447 
AND h.bill_time_str BETWEEN '2024-04-01 00:00:00' AND '2024-07-31 23:59:59'
GROUP BY item_id 
ORDER BY total_profit LIMIT 10,10;

explain结果

执行时间

case2 使用datetime时间

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "BILL_NO_JOIN",
  "itemJoin": "REL_ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "BILL_TIME"
}

生成sql：

SELECT c.item_id,c.item_name,
SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign_header h JOIN consign c 
ON h.bill_no = c.bill_no AND h.enp_id = c.enp_id 
JOIN item i ON c.item_str_id = i.rel_id AND c.enp_id = i.enp_id 
WHERE h.enp_id = 1869035491194941447
AND h.bill_time BETWEEN '2024-04-01T00:00:00' 
AND '2024-07-31T23:59:59' 
GROUP BY item_id 
ORDER BY total_profit LIMIT 10,10;

explain结果

执行时间

case3 使用datetime-key时间

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "BILL_NO_JOIN",
  "itemJoin": "REL_ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "BILL_TIME_KEY"
}

生成sql：

SELECT c.item_id,c.item_name,
SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign_header h JOIN consign c 
ON h.bill_no = c.bill_no AND h.enp_id = c.enp_id 
JOIN item i ON c.item_str_id = i.rel_id AND c.enp_id = i.enp_id 
WHERE h.enp_id = 1869035491194941447
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599
GROUP BY item_id 
ORDER BY total_profit LIMIT 10,10;

explain结果

执行时间

case4 item连表使用STR_ID_JOIN

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "BILL_NO_JOIN",
  "itemJoin": "STR_ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "BILL_TIME_KEY"
}

生成sql

SELECT c.item_id,c.item_name,SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign_header h 
JOIN consign c ON h.bill_no = c.bill_no AND h.enp_id = c.enp_id 
JOIN item i ON c.item_str_id = i.id
WHERE h.enp_id = 1869035491194941447
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
GROUP BY item_id ORDER BY total_profit LIMIT 10,10;

explain结果

执行时间

有了明显的时间优化，大概20%

case5 item连表使用ID_JOIN

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "BILL_NO_JOIN",
  "itemJoin": "ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "BILL_TIME_KEY"
}

这个问题，在mysql8中被优化了，测不出来问题，这里就不详述了

case6 header使用id连表

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "ID_JOIN",
  "itemJoin": "ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "BILL_TIME_KEY"
}

生成sql

SELECT c.item_id,c.item_name,SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign_header h 
JOIN consign c ON h.id = c.header_id
JOIN item i ON c.item_id = i.id
WHERE h.enp_id = 1869035491194941447 
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
GROUP BY item_id ORDER BY total_profit LIMIT 10,10;

explain结果

执行时间

结论：
反倒时间比之前更长

case7 不连表header

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941447,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "NONE",
  "itemJoin": "ID_JOIN",
  "orderBy": "PROFIT",
  "billTimeMode": "BILL_TIME_KEY"
}

生成sql

SELECT c.item_id,c.item_name,
SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign c JOIN item i ON c.item_id = i.id 
WHERE c.enp_id = 1869035491194941447 
AND c.bill_time_key BETWEEN 1711900800 AND 1722441599
GROUP BY item_id ORDER BY total_profit LIMIT 10,10;

explain结果

执行时间

结论：
反倒时间更长了

5.3、实验结论

case5，大概是效率最高的方式。

• 直接使用id做表关联，不见得会快。
• 时间key使用字符串甚至比dateTime更快，和转化为秒的key持平。

由于篇幅限制，进一步的测试在下一节再写。
或许，在制定主键策略时，可能用联合主键效果更好。

六、代码展示

还是老地方，请大家移步码云