How to optimize MySQL query speed

In the previous chapters, we introduced how to choose optimized data types and how to use indexes efficiently, which are essential for high-performance MySQL. But this is not enough, reasonable query design is also needed. If the query is poorly written, high performance cannot be achieved even if the table structure is reasonable and the index is appropriate.

When it comes to MySQL performance optimization, query optimization is the source of optimization, and it is also the best indicator of whether a system is faster. This chapter and the following chapters will focus on query performance optimization. We will introduce some query optimization techniques to help you have a deeper understanding of how MySQL actually executes queries, where it is slow, how to make it faster, and understand the reasons for high efficiency and low efficiency. This will help you better optimize query SQL statements.

This chapter starts with "Why is the query speed so slow?", so that you can clearly know where the query may be slow. This will help you better optimize the query and be one step ahead of others.

1. Where is the slowness?

The true measure of query speed is response time. If you think of a query as a task, it is composed of a series of subtasks, each of which takes a certain amount of time. If you want to optimize a query, you actually want to optimize its subtasks, either by eliminating some of them, reducing the number of times a subtask is executed, or making a subtask run faster.

When MySQL executes a query, what are the subtasks and which subtasks take the most time? This requires the use of some tools or methods (such as: execution plan) to analyze the query to locate where the slowness lies.

Generally speaking, the life cycle of a query can be roughly viewed in order: from the client to the server, then parsed on the server, an execution plan is generated, executed, and the results are returned to the client . Among them, "execution" can be considered the most important stage in the entire life cycle, which includes a large number of calls to the storage engine to retrieve data and data processing after the call, including sorting, grouping, etc.

When completing these tasks, queries need to spend time in different places at different stages, including network, CPU calculation, generating statistics and execution plans, lock waiting and other operations, especially the call operations to retrieve data from the underlying storage engine. These calls require memory operations, CPU operations, and may also generate a large number of context switches and system calls.

In all of the above operations, a lot of time will be consumed, and there will be some unnecessary additional operations. Some operations may be repeated many times, and some operations may be executed very slowly. This is where queries can actually be slow, and the goal of query optimization is to reduce or eliminate the time spent on these operations .

Through the above analysis, we have a comprehensive understanding of the query process and can clearly know where the query may have problems, which ultimately leads to the slowness of the entire query, providing direction for actual query optimization.

In other words, query optimization can be carried out from the following two perspectives:

• Reduce the number of subqueries
• Reduce extra, repetitive operations

A common cause of poor query performance is accessing too much data. When the amount of data is small, the query speed is good. Once the amount of data increases, the query speed will change dramatically, which will drive people crazy and provide a very bad experience. For query optimization, you can check from the following aspects:

• Is unnecessary data queried?
• Whether additional records were scanned

2. Have you queried unnecessary data?

In actual queries, the data actually needed is often queried, and then the redundant data is discarded by the application. This is additional overhead for MySQL and also consumes CPU and memory resources of the application server.
Some typical cases are as follows:

1. Query unnecessary records

This is a common mistake. People often mistakenly believe that MySQL will only return the required data, but in fact MySQL returns the entire result set before performing calculations.

Developers usually use SELECT statements to query a large number of results, and then use the application query or the front-end display layer to obtain the first N rows of data. For example, 100 records are queried on a news website, but only the first 10 are displayed on the page.

The most effective solution is to query as many records as needed, usually adding LIMIT after the query, that is: paginated query.

2. Return all columns when multiple tables are associated

If you want to find all the actors who appeared in the movie Academy Dinosaur, don't do the following:

select * from actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

This will return all the data columns of the three tables, but the actual requirement is to query the actor information. The correct way to write it should be:

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. Always query all columns

Every time you see select *, you must look at it with a strange look. Do you really need to return all the data columns?

In most cases, it is not necessary. Select * will result in a full table scan, which will prevent the optimizer from completing optimizations such as index scans. Too many columns will also cause additional I/O, memory, and CPU consumption for the server. Even if you really need to query all the columns, you should list all the columns one by one instead of *.

4. Repeatedly query the same data

If you're not careful, it's easy to make the mistake of executing the same query over and over again, returning the exact same data each time.

For example, if you need to query the URL of the user's avatar in the user's comment area, then if the user comments multiple times, you may query this data repeatedly. A better way to handle this is to cache the data when it is first queried, and then retrieve it directly from the cache when it is used subsequently.

3. Are additional records scanned?

After you make sure that your query is only looking for the data you need, you should next check whether the query is scanning too much data. For MySQL, the three simplest metrics for measuring query overhead are as follows:

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

No single metric can fully measure the cost of a query, but they can roughly reflect how much data MySQL needs to access when executing a query, and can roughly estimate the actual time it takes to run a query. These three indicators are recorded in the MySQL slow log, so checking the slow log records is a way to find out the queries that scan too many rows.

Slow query: Used to record statements in MySQL whose response time exceeds the threshold (long_query_time, default 10s), and record the slow query in the slow log. You can enable slow query through the variable slow_query_long. It is closed by default. Slow logs can be recorded in the slow_log table or files for inspection and analysis.

1. Response time

Response time is the sum of two parts: service time and queue time. Service time refers to how long it actually took the database to process the query. Queue time refers to the time when the server does not actually execute the query because it is waiting for some resources, which may be waiting for I/O operations, waiting for row locks, etc.

There is no consistent pattern or formula for response time under different types of application stress. Many factors, such as storage engine locks (table locks, row locks), high-concurrency resource competition, and hardware response, can affect response time. Therefore, response time can be both the result and the cause of a problem, depending on the case.

When you see the response time of a query, the first thing you need to ask yourself is whether this response time is a reasonable value.

2. Number of rows scanned and number of rows returned

When analyzing a query, it is helpful to see the number of rows scanned by the query, and also to analyze whether additional records were scanned.

This metric may not be perfect for identifying bad queries because not all rows have the same cost to access. Shorter rows are accessed quite quickly, and rows in memory can be accessed much faster than rows on disk.

Ideally, the number of rows scanned and the number of rows returned should be the same. But in fact, this kind of beautiful thing is not common. For example, when doing a join query, the ratio of the number of rows scanned to the number of rows returned is usually very small, usually between 1:1 and 10:1, but sometimes this value can be very large.

3. Number of rows scanned and access type

When evaluating query overhead, you need to consider the cost of finding a row of data in the table. MySQL has several access methods that can find and return a row of results. These access methods may need to access many rows to return a result, and some access methods may return results without scanning.

The type column in the EXPLAIN statement of the execution plan reflects the access type. There are many types of access, from full table scan to index scan, range scan, unique index, constant index, etc. The speeds listed here are from slow to fast, and the number of rows scanned is from most to least.

If the query cannot find the appropriate access type, the best solution is usually to add a suitable index, which is the issue of indexes we discussed earlier. Now it should be clear why indexes are so important for query optimization. Indexes allow MySQL to find required records in the most efficient way, scanning the least number of rows.

If you find that your query scans a lot of data but returns only a few rows, you can usually try the following techniques to optimize it:

• Use index covering scan to put all the required columns into the index, so that the storage engine can return the results without going back to the table to get the corresponding rows.
  
• Optimize table structure. For example, use a separate summary table to complete the query.
• Rewrite complex queries so that the MySQL optimizer can execute the query in a more optimal way.

The above is the details of how to optimize MySQL query speed. For more information about optimizing MySQL query speed, please pay attention to other related articles on 123WORDPRESS.COM!