MySQL Query performance optimization

Why is the query slow?

If you think of a query as a task, it consists of a series of subtasks, each of which consumes a certain amount of time. Optimizing a query is essentially optimizing its subtasks, either eliminating one of the subtasks, or reducing the number of child tasks executed or making the subtasks run faster.

The life cycle of a query is broadly divided into: from the client, to the server, and then on the server to parse, generate execution plan, execute (call the storage engine to retrieve data, call after processing sorting, grouping, etc.) and return the result to the client. To accomplish these tasks, the query needs to spend time in different places, including operations such as network, CPU compute, generate statistics and execution plans, lock waits (mutually exclusive waits), and especially call operations that retrieve data to the underlying storage engine, which also generates a lot of context switches and system calls depending on the storage engine.