How can I optimize a large mysql table with tens of millions of data records?

1. Data capacity: the total number of data records and the total number of bytes of each data record within 1-3 years;

2. Data items: whether there are large fields and whether the values of those fields are updated frequently;
3. SQL condition for Data Query: the column names of which data items frequently appear in the WHERE, GROUP BY, and ORDER BY clauses;
4. Data update SQL conditions: the number of columns that frequently appear in the WHERE clause of UPDATE or DELETE;
5. The statistical ratio of the SQL volume, for example, SELECT: UPDATE + DELETE: INSERT =?

6. What is the average daily execution volume of large tables and associated SQL statements?
7. Data in the Table: Update-oriented business or query-oriented business
8. What database physical servers will be used and the database server architecture?
9. How is concurrency?
10. Does the storage engine choose InnoDB or MyISAM?

I have a general understanding of the above 10 questions. Everything should be clear about how to design such a large table!

If optimization refers to a created table and cannot change the table structure, we recommend that you use the InnoDB engine to reduce disk I/O load by using more memory points, because I/O is often the bottleneck of the database server.

In addition, if you want to optimize the index structure to solve performance problems, we recommend that you modify SQL statements to make them faster. You have to rely only on the index structure. Of course, the premise is that,
The index has been created very well. If the index is read-oriented, you can enable query_cache,

And adjust some parameter values: sort_buffer_size, read_buffer_size, read_rnd_buffer_size, join_buffer_size

Other suggestions:

1. indexing, avoiding scanning, and searching based on the primary key, hundreds of millions of data is also very fast;
2. The anti-paradigm design changes the space for time to avoid join. Some join operations can be implemented using code without the need to use databases;