Analysis of QBlog technology principles in the autumn color Garden: Performance Optimization: read/write splitting and Text Database (18th)

Review in the previous section: 

Previous sectionAnalysis of QBlog technical principles of the autumn color Park: Performance Optimization: user and article counter solution (17),

Autumn GardenQBlog For access counters (User tables and article tables) that frequently generate update operations, another optimization solution is provided to enable the original concurrent operations, it becomes a scheduled ordered update operation for a single queue, effectively solving the problem of concurrency caused by counters.

 

Summary:

 

Although the decompression solution was frequently used, it still failed to block the kill of the access golden 4 K.

Under pressure, the dream potential is once again stimulated.

As a result, the new trick was born again: a long-lost trick: Text Database.

 

Content of this section:

 

1: analysis to find optimization points:

 

PassCYQ. DataAppDebug (the version V4.5.5 that will be released contains this class) to print the SQL statement on the page:

 

 

PS:For the optimization of SQL statements on the print page, see the previous article: Analysis of QBlog technology principles of the autumn Garden: Performance Optimization article: Global SQL statement optimization (13)

 

First, observe these statements on the page. We can see several statements involved:

1: The first table schema query statement, that is, the where 1 = 2 Statement.

2: blog user information reading statement

3: Links

PS: If there is no cache, there are still many statements related to the article list. The next section of the article focuses on this.

 

Then I thought about these statements for a long time and finally came to the conclusion that these statements should be wiped out.

 

2: Analyze and optimize the optimization points step by step:

 

2.1: Eliminate the SQL statement read from the table Architecture

This actually does not matter, because a table is read only once, and then the global cache is cached for 30 minutes by default, so the frequent occurrence is very low, but in order to pursue the homepage 0 statement, I am still taking it seriously. How can I eliminate it?

Elimination is actually a good solution. As long as the table architecture is external to the text during the first read, The read sequence of the architecture is changed to: cache-> text-> database.

 

The following is an example of external text and external architecture of the table architecture:

 

2.2: Eliminate SQL statements for reading user information

In fact, a user table is a big problem and is often 4 K, because there are too many statements that can involve reading user tables.

To this end, although the user information will be cached after each read, but the number of users is large, and the search engine will return back and forth, as a result, reading is quite frequent throughout the process. So I thought about how to eliminate it?

Similarly, when I read the user information externally for the first time, I refreshed the text when the user updated the data in the background.

Then the natural order of reading is changed to: cache-> text-> database.

So of course, now more than 4000 users in the autumn garden have produced more than 4000 texts, which seems to be very large!

It is inevitable that some people will sigh that if you have 1 million users, you will not generate 1 million texts? I want to say, no!

 

The following is an example of how to store user information text and user information in json format:

 

2.3: Eliminate the SQL statement used to read links

User links are not important compared to user information, but you will find that each user page may also have links.

So I plan to destroy it too. How can I eliminate it?

With the above two steps of experience, this step is too easy to implement. Similarly, for the first time, the user's friendship link is transferred to the file, and then the read is the text read. When the background is modified, it is also read text, but when writing, first write the database, then write the text.

As a result, more than 4000 users will also generate more than 4000 texts of friendship links.

 

The following is an example of how to store the text and link list of a link in json format:

 

2.4: What are the SQL statements in the article list?

The SQL statements in a large number of articles are not eliminated using text.

Why not?

The reason is also very simple, because the article list involves complex statements such as query, sorting, and grouping, and the text is not very good at these operations.

How is the article list optimized? This is a big project. At that time, I went for a walk and thought for three consecutive days. It was also the final optimization of QBlog of the autumn garden so far, the following section describes the details.

 

Summary:

 

By using text, QBlog transfers a large number of read databases to text reading, which effectively reduces the pressure on the database and ensures smooth website operation.

After an application, I made a first impression on the text:

Advantages: high speed, easy storage and reading of small data volumes (up to 0.1 million MB or 10 MB.

Disadvantages: operations such as deletion, update, query, paging, sorting, and concurrency control are complex, and the data volume is not suitable for too many operations.

 

In addition, according to the search results of "Text Database" on the Internet:

Text databases used to be popular in the php field. Most forums use text databases, and the anti-concurrency capability is quite strong. Of course, there are some technical means behind this, and later, php is basically the same as mysql.

As for the. net field, Text Databases have never been popular. Why?

 

Review of historical articles:

1. Analysis of QBlog technology principles in the autumn Garden: Opening part: Overall understanding (1) -- introduces the role of the overall folder and file

2: Analysis of QBlog technical principles of the autumn color Garden: Understanding the whole site processing process (2)-introducing the business processing process of the autumn color Garden

3: Analysis of QBlog technical principles in the autumn color Park: UrlRewrite's principle of URL without a suffix (3) -- introduces how to implement URL without a suffix

4: Analysis of QBlog technical principles in the autumn Garden: URL redirection System of UrlRewrite (4) -- introduce how to locate the processing program in the URL

5. Technical Principles of QBlog in the autumn color Park: Module-based page base class design (5)-Introduction to creating base classes and custom lifecycles

6: Analysis of QBlog technology principles in the autumn Garden: Module Page Base-Lifecycle Process (6)-Introduction to internal services of the basic Lifecycle

7. Technical Principles of QBlog in the autumn Garden: Module-based lifecycle-page loading (7)-Introduction to html Loading Principles

8. Technical Principles of QBlog in the autumn Garden: webpage processing-content filling (8)-How to fill html content

9: Analysis of QBlog technology principles in the autumn Garden: unique multi-language translation mechanism (9)-Introduction to the principles of html multi-language translation

10: Analysis of Technical Principles of QBlog in the autumn Garden: demo of page content filling and multi-language translation process (10) -- Summary DEMO code

11: Analysis of QBlog technical principles in the autumn Garden: page Post Submission mechanism (11) -- Introduction if Post submits data

12: Analysis of QBlog technology principles in the autumn color Garden: Performance Optimization: byte and cache and concurrency (12)-Performance Optimization: byte, concurrency and Cache

13: Analysis of QBlog technology principles in the autumn Garden: Performance Optimization article: Global SQL statement optimization (13)-Introduce Global SQL and perform targeted Optimization

14. Technical Principles of QBlog in the autumn color Garden: Performance Optimization article: constructing a continuous cache solution when the cache is always ineffective (14th)-introducing the second cache Solution

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

16: Analysis of QBlog technology principles in the autumn color Garden: Performance Optimization article: Concurrent limit of access and Distributed Concurrency solution for database sharding (16) -- Introduce the upper limit of access concurrency

17: Analysis of QBlog technology principles in the autumn color Park: Performance Optimization article: user and article counter solution (17)-introduces the counting optimization solution for user and article access

Appendix:

1: Analysis of QBlog technical principles of the autumn color Garden: technical implementation of one-click blog Installation Tool [Download source code] -- Principle of the open source autumn color garden Installation Tool

2: how to install and deploy the CYQBlog site of the autumn Garden

3: how to install and deploy the CYQBlog site of the autumn Garden in Windows 7

 

PS:QBlog:Http://www.cyqdata.com/download/article-detail-427