The table structure is roughly the following
Comment:
Id int (11),//on the primary key
Uid int (11),//comment id
An int (11),//article id
The content of text,
Hot int (11),
Time a datetime
Use the innodb engine, index size 15 g, data size is 25 g


Application scenarios:
Hot update is very frequent, the article details page to aid before filter and sort by hot and time take n comments,
Front already done to comment on each of the data cache, most have hit the cache read operation, only hot sorting to read when you pick up the article mysql, but also made a short time the result cache,
Background a comment like keyword search function, also have the uid and the function of an accurate search


A little thought:
Content field separately removed to the new table with id associated with it, make the comment form smaller so as to improve the efficiency of the hot order
I more conservative for table, worry about points selection sort operations will be difficult after

There are other aspects of the optimization please add

CodePudding user response:

1, low utilization of field put together, put together,
2, can only make a cache in the data layer? Can you do in the application layer?

CodePudding user response:

Keyword search Suggestions with the main retrieval, or some popular full-text retrieval process, the efficiency is not high in the database
The rest is accurate retrieval, in your description, aid the appeared many times, consider according to the partition, this retrieval access control in a small amount of data on the partition of
For the hot and time sorting in n comments before the problem, if you update the host usually is new, and the time of the latest related, then according to the time table to consider, so need the data in the table in the recent

CodePudding user response:

refer to the second floor ZJCXC response:

keyword search Suggestions with full main retrieval, or some popular full-text retrieval process, the efficiency is not high in the database
The rest is accurate retrieval, in your description, aid the appeared many times, consider according to the partition, this retrieval access control in a small amount of data on the partition of
For the hot and time sorting in n comments before the problem, if you update the host usually is new, and the time of the latest related, then according to the time table to consider, so need the data in the table in the recent

I been struggling for whether to table, the feeling is 100 million for mysql is not a very large amount of data, more concerned about is the points out the negative effect on the business will outweigh the benefits, may be later will introduce middleware to complete the operation, may my train of thought a little conservative

CodePudding user response:

This design to the index design, including the degree of differentiation, the most left prefix, etc., personal WeChat recently updated a series of articles that the public, I wait for you in individual number WeChat public "andyqian" to liao,