Sqldatabase optimization: cutting, database connection pool-[Examination System], sqldatabase connection

Sqldatabase optimization: cutting, database connection pool-[Examination System], sqldatabase connection

As mentioned in the previous article, developers need to pay attention to cpu and memory. SQL logs cannot be ignored. SQL logs show the error logs of the database operating system, which greatly facilitates troubleshooting.
Some SQL statements that listen for deadlocks and the most resource-consuming statements are written into the database for the exam. You can monitor deadlocks in time and find out which steps are taken in the current exam, including questions, answers, and exams.
Two words are heard: cutting and database connection pool.
Later I checked and found that both methods can optimize the database performance to varying degrees.

I. Cutting

Horizontal cutting:
It is to classify rows. The two commonly used methods are time-based and index-based. Time Division: for example, for five years of historical data, according to the law, the data for one year may be enough to meet most business needs, so we can extract the data for one year separately. In this way, the query efficiency will be higher. Index Division: for example, Department numbers. Each workshop only stores part information of its own workshop, and divides the information of each workshop into separate tables for more convenient query.
　　
Longitudinal cutting:
This is the so-called column cutting. When using big data, the number of columns directly affects the access effect.

Instance segmentation:
It is to abstract the Common Business parts and store them in different databases. For example, account database (storing user information), log database (storing monitoring data )......
　　
Separated instance physical storage:
Big data is stored on different servers for statistics. distributed computing can greatly increase the computing speed.

Ii. Database Connection Pool

As its name implies, it is the pool that stores database connections and has the ability to allocate, manage, and release database connections.
　　

Specifically, during initialization, a certain amount of database connections are created and put into the connection pool. When users access the database, they do not need to create a new connection and can directly obtain it from the connection pool, run out and put it back. Just like a bus, get off the bus and others can continue. If you have 30 seats, the minimum number of connections is guaranteed to be used by two people. If you have more than 50 people, you can only drop 30 seats and 30 seats are the maximum number of connections. Only a few people can wait in line for the next bus.
　　
Benefits:
Database connection is a key and expensive and effective resource. The database connection pool increases the scalability and robustness of the system. With few changes, you can achieve high throughput, low latency, and high performance. Release time> maximum idle time, which may easily lead to missed database connections. The connection pool race can effectively avoid this problem.

Iii. Summary

Learning is to apply the theory to practice. We hope that the system will apply the theoretical knowledge we have learned to practice in the future. Come on!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.