Database optimization & amp; lt; 1 & amp; gt; database layer and hardware layer overview _ MySQL

Database optimization amp; lt; 1 amp; gt; database layer and hardware layer overview bitsCN.com

I recently started to study the database. I feel that it is really nice to solve the big data problem. so I can take a look.

SQL optimization, this will be a series of courses, the process of learning, it will be recorded, and later for filing, the same, the technology

It is a thing without borders. it represents my personal understanding. I really hope that the great gods will come here and raise their comments. thank you!

Do

I. database optimization overview

High-performance databases depend on several factors, such as table structures, query statements, server hardware configurations, and software settings. Software

The construction of the hardware layer directly leads to CPU and I/O operations. Therefore, our optimized strategy is generally to reduce unnecessary disk I/O, as far as possible.

Make good use of the physical structure of the table, such as the index, to quickly access data and achieve efficient query.

1.1 Optimization at the database layer

The most important factor in making database applications faster is of course its basic design, such:

1. whether the table structure is reasonable is manifested in: whether the correct data type is used. if it is a frequently written structure, is it once?

A write operation will update many columns. the load is that indexes need to be maintained.

2. whether the correct index is used is very important. if improperly used, all data may be scanned when the data is large.

If the memory is not large enough, the corresponding disk will be used for sorting and other operations, it will be very slow

3. whether a reasonable storage engine is used. of course, the mysql I am using is still good for innodb. of course,

If your reference does not have high requirements on things, you can consider MyISM. this storage engine is really fast,

Security needs to be considered.

4. is the correct row format used? mysql uses the compressed row format, which causes less disk I/O and read/write data. of course

What's bad about him is that he cannot set up indexes and compare data here.

5. whether your application uses a lock policy and uses row-level locks for Innodb. This encourages more parallel operations.

If an application can move a lock to an application, it uses the corresponding language features, or the system calls such as mutex lock and Semaphore.

Instead of locking the table with the correct row of maintenance data.

6. if a reasonable cache is used, the cache will be the same as the pages in the memory. if the cache is larger, a large number of fragments will be unavailable,

Therefore, many queries have no cache and the previous cache is cleared. during the current query, the cache cannot be used. In addition, if

When it is small, it will cause mysql to put more pressure on cache management, because it may cache data for several pages for not one query and manage these

Data requires a certain amount of overhead.

1.2 hardware layer optimization

When the database is more busy, the hardware requirements are higher. Therefore, we should be faster and better able to identify system bottlenecks.

Some decisions, whether to replace or add the corresponding hardware, the typical bottleneck appears in the following areas:

1. disk seek and read/write

The disk tracing speed directly determines the speed of data access, while the disk head tracing speed is limited. therefore, consider

For example, data can be read in parallel on multiple disks. this is not tested yet. in principle, I think it is feasible because it is applicable

In fact, it implements parallel read/write, because the disk's spindle drives the rotation of the head, then one cylindrical can be read at a time, and

Most servers have multiple disks. you can configure the database to support multi-disk storage and parallel read/write, which is faster.

2. memory bandwidth

When the CPU needs more data to fill the cache, the main memory may become a bottleneck, you can consider faster and larger memory,

It may be faster, because the cpu processing speed is several orders of magnitude higher than the memory speed. if the memory is fast enough

For example, the CPU waits for less time, and the natural speed is up.

BitsCN.com