CN111881145A - Method and device for processing service data table, server and storage medium - Google Patents
Method and device for processing service data table, server and storage medium Download PDFInfo
- Publication number
- CN111881145A CN111881145A CN202010764935.2A CN202010764935A CN111881145A CN 111881145 A CN111881145 A CN 111881145A CN 202010764935 A CN202010764935 A CN 202010764935A CN 111881145 A CN111881145 A CN 111881145A
- Authority
- CN
- China
- Prior art keywords
- data table
- optimized
- business
- service
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
Abstract
The invention provides a method and a device for processing a service data table, a server and a storage medium, and relates to the technical field of data processing. The method comprises the following steps: counting historical calling information of a plurality of business constraint codes in a business data table, wherein each business constraint code corresponds to one identification group in the business data table; the service data table comprises: a master table and at least one slave table; determining a business constraint code to be optimized from a plurality of business constraint codes according to historical calling information; determining a field to be optimized in a business data table from an identification group corresponding to a business constraint code to be optimized; and establishing a non-unique index of the service data table according to the field to be optimized. The fields to be optimized of the business data table are determined, the indexes of the business data table are established according to the fields to be optimized, the data to be inquired can be inquired through the indexes to reduce the inquiry range, and then the inquiry is carried out according to other identification fields, so that the inquiry quantity is reduced, and the efficiency of searching the repetitive data for the master-slave table is improved.
Description
Technical Field
The present invention relates to the field of data processing technologies, and in particular, to a method and an apparatus for processing a service data table, a server, and a storage medium.
Background
The data of the slave table is dependent on the master table, and the master table and the slave table are generally subjected to associated query when the data is queried. The master table can be used for storing main information, the slave table is used for storing client extension information, and the content for determining whether repeatability exists in the master table and the slave table is more and more important when data in the master table and the slave table is inserted and modified.
In the related technology, data to be inquired is determined, a first data group of the data to be inquired is data to be inserted or modified, all data in a main table and all data in a secondary table are correspondingly traversed, and whether target data which are repeated with the data to be inquired exist in a database or not is searched.
However, in the related art, the target data can be determined only by traversing the master table and each slave table, the searched data is more, and the efficiency of searching the repetitive data for the master table and the slave table is reduced.
Disclosure of Invention
The present invention aims to provide a method, an apparatus, a server and a storage medium for processing a service data table, so as to determine target data only by traversing a master table and each slave table in the related art, and reduce the problem of low efficiency when searching repetitive data for the master table and the slave table.
In order to achieve the above purpose, the embodiment of the present invention adopts the following technical solutions:
in a first aspect, an embodiment of the present invention provides a method for processing a service data table, where the method includes:
counting historical calling information of a plurality of service constraint codes in a service data table, wherein each service constraint code corresponds to one identification group in the service data table; the service data table comprises: a master table and at least one slave table, said one identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table;
determining a business constraint code to be optimized from a plurality of business constraint codes according to the historical calling information;
determining a field to be optimized in the service data table from the identification group corresponding to the service constraint code to be optimized;
and establishing a non-unique index of the service data table according to the field to be optimized.
Further, the history calling information includes: the number of calls and the total call time; the historical calling information of a plurality of service constraint codes in the statistical service data table comprises:
counting the calling times and the total calling time of each business constraint code in the business data table within a preset time period;
determining a business constraint code to be optimized from a plurality of business constraint codes according to the historical calling information, wherein the determining comprises the following steps:
determining the average calling time consumption of each business constraint code according to the calling times and the total calling time consumption;
and determining the business constraint code to be optimized from the plurality of business constraint codes according to the average calling time consumption of the plurality of business constraint codes.
Further, the determining the to-be-optimized business constraint code from the plurality of business constraint codes according to the average call time consumption includes:
displaying the average calling time of a plurality of business constraint codes;
receiving the selection operation of the business constraint code to be optimized input by a user according to the average calling time consumption of a plurality of business constraint codes;
and determining the business constraint code to be optimized according to the selection operation.
Further, the determining a field to be optimized in the service data table from the identifier group corresponding to the service constraint code to be optimized includes:
determining the hash degree of each identification field in the identification group corresponding to the service constraint code to be optimized in the service data table;
and determining the identification field with the maximum hash degree as the field to be optimized from the identification group corresponding to the service constraint code to be optimized according to the hash degree of each identification field.
Further, the method further comprises:
obtaining a newly inserted data record, the newly inserted data record comprising: data of each field in the service data table;
taking the data of the field to be optimized in the newly inserted data record as an initial limiting condition, and reducing the verification range of the business data table;
counting the number of the data which are uniquely checked in the reduced range in the service data table;
and if the number of the uniquely verified data is more than 1, deleting the newly inserted data record in the service data table.
Further, the reducing the check range of the service data table by using the data corresponding to the field to be optimized in the newly inserted data record as an initial limiting condition includes:
taking data corresponding to the field to be optimized as an initial limiting condition, and inquiring a first data table, wherein the first data table is a data table in which the field to be optimized is located in the service data table;
if the first data table has data corresponding to the field to be optimized, determining that a data record corresponding to the data corresponding to the field to be optimized in a second data table is a data record to be checked, wherein the second data table is a data table where other fields are located, and the other fields are fields except for the field to be optimized in an identification group corresponding to the business constraint code to be optimized in the business data table;
inquiring the data record to be checked according to the data corresponding to the other fields in the newly inserted data record; and the reduced range is the data record to be verified in the second data table.
Further, the method further comprises:
calculating a hash code to be verified according to the data of the identification group corresponding to the business constraint code to be optimized in the newly inserted data record;
inquiring the service data table according to the hash code to be verified;
and if the hash code identical to the hash code to be verified exists in the service data table, deleting the newly inserted data record in the service data table.
In a second aspect, an embodiment of the present invention further provides a device for processing a service data table, where the device includes:
the statistical module is used for counting historical calling information of a plurality of business constraint codes in a business data table, and each business constraint code corresponds to one identification group in the business data table; the service data table comprises: a master table and at least one slave table, said one identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table;
the first determining module is used for determining a business constraint code to be optimized from a plurality of business constraint codes according to the historical calling information;
a second determining module, configured to determine a field to be optimized in the service data table from the identifier group corresponding to the service constraint code to be optimized;
and the establishing module is used for establishing the non-unique index of the service data table according to the field to be optimized.
Further, the history calling information includes: the number of calls and the total call time;
the statistical module is further configured to count the number of calls and the total call time of each service constraint code in the service data table within a preset time period;
the first determining module is further configured to determine an average call time of each service constraint code according to the call times and the total call time; and determining the business constraint code to be optimized from the plurality of business constraint codes according to the average calling time consumption of the plurality of business constraint codes.
Further, the first determining module is further configured to show an average call time of the plurality of business constraint codes; receiving the selection operation of the business constraint code to be optimized input by a user according to the average calling time consumption of a plurality of business constraint codes; and determining the business constraint code to be optimized according to the selection operation.
Further, the second determining module is further configured to determine a hash degree of each identification field in the identification group corresponding to the to-be-optimized service constraint code in the service data table; and determining the identification field with the maximum hash degree as the field to be optimized from the identification group corresponding to the service constraint code to be optimized according to the hash degree of each identification field.
Further, the apparatus further comprises:
an obtaining module configured to obtain a newly inserted data record, where the newly inserted data record includes: data of each field in the service data table;
the narrowing module is used for narrowing the verification range of the business data table by taking the data of the field to be optimized in the newly inserted data record as an initial limiting condition;
the first statistical module is used for counting the number of the uniquely verified data in the reduced range in the service data table;
and the first deleting module is used for deleting the newly inserted data record in the service data table if the number of the uniquely verified data is greater than 1.
Further, the narrowing module is further configured to query a first data table by using the data corresponding to the field to be optimized as an initial limiting condition, where the first data table is a data table in which the field to be optimized is located in the service data table; if the first data table has data corresponding to the field to be optimized, determining that a data record corresponding to the data corresponding to the field to be optimized in a second data table is a data record to be checked, wherein the second data table is a data table where other fields are located, and the other fields are fields except for the field to be optimized in an identification group corresponding to the business constraint code to be optimized in the business data table; inquiring the data record to be checked according to the data corresponding to the other fields in the newly inserted data record; and the reduced range is the data record to be verified in the second data table.
Further, the apparatus further comprises:
the calculation module is used for calculating the hash code to be verified according to the data of the identification group corresponding to the business constraint code to be optimized in the newly inserted data record;
the query module is used for querying the service data table according to the hash code to be verified;
and the second deleting module is used for deleting the newly inserted data record in the service data table if the hash code which is the same as the hash code to be verified exists in the service data table.
In a third aspect, an embodiment of the present invention further provides a server, including: a memory storing a computer program executable by the processor, and a processor implementing the method of any of the first aspects when executing the computer program.
In a fourth aspect, an embodiment of the present invention further provides a storage medium, where a computer program is stored on the storage medium, and when the computer program is read and executed, the method of any one of the above first aspects is implemented.
The invention has the beneficial effects that: the embodiment of the invention provides a method for processing a service data table, which comprises the steps of counting historical calling information of a plurality of service constraint codes in the service data table, wherein each service constraint code corresponds to an identification group in the service data table; the service data table comprises: a master table and at least one slave table, an identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table; determining a business constraint code to be optimized from a plurality of business constraint codes according to historical calling information; determining a field to be optimized in a business data table from an identification group corresponding to a business constraint code to be optimized; and establishing a non-unique index of the service data table according to the field to be optimized. The fields to be optimized of the business data table are determined, the indexes of the business data table are established according to the fields to be optimized, the data to be inquired can be inquired through the indexes to reduce the inquiry range, and then the inquiry is carried out according to other identification fields, so that the inquiry quantity is reduced, and the efficiency of searching the repetitive data for the master-slave table is improved.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that are required to be used in the embodiments will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present invention and therefore should not be considered as limiting the scope, and for those skilled in the art, other related drawings can be obtained according to the drawings without inventive efforts.
Fig. 1 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 2 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 3 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 4 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 5 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 6 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 7 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention;
fig. 8 is a schematic structural diagram of a processing apparatus for a service data table according to an embodiment of the present invention;
fig. 9 is a schematic structural diagram of a server according to an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, but not all, embodiments of the present invention.
In the method for processing the service data table provided by the embodiment of the invention, the execution main body can be a server.
Fig. 1 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention; as shown in fig. 1, the method may include:
s101, counting historical calling information of a plurality of business constraint codes in a business data table, wherein each business constraint code corresponds to one identification group in the business data table.
Wherein, the service data table includes: a master table and at least one slave table, an identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table.
In addition, the historical invocation information of each business constraint code may be: and at least one user inquires the business data table according to the business constraint code, and the time consumption information is obtained. The server can count the times and time consumption information of each business constraint code in the business data table to obtain historical calling information of a plurality of business constraint codes.
Of course, the server may also count historical calling information of a plurality of service constraint codes of a plurality of service data tables, and in the embodiment of the present invention, the number of the service data tables and the number of the service constraint codes corresponding to each service data table are not specifically limited.
S102, determining a business constraint code to be optimized from a plurality of business constraint codes according to historical calling information.
In some embodiments, the server may determine the service constraint code to be optimized from the plurality of service constraint codes according to the historical calling information of the service data table by using a preset determination rule; the server may also determine the service constraint code to be optimized from the plurality of service constraint codes based on the selection operation of the user, which is not specifically limited in the embodiment of the present invention.
The selection operation by the user may be a selection operation performed by the user based on the history calling information.
S103, determining a field to be optimized in the service data table from the identification group corresponding to the service constraint code to be optimized.
Wherein, the number of the fields to be optimized can be one.
In a possible implementation manner, for a plurality of identification fields corresponding to the service constraint code to be optimized, the server may adopt a preset algorithm to perform statistical processing on the plurality of identification fields respectively to obtain a processing result, and the server may determine the field to be optimized from the service constraint code to be optimized in the service data table according to the processing result.
And S104, establishing a non-unique index of the service data table according to the field to be optimized.
The non-unique index is an index type, and when the index type is the non-unique index, the data corresponding to the field to be optimized can be the same.
In the embodiment of the invention, the field to be optimized is used as the index of the business data table, for the data to be inquired, the server can inquire the data corresponding to the field to be optimized in the business data table according to the data corresponding to the field to be optimized in the data to be inquired, reduce the inquiry range, and then inquire the data corresponding to other identification fields in the business data table according to the data corresponding to other identification fields in the data to be inquired, so as to realize the inquiry of the repetitive data in the master-slave table.
In summary, the embodiment of the present invention provides a method for processing a service data table, which includes counting historical call information of a plurality of service constraint codes in the service data table, where each service constraint code corresponds to an identifier group in the service data table; the service data table comprises: a master table and at least one slave table, an identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table; determining a business constraint code to be optimized from a plurality of business constraint codes according to historical calling information; determining a field to be optimized in a business data table from an identification group corresponding to a business constraint code to be optimized; and establishing a non-unique index of the service data table according to the field to be optimized. The fields to be optimized of the business data table are determined, the indexes of the business data table are established according to the fields to be optimized, the data to be inquired can be inquired through the indexes to reduce the inquiry range, and then the inquiry is carried out according to other identification fields, so that the inquiry quantity is reduced, and the efficiency of searching the repetitive data for the master-slave table is improved.
Optionally, the historical invocation information may include: the number of calls and the total call time.
Fig. 2 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention; as shown in fig. 2, counting historical invocation information of a plurality of service constraint codes in the service data table, where the step S101 may include:
s201, counting the calling times and the total calling time of each business constraint code in the business data table in a preset time period.
Wherein, one call can be: and performing repeated data query on the service data table according to the service constraint code, wherein the corresponding query time can be time consumed for calling.
In some embodiments, for each business constraint code, the server overlaps the number of times that the business constraint code is called within a preset time period, each time the calling has corresponding calling time consumption, and the server also overlaps the calling time consumption to obtain the total calling time consumption.
The S102 may include:
s202, determining the average calling time consumption of each business constraint code according to the calling times and the total calling time consumption.
The server can calculate a quotient of the total call time consumption divided by the number of calls, wherein the quotient is the average call time consumption of the business constraint codes.
For example, the service data table may be an "attendance record table", the fields corresponding to the service constraint codes may be "year" and "customer name", and within a month, the number of calls of the service data table is 5199 times, the total consumed time is 325.999 seconds, and the average consumed time is 62 milliseconds;
s203, determining the business constraint code to be optimized from the business constraint codes according to the average calling time consumption of the business constraint codes.
In one possible implementation manner, the server may select, from the plurality of business constraint codes, the business constraint code that takes the longest average call time as the business constraint code to be optimized.
It should be noted that the server may determine the service constraint code to be optimized from the multiple service constraint codes according to the average call time of the multiple service constraint codes; or determining the business constraint code to be optimized from a plurality of business constraint codes according to the calling times or total calling time of each business constraint code; the business constraint code to be optimized may also be determined from a plurality of business constraint codes according to the selection operation of the user, which is not specifically limited in the embodiment of the present invention.
Optionally, fig. 3 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention, as shown in fig. 3, S203 may include:
s301, displaying average calling time of a plurality of business constraint codes.
The server can control the average calling time of the terminal for displaying a plurality of service constraint codes. The server can control the average calling time for the terminal to display a plurality of business constraint codes of one business data table, and can also control the average calling time for the terminal to display a plurality of business constraint codes of a plurality of business data tables.
Of course, the server can also control the calling times and total calling time of the terminal for displaying a plurality of service constraint codes, whether the service constraint codes are optimized before, and the like.
As shown in Table 1, the business data table may include "week history schedule" and "old customer record table", where the fields corresponding to the first business code of the "week history schedule" may be "name" and "date", and the fields corresponding to the second business code of the "week history schedule" may be "job number" and "item"; the 'old customer profile table' has a service code, and the corresponding fields of the service code can be 'serial number' and 'serial number'.
The calling frequency corresponding to the first service code of the "week history schedule" is 1282 times, the total calling time consumption is 128.097 seconds, and the average time consumption can be 97 milliseconds; the number of calls corresponding to the second service code of the "history schedule" is 4663, the total call time is 143.473 seconds, the average time consumption is 30 milliseconds, the number of calls of the "old customer profile" is 4663, the total call time consumption is 43.393 seconds, and the average time consumption is 9 milliseconds.
TABLE 1
S302, receiving a selection operation of the business constraint code to be optimized, which is input by a user according to the average calling time consumption of a plurality of business constraint codes.
S303, determining a business constraint code to be optimized according to the selection operation.
In the embodiment of the invention, the server can control the average calling time of a plurality of business constraint codes displayed by the terminal, the terminal can determine the business data table and the corresponding business constraint codes to be optimized according to the selection operation of the user and send the business data table and the corresponding business constraint codes to be optimized to the server, and the server can receive the determined business data table and the corresponding business constraint codes to be optimized.
Optionally, fig. 4 is a flowchart illustrating a method for processing a service data table according to an embodiment of the present invention, as shown in fig. 4, where step S103 may further include:
s401, determining the hash degree of each identification field in the identification group corresponding to the service constraint code to be optimized in the service data table.
The hash degree is the number of the non-repeated data corresponding to each identification field in the service data table.
For example, the identification field corresponding to the service constraint code to be optimized may include: the "area" and the "customer name", and the data corresponding to the "area" in the service data table may include: shanghai, beijing, shanghai; the data corresponding to the "customer name" in the service data table may include: zhang three, Li four, Zhang three, Wang five, Zhao somewhat; the hash degree corresponding to "area" may be 2 and the hash degree corresponding to "customer name" may be 4.
S402, according to the hash degree of each identification field, determining the identification field with the maximum hash degree as the field to be optimized from the identification group corresponding to the service constraint code to be optimized.
It should be noted that, in the service data table, the number of the unrepeated data corresponding to the field to be optimized is the largest.
For example, when the hash degree corresponding to the "area" may be 2, and the hash degree corresponding to the "customer name" may be 4, the "customer name" may be used as the field to be optimized of the business constraint code to be optimized.
Optionally, fig. 5 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention, and as shown in fig. 5, the method may further include:
s501, acquiring the newly inserted data record.
Wherein the newly inserted data record comprises: and data of each field in the service data table.
It should be noted that the newly inserted data record may be newly inserted data corresponding to each field in the service data table, may also be modified data corresponding to each field in the service data table, and may also be other data that needs to be queried repeatedly and corresponds to each field in the service data table, which is not limited in the embodiment of the present invention.
For example, the service data table may include a master table and a slave table, and the fields in the master table may include: "year", "month", "area", "status", fields from the table may include "customer name", "arrival time", "time filled in", "status".
For newly inserted data records, data corresponding to the "year" in the main table is "2019", data corresponding to the "month" is "1", data corresponding to the "region" is "shanghai", and data corresponding to the "state" is "newly inserted"; the data corresponding to the client name is 'Zhang III', the data corresponding to the arrival time is '2019-01-10', the data corresponding to the filling time is '2019-01-05', and the data corresponding to the state is 'new insertion'.
S502, taking the data of the field to be optimized in the newly inserted data record as an initial limiting condition, and reducing the checking range of the service data table.
In the embodiment of the invention, the server can take the data of the field to be optimized in the newly inserted data record as the initial limiting condition, query the data corresponding to the field to be optimized in the service data table to obtain the queried data, and reduce the verification range of the service data table.
It should be noted that, the data corresponding to the field to be optimized in the queried data is the same as the data of the field to be optimized in the newly inserted data record, and the queried data may further include: and data corresponding to other fields in the service data table after the check range is reduced.
S503, counting the number of the data which are uniquely checked in the reduced range in the service data table.
In some embodiments, the server may further query the reduced range according to the non-to-be-optimized field in the identifier group, that is, other identifier fields, and data corresponding to other identifier fields in the newly inserted data record, to obtain a statistical result, where the statistical result is used to indicate the number of uniquely verified data.
And S504, if the number of the uniquely verified data is larger than 1, deleting the newly inserted data record in the service data table.
In the embodiment of the present invention, if the number of the uniquely-verified data is equal to 1, it indicates that the verified data only includes the newly-inserted data record itself, that is, the same data as the newly-inserted data record does not exist in the service data table, and if the number of the uniquely-verified data is greater than 1, it indicates that the same data as the newly-inserted data record exists in the service data table, and the newly-inserted data record in the service data table may be deleted.
And when the newly inserted data record is not inserted into the business data table, counting the number of the uniquely checked data in the business data table, if the number of the uniquely checked data is equal to 1, indicating that the same data as the newly inserted data record exists in the business data table, and deleting the newly inserted data record in the business data table.
Optionally, fig. 6 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention, as shown in fig. 6, S502 may include:
s601, using the data corresponding to the field to be optimized as an initial limiting condition, and inquiring a first data table.
The first data table may be a data table in which a field to be optimized in the service data table is located. The first data table may be a master table or a slave table.
In the embodiment of the present invention, the server may query the data corresponding to the field to be optimized in the first data table according to the data of the field to be optimized in the newly inserted data record.
For example, as shown in table 2, the first data table may be a slave table, the field to be optimized may be a "customer name", the data corresponding to the "customer name" in the newly inserted data record is "liquad", and then "liquad" may be "searched in the" customer name "to obtain two data records, one is the newly inserted data record and one is the data record existing in the first data table.
TABLE 2
| Name of customer | Time of arrival | Time filling | Status of state |
| Zhang three | 2018-11-05 | 2018-10-25 | Has been verified |
| Li four | 2018-12-15 | 2018-12-02 | Has been verified |
| Li four | 2019-01-10 | 2019-01-05 | New insertion |
S602, if the first data table has data corresponding to the field to be optimized, determining that the data record of the data corresponding to the field to be optimized in the second data table is the data record to be verified.
And the second data table is a data table where other fields are located, and the other fields are fields except the fields to be optimized in the identification group corresponding to the service constraint code to be optimized in the service data table.
It should be noted that, when the first data table is a master table, the second data table may be at least one slave table, and when the first data table is a slave table, the second data table may be the master table or at least one slave table. For a master table and at least one slave table in a service data table, each row of data is corresponding.
As shown in table 3, table 3 may be the main table corresponding to table 2, and the identification field in table 3 may be: "region".
TABLE 3
| Year of year | Degree of the moon | Region(s) | Status of state |
| 2018 | 11 | Shanghai province | Has been verified |
| 2018 | 12 | Nanjing | Has been verified |
| 2019 | 1 | Shanghai province | New insertion |
And S603, inquiring the data record to be checked according to the data corresponding to other fields in the newly inserted data record.
And recording the data to be verified in the second data table within the reduced range. The other fields may be located in the first data table and/or the second data table.
For example, the "area" in table 3 may be other fields, the data corresponding to the "area" in the newly inserted data record may be "shanghai", and the data record to be verified may include: "Nanjing" and "Shanghai", the server queries the data records to be verified to determine that the number of the uniquely verified data is equal to 1, that is, the newly inserted data itself, and it is stated that when the limiting conditions are "area" and "customer name", the data which is duplicated with the newly inserted data records does not exist in the service data table.
In the embodiment of the invention, when one service data table has a plurality of identification groups, the repeatability check of the newly inserted data record can be carried out according to each identification group.
It should be noted that, when the number of repetitions of each identification field is the same and the multiplication result is just equal to the total number of records, the range that can be narrowed by using the index is equal to "1/power of field of the number of records", for example, 1 million records, and in the case of combining 2 fields, the range can be narrowed to 1000. In general, the data distribution is not uniform, so that the effect of the scheme is better. For example, 7724 records are in the service data table, and the non-repeated record of the field to be optimized is 2860, so that the range can be narrowed to about 2.7, and the performance is greatly improved.
Optionally, fig. 7 is a schematic flowchart of a method for processing a service data table according to an embodiment of the present invention, and as shown in fig. 7, the method may further include:
s701, calculating the hash code to be verified according to the data of the identification group corresponding to the business constraint code to be optimized in the newly inserted data record.
In the embodiment of the present invention, the identifier group includes a plurality of identifier fields, and the server may determine data corresponding to each identifier field in the newly inserted data record to obtain a plurality of data, and calculate the hash code to be verified according to the plurality of data.
As shown in tables 2 and 3, for the newly inserted data record, the data corresponding to the "customer name" is "lie four", the data corresponding to the area is "shanghai", and the hash code to be verified can be determined according to "lie four" and "shanghai".
S702, inquiring a service data table according to the hash code to be checked.
Each row of records in the service data table has a corresponding hash code, and the server can calculate the hash code of each row of records according to the data corresponding to the identification group in each row of records.
In addition, the server can query a plurality of hash codes in the service data table according to the hash code to be verified.
It should be noted that the hash code may be a hash value, may also be an MD5(Message-Digest Algorithm), and may also be other types of values, which is not limited in this embodiment of the present invention.
And S703, deleting the newly inserted data record in the service data table if the hash code identical to the hash code to be verified exists in the service data table.
In the embodiment of the present invention, when a newly inserted data record is not inserted into the service data table, if a hash code identical to the hash code to be verified exists in the service data table, it indicates that the same data in the service data table exists, and the newly inserted data record in the service data table is deleted.
In addition, when a newly inserted data record is inserted into the service data table, if the number of hash codes in the service data table, which is the same as the hash code to be verified, is greater than 1, the same data in the service data table is described, and the newly inserted data record in the service data table can be deleted.
Fig. 8 is a schematic structural diagram of a device for processing a service data table according to an embodiment of the present invention, and as shown in fig. 8, the device may include:
a statistic module 801, configured to count historical call information of multiple service constraint codes in a service data table, where each service constraint code corresponds to an identifier group in the service data table; the service data table comprises: a master table and at least one slave table, an identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table;
a first determining module 802, configured to determine, according to the historical invocation information, a to-be-optimized service constraint code from the multiple service constraint codes;
a second determining module 803, configured to determine a field to be optimized in the service data table from the identifier group corresponding to the service constraint code to be optimized;
an establishing module 804, configured to establish a non-unique index of the service data table according to the field to be optimized.
Optionally, the historical invocation information includes: the number of calls and the total call time;
the statistical module 801 is further configured to count the number of calls and the total call time of each service constraint code in the service data table within a preset time period;
the first determining module 802 is further configured to determine an average call time of each service constraint code according to the call times and the total call time; and determining the business constraint code to be optimized from the plurality of business constraint codes according to the average calling time consumption of the plurality of business constraint codes.
Optionally, the first determining module 802 is further configured to show an average call time of a plurality of business constraint codes; receiving the selection operation of the service constraint code to be optimized input by a user according to the average calling time consumption of a plurality of service constraint codes; and determining a business constraint code to be optimized according to the selection operation.
Optionally, the second determining module 803 is further configured to determine a hash degree of each identification field in the identification group corresponding to the to-be-optimized service constraint code in the service data table; and according to the hash degree of each identification field, determining the identification field with the maximum hash degree as the field to be optimized from the identification group corresponding to the service constraint code to be optimized.
Optionally, the apparatus further comprises:
an acquisition module to acquire a newly inserted data record, the newly inserted data record comprising: data of each field in the service data table;
the narrowing module is used for narrowing the verification range of the business data table by taking the data of the field to be optimized in the newly inserted data record as an initial limiting condition;
the first statistical module is used for counting the number of the uniquely verified data in the reduced range in the service data table;
and the first deleting module is used for deleting the newly inserted data record in the service data table if the number of the uniquely verified data is greater than 1.
Optionally, the narrowing module is further configured to query a first data table by using data corresponding to the field to be optimized as an initial limiting condition, where the first data table is a data table in which the field to be optimized is located in the service data table; if the first data table has data corresponding to the field to be optimized, determining that the data record of the data corresponding to the field to be optimized in the second data table is the data record to be checked, wherein the second data table is a data table where other fields are located, and the other fields are fields except the field to be optimized in the identification group corresponding to the business constraint code to be optimized in the business data table; inquiring the data record to be verified according to the data corresponding to other fields in the newly inserted data record; and recording the data to be verified in the second data table within the reduced range.
Optionally, the apparatus further comprises:
the calculation module is used for calculating the hash code to be verified according to the data of the identification group corresponding to the business constraint code to be optimized in the newly inserted data record;
the query module is used for querying the service data table according to the hash code to be verified;
and the second deleting module is used for deleting the newly inserted data record in the service data table if the hash code which is the same as the hash code to be verified exists in the service data table.
The above-mentioned apparatus is used for executing the method provided by the foregoing embodiment, and the implementation principle and technical effect are similar, which are not described herein again.
These above modules may be one or more integrated circuits configured to implement the above methods, such as: one or more Application Specific Integrated Circuits (ASICs), or one or more microprocessors (DSPs), or one or more Field Programmable Gate Arrays (FPGAs), among others. For another example, when one of the above modules is implemented in the form of a processing element scheduler code, the processing element may be a general-purpose processor, such as a Central Processing Unit (CPU) or other processor capable of calling program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).
Fig. 9 is a schematic structural diagram of a server according to an embodiment of the present invention, where the server may include: a processor 901, a memory 902.
The memory 902 is used for storing programs, and the processor 901 calls the programs stored in the memory 902 to execute the above method embodiments. The specific implementation and technical effects are similar, and are not described herein again.
Optionally, the invention also provides a program product, for example a computer-readable storage medium, comprising a program which, when being executed by a processor, is adapted to carry out the above-mentioned method embodiments.
In the embodiments provided in the present invention, it should be understood that the disclosed apparatus and method may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the division of the units is only one logical division, and other divisions may be realized in practice, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.
The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.
In addition, functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, or in a form of hardware plus a software functional unit.
The integrated unit implemented in the form of a software functional unit may be stored in a computer readable storage medium. The software functional unit is stored in a storage medium and includes several instructions to enable a computer device (which may be a personal computer, a server, or a network device) or a processor (processor) to execute some steps of the methods according to the embodiments of the present invention. And the aforementioned storage medium includes: a U disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.
Claims (10)
1. A method for processing a service data table is characterized by comprising the following steps:
counting historical calling information of a plurality of service constraint codes in a service data table, wherein each service constraint code corresponds to one identification group in the service data table; the service data table comprises: a master table and at least one slave table, said one identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table;
determining a business constraint code to be optimized from a plurality of business constraint codes according to the historical calling information;
determining a field to be optimized in the service data table from the identification group corresponding to the service constraint code to be optimized;
and establishing a non-unique index of the service data table according to the field to be optimized.
2. The method of claim 1, wherein the historical invocation information comprises: the number of calls and the total call time; the historical calling information of a plurality of service constraint codes in the statistical service data table comprises:
counting the calling times and the total calling time of each business constraint code in the business data table within a preset time period;
determining a business constraint code to be optimized from a plurality of business constraint codes according to the historical calling information, wherein the determining comprises the following steps:
determining the average calling time consumption of each business constraint code according to the calling times and the total calling time consumption;
and determining the business constraint code to be optimized from the plurality of business constraint codes according to the average calling time consumption of the plurality of business constraint codes.
3. The method of claim 2, wherein the determining the to-be-optimized business constraint code from a plurality of business constraint codes according to the average call latency comprises:
displaying the average calling time of a plurality of business constraint codes;
receiving the selection operation of the business constraint code to be optimized input by a user according to the average calling time consumption of a plurality of business constraint codes;
and determining the business constraint code to be optimized according to the selection operation.
4. The method according to claim 1, wherein the determining a field to be optimized in the service data table from the identification group corresponding to the service constraint code to be optimized includes:
determining the hash degree of each identification field in the identification group corresponding to the service constraint code to be optimized in the service data table;
and determining the identification field with the maximum hash degree as the field to be optimized from the identification group corresponding to the service constraint code to be optimized according to the hash degree of each identification field.
5. The method of claim 1, further comprising:
obtaining a newly inserted data record, the newly inserted data record comprising: data of each field in the service data table;
taking the data of the field to be optimized in the newly inserted data record as an initial limiting condition, and reducing the verification range of the business data table;
counting the number of the data which are uniquely checked in the reduced range in the service data table;
and if the number of the uniquely verified data is more than 1, deleting the newly inserted data record in the service data table.
6. The method according to claim 5, wherein the narrowing the check range of the service data table by using the data corresponding to the field to be optimized in the newly inserted data record as an initial limiting condition comprises:
taking data corresponding to the field to be optimized as an initial limiting condition, and inquiring a first data table, wherein the first data table is a data table in which the field to be optimized is located in the service data table;
if the first data table has data corresponding to the field to be optimized, determining that a data record corresponding to the data corresponding to the field to be optimized in a second data table is a data record to be checked, wherein the second data table is a data table where other fields are located, and the other fields are fields except for the field to be optimized in an identification group corresponding to the business constraint code to be optimized in the business data table;
inquiring the data record to be checked according to the data corresponding to the other fields in the newly inserted data record; and the reduced range is the data record to be verified in the second data table.
7. The method of claim 1, further comprising:
calculating a hash code to be verified according to the data of the identification group corresponding to the business constraint code to be optimized in the newly inserted data record;
inquiring the service data table according to the hash code to be verified;
and if the hash code identical to the hash code to be verified exists in the service data table, deleting the newly inserted data record in the service data table.
8. A device for processing a service data table, comprising:
the statistical module is used for counting historical calling information of a plurality of business constraint codes in a business data table, and each business constraint code corresponds to one identification group in the business data table; the service data table comprises: a master table and at least one slave table, said one identification group comprising: at least one identification field from the master table, and at least one identification field of each slave table;
the first determining module is used for determining a business constraint code to be optimized from a plurality of business constraint codes according to the historical calling information;
a second determining module, configured to determine a field to be optimized in the service data table from the identifier group corresponding to the service constraint code to be optimized;
and the establishing module is used for establishing the non-unique index of the service data table according to the field to be optimized.
9. A server, comprising: a memory storing a computer program executable by the processor, and a processor implementing the method of any of the preceding claims 1-7 when executing the computer program.
10. A storage medium having stored thereon a computer program which, when read and executed, implements the method of any of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010764935.2A CN111881145A (en) | 2020-07-31 | 2020-07-31 | Method and device for processing service data table, server and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010764935.2A CN111881145A (en) | 2020-07-31 | 2020-07-31 | Method and device for processing service data table, server and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111881145A true CN111881145A (en) | 2020-11-03 |
Family
ID=73205375
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010764935.2A Pending CN111881145A (en) | 2020-07-31 | 2020-07-31 | Method and device for processing service data table, server and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111881145A (en) |
Citations (21)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6047285A (en) * | 1996-03-08 | 2000-04-04 | Oracle Corporation | Method for using an index as a workspace for deferred enforcement of uniqueness constraints |
| US20020174109A1 (en) * | 2001-05-16 | 2002-11-21 | Chandy Kanianthra Mani | Event detection with concurrent data updates |
| US20030167253A1 (en) * | 2002-03-04 | 2003-09-04 | Kelly Meinig | Method and system for identification and maintenance of families of data records |
| US20030233347A1 (en) * | 2002-06-04 | 2003-12-18 | Weinberg Paul N. | Method and apparatus for generating and utilizing qualifiers and qualified taxonomy tables |
| US20040003005A1 (en) * | 2002-06-28 | 2004-01-01 | Surajit Chaudhuri | Detecting duplicate records in databases |
| US20140059015A1 (en) * | 2012-08-23 | 2014-02-27 | Microsoft Corporation | Selecting candidate rows for deduplication |
| US20140081907A1 (en) * | 2012-09-18 | 2014-03-20 | Oracle International Corporation | Client-Side Handling Of Transient Duplicates For Row-Level Replication |
| US20140101117A1 (en) * | 2012-10-10 | 2014-04-10 | Salesforce.Com, Inc. | Methods and systems for managing records in an on-demand system |
| US20160171039A1 (en) * | 2014-12-10 | 2016-06-16 | Katrin Eisenreich | Generating hash values |
| CN105701098A (en) * | 2014-11-25 | 2016-06-22 | 国际商业机器公司 | Method and apparatus for generating index for table in database |
| CN106919678A (en) * | 2017-02-27 | 2017-07-04 | 武汉珞佳伟业科技有限公司 | A kind of database inquiry optimization system and method |
| CN106919637A (en) * | 2016-07-04 | 2017-07-04 | 阿里巴巴集团控股有限公司 | Unique index is created and uniqueness querying method and device |
| US9747295B1 (en) * | 2014-11-03 | 2017-08-29 | Sprint Communications Company L.P. | Updating a large dataset in an enterprise computer system |
| CN107784030A (en) * | 2016-08-31 | 2018-03-09 | 华为技术有限公司 | A kind of method and device for handling Connection inquiring |
| CN108509530A (en) * | 2018-03-14 | 2018-09-07 | 武汉斗鱼网络科技有限公司 | A kind of slow query statement automatic optimization method of MySQL, computer equipment and storage medium |
| US10108634B1 (en) * | 2016-03-23 | 2018-10-23 | EMC IP Holding Company LLC | Identification and removal of duplicate event records from a security information and event management database |
| US10216820B1 (en) * | 2016-12-14 | 2019-02-26 | Gravic, Inc. | Method and apparatus for resolving constraint violations in a database replication system |
| CN109564568A (en) * | 2017-02-13 | 2019-04-02 | 赛思研究所 | Distributed data collection index |
| US20190324955A1 (en) * | 2018-04-24 | 2019-10-24 | VIktor von Drakk | Improved method and device for correlating multiple tables in a database environment |
| CN110399377A (en) * | 2019-08-30 | 2019-11-01 | 北京东软望海科技有限公司 | Optimization method, device, electronic equipment and the computer readable storage medium of SQL |
| CN111241059A (en) * | 2020-01-07 | 2020-06-05 | 广州虎牙科技有限公司 | Database optimization method and device based on database |
-
2020
- 2020-07-31 CN CN202010764935.2A patent/CN111881145A/en active Pending
Patent Citations (22)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6047285A (en) * | 1996-03-08 | 2000-04-04 | Oracle Corporation | Method for using an index as a workspace for deferred enforcement of uniqueness constraints |
| US20020174109A1 (en) * | 2001-05-16 | 2002-11-21 | Chandy Kanianthra Mani | Event detection with concurrent data updates |
| US20030167253A1 (en) * | 2002-03-04 | 2003-09-04 | Kelly Meinig | Method and system for identification and maintenance of families of data records |
| US20030233347A1 (en) * | 2002-06-04 | 2003-12-18 | Weinberg Paul N. | Method and apparatus for generating and utilizing qualifiers and qualified taxonomy tables |
| US20040003005A1 (en) * | 2002-06-28 | 2004-01-01 | Surajit Chaudhuri | Detecting duplicate records in databases |
| US20140059015A1 (en) * | 2012-08-23 | 2014-02-27 | Microsoft Corporation | Selecting candidate rows for deduplication |
| US20140081907A1 (en) * | 2012-09-18 | 2014-03-20 | Oracle International Corporation | Client-Side Handling Of Transient Duplicates For Row-Level Replication |
| US20140101117A1 (en) * | 2012-10-10 | 2014-04-10 | Salesforce.Com, Inc. | Methods and systems for managing records in an on-demand system |
| US9747295B1 (en) * | 2014-11-03 | 2017-08-29 | Sprint Communications Company L.P. | Updating a large dataset in an enterprise computer system |
| CN105701098A (en) * | 2014-11-25 | 2016-06-22 | 国际商业机器公司 | Method and apparatus for generating index for table in database |
| US20160171039A1 (en) * | 2014-12-10 | 2016-06-16 | Katrin Eisenreich | Generating hash values |
| US10108634B1 (en) * | 2016-03-23 | 2018-10-23 | EMC IP Holding Company LLC | Identification and removal of duplicate event records from a security information and event management database |
| CN106919637A (en) * | 2016-07-04 | 2017-07-04 | 阿里巴巴集团控股有限公司 | Unique index is created and uniqueness querying method and device |
| CN107784030A (en) * | 2016-08-31 | 2018-03-09 | 华为技术有限公司 | A kind of method and device for handling Connection inquiring |
| US10216820B1 (en) * | 2016-12-14 | 2019-02-26 | Gravic, Inc. | Method and apparatus for resolving constraint violations in a database replication system |
| CN109564568A (en) * | 2017-02-13 | 2019-04-02 | 赛思研究所 | Distributed data collection index |
| CN111966684A (en) * | 2017-02-13 | 2020-11-20 | 赛思研究所 | Distributed dataset indexing |
| CN106919678A (en) * | 2017-02-27 | 2017-07-04 | 武汉珞佳伟业科技有限公司 | A kind of database inquiry optimization system and method |
| CN108509530A (en) * | 2018-03-14 | 2018-09-07 | 武汉斗鱼网络科技有限公司 | A kind of slow query statement automatic optimization method of MySQL, computer equipment and storage medium |
| US20190324955A1 (en) * | 2018-04-24 | 2019-10-24 | VIktor von Drakk | Improved method and device for correlating multiple tables in a database environment |
| CN110399377A (en) * | 2019-08-30 | 2019-11-01 | 北京东软望海科技有限公司 | Optimization method, device, electronic equipment and the computer readable storage medium of SQL |
| CN111241059A (en) * | 2020-01-07 | 2020-06-05 | 广州虎牙科技有限公司 | Database optimization method and device based on database |
Non-Patent Citations (3)
| Title |
|---|
| 周有文 等: "《汉化ORACLE及其在管理信息系统中的应用》", 湖南师范大学出版社, pages: 99 * |
| 星速云小编: ""阿里云链路追踪 Tracing Analysis查看 SQL 性能分析"", pages 1 - 3, Retrieved from the Internet <URL:《https://www.xingsuyun58.com/23458.html》> * |
| 龚成鹏: ""基于关系型数据库中SQL语句优化规则的研究 ————以Oracle为例"", 《中国优秀硕士学位论文全文数据库 信息科技辑》, vol. 1, no. 12, pages 138 - 286 * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10331642B2 (en) | Data storage method and apparatus | |
| CN106933836B (en) | Data storage method and system based on sub-tables | |
| CN111352902A (en) | Log processing method and device, terminal equipment and storage medium | |
| CN108647357B (en) | Data query method and device | |
| US20100005114A1 (en) | Efficient Delta Handling In Star and Snowflake Schemes | |
| WO2006052541A2 (en) | Methods, systems, and computer-readable mediums for indexing and rapidly searching data records | |
| CN106570027B (en) | Transactional task processing method and device | |
| CN109033165B (en) | Data display method, computer readable storage medium and terminal equipment | |
| CN111061758B (en) | Data storage method, device and storage medium | |
| CN106156040A (en) | multi-dimensional data management method and device | |
| CN111475105A (en) | Monitoring data storage method, device, server and storage medium | |
| CN110851474A (en) | Data query method, database middleware, data query device and storage medium | |
| CN112765163A (en) | Data index storage method, system and device capable of extending dimensionality at will | |
| CN105677645B (en) | A kind of tables of data comparison method and device | |
| US10606829B1 (en) | Methods and systems for identifying data inconsistencies between electronic record systems using data partitioning | |
| CN104166650B (en) | Data storage device and date storage method | |
| CN105408883A (en) | Database table column annotation | |
| CN111881145A (en) | Method and device for processing service data table, server and storage medium | |
| WO2014035934A2 (en) | Compressed set representation for sets as measures in olap cubes | |
| CN115481026A (en) | Test case generation method and device, computer equipment and storage medium | |
| US9092472B1 (en) | Data merge based on logical segregation | |
| CN109582863B (en) | Recommendation method and server | |
| CN112632115A (en) | BI-based data query method and system | |
| CN107861956B (en) | Method and device for inquiring data record of bayonet passing vehicle | |
| US20150039598A1 (en) | Data analysis control |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |