MySQL Note 03 Query performance optimizations

Query performance optimization

1. Why is the query slower?

1). 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. If you want to refine a query, you actually optimize its subtasks to either eliminate some of the subtasks, or reduce the number of subtasks performed, or make the subtasks run faster.

2). In general, the life cycle of a query can be roughly in order: from the client to the server side, then parse on the server, generate execution plans, execute, and return results to the client. where "execution" can be considered the most important phase of the entire life cycle, including

A large number of calls to retrieve data to the storage engine, as well as post-invocation data processing, including sorting, grouping, and so on.

3). In completing these tasks, the query needs to spend time in different places, including the network, CPU compute, generating statistics and execution plans, lock waits (mutually exclusive waits), and so on, especially the call operations that retrieve data to the underlying storage engine, which require in-memory operations, CPU operations

And out-of-memory time-consuming IO operations, depending on the context, can result in a large number of context switches and system calls.

2. Slow Query Basics: Optimizing data access

The most basic reason for poor query performance is that there is too much data to access. Some queries may inevitably need to filter large amounts of data, but this is not uncommon. Most poorly performing queries can be optimized in a way that reduces the number of accesses. For inefficient queries, you can analyze them in the following two steps:

1). Verify that the application is retrieving a large amount of data than is needed. This usually means that too many rows have been accessed, but sometimes it is possible to access too many columns.

2). Verify that the MySQL server layer is analyzing a large number of data rows that are more than needed.

2.1 Do you request unwanted data from the database?

1). Some typical cases

A. Querying for unwanted records: a common mistake is that MySQL is often mistaken for only the data that is needed, but in fact MySQL returns all the result sets in the calculation. The simplest and most effective solution is to add a limit after such a query.

B. Returning all columns when multiple table associations are associated

C. Always take out all columns: Every time you see a select *, you need to look at it with suspicion, is it really necessary to return all the columns? Removing all the columns will make the optimizer unable to complete an index overlay scan such optimizations, and will also bring additional network, IO, memory, and

CPU consumption.

D. Querying the same data repeatedly: a good solution is to cache the data when it is first queried and take it out of the cache when needed, so that performance is better.

2.2 MySQL is scanning for additional records:

1). For MySQL, the simplest three metrics to measure query overhead are:

A. Response time: Response time is the sum of two parts: service time and queue time. Service time is how long it really takes the database to process this query. Queuing time is when the server waits for some resources and does not actually execute the query-it may be the completion of an IO operation, or

Is waiting for a row lock and so on.

B. Number of rows scanned and number of rows returned: When you parse a query, it is helpful to see the number of rows scanned by that query. This is to some extent an indication that the query is inefficient in finding the data it needs.

C. Number of rows scanned and types of access: When evaluating query overhead, consider the cost of finding a row of data from a table. There are several ways to access MySQL to query and return a row of results. There are ways that you might need to scan many rows to return a row of results, and some access

The method may return results without scanning.

The type column in the Expalin statement reflects the access types. There are many types of access, from full table scans to index scans, range scans, unique index scans, constant references, and so on. These are listed here, the speed is from slow to fast, the number of rows scanned is from more to less. You don't have to remember this.

Types of access, but need to understand the concepts of scan tables, scanned indexes, scope access, and single-valued access.

2). General MySQL can use the following three ways to apply the where condition, from good to bad in turn:

A. Use the Where condition in the index to filter for unmatched records. This is done at the storage engine level.

B. Use an index overlay scan (using index in the extra column) to return records, filter unwanted records directly from the index, and return hit results. This is done at the MySQL server layer, but there is no need to return the table query records.

C. Return data from the data table and filter out records that do not meet the criteria (the using where is present in the extra column). This is done at the MySQL server layer, and MySQL needs to read the records from the data table and then filter.

3). If you find that a query needs to scan large amounts of data but only returns a few rows (using aggregate functions, etc.), you can usually try the following techniques to refine them:

A. Use the index overlay scan to put all required columns into the index so that the storage engine can return the results without having to fetch the corresponding rows back to the table.

B. Change the structure of the library table. For example, use a separate summary table.

C. Rewrite this complex query so that the MySQL optimizer can execute the query in a more optimal manner.

3. How to Refactor queries: Sometimes you can convert a query to a method that returns the same results, but with better performance.

3.1 A complex query or multiple simple queries

A. The important question to consider when designing a query is whether a complex query needs to be broken into a simple query. In traditional implementations, it is always emphasized that the database layer is required to do as much work as possible, and the logic of doing so is to always think of network communication,

Query parsing and optimization is a very expensive thing to do. But the idea is not for MySQL, and MySQL is designed to make connections and disconnects very lightweight and efficient in returning a small query result. The modern network is much faster than it used to be,

Whether it's bandwidth or latency.

B. mysql has the ability to scan millions of rows of data per second in memory, compared to the MySQL response data to the client is much slower. When other conditions are the same, it is certainly better to use as few queries as possible. But sometimes, a big query is broken down into

It is also necessary to have multiple small queries.

3.2 Segmentation query: Deleting old data is a good example. When a large amount of data is purged periodically, the cleavage can be done with a single big statement, which may involve locking up a lot of data at once, filling up the entire transaction log, exhausting system resources, and blocking many small but important queries.

It is also important to note that if you delete the data each time, you can pause for the next deletion and the server pressure will spread over a long period of time.

3.3 Decomposing The associated query:

The following advantages are reconstructed by the way the query is decomposed:

A. Make the cache more efficient. Many applications can easily query the corresponding result set using a table of CDs.

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 achieve high performance and scalability.

D. The efficiency of the query itself may also be improved.

E. Queries that can reduce redundant records. A subset of data may need to be accessed repeatedly in a management query.

F. Further, this is equivalent to implementing a hash association in the application instead of using the nested Loop Association of MySQL. Some scenario hash associations are much more efficient.

4. Basis for query execution:

The process of query execution:

1). Send a single query to the server

2). The server checks the query cache and, if the cache is hit, returns the results stored in the cache immediately. Otherwise proceed to the next stage.

3). The server side performs SQL parsing, preprocessing, and then generates the corresponding execution plan by the optimizer.

4). MySQL invokes the API of the storage engine to execute the query, based on the execution plan generated by the optimizer.

5). Returns the result to the client.

MySQL Note 03 Query performance optimizations