CN106991174A - A kind of optimization method of Smart Rack system databases - Google Patents
A kind of optimization method of Smart Rack system databases Download PDFInfo
- Publication number
- CN106991174A CN106991174A CN201710218183.8A CN201710218183A CN106991174A CN 106991174 A CN106991174 A CN 106991174A CN 201710218183 A CN201710218183 A CN 201710218183A CN 106991174 A CN106991174 A CN 106991174A
- Authority
- CN
- China
- Prior art keywords
- database
- data
- optimization method
- reduce
- rack system
- 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
- 238000000034 method Methods 0.000 title claims abstract description 33
- 238000005457 optimization Methods 0.000 title claims abstract description 12
- 230000003993 interaction Effects 0.000 claims abstract description 14
- 230000008569 process Effects 0.000 claims description 18
- 238000012545 processing Methods 0.000 claims description 15
- 230000009467 reduction Effects 0.000 claims description 4
- 238000004364 calculation method Methods 0.000 claims description 3
- 238000012216 screening Methods 0.000 claims description 3
- 230000006870 function Effects 0.000 description 6
- 230000005540 biological transmission Effects 0.000 description 4
- 230000008859 change Effects 0.000 description 3
- 238000005516 engineering process Methods 0.000 description 3
- 235000012364 Peperomia pellucida Nutrition 0.000 description 2
- 240000007711 Peperomia pellucida Species 0.000 description 2
- 238000004458 analytical method Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 2
- 230000008901 benefit Effects 0.000 description 2
- 238000012790 confirmation Methods 0.000 description 2
- 230000007423 decrease Effects 0.000 description 2
- 238000000151 deposition Methods 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 230000000977 initiatory effect Effects 0.000 description 2
- 238000007689 inspection Methods 0.000 description 2
- 230000009897 systematic effect Effects 0.000 description 2
- 238000010276 construction Methods 0.000 description 1
- 230000007812 deficiency Effects 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 230000008520 organization Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24532—Query optimisation of parallel queries
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention relates to a kind of optimization method of Smart Rack system databases, it is characterised in that comprises the following steps：Step 1）：Reduce data access amount；Step 2）：Reduce the data volume returned；Step 3）：Reduce interaction times；Step 4）：Reduce database server CPU operands.The technical scheme provided by the present invention, can quickly position the bottleneck point of energy property, the main expense position of database is quickly found out, so as to be quickly found out the performance bottleneck point of database and be handled, to improve the operational efficiency of whole database.
Description
Technical field
The invention belongs to Smart Rack database fields, and in particular to a kind of optimization of Smart Rack system databases
Method.
Background technology
Database is the general data processing system of a unit or an application field, and what it was stored is to belong to enterprise
With operating divisions, the set of the relevant data of organization and individual.Data in database are to share its information by numerous users
And set up, have been extricated from the limitation and restriction of specific procedure.Different users can use database by respective usage
In data；Multiple users can simultaneously in shared data bank data resource, i.e., different users can access data simultaneously
Same data in storehouse.Data sharing not only meets requirement of each user to the information content, while also meeting each use
The requirement that information communicates between family.
Smart Rack are unified that the server node that it is carried is powered, radiated using standard cabinet as designing unit
And management, it is widely used in the fields such as big data cloud computing, it is necessary to which often reply comes from the substantial amounts of data directory in internet end, example
As 12306 websites in peak period face hundreds of millions of access, often result in network congestion, website collapse etc. delay machine phenomenon when have
Occur.This is the deficiencies in the prior art part.
The content of the invention
It is an object of the present invention in view of the above-mentioned drawbacks of the prior art, provide design a kind of Smart Rack systems
The optimization method of system database, to solve above-mentioned technical problem.
To achieve the above object, the present invention provides following technical scheme：
A kind of optimization method of Smart Rack system databases, it is characterised in that comprise the following steps：
Step 1）：Reduce data access amount；
Step 2）：Reduce the data volume returned；
Step 3）：Reduce interaction times；
Step 4）：Reduce database server CPU operands.
Preferably, being comprised the steps of in described step 1：
Step 1.1）：Create and use correct index；The real executive plan of database is checked in database；According to true
Executive plan to determine that index is known no proper use of；
Step 1.2）：Only pass through index accesses data
Several fields in table are simply accessed, and field contents are less, can individually set up a combination for these fields
Index, thus can be directly only by access index with regard to that can obtain data, and the disk space that general index takes is smaller than table very
Many, institute can greatly reduce disk I/O expense by this way.
Step 1.3）：Optimize database executive plan
Database executive plan is one of most crucial technology of relevant database, and it represents data access when database is performed
Algorithm.Because business demand becomes increasingly complex, table data volume is also increasing, and database is also required to support extremely complex business
Logic, but the performance of database also needs to raising, and therefore, outstanding relevant database is except needing to support complicated database
Outside grammer and more polygamma function, in addition it is also necessary to there is outstanding algorithms library to improve database performance.
Step 2.1）：Data page processing, and specifically comprise the steps of：
Client (application program or browser) paging, by data from the whole locally downloading application program or clear of application server
Look at device, paging processing is carried out by local code inside application program or browser, this method coding is simple, it is possible to reduce visitor
Family end and application server network interaction number of times；
Data are all downloaded to application server, inside application server by application server paging from database server
Data screening is carried out again.Advantage is that coding is simple, it is only necessary to a database interaction, total data and the similar when property of paged data
Can be preferably；
Database paging, database completion twice is needed using database paging
Step 2.2）：Only return to the field needed
The return field unnecessary by removing can improve performance, if having big field or content more in your inquiry table
Field, such as remark information, file content, that is certain in inquiry table it is noted that the problem of this respect, otherwise may band
Carry out serious performance issue；If table will often be inquired about and ask the probability of big content field very low, we, which can use, divides
List processing, two man-to-man relation tables are split into by one big table, and the big content field being of little use is placed on into one individually
In table.
Step 3.1）：Using batch DML
Database access framework typically both provides the interface that batch is submitted, and many numbers will not be typically reduced using batch operations
According to the physics IO of storehouse server, but the interaction times of client and service end can be greatly reduced, so as to reduce multiple initiation
Network delay expense, while can also reduce the CPU overhead of database；
Step 3.2）：Inquired about using In List
Many times need to inquire about data-base recording by some ID, we can issue database using mono- request of an ID,
The quantity of database request can be greatly reduced by so handling, so as to improve performance；Assessment once puts how many inside IN
Individual value also needs to consider the expense of application server local memory, and having will calculate in local data usage cycles when concurrently accessing
The concurrent upper limit；
Step 3.3）：Fetch Size are set
When using select from data base querying data, data acquiescence is according to client fetch_size parameter processings, often
Secondary returns to fetch_size bars record, is fetched evidence from service end again when client cursor traverses afterbody, to the last entirely
Portion's transmission is completed.If, so can be with so when we once will take mass data from service end, fetch_size can be increased
Interaction times and the server data time of result data transmission are reduced, performance is improved.
Step 3.4）：Use storing process
Large database typically all supports storing process, rational to improve systematic function using storing process, using depositing
Whole service logic can be encapsulated in storing process by storage process, then directly invoke storing process processing in client, this
Sample can reduce the cost of network interaction.
Step 4.1）：Use binding variable
Binding variable refers to submit the value of change in the form of variable parameter in database, why can be carried using binding variable
High database analysis feature, this needs to perform principle explanation from database, when a data storehouse is sent to database server
Afterwards, database character string can be carried out hash computings by system first, be obtained after hash values again from the database in server memory
Retrieved in buffer area, if identical database character, and confirmation is the database statement of same logic, then from altogether
Enjoy and the corresponding executive plan of database is taken out in the caching of pond, data are read according to executive plan and client is returned result to；
）：Reasonable employment sorts
When record set increases to more than up to ten thousand, big record set sequence not only increases CPU overhead, and may be due to interior
The not enough phenomenon for occurring hard disk sequence is deposited, performance can drastically decline when occurring hard disk sequence.
）：Operation is compared in reduction
The service logic of database often compares operation comprising some, all embodies very for these comparison operating databases
Good, Like fuzzy queries are not to be good at very much for database, have more than up to ten thousand in particular for the record of fuzzy inspection
When, such case typically using special Search or can improve performance using full-text index scheme
Step 4.4）：Complex calculation is by client process
A large amount of this kind of complicated function computings, are placed on client process, 10,000-10 ten thousand times can only be also handled during general CPU is per second so
Functional operation, be placed in database be unfavorable for high concurrent processing.
The beneficial effects of the present invention are the technical scheme provided by the present invention can quickly position the bottleneck of energy property
Point, is quickly found out the main expense position of database, so as to be quickly found out the performance bottleneck point of database and be handled, to carry
The operational efficiency of high whole database.In addition, design principle of the present invention is reliable, and it is simple in construction, before application widely
Scape.
As can be seen here, the present invention compared with prior art, improves with prominent substantive distinguishing features and significantly, and it is implemented
Beneficial effect be also obvious.
Embodiment
Below by specific embodiment, the present invention will be described in detail, and following examples are explanation of the invention, and
The invention is not limited in implementation below.
The present invention provides following examples：
A kind of optimization method of Smart Rack system databases, it is characterised in that comprise the following steps：
Step 1）：Reduce data access amount；
Step 2）：Reduce the data volume returned；
Step 3）：Reduce interaction times；
Step 4）：Reduce database server CPU operands.
Preferably, being comprised the steps of in described step 1：
Step 1.1）：Create and use correct index；The real executive plan of database is checked in database；According to true
Executive plan to determine that index is known no proper use of；
Step 1.2）：Only pass through index accesses data
Several fields in table are simply accessed, and field contents are less, can individually set up a combination for these fields
Index, thus can be directly only by access index with regard to that can obtain data, and the disk space that general index takes is smaller than table very
Many, institute can greatly reduce disk I/O expense by this way.
Step 1.3）：Optimize database executive plan
Database executive plan is one of most crucial technology of relevant database, and it represents data access when database is performed
Algorithm.Because business demand becomes increasingly complex, table data volume is also increasing, and database is also required to support extremely complex business
Logic, but the performance of database also needs to raising, and therefore, outstanding relevant database is except needing to support complicated database
Outside grammer and more polygamma function, in addition it is also necessary to there is outstanding algorithms library to improve database performance.
In the present embodiment, described step 2）Comprise the steps of：
Step 2.1）：Data page processing, and specifically comprise the steps of：
Client (application program or browser) paging, by data from the whole locally downloading application program or clear of application server
Look at device, paging processing is carried out by local code inside application program or browser, this method coding is simple, it is possible to reduce visitor
Family end and application server network interaction number of times；
Data are all downloaded to application server, inside application server by application server paging from database server
Data screening is carried out again.Advantage is that coding is simple, it is only necessary to a database interaction, total data and the similar when property of paged data
Can be preferably；
Database paging, database completion twice is needed using database paging；
Step 2.2）：Only return to the field needed
The return field unnecessary by removing can improve performance, if having big field or content more in your inquiry table
Field, such as remark information, file content, that is certain in inquiry table it is noted that the problem of this respect, otherwise may band
Carry out serious performance issue；If table will often be inquired about and ask the probability of big content field very low, we, which can use, divides
List processing, two man-to-man relation tables are split into by one big table, and the big content field being of little use is placed on into one individually
In table.
The step 3）Include step in detail below：
Step 3.1）：Using batch DML
Database access framework typically both provides the interface that batch is submitted, and many numbers will not be typically reduced using batch operations
According to the physics IO of storehouse server, but the interaction times of client and service end can be greatly reduced, so as to reduce multiple initiation
Network delay expense, while can also reduce the CPU overhead of database；
Step 3.2）：Inquired about using In List
Many times need to inquire about data-base recording by some ID, we can issue database using mono- request of an ID,
The quantity of database request can be greatly reduced by so handling, so as to improve performance；Assessment once puts how many inside IN
Individual value also needs to consider the expense of application server local memory, and having will calculate in local data usage cycles when concurrently accessing
The concurrent upper limit；
Step 3.3）：Fetch Size are set
When using select from data base querying data, data acquiescence is according to client fetch_size parameter processings, often
Secondary returns to fetch_size bars record, is fetched evidence from service end again when client cursor traverses afterbody, to the last entirely
Portion's transmission is completed.If, so can be with so when we once will take mass data from service end, fetch_size can be increased
Interaction times and the server data time of result data transmission are reduced, performance is improved.
Step 3.4）：Use storing process
Large database typically all supports storing process, rational to improve systematic function using storing process, using depositing
Whole service logic can be encapsulated in storing process by storage process, then directly invoke storing process processing in client, this
Sample can reduce the cost of network interaction.
Described step 4）Specifically comprise the steps of：
Step 4.1）：Use binding variable
Binding variable refers to submit the value of change in the form of variable parameter in database, why can be carried using binding variable
High database analysis feature, this needs to perform principle explanation from database, when a data storehouse is sent to database server
Afterwards, database character string can be carried out hash computings by system first, be obtained after hash values again from the database in server memory
Retrieved in buffer area, if identical database character, and confirmation is the database statement of same logic, then from altogether
Enjoy and the corresponding executive plan of database is taken out in the caching of pond, data are read according to executive plan and client is returned result to；
Step 4.2）：Reasonable employment sorts
When record set increases to more than up to ten thousand, big record set sequence not only increases CPU overhead, and may be due to interior
The not enough phenomenon for occurring hard disk sequence is deposited, performance can drastically decline when occurring hard disk sequence.
Step 4.3）：Operation is compared in reduction
The service logic of database often compares operation comprising some, all embodies very for these comparison operating databases
Good, Like fuzzy queries are not to be good at very much for database, have more than up to ten thousand in particular for the record of fuzzy inspection
When, such case typically using special Search or can improve performance using full-text index scheme.
Step 4.4）：Complex calculation is by client process
A large amount of this kind of complicated function computings, are placed on client process, 10,000-10 ten thousand times can only be also handled during general CPU is per second so
Functional operation, be placed in database be unfavorable for high concurrent processing.
Disclosed above is only the preferred embodiment of the present invention, but the present invention is not limited to this, any this area
What technical staff can think does not have a creative change, and some improvement made without departing from the principles of the present invention and
Retouching, should all be within the scope of the present invention.
Claims (5)
1. a kind of optimization method of Smart Rack system databases, it is characterised in that comprise the following steps：Step 1）：Reduction
Data access amount；Step 2）：Reduce the data volume returned；Step 3）：Reduce interaction times；Step 4）：Reduce database service
Device CPU operands.
2. the optimization method of a kind of Smart Rack system databases according to claim 1, it is characterised in that described
Comprised the steps of in step 1：Step 1.1）：Create and use correct index；Step 1.2）：Only pass through index accesses number
According to；Step 1.3）：Optimize database executive plan.
3. a kind of optimization method of Smart Rack system databases according to claim 1 or 2, it is characterised in that institute
The step 2 stated）Comprise the steps of：Step 2.1）：Data page processing, and specifically comprise the steps of：Client paging,
By data from application server all locally downloading application program or browser, this is passed through inside application program or browser
Ground code carries out paging processing；Data are all downloaded to application server by application server paging from database server,
Data screening is carried out inside application server again；Database paging, database completion twice is needed using database paging；Step
2.2）：Only return to the field needed.
4. a kind of optimization method of Smart Rack system databases according to claim 3, it is characterised in that the step
Rapid 3）Include step in detail below：Step 3.1）：Using batch DML；Step 3.2）：Inquired about using In List；Step
3.3）：Fetch Size are set；Step 3.4）：Use storing process.
5. the optimization method of a kind of Smart Rack system databases according to claim 4, it is characterised in that described
Step 4）Specifically comprise the steps of：Step 4.1）：Use binding variable；Step 4.2）：Reasonable employment sorts；Step 4.3）：
Operation is compared in reduction；Step 4.4）：Complex calculation is by client process.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710218183.8A CN106991174A (en) | 2017-04-05 | 2017-04-05 | A kind of optimization method of Smart Rack system databases |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710218183.8A CN106991174A (en) | 2017-04-05 | 2017-04-05 | A kind of optimization method of Smart Rack system databases |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN106991174A true CN106991174A (en) | 2017-07-28 |
Family
ID=59415422
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710218183.8A Pending CN106991174A (en) | 2017-04-05 | 2017-04-05 | A kind of optimization method of Smart Rack system databases |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106991174A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110119364A (en) * | 2018-02-06 | 2019-08-13 | 北京京东尚科信息技术有限公司 | A kind of method and system that input/output batch is submitted |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103853727A (en) * | 2012-11-29 | 2014-06-11 | 深圳中兴力维技术有限公司 | Method and system for improving large data volume query performance |
| US8825629B2 (en) * | 2003-09-06 | 2014-09-02 | Oracle International Corporation | Method for index tuning of a SQL statement, and index merging for a multi-statement SQL workload, using a cost-based relational query optimizer |
-
2017
- 2017-04-05 CN CN201710218183.8A patent/CN106991174A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8825629B2 (en) * | 2003-09-06 | 2014-09-02 | Oracle International Corporation | Method for index tuning of a SQL statement, and index merging for a multi-statement SQL workload, using a cost-based relational query optimizer |
| CN103853727A (en) * | 2012-11-29 | 2014-06-11 | 深圳中兴力维技术有限公司 | Method and system for improving large data volume query performance |
Non-Patent Citations (1)
| Title |
|---|
| XIANMIN_YAN: ""数据库性能优化详解"", 《CSDN-HTTPS://BLOG.CSDN.NET/YZLLZ001、ARTICLE/DETAILS/54848513》 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110119364A (en) * | 2018-02-06 | 2019-08-13 | 北京京东尚科信息技术有限公司 | A kind of method and system that input/output batch is submitted |
| CN110119364B (en) * | 2018-02-06 | 2023-04-07 | 北京京东尚科信息技术有限公司 | Method and system for input/output batch submission |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11620400B2 (en) | Querying in big data storage formats | |
| US20190258625A1 (en) | Data partitioning and ordering | |
| Zhao et al. | Dache: A data aware caching for big-data applications using the MapReduce framework | |
| US20160140152A1 (en) | Intelligent compiler for parallel graph processing | |
| CN105183735B (en) | The querying method and inquiry unit of data | |
| US11449507B2 (en) | Database engine | |
| US8812489B2 (en) | Swapping expected and candidate affinities in a query plan cache | |
| US9298775B2 (en) | Changing the compression level of query plans | |
| US20160147862A1 (en) | Delegation of Database Post-Commit Processing | |
| US8924373B2 (en) | Query plans with parameter markers in place of object identifiers | |
| EP1808779B1 (en) | Bundling database | |
| US11698893B2 (en) | System and method for use of lock-less techniques with a multidimensional database | |
| AU2017243870B2 (en) | "Methods and systems for database optimisation" | |
| US10783142B2 (en) | Efficient data retrieval in staged use of in-memory cursor duration temporary tables | |
| US20130290295A1 (en) | Maintaining fault domains in a distributed database | |
| US11645281B1 (en) | Caching query plans in database systems | |
| Liu et al. | Mostly order preserving dictionaries | |
| US8812523B2 (en) | Predicate result cache | |
| Patel et al. | Workload aware Cost-based Partial loading of Raw data for Limited Storage Resources | |
| CN106991174A (en) | A kind of optimization method of Smart Rack system databases | |
| Orhean et al. | Scanns: Towards scalable and concurrent data indexing and searching in high-end computing system | |
| CN113157692B (en) | Relational memory database system | |
| Gupta et al. | Pragamana: performance comparison and programming alpha-miner algorithm in relational database query language and NoSQL column-oriented using apache phoenix | |
| US9841905B2 (en) | File system having a database management system using file pre-allocation in mass storage devices | |
| Rahman et al. | Optimizing and enhancing performance of database engine using data clustering technique |
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: 20170728 |
|
| RJ01 | Rejection of invention patent application after publication |