ASP. NET Website optimization series database Optimization

Speaking of the Three Kingdoms: in terms of the world's general trend, the word "Minutes" must be combined and the word "points" must be used to optimize our databases.

When our data volume is small, we place user tables, blog tables, Forum tables, and Flash tables in a single database. Our business grows very well, soon after, we tried our best to optimize the query, but the effect was still poor. At this time, we had to use the word splitting tips.

If you have a foresight, you will add a prefix to the table name and stored procedure name. For example, the Forum table name is bbs_xxx and the blog table name is blog_xxx; in this way, table sharding is easier. Speaking of this, you may think about the foreign key constraint. In my blog tables and forum post tables, the user table's primary key is used as the foreign key. This is easy to handle. We need to delete the foreign key on the fly. This may cause some trouble. Let's analyze what problems we may encounter:

1. Split the database into multiple databases. Do I need to perform cross-database inner join operations without foreign keys?
Suppose that the blog table has a foreign key reference to the user table. We need to display the blog list on the homepage, and the blog list needs to display the user name and user ID information.
In the previous user table, when the blog table is in a library, we can use the foreign key inner join to obtain the user's association information. Now the user library and blog library are split into two databases, I want to say no to cross-database inner join. Why? Because it is not suitable for expansion, if one day our business volume increases, we need to move the user library to another machine, this will cause inner join to be cross-server. This is obviously not a good solution. What should we do? I have two solutions for you to judge:
1) do a design that violates the paradigm, and store the user's unchanged information username and user ID in the blog table, so that the user name can be redundant. In this way, it can ensure that the user name associated with the blog data is highly efficient.
2) We no longer retrieve user name information from the database, but retrieve it from the cache. We can form a recently active user data pool in the cache, when we need a user name, we can get it from this cache zone.

Currently, the first solution is used in my application, and the second method is more scalable. The first method is to store redundant data only with the user name. Sometimes it is enough to store only the user name, sometimes there may be insufficient problems.

2. If cascading deletion based on foreign keys is used, this is a nightmare.
To solve this problem, my solution is to modifyProgramTo perform cascading deletion in the program logic, do not perform cascading deletion in the database. cascading deletion is a logic implied in the database, it is a bad design.

3. Trigger may also cause the same trouble of cascade deletion with foreign keys. It is also used to modify the program logic, instead of the implicit logic at the database level.

Maybe you will say that the performance will be improved after database sharding? The specific analysis of this problem depends on the performance of your server. If the CPU, I/O, and memory of the database are still under heavy pressure after database sharding; you can migrate one of the databases after the database sharding to another server, so that the two servers can share the data access pressure, which will definitely improve the performance.

In the end, database sharding and sharding are determined by the data volume and performance requirements. Next sub-table is coming soon!

 

Related Articles: ASP. NET Website optimization series database optimization Word Table (Vertical Split, horizontal partition)
Bytes