Intelligent database and table division method, system and device based on ShardingSphere and storage medium

Abstract

The invention relates to the technical field of electric digital data processing, in particular to a ShardingSphere-based intelligent database and table division method, which comprises the following steps of: responding to remote service access, and executing service logic activation; responding to a frame system of reflection and MyBatis, and obtaining a to-be-accessed object set; the method comprises the following steps: generating ShardingSphere sub-library and sub-table configuration for an object set to be accessed on the basis of a dynamic configuration rule; and responding to the service logic access, and based on the dynamic acquisition rule, the ShardingSphere performs routing on the sql into different target objects. According to the method, manual operation in the prior art can be distinguished, the rule is generated according to the service dynamic configuration rule, and the error probability is reduced to the maximum extent; and meanwhile, when the medical service is developed, the sub-library and sub-table rules can be automatically generated for automatic distribution, system crash caused by adding a new rule does not need to be worried about, and later upgrading of the system is also facilitated.

Description

Technical Field

[0001] The present application relates to the field of electronic digital data processing technology, and in particular to a method for intelligent database and table partitioning based on ShardingSphere, and also to a system, device and storage medium. Background Art

[0002] If the amount of data in the medical business system is too large, the database needs to be divided into different databases and tables according to the business volume. For example, ShardingShpere can be used for database and table division. The existing solutions all manually specify how to divide each table into different databases and tables, which is complicated to configure and cannot dynamically adjust the database and table division configuration according to the normal business.

[0003] Based on this, it is particularly important if the newly added business table can also automatically generate the sub-library and table rules for automatic allocation without manually configuring the sub-library and table rules for each table. Summary of the invention

[0004] Through research, the inventors found that the disadvantage of traditional sub-library and sub-table configuration is that the configuration can only be maintained manually and is prone to errors. Once the sub-library and sub-table configuration is maintained incorrectly, it will cause the system to crash. At the same time, when the system is upgraded, the sub-library and sub-table are no longer in use, and the configuration file must be manually updated, otherwise the system will crash.

[0005] The purpose of this application is to provide a method for intelligent database and table sharding based on ShardingSphere, which generates rules according to business dynamic configuration rules to solve the technical problem that the existing technology cannot provide a method for automatically configuring database and table sharding rules and performing automatic allocation.

[0006] According to one aspect of the present application, a method for intelligent database and table partitioning based on ShardingSphere is provided, and the method is executed by a processor, including:

[0007] Respond to remote business access and execute business logic activation;

[0008] Respond to reflection and MyBatis framework system to obtain the collection of objects to be accessed;

[0009] For the set of objects to be accessed, generate ShardingSphere sub-library and sub-table configuration based on dynamic configuration rules;

[0010] In response to business logic access, ShardingSphere routes SQL execution to different target objects based on dynamic acquisition rules.

[0011] In some embodiments, the set of objects to be accessed is at least Schema information of a table.

[0012] In some embodiments, the dynamic configuration rule is at least:

[0013] Based on the tenant and department fields, if both tenant and department fields exist, the database table is divided into different data after executing the hash algorithm according to the tenant, and the remainder is divided into different tables after executing the hash algorithm according to the department;

[0014] If only tenants exist, the database will be divided according to the tenants, and the remainder will be taken after the hash algorithm is executed. The data will be distributed to different databases according to the tenant fields, and the remainder will be taken after the hash algorithm is executed.

[0015] If none of them exist, a dictionary table is created and all data have the same data.

[0016] According to another aspect of the present application, a ShardingSphere-based intelligent sub-library and sub-table system is provided, including:

[0017] A service access module, the service access module is used to respond to remote service access and execute service logic activation;

[0018] An acquisition module, which is used to respond to reflection and the MyBatis framework system to acquire a set of objects to be accessed;

[0019] A generation module, which is used to generate ShardingSphere sub-library and sub-table configurations for the set of objects to be accessed based on dynamic configuration rules;

[0020] Execution module, which is used to respond to business logic access. Based on dynamic acquisition rules, ShardingSphere routes SQL execution to different target objects.

[0021] According to another aspect of the present application, there is provided a device for intelligent sub-library and sub-table distribution based on ShardingSphere, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the method described above is implemented when the processor executes the computer program.

[0022] According to another aspect of the present application, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method as described above is implemented.

[0023] Compared with the prior art, this application has the following advantages and beneficial effects:

[0024] The method of the present application can be distinguished from the manual work of the prior art, and can generate rules based on dynamic business configuration rules, thereby minimizing the probability of errors. At the same time, the method of the present application can automatically generate sub-library and sub-table rules for automatic allocation when the medical business develops, without worrying about the need to add new rules causing the system to crash, and it is also convenient for later system upgrades. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0026] Figure 1 is a method flow chart of the present application;

[0027] Figure 2 It is a system configuration diagram of this application;

[0028] Figure 3 It is a schematic diagram of the method of this application. DETAILED DESCRIPTION

[0029] The following will be combined with the attached examples of the present application Figure 1-3 The technical solutions in the embodiments of the present application are clearly and completely described together. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments.

[0030] For easier understanding, some terms are explained:

[0031] 1. MyBatis: It is an open source Java persistence layer framework, mainly used to simplify database operations;

[0032] 2.Schema: It is called mode in Chinese. It is the organization and structure of the database. The mode includes tables, columns, data types, views, stored procedures, relationship primary keys, foreign keys, etc.

[0033] 3.SQL: It is a special purpose programming language, a database query and programming language used to access data and query, update and manage relational database systems.

[0034] Application Overview

[0035] With the advent of the big data era, the number of Internet images, files, and videos has also grown at an explosive rate. File processing has become a huge challenge for various business systems. In traditional Web applications, all functions and files are on the same server. When the number of files reaches a certain level, there are a series of problems such as serious degradation of access performance, difficulty in expanding file storage space, and difficulty in sharing files between systems.

[0036] ShardingSphere is a relational database middleware that aims to fully and reasonably utilize the computing and storage capabilities of relational databases in distributed scenarios. By splitting large amounts of data and storing them in different databases and tables, it reduces the data pressure of a single database and table, thereby improving the performance of data operations.

[0037] Therefore, based on the technical advantages of ShardingSphere and combined with dynamic configuration rules, this application proposes a method of intelligent sub-library and sub-table based on ShardingSphere, which effectively solves the problems of complex configuration when medical data grows, and the inability to reasonably adjust the sub-library and sub-table configuration according to the normal needs of medical business.

[0038] Exemplary Methods

[0039] The present application provides a method for intelligent database and table partitioning based on ShardingSphere, which is executed by a processor. The processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components, etc.; the general-purpose processor can be a microprocessor or the processor can also be any conventional processor, etc.

[0040] The specific method includes: Figure 1 and 3 :

[0041] Respond to remote business access and execute business logic activation;

[0042] Respond to reflection and MyBatis framework system to obtain the collection of objects to be accessed;

[0043] For the set of objects to be accessed, ShardingSphere sub-library and sub-table configuration is generated based on dynamic configuration rules, that is, according to the Schema fields of all tables and the dynamic configuration rules, ShardingSphere sub-library and sub-table configuration is generated;

[0044] In response to business logic access, ShardingSphere routes SQL execution to different target objects based on dynamic acquisition rules. That is, when business logic is accessed, ShardingSphere will perform routing tables according to dynamic acquisition rules.

[0045] In some possible implementations, the set of objects to be accessed is at least the Schema information of the table, that is, the Schema of the table in the system is obtained through reflection and the mybatis framework.

[0046] In some possible implementations, the dynamic configuration rules are at least: based on the tenant and department fields, if both tenant and department fields exist, the database table is distributed to different data after executing the hash algorithm according to the tenant, and distributed to different tables after executing the hash algorithm according to the department; if only tenants exist, only the database is divided according to the tenant, and the data is distributed to different databases after executing the hash algorithm according to the tenant field; if there is none, a dictionary table is established, and all data have the same data. It should be noted that the provided extension can specify at least a single table: the specified table is not divided into a library or table in the specified database; broadcast table: the specified table has the same data in each database, without being divided into tables.

[0047] In order to better understand the present application, the present application also provides a ShardingSphere intelligent sub-library and sub-table system. Figure 2 , the system is divided into one or more modules, one or more modules / units are stored in the memory and executed by the processor to complete the present application. One or more modules can be a series of computer program instruction segments that can complete specific functions, and the instruction segments are used to describe the execution process of the computer program. For example, the computer program can be divided into a business access module, an acquisition module, a generation module and an execution module. The specific functions of each module are as follows: the business access module is used to respond to remote business access and execute business logic activation; the acquisition module is used to respond to reflection and MyBatis framework system to obtain the set of objects to be accessed; the generation module is used to generate ShardingSphere sub-library and sub-table configuration for the set of objects to be accessed based on dynamic configuration rules; the execution module is used to respond to business logic access, and based on dynamic acquisition rules, ShardingSphere executes SQL routing to different target objects.

[0048] According to another aspect of the present application, there is provided a device for intelligent sub-library and sub-table distribution based on ShardingSphere, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the above method is implemented.

[0049] According to another aspect of the present application, a computer-readable storage medium is provided, which stores a computer program, and when the computer program is executed by a processor, it implements any of the above methods. When the module based on the integration of the ShardingSphere intelligent sub-library and sub-table system is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this, the present application implements all or part of the process in the above method, and can also be completed by instructing related hardware through a computer program. The computer program can be stored in a computer-readable storage medium, and when the computer program is executed by a processor, the steps of each of the above methods can be implemented.

[0050] Among them, the computer program includes computer program code, which can be in source code form, object code form, executable file or some intermediate form, etc. Computer-readable storage media may include: any entity or device capable of carrying the computer program code, recording medium, USB flash drive, mobile hard disk, magnetic disk, optical disk, computer memory, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), electric carrier signal, telecommunication signal and software distribution medium, etc. It should be noted that the content contained in the computer-readable medium can be appropriately increased or decreased according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electric carrier signals and telecommunication signals.

[0051] The above shows and describes the basic principles and main features of the present application and the advantages of the present application. For those skilled in the art, it is obvious that the present application is not limited to the details of the above exemplary embodiments, and the present application can be implemented in other specific forms without departing from the spirit or basic features of the present application. Therefore, no matter from which point of view, the embodiments should be regarded as exemplary and non-restrictive. The scope of the present application is defined by the attached claims rather than the above description, and it is intended that all changes falling within the meaning and scope of the equivalent elements of the claims are included in the present application. Any figure mark in the claims should not be regarded as limiting the claims involved.

[0052] In addition, it should be understood that although the present specification is described according to implementation modes, not every implementation mode contains only one independent technical solution. This description of the specification is only for the sake of clarity. Those skilled in the art should regard the specification as a whole. The technical solutions in each embodiment may also be appropriately combined to form other implementation modes that can be understood by those skilled in the art.

Claims

1. A method for intelligent database and table partitioning based on ShardingSphere, which is executed by a processor and is characterized in that: include: Respond to remote business access and execute business logic activation; Respond to reflection and MyBatis framework system to obtain the collection of objects to be accessed; For the set of objects to be accessed, generate ShardingSphere sub-library and sub-table configuration based on dynamic configuration rules; In response to business logic access, ShardingSphere routes SQL execution to different target objects based on dynamic acquisition rules.

2. The method according to claim 1, characterized in that The object set to be accessed is at least Schema information of the table.

3. The method according to claim 2, characterized in that The dynamic configuration rules are at least: Based on the tenant and department fields, if both tenant and department fields exist, the database table is divided into different data after executing the hash algorithm according to the tenant, and the remainder is divided into different tables after executing the hash algorithm according to the department; If only tenants exist, the database will be divided according to the tenants, and the remainder will be taken after the hash algorithm is executed. The data will be distributed to different databases according to the tenant fields, and the remainder will be taken after the hash algorithm is executed. If none of them exist, a dictionary table is created and all data have the same data.

4. An intelligent database and table sharding system based on ShardingSphere, characterized in that: include A service access module, the service access module is used to respond to remote service access and execute service logic activation; An acquisition module, which is used to respond to reflection and the MyBatis framework system to acquire a set of objects to be accessed; A generation module, which is used to generate ShardingSphere sub-library and sub-table configurations for the set of objects to be accessed based on dynamic configuration rules; Execution module, which is used to respond to business logic access. Based on dynamic acquisition rules, ShardingSphere routes SQL execution to different target objects.

5. A ShardingSphere-based intelligent database and table sharding device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method according to claim 1 is implemented.

6. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method according to claim 1 is implemented.

Images