The original use of MySQL processing less data, small-scale, do not pay attention to the performance of the problem. The recent spike in data volumes, at least 20W of new data per day, has caused MySQL to be poor in performance and must be valued for MySQL optimization.
Experience for reference
In cases where a join query is not involved, the size of a single table is larger than 2g, and it needs to be stored in tables (the number of rows in the table should be around 1000W, if it contains a string type, about 500W rows).
The number of tables in a single database has to be controlled to within 300 or 400 (go DBA's assertion, and control to within 128).
When using the InnoDB engine, innodb_file_per_table=1 sets the data for each table to be stored separately, preventing all table data from being stored in the ibdata1, resulting in a large file size and degraded performance.
When you install MySQL, you should do a good job of MySQL tuning, to avoid later problems, and then to optimize the impact of MySQL business.
MySQL Performance optimization related articles
InnoDB expansion, Ibdata1 slimming
InnoDB Performance Optimization
Turn on Slow query
MySQL performance optimization related