SQL database optimization: Cut, database Connection pool--"exam system"

In the previous article, developers need to focus on CPU and memory during the exam. SQL log also can not be ignored, the SQL log shows the database operating system error log, to provide a great convenience for debugging.
The test database writes some SQL statements that listen for deadlocks and the current most consumed resource statement. Can monitor the deadlock and understand the current exam to which step, is the question, or answer, or handed in.
Of these, two words were heard: Cut, database connection pool.
Later, we found that both methods can optimize the performance of the database from different degrees.

First, cutting

Transverse cutting:
is to classify the rows, the two commonly used are divided by time and index. Time Division: For example, 5 years of historical data, according to the 28 law, 1 years of data may be sufficient to meet the majority of business needs, so separate 1 years of data to take out. This makes querying more efficient. Index Division: For example, department number, each workshop only store their own workshop parts information, the individual workshop information into a separate table more convenient query.
　　
Longitudinal cutting:
is called a row cutting. When it comes to big data, the number of columns directly affects the access effect.

Instance segmentation:
is to abstract the common business parts and store them in separate databases. For example, account database (store user information), log database (storage monitoring data) ...
　　
Instance Physical Storage Segmentation:
When big data is placed on different servers, statistics can be used to improve the computing speed through distributed computing.

Second, database connection pool

As the name implies, is a pool of database connections, has the ability to allocate, manage, release database connections.
　　

In particular, when initializing, create a quantitative database connection to the connection pool, the user access to the database, no need to establish a new connection, directly from the connection pool to take it, run out and put back. Like a bus, you get off, and others can come. 30 seats, sure can guarantee 2 people in use, car driver and conductor, this is the minimum number of connections; to 50 people, the car can only put down 30, 30 is the maximum number of connections. The rest of the people can only wait in line for the next bus.
　　
Benefits:
Database connection is a key expensive and efficient resource, and database connection pool increases the scalability and robustness of the system. With little change, high throughput and low latency and high performance can be achieved. Release time > Maximum idle time, easy to cause database connection omission, the connection pool relay is good to avoid this problem.

Iii. Summary

Learning is to apply the theory to practice, I hope to do the system can learn the theoretical knowledge of the good application to practice, refueling!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL database optimization: Cut, database Connection pool--"exam system"