Database learning, database

Database learning, database

• Large-scale concurrency
  • Optimize server configurations
  • Use Server Load balancer
  • Database Structure Design
  • Middleware Optimization
  • Data Cache Usage
• High database concurrency
  • Database concurrency Policy
• Database Design Suggestions
  • Table Design Problems
  • Query Optimization
  • Notes
  • Algorithm Optimization

Large-scale concurrency

When many people access the website, the concurrency increases, and the website we designed will face challenges. There are many designs that are not problematic, and problems may be exposed at this time.

Just as the macroscopic physical theorem is not suitable for microscopic physics.
The following are some optimization suggestions.

Optimize server configurations

This is to improve the server on the hardware, because any software optimization has bottlenecks and limits, so increasing the number and configuration of servers is a simple and effective solution. If the performance is not good, the quantity is guaranteed. It is better to have another instance as redundant to ensure server faults and improve robustness.

Use Server Load balancer

This word is very popular nowadays, because many websites are collecting a large amount of data, so the data concurrency is also very good. Server Load balancer is the core technology to solve centralized concurrent access and is also a more effective method.

The main device is the Server Load balancer server. The Server Load balancer software is installed to distribute the High-concurrency server generated by large-scale users to various servers.

Database Structure Design

This part also contains a lot of content, generally optimizing the database structure and database statements. Below I will detail the database optimization methods.

Middleware Optimization

I was not very familiar with this part, and I was not very studious at the time.
Apache, IIS, Tomact, and WebLogin are all middleware.

Data Cache Usage

Cache is used to Cache common database data, reducing the pressure on the server to request again. Increase speed.

High database concurrency

When the database is highly concurrent, you need to find the bottleneck first. CPU, IO, or network.
So we can have the following solutions:

• Increase network bandwidth
• High-performance database servers and Web servers
• Optimize paging Data Access
• Stable and high-performance database access layer
• Optimize database cache and use the memory cache to process data
• Database INDEX OPTIMIZATION
• Rigorous transaction processing design
• Logs of completed operations are easy to locate errors.
• Add Server Load balancer
• Optimize front-end code
• Consider using compression to transfer HTML and Javascript code.

Database concurrency Policy Database Design Recommendations

Here I write down some of the knowledge I have learned in the book, and some of the database design is simple.
1. Multi-table design.
2. Do not associate the auto-increment attribute with the sub-table as the primary key, which is not convenient for system migration and recovery.

Query Optimization for specific table design issues

Dropping an index will scan the entire table

• Determine the NULL value. (The default value 0 will improve)
• Yes! = Or <> Operator
• Use or link

Note: Algorithm Optimization

Avoid using cursors whenever possible. The set-based method may be more effective.
Create an efficient index.

Large databases generally have two types of indexes: Cluster Index and non-cluster index.

Tables without cluster indexes are stored according to the heap structure. Add the data to the end of the table.

A table has only one cluster index, and data is physically stored in the order of cluster indexes (B tree structure ). Improves the query speed. This will reduce the performance of insert, update, and delete operations.

As shown in the preceding figure. Study Notes.