Cold and hot data conversion storage method and device, computer device and storage medium

Abstract

The application discloses a cold and hot data automatic conversion storage method, and applies to the technical field of databases. The method provided by the application comprises the following steps: obtaining a table name of a data table corresponding to a to-be-analyzed SQL script and a first primary key value from a slave database of a target database; if the table name and the first primary key value are successfully obtained, storing the table name and the first primary key value into a preset key-value database; obtaining a primary key value set, wherein a second primary key value of the primary key value set is obtained from the slave database of the target database; judging whether each second primary key value in the primary key value set exists in the key-value database, if not, obtaining first row data corresponding to the second primary key value from a master database of the target database; converting and storing the first row data into a preset non-relational database, and deleting the first row data from the master database of the target database.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, apparatus, computer equipment, and storage medium for automatically converting and storing hot and cold data. Background Technology

[0002] As businesses grow, enterprise data becomes increasingly massive, leading to increased management costs and risks. Furthermore, it's difficult to determine whether some data will still be needed after its creation. Traditional methods involve archiving enterprise data chronologically, defaulting to querying only data within certain timeframes. This means data within those timeframes is considered "hot data," while other data is archived as "cold data" and stored using different cost-effective storage media.

[0003] However, in actual business scenarios, dividing cold and hot data according to time has many shortcomings. For example, if the query frequency of some cold data increases within a certain period of time, then this part of cold data should be converted into hot data, and then converted back into cold data after the end of that period. In other words, traditional technology does not automatically convert and store data stored in the database as cold and hot data according to the frequency of use. Summary of the Invention

[0004] This application provides a method, apparatus, computer device, and storage medium for automatic conversion and storage of cold and hot data, in order to solve the problem that traditional technologies do not automatically convert and store data stored in the database as cold and hot data according to the frequency of use.

[0005] A first aspect of this application provides a method for automatically converting between hot and cold data storage, comprising:

[0006] Retrieve the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database;

[0007] If the table name and the first primary key value are successfully obtained, the table name and the first primary key value are stored in a preset key-value database, wherein the key of each key-value pair in the key-value database is the table name and the value is the first primary key value;

[0008] Obtain a set of primary key values, wherein the second primary key value of the set of primary key values ​​is obtained from a slave database of the target database;

[0009] Determine whether each of the second primary key values ​​in the primary key value set exists in the key value database. If it does not exist, retrieve the first row of data corresponding to the second primary key value from the main database of the target database, wherein the first row of data is cold data.

[0010] The first row of data is transferred to a preset non-relational database, and the first row of data is deleted from the main database of the target database.

[0011] A second aspect of this application provides a storage device for automatically switching between hot and cold data, comprising:

[0012] The first data acquisition module is used to obtain the table name and the first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database;

[0013] The first condition processing module is used to store the table name and the first primary key value into a preset key-value database if the table name and the first primary key value are successfully obtained. The table name is the key of each key-value pair in the key-value database, and the first primary key value is the value.

[0014] The primary key value set module is used to obtain a primary key value set, wherein the second primary key value of the primary key value set is obtained from the slave database of the target database;

[0015] The first row data acquisition module is used to determine whether each of the second primary key values ​​in the primary key value set exists in the key value database. If it does not exist, the first row data corresponding to the second primary key value is obtained from the main database of the target database. The first row data is cold data.

[0016] The first row data processing module is used to transfer the first row data to a preset non-relational database and delete the first row data from the main database of the target database;

[0017] A third aspect of this application provides a computer device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the above-described automatic hot and cold data conversion and storage method.

[0018] A fourth aspect of this application provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the above-described automatic hot and cold data conversion storage method.

[0019] The aforementioned method, apparatus, computer equipment, and storage medium for automatic conversion and storage of hot and cold data retrieve the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database. If the table name and first primary key value are successfully retrieved, they are stored in a preset key-value database. A set of primary key values ​​is retrieved, and it is determined whether each second primary key value in the set exists in the key-value database. If it does not exist, the first row of data corresponding to the second primary key value is retrieved from the master database of the target database based on the second primary key value. The first row of data is transferred to a preset non-relational database, and the first row of data is deleted from the master database of the target database. The above steps are repeated according to a preset hot and cold data processing frequency to complete the automatic conversion and storage of hot and cold data in the target database. This not only automatically converts and stores hot and cold data according to usage frequency but also improves the execution efficiency of hot and cold data conversion. Attached Figure Description

[0020] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments of this application will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0021] Figure 1 This is a schematic diagram of an application environment for an automatic hot and cold data conversion and storage method according to an embodiment of this application;

[0022] Figure 2 This is a flowchart of a method for automatically converting and storing hot and cold data according to an embodiment of this application;

[0023] Figure 3 This is a schematic diagram of the structure of an automatic hot and cold data conversion storage device according to an embodiment of this application;

[0024] Figure 4 This is a schematic diagram of a computer device according to an embodiment of this application. Detailed Implementation

[0025] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0026] The automatic hot / cold data conversion and storage method provided in this application can be applied to applications such as... Figure 1In this application environment, the computer equipment can be, but is not limited to, various personal computers and laptops. The computer equipment can also be a server, which can be a standalone server or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms. This is understandable. Figure 1 The number of computer devices shown is merely illustrative and can be expanded in any number according to actual needs.

[0027] In one embodiment, such as Figure 2 As shown, a method for automatically converting and storing hot and cold data is provided, which can be applied to... Figure 1 The following steps, S101 to S105, are used as an example of computer equipment:

[0028] S101. Obtain the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database.

[0029] Specifically, firstly, the SQL script to be analyzed is parsed to obtain the table name and index field of the corresponding data table. Specifically, the table name is first obtained from the SQL script, and then the index field of the data table is retrieved from the primary database of the target database based on the table name. Next, it is determined whether the index field is the primary key of the data table; if not, the primary key value of the data table is retrieved from the secondary database of the target database as the first primary key value.

[0030] Furthermore, before obtaining the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database, the process further includes: obtaining the SQL script to be analyzed from the load balancing component. The SQL script to be analyzed is obtained through the traffic replication function of the load balancing component. As is well known, with the increasing growth of data, database clusters are needed to meet database storage demands, while load balancing components are used to evenly distribute data access pressure across each server in the database cluster. Existing load balancing components are generally configured with traffic replication functionality to facilitate the analysis of real traffic from online services (e.g., analyzing access traffic for cold and hot data in this embodiment). This traffic replication function is implemented by a traffic replication tool, which includes, but is not limited to, gor, tcpreplay, tcpcopy, ngx_http_mirror_module, etc. Traffic replication also includes traffic analysis functionality, meaning the traffic replication tool can identify various network requests initiated against the target system, such as image file requests, video file requests, SQL operation requests, table submission requests, HTTP requests, etc. The method of using the traffic replication tool and how to obtain the SQL script to be analyzed from the traffic of the load balancing component through the traffic replication tool are based on existing traffic replication tool usage methods and traffic analysis methods. However, they are not core steps of this application and will not be described in detail here.

[0031] S102. If the table name and the first primary key value are successfully obtained, the table name and the first primary key value are stored in a preset key-value database, wherein the key of each key-value pair in the key-value database is the table name and the value is the first primary key value.

[0032] Key-value databases are a type of non-relational database that uses a simple key-value approach to store data. They store data as a collection of key-value pairs, where the key serves as a unique identifier. Both keys and values ​​can be anything, ranging from simple objects to complex composite objects. Key-value databases are highly partitionable and allow for horizontal scaling at a scale unattainable by other types of databases.

[0033] Furthermore, the key-value database is a Redis database, and storing the table name and the first primary key value into the preset key-value database includes: first, using the SETBIT command of the Redis database to write the table name and the first primary key value into the Redis database, wherein the key is the table name, the value is the first primary key value, and the data type of the first primary key value being written is the string type of the Redis database. Further, before the first primary key value is written into the Redis database, the first primary key value is converted into BITSET type data. Using the string type of the Redis database to store the BITSET type data of the first primary key value makes the storage space occupied by the first primary key value in the Redis database smaller, while also improving read and write throughput.

[0034] S103. Obtain the primary key value set, wherein the second primary key value of the primary key value set is obtained from the slave database of the target database.

[0035] Since all primary key values ​​are retrieved from the target database, step S103 inevitably involves frequent access to the target database, generating access pressure. In step S103, the primary key value set is obtained from a slave database of the target database, rather than from the master database. If the primary key value set were obtained from the master database, it would inevitably preempt system resources owned by real users in the production environment, causing a decline in the user experience, such as timeouts when retrieving data from the target database. Furthermore, retrieving data in reverse order of creation time in step S103 references the traditional method of dividing data into cold and hot data based on time; that is, data associated with creation times closer to the current time is most likely to be hot data. Furthermore, each data table in the target database contains an acquisition time rule field, which means that the primary key value set is acquired in the order or in reverse order of the creation time. This is because there are certain special scenarios where data created a long time ago is the most frequently accessed hot data. For example, in a customer relationship management system, the customer information data that is earliest retained and has been kept in the database table is the most frequently accessed data.

[0036] S104. Determine whether each of the second primary key values ​​in the primary key value set exists in the key value database. If it does not exist, obtain the first row of data corresponding to the second primary key value from the main database of the target database, wherein the first row of data is cold data.

[0037] Specifically, determining whether each second primary key value in the primary key value set exists in the key value database includes: sequentially retrieving each second primary key value from the primary key value set; using the BITGET command of the Redis database to query whether each second primary key value exists in the Redis database; if the return result of the BITGET command is 0, then it is determined that the second primary key value does not exist in the Redis database, and the third row of data corresponding to the second primary key value is cold data; if the return result of the BITGET command is greater than 0, then it is determined that the second primary key value exists in the Redis database, and the third row of data corresponding to the second primary key value is hot data. The time complexity of the SETBIT and BITGET commands in the Redis database is O(1), meaning that the data operation process of the SETBIT and BITGET commands is efficient, thereby improving the efficiency of the automatic cold and hot data conversion storage described in this embodiment. Furthermore, the data corresponding to the first primary key value stored in the Redis database is identified as hot data, while the data in the target database whose primary key value does not correspond to the first row of data corresponding to the second primary key value in the Redis database is considered cold data. In other words, the data in the target database corresponding to the SQL script to be analyzed, obtained through the traffic replication function of the load balancing component, is hot data, while the data in the target database not corresponding to the SQL script to be analyzed is cold data. After automatically identifying the hot and cold data in the target database, the cold data in the target database needs to be transferred to another location to reduce the data size of the target database and further improve the data operation efficiency of the target database.

[0038] S105. Transfer the first row of data to a preset non-relational database, and delete the first row of data from the main database of the target database.

[0039] The non-relational databases include, but are not limited to, MongoDB, HBase, Cassandra, CouchDB, Neo4J, and Memcached. Further, based on the business characteristics of the data tables in the target database and the data structure characteristics of those tables, the first row of data is stored in different types of non-relational databases. For example, document-type data is transferred to the document-type non-relational database MongoDB, product information data is transferred to the columnar database HBase, and user-defined node data and node relationships with a graph data structure are transferred to the graph database Neo4J. Since the specific usage details of different database products vary, the specific technical details of transferring the first row of data to a preset non-relational database are not elaborated here. The selection of the non-relational database technology can be based on the characteristics of the target business, choosing at least one non-relational database accordingly.

[0040] It should be specifically noted that the target database in this embodiment is a relational database, including but not limited to Oracle, DB2, MySQL, Microsoft SQL Server, and PostgreSQL. Furthermore, because the target system requires data consistency and atomicity for data stored in relational databases, and relational databases generally require higher hardware resources and performance compared to non-relational databases, automatically identifying a portion of the target database as cold data and transferring it to the lower-cost non-relational database not only reduces the amount of data in the target database but also reduces request fluctuations and data risks, while simultaneously lowering the overall system storage cost.

[0041] Furthermore, after obtaining the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database, the process further includes: if obtaining the table name and first primary key value fails, then obtaining the second row of data corresponding to the first primary key value from the non-relational database. Wherein, when obtaining the table name and first primary key value from the slave database of the target database fails, it indicates that the target database does not contain the data table corresponding to the table name and first primary key value, and also indicates that the data in the data table corresponding to the table name and first primary key value has been classified as cold data. Therefore, the data in the data table corresponding to the table name and first primary key value needs to be restored from the non-relational database containing the cold data to the target database and converted into hot data. After obtaining the second row of data corresponding to the first primary key value from the non-relational database using the primary key value, the second row of data is first transferred to the master database of the target database, and then deleted from the non-relational database. Furthermore, due to the synchronization mechanism between the master and slave databases of the target database, the second row of data will also be copied from the master database to the slave database of the target database. The data synchronization mechanism between the master and slave databases of the target database is not a key technical detail of the automatic cold and hot data conversion and storage method in this embodiment, so it will not be described in detail here.

[0042] Furthermore, the non-relational database is an Elasticsearch cluster, which is used to store cold data. The step of transferring the first row of data to a preset non-relational database and deleting the first row of data from the master database of the target database includes: retrieving the first row of data from a slave database of the target database based on the second primary key value; transferring the first row of data to the Elasticsearch cluster; and deleting the first row of data corresponding to the second primary key value from the master database of the target database. The Elasticsearch cluster provides a distributed, multi-user full-text search function, further improving the execution efficiency of retrieving the second row of data corresponding to the first primary key value from the non-relational database, enabling faster retrieval of the corresponding second row of data using the first primary key value. Furthermore, the Elasticsearch cluster also includes a data analysis engine, which can perform data analysis on the cold data stored in the Elasticsearch cluster according to the business needs of the target system to achieve business goals. Furthermore, the ElasticSearch cluster also includes machine learning capabilities, including anomaly detection using clustering models in unsupervised learning (e.g., detecting abnormal access in the field of information security) and data frame analysis using classification and regression models in supervised learning (e.g., price model analysis in the e-commerce industry). This allows some data in the target database to be automatically converted into cold data, so that the cold data can also generate data value, rather than simply occupying hardware storage space and waiting to be converted into hot data.

[0043] Furthermore, after deleting the first row of data from the main database of the target database, the method further includes repeatedly executing steps S101 to S105 according to a preset cold and hot data processing frequency. This automatically identifies the data in the target database as cold or hot data, transfers the cold data in the target database to the non-relational database, and simultaneously transfers the hot data stored in the non-relational database to the target database. Further, the preset cold and hot data processing frequency is not fixed in this embodiment's automatic cold and hot data conversion method because in actual business scenarios, cold and hot data exhibit certain periodic changes. For example, in the personal consumption field of e-commerce, users of e-commerce platforms frequently query their historical consumption records at the end of the month or year and compare them with consumption records from much earlier times. In such cases, some historical consumption records stored as cold data need to be converted into hot data so that users of the e-commerce platform can access them more quickly. Therefore, the preset cold and hot data processing frequency needs to be further increased. Furthermore, after acquiring the SQL script to be analyzed each time through the traffic replication function of the load balancing component, all acquired SQL scripts to be analyzed are saved. When repeating steps S101 to S105 the next time, it is first determined whether all acquired SQL scripts to be analyzed are completely identical to the previously acquired SQL scripts. If they are completely identical, the subsequent steps of the automatic hot / cold data conversion and storage method in this embodiment do not need to be executed, and the preset hot / cold data processing frequency is further reduced. Furthermore, if all acquired SQL scripts to be analyzed differ from the previously acquired SQL scripts, only the SQL scripts with differences are replaced, and then the steps of the automatic hot / cold data conversion and storage method in this embodiment are executed. That is, the automatic hot / cold data conversion and storage method in this embodiment only performs automatic hot / cold data conversion and storage on the SQL scripts that have changed, further reducing the total amount of data that needs to be processed and improving the execution efficiency of the automatic hot / cold data conversion and storage method in this embodiment.

[0044] Furthermore, a switch for automatically converting and storing hot and cold data is set. As is well known, various unforeseen events can occur in real-world applications. For example, in the e-commerce sector, business departments may need to hold online events at a certain time, which will inevitably put significant pressure on the database. In such cases, it is necessary to stop the execution of the automatic hot and cold data conversion and storage method to avoid further pressure on the database. Before retrieving the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database, the process includes: determining whether the automatic hot and cold data conversion and storage processing switch is in the on state. If the switch is in the off state, subsequent steps are not executed. Simultaneously, the status of the automatic hot and cold data conversion and storage processing switch is checked at preset polling access intervals until the switch is in the on state.

[0045] The automatic hot and cold data conversion and storage method provided in this application obtains the table name and first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database; if the table name and the first primary key value are successfully obtained, they are stored in a preset key-value database; a primary key value set is obtained, wherein the primary key value set is obtained from the slave database of the target database in reverse order of creation time; it is determined whether each second primary key value in the primary key value set exists in the key-value database; if it does not exist, the first row of data corresponding to the second primary key value is obtained from the master database of the target database according to the second primary key value; the first row of data is transferred to a preset non-relational database, and the first row of data is deleted from the master database of the target database. This method not only achieves automatic hot and cold data conversion at the data access level, enabling the control of hot data actually used by the target system, but also transfers a large amount of cold data to relatively low-cost storage, saving the storage cost of relational databases, further improving the stability of the target system and reducing the risk of large data volumes, thus achieving precise automatic control of hot and cold data in the target system.

[0046] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0047] In one embodiment, a cold / hot data automatic conversion storage device 100 is provided, which corresponds one-to-one with the cold / hot data automatic conversion storage method described in the above embodiments. For example... Figure 3As shown, the automatic hot / cold data conversion storage device 100 includes a first data acquisition module 11, a first condition processing module 12, a primary key value set module 13, a first row data acquisition module 14, and a first row data processing module 15. Detailed descriptions of each functional module are as follows:

[0048] The first data acquisition module 11 is used to obtain the table name and the first primary key value of the data table corresponding to the SQL script to be analyzed from the slave database of the target database;

[0049] The first condition processing module 12 is used to store the table name and the first primary key value into a preset key value database if the table name and the first primary key value are successfully obtained. The table name is the key of each key value pair in the key value database, and the first primary key value is the value.

[0050] The primary key value set module 13 is used to obtain a primary key value set, wherein the second primary key value of the primary key value set is obtained from the slave database of the target database;

[0051] The first row data acquisition module 14 is used to determine whether each of the second primary key values ​​in the primary key value set exists in the key value database. If it does not exist, the first row data corresponding to the second primary key value is obtained from the main database of the target database. The first row data is cold data.

[0052] The first row data processing module 15 is used to transfer the first row data to a preset non-relational database and delete the first row data from the main database of the target database.

[0053] Furthermore, the first data acquisition module 11 also includes:

[0054] The SQL script acquisition submodule is used to obtain the SQL script to be analyzed from the load balancing component. The SQL script to be analyzed is obtained through the traffic replication function of the load balancing component.

[0055] The second row data acquisition submodule is used to retrieve the second row of data corresponding to the first primary key value from the non-relational database if it fails to retrieve the table name and the first primary key value.

[0056] The second row data transfer submodule is used to transfer the second row data to the main database of the target database and delete the second row data from the non-relational database.

[0057] Furthermore, the first condition processing module 12 also includes:

[0058] The Redis data writing submodule is used to write the table name and the first primary key value into the Redis database using the SETBIT command of the Redis database, wherein the key is the table name, the value is the first primary key value, and the data type of the first primary key value being written is the string type of the Redis database.

[0059] Furthermore, the first row data acquisition module 14 also includes:

[0060] The primary key value set acquisition submodule is used to sequentially acquire each second primary key value from the primary key value set;

[0061] The Redis database query submodule is used to query whether each second primary key value exists in the Redis database using the BITGET command of the Redis database;

[0062] The cold data determination submodule is used to determine that if the return result of the BITGET command is 0, the second primary key value is not in the Redis database, and the third row of data corresponding to the second primary key value is cold data.

[0063] The hot data submodule is used to determine that if the return result of the BITGET command is greater than 0, the second primary key value is in the Redis database, and the third row of data corresponding to the second primary key value is hot data.

[0064] Furthermore, the first row data processing module 15 also includes:

[0065] The second primary key value acquisition submodule is used to retrieve the first row of data from the slave database of the target database based on the second primary key value;

[0066] The ElasticSearch cluster storage submodule is used to transfer the first row of data to the ElasticSearch cluster;

[0067] The second primary key value deletion submodule is used to delete the first row of data corresponding to the second primary key value from the master database of the target database.

[0068] The terms "first" and "second" in the above-mentioned modules / units are only used to distinguish different modules / units and are not intended to specify which module / unit has a higher priority or any other limiting meaning. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or device that includes a series of steps or modules is not necessarily limited to those steps or modules explicitly listed, but may include other steps or modules not explicitly listed or inherent to these processes, methods, products, or devices. The module divisions appearing in this application are merely logical divisions; in actual applications, different division methods may be used.

[0069] Specific limitations regarding the automatic hot / cold data conversion storage device can be found in the limitations of the automatic hot / cold data conversion storage method described above, and will not be repeated here. Each module in the aforementioned automatic hot / cold data conversion storage device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device in hardware form, or stored in the memory of a computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0070] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 4 As shown, the computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The database stores data involved in the automatic hot / cold data conversion storage method. The network interface communicates with external terminals via a network connection. When the computer program is executed by the processor, it implements an automatic hot / cold data conversion storage method.

[0071] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the steps of the automatic hot / cold data conversion and storage method described in the above embodiments, for example... Figure 2 The steps S101 to S105 shown, as well as other extensions and related steps of the method, are examples. Alternatively, when the processor executes the computer program, it implements the functions of each module / unit of the automatic hot / cold data conversion storage device in the above embodiments, for example... Figure 3The functions of modules 11 to 15 are shown. To avoid repetition, they will not be described again here.

[0072] The processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor. The processor is the control center of the computer device, connecting various parts of the computer device via various interfaces and lines.

[0073] The memory can be used to store the computer programs and / or modules. The processor implements various functions of the computer device by running or executing the computer programs and / or modules stored in the memory and by calling data stored in the memory. The memory may mainly include a program storage area and a data storage area. The program storage area may store the operating system, applications required for at least one function (such as sound playback function, image playback function, etc.), etc.; the data storage area may store data created based on the use of the mobile phone (such as audio data, video data, etc.).

[0074] The memory can be integrated into the processor or it can be set up separately from the processor.

[0075] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When executed by a processor, the computer program implements the steps of the automatic hot / cold data conversion storage method described in the above embodiments, for example... Figure 2 The steps S101 to S105 shown, as well as other extensions and related steps of the method, are examples. Alternatively, when a computer program is executed by a processor, it implements the functions of each module / unit of the automatic hot / cold data conversion storage device in the above embodiments, for example... Figure 3 The functions of modules 11 to 15 are shown. To avoid repetition, they will not be described again here.

[0076] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0077] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0078] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A method for automatically converting cold and hot data, characterized by, The method comprises the following steps: obtaining a to-be-analyzed SQL script from a load balancing component, wherein the to-be-analyzed SQL script is obtained through a traffic replication function of the load balancing component; obtaining a table name of a data table corresponding to data of the to-be-analyzed SQL script and a first primary key value from a slave database of a target database; if the table name and the first primary key value are successfully obtained, storing the table name and the first primary key value in a preset key-value database, wherein a key of each key-value pair in the key-value database is the table name, and a value is the first primary key value; obtaining a primary key value set, wherein a second primary key value in the primary key value set is obtained from the slave database of the target database; judging whether each second primary key value in the primary key value set exists in the key-value database, and if not, obtaining first row data corresponding to the second primary key value from a master database of the target database, wherein the first row data is cold data; storing the first row data in a preset non-relational database, and deleting the first row data from the master database of the target database.

2. The cold-hot data automatic conversion storage method according to claim 1, characterized in that, The method further comprises the following steps: parsing the to-be-analyzed SQL script to obtain a table name of a data table corresponding to the to-be-analyzed SQL script and an index field; if the index field is not a primary key of the data table, obtaining a primary key value of the data table from the slave database of the target database as the first primary key value.

3. The cold-hot data automatic conversion storage method according to claim 1, wherein, The key-value database is a Redis database, and the storing of the table name and the first primary key value in the preset key-value database comprises the following steps: using a SETBIT command of the Redis database to write the table name and the first primary key value into the Redis database, wherein a key is the table name, a value is the first primary key value, and a data type to which the first primary key value is written is a string type of the Redis database.

4. The cold-hot data automatic conversion storage method according to claim 3, characterized by, The judging whether each second primary key value in the primary key value set exists in the key-value database comprises the following steps: obtaining each second primary key value from the primary key value set in sequence; using a BITGET command of the Redis database to query whether the second primary key value exists in the Redis database; if a return result of the BITGET command is 0, it is determined that the second primary key value does not exist in the Redis database, and third row data corresponding to the second primary key value is cold data; if the return result of the BITGET command is greater than 0, it is determined that the second primary key value exists in the Redis database, and third row data corresponding to the second primary key value is hot data.

5. The method of claim 1, wherein, The method further comprises the following steps after the obtaining of the table name of the data table corresponding to the data of the to-be-analyzed SQL script and the first primary key value from the slave database of the target database: if the table name and the first primary key value are not successfully obtained, obtaining second row data corresponding to the first primary key value from the non-relational database; storing the second row data in the master database of the target database, and deleting the second row data from the non-relational database.

6. The cold-hot data automatic conversion storage method according to claim 4, wherein, The non-relational database is an ElasticSearch cluster, the ElasticSearch cluster is used to save cold data, and the converting the first row of data to the preset non-relational database and deleting the first row of data from the master library of the target database comprises: obtaining the first row of data from the slave library of the target database according to the second primary key value; converting the first row of data to the ElasticSearch cluster; deleting the first row of data corresponding to the second primary key value from the master library of the target database.

7. A cold-hot data automatic conversion storage device characterized by comprising: comprise: a first data obtaining module, configured to obtain a table name and a first primary key value of a data table corresponding to a data to be analyzed from a slave library of a target database; a first condition processing module, configured to store the table name and the first primary key value in a preset key-value database if the table name and the first primary key value are successfully obtained, wherein a key of each key-value pair in the key-value database is the table name, and a value is the first primary key value; a primary key value set module, configured to obtain a primary key value set, wherein a second primary key value of the primary key value set is obtained from the slave library of the target database; a first row of data obtaining module, configured to determine whether each second primary key value in the primary key value set exists in the key-value database, and if not, obtain a first row of data corresponding to the second primary key value from a master library of the target database, wherein the first row of data is cold data; a first row of data processing module, configured to convert the first row of data to a preset non-relational database and delete the first row of data from the master library of the target database; wherein the first data obtaining module further comprises a SQL script obtaining sub-module, configured to obtain a to-be-analyzed SQL script from a load balancing component, wherein the to-be-analyzed SQL script is obtained through a traffic replication function of the load balancing component.

8. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, The processor executes the computer program to realize the steps of the cold and hot data automatic conversion storage method in any one of claims 1 to 6.

9. A computer-readable storage medium storing a computer program, the computer program comprising instructions that, when executed by a computer, cause the computer to perform the method of any one of claims 1 to 8. The computer program is executed by the processor to realize the steps of the cold and hot data automatic conversion storage method in any one of claims 1 to 6.

Images