Database Learning-large-scale concurrency optimization learning notes

• Large-scale concurrency
  • Optimize server Configuration
  • Using load Balancing
  • Database structure Design
  • Middleware optimization
  • Data Cache Usage
• High concurrency in the database
  • Database concurrency Policy
• Database Design Recommendations
  • Specific problems with table design
  • Query optimization
  • Precautions
  • Algorithm optimization

Large-scale concurrency

When a lot of people visit the site, concurrency increases, this time we design the site will encounter challenges. A lot of design that is not a problem, this time may be exposed to problems.

Just like the macroscopic physics theorem is not suitable for microscopic.
Here are some recommended ways to optimize.

Optimize server Configuration

This is a hardware upgrade of the server, because any software optimization has bottlenecks and limits, so the increase in the number of servers and configuration is a relatively simple and effective solution. If performance is not very good, then the quantity is guaranteed. It is best to have another one as redundancy to protect the server's failures and improve robustness.

Using load Balancing

This word is very hot now, because many websites are collecting a lot of data, so the data concurrency is also very good. Load balancing is the core technology to solve the centralized concurrent access, and it is also a more effective method.

The main equipment is the Load Balancer server, install load balancer software, the large-scale user generated high concurrency equalization to the various servers.

Database structure Design

This part is also a lot of content, generally is the database structure, database statement optimization. I'll write down the database optimization method in detail below.

Middleware optimization

This part of my contact is not much, at that time also did not study well.
such as Apache, IIS, Tomact, weblogin these are middleware.

Data Cache Usage

Caching is caching the database's common data with the cache, reducing the pressure on the server to re-request. Increase speed.

High concurrency in the database

If the database is high concurrency, look for the bottleneck first. The CPU or the IO or the network.
So we can have some of the following solutions:

• Increase network bandwidth
• High-performance database servers and Web servers
• Optimize Paging Access data
• Stable and high-performance database access layer
• Optimizes database caching and uses memory caching to process data
• Index optimization of the database
• Rigorous design of transaction processing
• Logging of completed operations logs makes it easy to locate errors.
• Increase Load Balancer Server
• Optimizing Front-end code
• Consider transferring HTML and JavaScript code in a compressed manner.

Database concurrency Policy

1. The final result comes into effect. Let the user last modify the results to take effect, reduce the number of operations.
2. Optimistic concurrency. When a conflict occurs only occasionally, the user is informed when a conflict occurs.
3. Pessimistic concurrency. Conflicts often occur, and can not casually let the conflict occur, re-operation, then the user on the table record editing when the lock, not let others to edit.
4. The version number method. When the record is read, the date timestamp or version number is set, and when the record is updated, it is matched. (To ensure accuracy, the trigger should be set on the table).

Database Design Recommendations

Here write down what I usually learn and some of the books on the knowledge, some of the database design is simple.
1. Multi-table design.
2. Do not use the self-increment attribute as the primary key associated with the child table, it is not easy for system migration and recovery.

Specific problems with table design

1. The length of the data row does not exceed 8,020 bytes, and if it is exceeded, the physical page occupies two rows, resulting in storage fragmentation. Reduce query efficiency.
2. The ability to use numeric fields, as much as possible without character types, because string matching is more time-consuming.

Query optimization

1. Reduce the number of database accesses based on the implementation functionality.
2. Reduce the network burden by minimizing the number of rows accessed through the parameters.
3. When querying SQL, try to put the index in the first column.
4. The algorithm structure is as simple as possible.
5. Forgive me for not using SELECT * from table. Don't use *.
6. Multi-use MySQL query optimizer

Operations that discard an index for full table scanning

• The null value is judged. (The default value is 0 improves)
• With! = or the <> operator
• Link with or

Precautions

1. Use with cautionin 和 not in
2. Avoid using non-heading captions in indexed characters. (cannot be accelerated with index)
3. You can use an index acceleration with the Force query optimizer
4. Try to avoid where using function operations on fields in clauses.
5. Do not perform an expression operation on the left side of the WHERE clause = .
6. Use exist if you can.
7. Try to avoid large practical operations. can improve concurrency capabilities.
8. Make full use of the connection conditions.
9. Accelerating queries with views
10. Replace with distinctgroup by
11. union allReplace withunion

Algorithm optimization

The set-based approach may be more efficient if you try to avoid using cursors.
Establish an efficient index.

Large databases typically have two types of indexes: clustered and non-clustered.

Tables that do not have a clustered index are stored according to the heap structure. The data is added to the tail of the table.

A table has only one clustered index, and the data is physically stored in the order of the cluster index (b-tree structure). Improve query speed. Reduces the performance of the Insert update delete operation.

Roughly as above. Study notes.

Database Learning-large-scale concurrency optimization learning notes