The following optimization methods need to be considered comprehensively, and it is inevitable to lose one or the other, which is determined according to the actual application scenarios: 1. Minimize joins: the advantage of MySQL lies in its simplicity, and the MySQL optimizer is highly efficient. For complex multi-table joins, on the one hand, due to its limited optimizer, the performance is not ideal. MySQL > select * from ‘sort’; select * from ‘sort’; 1) SORT by index 2) reduce the number of entries involved in sorting 3) SQL statements with DISTINCT,UNION,ORDERBY start SQL engine execution, resource consuming SORT (SORT) function. The MySQL optimizer does not solve the execution plan optimization problem very well when there are multiple conditions in the WHERE clause with ‘or’. In addition, MySQL’s unique hierarchical architecture of SQL and Storage results in low performance. In many cases, it is better to use union all or union (when necessary) instead of “OR”. 5. Try to replace union all with Union All. The main difference between union and Union all is that the former needs to combine the two result sets and then perform the unique filtering operation, which involves sorting, increases a large number of CPU operations, and increases resource consumption and delay. So use union all instead of union when we can confirm that duplicate result sets are impossible or don’t care about duplicate result sets. 6, as early as possible filtering, for example, when we carry out paging data query in multiple tables, we had better be able to filter the data in one table and divide the page, and then use the result set of divided page to Join with other tables, so as to reduce unnecessary IO operations as much as possible, greatly saving the time consumed by IO operations. SQL > select * from ‘where’ where (select * from ‘where’); SQL > select * from ‘where’; SQL > select * from ‘where’; Select orderID from details where unitprice > 10 groupby orderID, Select distinct orderID from details where unitprice > 10 Prevent other users from accessing the table 10. Prioritize high-concurrency SQL over “large” SQL that is executed infrequently. High-concurrency SQL is much more destructive than low-frequency SQL, which can crash the system directly if it fails. For some SQL consuming a lot of IO, slow response, due to the low frequency, at most is to make the whole system slower response, there is a buffer opportunity.

For more free technical information: annalin1203