Asp.Net website optimization series of database optimization on the sub database
- 2020-05-10 17:58:26
- OfStack
If you're prescient, you'll prefix the table name, the stored procedure name, for example, the forum table will be BBS_xxx, the blog table will be BLOG_xxx; In this case, it will be easier to deal with the sub-table 1. Speaking of which, you might think about the foreign key constraint. My blog and forum posts all use the primary key of User as the foreign key. This is very easy to deal with, we need to delete the foreign keys immediately, this decision may bring some trouble, let's analyze the following may encounter some problems:
1. Split into multiple libraries. No foreign keys.
Hypothetical scenario: the blog table has a foreign key reference to the user table, and we need to display the list of blogs on the home page, and the list of blogs needs to display the user name and information about user id
Before, when the user table and the blog table were in a database, we could get the user's related information through the foreign key inner join. Now, the user library and the blog library have been split into two libraries. I want to do inner join and no for the cross-library. Why? Because it's not a good idea to scale, and if we have a day where our business is growing again, we need to move the user base to another machine, which is going to cause inner join to cross servers, which is obviously not a good idea, so what do we do? I have two options for you to judge:
1) make a design that violates the normal pattern, and save the user's unchanged information username and user id1 in the blog table, so as to make the user name redundant, so as to ensure that it is very efficient to take the blog data together with the user name
2) instead of taking the user name information from the database, we will take it from the cache. We can form a recently active user data pool in the cache and take it from the cache when we need the user name.
Currently, I use the first scheme in my application, the second one is more scalable, and the first one can only store the user name when storing the redundant data. Sometimes, only the user name is enough, and sometimes it may not be enough.
2. If you use cascading deletions based on foreign keys, this is a nightmare
To deal with this problem, my plan is to modify the program, if you need to cascade delete, complete in the program logic, do not do cascade delete in the database, cascade delete is a kind of implicit in the database logic, is a bad design scheme.
3. Triggers can also cause the same trouble as cascades of foreign keys, and also modify the program logic to replace the implicit logic at the database level.
You might say that there is a performance improvement for 1 after partitioning? This problem has to be specific analysis, it depends on your server performance, if the database cpu, io, memory pressure is still very large; You can then move one of the libraries to another server after the split, and having two servers share the data access pressure will definitely improve performance.
Finally, it is determined by the data volume and performance requirements. Please look forward to the next sub-table!
1. Split into multiple libraries. No foreign keys.
Hypothetical scenario: the blog table has a foreign key reference to the user table, and we need to display the list of blogs on the home page, and the list of blogs needs to display the user name and information about user id
Before, when the user table and the blog table were in a database, we could get the user's related information through the foreign key inner join. Now, the user library and the blog library have been split into two libraries. I want to do inner join and no for the cross-library. Why? Because it's not a good idea to scale, and if we have a day where our business is growing again, we need to move the user base to another machine, which is going to cause inner join to cross servers, which is obviously not a good idea, so what do we do? I have two options for you to judge:
1) make a design that violates the normal pattern, and save the user's unchanged information username and user id1 in the blog table, so as to make the user name redundant, so as to ensure that it is very efficient to take the blog data together with the user name
2) instead of taking the user name information from the database, we will take it from the cache. We can form a recently active user data pool in the cache and take it from the cache when we need the user name.
Currently, I use the first scheme in my application, the second one is more scalable, and the first one can only store the user name when storing the redundant data. Sometimes, only the user name is enough, and sometimes it may not be enough.
2. If you use cascading deletions based on foreign keys, this is a nightmare
To deal with this problem, my plan is to modify the program, if you need to cascade delete, complete in the program logic, do not do cascade delete in the database, cascade delete is a kind of implicit in the database logic, is a bad design scheme.
3. Triggers can also cause the same trouble as cascades of foreign keys, and also modify the program logic to replace the implicit logic at the database level.
You might say that there is a performance improvement for 1 after partitioning? This problem has to be specific analysis, it depends on your server performance, if the database cpu, io, memory pressure is still very large; You can then move one of the libraries to another server after the split, and having two servers share the data access pressure will definitely improve performance.
Finally, it is determined by the data volume and performance requirements. Please look forward to the next sub-table!