CN117992461A - Database data storage method based on column-type memory storage mode - Google Patents
Database data storage method based on column-type memory storage mode Download PDFInfo
- Publication number
- CN117992461A CN117992461A CN202410397037.6A CN202410397037A CN117992461A CN 117992461 A CN117992461 A CN 117992461A CN 202410397037 A CN202410397037 A CN 202410397037A CN 117992461 A CN117992461 A CN 117992461A
- Authority
- CN
- China
- Prior art keywords
- storage
- table object
- memory
- database
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 230000005055 memory storage Effects 0.000 title claims abstract description 84
- 238000000034 method Methods 0.000 title claims abstract description 56
- 238000013500 data storage Methods 0.000 title claims abstract description 16
- 238000003860 storage Methods 0.000 claims abstract description 173
- 230000006835 compression Effects 0.000 claims description 18
- 238000007906 compression Methods 0.000 claims description 18
- 238000012216 screening Methods 0.000 claims description 13
- 238000012217 deletion Methods 0.000 claims description 8
- 230000037430 deletion Effects 0.000 claims description 8
- 238000012545 processing Methods 0.000 description 7
- 238000010586 diagram Methods 0.000 description 6
- 238000004590 computer program Methods 0.000 description 5
- 230000006870 function Effects 0.000 description 5
- 230000003068 static effect Effects 0.000 description 5
- 238000004891 communication Methods 0.000 description 4
- 238000004458 analytical method Methods 0.000 description 3
- 238000012423 maintenance Methods 0.000 description 3
- 238000007405 data analysis Methods 0.000 description 2
- 238000013523 data management Methods 0.000 description 2
- 230000001360 synchronised effect Effects 0.000 description 2
- 239000002699 waste material Substances 0.000 description 2
- 238000004220 aggregation Methods 0.000 description 1
- 230000002776 aggregation Effects 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 238000009826 distribution Methods 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000001914 filtration Methods 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 238000005259 measurement Methods 0.000 description 1
- 238000005457 optimization Methods 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
- 238000011112 process operation Methods 0.000 description 1
- 238000012163 sequencing technique Methods 0.000 description 1
- 239000004984 smart glass Substances 0.000 description 1
- 238000007619 statistical method Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a database data storage method based on a column-type memory storage mode, which comprises the steps of obtaining a target table object of a database to be stored in a memory based on the column-type memory storage mode, and setting the storage attribute of the target table object as the column-type memory storage mode; acquiring storage parameters for memory storage corresponding to the target table object; reading data corresponding to the target table object; and storing the data corresponding to the target table object in a memory in a data structure stored in a column mode based on the storage parameters.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a database data storage method based on a column-type memory storage mode.
Background
In the existing database technology, database data is generally stored in a column type storage mode or a row type storage mode, such as in nonvolatile storage equipment, if the database data is stored in a row type storage mode for physical storage, when a plurality of column fields in a table are queried, all the column fields need to be read, so that query efficiency is reduced, unnecessary resource waste can be caused, and a scene requiring full-field query rarely occurs in the use process of the database, so that the situation of data redundancy is easy to occur; if a column memory storage mode is adopted for physical storage, if more column fields are needed during query, the integrity of data is difficult to ensure, and the query precision is affected.
Disclosure of Invention
In order to solve the existing technical problems, the embodiment of the invention provides a database data storage method and electronic equipment based on a column memory storage mode, which can reduce data redundancy, improve query efficiency and ensure data integrity.
In a first aspect, a database data storage method based on a column-type memory storage mode is provided, including: acquiring a target table object of a database to be stored in a memory based on a column-type memory storage mode, and setting the storage attribute of the target table object as the column-type memory storage mode;
Acquiring storage parameters for memory storage corresponding to the target table object;
reading data corresponding to the target table object;
And storing the data corresponding to the target table object in a memory in a data structure stored in a column mode based on the storage parameters.
The method comprises the steps of obtaining a target table object, wherein the target table object represents a table object which needs to be stored in a memory based on a column memory storage mode, setting the storage attribute of the target table object as the column memory storage mode, obtaining a storage parameter corresponding to the target table object, and then storing data corresponding to the target table object in the memory in a column memory based on the storage parameter in a data structure, so that when the target table object is needed to be used later, the required column data can be directly obtained in a corresponding reading mode of the column memory, all column fields are not required to be read, thereby reducing data redundancy, improving the query efficiency, and the non-volatile storage device can store data of a database system in a row memory mode, wherein the target table object stored in the memory in the column memory storage mode is only part of data in the database system, and further, the data integrity can be ensured. The target table object is stored in the memory in a column memory storage mode, and the index number of the table can be further optimized, so that unnecessary indexes are removed, common column data is focused on, and the data query efficiency is improved. And the data of the target table object stored in the memory is not permanently stored data, and resources can be updated, deleted, released and the like in real time according to the actual application requirements, so that the flexibility of data management can be improved.
Drawings
FIG. 1 is a diagram of an application environment of a database data storage method based on a columnar memory storage manner in an embodiment;
FIG. 2 is a flow chart of a database data storage method based on a columnar memory storage manner in one embodiment;
FIG. 3 is a schematic diagram of a compression unit in a database data storage method based on a columnar memory storage manner according to an embodiment;
FIG. 4 is a schematic diagram of a memory device in which database data is stored in a memory based on a columnar memory storage manner according to an embodiment;
fig. 5 is a schematic diagram of an electronic device in an embodiment.
Detailed Description
The technical scheme of the invention is further elaborated below by referring to the drawings in the specification and the specific embodiments.
Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the scope of the invention. The term "and/or" as used herein includes any and all combinations of one or more of the associated listed items.
In the following description, reference is made to the expression "some embodiments" which describe a subset of all possible embodiments, but it should be understood that "some embodiments" may be the same subset or a different subset of all possible embodiments and may be combined with each other without conflict.
Referring to fig. 1, an application environment diagram of a database data storage method based on a columnar memory storage manner in an embodiment is shown. The application environment diagram may include a business system 100, a database system 200, and a storage system 300 (hereinafter referred to as storage system 300) in memory for database data based on a columnar memory storage manner. Wherein the business system 100 is in data communication with the database system 200, the business system 100 is a system for user use, and the database system 200 is a database based on a relational database model, and provides data support for the business system 100. The business system 100 provides various functions to users based on the data provided by the database system 200. The business system 100 requests data from the database system 200 based on the user's functional usage. Database system 200 queries data associated with a request according to the request and using a database query language. The storage system 300 is in data communication with the database system 200, and the storage system 300 loads database data to be loaded into the memory in the database system 200 by using the database data storage method based on the column memory storage mode in the embodiment of the application, and stores the data in the memory in the column memory storage mode, so as to improve the timeliness and accuracy of the data query of the service system 100. Database system 200 may be an oracle database, db2 block, SQL SERVER database, mySQL, or the like. The business system 100, the in-memory storage system 300 based on column-based memory storage may refer to logically independent software, applets, etc. The service system 100, the database system 200 and the storage system 300 may be located in the same electronic device at the same time or partially, or may be located in different electronic devices, so as to ensure that the service system 100, the database system 200 and the storage system 300 can perform data communication. In some embodiments, the storage system 300 may be located in the database system 200 as a storage tool for the database system 200.
Referring to fig. 2, a flowchart of a database data storage method based on a column-type memory storage mode according to an embodiment of the application is shown. The database data storage method based on the column-type memory storage mode is applied to the electronic equipment, and comprises the following steps of:
S11, acquiring a target table object of a database to be stored in a memory based on a column-type memory storage mode, and setting the storage attribute of the target table object as the column-type memory storage mode.
In this embodiment, the target table object indicates that the table object stored in the memory in the columnar memory storage manner needs to be loaded. Table objects include, but are not limited to, database table-level objects, sub-tables of table-level objects, and the like. The storage attribute is used to identify whether the table object needs to be stored in the memory in a columnar memory storage manner. The data of each table object of each database is stored in a line manner on the nonvolatile memory device. For the database system 200, a plurality of databases may be provided, and typically, more table objects are provided in each database, and some table objects may be set as target table objects according to historical usage data of the database system 200 or requirements of current query, so that storage attributes of the target table objects are set as a column-type memory storage manner.
S12, obtaining storage parameters for memory storage corresponding to the target table object.
In this embodiment, the storage parameter is used to indicate the parameter that the target table object in each database in the database system 200 stores in the memory. The corresponding storage parameters in the memory of each database may be pre-assigned, including, but not limited to, storage size, storage address. Where the memory address includes, but is not limited to, a starting memory address. The storage parameters can be set through static parameters provided by the database instance, so that when the instance is started, data corresponding to the target table object can be loaded in a corresponding storage space according to the storage parameters provided by the static parameters.
In this embodiment, the storage parameters are associated with databases, each database corresponds to a storage parameter, the storage parameter associated with the identifier of the database is found according to the identifier of the database where the target object is located, and the storage parameter corresponding to the target table object is determined to be stored in the memory based on the column-type memory storage mode.
S13, reading data corresponding to the target table object.
In this embodiment, data of each table object of each database is stored in a line on the nonvolatile storage device. Therefore, the data of the target table object is read from the nonvolatile storage device by a read method corresponding to the line storage method.
And S14, storing the data corresponding to the target table object in a memory in a data structure stored in a column mode based on the storage parameters.
In this embodiment, based on the data structure of the columnar storage, the read data is converted into the data required by the columnar storage, then the storage space where the database corresponding to the target table object is located in the memory is determined based on the storage parameter, and the data corresponding to the target table object is stored in the memory in the form of the converted data.
The application scenario of the above embodiment includes an application scenario of online analysis processing workload, a data warehouse application scenario, and a high concurrency query scenario. In an application scenario of online analysis processing workload, the database columnar storage is suitable for processing a large amount of analysis queries, because the columnar storage can efficiently process operations such as aggregation queries, data analysis, report generation and the like. In the application scenario of a data warehouse, columnar storage may provide faster query performance for data warehouse applications that require extensive data analysis and complex queries. In a high concurrency query scenario, columnar storage is generally better able to support high concurrency queries because queries only need to read the required column data, and do not need to read the entire row of data, reducing the amount of data read.
In the above embodiment, the target table object is obtained, where the target table object represents a table object that needs to be stored in the memory based on the columnar memory storage manner, the storage attribute of the target table object is set as the columnar memory storage manner, the storage parameter corresponding to the target table object is obtained, and then the data corresponding to the target table object is stored in the memory in the columnar data structure based on the storage parameter, so that when the target table object is needed to be used later, the required column data can be obtained directly from the memory in the columnar data reading manner, without reading all column fields, thereby reducing the data redundancy, improving the query efficiency, and in the nonvolatile storage device, the data of the database system can be stored in the columnar memory storage manner, and the target table object stored in the memory in the columnar memory storage manner is only part of the data in the database system, thereby also ensuring the data integrity. The target table object is stored in the memory in a column memory storage mode, and the index number of the table can be further optimized, so that unnecessary indexes are removed, common column data is focused on, and the data query efficiency is improved. And the data of the target table object stored in the memory is not permanently stored data, and resources can be updated, deleted, released and the like in real time according to the actual application requirements, so that the flexibility of data management can be improved.
In some embodiments, the obtaining the target table object of the database to be stored in the memory based on the columnar memory storage manner includes at least one of:
Automatically identifying a target table object which accords with the storage condition of the columnar memory in the database;
The automatic identification database is characterized in that the target table object meeting the storage condition of the columnar memory comprises at least one of the following components:
Statistically analyzing the use data of each column in each table object in the database, screening columns with the use rate meeting preset conditions according to the use data of each column in each table object in the database, and determining the table object corresponding to the screened columns as the target table object, wherein the preset conditions comprise at least one of the following: the utilization rate row is arranged at the front preset position, and the utilization rate is higher than or equal to the preset utilization rate; or (b)
Obtaining a table object associated with a target service system, statistically analyzing the use data of each column in the table object associated with each service system, screening columns with the use rate meeting the preset conditions according to the use data of each column in the table object associated with each service system, and determining the table object corresponding to the screened columns as the target table object, wherein the preset conditions comprise at least one of the following: the utilization rate row is arranged in the front preset position, and the utilization rate is higher than or equal to the preset utilization rate.
Since there may be multiple databases in the database system 200, multiple business systems may be connected, but in many application scenarios, some columns may be only interested, for example, in an electronic commerce database system, the frequency of searching the first 20 products with the highest sales in the present year is found more frequently through statistical analysis, and the query statement only concerns three data columns: time (date), item (item), and sales (sales count). Other data columns of the commodity, such as commodity URL, commodity description, store to which the commodity belongs, etc., are useless data columns for the query statement, and the column memory storage mode only needs to read the data columns storing time, commodity and sales, and the row type database needs to read all the data columns, so that the common columns are needed to be analyzed from the whole database system or from the service system, and are loaded in the memory in the column memory storage mode, thereby reducing data redundancy and improving the query efficiency.
Thus, the screening conditions may be set to automatically periodically statistically analyze the usage data of each column in each table object in the database or statistically analyze the usage data of each column in each table object associated with each business system, for example, periodically at preset time intervals. Where the usage data includes, but is not limited to, frequency of use, number of uses, length of use, etc. And comprehensively evaluating the utilization rate of each column according to the utilization data, so that columns with higher utilization rate are screened out, and then, table objects corresponding to the columns with higher utilization rate are taken as target table objects. Wherein the target business system may be one or more, the target business system includes, but is not limited to: newly added service system, service system occupying system resources higher than resource preset condition, and user-defined service system. In an alternative embodiment, the target service system is set by setting a target service system setting item on the user interface, and the target service system can be set by the target service system setting item, for example, a user can add a new service system or a service system occupying a system resource higher than a resource preset condition as a target service system, and can also customize the target service system. In an alternative embodiment, the new service system or the service system occupying the system resources higher than the resource preset condition can be automatically added as the target service system when the new service system is automatically detected. The service system is flexibly detected in a plurality of modes, so that the service system is analyzed, the working efficiency of the database system is improved, and the service system of the user side can stably run.
In the above embodiment, the usage data of each column in each table object in the database is automatically and periodically statistically analyzed or the usage data of each column in the table objects associated with each service system is statistically analyzed, and the usage rate of each column is comprehensively evaluated according to the usage data, so that the column with higher usage rate is selected, then the table object corresponding to the column with higher usage rate is used as the target table object, so that when the target table object is needed in the subsequent process, the needed column data can be directly obtained from the memory in the corresponding reading mode stored in the column without reading all column fields, thereby reducing the data redundancy and improving the query efficiency,
In some embodiments, the obtaining the target table object of the database to be stored in the memory based on the columnar memory storage manner includes:
and acquiring a configuration instruction for configuring the storage attribute of the database object, and acquiring a target table object of the database based on the configuration instruction.
A user configuration interface may be provided, on which a database object configuration item is provided, the database object configuration item including a storage attribute item by which whether to set a storage manner of a table object in a memory to a columnar memory storage manner may be set. In an alternative example, a selected instruction of a configuration item of a database object is obtained, a selected table object is determined according to the selected instruction, then a configuration instruction of a storage attribute item of the selected table object is obtained, and if the storage attribute item of the selected table object indicates that a storage mode of the selected table object in a memory is set as a column-type memory storage mode, the selected table object is determined to be a target table object.
In the above embodiment, the user configuration interface is provided, so that the user can flexibly configure the table objects which need to be loaded into the memory and stored in the column according to the own requirements, thereby improving the working efficiency of the database system.
In some embodiments, the obtaining the target table object of the database to be stored in the memory based on the columnar memory storage manner includes:
When a database instance is started, acquiring a current database use state, acquiring use data of each current table object when the current database use state meets a set condition, screening columns with the use rate meeting a preset condition according to the use data of each column in each current table object, and determining the table object corresponding to the screened columns as the target table object, wherein the set condition comprises at least one of the following: the resources occupied by the current database exceed the preset resource value, and the running index of the current query statement exceeds the preset value; the preset conditions include at least one of the following: the utilization rate row is arranged in the front preset position, and the utilization rate is higher than or equal to the preset utilization rate.
Because a more complex application scene may be encountered in the running process of the database, more system resources are required to be occupied under the scene, so that the use efficiency of the database is affected, the state of the database can be automatically monitored in the running process of the database, the list with the use rate meeting the preset condition is determined as the target list object, the list objects corresponding to the screened list are loaded in the memory frequently, and the system I/O is greatly reduced by storing in a list memory storage mode, so that the data redundancy is reduced, and the query efficiency is improved. Wherein the resources include, but are not limited to: processor resources, memory resources, and the like. Operational metrics include, but are not limited to: resolving call times, disk reading times, cache reading times, processing line numbers, execution times, processing operation time, operation time of query sentences, average resolving time, sequencing times, shared memory, physical reading request times, physical reading byte numbers, physical writing requests, physical writing byte numbers and the like.
In the above embodiment, table objects corresponding to some columns meeting the conditions can be set as target table objects in the running process of the database, so that the state of the database can be automatically monitored in the running process of the database, and the measurement of the database can be adjusted in real time, thereby improving the query efficiency and the utilization rate of the database.
In some embodiments, before obtaining the storage parameter for memory storage corresponding to the target table object, the method further includes:
The method comprises the steps of pre-distributing storage spaces of all databases and setting storage parameters of all the storage spaces, wherein the storage parameters comprise at least one of the following: memory size, memory address.
The memory space may be preset for each database in the database system 200, so that when a table object in the database is set as a target table object, the table object may be loaded into the corresponding memory space. Wherein the storage parameters include, but are not limited to, storage size, storage address. Where the memory address includes, but is not limited to, a starting memory address. The storage parameters can be set through static parameters provided by the database instance, so that when the instance is started, data corresponding to the target table object can be loaded in a corresponding storage space according to the storage parameters provided by the static parameters.
In the above embodiment, the storage space in the memory is allocated to each database in advance, so that the use efficiency of the database is improved.
In some embodiments, storing the data corresponding to the target table object in the memory in a data structure stored in a column based on the storage parameter includes:
Dividing a plurality of different continuous row groups on each column in the data corresponding to the target table object into a plurality of data blocks, wherein the row boundaries of the data blocks corresponding to each column in the same row group are the same;
compressing the data blocks respectively to form a plurality of compression units;
and storing the plurality of compression units in a storage space corresponding to the target table object in a memory based on the storage parameters.
The basic Unit of the table object stored in the memory in a column is a Compression Unit CU (Compression Unit), i.e. a compressed data block consisting of a part of the data of a column in the table. The whole table or partition is divided into a plurality of CUs according to different columns, as shown in fig. 3, there are six columns of columns C0, C1, C2, C3, C4 and C5 in the figure, wherein every 10 ten thousand rows are used as a row group, two row groups are shown in fig. 3, each column is divided into two data blocks, each data block is compressed into a compression unit, and then each column is finally formed into two compression units. For different columns, as shown in fig. 3, the row boundaries of the data blocks corresponding to the columns in the same row group are the same. After the data blocks are compressed, the size of the compression unit corresponding to each data block may be different because of the difference of data characteristics and compression rate.
Optionally, storing the plurality of compression units in the storage space corresponding to the target table object in the memory based on the storage parameter includes:
When a database instance is started, obtaining storage attributes of all table objects through starting a servo process;
acquiring a target table object according to the storage attribute;
And loading a plurality of compression units corresponding to the target table object into a memory according to the storage parameters.
In this embodiment, when an instance is started, a servo process is started, column storage attributes of each table object are detected, and data of the target table object is loaded into a shared memory of the column storage.
In the above embodiment, the data corresponding to the target table object is converted into the data structure of column storage, so that column storage can be conveniently and well realized in the memory, thereby reducing data conversion and improving the working efficiency of the database.
In some embodiments, the method further comprises:
And storing the log information corresponding to the target table object in a preset area in the memory, and updating the data corresponding to the target table object in the storage space according to the log information.
In order to make the data stored in the shared memory consistent, the log information of the target table object in the shared memory is maintained, and the log information includes, but is not limited to, a change log, a redo log, and the like. The log information is stored in a preset area of the shared memory and maintained by a transaction management system of a database kernel, so that consistency of the columnar storage of the shared memory on transaction data is ensured.
In the above embodiment, the log information of the target table object in the shared memory is maintained regularly, and the data corresponding to the target table object in the storage space is updated according to the log information, so that the consistency of the data can be ensured, and the accuracy of database use can be improved.
In some embodiments, the method further comprises:
acquiring a current query statement and a table object associated with the current query statement;
And generating an optimal execution plan corresponding to the current query statement according to the storage attribute of the associated table object, and selecting the optimal execution plan from the generated optimal execution plan by utilizing an optimizer.
The optimized execution plan is a selected one of a plurality of execution plans generated based on the grammar, the semantics, and the base data in the database of the query statement. The execution plan is typically presented in the form of a tree structure. Each node represents an operation such as table scanning, index scanning, ordering, and so forth. Relationships between nodes represent dependencies between operations. The data in each node in the execution plan includes, but is not limited to, operation type, operation object, operation condition, operation cost, and the like. Wherein the operation type represents the operation type represented by the node, such as table scan, index scan, ordering, etc.; the operation object represents the object of the operation, such as a table name, an index name, etc.; the operation condition represents the condition of the node operation, such as the filtering condition in the where clause; the operation cost represents the operation cost of the node by taking the number of IOs or the running time of the processor as a unit. Therefore, by executing the optimal execution plan of the current query statement, the optimal query of one query statement can be completed. The base data is used to indicate the resource data and object data involved in the database system. The base data is the base data for subsequent optimization of the current query statement. Where the resource data may be system level information indicating a database system, including both processor and Input/Output (IO) levels. Specific indicators of resource data include, but are not limited to, processor speed under no load, processor speed under load, IO retrieval time, IO transfer speed, maximum IO throughput, IO number. The object data is used to indicate object level information in the database system, and the actual acquisition granularity may include, but is not limited to: table objects, column objects, and index objects. The table object counts the number of lines, the number of pages (or the number of blocks), and the average length of the lines; counting the number of unique values, the number of NULL values and the data distribution of the columns by the column object; the index object counts the number of leaf blocks of the index tree, the hierarchy of the index tree, the clustering factor, etc. The storage attributes of the table objects associated with the current query statement affect the resource data, and therefore the storage attributes of the table objects associated with the current query statement need to be considered in generating the optimal execution plan.
During inquiry, the required column data is directly obtained from the memory in a corresponding reading mode of column storage without reading all column fields, so that the data redundancy is reduced and the inquiry efficiency is improved. Therefore, when generating the optimal execution plan, whether the storage attribute of the table object is the table object stored in the memory in the column-type memory storage mode is automatically identified, so that the optimal execution plan is generated by combining the storage attribute of the associated table object, and the optimal execution plan with the shortest running time is found out as the optimal execution plan.
In the above embodiment, the optimal execution plan is generated by combining the storage attribute of the table object associated with the query statement, so that the optimal execution plan with the shortest running time can be conveniently found out as the optimal execution plan, thereby improving the query efficiency of the query statement.
In some embodiments, the method further comprises at least one of:
automatically identifying a table object which accords with the storage condition of the removed column type memory;
The automatically identifying a table object that meets the removal list memory storage condition includes at least one of:
Statistically analyzing historical use data of each table object stored in a storage space corresponding to each database in the memory, screening out table objects meeting the deletion condition according to the historical use data, deleting the screened table objects from the memory, and releasing storage resources in the memory of the table objects;
acquiring a deleting instruction for deleting a table object of a database, acquiring the table object to be deleted based on the deleting instruction, and when the storage attribute of the table object to be deleted is a column type memory storage mode, deleting the table object to be deleted and releasing the storage resource of the table object in a memory.
Because the shared memory is not permanently stored, the data stored in the shared memory can be dynamically managed, and the historical usage data of each table object stored in the storage space corresponding to each database in the memory can be periodically analyzed, wherein the usage data comprises, but is not limited to, the frequency of use, the number of times of use, the time of use and the like. And comprehensively evaluating the utilization rate of each table object stored in the shared memory according to the utilization data. Wherein the deletion condition includes, but is not limited to, the usage rate being ranked at a later preset bit, the usage rate being below a preset usage rate threshold. Therefore, table objects meeting the deletion conditions in the shared memory can be deleted regularly, so that the data in the shared memory are released, and the occupation of resources is reduced.
In some alternative embodiments, a maintenance interface for sharing the memory table object may be provided, a user sets a table object to be deleted on the maintenance interface, obtains a deletion instruction based on the maintenance interface, then obtains a table object to be deleted based on the deletion instruction, and determines whether a storage attribute of the table object to be deleted is a column memory storage manner in the memory, and when the storage attribute of the table object to be deleted is the column memory storage manner, the table object to be deleted is released, and a storage resource of the table object to be deleted in the memory is released.
In the embodiment, the table objects meeting the deletion conditions in the shared memory can be deleted regularly, so that the data in the shared memory is released, the occupation of resources is reduced, and the user is supported to dynamically manage the storage resources in the shared memory based on the user interface, so that the unused data can be released in time, and the resource waste is reduced.
Referring to fig. 4, an embodiment of the present application provides a storage device for storing database data in a memory based on a column-type memory storage mode, including: the acquisition module 21 acquires a target table object of a database to be stored in a memory based on a column-type memory storage mode, and sets the storage attribute of the target table object as the column-type memory storage mode; the obtaining module 21 is further configured to obtain a storage parameter for memory storage corresponding to the target table object; a reading module 22, configured to read data corresponding to the target table object; and the execution module 23 is configured to store the data corresponding to the target table object in the memory in a data structure stored in a column based on the storage parameter.
Optionally, the obtaining module 21 is further configured to:
Automatically identifying a target table object which accords with the storage condition of the columnar memory in the database;
The automatic identification database is characterized in that the target table object meeting the storage condition of the columnar memory comprises at least one of the following components:
Statistically analyzing the use data of each column in each table object in the database, screening columns with the use rate meeting preset conditions according to the use data of each column in each table object in the database, and determining the table object corresponding to the screened columns as the target table object, wherein the preset conditions comprise at least one of the following: the utilization rate row is arranged at the front preset position, and the utilization rate is higher than or equal to the preset utilization rate; or (b)
Obtaining a table object associated with a target service system, statistically analyzing the use data of each column in the table object associated with each service system, screening columns with the use rate meeting the preset conditions according to the use data of each column in the table object associated with each service system, and determining the table object corresponding to the screened columns as the target table object, wherein the preset conditions comprise at least one of the following: the utilization rate row is arranged in the front preset position, and the utilization rate is higher than or equal to the preset utilization rate.
Optionally, the obtaining module 21 is further configured to:
and acquiring a configuration instruction for configuring the storage attribute of the database object, and acquiring a target table object of the database based on the configuration instruction.
Optionally, the obtaining module 21 is further configured to:
When a database instance is started, acquiring a current database use state, acquiring use data of each current table object when the current database use state meets a set condition, screening columns with the use rate meeting a preset condition according to the use data of each column in each current table object, and determining the table object corresponding to the screened columns as the target table object, wherein the set condition comprises at least one of the following: the resources occupied by the current database exceed the preset resource value, and the running index of the current query statement exceeds the preset value; the preset conditions include at least one of the following: the utilization rate row is arranged in the front preset position, and the utilization rate is higher than or equal to the preset utilization rate.
Optionally, before acquiring the storage parameter for memory storage corresponding to the target table object, the acquiring module 21 is further configured to:
The method comprises the steps of pre-distributing storage spaces of all databases and setting storage parameters of all the storage spaces, wherein the storage parameters comprise at least one of the following: memory size, memory address.
Optionally, the execution module 23 is further configured to:
Dividing a plurality of different continuous row groups on each column in the data corresponding to the target table object into a plurality of data blocks, wherein the row boundaries of the data blocks corresponding to each column in the same row group are the same;
compressing the data blocks respectively to form a plurality of compression units;
and storing the plurality of compression units in a storage space corresponding to the target table object in a memory based on the storage parameters.
Optionally, the execution module 23 is further configured to:
When a database instance is started, obtaining storage attributes of all table objects through starting a servo process;
acquiring a target table object according to the storage attribute;
And loading a plurality of compression units corresponding to the target table object into a memory according to the storage parameters.
Optionally, the execution module 23 is further configured to:
And storing the log information corresponding to the target table object in a preset area in the memory, and updating the data corresponding to the target table object in the storage space according to the log information.
Optionally, the execution module 23 is further configured to:
acquiring a current query statement and a table object associated with the current query statement;
And generating an execution plan corresponding to the current query statement according to the storage attribute of the associated table object, and selecting an optimal execution plan from the generated execution plans by using an optimizer.
Optionally, the execution module 23 is further configured to:
automatically identifying a table object which accords with the storage condition of the removed column type memory;
The automatically identifying a table object that meets the removal list memory storage condition includes at least one of:
Statistically analyzing historical use data of each table object stored in a storage space corresponding to each database in the memory, screening out table objects meeting the deletion condition according to the historical use data, deleting the screened table objects from the memory, and releasing storage resources in the memory of the table objects;
acquiring a deleting instruction for deleting a table object of a database, acquiring the table object to be deleted based on the deleting instruction, and when the storage attribute of the table object to be deleted is a column type memory storage mode, deleting the table object to be deleted and releasing the storage resource of the table object in a memory.
Optionally, the storage format of the target table object in the nonvolatile storage device is a line storage mode.
It will be appreciated by those skilled in the art that the structure of the in-memory storage device for database data based on the columnar-based memory storage method in fig. 4 does not constitute limitation on the in-memory storage device for database data based on the columnar-based memory storage method, and the respective modules may be implemented in whole or in part by software, hardware, or a combination thereof. The above modules may be embedded in hardware or independent of a controller in a computer device, or may be stored in software in a memory in the computer device, so that the controller may call and execute operations corresponding to the above modules. In other embodiments, the column-based memory storage may include more or less modules in the memory device in memory than illustrated.
Referring to fig. 5, in another aspect of the embodiment of the present application, there is further provided an electronic device 500, including a memory 3011 and a processor 3012, where the memory 3011 stores a computer program, and the computer program when executed by the processor causes the processor 3012 to execute the steps of the database data storage method based on the columnar memory storage manner provided in any one of the embodiments of the present application. Electronic device 500 may include a computing device (e.g., a desktop computer, a laptop computer, a tablet computer, a handheld computer, a smart speaker, a server, etc.), a mobile phone (e.g., a smart phone, a wireless phone, etc.), a wearable device (e.g., a pair of smart glasses or a smart watch), or the like.
The storage device of the database data in the memory based on the columnar memory storage mode includes, but is not limited to: embedded equipment and electronic equipment.
Where the processor 3012 is a control center, various interfaces and lines are utilized to connect various portions of the overall computer device, perform various functions of the computer device and process data by running or executing software programs and/or modules stored in the memory 3011, and invoking data stored in the memory 3011. Optionally, the processor 3012 may include one or more processing cores; preferably, the processor 3012 may integrate an application processor and a modem processor, wherein the application processor primarily handles operating systems, user pages, applications, etc., and the modem processor primarily handles wireless communications. It will be appreciated that the modem processor described above may not be integrated into the processor 3012.
The memory 3011 may be used to store software programs and modules, and the processor 3012 executes various functional applications and data processing by executing the software programs and modules stored in the memory 3011. The memory 3011 may mainly include a storage program area that may store an operating system, application programs required for at least one function (such as a sound playing function, an image playing function, etc.), and a storage data area; the storage data area may store data created according to the use of the computer device, etc. In addition, memory 3011 may include high-speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other volatile solid-state storage device. Accordingly, the memory 3011 may also include a memory controller to provide access to the memory 3011 by the processor 3012.
In another aspect of the embodiments of the present application, a storage medium is further provided, where a computer program is stored, where the computer program when executed by a processor causes the processor to execute the steps of the database data storage method based on the column-type memory storage method provided in any one of the embodiments of the present application.
Those skilled in the art will appreciate that implementing all or part of the processes of the methods provided in the above embodiments may be accomplished by computer programs stored on a non-transitory computer readable storage medium, which when executed, may comprise processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in embodiments provided herein may include non-volatile and/or volatile memory. The nonvolatile 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 a variety of forms such as Static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double Data Rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (SYNCHLINK) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), among others.
The foregoing is merely illustrative of the present invention, and the present invention is not limited thereto, and any person skilled in the art will readily recognize that variations or substitutions are within the scope of the present invention. The scope of the invention is to be determined by the appended claims.
Claims (10)
1. The database data storage method based on the column-type memory storage mode is characterized by comprising the following steps of:
acquiring a target table object of a database to be stored in a memory based on a column memory storage mode, and setting the storage attribute of the target table object as a column memory mode;
Acquiring storage parameters for memory storage corresponding to the target table object;
reading data corresponding to the target table object;
And storing the data corresponding to the target table object in a memory in a data structure stored in a column mode based on the storage parameters.
2. The method for storing database data based on the columnar memory storage as recited in claim 1, wherein the obtaining the target table object of the database to be stored in the memory based on the columnar memory storage includes at least one of:
Automatically identifying a target table object which accords with the storage condition of the columnar memory in the database;
The automatic identification database is characterized in that the target table object meeting the storage condition of the columnar memory comprises at least one of the following components:
Statistically analyzing the use data of each column in each table object in the database, screening columns with the use rate meeting preset conditions according to the use data of each column in each table object in the database, and determining the table object corresponding to the screened columns as the target table object, wherein the preset conditions comprise at least one of the following: the utilization rate row is arranged at the front preset position, and the utilization rate is higher than or equal to the preset utilization rate; or (b)
Obtaining a table object associated with a target service system, statistically analyzing the use data of each column in the table object associated with each service system, screening columns with the use rate meeting the preset conditions according to the use data of each column in the table object associated with each service system, and determining the table object corresponding to the screened columns as the target table object, wherein the preset conditions comprise at least one of the following: the utilization rate row is arranged at the front preset position, and the utilization rate is higher than or equal to the preset utilization rate;
and acquiring a configuration instruction for configuring the storage attribute of the database object, and acquiring a target table object of the database based on the configuration instruction.
3. The method for storing database data based on the columnar memory storage as recited in claim 1, wherein the obtaining the target table object of the database to be stored in the memory based on the columnar memory storage includes:
When a database instance is started, acquiring a current database use state, acquiring use data of each current table object when the current database use state meets a set condition, screening columns with the use rate meeting a preset condition according to the use data of each column in each current table object, and determining the table object corresponding to the screened columns as the target table object, wherein the set condition comprises at least one of the following: the resources occupied by the current database exceed the preset resource value, and the running index of the current query statement exceeds the preset value; the preset conditions include at least one of the following: the utilization rate row is arranged in the front preset position, and the utilization rate is higher than or equal to the preset utilization rate.
4. The method for storing database data based on a columnar memory storage manner as claimed in claim 1, wherein before obtaining the storage parameters for memory storage corresponding to the target table object, the method further comprises:
The method comprises the steps of pre-distributing storage spaces of all databases and setting storage parameters of all the storage spaces, wherein the storage parameters comprise at least one of the following: memory size, memory address.
5. The method for storing database data based on a columnar memory storage manner as claimed in any one of claims 1 to 4, wherein storing the data corresponding to the target table object in the columnar memory based on the storage parameter includes:
Dividing a plurality of different continuous row groups on each column in the data corresponding to the target table object into a plurality of data blocks, wherein the row boundaries of the data blocks corresponding to each column in the same row group are the same;
compressing the data blocks respectively to form a plurality of compression units;
and storing the plurality of compression units in a storage space corresponding to the target table object in a memory based on the storage parameters.
6. The method for storing database data based on a columnar memory storage manner as claimed in claim 5, wherein storing the plurality of compression units in the memory space corresponding to the target table object based on the storage parameter includes:
When a database instance is started, obtaining storage attributes of all table objects through starting a servo process;
acquiring a target table object according to the storage attribute;
And loading a plurality of compression units corresponding to the target table object into a memory according to the storage parameters.
7. The method for storing database data based on a columnar memory storage as recited in claim 4, wherein the method further comprises:
And storing the log information corresponding to the target table object in a preset area in the memory, and updating the data corresponding to the target table object in the storage space according to the log information.
8. A method for storing database data based on a columnar memory storage as recited in any one of claims 1 to 4, wherein the method further includes:
acquiring a current query statement and a table object associated with the current query statement;
And generating an execution plan corresponding to the current query statement according to the storage attribute of the associated table object, and selecting an optimal execution plan from the generated execution plans by using an optimizer.
9. The method for storing database data based on a columnar memory storage according to any one of claims 1 to 4, wherein the storage format of the target table object in the nonvolatile storage device is a columnar storage.
10. A method for storing database data based on a columnar memory storage as recited in any one of claims 1 to 4, further comprising
Automatically identifying a table object which accords with the storage condition of the removed column type memory;
The automatically identifying a table object that meets the removal list memory storage condition includes at least one of:
Statistically analyzing historical use data of each table object stored in a storage space corresponding to each database in the memory, screening out table objects meeting the deletion condition according to the historical use data, deleting the screened table objects from the memory, and releasing storage resources in the memory of the table objects;
acquiring a deleting instruction for deleting a table object of a database, acquiring the table object to be deleted based on the deleting instruction, and when the storage attribute of the table object to be deleted is a column type memory storage mode, deleting the table object to be deleted and releasing the storage resource of the table object in a memory.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410397037.6A CN117992461A (en) | 2024-04-03 | 2024-04-03 | Database data storage method based on column-type memory storage mode |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410397037.6A CN117992461A (en) | 2024-04-03 | 2024-04-03 | Database data storage method based on column-type memory storage mode |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN117992461A true CN117992461A (en) | 2024-05-07 |
Family
ID=90899669
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202410397037.6A Pending CN117992461A (en) | 2024-04-03 | 2024-04-03 | Database data storage method based on column-type memory storage mode |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117992461A (en) |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20160253382A1 (en) * | 2015-02-26 | 2016-09-01 | Ori Software Development Ltd. | System and method for improving a query response rate by managing a column-based store in a row-based database |
| CN107077480A (en) * | 2014-09-17 | 2017-08-18 | 华为技术有限公司 | The method and system of column storage database is adaptively built from the row data storage storehouse of current time based on query demand |
| CN111930848A (en) * | 2020-09-17 | 2020-11-13 | 阿里云计算有限公司 | Data partition storage method, device and system |
| CN113254270A (en) * | 2021-05-28 | 2021-08-13 | 济南浪潮数据技术有限公司 | Self-recovery method, system and storage medium for storing cache hotspot data |
| CN115455040A (en) * | 2022-06-24 | 2022-12-09 | 苏州浪潮智能科技有限公司 | Method, device, equipment and medium for improving database SQL query efficiency |
| WO2022257575A1 (en) * | 2021-06-11 | 2022-12-15 | 华为技术有限公司 | Data processing method, apparatus, and device |
| CN117609226A (en) * | 2023-11-03 | 2024-02-27 | 上海创帧软件有限公司 | Information stream data storage method and device, electronic equipment and readable medium |
-
2024
- 2024-04-03 CN CN202410397037.6A patent/CN117992461A/en active Pending
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107077480A (en) * | 2014-09-17 | 2017-08-18 | 华为技术有限公司 | The method and system of column storage database is adaptively built from the row data storage storehouse of current time based on query demand |
| US20160253382A1 (en) * | 2015-02-26 | 2016-09-01 | Ori Software Development Ltd. | System and method for improving a query response rate by managing a column-based store in a row-based database |
| CN111930848A (en) * | 2020-09-17 | 2020-11-13 | 阿里云计算有限公司 | Data partition storage method, device and system |
| CN113254270A (en) * | 2021-05-28 | 2021-08-13 | 济南浪潮数据技术有限公司 | Self-recovery method, system and storage medium for storing cache hotspot data |
| WO2022257575A1 (en) * | 2021-06-11 | 2022-12-15 | 华为技术有限公司 | Data processing method, apparatus, and device |
| CN115455040A (en) * | 2022-06-24 | 2022-12-09 | 苏州浪潮智能科技有限公司 | Method, device, equipment and medium for improving database SQL query efficiency |
| CN117609226A (en) * | 2023-11-03 | 2024-02-27 | 上海创帧软件有限公司 | Information stream data storage method and device, electronic equipment and readable medium |
Non-Patent Citations (1)
| Title |
|---|
| 阿里云开发者: "400倍加速，PolarDB HTAP实时数据分析技术解密", pages 1 - 22, Retrieved from the Internet <URL:https://juejin.cn/post/7129394143133958180> * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111506559A (en) | Data storage method and device, electronic equipment and storage medium | |
| CN111339171B (en) | Data query method, device and equipment | |
| CN108073696B (en) | GIS application method based on distributed memory database | |
| CN109669975B (en) | Industrial big data processing system and method | |
| CN109885642B (en) | Hierarchical storage method and device for full-text retrieval | |
| CN110659283A (en) | Data label processing method and device, computer equipment and storage medium | |
| CN111708895B (en) | Knowledge graph system construction method and device | |
| CN111061758A (en) | Data storage method, device and storage medium | |
| CN111488323B (en) | Data processing method and device and electronic equipment | |
| CN115544007A (en) | Label preprocessing method and device, computer equipment and storage medium | |
| CN112231351A (en) | Real-time query method and device for PB-level mass data | |
| CN113177021B (en) | Data export method and device for different data sources | |
| CN116662019B (en) | Request distribution method and device, storage medium and electronic device | |
| CN108932258B (en) | Data index processing method and device | |
| CN113391916A (en) | Organization architecture data processing method, device, computer equipment and storage medium | |
| CN117992461A (en) | Database data storage method based on column-type memory storage mode | |
| CN112835932B (en) | Batch processing method and device for business table and nonvolatile storage medium | |
| CN113868138A (en) | Method, system, equipment and storage medium for acquiring test data | |
| CN112579705B (en) | Metadata acquisition method, device, computer equipment and storage medium | |
| KR20220072545A (en) | Device and method for preparating data based on data division | |
| CN117271481B (en) | Automatic database optimization method and equipment | |
| CN113868267A (en) | Method for injecting time sequence data, method for inquiring time sequence data and database system | |
| CN111538713A (en) | Hive-oriented multi-mode data processing method and device and electronic equipment | |
| CN118132566B (en) | Database index optimization method | |
| CN111339245A (en) | Data storage method, device, storage medium and equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |