Mysql Tens Data large table How to optimize? _mysql

1. The capacity of the data: how many data in 1-3 years, how many bytes per piece of data;

2. Data items: whether there are large segments, the values of those fields are often updated;
3. Data query SQL Condition: Which data item's column name often appears in where, GROUP by, ORDER BY clause medium;
4. Data Update class SQL Condition: How many columns often appear in the WHERE clause of the update or delete;
The statistic ratio of 5.SQL quantity, such as: select:update+delete:insert= how much?

6. What is the magnitude of the total daily amount of execution expected for large tables and associated SQL?
7. Data in the table: Update the main business or query-oriented business
8. What database physical server do you intend to use, and the database server architecture?
9. How is concurrency?
10. Storage Engine Select InnoDB or MyISAM?

Roughly understand the above 10 questions, as to how to design such a large table, should be clear everything!

As for optimization, if it means creating a table and not changing the structure of the table, it is recommended that the InnoDB engine, the use of point memory, reduce disk IO load, because IO is often the bottleneck of the database server

In addition to the optimization of the index structure to solve performance problems, we recommend that you modify the class of SQL statements, so that they are faster, forced to rely only on the way the organization structure, of course, if this is the case,
The index has been created very well, if read-oriented, you can consider opening Query_cache,

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

Other people suggest:

1. Index, avoid scanning, based on the primary key of the search, billions of data is also very fast;
2. Anti-normal design, to space for time to avoid join, some join operations can be implemented in code, there is no need to use the database to achieve;