当前位置： 首页 > article >正文

芝法酱学习笔记（2.2）——sql性能优化2

article 2024/12/24 13:43:46

一、前言

在上一节中，我们使用实验的方式，验证了销售单报表的一些sql性能优化的猜想。但实验结果出乎我们的意料，首先是时间查询使用char和datetime相比，char可能更快，使用bigint（转为秒）和char速度相当。其次是最令人不可理解的是，在连表的时候，直接使用主键id做连表，竟然远远比使用多条件联合索引的连表方式更慢。
小编苦思冥想，认为在千万级数据量下，bigint的筛选效率可能没有联合索引高。那么，如果我们把主键设置为联合主键，在连表时会不会更快呢？那么，就开干吧

二、表设计

2.1 item_new表

我们新建一个item表，表结构和索引如下图所示：
在这里插入图片描述
主键：enp_id,id
不添加任何其他索引

2.2 consign_new表

consign表也类似，我们也用联合主键
在这里插入图片描述
主键：enp_id,header_id,id

三、数据同步导入

为了控制变量，我们不重新生成数据，我们把上次的数据查询出来并相应的插入新表

@Override
    public void syncNewTable() {
        // 查出所有企业
        List<GenEnterpriseEntity> enterpriseEntities = mEnterpriseDbService.list();

        for(GenEnterpriseEntity enterpriseEntity : enterpriseEntities) {

            log.info("开始导入"+enterpriseEntity.getName()+"数据");

            List<GenItemEntity> itemEntityList = mItemDbService.listByEnpId(enterpriseEntity.getId());
            List<GenItemNewEntity> itemNewEntityList = new ArrayList<>();

            for(GenItemEntity itemEntity : itemEntityList) {
                GenItemNewEntity itemNewEntity = new GenItemNewEntity();
                itemNewEntity.createInit();
                itemNewEntity.setEnpId(itemEntity.getId());
                itemNewEntity.setEnpCode(itemEntity.getEnpCode());
                itemNewEntity.setId(itemEntity.getId());
                itemNewEntity.setName(itemEntity.getName());
                itemNewEntity.setCost(itemEntity.getCost());
                itemNewEntity.setTestData(true);
                itemNewEntityList.add(itemNewEntity);
            }

            TransactionTemplate template = new TransactionTemplate(mTransactionManager);
            template.execute(status ->{
                mItemNewDbService.saveBatch(itemNewEntityList);
                return true;
            });
            log.info("导入商品完成");

            final String DAY_BEGIN = "2018-01-01";
            final String DAY_END = "2024-12-31";


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

            while (!startDate.isAfter(endDate)) {

                log.info("导入"+startDate+"的销售单数据");

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

                long billTimeKeyBeg = CommonUtil.LocalDateTimeToSecond(billTimeBeg);
                long billTimeKeyEnd = CommonUtil.LocalDateTimeToSecond(billTimeEnd);

                List<GenConsignEntity> consignEntityList = mConsignDbService.findAll(enterpriseEntity.getId(),billTimeKeyBeg,billTimeKeyEnd);
                List<GenConsignNewEntity> consignNewEntityList = new ArrayList<>();

                for(GenConsignEntity consignEntity : consignEntityList) {
                    GenConsignNewEntity consignNewEntity = new GenConsignNewEntity();
                    consignNewEntity.createInit();
                    consignNewEntity.setId(consignEntity.getId());
                    consignNewEntity.setEnpId(consignEntity.getEnpId());
                    consignNewEntity.setHeaderId(consignEntity.getHeaderId());
                    consignNewEntity.setBillTimeKey(consignEntity.getBillTimeKey());
                    consignNewEntity.setItemId(consignEntity.getItemId());
                    consignNewEntity.setItemName(consignEntity.getItemName());
                    consignNewEntity.setItemCnt(consignEntity.getItemCnt());
                    consignNewEntity.setPrice(consignEntity.getPrice());
                    consignNewEntity.setDescription(consignEntity.getDescription());
                    consignNewEntity.setTestData(true);
                    consignNewEntityList.add(consignNewEntity);
                }
                consignNewEntityList.sort(Comparator.comparing(GenConsignNewEntity::getHeaderId));

                template = new TransactionTemplate(mTransactionManager);
                template.execute(status -> {
                    mConsignNewDbService.saveBatch(consignNewEntityList);
                    return true;
                });

                log.info(startDate+"的销售单数据导入完成");

                startDate = startDate.plusDays(1l);
            }
        }
    }

四、mapper改写

4.1 枚举

@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"),
    NEW_CONSIGN(3,"新consign表","enp_id和header_id做连接","id","header_id");


    @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"),
    NEW_TABLE(4,"和item_new做连接","和item_new做连接,不但连id，还连enp_id","item_id","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;
}

4.2 xml改写

这里不放完整代码了，就改了2处

<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>
            <!--新增模式-->
            <when test="IN.headerJoin.name() == 'NEW_CONSIGN'">
                consign_header h JOIN consign_new 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>
            <!--新增模式-->
            <when test="IN.itemJoin.name() == 'NEW_TABLE'">
                JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol} AND c.enp_id = i.enp_id
            </when>
        </choose>

五、实验

请求json

{
  "current": 2,
  "size": 10,
  "enterpriseId": 1869035491194941444,
  "billTimeBeg": "2024-04-01",
  "billTimeEnd": "2024-07-31",
  "headerJoin": "NEW_CONSIGN",
  "itemJoin": "NEW_TABLE",
  "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_new c ON h.id = c.header_id AND h.enp_id = c.enp_id 
JOIN item i ON c.item_id = i.id AND c.enp_id = i.enp_id 
WHERE h.enp_id = 1869035491194941444 
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
GROUP BY item_id 
ORDER BY total_profit LIMIT 10,10

explain结果

select_typetabletypekeyken_kenrowsfiltered
simplehrangidx_time_key1611516100
simplecrefPRIMARY1643100
simpleirefPRIMARY81100

执行时间

enp_idenp_codecnttime
1869035491194941442enp_0012488142.638s
1869035491194941443enp_0022637802.285s
1869035491194941444enp_0031205221.157s
1869035491194941445enp_004842621.003s
1869035491194941446enp_0051746734.157s
1869035491194941447enp_0063427514.105s
1869035491194941448enp_007529640.48s
1869035491194941449enp_0081721593.895s
1869035491194941450enp_0091816324.688s
1869035491194941451enp_0101883825.168s

先前使用id主键连表的执行时间

enp_idenp_codecnttime
1869035491194941442enp_00124881419.311s
1869035491194941443enp_00226378018.534s
1869035491194941444enp_00312052213.849s
1869035491194941445enp_004842625.782s
1869035491194941446enp_00517467321.158s
1869035491194941447enp_00634275120.927s
1869035491194941448enp_007529643.087s
1869035491194941449enp_00817215919.982s
1869035491194941450enp_00918163223.256s
1869035491194941451enp_01018838226.057s

结论：
效率比先前，提升了6~8倍！！！

六、经验总结

在设计表的时候，如果有明确的父子表层级关系（1对多），并且数据量很大，子表的主键直接设计成联合主键。
比如本案例中的，enterprise -> item,consign_header; consign_header->consign

七、一些其他筛选参数

上节我们还有一些其他筛选参数的情况没有测试，诸如门店id，业务员id等
群友们可以自行测试，我这里就不耗费篇幅了。
结论就是按照预期走了索引。

八、order by问题

上期由于篇幅原因，还有一个问题没有实验，就是order by id使得主键索引覆盖了正常的索引。
小编懒得写代码做实验了，我们直接写sql吧：

ORDER BY id DESC:

SELECT * FROM consign_header h
WHERE h.enp_id = 1869035491194941447
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
ORDER BY id DESC LIMIT 100;

在workbench的执行时间：0.047S
在这里插入图片描述

ORDER BY bill_time_key DESC:

explain SELECT * FROM consign_header h
WHERE h.enp_id = 1869035491194941447
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
ORDER BY h.bill_time_key DESC LIMIT 100;

在workbench的执行时间：0.015S，并且第二次执行会因缓存变为0秒
在这里插入图片描述
我们可以看到，order by id 会使得查找条件不走索引，而走了主键，并且速度显著降低。


http://www.kler.cn/a/449374.html

相关文章：

  • idea2024创建JavaWeb项目以及配置Tomcat详解
  • Element-plus表格使用总结
  • ARM异常处理 M33
  • 重温设计模式--命令模式
  • AOP 面向切面编程的实现原理
  • Java/JDK下载、安装及环境配置超详细教程【Windows10、macOS和Linux图文详解】
  • 0.96寸OLED显示屏详解
  • Day1 苍穹外卖前端 Vue基础、Vue基本使用方式、Vue-router、Vuex、TypeScript
  • Python实现将series系列数据格式批量转换为Excel
  • OCR（五）linux 环境 基于c++的 paddle ocr 编译【CPU版本 】
  • 高原地区无人机巡检作业技术详解
  • 螺栓连接|结构强度与刚度评定
  • C++练习题之计算天数
  • SpringBoot3-第二篇（Web开发）
  • 使用FreeNAS软件部署ISCSI的SAN架构存储（IP-SAN）练习题
  • 物联网水文观测设备
  • 蓝桥杯物联网开发板硬件组成
  • 汽车IVI中控开发入门及进阶（41）：视频播放器MPlayer
  • 单片机的内存是指RAM还是ROM
  • Android Studio Gradle Sync timeout
  • H5海康WS在线视频播放器：打造高效流畅的Web视频体验
  • BufferedWriter（废稿）
  • JUC并发编程之Thread学习笔记
  • count(1)、count(_)与count(列名)的区别？
  • 七牛云短信服务
  • 《前端web开发-HTML5基础》