Database optimization < > database layer and hardware layer overview

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Recently started research database of things, feelings can solve data problems, so the feeling is really very comfortable,, can learn about

Optimization of SQL, this will be a series of courses, learning process, record, later for filing, similarly, technology

No one is the boundary of things, written on behalf of my personal understanding, hope God to the onlookers, advice, appreciate not

As far as

One, overview of database optimization

High performance database depends on several factors, such as query table structure, hardware configuration and software, server settings. The software

Construction led directly to the hardware layer CPU and I/O operation, so, we optimize the strategy, generally, reduce unnecessary disk I/O, as far as possible

The physical structure of tables such as index to quickly access data using, achieve efficient query.

1.1, Optimization of the database layer

Let the database application of the most important factors more quickly of course is the basic design of his, such as:

1,The table structure is reasonable, these displays in: whether the correct data type, if it is a write frequent structure, whether a

Write once, will be updated to a lot of column, the load is, there will be corresponding index need maintenance

2,Whether the correct index usage, this is very important, if used improperly, when the data is big, may be full scan, such

If the memory is not big enough, will sort the operation using the corresponding disk, will be very slow

3,If the storage engine reasonable use, of course, I now use the mysql, for InnoDB, the effect is good, of course,

If you quote for things is not very high, can be considered to be well MyISM, the storage engine speed is really fast, but

Is the safety aspects need to be considered

4,Whether the correct line type, compression expenditure in MySQL type, it will cause the disk I/O and read less write data, of course

His bad place is not on an established index and data

5,If your application uses a locking policy, for the Innodb, uses row level locks, which encourages parallel operation more, if the

The application, if the lock can be moved to the application, use the language characteristics of the corresponding, or system calls such as mutexes, semaphores etc.

To deal with, instead of maintaining the correct data to lock table

6,If the cache cache reasonable use, the cache is more memory paging, big words, will cause a lot of debris can not use,

Therefore, many queries without local cache and cleared before the cache, when the next query, cannot take advantage of caching, in addition, if

Small, will cause the MYSQL to cache management pressure, because no one query may be several pages to cache data, the management of these

Data is needed for a certain overhead

1.2, Optimization of hardware layer

When a database is more busy, the requirements on the hardware is higher, therefore, we should be faster, better identification of system bottlenecks, early

Some decisions, whether to replace or add the corresponding hardware, typical bottleneck appeared in so many places:

1,The disk seek and read and write

Disk seek speed determines the data access speed, and seek speed disk head is limited, therefore, can be considered

Let the data read in parallel, for example in the multiple disk read, this has not been tested, in principle I think feasible, because of the single disk

In fact, he is the implementation of parallel read and write, because the disk spindle drive head rotation, so one can read a cylinder, and the

Most servers are more disk, can consider the configuration database to support multi disc storage, parallel read and write, the more quickly.

2,Memory bandwidth

When CPU need more data to populate the cache, memory may become a bottleneck, can consider to faster and more memory,

Compared with may be better, faster, because the processing speed of CPU than memory speed several orders of magnitude higher, if the memory is fast enough, the relative

For CPU, the waiting time is less, the natural rate is up.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Annie at December 12, 2013 - 4:36 PM