CN109977145A - A kind of database auto-partition management method and system based on horizontal partitioning - Google Patents
A kind of database auto-partition management method and system based on horizontal partitioning Download PDFInfo
- Publication number
- CN109977145A CN109977145A CN201910242443.4A CN201910242443A CN109977145A CN 109977145 A CN109977145 A CN 109977145A CN 201910242443 A CN201910242443 A CN 201910242443A CN 109977145 A CN109977145 A CN 109977145A
- Authority
- CN
- China
- Prior art keywords
- data
- subregion
- database
- time
- partition management
- 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
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention is more particularly directed to a kind of database auto-partition management method and system based on horizontal partitioning.The database auto-partition management method based on horizontal partitioning, by business demand flexible configuration zone attribute, it is automatic to execute division operation, task schedule is carried out automatically according to the configuration period, meet the zone object of time interval by periodic inspection, creation, deletion and the data dump of subregion are carried out according to configuration information, it is primary intermediate without manual intervention with postponing, and then realize auto-partition management.The database auto-partition management method and system based on horizontal partitioning, the efficient zoned of MySQL database can not only be realized while not influencing the normal operation of business on line, improve database access performance, manual intervention is also reduced simultaneously, simplify division operation, can be avoided artificial mistake write accidentally write caused by hidden danger, save a large amount of human costs.
Description
Technical field
The present invention relates to database expansion technique field, in particular to a kind of database auto-partition based on horizontal partitioning
Management method and system.
Background technique
With the rapid development of Internet technology, data volume sharply expands, and increasing data volume necessarily causes to count
According to the downslide of library performance.Under the premise of not influencing business normal operation on line, these data how are managed, control data scale
It becomes in order to which enterprise has to solve the problems, such as.Table is divided to be one of most common solution MySQL database carry out level.
MySQL is a mini-relational type data base management system, and the data in MySQL database are in the form of a file
It is stored on disk.If the data volume of a table is too big, file size will become larger, and search data and just become therewith
Slowly.At this time if database is carried out horizontal partitioning (still retain and arrange), one whole big table laterally cut by certain rule
Point, it is dispersed in record on each subregion, then when inquiring and access, it is only necessary to for partial document progress, rather than whole magnify
Table can effectively increase access speed and writing speed, and response is faster.Meanwhile for no longer valid or unconcerned data, than
Subregion where it can be carried out subregion deletion or data dump by such as out-of-date performance data, log recording.
However MySQL database can not be automatically brought into operation subregion, so the usage mode of general subregion is several
It SQL statement is write by scene according to library manager manually performs and realize the operation to partitions of database.But there are following
Problem:
First is that since data demand is written in existing and legal subregion, if database maintenance personnel do not mention
The expected subregion of preceding creation, then new data will be unable to that correct subregion is written, or even cause loss of data；
Second is that the zone configuration that MySQL database provides is single, it is unable to satisfy single and creates the business such as multiple subregions
Demand；
Third is that due to manual operation, inevitably exist database maintenance personnel write realize subregion SQL during mistake write and accidentally write
Hidden danger；
Fourth is that division operation belongs to mostly is repeated cyclically work, labor intensive.
Based on the above situation, the present invention devises a kind of database auto-partition management method based on horizontal partitioning and is
System.Can be efficient zoned, while improving database access performance, manual intervention be reduced to the greatest extent, simplify division operation, and
The normal operation of business on line is had no effect.
Summary of the invention
In order to compensate for the shortcomings of the prior art, the present invention provides a kind of databases based on horizontal partitioning being simple and efficient
Auto-partition management method and system.
The present invention is achieved through the following technical solutions:
A kind of database auto-partition management method based on horizontal partitioning, it is characterised in that: flexibly match by business demand
Zone attribute is set, executes division operation automatically, task schedule is carried out automatically according to the configuration period, is met by periodic inspection
The zone object of time interval carries out creation, deletion and the data dump of subregion according to configuration information, once matches and postpone centre
Without manual intervention, and then realize auto-partition management；Specifically includes the following steps:
(1) partition creating is carried out to specified tables of data, field and/or period；Available data is stored in first subregion, it will
The data in first time point to the second time point section are stored in second subregion；
(2) to time range outside data carry out subregion deletion, reduce the data scale of tables of data；To needing persistence
It is file that data, which carry out partition data dump,；
(3) configuration timing executes the period, executes division operation automatically, realizes subregion scheduler task；
(4) log produced by record partitioning operates is used for problem backtracking and investigation；
(5) it when critical data table subregion breaks down, issues alarm to database administrator in time.
It, can be to specified tables of data, control field, time interval and/or list when creating new subregion in the step (1)
Subzone number is configured；When creating the division for the first time, point an of current time is created to the control field of specified tables of data
It successively creates the division if the single number of partitions is more than one according to time interval in area.
It, can be to specified tables of data, control field and/or most Kubo when deleting subregion and its data in the step (2)
The time is stayed to be configured；When the task schedule time meeting the retention time at most of subregion, subregion and its data are deleted together.
It, can be to specified tables of data, subregion, document form, storage when partition data is exported in the step (2)
Position and/or dump time are configured；When the task schedule time meeting the dump time, by the data on subregion with SQL or
Excel document form exports to storage location.
In the step (3), system according to definition dispatching cycle timing execute task, dispatching cycle both can daily or
It is executed by lunar periodicity, also can specify what day or exact date；Between the scheduling execution time meets the time of division operation
Every when, it will triggering division operation.
In the step (4), the calling of storing process is to obtain resource pair by interface by shell script execution
The user name answered, password and SSH protocol port information, foundation are connect with the SSH of target resource, make shell script in target
It is executed in resource, and returns to implementing result；While shell script calls storing process, database type information is written
Journal file, system analysis collection result, and realize parsing storage.
In the step (5), fault verification rule relies on regular expression, is adjusted flexibly according to specific business need, when
It was found that after notifying related personnel, failure to solve in time by mail or short message form, adjustment state is to release failure when failure.
The management system of the database auto-partition management method based on horizontal partitioning, including partition management module are adjusted
Task module, logger module and fault warning module are spent, the partition management module is deleted and divided by the submodule that creates the division
Area's submodule and dump data submodule are constituted；
The Technical Architecture bottom of the management system is MySQL database, storing process and SQL statement is supported, by depositing
Storage process and SQL statement realize the concrete operations to subregion, call the storing process and incoming parameter to be completed by shell script,
Operation layer includes common Development Framework Spring, task schedule Quartz and the Alert for alarm, the patrol task
Scheduling is executed and is realized by way of Development Framework Spring+ task schedule Quartz, and top layer is then user's presentation layer.
The beneficial effects of the present invention are: being somebody's turn to do database auto-partition management method and system based on horizontal partitioning, not only
The efficient zoned of MySQL database can be realized while not influencing the normal operation of business on line, improve database visit
Ask performance, while also reducing manual intervention, simplify division operation, can be avoided artificial mistake write accidentally write caused by hidden danger, section
A large amount of human costs are saved.
Detailed description of the invention
Attached drawing 1 is that the present invention is based on the database auto-partition management method schematic diagrames of horizontal partitioning.
Attached drawing 2 is that the present invention is based on the database auto-partition management system the functional block diagrams of horizontal partitioning.
Attached drawing 3 is that the present invention is based on the database auto-partition management system technical framework diagrams of horizontal partitioning.
Specific embodiment
In order to which technical problems, technical solutions and advantages to be solved are more clearly understood, tie below
Drawings and examples are closed, the present invention will be described in detail.It should be noted that specific embodiment described herein is only used
To explain the present invention, it is not intended to limit the present invention.
The database auto-partition management method based on horizontal partitioning, by business demand flexible configuration zone attribute, certainly
It is dynamic to execute division operation, task schedule is carried out automatically according to the configuration period, and point of time interval is met by periodic inspection
Area's object carries out creation, deletion and the data dump of subregion according to configuration information, primary intermediate without artificial dry with postponing
In advance, and then auto-partition management is realized；Specifically includes the following steps:
(1) partition creating is carried out to specified tables of data, field and/or period；Available data is stored in first subregion, it will
The data in first time point to the second time point section are stored in second subregion；
(2) to time range outside data carry out subregion deletion, reduce the data scale of tables of data；To needing persistence
It is file that data, which carry out partition data dump,；
(3) configuration timing executes the period, executes division operation automatically, realizes subregion scheduler task；
(4) log produced by record partitioning operates is used for problem backtracking and investigation；
(5) it when critical data table subregion breaks down, issues alarm to database administrator in time.
It, can be to specified tables of data, control field, time interval and/or list when creating new subregion in the step (1)
Subzone number is configured；When creating the division for the first time, point an of current time is created to the control field of specified tables of data
It successively creates the division if the single number of partitions is more than one according to time interval in area.
It, can be to specified tables of data, control field and/or most Kubo when deleting subregion and its data in the step (2)
The time is stayed to be configured；When the task schedule time meeting the retention time at most of subregion, subregion and its data are deleted together.
It, can be to specified tables of data, subregion, document form, storage when partition data is exported in the step (2)
Position and/or dump time are configured；When the task schedule time meeting the dump time, by the data on subregion with SQL or
Excel document form exports to storage location.
In the step (3), system according to definition dispatching cycle timing execute task, dispatching cycle both can daily or
It is executed by lunar periodicity, also can specify what day or exact date；Between the scheduling execution time meets the time of division operation
Every when, it will triggering division operation.
In the step (4), the calling of storing process is to obtain resource pair by interface by shell script execution
The user name answered, password and SSH (Secure Shell, containment agreement) protocol port information is established and target resource
SSH connection, executes shell script on target resource, and return to implementing result；Storing process is called in shell script
Meanwhile journal file, system analysis collection result is written into database type information, and realize parsing storage.
In the step (5), fault verification rule relies on regular expression, is adjusted flexibly according to specific business need, when
It was found that after notifying related personnel, failure to solve in time by mail or short message form, adjustment state is to release failure when failure.
The management system of the database auto-partition management method based on horizontal partitioning, including partition management module are adjusted
Task module, logger module and fault warning module are spent, the partition management module is deleted and divided by the submodule that creates the division
Area's submodule and dump data submodule are constituted；
MySQL database operation need realized by SQL statement, but SQL statement be it is non-procedural, only operate and
Operating procedure is not described, and storing process (Stored Procedure) is one group of SQL statement in order to complete specific function
Collection.It is made of SQL statement and some control structures, has certain logical capability, and the name of specified storing process can be passed through
Simultaneously given parameters (if the storing process has parameter) execute word to call, simultaneously because SQL statement is precompile, so
It is suitable technical solution that the specific functions such as division operation are encapsulated with SQL statement.
The Technical Architecture bottom of the management system is MySQL database, storing process and SQL statement is supported, by depositing
Storage process and SQL statement realize the concrete operations to subregion, call the storing process and incoming parameter to be completed by shell script,
Operation layer includes common Development Framework Spring, task schedule Quartz and the Alert for alarm, the patrol task
Scheduling is executed and is realized by way of Development Framework Spring+ task schedule Quartz, and top layer is then user's presentation layer.
With the entire flow for illustrating division operation for embodiment that creates the division.
User issues the request created the division first, then to tables of data, control field, time interval, the single number of partitions
Etc. information configured；Then, scheduler task is opened, is periodically executed and creates the division, is met between the time of subregion if executing the time
Every, it will the creation of subregion is carried out according to configured partition information, and records execution journal, otherwise will be skipped；If executing
It breaks down in journey, then issues the alarm such as mail or short message to related personnel, otherwise terminate.
It compared with prior art, should be based on the database auto-partition management method of horizontal partitioning and the beneficial effect of system
It is mainly reflected in the following aspects:
First is that carrying out division operation automatically, in the flexible configuration period, not will cause new data can not be written expected subregion, even
Data lose the case where not losing；
Second is that enriched to original sectoring function, the reality that single creates multiple subregions not only is increased to creating the division
Existing, also increasing partition data dump is the realization that document form saves；
Third is that database administrator need not writing directly facing SQL statement again, it is only necessary to specified tables of data, control field
Equal configuration informations, simplify division operation, and greatly reduce artificial mistake and write the hidden danger accidentally write；
Fourth is that primary configuration can periodical automatic running without artificial repetitive operation save a large amount of human costs.
Claims (8)
1. a kind of database auto-partition management method based on horizontal partitioning, it is characterised in that: press business demand flexible configuration
Zone attribute executes division operation automatically, task schedule is carried out automatically according to the configuration period, when meeting by periodic inspection
Between the zone object that is spaced, carry out creation, deletion and the data dump of subregion according to configuration information, it is primary with postponing intermediate nothing
Manual intervention is needed, and then realizes auto-partition management；Specifically includes the following steps:
(1) partition creating is carried out to specified tables of data, field and/or period；Available data is stored in first subregion, by first
The data in time point to the second time point section are stored in second subregion；
(2) to time range outside data carry out subregion deletion, reduce the data scale of tables of data；To the data for needing persistence
Progress partition data dump is file；
(3) configuration timing executes the period, executes division operation automatically, realizes subregion scheduler task；
(4) log produced by record partitioning operates is used for problem backtracking and investigation；
When critical data table subregion breaks down, issues alarm to database administrator in time.
2. the database auto-partition management method according to claim 1 based on horizontal partitioning, it is characterised in that: described
In step (1), specified tables of data, control field, time interval and/or the single number of partitions can be carried out when creating new subregion
Configuration；When creating the division for the first time, the subregion of a current time is created to the control field of specified tables of data, if single subregion
Number is more than one, then successively creates the division according to time interval.
3. the database auto-partition management method according to claim 1 based on horizontal partitioning, it is characterised in that: described
In step (2), subregion is deleted and its can be to specified tables of data when data, control field and/or retention time is matched at most
It sets；When the task schedule time meeting the retention time at most of subregion, subregion and its data are deleted together.
4. the database auto-partition management method according to claim 1 or 2 based on horizontal partitioning, it is characterised in that:
In the step (2), when partition data is exported can to specified tables of data, subregion, document form, storage location and/
Or the dump time is configured；When the task schedule time meeting the dump time, by the data on subregion with SQL or excel text
Part form exports to storage location.
5. the database auto-partition management method according to claim 1 based on horizontal partitioning, it is characterised in that: described
In step (3), system executes task according to timing dispatching cycle of definition, can both hold daily or by lunar periodicity dispatching cycle
What day or exact date row, also can specify；When scheduling, which executes the time, meets the time interval of division operation, it will triggering
Division operation.
6. the database auto-partition management method according to claim 1 based on horizontal partitioning, it is characterised in that: described
In step (4), the calling of storing process is to obtain the corresponding user name of resource by interface by shell script execution, close
Code and SSH protocol port information, foundation are connect with the SSH of target resource, execute shell script on target resource, and
Return to implementing result；While shell script calls storing process, journal file, system is written into database type information
Collection result is parsed, and realizes parsing storage.
7. the database auto-partition management method according to claim 1 based on horizontal partitioning, it is characterised in that: described
In step (5), fault verification rule relies on regular expression, is adjusted flexibly according to specific business need, when a fault is found, leads to
It crosses mail or short message form notifies related personnel in time, after failure solution, adjustment state is to release failure.
8. the management system of the database auto-partition management method according to claims 1 to 7 based on horizontal partitioning,
It is characterized in that: including partition management module, scheduler task module, logger module and fault warning module, the subregion pipe
Module is managed by the submodule that creates the division, subregion submodule is deleted and dump data submodule is constituted；
The Technical Architecture bottom of the management system is MySQL database, storing process and SQL statement is supported, by storing
Journey and SQL statement realize the concrete operations to subregion, call storing process and incoming parameter to be completed by shell script, business
Layer includes common Development Framework Spring, task schedule Quartz and the Alert for alarm, the scheduling of the patrol task
Execution realizes that top layer is then user's presentation layer by way of Development Framework Spring+ task schedule Quartz.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910242443.4A CN109977145A (en) | 2019-03-28 | 2019-03-28 | A kind of database auto-partition management method and system based on horizontal partitioning |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910242443.4A CN109977145A (en) | 2019-03-28 | 2019-03-28 | A kind of database auto-partition management method and system based on horizontal partitioning |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN109977145A true CN109977145A (en) | 2019-07-05 |
Family
ID=67081231
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910242443.4A Pending CN109977145A (en) | 2019-03-28 | 2019-03-28 | A kind of database auto-partition management method and system based on horizontal partitioning |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109977145A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111949653A (en) * | 2020-07-03 | 2020-11-17 | 广州博依特智能信息科技有限公司 | Industrial offline calculation scheduling method based on data warehouse hive |
| CN112035482A (en) * | 2020-09-01 | 2020-12-04 | 中国银行股份有限公司 | Service table partitioning method and device |
| CN113934712A (en) * | 2021-12-17 | 2022-01-14 | 昆仑智汇数据科技(北京)有限公司 | Method, device and equipment for processing field model of industrial quality inspection data |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101086732A (en) * | 2006-06-11 | 2007-12-12 | 上海全成通信技术有限公司 | A high magnitude of data management method |
| CN101216821A (en) * | 2007-01-05 | 2008-07-09 | 中兴通讯股份有限公司 | Data acquisition system storage management method |
| US20100250540A1 (en) * | 2009-03-24 | 2010-09-30 | Adda Serge | Method for managing a relational database of the sql type |
| CN105893531A (en) * | 2016-03-31 | 2016-08-24 | 武汉虹信技术服务有限责任公司 | PostgreSQL database mass data management method and system |
| CN106537388A (en) * | 2014-05-31 | 2017-03-22 | 邻客音公司 | Partitioning a database |
-
2019
- 2019-03-28 CN CN201910242443.4A patent/CN109977145A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101086732A (en) * | 2006-06-11 | 2007-12-12 | 上海全成通信技术有限公司 | A high magnitude of data management method |
| CN101216821A (en) * | 2007-01-05 | 2008-07-09 | 中兴通讯股份有限公司 | Data acquisition system storage management method |
| US20100250540A1 (en) * | 2009-03-24 | 2010-09-30 | Adda Serge | Method for managing a relational database of the sql type |
| CN106537388A (en) * | 2014-05-31 | 2017-03-22 | 邻客音公司 | Partitioning a database |
| CN105893531A (en) * | 2016-03-31 | 2016-08-24 | 武汉虹信技术服务有限责任公司 | PostgreSQL database mass data management method and system |
Non-Patent Citations (2)
| Title |
|---|
| 任宏萍: "《面向对象程序设计》", 31 October 2010, 华中科技大学出版社 * |
| 桑园: "《MySQL数据库应用案例教程》", 31 August 2018, 上海交通大学出版社 * |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111949653A (en) * | 2020-07-03 | 2020-11-17 | 广州博依特智能信息科技有限公司 | Industrial offline calculation scheduling method based on data warehouse hive |
| CN112035482A (en) * | 2020-09-01 | 2020-12-04 | 中国银行股份有限公司 | Service table partitioning method and device |
| CN112035482B (en) * | 2020-09-01 | 2023-08-18 | 中国银行股份有限公司 | Service table partitioning method and device |
| CN113934712A (en) * | 2021-12-17 | 2022-01-14 | 昆仑智汇数据科技(北京)有限公司 | Method, device and equipment for processing field model of industrial quality inspection data |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109460349B (en) | Test case generation method and device based on log | |
| CN100487700C (en) | Data processing method and system of data library | |
| CN109977145A (en) | A kind of database auto-partition management method and system based on horizontal partitioning | |
| CN111061788B (en) | Multi-source heterogeneous data conversion integration system based on cloud architecture and implementation method thereof | |
| CN113111129B (en) | Data synchronization method, device, equipment and storage medium | |
| CN109271435A (en) | A kind of data pick-up method and system for supporting breakpoint transmission | |
| CN109885642B (en) | Hierarchical storage method and device for full-text retrieval | |
| CN107103064B (en) | Data statistical method and device | |
| CN112148788A (en) | Data synchronization method and system for heterogeneous data source | |
| CN105069134A (en) | Method for automatically collecting Oracle statistical information | |
| CN105184166A (en) | Kernel-based Android application real-time behavior analysis method and system | |
| CN101944114A (en) | Data synchronization method between memory database and physical database | |
| CN101707399A (en) | Method and system for acquiring electric energy information | |
| CN112199394A (en) | Alarm information pushing method and system, intelligent terminal and storage medium | |
| CN104572856A (en) | Converged storage method of service source data | |
| CN115587118A (en) | Task data dimension table association processing method and device and electronic equipment | |
| CN117149873A (en) | Data lake service platform construction method based on flow batch integration | |
| CN113793213B (en) | Method and device for implementing decision mode of asynchronous credit wind control breakpoint continuous operation | |
| CN116089027A (en) | Non-blocking distributed scheduling task scheduling method based on JVM | |
| CN110515833A (en) | A kind of method of software test, equipment and readable medium | |
| CN118152450A (en) | Data communication method, equipment and medium for unidirectional network isolation environment | |
| CN111782618B (en) | Data synchronization system | |
| CN112988722A (en) | Hive partition table data cleaning method and device and storage medium | |
| CN117370337A (en) | Partition creation method, partition creation device, computer equipment and storage medium | |
| CN116578570A (en) | Method, system and equipment for automatically optimizing table data structure layout |
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 | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20190705 |
|
| RJ01 | Rejection of invention patent application after publication |