# 1 **目的** **目前**Lov**全模糊查询存在性能问题，通过数据库的数据同步到**ES,**利用**ES**的模糊搜索高性能提高查询性能。** ## 2 **架构图** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666928985445-806913a1-d46f-4ed7-8301-d406ea0c0804.png)![111.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB182F700E817BB1B7B2AE0305.png) ## 2.1 **架构说明** ### 2.1.1 **数据同步说明** **l **需要进行数据同步的微服务添加ES同步客户端依赖（**如果已依赖，可以不用依赖，版本取平台包对应的版本）** | ``` cn.ztessc.platform zfs-es-query-tool ``` | | | - | * **后台配置同步信息。详细文档参考zfs-es-query-sync服务的《ES-耗时复杂查询组件-简明开发使用手册》** * **底层拦截同步表的insert,update,delete的增删改语句，通过sql语法解析器解析拦截的sql获取相应的值进行数据同步** * **如果数据同步失败，将发送到MQ中进行异步处理。** * **定时器每天晚上进行DB与ES数据总量的比较，数据不一致时，进行全量同步。** ## 2.2 **ES同步配置** **【注意】** **同步配置请一定走界面进行配置。** ### 2.2.1 **新增一个业务的同步配置** **功能位置**：**接口平台**--ES**同步管理**--**同步配置**-**新增** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929081338-c18842cc-d272-4f30-ba42-92b09c4586ad.png)![aa.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB183DB2691759B1B7B2AE0306.png) **1**、首先：新增同步定义配置：定义**es**索引，**同名名称，**es**索引名都是自定义。** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929089374-21d0ec38-0ed9-4091-ac59-ac353dba3aa6.png)![bb.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB18401B510017B1B7B2AE0307.png) **2**、然后：配置改业务同步要同步的表 ![cc.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB186B63B566DBB1B7B2AE0309.png) ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929105687-d2398f0a-bfb2-4022-88d0-c122827aed2b.png)![dd.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB1863E7D3EB4FB1B7B2AE0308.png) ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929117050-28a41892-c91a-421b-91cf-0fcea070a28b.png)![ee.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB186E313557ACB1B7B2AE030A.png) ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929122897-ccab0175-508b-4d68-9faf-e1a729cde5aa.png) **依次配置该同步需要监控数据变化的表。** ## 1.1 关于监控表的说明 **1、**一般是多表关联查询才会出现慢的情况，也即一般需要配置该业务查询相关的多个表 **2、**主表可以有多个，如业务表、历史表的情况。【注意】这里的主表不是**DB**中的主表，而是该查询功能界面展示是以那张表的数据为行记录，则这张表是主表。**如**db**中的父子表，功能是查询的字表，关联出主表的字段进行展示，则子表是功能主表。** **3、**同步查询**SQL**的配置，建议配置为**功能主表** left join **从表。如下例所示：** ``` SELECT t1.SOURCE_SYS_CODE AS SOURCE_SYS_CODE, t1.LE_ID AS HEADER_LE_ID, t1.BOE_ABSTRACT AS BOE_ABSTRACT, t1.BP_COUNT AS BP_COUNT, t1.BOE_STATUS AS BOE_STATUS, t1.GROUP_ID AS SERVICE_GROUP_ID, t1.teamwork_status AS HEADER_TEAMWORK_STATUS, t1.ERP_SYNC_STATUS AS ERP_SYNC_STATUS, t1.BOE_DATE AS BOE_DATE, t2.ID AS VOUCHER_ID, t2.CS_APPROVAL_ID AS CS_APPROVAL_ID, t2.VOUCHER_NUM AS VOUCHER_NUM, t2.VOUCHER_DATE AS VOUCHER_DATE, t2.SOB_ID AS SOB_ID, t2.LE_ID AS LE_ID, t2.BOE_HEADER_ID AS BOE_HEADER_ID, t2.VOUCHER_TYPE AS VOUCHER_TYPE, t2.VOUCHER_AMOUNT AS VOUCHER_AMOUNT, t2.VOUCHER_STATUS AS VOUCHER_STATUS, t2.EMPLOYEE_ID AS EMPLOYEE_ID, t2.REMARK AS REMARK, t2.IMPORT_DATE AS IMPORT_DATE, t2.IMPORT_FAILED_REASON AS IMPORT_FAILED_REASON, t2.REVERSAL_NUM AS REVERSAL_NUM, t2.BOE_NO AS BOE_NO, t2.IMPORT_EMPLOYEE_ID AS IMPORT_EMPLOYEE_ID, t2.CURRENCY_CODE AS CURRENCY_CODE, t2.ARCHIVE_FLAG AS ARCHIVE_FLAG, t2.ARCHIVE_DATE AS ARCHIVE_DATE, t2.ENABLED_FLAG AS ENABLED_FLAG, t2.VALIDITY_FLAG AS VALIDITY_FLAG, t2.GROUP_ID AS GROUP_ID, t2.CREATE_BY AS CREATE_BY, t2.CREATE_DATE AS CREATE_DATE, t2.LAST_UPDATE_BY AS LAST_UPDATE_BY, t2.LAST_UPDATE_DATE AS LAST_UPDATE_DATE, t2.EXTEND_S1 AS EXTEND_S1, t2.EXTEND_S2 AS EXTEND_S2, t2.EXTEND_S3 AS EXTEND_S3, t2.EXTEND_S4 AS EXTEND_S4, t2.EXTEND_S5 AS EXTEND_S5, t2.EXTEND_S6 AS EXTEND_S6, t2.EXTEND_S7 AS EXTEND_S7, t2.EXTEND_S8 AS EXTEND_S8, t2.VOUCHER_AFFAIR_NO AS VOUCHER_AFFAIR_NO, t2.ERP_TYPE AS ERP_TYPE, t2.VOUCHER_CATEGORY AS VOUCHER_CATEGORY, t2.teamwork_status AS TEAMWORK_STATUS, t.ID AS ID, t.EXPENSE_NUM AS EXPENSE_NUM, t.ACCOUNT_ID AS ACCOUNT_ID, t.DEBIT_LENDER_CODE AS DEBIT_LENDER_CODE, t.SOURCE_AMOUNT AS SOURCE_AMOUNT, t.SOURCE_CURRENCY_CODE AS SOURCE_CURRENCY_CODE, t.RATE_VALUE AS RATE_VALUE, t.RATE_TYPE AS RATE_TYPE, t.RATE_DATE AS RATE_DATE, t.BSCHL AS BSCHL, t.UMSKZ AS UMSKZ, t.ANTI_ACCOUNT_FLAG AS ANTI_ACCOUNT_FLAG, t.UMSKZ_NAME AS UMSKZ_NAME, t.DIST_AMOUNT AS DIST_AMOUNT, t.ENABLED_FLAG AS LINES_ENABLED_FLAG, t1.ENABLED_FLAG AS BOE_ENABLED_FLAG FROM OP_VOUCHER_LINES t LEFT JOIN OP_VOUCHER_HEADERS t2 ON t.VOUCHER_ID = t2.ID LEFT JOIN ZFS_BOE_HEADER t1 ON t1.ID = t2.BOE_HEADER_ID WHERE t.ID={id} ``` **4、**从表也要配置查询**SQL**语句，因为从表的数据变化了，也会导致数据需同步（因为关联查询了它的字段）。从表的查询**SQL**语句与主表的几乎一样，只有后面的查询条件字段稍有变化，看下面的示例：【注意对比这条和上面一条的**where**条件】 ``` SELECT t1.SOURCE_SYS_CODE AS SOURCE_SYS_CODE, t1.LE_ID AS HEADER_LE_ID, t1.BOE_ABSTRACT AS BOE_ABSTRACT, t1.BP_COUNT AS BP_COUNT, t1.BOE_STATUS AS BOE_STATUS, t1.GROUP_ID AS SERVICE_GROUP_ID, t1.teamwork_status AS HEADER_TEAMWORK_STATUS, t1.ERP_SYNC_STATUS AS ERP_SYNC_STATUS, t1.BOE_DATE AS BOE_DATE, t2.ID AS VOUCHER_ID, t2.CS_APPROVAL_ID AS CS_APPROVAL_ID, t2.VOUCHER_NUM AS VOUCHER_NUM, t2.VOUCHER_DATE AS VOUCHER_DATE, t2.SOB_ID AS SOB_ID, t2.LE_ID AS LE_ID, t2.BOE_HEADER_ID AS BOE_HEADER_ID, t2.VOUCHER_TYPE AS VOUCHER_TYPE, t2.VOUCHER_AMOUNT AS VOUCHER_AMOUNT, t2.VOUCHER_STATUS AS VOUCHER_STATUS, t2.EMPLOYEE_ID AS EMPLOYEE_ID, t2.REMARK AS REMARK, t2.IMPORT_DATE AS IMPORT_DATE, t2.IMPORT_FAILED_REASON AS IMPORT_FAILED_REASON, t2.REVERSAL_NUM AS REVERSAL_NUM, t2.BOE_NO AS BOE_NO, t2.IMPORT_EMPLOYEE_ID AS IMPORT_EMPLOYEE_ID, t2.CURRENCY_CODE AS CURRENCY_CODE, t2.ARCHIVE_FLAG AS ARCHIVE_FLAG, t2.ARCHIVE_DATE AS ARCHIVE_DATE, t2.ENABLED_FLAG AS ENABLED_FLAG, t2.VALIDITY_FLAG AS VALIDITY_FLAG, t2.GROUP_ID AS GROUP_ID, t2.CREATE_BY AS CREATE_BY, t2.CREATE_DATE AS CREATE_DATE, t2.LAST_UPDATE_BY AS LAST_UPDATE_BY, t2.LAST_UPDATE_DATE AS LAST_UPDATE_DATE, t2.EXTEND_S1 AS EXTEND_S1, t2.EXTEND_S2 AS EXTEND_S2, t2.EXTEND_S3 AS EXTEND_S3, t2.EXTEND_S4 AS EXTEND_S4, t2.EXTEND_S5 AS EXTEND_S5, t2.EXTEND_S6 AS EXTEND_S6, t2.EXTEND_S7 AS EXTEND_S7, t2.EXTEND_S8 AS EXTEND_S8, t2.VOUCHER_AFFAIR_NO AS VOUCHER_AFFAIR_NO, t2.ERP_TYPE AS ERP_TYPE, t2.VOUCHER_CATEGORY AS VOUCHER_CATEGORY, t2.teamwork_status AS TEAMWORK_STATUS, t.ID AS ID, t.EXPENSE_NUM AS EXPENSE_NUM, t.ACCOUNT_ID AS ACCOUNT_ID, t.DEBIT_LENDER_CODE AS DEBIT_LENDER_CODE, t.SOURCE_AMOUNT AS SOURCE_AMOUNT, t.SOURCE_CURRENCY_CODE AS SOURCE_CURRENCY_CODE, t.RATE_VALUE AS RATE_VALUE, t.RATE_TYPE AS RATE_TYPE, t.RATE_DATE AS RATE_DATE, t.BSCHL AS BSCHL, t.UMSKZ AS UMSKZ, t.ANTI_ACCOUNT_FLAG AS ANTI_ACCOUNT_FLAG, t.UMSKZ_NAME AS UMSKZ_NAME, t.DIST_AMOUNT AS DIST_AMOUNT, t.ENABLED_FLAG AS LINES_ENABLED_FLAG, t1.ENABLED_FLAG AS BOE_ENABLED_FLAG FROM OP_VOUCHER_LINES t LEFT JOIN OP_VOUCHER_HEADERS t2 ON t.VOUCHER_ID = t2.ID LEFT JOIN ZFS_BOE_HEADER t1 ON t1.ID = t2.BOE_HEADER_ID WHERE t.VOUCHER_ID={id} ``` ## 1.2 **修改同步配置** ### 1.2.1 **新增字段** **当发现当前同步的字段不满足需要**，需要增加字段时，在同步配置界面编辑修改同步的**SQL**语句，保存即可。组件会自动在**es**索引上添加字段。 ### 1.2.2 **修改字段** **变字段名等同于新增字段。** ### 1.2.3 **删除字段** **减少了同步**SQL**中的字段，**es**中不会删除减少的字段，只是同步的数据中没有这些字段的值。** **如要删除索引中这些字段，可根据需要执行全量同步。** ## 2.3 **全量同步** **新增好同步配置后，或修改了同步配置需要进行全量数据重新同步到**es**时，在同步配置界面执行全量同步：**【注意】在晚上少人使用时才进行大量数据的全量同步。 ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929458656-0750f582-a068-4954-aa94-4a07ee6ad5f0.png)![gg.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB187513F68629B1B7B2AE030B.png) **同步的结果查看：**在异常信息**菜单下可以看全量、实时同步的结果信息、异常信息，** **点“处理登记”，可以看结果，看异常信息。** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666929468294-54147b36-db89-4137-9d93-14acdcf9552e.png)![hh.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB187739446CF4B1B7B2AE030C.png) ## 2.4 **Lov数据查询说明** ### 2.4.1 **Lov基于数据库查询配置**(原逻辑) #### 2.4.1.1 **原数据库查询eas.Lovitems.xml配置** ``` { singleGrid: 'Y', autoQuery: 'Y', query: [ { code: 'CODE', label: '核算主体编码', type: 'text', }, { code: 'NAME', label: '核算主体名称', type: 'text', }, { code: 'SOB_ID', label: '套账ID', type: 'text', show:false, }], field: [ { code: 'ID', label: '核算主体ID', show: false, }, { code: 'CODE', label: '核算主体编码', }, { code: 'NAME', label: '核算主体名称', } ] } ``` #### 2.4.1.2 **原数据库查询eas.Sqlconfig.xml配置** ``` ``` ### 2.4.2 **Lov基于ES查询配置** #### 2.4.2.1 **ES查询的eas.Lovitems.xml配置** ``` { singleGrid: 'Y', autoQuery: 'Y', < !--是否基于ES查询--> isEsQuery: 'Y', < !--ES的索引--> indexName: 'sys_fu_cc_mode_new', query: [ { code: 'CODE', label: '核算主体编码', type: 'text', }, { code: 'NAME', label: '核算主体名称', type: 'text', }, { code: 'SOB_ID', label: '套账ID', type: 'text', show:false, }], < !--Lov的SQL配置的固定条件，对应原Lov中SQL的条件，注意t1.enabled_flag = '0'and t1.validity_flag = '0'--> queryEs:[ { code: 'enabledFlag', label: '是否可见', show: false, column: 'enabled_flag', conditionType: '=', value: '0' }, { code: 'validityFlag', label: '是否有效', show: false, column: 'validity_flag', conditionType: '=', value: '0' } ], < !--Lov的固定排序，对应原Lov中SQL的固定排序order by t1.id desc --> sort:[{ { column:'id', order:'desc' } ], field: [ { code: 'ID', label: '核算主体ID', show: false, }, { code: 'CODE', label: '核算主体编码', }, { code: 'NAME', label: '核算主体名称', } ] } ``` #### 2.4.2.2 **ES后台同步配置的SQL** **主表配置的**SQL**：** ``` SELECT sfll.id,sfl.id as auth_id,sfl.CODE,sfll.NAME,sfl.ENABLED_FLAG,sfl.VALIDITY_FLAG,sfl.fu_type,sfl.GROUP_ID,sfll.LANGUAGE FROM SYS_FU_LE_CC_LANGUAGE sfll LEFT JOIN SYS_FU_LE_CC sfl on sfl.ID = sfll.LE_ID where sfll.id={id} ``` 1. ** ES保存的完整的全量数据，所以同步不允许在where后台带固定条件和排序，并且必须加上所有的展示字段和条件过滤字段，在ES进行查询时，可以进行条件过滤。** 2. **如果存在一对多的关联关系时，必须多的一方作为主表，否则会由于id相同会把数据覆盖掉。** 3. **如果存在需要数据权限过滤时，必须把数据权限的字段也必须加上，如果数据权限也是根据除主表以外的id字段进行过滤时，id需要取别名为auth_id（固定写法）,如上面的sql中auth_id。** **从表配置的**SQL**：** ``` SELECT sfll.id,sfl.id as auth_id,sfl.CODE,sfll.NAME,sfl.ENABLED_FLAG,sfl.VALIDITY_FLAG,sfl.fu_type,sfl.GROUP_ID,sfll.LANGUAGE FROM SYS_FU_LE_CC_LANGUAGE sfll LEFT JOIN SYS_FU_LE_CC sfl on sfl.ID = sfll.LE_ID where sfl.id={id} ``` ### 2.4.3 **添加同步开关配置** **在**es.yml**中添加同步开关：** ``` busi: es: syncenable: true ``` ### 2.4.4 **事务** **涉及到增删改操作时，不管是单表操作还是多表操作，一定要加spring事务注解@Transactional(rollbackFor = Exception.class)。否则会同步失败，因为底层是基于spring的事务监听进行数据同步的。** ### 2.4.5 **定时器** **每天晚上会通过定时器比较数据库的数量与ES的总数是否一致，如果不一致，会进行全量同步。** #### 2.4.5.1 **在全局编码的SOURCESERVICE创建一个子编码zfs-es-query-sync** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666935149649-5a052411-6194-473d-a2c8-eaeb78d07cbe.png)![ii.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB187BAEFCDD4DB1B7B2AE030D.png) #### 2.4.5.2 **在全局编码的PLATFORM创建一个子编码esSync** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666935140794-389b0204-e747-400a-b90c-75a7e43497e2.png)![jj.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB187D5E59EB7EB1B7B2AE030E.png) #### 2.4.5.3 **定时器界面** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666935131797-b4d93020-01a9-4d3c-9141-a52a9825c02d.png)![kk.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB187F472B6D42B1B7B2AE030F.png) #### 2.4.5.4 **定时器脚本** **oracle**： ``` zfs_sys.base_init_data.INIT_JOB ('GP00zfs_es_query_sync0010', 'esSyncTask', ' 0 35 23 * * ? *', 'comparativeDataEsAndDbJob', 'zfs-es-query-sync', 'DB与ES数据同步定时器', 'N', '', 'GP00' ); ``` **Mysql**： ``` zfs_sys.init_job('GP00zfs_es_query_sync0010', 'esSyncTask', ' 0 35 23 * * ? *', 'comparativeDataEsAndDbJob', 'zfs-es-query-sync', 'DB与ES数据同步定时器', 'N', '', 'GP00' ); ``` **DM**： ``` zfs_sys.base_init_data.INIT_JOB ('GP00zfs_es_query_sync0010', 'esSyncTask', ' 0 35 23 * * ? *', 'comparativeDataEsAndDbJob', 'zfs-es-query-sync', 'DB与ES数据同步定时器', 'N', '', 'GP00' ); ``` ### 2.4.6 **ES同步管理菜单配置** #### 2.4.6.1 **菜单界面** ![](https://cdn.nlark.com/yuque/0/2022/png/21423066/1666934917226-6957f4e1-2245-42c5-9478-e5493cde2890.png)![ll.png](http://tmpbbs.ztc.ztccloud.com.cn:6875/developers-server/rest/file/group1/2023/02/26/A04ABFCB18823016A2E8B1B7B2AE0310.png) **2.4.6.2 **菜单脚本 **Oracle**： ``` zfs_sys.base_data_imp.insert_menu('GP00','essyncconf_1000001','ZTESSC-C001-GP00_ip_070024','ES同步管理','Es synchronization management',1,'menu-reim','ip',1,'/ip/index','','0'); zfs_sys.base_data_imp.insert_menu('GP00','essyncconf_1000002','essyncconf_1000001','同步配置','Synchronization configuration',1,'menu-reim','ip',2,'confManage','','0'); zfs_sys.base_data_imp.insert_menu('GP00','essyncconf_1000003','essyncconf_1000001','异常信息','Abnormal information',1,'menu-reim','ip',3,'confManage','','0'); ``` **MySQL**： ``` zfs_sys.insert_menu('GP00','essyncconf_1000001','ZTESSC-C001-GP00_ip_070024','ES同步管理','Es synchronization management',1,'menu-reim','ip',1,'/ip/index','','0'); zfs_sys.insert_menu('GP00','essyncconf_1000002','essyncconf_1000001','同步配置','Synchronization configuration',1,'menu-reim','ip',2,'confManage','','0'); zfs_sys.insert_menu('GP00','essyncconf_1000003','essyncconf_1000001','异常信息','Abnormal information',1,'menu-reim','ip',3,'confManage','','0'); ``` **DM**： ``` zfs_sys.base_data_imp.insert_menu('GP00','essyncconf_1000001','ZTESSC-C001-GP00_ip_070024','ES同步管理','Es synchronization management',1,'menu-reim','ip',1,'/ip/index','','0'); zfs_sys.base_data_imp.insert_menu('GP00','essyncconf_1000002','essyncconf_1000001','同步配置','Synchronization configuration',1,'menu-reim','ip',2,'confManage','','0'); zfs_sys.base_data_imp.insert_menu('GP00','essyncconf_1000003','essyncconf_1000001','异常信息','Abnormal information',1,'menu-reim','ip',3,'confManage','','0'); ```

Lov支持ES查询简明使用手册

关联知识库

Lov支持ES查询简明使用手册

关于作者

关联知识库

目录