How to design a database with large amount of data and large query span, such as transaction records?

  mysql, question

At present, there is a simple trading system, which naturally needs to store all trading records and generate serial numbers or something.

At present, the following have been put into usemysqlAndredis, if necessary, can addmongodb

In my imagination, the serial number of the transaction record is obtained by combining the date of the day with the redis atomic self-increment

However, it is difficult to design the database of transaction record table, because the amount of data may be very large, so it is necessary to divide the table or even the database. However, it is also required that transaction records can be queried through any customized time period. How to design data structure reasonably and ensure certain performance?

Thank you all first

It is not easy to answer and it is not recommended to consider it too early.
Can you understand “the amount of data may be very large” like this? At the beginning of the project, the amount of data is not large, but it will become Taobao if it cannot be prepared in the future. The amount of data is bound to be very large.
In fact, it can be done at any time regardless of the way of table division, partition and Merge engine, and there is no need to make such a long-term plan in the early stage, because your plan is not as good as the change, such as the typical hash method. How many do you have? Leave 2 digits, after that the amount of data is large or not enough, leave 6 digits? There are more tables than data.
Moreover, when the amount of data is too large, it is not enough to divide it by itself. What is needed is redundancy to improve the performance. One set is cut by user and the other by transaction time. If you still want to query the transaction records according to the purchased commodities, OK, another set is cut by commodity information.

I don’t think it is necessary to consider the sub-library, because you can use the distributed file system to play clusters. The so-called sub-library is actually to divide the data into several types, which are commonly used, rarely used, and at the bottom of the closet. I have not divided the library, this paragraph is purely speculative.

My personal opinion is for reference only and can be used as a reference.