Optimization of query performance

A query does not perform well, most of the reason is due to the large amount of data. Many queries filter out a large amount of data and do not work. In fact, most bad statements have access to less data. There are two steps we can take to analyze performance-poor query statements.

Find out if your application is getting more data than you need. It means that it accesses too much data, but it may also have access to too many columns.

Find out if the MySQL server is analyzing too many rows.

Is the data obtained from the database more than you need?

Some query statements get a lot of unwanted data and then throw them away. This requires the MySQL server to do additional work, increased the burden on the network, and consumes the application server's memory and CPU resources.

Here are some common mistakes.

Get the unwanted rows

One of the most common errors is that MySQL provides the data that it needs, instead of counting and returning all the data sets. We see this error often occurs in applications designed by people who are familiar with other databases. These developers often use Select to get many rows, then take the first n rows, and close the dataset. They may consider that MySQL will provide 10 lines and abort the execution of the query, but the full result set is generated when MySQL is really doing it. The client library gets all the rows and discards many rows. The best solution is to add the limit condition after the query.

Get all columns in a table's multiple-join query

If you want to get all the actors that appear in the Academy dinosaur film, don't write the following statement

mysql> SELECT * FROM sakila.actor
　　　 -> INNER JOIN sakila.film_actor USING(actor_id)
　　　 -> INNER JOIN sakila.film USING(film_id)
　　　 -> WHERE sakila.film.title = 'Academy Dinosaur';

This query returns all the columns for each of the three tables. The correct statement is as follows:

Mysql> SELECT sakila.actor.* from Sakila.actor ...;

Get all the columns

You must be skeptical when you see the select *. Do you really need all the columns? Maybe not. Getting all the columns may invalidate some optimizations, such as overwriting the index, which will increase I/O, memory, and CPU consumption.

Some DBAs have disabled select * for this reason, and a table-modified column also reduces the chance of an error.

Of course, it's not always bad to get some data that's beyond what you need. In many of the cases we've studied, people have told us that this wasteful approach simplifies development by allowing developers to use the same code in different places. This can be considered, as long as you understand the performance of the consumption of the line. It's also useful to get unwanted data if you've applied some caching schemes or some other good ideas in your program. Getting and caching all of the objects may be better than a lot of the data being retrieved separately.

Does MySQL check for too much data?

Once you've determined that your query gets the data you need, you can see if the query checks for too much data. In MySQL, the simplest consumption metric is:

Execution time

Number of rows checked

Number of rows returned

No metric can be used to measure the cost of a query perfectly. But they can reflect the data that MySQL performs a query and can approximate the speed at which the query runs. These three metrics are recorded in the slow query log, so it's best to see the slow query log if you want to know if you're checking for too much data.

Execution time

In the second chapter we have already discussed, in MySQL5.0 and previous versions, the slow query log has a lot of limitations. Including the lack of finer granularity of the log.

Fortunately, there are a number of patches that allow you to record and measure the microseconds level of the query. They are all included in the MySQL5.1 server, but if you are using the old version, you can only patch it. Be careful not to overly value execution time. The reason for this indicator is that it is an indicator of a goal, but it is not always the same under changing conditions. Other factors-such as storage engine locks, high concurrency, and hardware-can affect execution time. This metric is very helpful for F to find query statements that apply response time or the impact of server fetching, but it does not give the actual execution time.