A pig gene bank data retrieval method and system, and storage medium

Abstract

The present invention provides a method and system for retrieving data of a pig gene bank, and a storage medium. When a preset condition is met, a statistical analysis of a pig gene bank query record is triggered, hot spot information of the pig gene bank corresponding to the query request is obtained, and an index tree of the hot spot field is constructed; the order of the fields in the joint index is determined according to the number of occurrences of the fields in the hot spot joint field, and a B+ tree is constructed according to the order as the index tree of the hot spot field. When a user queries the pig gene bank, it is determined whether to query through the index tree according to the SQL statement corresponding to the query request. If so, the query is made through the index tree, otherwise a full table scan query is performed. The present invention can automatically construct an index of the gene bank when the preset condition is met, and two different index trees are constructed by judging the hot spot information, thereby improving the efficiency of searching the index, and thus improving the query speed of the pig gene bank.

Description

Technical Field

[0001] The present invention relates to the field of database retrieval, and in particular to a method, system and storage medium for retrieval of pig gene bank data. Background Art

[0002] my country is a major pork consumer and producer in the world. According to the 2020 Statistical Yearbook released by the Food and Agriculture Organization of the United Nations, my country's pig inventory ranks first in the world. Pig farming is inseparable from the breeding and reproduction of breeding pigs. According to the "Chinese Livestock and Poultry Genetic Resources·Pig" released by the Ministry of Agriculture, my country has 104 local breeds, 18 cultivated breeds, and 6 introduced breeds. Although there are many pig breeds, most of the pigs raised in commercial farms are introduced breeds, such as Large White, Duroc, and Landrace, because many breeds have a long growth cycle. The number of native pigs raised in my country continues to decline, and some breeds are endangered. As a precious biological resource, local pigs still play a very important role in breeding, selection, and biodiversity.

[0003] Establishing a pig gene bank is a key measure to protect local breeds. The current pig gene bank only preserves pig tissue samples or sperm and egg cells at low temperatures. Users lack an intuitive understanding of pig breeds and cannot directly compare the differences between different breeds of pigs, which is very inconvenient for users or researchers to consult. Providing users or researchers with a very intuitive display is an important aspect of improving the application of pig gene banks. For example, when breeding a certain breed of pigs, you only need to view the relevant information of different breeds of pigs in the pig gene bank system. The relevant information of pigs is not limited to the breed of pigs, the full gene sequence, the storage location of gene samples, videos of the breed of pigs, etc. Users can even place orders directly online, and then breed the breed of pigs selected by the user through cloning and other methods, and deliver them to the user.

[0004] When users query the relevant content of the gene bank, there will be a freeze or slow response. The index can improve the query speed of the database. In the past, the index of the database was manually added by the R&D personnel. When setting up the database, the fields that may be frequently accessed in the database are manually judged, and then the index is established through the SQL language (Structured Query Language), such as creating an index with the CREATE INDEX statement. However, there is a lot of subjectivity in human judgment, and the index cannot be automatically adjusted during the operation of the database. Moreover, the index cannot improve the retrieval speed of hot data. For example, in a certain period of time, users often query the genes of Duroc breed pigs, and in another period of time, users often query the local native pig Jinhua pigs. If a B+tree index tree is constructed, it is necessary to search for the corresponding indexes of Duroc and Jinhua pigs in turn according to the tree level. Although it is possible to add frequently accessed data to the cache and improve the query efficiency of the gene bank through the cache mechanism, the cache size is limited and cannot completely solve the problem of slow query speed of the gene bank. The index is established manually. Only when adding or deleting, the database engine will maintain the index. It is impossible to dynamically adjust the index by relying solely on query operations, and the existing B+tree index is not efficient. Summary of the invention

[0005] In order to improve the efficiency of gene bank query, firstly, the present invention provides a method for retrieving pig gene bank data, the method comprising the following steps:

[0006] S1, triggering statistical analysis of pig gene library query records when a preset condition is met, obtaining hotspot information of the pig gene library corresponding to the query request, wherein the hotspot information includes a hotspot table, a hotspot field, a hotspot joint field, and a hotspot value of the hotspot field;

[0007] S2, constructing an index tree of the hotspot field, wherein the root node of the index tree includes two parts, the first part is the hotspot value, and the second part is a B+ tree constructed of non-hotspot values; determining the order of the fields in the joint index according to the number of occurrences of the fields in the hotspot joint field, and constructing a B+ tree as the index tree of the hotspot field according to the order;

[0008] S3, when the user queries the pig gene library, it is determined whether to query through the index tree according to the SQL statement corresponding to the query request. If so, the query is performed through the index tree, otherwise a full table scan query is performed.

[0009] Preferably, the preset condition is:

[0010] The frequency and the average value of the execution time of the SQL query statement within the first preset time length are counted, and a weight value is calculated according to the frequency and the average value. If the weight value is greater than a first threshold value and the load of the gene bank server is less than a second threshold value, the trigger condition is met, otherwise, the trigger condition is not met;

[0011] The weight value is calculated according to the frequency and the average value, specifically: according to the formula Calculate the weight value, where w is the weight value, f is the frequency, is the frequency reference value, t is the average value, is the average time reference value, α and β are adjustment factors, 0<α, β<1, α+β=1.

[0012] Preferably, when the preset conditions are met, the statistical analysis of the pig gene bank query records is triggered to obtain the hot spot information of the pig gene bank corresponding to the query request, specifically:

[0013] Obtaining SQL statements in the query records within the second preset time period, counting the number of occurrences of the table name, the number of occurrences of the field, and the number of occurrences of the corresponding values ​​of the field in the SQL statement according to the table name and the field name, and counting the number of occurrences of different fields in one SQL statement;

[0014] The table whose table name appears the most times is taken as the hotspot table, the field whose ratio of the number of field appearances in the hotspot table to the number of field appearances in all fields of the hotspot table is greater than the third threshold is taken as the hotspot field, and the field whose ratio of the number of times different fields of the hotspot table appear in an SQL statement to the number of SQL query statements of the hotspot table is greater than the third threshold is taken as the hotspot joint field; if a field is both a hotspot field and located in the hotspot joint field, the field is taken as the first field of the hotspot joint field; if there are multiple hotspot fields that are also located in the hotspot joint field, the multiple hotspot fields are sorted, and the hotspot field with the highest sorting is taken as the first field of the hotspot joint field; the hotspot joint field includes at least two fields.

[0015] Preferably, the order of fields in the joint index is determined according to the number of times the fields appear in the hot spot joint field, specifically: the hot spot joint field is sorted from high to low according to the number of times it appears in the query of the hot spot table, and the position of the field in the joint index is determined according to the sorting result.

[0016] Preferably, the querying through the index tree is specifically:

[0017] Determine whether it is a joint index search based on the field of the gene library of the SQL statement. If so, search in the index tree based on the value corresponding to the field of the gene library of the SQL statement; if not, determine whether to search from the first part or the second part first based on the keywords and field values ​​of the SQL statement.

[0018] Preferably, determining whether to search from the first part or the second part first according to the keyword and the value of the field of the SQL statement is specifically:

[0019] Set a preset keyword. If the keyword of the SQL statement is a preset keyword, it is searched from the first part first; otherwise, it is searched from the second part first.

[0020] In another aspect, the present invention provides a pig gene bank data retrieval system, the system comprising the following modules:

[0021] A hotspot information statistics module is used to trigger a statistical analysis of the pig gene bank query record when a preset condition is met, and obtain the hotspot information of the pig gene bank corresponding to the query request, wherein the hotspot information includes a hotspot table, a hotspot field, a hotspot joint field, and a hotspot value of the hotspot field;

[0022] An index tree construction module is used to construct an index tree for the hotspot field, wherein the root node of the index tree includes two parts, the first part is the hotspot value, and the second part is a B+ tree constructed of non-hotspot values; the order of the fields in the joint index is determined according to the number of occurrences of the fields in the hotspot joint field, and a B+ tree is constructed according to the order as the index tree of the hotspot field;

[0023] The gene bank search module is used to determine whether to query through the index tree according to the SQL statement corresponding to the query request when the user queries the pig gene bank. If so, query through the index tree, otherwise perform a full table scan query.

[0024] Preferably, the preset condition is:

[0025] The frequency and the average value of the execution time of the SQL query statement within the first preset time length are counted, and a weight value is calculated according to the frequency and the average value. If the weight value is greater than a first threshold value and the load of the gene bank server is less than a second threshold value, the trigger condition is met, otherwise, the trigger condition is not met;

[0026] The weight value is calculated according to the frequency and the average value, specifically: according to the formula Calculate the weight value, where w is the weight value, f is the frequency, is the frequency reference value, t is the average value, is the average time reference value, α and β are adjustment factors, 0<α, β<1, α+β=1.

[0027] Preferably, when the preset conditions are met, the statistical analysis of the pig gene bank query records is triggered to obtain the hot spot information of the pig gene bank corresponding to the query request, specifically:

[0028] Obtaining SQL statements in the query records within the second preset time period, counting the number of occurrences of the table name, the number of occurrences of the field, and the number of occurrences of the corresponding values ​​of the field in the SQL statement according to the table name and the field name, and counting the number of occurrences of different fields in one SQL statement;

[0029] The table whose table name appears the most times is taken as the hotspot table, the field whose ratio of the number of field appearances in the hotspot table to the number of field appearances in all fields of the hotspot table is greater than the third threshold is taken as the hotspot field, and the field whose ratio of the number of times different fields of the hotspot table appear in an SQL statement to the number of SQL query statements of the hotspot table is greater than the third threshold is taken as the hotspot joint field; if a field is both a hotspot field and located in the hotspot joint field, the field is taken as the first field of the hotspot joint field; if there are multiple hotspot fields that are also located in the hotspot joint field, the multiple hotspot fields are sorted, and the hotspot field with the highest sorting is taken as the first field of the hotspot joint field; the hotspot joint field includes at least two fields.

[0030] Preferably, the order of fields in the joint index is determined according to the number of times the fields appear in the hot spot joint field, specifically: the hot spot joint field is sorted from high to low according to the number of times it appears in the query of the hot spot table, and the position of the field in the joint index is determined according to the sorting result.

[0031] Preferably, the querying through the index tree is specifically:

[0032] Determine whether it is a joint index search based on the field of the gene library of the SQL statement. If so, search in the index tree based on the value corresponding to the field of the gene library of the SQL statement; if not, determine whether to search from the first part or the second part first based on the keywords and field values ​​of the SQL statement.

[0033] Preferably, determining whether to search from the first part or the second part first according to the keyword and the value of the field of the SQL statement is specifically:

[0034] Set a preset keyword. If the keyword of the SQL statement is a preset keyword, it is searched from the first part first; otherwise, it is searched from the second part first.

[0035] Finally, the present invention also provides a computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, enables the processor to implement the method described above.

[0036] Aiming at the problem of slow speed when querying a pig gene bank, the present invention proposes a pig gene bank data retrieval method, system and storage medium, sets preset conditions, and when the preset conditions are met, finds hot spot information related to the gene bank query statement, and then constructs two index trees according to the hot spot information. Specifically, one index tree is constructed for the hot spot field, and another index tree is constructed for the hot spot joint field, wherein the index tree corresponding to the hot spot field places the hot spot value corresponding to the hot spot field at the root node of the index tree, and places the non-hot spot value in the second part of the root node, thereby improving the retrieval speed of the hot spot value corresponding to the hot spot field; the index tree of the hot spot joint field is sorted according to the frequency of occurrence of the hot spot joint field in the query statement, and then the index tree corresponding to the hot spot joint field is constructed, thereby improving the retrieval speed of the hot spot joint field. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0038] Figure 1 This is a flow chart of Embodiment 1;

[0039] Figure 2 This is a schematic diagram of the data table structure;

[0040] Figure 3 B+ tree used by MySQL's InnoDB engine;

[0041] Figure 4 A B+ tree built based on hotspot fields;

[0042] Figure 5 It is a module structure diagram of the present invention. DETAILED DESCRIPTION

[0043] In this article, relational terms such as first and second, etc. are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "comprise", "include" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the statement "comprise a ..." do not exclude the presence of other identical elements in the process, method, article or device including the elements.

[0044] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention. Specific embodiment 1

[0046] The present invention provides a method for retrieving pig gene bank data, such as Figure 1 As shown, the method comprises the following steps:

[0047] S1, triggering statistical analysis of pig gene library query records when a preset condition is met, obtaining hotspot information of the pig gene library corresponding to the query request, wherein the hotspot information includes a hotspot table, a hotspot field, a hotspot joint field, and a hotspot value of the hotspot field;

[0048] There is a time correlation when users query pig breeds and related gene information. For example, at a certain period of time, more people are interested in Duroc breed pigs, while at another period of time, more people are interested in local Jinhua pigs. In addition, there is a situation where there is a certain period of time when people are more interested in pig disease resistance, while at another period of time, many users query lean meat rate. With the change of these information, the fields of the gene library queried by users will also be different. When the gene library is relatively small or has fewer fields, the query speed will not be significantly reduced. However, with the increase in the amount of data in the gene library and the increase in fields, if all fields are indexed, the index itself will take up a lot of space, and the maintenance of the index is also very troublesome.

[0049] The present invention triggers statistical analysis of gene library queries and index creation operations only when preset conditions are met, and indexes are created for hotspot fields and hotspot joint fields, thus avoiding human subjective influences, dynamically creating indexes, and reducing the number of indexes.

[0050] When a user queries pig gene-related information, the background program will call an SQL statement to query the gene library. The gene library includes but is not limited to pig breeds, gene profiles, locations of pig full-sequence genes, frozen tissues, pig genealogies, locations of cloning institutions, pig characteristics, maternal conditions, etc. The above information can be located in the same table or in different tables. The present invention does not make specific limitations on this. When located in the same table, the structure of the table is as follows: Figure 2 As shown in the figure. The query statements of the gene library reflect the content that users have paid more attention to in the recent period. In the future, users are very likely to query this information. Through statistical analysis, we can get the tables and field information of the tables that are frequently queried in the recent period, and then get the hotspot tables, hotspot fields, hotspot joint fields, and hotspot values ​​of hotspot fields. The hotspot joint field is to query a record based on multiple fields. For example, select * from piggene where pig_breed = "Jinhua pig" and clone_region = "Henan", then the fields "pig_breed" and "clone_region" are joint fields, that is, the joint field includes at least two fields from the same database table.

[0051] S2, constructing an index tree of the hotspot field, wherein the root node of the index tree includes two parts, the first part is the hotspot value, and the second part is a B+ tree constructed of non-hotspot values; determining the order of the fields in the joint index according to the number of occurrences of the fields in the hotspot joint field, and constructing a B+ tree as the index tree of the hotspot field according to the order;

[0052] The B+ tree used by MySQL's InnoDB engine is as follows: Figure 3 As shown, in the present invention, unless otherwise specified, the B+ tree refers to the B+ tree used by the InnoDB engine of MySQL.

[0053] The index tree of the present invention is different from the B+ tree used by the InnoDB engine of MySQL. Specifically, the present invention adopts an improved B+ tree for the hotspot field. If Duroc pig is the hotspot value corresponding to the hotspot field, the constructed search tree is as follows: Figure 4 As shown, Yorkshire is located in the first part of the root node, and other non-hotspot values ​​are located in the second part of the root node. The second part is a B+ tree.

[0054] The joint index is also called the aggregate index. The joint index complies with the leftmost prefix matching principle. The leftmost prefix matching principle means that the joint index will only be used if the first field when creating the index is used in the query condition. Otherwise, the full table scan query will be performed. In order to reuse the joint index and avoid full table scan query due to failure to meet the leftmost prefix matching principle, the present invention sorts the fields of the hotspot joint field and then creates a hotspot joint index tree in the sorted order.

[0055] S3, when the user queries the pig gene library, it is determined whether to query through the index tree according to the SQL statement corresponding to the query request. If so, the query is performed through the index tree, otherwise a full table scan query is performed.

[0056] In one embodiment, the preset condition is: counting the frequency of SQL query statements within a first preset time and the average value of the execution time, calculating a weight value according to the frequency and the average value, if the weight value is greater than a first threshold and the load of the gene bank server is less than a second threshold, the trigger condition is met, otherwise, the trigger condition is not met;

[0057] The weight value is calculated according to the frequency and the average value, specifically: according to the formula Calculate the weight value, where w is the weight value, f is the frequency, is the frequency reference value, t is the average value, is the average time reference value, α and β are adjustment factors, 0<α, β<1, α+β=1.

[0058] When too many indexes are created, storage space will be occupied. For example, if a table has 10,000 records, there will be 10,000 index items if only the primary key is used to index all the records. If the table has more records, the space occupied by the index is also very considerable; and it is very troublesome and time-consuming to maintain the index. In a specific embodiment, S1 also includes: when the preset conditions are met, all other indexes of the hotspot table except the index of the primary key are deleted.

[0059] In one embodiment, the statistical analysis of the pig gene library query record is triggered when the preset conditions are met to obtain the hot spot information of the pig gene library corresponding to the query request, specifically:

[0060] Obtaining SQL statements in the query records within the second preset time period, counting the number of occurrences of the table name, the number of occurrences of the field, and the number of occurrences of the corresponding values ​​of the field in the SQL statement according to the table name and the field name, and counting the number of occurrences of different fields in one SQL statement;

[0061] The table whose table name appears the most times is taken as the hotspot table, the field whose ratio of the number of field appearances in the hotspot table to the number of field appearances in all fields of the hotspot table is greater than the third threshold is taken as the hotspot field, and the field whose ratio of the number of times different fields of the hotspot table appear in an SQL statement to the number of SQL query statements of the hotspot table is greater than the third threshold is taken as the hotspot joint field; if a field is both a hotspot field and located in the hotspot joint field, the field is taken as the first field of the hotspot joint field; if there are multiple hotspot fields that are also located in the hotspot joint field, the multiple hotspot fields are sorted, and the hotspot field with the highest sorting is taken as the first field of the hotspot joint field; the hotspot joint field includes at least two fields.

[0062] The field refers to a field of a database table or a field corresponding to a field of a data table. The different fields in one SQL statement refer to at least two fields of a database table being in one SQL statement at the same time, for example, select * from piggene where pig_breed = "***" and clone_region = "***", the "pig_breed" and "clone_region" fields are in the same SQL statement. In a specific embodiment, the third threshold is any number between 0.3 and 0.5.

[0063] Since the joint index complies with the leftmost prefix matching principle, if the SQL query statement only uses the first field generated by the joint index to search the gene library, in this case, the joint index can be used for retrieval without having to generate an index for the first field separately. It can be used as the first field of the joint index.

[0064] In order to reduce the situation where the joint index cannot be used due to the leftmost prefix matching principle of the joint index, in one embodiment, the order of the fields in the joint index is determined according to the number of times the fields appear in the hot spot joint field, specifically: the hot spot joint field is sorted from high to low according to the number of times it appears in the query of the hot spot table, and the position of the field in the joint index is determined according to the sorting result.

[0065] For example, the fields of the joint index are:<pig_breed,clone_region> In the query of the hotspot table piggene, select * from piggene where pig_breed = "***" was queried 40 times;

[0066] 90 queries were made through select * from piggene where clone_region = "***";

[0067] 50 queries were performed using select * from piggene where pig_breed = "***" and clone_region = "***".

[0068] The field "pig_breed" of the hotspot table piggene appears 90 times, and "clone_region" appears 140 times. The results of sorting "pig_breed" and "clone_region" are:<clone_region,pig_breed> , then according to<clone_region,pig_breed> Create joint indexes for popular joint fields.

[0069] In one embodiment, the querying through the index tree is specifically:

[0070] Determine whether it is a joint index search based on the field of the gene library of the SQL statement. If so, search in the index tree based on the value corresponding to the field of the gene library of the SQL statement; if not, determine whether to search from the first part or the second part first based on the keywords and field values ​​of the SQL statement.

[0071] In one embodiment, determining whether to search in the first part or the second part first according to the keyword and the value of the field of the SQL statement is specifically as follows:

[0072] Set a preset keyword. If the keyword of the SQL statement is a preset keyword, the first part is searched first; otherwise, the second part is searched first. The preset keyword includes but is not limited to "=".

[0073] In another aspect, the present invention provides a pig gene bank data retrieval system, such as Figure 5 As shown, the system includes the following modules:

[0074] A hotspot information statistics module is used to trigger a statistical analysis of the pig gene bank query record when a preset condition is met, and obtain the hotspot information of the pig gene bank corresponding to the query request, wherein the hotspot information includes a hotspot table, a hotspot field, a hotspot joint field, and a hotspot value of the hotspot field;

[0075] An index tree construction module is used to construct an index tree for the hotspot field, wherein the root node of the index tree includes two parts, the first part is the hotspot value, and the second part is a B+ tree constructed of non-hotspot values; the order of the fields in the joint index is determined according to the number of occurrences of the fields in the hotspot joint field, and a B+ tree is constructed according to the order as the index tree of the hotspot field;

[0076] The gene bank search module is used to determine whether to query through the index tree according to the SQL statement corresponding to the query request when the user queries the pig gene bank. If so, query through the index tree, otherwise perform a full table scan query.

[0077] Furthermore, the preset condition is:

[0078] The frequency and the average value of the execution time of the SQL query statement within the first preset time length are counted, and a weight value is calculated according to the frequency and the average value. If the weight value is greater than a first threshold value and the load of the gene bank server is less than a second threshold value, the trigger condition is met, otherwise, the trigger condition is not met;

[0079] The weight value is calculated according to the frequency and the average value, specifically: according to the formula Calculate the weight value, where w is the weight value, f is the frequency, is the frequency reference value, t is the average value, is the average time reference value, α and β are adjustment factors, 0<α, β<1, α+β=1.

[0080] Furthermore, when the preset conditions are met, the statistical analysis of the pig gene bank query records is triggered to obtain the hot spot information of the pig gene bank corresponding to the query request, specifically:

[0081] Obtaining SQL statements in the query records within the second preset time period, counting the number of occurrences of the table name, the number of occurrences of the field, and the number of occurrences of the corresponding values ​​of the field in the SQL statement according to the table name and the field name, and counting the number of occurrences of different fields in one SQL statement;

[0082] The table whose table name appears the most times is taken as the hotspot table, the field whose ratio of the number of field appearances in the hotspot table to the number of field appearances in all fields of the hotspot table is greater than the third threshold is taken as the hotspot field, and the field whose ratio of the number of times different fields of the hotspot table appear in an SQL statement to the number of SQL query statements of the hotspot table is greater than the third threshold is taken as the hotspot joint field; if a field is both a hotspot field and located in the hotspot joint field, the field is taken as the first field of the hotspot joint field; if there are multiple hotspot fields that are also located in the hotspot joint field, the multiple hotspot fields are sorted, and the hotspot field with the highest sorting is taken as the first field of the hotspot joint field; the hotspot joint field includes at least two fields.

[0083] Furthermore, the order of fields in the joint index is determined according to the number of times the fields appear in the hot spot joint field, specifically: the hot spot joint field is sorted from high to low according to the number of times it appears in the query of the hot spot table, and the position of the field in the joint index is determined according to the sorting result.

[0084] Furthermore, the querying through the index tree is specifically:

[0085] Determine whether it is a joint index search based on the field of the gene library of the SQL statement. If so, search in the index tree based on the value corresponding to the field of the gene library of the SQL statement; if not, determine whether to search from the first part or the second part first based on the keywords and field values ​​of the SQL statement.

[0086] Further, the determining whether to search from the first part or the second part first according to the keyword and the value of the field of the SQL statement is specifically:

[0087] Set a preset keyword. If the keyword of the SQL statement is a preset keyword, it is searched from the first part first; otherwise, it is searched from the second part first. Specific embodiment three

[0089] The present invention provides a computer-readable storage medium on which executable instructions are stored. When the instructions are executed by a processor, the processor implements the method described in the first specific embodiment.

[0090] The device embodiments described above are merely illustrative, and some or all of the modules may be selected according to actual needs to achieve the purpose of the present embodiment. A person skilled in the art may understand and implement the present invention without creative effort.

[0091] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by adding a necessary general hardware platform, and of course can also be implemented by combining hardware and software. Based on such an understanding, the above technical solution is essentially or the part that contributes to the prior art can be embodied in the form of a computer product, and the present invention can be in the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0092] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for retrieving pig gene bank data, characterized in that: The method comprises the following steps: S1, triggering statistical analysis of pig gene library query records when a preset condition is met, obtaining hotspot information of the pig gene library corresponding to the query request, wherein the hotspot information includes a hotspot table, a hotspot field, a hotspot joint field, and a hotspot value of the hotspot field; S2, constructing an index tree of the hotspot field, wherein the root node of the index tree includes two parts, the first part is the hotspot value, and the second part is a B+ tree constructed of non-hotspot values; determining the order of the fields in the joint index according to the number of occurrences of the fields in the hotspot joint field, and constructing a B+ tree as the index tree of the hotspot field according to the order; S3, when the user queries the pig gene bank, determine whether to query through the index tree according to the SQL statement corresponding to the query request, if so, query through the index tree, otherwise perform a full table scan query; The preset condition is: counting the frequency and the average execution time of the SQL query statement within the first preset time, calculating the weight value according to the frequency and the average value, if the weight value is greater than the first threshold and the load of the gene bank server is less than the second threshold, the trigger condition is met, otherwise, the trigger condition is not met; the weight value is calculated according to the frequency and the average value, specifically: according to the formula Calculate the weight value, where w is the weight value, f is the frequency, is the frequency reference value, t is the average value, is the average time reference value, α and β are adjustment factors, 0<α, β<1, α+β=1.

2. The method according to claim 1, characterized in that The statistical analysis of the pig gene bank query record is triggered when the preset conditions are met to obtain the hot spot information of the pig gene bank corresponding to the query request, specifically: Obtaining SQL statements in the query records within the second preset time period, counting the number of occurrences of the table name, the number of occurrences of the field, and the number of occurrences of the corresponding values ​​of the field in the SQL statement according to the table name and the field name, and counting the number of occurrences of different fields in one SQL statement; The table whose table name appears the most times is taken as the hotspot table, the field whose ratio of the number of field appearances in the hotspot table to the number of field appearances in all fields of the hotspot table is greater than the third threshold is taken as the hotspot field, and the field whose ratio of the number of times different fields of the hotspot table appear in an SQL statement to the number of SQL query statements of the hotspot table is greater than the third threshold is taken as the hotspot joint field; if a field is both a hotspot field and located in the hotspot joint field, the field is taken as the first field of the hotspot joint field; if there are multiple hotspot fields that are also located in the hotspot joint field, the multiple hotspot fields are sorted, and the hotspot field with the highest sorting is taken as the first field of the hotspot joint field; the hotspot joint field includes at least two fields.

3. The method according to claim 2, characterized in that The order of fields in the joint index is determined according to the number of times the fields appear in the hotspot joint field, specifically: the hotspot joint field is sorted from high to low according to the number of times it appears in the query of the hotspot table, and the position of the field in the joint index is determined according to the sorting result.

4. The method according to any one of claims 1 to 3, characterized in that: The query through the index tree is specifically: Determine whether it is a joint index search based on the field of the gene library of the SQL statement. If so, search in the index tree based on the value corresponding to the field of the gene library of the SQL statement; if not, determine whether to search from the first part or the second part first based on the keywords and field values ​​of the SQL statement.

5. The method according to claim 4, characterized in that The determining whether to search in the first part or the second part first according to the keyword and the value of the field of the SQL statement is specifically: Set a preset keyword. If the keyword of the SQL statement is a preset keyword, it is searched from the first part first; otherwise, it is searched from the second part first.

6. A pig gene bank data retrieval system, characterized in that: The system includes the following modules: A hotspot information statistics module is used to trigger a statistical analysis of the pig gene bank query record when a preset condition is met, and obtain the hotspot information of the pig gene bank corresponding to the query request, wherein the hotspot information includes a hotspot table, a hotspot field, a hotspot joint field, and a hotspot value of the hotspot field; An index tree construction module, used to construct an index tree of the hotspot field, wherein the root node of the index tree includes two parts, the first part is the hotspot value, and the second part is a B+ tree constructed of non-hotspot values; Determine the order of fields in the joint index according to the number of occurrences of the fields in the hot joint field, and construct a B+ tree as an index tree of the hot field according to the order; The gene bank search module is used to determine whether to query through the index tree according to the SQL statement corresponding to the query request when the user queries the pig gene bank. If so, query through the index tree, otherwise perform a full table scan query; The preset condition is: counting the frequency and the average execution time of the SQL query statement within the first preset time, calculating the weight value according to the frequency and the average value, if the weight value is greater than the first threshold and the load of the gene bank server is less than the second threshold, the trigger condition is met, otherwise, the trigger condition is not met; the weight value is calculated according to the frequency and the average value, specifically: according to the formula Calculate the weight value, where w is the weight value, f is the frequency, is the frequency reference value, t is the average value, is the average time reference value, α and β are adjustment factors, 0<α, β<1, α+β=1.

7. The system according to claim 6, characterized in that The statistical analysis of the pig gene bank query record is triggered when the preset conditions are met to obtain the hot spot information of the pig gene bank corresponding to the query request, specifically: Obtaining SQL statements in the query records within the second preset time period, counting the number of occurrences of the table name, the number of occurrences of the field, and the number of occurrences of the corresponding values ​​of the field in the SQL statement according to the table name and the field name, and counting the number of occurrences of different fields in one SQL statement; The table whose table name appears the most times is taken as the hotspot table, the field whose ratio of the number of field appearances in the hotspot table to the number of field appearances in all fields of the hotspot table is greater than the third threshold is taken as the hotspot field, and the field whose ratio of the number of times different fields of the hotspot table appear in an SQL statement to the number of SQL query statements of the hotspot table is greater than the third threshold is taken as the hotspot joint field; if a field is both a hotspot field and located in the hotspot joint field, the field is taken as the first field of the hotspot joint field; if there are multiple hotspot fields that are also located in the hotspot joint field, the multiple hotspot fields are sorted, and the hotspot field with the highest sorting is taken as the first field of the hotspot joint field; the hotspot joint field includes at least two fields.

8. A computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, enables the processor to implement the method according to any one of claims 1 to 5.

Images