Optimize Discuz through MySQL! Hot posts turning skills, discuz hot posts

Optimize Discuz through MySQL! Hot posts turning skills, discuz hot posts

Preface: discuz! As a leading Community system, webmasters provide an all-in-one website solution and are open-source (although some code is encrypted ), it has made great contributions to the development of this vertical industry. Nevertheless, discuz! In the system source code, there are still more or less pitfalls. The most famous one is the use of the MyISAM engine by default, as well as the MyISAM engine-based preemptive building function, the session table uses the memory engine, and so on. You can refer to the following historical articles. This time we will talk about discuz! Another problem in coping with the logic feature of hot posts turning pages.

In our environment, the MySQL-5.6.6 version is used.

When viewing a post and turning pages, an SQL statement similar to the following will be generated:

mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: tid,displayorder,first key: displayorder key_len: 3 ref: const rows: 593371 Extra: Using index condition; Using where; Using filesort

The cost of executing this SQL statement is:

-- According to the number of index access Row Records, the overall status is better.

| Handler_read_key   | 16  |

-- The number of times that the next row of records is accessed according to the index order, usually because of the index range scan or full index scan, which is generally a relatively good status.

| Handler_read_next   | 329881 |

-- The total number of times a row record is read in a certain order. If you want to sort the results, the value is usually relatively large. This value is also relatively large when an index cannot be used in a full table scan or multi-table join operation.

| Handler_read_rnd   | 15  |

When you encounter a hot post that needs to go back to many pages, for example:

mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860, 15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: displayorder key: displayorder key_len: 3 ref: const rows: 593371 Extra: Using where; Using filesort

The execution cost of this SQL statement is changed to (Handler_read_key and Handler_read_rnd are much larger ):

| Handler_read_key | 129876 | -- because many rows need to be skipped before
| Handler_read_next | 329881 | -- same as above
| Handler_read_rnd | 129875 | -- Sort a large result set first.

It can be seen that when encountering hot posts, the cost of this SQL will be very high. If this hot post is frequently accessed by a large number of historical replies, or when the search engine repeatedly requests and replies to history pages, it is easy to crush the database server directly.

Summary: The reason why the 'displayorder' index sorting cannot be used in this SQL statement is that the second index column 'invisable' adopts RANGE query ), as a result, the Union index cannot be used to sort the 'dateline 'field (if it is WHERE tid =? AND invisible IN (?, ?) AND dateline =? In this case, the entire joint index can be used completely. Pay attention to the difference between the two ).

After knowing the cause, the corresponding optimization solution is clear:
Create a new index idx_tid_dateline, which only includes the tid and dateline columns (according to the statistics of other indexes, the base of item_type and item_id is too low, so it is not included in the Union index. Of course, you can also consider adding ).

Let's take a look at the execution plan after the new index is used:

mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: tid,displayorder,first,idx_tid_dateline key: idx_tid_dateline key_len: 3 ref: const rows: 703892 Extra: Using where

As you can see, the existing Using filesort disappears and can be sorted directly through the index.

However, if this hot post turns to an older history reply, the corresponding SQL still cannot use the new index:

mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: tid,displayorder,first,idx_tid_dateline key: displayorder key_len: 3 ref: const rows: 593371 Extra: Using where; Using filesort

Compare the execution plan of the optimizer if it is recommended to use a new index:

mysql> desc SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: idx_tid_dateline key: idx_tid_dateline key_len: 3 ref: const rows: 703892 Extra: Using where

As you can see, because the query optimizer thinks that the number of rows to be scanned by the latter is more than 0.11 million, the former is more efficient.

In fact, in this example, sorting is more costly, so we need to prioritize sorting, so we should force the use of new indexes, that is, using the subsequent execution plan, specify the index in the corresponding program.

Finally, let's take a look at the profiling statistics comparison of the two execution plans when the hot posts turn to very old history replies:

1. Use the old index (displayorder ):

Mysql> SELECT * FROM pre_forum_post WHERE tid = 8201301 AND 'invisable' IN ('0', '-2') order by dateline LIMIT 129860,15; # view profiling results | starting | 0.020203 | checking permissions | 0.000026 | Opening tables | 0.000036 | init | 0.000099 | System lock | 0.000092 | optimizing | 0.000038 | statistics | 0.000123 | preparing | 0.000043 | Sorting result | 0.000025 | executing | 0.000023 | Sending data | 0.000045 | Creating sort index | 0.941434 | end | 0.000077 | query end | 0.000044 | closing tables | 0.000038 | freeing items | 0.000056 | cleaning up | 0.000040 |

2. If a new index (idx_tid_dateline) is used ):

Mysql> SELECT * FROM pre_forum_post use index (idx_tid_dateline) WHERE tid = 8201301 AND 'invisable' IN ('0', '-2') order by dateline LIMIT 129860,15; # Compare and view profiling results | starting | 0.000151 | checking permissions | 0.000033 | Opening tables | 0.000040 | init | 0.000105 | System lock | 0.000044 | optimizing | 0.000038 | statistics | 0.000188 | preparing | 0.000044 | Sorting result | 0.000024 | executing | 0.000023 | Sending data | 0.917035 | end | 0.000074 | query end | 0.000030 | closing tables | 0.000036 | freeing items | 0.000049 | cleaning up | 0.000032 |

As you can see, the efficiency has been improved, but it is not obvious, because it does need to scan a larger amount of data, so the Sending data stage takes more time.

At this time, we can refer to a previous optimization solution: [MySQL optimization case] series-Paging Optimization

Then you can rewrite the SQL statement as follows:

mysql> EXPLAIN SELECT * FROM pre_forum_post t1 INNER JOIN ( SELECT id FROM pre_forum_post use index(idx_tid_dateline) WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15) t2 USING (id)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table:  type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 129875 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t1 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: t2.id rows: 1 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: pre_forum_post type: ref possible_keys: idx_tid_dateline key: idx_tid_dateline key_len: 3 ref: const rows: 703892 Extra: Using where

Let's take a look at the profiling statistics of this SQL statement:

| starting    | 0.000209 || checking permissions | 0.000026 || checking permissions | 0.000026 || Opening tables  | 0.000101 || init     | 0.000062 || System lock   | 0.000049 || optimizing   | 0.000025 || optimizing   | 0.000037 || statistics   | 0.000106 || preparing   | 0.000059 || Sorting result  | 0.000039 || statistics   | 0.000048 || preparing   | 0.000032 || executing   | 0.000036 || Sending data   | 0.000045 || executing   | 0.000023 || Sending data   | 0.225356 || end     | 0.000067 || query end   | 0.000028 || closing tables  | 0.000023 || removing tmp table | 0.000029 || closing tables  | 0.000044 || freeing items  | 0.000048 || cleaning up   | 0.000037 |

As you can see, the efficiency has been improved by more than one time, which is quite good.

Finally, this problem will only appear when the hot posts are turning pages. Generally, there is no problem if only the posts replied on pages 1 and 2 still adopt the original execution plan.

Therefore, we recommend discuz! Officially modify or add a new index, and in the code to determine whether the hot posts flip pages, if yes, force the use of new indexes to avoid performance problems.