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

一、前言

在上一节中，我们使用实验的方式，验证了销售单报表的一些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 会使得查找条件不走索引，而走了主键，并且速度显著降低。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/939798.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

安装Linux操作系统

安装Linux操作系统

确保虚拟机安装成功&#xff0c;接下来开始安装操作系统&#xff0c;通过虚拟光驱安装。 1. 点击图中的 CD/DVD &#xff0c;设置光盘文件&#xff0c;光盘文件下载地址&#xff1a; https://mirrors.tuna.tsinghua.edu.c n/centos-vault/8.5.2111/isos/x86_64/ 说明&#xf…
阅读更多...
【网络安全产品大调研系列】1. 漏洞扫描

【网络安全产品大调研系列】1. 漏洞扫描

1. 为什么会出现漏扫技术&#xff1f; 每次黑客攻击事件进行追溯的时候&#xff0c;根据日志分析后&#xff0c;我们往往发现基本都是系统、Web、 弱口令、配置这四个方面中的其中一个出现的安全问题导致黑客可以轻松入侵的。 操作系统的版本滞后&#xff0c;没有更新补丁&am…
阅读更多...
Java CountDownLatch 用法和源码解析

Java CountDownLatch 用法和源码解析

&#x1f9d1; 博主简介&#xff1a;CSDN博客专家&#xff0c;历代文学网&#xff08;PC端可以访问&#xff1a;https://literature.sinhy.com/#/literature?__c1000&#xff0c;移动端可微信小程序搜索“历代文学”&#xff09;总架构师&#xff0c;15年工作经验&#xff0c;…
阅读更多...
AFL-Fuzz 的使用

AFL-Fuzz 的使用

AFL-Fuzz 的使用 一、工具二、有源码测试三、无源码测试 一、工具 建议安装LLVM并使用afl-clang-fast或afl-clang-lto进行编译&#xff0c;这些工具提供了更现代和高效的插桩技术。您可以按照以下步骤安装LLVM和afl-clang-fast&#xff1a; sudo apt update sudo apt install…
阅读更多...
Java项目--仿RabbitMQ的消息队列--网络通信协议设计

Java项目--仿RabbitMQ的消息队列--网络通信协议设计

目录 一、引言 二、设计 三、代码 1.Request 2.Response 3.BasicArguments 4.BasicReturns 四、方法类 1.创建交换机 2.删除交换机 3.创建队列 4.删除队列 5.创建绑定 6.删除绑定 7.消息发布 8.消费消息 9.集中返回 五、实现Broker Server类 六、实现连…
阅读更多...
MySQL通过binlog日志进行数据恢复

MySQL通过binlog日志进行数据恢复

记录一次阿里云MySQL通过binlog日志进行数据回滚 问题描述由于阿里云远程mysql没有做安全策略 所以服务器被别人远程攻击把数据库给删除&#xff0c;通过查看binlog日志可以看到进行了drop操作&#xff0c;下面将演示通过binlog日志进行数据回滚操作。 1、查询是否开始binlog …
阅读更多...
王佩丰24节Excel学习笔记——第十二讲：match + index

王佩丰24节Excel学习笔记——第十二讲：match + index

【以 Excel2010 系列学习&#xff0c;用 Office LTSC 专业增强版 2021 实践】 【本章小技巧】 vlookup与match&#xff0c;index 相结合使用match,index 结合&#xff0c;快速取得引用的值扩展功能&#xff0c;使用match/index函数&#xff0c;结合照相机工具获取照片 一、回顾…
阅读更多...
《Time Ghost》的制作：使用 DOTS ECS 制作更为复杂的大型环境

《Time Ghost》的制作：使用 DOTS ECS 制作更为复杂的大型环境

*基于 Unity 6 引擎制作的 demo 《Time Ghost》 开始《Time Ghost》项目时的目标之一是提升在 Unity 中构建大型户外环境的构建标准。为了实现这一目标&#xff0c;我们要有处理更为复杂的场景的能力、有足够的工具支持&#xff0c;同时它对引擎的核心图形、光照、后处理、渲染…
阅读更多...
【考前预习】4.计算机网络—网络层

【考前预习】4.计算机网络—网络层

往期推荐 【考前预习】3.计算机网络—数据链路层-CSDN博客 【考前预习】2.计算机网络—物理层-CSDN博客 【考前预习】1.计算机网络概述-CSDN博客 目录 1.网络层概述 2.网络层提供的两种服务 3.分类编址的IPV4 4.无分类编址的IPV4—CIDR 5.IPV4地址应用规划 5.1使用定长子…
阅读更多...
解决pip下载慢

解决pip下载慢

使用pip下载大量安装包&#xff0c;下载速度太慢了 1、问题现象 pip安装包速度太慢 2、解决方案 配置国内源 vi /root/.config/pip/pip.conf[global] timeout 6000 index-url https://mirrors.aliyun.com/pypi/simple/ trusted-host mirrors.aliyun.com
阅读更多...
【Linux】Linux权限管理：文件与目录的全面指南

【Linux】Linux权限管理：文件与目录的全面指南

在Linux系统中&#xff0c;权限管理是确保数据安全的关键。本文将为大家介绍Linux文件与目录的权限管理&#xff0c;帮助你理解如何设置和管理访问权限。无论你是新手还是有经验的用户&#xff0c;这里都将提供实用的技巧和知识&#xff0c;助你更好地掌握Linux环境。让我们一起…
阅读更多...
【模型压缩】原理及实例

【模型压缩】原理及实例

在移动智能终端品类越发多样的时代&#xff0c;为了让模型可以顺利部署在算力和存储空间都受限的移动终端&#xff0c;对模型进行压缩尤为重要。模型压缩&#xff08;model compression&#xff09;可以降低神经网络参数量&#xff0c;减少延迟时间&#xff0c;从而实现提高神经…
阅读更多...
Android Stduio 2024版本设置前进和后退按钮显示在主界面

Android Stduio 2024版本设置前进和后退按钮显示在主界面

Android Studio 2024&#xff08;Ladybug&#xff09;安装后发现前进和后退按钮不显示在主界面的工具栏&#xff0c;且以前在View中设置的办法无效&#xff1a; Android Studio 2024&#xff08;Ladybug&#xff09;的设置方式&#xff1a; File->Settings->Appearance&…
阅读更多...
MySQL数据库——门诊管理系统数据库数据表

MySQL数据库——门诊管理系统数据库数据表

门诊系统数据库his 使用图形化工具或SQL语句在简明门诊管理系统数据库his中创建数据表&#xff0c;数据表结构见表2-3-9&#xff5e;表2-3-15所示。 表2-3-9 department&#xff08;科室信息表&#xff09; 字段名称 数据类型 长度 是否为空 说明 dep_ID int 否 科室…
阅读更多...
Ubuntu上如何部署Nginx？

Ubuntu上如何部署Nginx？

环境&#xff1a; Unbuntu 22.04 问题描述&#xff1a; Ubuntu上如何部署Nginx&#xff1f; 解决方案&#xff1a; 在Ubuntu上部署Nginx是一个相对简单的过程&#xff0c;以下是详细的步骤指南。我们将涵盖安装Nginx、启动服务、配置防火墙以及验证安装是否成功。 1. 更新…
阅读更多...
【从零开始入门unity游戏开发之——C#篇08】逻辑运算符、位运算符

【从零开始入门unity游戏开发之——C#篇08】逻辑运算符、位运算符

文章目录 一、逻辑运算符1、**&&&#xff08;逻辑与&#xff09;**语法&#xff1a;示例&#xff1a; 2、**||&#xff08;逻辑或&#xff09;**语法&#xff1a;示例&#xff1a; 3、**!&#xff08;逻辑非&#xff09;**语法&#xff1a;示例&#xff1a; 4、**^&…
阅读更多...
【Android开发】安装Android Studio（2023.1.1）

【Android开发】安装Android Studio（2023.1.1）

下载安装包 Android Studio2023.1.1百度云盘下载&#xff0c;提取码&#xff1a;6666https://pan.baidu.com/s/1vNJezi7aDOP0poPADcBZZg?pwd6666 安装Android Studio 2023.1.1 双击下载好的安装包 弹出界面点击下一步 继续点击【Next】 更改安装路径后继续点击【Next】 点…
阅读更多...
.net winform 实现CSS3.0 泼墨画效果

.net winform 实现CSS3.0 泼墨画效果

效果图 代码 private unsafe void BlendImages1(Bitmap img1, Bitmap img2) {// 确定两个图像的重叠区域Rectangle rect new Rectangle(0, 0,Math.Min(img1.Width, img2.Width),Math.Min(img1.Height, img2.Height));// 创建输出图像&#xff0c;尺寸为重叠区域大小Bitmap b…
阅读更多...
Linux下部署MySQL8.0集群 - 主从复制(一主两从)

Linux下部署MySQL8.0集群 - 主从复制(一主两从)

目录 一、部署前准备 1、查看系统信息 # 查看系统版本 cat /etc/red* # 查看系统位数 getconf LONG_BIT[rootlocalhost ~]# cat /etc/red* CentOS Linux release 7.5.1804 (Core) [rootlocalhost ~]# getconf LONG_BIT 642、下载对应安装包 进入MySQL官网&#xff1a;https:…
阅读更多...
编辑， 抽成组件

编辑， 抽成组件

问题 错误思路&#xff1a; 1 dept不能修改&#xff0c; 用watch监听一下&#xff1a;赋值给新的变量进行修改&#xff0c; 问题&#xff1a; currentDept 发生改变&#xff0c; depth也发生了改变&#xff0c;因为是浅拷贝&#xff0c; 用了json.pase(json.stringify(value…
阅读更多...
最新文章

Copyright @ 2022~2023