优化SQL语句，以提高查询速度

liurunyu

2025-07-29 83c1bbca96beb5461fff56d861214444348e86d0

 pipIrr-platform/pipIrr-global/src/main/resources/application-global.yml

@@ -1,9 +1,16 @@
spring:
    main:
        allow-bean-definition-overriding: true #设置为true时，后定义的bean会覆盖之前定义的相同名称的bean
    servlet:
        multipart:
            # 前端上传文件，限制单个文件的大小和限制所有文件的大小
            max-file-size: 500MB
            max-request-size: 5000MB
    http:
        client:
            # spring restTemplate 对外http访问时
            connect-timeout: 3000 # 连接超时时间
            read-timeout: 3000 # 读取超时时间
logging:
    charset:
        console: UTF-8
@@ -81,10 +88,12 @@
        dev: false  #是否开发阶段，true或false
        dsName: ym  #开发阶段，设置临时的数据库名称
    nginx:
        webPort: 19913
        root: D:/apps/pipIrr/nginx-1.27.0
        webPort: 54321
        webFilePort: 64321
    mw:
        webPort: 8070 #通信中间件中应用，不能在web模块系统中应用
        actutorPort: 9070 #通信中间件中应用，不能在web模块系统中应用
        webPort: ${common.pipIrr_mw_webPort} #通信中间件中应用，不能在web模块系统中应用
        actutorPort: ${common.pipIrr_mw_actutorPort} #通信中间件中应用，不能在web模块系统中应用
        idSuffix: 0
    sso:
        cacheMaximumSize: 10000 #登录用户缓存可缓存用户的最大值
@@ -137,6 +146,10 @@
        webPort: 8089
        actutorPort: 9089
        idSuffix: 11
    terminal:
        webPort: 8090
        actutorPort: 9090
        idSuffix: 12
    # 2023-10-24新增，用于执行临时任务，例如删除数据库中上些出错的数据
    temp:
        webPort: 8099
@@ -192,112 +205,112 @@
        supportedFileTypes: docx,xlsx,doc,xls,pdf #支持的文档（非媒体文件，如照片、录音、录像）文件类型
        sv1:
            id: dyFile1
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile1
            hashStart: 0
            hashEnd: 5461
            restUrl: http://127.0.0.1:${pipIrr.file1.webPort}/file #file是上下文,ip为局域网ip或本地IP
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file1.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv2:
            id: dyFile2
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile2
            hashStart: 5462
            hashEnd: 10923
            restUrl: http://127.0.0.1:${pipIrr.file2.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file2.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv3:
            id: dyFile3
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile3
            hashStart: 10924
            hashEnd: 16385
            restUrl: http://127.0.0.1:${pipIrr.file3.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file3.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv4:
            id: dyFile4
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile4
            hashStart: 16386
            hashEnd: 21847
            restUrl: http://127.0.0.1:${pipIrr.file4.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file4.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv5:
            id: dyFile5
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile5
            hashStart: 21848
            hashEnd: 27309
            restUrl: http://127.0.0.1:${pipIrr.file5.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file5.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv6:
            id: dyFile6
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile6
            hashStart: 27310
            hashEnd: 32767
            restUrl: http://127.0.0.1:${pipIrr.file6.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file6.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv7:
            id: dyFile7
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile7
            hashStart: 32768
            hashEnd: 38229
            restUrl: http://127.0.0.1:${pipIrr.file7.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file7.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv8:
            id: dyFile8
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile8
            hashStart: 38230
            hashEnd: 43691
            restUrl: http://127.0.0.1:${pipIrr.file8.webPort}/file
            webUrl: http://no253541tf71.vicp.fun${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file8.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv9:
            id: dyFile9
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile9
            hashStart: 43692
            hashEnd: 49153
            restUrl: http://127.0.0.1:${pipIrr.file9.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file9.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv10:
            id: dyFile10
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile10
            hashStart: 49154
            hashEnd: 54615
            restUrl: http://127.0.0.1:${pipIrr.file10.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file10.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv11:
            id: dyFile11
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile11
            hashStart: 54616
            hashEnd: 60077
            restUrl: http://127.0.0.1:${pipIrr.file11.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file11.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名
        sv12:
            id: dyFile12
            absolutePath: E:/apps/pipIrr/nginx-1.27.0/html/webfiles/
            absolutePath: ${pipIrr.nginx.root}/html/webfiles/
            relativePath: webFile12
            hashStart: 60078
            hashEnd: 65535
            restUrl: http://127.0.0.1:${pipIrr.file12.webPort}/file
            webUrl: http://no253541tf71.vicp.fun:${pipIrr.nginx.webPort}/webfiles/
            webDownloadPath: http://no253541tf71.vicp.fun:${pipIrr.file12.webPort}/file/download/down?id=
            webUrl: http://8.130.130.233:${pipIrr.nginx.webFilePort}/webfiles/ #nginx服务路径,安装部署时IP改成外网IP或域名
            webDownloadPath: http://8.130.130.233:${pipIrr.nginx.webPort}/file/download/down?id=  #安装部署时IP改成外网IP或域名

#项目编号
#projectCode:
@@ -308,8 +321,8 @@
project:
    #项目编码
    projectNo: 10
    #控制器类型
    controllerType: 57
    #控制器类型 (01（0x01）:测控一体阀；02（0x02）:表阀一体机；57(0x57):井电控制器)
    controllerType: 02
    #默认行政区划编码（天津-天津市-武清区-京滨工业园-大禹节水）
    divisionCode: 120114403100

@@ -324,28 +337,53 @@
    mj:
        url: "http://127.0.0.1:8071"
        comSendUrl: "http://127.0.0.1:8071/rtuMw/com/send"
    mq:
        url: "http://127.0.0.1:8100"
        comSendUrl: "http://127.0.0.1:8100/rtuMw/com/send"
    sp:
        url: "http://127.0.0.1:8073"
        comSendUrl: "http://127.0.0.1:8073/rtuMw/com/send"
    test:
        url: "http://127.0.0.1:8072"
        comSendUrl: "http://127.0.0.1:8072/rtuMw/com/send"
    #监测控制模块回调地址
    mq:
        url: "http://127.0.0.1:8100"
        comSendUrl: "http://127.0.0.1:8100/rtuMw/com/send"
    yq:
        url: "http://127.0.0.1:8101"
        comSendUrl: "http://127.0.0.1:8101/rtuMw/com/send"
    hlj:
        url: "http://127.0.0.1:8102"
        comSendUrl: "http://127.0.0.1:8102/rtuMw/com/send"
    gz:
        url: "http://127.0.0.1:8103"
        comSendUrl: "http://127.0.0.1:8103/rtuMw/com/send"
    lz:
        url: "http://127.0.0.1:8104"
        comSendUrl: "http://127.0.0.1:8104/rtuMw/com/send"
    jc:
        url: "http://127.0.0.1:8105"
        comSendUrl: "http://127.0.0.1:8105/rtuMw/com/send"
    jyg:
        url: "http://127.0.0.1:8106"
        comSendUrl: "http://127.0.0.1:8106/rtuMw/com/send"
    #Rtu远程命令结果回调地址
    rtuCallbackUrl_rm: "http://127.0.0.1:8081/remote/comRes/receive"
    #rtu远程升级模块回调地址
    #MQTT远程命令结果回调地址
    mqttCallbackUrl_rm: "http://127.0.0.1:8081/remote/mqttRes/receive"
    #接收通信中间件消息中心消息的地址
    mwMsCenterReceiveUrl_rm: "http://127.0.0.1:8081/remote/msCenter/receive"
    #rtu远程升级状态信息回调地址
    ugCallbackUrl_rm: "http://127.0.0.1:8081/remote/rtuUpgradeStateReceiver/receive"
    #微信小程序回调地址
    #微信小程序应用中Rtu远程命令结果回调地址
    rtuCallbackUrl_wx: "http://127.0.0.1:8087/wx/comRes/receive"
    waitMwRtnResultTimeout: 60 #等待中间件返回结果超时时间，单位秒钟

#不进行userToken过滤的URL，@ConfigurationProperties要求tokennofilter中所有字母都小写
tokennofilter:
    urls:
        - /sso/sso
        - /remote/comRes/receive
        - /remote/msCenter/receive
        - /remote/rtuUpgradeStateReceiver/receive
        - /remote/websocket
        - /wx/comRes/receive
        - /app/captcha/get
        - /file/file #web分布式文件系统
@@ -359,7 +397,7 @@
#钉钉消息推送
dingtalk:
    ym:
        enable: true #是否启用
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
@@ -367,15 +405,7 @@
        at-all: true
        mobile: 18602657034
    mj:
        enable: true #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    mq:
        enable: true #是否启用
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
@@ -383,7 +413,7 @@
        at-all: true
        mobile: 18602657034
    sp:
        enable: true #是否启用
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
@@ -391,7 +421,63 @@
        at-all: true
        mobile: 18602657034
    test:
        enable: true #是否启用
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    mq:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    yq:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    hlj:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    gz:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    lz:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    jc:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
            secret: SEC6042bc964d08899a5853eb321eb5a4d842a395982777f815bd07451c879228b7
        at-all: true
        mobile: 18602657034
    jyg:
        enable: false #是否启用
        robot:
            url: https://oapi.dingtalk.com/robot/send
            access-token: fecef8e7725998f8912af05419580861aafc73413c4920036c07c050fa33055f
@@ -399,9 +485,27 @@
        at-all: true
        mobile: 18602657034

#视频监控相关
video:
    #萤石云开放平台
    ys:
        accessTokenExpireDay: 7 #AccessToken过期时间，单位天
        appKey: 93987fd687444c23b427181a108f5253
        secret: d73a08e4fb3c3d9d0b696630eea8bd4e
        requestAccessTokenUrl: https://open.ys7.com/api/lapp/token/get
        requestVideoCommonUrl: https://open.ys7.com/console/jssdk/pc.html
        requestVideoCameraPreUrl: ezopen://open.ys7.com/
        requestVideoCameraTail4LiveUrl: /1.live
        requestVideoCameraTail4ThemeIdUrl: themeId=
    types:
        - 1,萤石 #1:萤石
        #- 2,海康 #2:海康
        #- 3,大华 #3:大华
        #- 4,天地伟业 #4:天地伟业
    screen:
        count: 4 #默认显示4个视频
#阀控器参数
rtu:
    batteryVolt: 17
    signalIntensity:
        weak: 10
        ordinary: 20
@@ -410,4 +514,4 @@
#虚拟卡配置参数
vc:
    #虚拟卡最低余额报警值
    alarmValue: 10
    alarmValue: 10