One of the MySQL query optimizations:

Query performance optimization

Why is the query slower?
The life cycle of a query can be roughly in order: from the client to the server, then parse on the server, generate execution plans, execute, and return results to the client. In the execution phase, there are a number of calls to retrieve data to the storage engine and the data processed after the call, including sorting, grouping.
The reason for the slow query is that some unnecessary extra actions, some operations are repeated many times, and some operations are performed too slowly.
The purpose of the optimization query is to reduce and eliminate the time taken by these operations.

Slow Query Basics: Optimizing data Access

The most basic reason for poor query performance is that there is too much data to access. So for inefficient queries, you can analyze them from the following two areas:
1. Verify that the application is retrieving a large amount of data than is needed.
2. Verify that the MySQL server layer is analyzing a large number of data rows that are more than needed.
whether the query takes only the required
The inefficient query table now has the following aspects:
A. Query for records that are not required;
B. Return all columns When multiple table associations are associated;
C. Always take out all the columns;
D. Repeat querying for the same data.
Note: In the query statement with the careful use of the SELECT * statement, we should only take the database data required.
is MySQL scanning for additional records
After you have determined that the query returns only the data that you need, the query has the following three metrics to measure the cost of the query in order to return the results if it has scanned too much data:

• Response time
• Number of rows scanned
• Number of rows returned

You can find records of these three metrics by querying slow logs
Indexes have a significant impact on the number of rows scanned, and when a query is discovered that requires a large amount of data to be scanned but returns only a small number of rows, you can use an index overlay scan to avoid back-table queries.

How to Refactor queries

Select a complex query or a number of simple queries?
The author of the book's view: In the MySQL inside every second can scan the memory of hundreds of rows of data, compared to the MySQL response data to the customer service side is much slower. When other conditions are the same, it is better to use as few queries as possible. However, it is not denied that a large query is decomposed into multiple small queries.
Ways to decompose a query:
Segmentation query: as the name implies, is to cut a large query into many small queries, each small query function exactly the same, return a part of the results, we just need to repeat the small query on the line. (Application: When clearing large amounts of data, if a large statement may be a one-time to consume a lot of resources, blocking other queries, then we can cut it into multiple small queries, that is, each query only delete the appropriate amount of queries, multiple times)
explode an associated query
You can break up a single multi-table association query into multiple queries, make one-table queries for each table, and then associate the results in your application. (The associated queries that are made in the database are transferred to the application tier)
Advantages:
A. Make the cache more efficient. For the results of a single-table query, the application can be easily cached, after the decomposition of the statement, we can efficiently use the cache to query.
B. After the query is decomposed, executing a single query can reduce the competition for locks.
C. Associating at the application layer makes it easier to split the database and make it easier to perform high performance and scalability.
D. The efficiency of the query itself will also be improved. Querying by ID sequence is more efficient than random correlation. (using in () instead of the join...on of the associated query ... ）
E. Reduce the query for redundant records. In the application layer to make the associated query, for a record application only need to query once, and in the database to do the associated query, you may need to access a part of the data repeatedly.

basis for query execution

How does MySQL optimize and execute queries?

1. The client sends a query to the server;
2. The server checks the query cache first, and returns the results stored in the cache immediately if the cache is hit. Otherwise proceed to the next stage;
3. The server performs SQL parsing, preprocessing, and generating the corresponding execution plan in the query optimizer;
4.MySQL invokes the API of the storage engine to execute the query, based on the execution plan generated by the optimizer.
5. The results are returned to the customer service side and are also placed in the query cache.
MySQL client/server communication protocol
The communication protocol between MySQL client and server is "half duplex", which means that at any one time, either the server sends the data to the client or the client sends the data to the server, and the two actions cannot be performed simultaneously. This means of communication creates many limitations.
In most library functions that connect to MySQL (the function method that calls the MySQL database), the default is to get all the result sets and cache them in memory.
Of course, sometimes this is not a good method of caching, a large result set in the cache will occupy a lot of time and memory. Not using the cache, fetching data from the server, and then processing directly, but this will increase the pressure on the server, the server can only release resources after the query is completed.
Query Cache(Query cache)
MySQL finds the data in the query cache through a case-sensitive hash lookup, which is an exact matching lookup.
Query Optimization processing
Converting SQL to an execution task includes "parsing SQL, preprocessing, optimizing the execution plan."
Syntax parser: MySQL parses the SQL statement through the keyword and generates a corresponding "parse tree", which the MySQL parser uses to validate and parse the query (the order of the keywords is correct, verifying that the quotation marks can match correctly before and after).
Preprocessor: The preprocessor will further check whether parsing is legitimate (checking the existence of data tables and data columns, resolving names, aliases to see if they are ambiguous).
query optimizer
The query optimizer is a cost-based optimizer that tries to predict the cost of a query using some kind of execution plan, and selects one of the most expensive (the cost of the current query that MySQL calculates is available by querying the value of the current session's Last_query_cost)

The above statement predicts that this count (*) operation will require a random lookup of 1.8 data pages to complete.
Types of optimizations that MySQL can handle:
1. Redefine the order of the associated tables;
2. Convert the outer connection into an inner connection;
3. Use the equivalent change rule; you can combine and reduce some comparisons, and you can remove some of the constant and non-established judgments.
4. Optimize the Count (), Min (), and Max (); Indexes and columns can often be used to help MySQL optimize this type of expression, such as finding the minimum value, just find the leftmost first record in the index tree.
5. Estimate and convert to constant expression; When MySQL detects that an expression can be converted to a constant, it always optimizes the expression as a constant.
6. Overwrite index scan; When the scanned index column contains the columns that are needed in all queries, MySQL can directly use the index to return the required data.
7. Sub-query optimization;
8. Terminate the query prematurely, such as using the limit clause to find the limited number of data.
9. Equivalent propagation; If the values of the two columns are associated by an equation, then MySQL can pass the where condition of one of the columns to the other column.
10. The comparison of list in (), the MySQL pair in () list is optimized, first the values in the list are sorted, and then the binary lookup method to determine whether the values in the list meet the criteria.
mysql sorting optimizations:
In addition to using indexes for sorting, MySQL also has a sorting method called file ordering (filesort).
When the amount of data that needs to be sorted is less than the "sort buffer", MySQL uses memory for "quick sorting", and when there is not enough memory, chunks are chunked, each separate block uses a "quick sort" and the sorting results of each block are placed on disk, and the results are merged.
Two sorting algorithms for MySQL:
Two transfers sort (old version used): reads the row pointer and the field that needs to be sorted (the first time), sorts it, and then reads the desired data row (the second time) based on the sort result. The advantage is that the sort buffer can hold more rows of data for sorting, and the downside is that a second read generates a lot of random I/O.
Word Transfer sort (new version used): All the columns required by the query are read first, then sorted according to the given column, and the sorting results are returned directly.

MySQL does not allow querying and updating on the same table at the same time, so when we write such a statement, we first process an operation and then correlate the temporary table produced by this operation, which is equivalent to running the subquery first and then completing the operation.

Refine a specific type of query

optimize count () queries
COUNT (*): Counts the number of rows, which is much faster than the number of column values that are generally counted.
Simple optimization: Reduce the number of scans by modifying conditional statements. (Always remember that counting count (*) is fast, faster than counting all the stats with a condition)
Use approximate values: that is, the count () result can be replaced by a value that is estimated by an optimizer.
Optimizing associated Queries
1. Make sure that the on or using clause has an index on the column, and that the general index is on the corresponding column on the last associated table.
2. Make sure that the expressions in group by and ORDER by at any time only involve columns on one table, so that MySQL can use the index to optimize the process.
Refine subqueries
Use associative queries instead of subqueries whenever possible.

One of the MySQL query optimizations: