MySQL slow optimization method, the default installation, restore database, batch submit data save, queries are slowly, version: 5.7

CodePudding user response:

Optimization according to the actual situation to do, need some status value at runtime as the basis,
But according to statistics, thirty percent of the problem is caused by design structure, thirty percent is the issue of SQL statements,
Suggestion:
1. Check for all the tables, pay special attention to table with no indexes, circumstances create index
2. Open the slow query, will slow query collected for analysis

CodePudding user response:

Need embarks from the actual scene, the author analyzes the reasons of slow SQL execution efficiency, not one or two words can summarize, on the basis of the above supplement, can from the following perspective:
1 SQL statements written specifications; Such as: joint query involves multiple SQL statements or join the union, one is to reduce the unnecessary query statement, the second is the where condition after the forward as far as possible, reduce the result set;
2 check the SQL statement whether there is an implicit conversion, check whether there is any index failure;
3 the database parameter optimization, sort_buffer/join_buffer etc parameters of the appropriate adjustments,

CodePudding user response:

The default installation data query slow possible reasons: innodb_buffer_pool_size set too low. You can check the this parameter is set to about 70% of the system memory

CodePudding user response:

Simple and easy to use SQL tuning tool

https://www.tosska.cn/tosska-sql-tuning-expert-for-mysql-tsem-free-download-zh/