Analysis of QBlog technology principles in the autumn color Garden: Performance Optimization: Database articles table sharding and database sharding and decompression solutions (15th)

Review:1: Analysis of QBlog technology principles in the autumn Garden: Opening part: Overall understanding (1) -- Introduction of the role of the entire folder and file 2: Analysis of QBlog technology principles in the autumn Garden: understanding of the whole site processing process (2) -- Introduction to the business processing process of the autumn color Garden 3: analysis of the technical principles of the QBlog of the autumn color Garden: UrlRewrite-No suffix URL principle (3) -- Introduction to how to implement the technical principles of the QBlog without a suffix URL4: the autumn color Garden: urlRewrite-based URL redirection System (IV)-Introduction to how to locate URLs to the processing program 5: Technical Principles of QBlog in the autumn Garden: Module-based Page Base Design (V) -- Introduction to creating a base class and a custom Life Cycle 6: Technical Principles of the QBlog of the autumn color garden analysis: Module Page Base Class-Life Cycle Process (6) -- Introduction to the internal business of the basic class life cycle 7: analysis of Technical Principles of QBlog in the autumn color Park: Module-based life cycle-page loading (7)-Introduction to the html loading principle on the page 8: Analysis of Technical Principles of QBlog in the autumn color Park: webpage processing-content filling (8)-Introduction to how html content is filled 9: Technical Principles of QBlog in the autumn Garden: unique multilingual translation mechanism (9) -- Introduction of html multi-language translation principle 10: Analysis of QBlog technical principles of the autumn Garden: Examples of page content filling and multi-language translation process demonstration (10) -- Summary DEMO code 11: analysis of the technical principles of the QBlog of the autumn Garden: page Post Submission mechanism (11) -- Introduction if Post is used to submit data 12: Qiu color garden QBlog technical principles analysis: Performance Optimization article: byte and cache and concurrency (12) -- Introduction to performance optimization: byte, concurrency and cache 13: Technical Principles of QBlog in the autumn color Park: Performance Optimization article: Global SQL statement optimization (13)-Introduce Global SQL and conduct targeted optimization 14: Analysis of Technical Principles of QBlog in the autumn color Park: performance Optimization: Build a continuous cache solution when the cache is always ineffective (14th) -- Introduce the secondary cache solution chapter: 1: Analysis of QBlog technical principles of the autumn Garden: blog one-click Installation Tool technical implementation [Download source code] -- Principle of the open-source Installation Tool for the autumn garden 2: how to install and deploy the CYQBlog site for the autumn Garden 3: how to install and deploy the CYQBlog site for the autumn Garden in Windows 7 PS: QBlog: Http://www.cyqdata.com/download/article-detail-427  Review in the previous section:In the previous section, the QBlog technology principle of the autumn Colorado Park: Performance Optimization article: The cache is always ineffective, and the continuous cache solution (14th) is constructed. This article introduces the performance optimization of QBlog in the autumn Colorado Park, in order to avoid the blank period of cache failure, the static method is used as a temporary buffer policy scheme. Content of this section:This section describesQBlogFrom another perspective, the website optimization method is optimized based on database/table sharding. Basic description:Autumn GardenQBlogFrom the very beginning, the optimization work is based on code-based technical optimization solutions, one of the major reasons:Is the autumn GardenQBlog At first, it was stored in a subdirectory of a friend's foreign VM. I only have ftp permissions;It is not easy to operate a hundreds of M access database that is running,Therefore, optimization work can only seek technical breakthroughs.Since the autumn GardenQBlogTransferAfter VPS, access can be operated physically, and the optimization options are also increased. Therefore, the overall optimization pressure is slightly shifted to the access database. This section describes the first database optimization method: Table sharding and database sharding in the article.Basic Analysis: Autumn GardenQBlog The reason why the access database exceeds 600 mb is that there are a large number of article tables Blog_Content. The most space in the article table is the content of the article, which will be extracted in this section. Let's take a look at the design of the original article table data structure: blog article table: Blog_Content field: ID Article IDTitle Article Body Article Content Abstract introduction ...... the other fields are omitted. Here is the design of a regular article table, that is, the Body field of the article content, which is usually placed in the article table. What happens when this field appears and is used? 1: When publishing or editing an article in the background, 2: view the article. It can be seen from this that there are not many and few involved points, and there are static pages on the front-end, basically in addition to sending and editing articles, the content of such a large amount of data is hardly shown. Under what circumstances does the Document Table appear and use? This is countless.QBlogThe homepage, user blog homepage, article list, and article file are used on almost every page. Therefore, it is quite necessary to separate the Body fields of articles that are not commonly used and occupy more than 90% of the space. As a result, the table is split: A Blog_ContentBody table: field is added, and there are two: ID: Article IDBody: article content. However, only table sharding does not seem powerful enough. Then perform database sharding: directly allocate Blog_ContentBody to another access database. Tip: I found that the database for removing the content of the article only has dozens of MB left, and the content of the article actually accounts for more than 500 mb. As a result, the autumn GardenQBlogThe database name has changed from one data to two databases:Change the name from myspace. mdb:Qblog. mdb master databaseQblogbody. mdb document content database Of course, if the table is split and the database is split, the Code cannot be followed up, and the technical means must also be upgraded. Steps for upgrading: 1: Table sharding and database sharding, code Dynamic Modification and compatibility upgrade The CYQ. Data framework already supports simultaneous operations on multiple databases. Therefore, after database sharding, the number of code changes is very small, with the following major changes:1: Delete the Body enumeration field of the Blog_Content table.2: Add multi-database enumeration:Public enum U_QBlogBodyEnum
{
Blog_ContentBody,
} Then add a QBlogBodyConn database link to the database.3: publish, edit, and display articles, and add independent operation functions for the article content. Code changes are very small, that is, the work completed within half an hour. 2: Database sharding steps:1: log on to the vps server and use CYQ. DBImport to import the ID and Body fields from the original database to the Blog_contentBody table of another database.2: Open the original table Blog_Content, delete the Body field, compress the database, and more than 20 mb is left.3: directly upgrade the dll to the server, and the entire upgrade process is very fast. After the separation and upgrade, I feel the autumn GardenQBlogThe access speed has been refreshed. Summary:So far, the autumn GardenQBlogStarted to go into the overall policy solution that combines database optimization and code optimization,However, one thing that hasn't been changed is that access is still struggling. Don't ask me why I don't need mssql.Maybe one day, Access cannot run, and the optimization is on the top, so other databases will be on the stage. 

This article from the "passing by autumn" blog, please be sure to keep this source http://cyq1162.blog.51cto.com/2127378/606977