How to optimize the mysql data table？
- 2020-05-10 23:02:55
- OfStack
1. Data capacity: how many pieces of data will be available in 1-3 years, and how many bytes of each piece of data will be available;
2. Data items: whether there are large fields and whether the values of those fields are updated frequently;
3. Data query SQL condition: which data items' column names frequently appear in WHERE, GROUP BY, ORDER BY clauses;
4. Data update class SQL condition: how many columns frequently appear in UPDATE or DELETE WHERE clauses;
5. The statistical ratio of the amount of SQL, such as: SELECT: UPDATE+DELETE: INSERT= what?
6. What order of magnitude is the expected total daily execution of the large table and the associated SQL?
7. Data in the table: update the main business or query the main business
8. What database physical server are you going to use, and the database server architecture?
9. What about concurrency?
10. InnoDB or MyISAM for storage engine?
Roughly understand the above 10 questions, as to how to design such a large table, what should be clear!
As for optimization, if it means creating a good table and not changing the structure of the table, it is recommended that the InnoDB engine make more use of memory to reduce the load on the disk IO, because IO is often the bottleneck of the database server
In addition, to optimize the index structure to solve the performance problem, it is recommended to give priority to modifying class SQL statements to make them faster, and to only rely on the index structure, of course, the premise is that,
The index has been created very well, so if you want to read primarily, consider opening query_cache,
And adjust some parameter values: sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size
Others suggest:
1. Index, avoid scanning, search based on the primary key, hundreds of millions of data is fast;
2. Anti-paradigm design, space for time, to avoid join, some join operations can be implemented in code, there is no need to use the database to achieve;
2. Data items: whether there are large fields and whether the values of those fields are updated frequently;
3. Data query SQL condition: which data items' column names frequently appear in WHERE, GROUP BY, ORDER BY clauses;
4. Data update class SQL condition: how many columns frequently appear in UPDATE or DELETE WHERE clauses;
5. The statistical ratio of the amount of SQL, such as: SELECT: UPDATE+DELETE: INSERT= what?
6. What order of magnitude is the expected total daily execution of the large table and the associated SQL?
7. Data in the table: update the main business or query the main business
8. What database physical server are you going to use, and the database server architecture?
9. What about concurrency?
10. InnoDB or MyISAM for storage engine?
Roughly understand the above 10 questions, as to how to design such a large table, what should be clear!
As for optimization, if it means creating a good table and not changing the structure of the table, it is recommended that the InnoDB engine make more use of memory to reduce the load on the disk IO, because IO is often the bottleneck of the database server
In addition, to optimize the index structure to solve the performance problem, it is recommended to give priority to modifying class SQL statements to make them faster, and to only rely on the index structure, of course, the premise is that,
The index has been created very well, so if you want to read primarily, consider opening query_cache,
And adjust some parameter values: sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size
Others suggest:
1. Index, avoid scanning, search based on the primary key, hundreds of millions of data is fast;
2. Anti-paradigm design, space for time, to avoid join, some join operations can be implemented in code, there is no need to use the database to achieve;