MySQL Query performance optimization

mysql query process:

1. The client sends a query request.
2. The server checks the query cache and returns the result if the cache is hit, otherwise, proceeds.
3. The server performs SQL parsing, preprocessing, and then the optimizer generates the execution plan.
4. The MySQL call Storage Engine API executes the optimizer-generated execution plan for the query.
5. Returns the result.

Optimize data access:

1. Get only the necessary data:
   • Whether redundant records were queried;
   • Whether all columns are returned when the multiple table is associated;
   • Whether to always remove all columns (avoid select *);
   • Whether the same data is queried repeatedly (cache substitution).
2. Avoid additional record scans: Query cost metrics (response time, number of rows scanned, number of rows returned)
   • Response Time: Service time (execution query) + Queue time (IO or wait for resources, locks, etc.); fast online estimation method.
   • Number of rows scanned and number of rows returned: General 1:1-->1:10.
   • Number of rows scanned and types of access: the difference in the number of scanned rows for different accesses to the same row of data (scan table, index, scope access, unique index, Changshu reference, single-value access), usually increasing the index is the most straightforward method. Query optimization tips for a large number of scans that return a small number of rows:
     • Using an index overlay scan: Put all the required columns into the index, and the storage engine returns the results without having to fetch the corresponding rows back to the table.
     • Change the structure of the library table: increase the summary table storage, space change time, efficiency.
     • Rewrite query: SQL structure.

Refactoring Query mode:

1. The choice of complex query and simple query: The factors of network communication, query parsing and optimization are considered in complex query. A simple query that decomposes complex queries into multiple combinations can sometimes be a good choice.
2. Slice query: Cut large queries into multiple identical small queries. Example: When you delete old data.
3. Explode associative queries: Consolidate individual queries that are decomposed at the application level.
   • Increased cache efficiency: application services often need to cache common single-table queries and reuse them.
   • A single query that is decomposed can reduce the competition for locks.
   • The application layer is associated to make database splitting easier, and to build high-performance and highly scalable programs and services.
   • Improve the efficiency of the query.
   • Queries that reduce redundant records.
   • The application layer's hash correlation efficiency is higher than the MySQL Loop nesting association.

MySQL Query performance optimization