MySQL paging Optimization

MySQL paging Optimization

This afternoon, I helped my colleagues rewrite a MySQL SQL statement that involves two tables, one of which is a dictionary table (a field needs to be returned ), the other table is a business table (which contains 150 fields and must be returned in full). Of course, whether the number of fields is reasonable is not evaluated here. Usually, the returned data is about million, and the system can still receive the data. However, on January 1, the data volume was about million, resulting in SQL Execution time being too long and the results could not be fed back within the specified time. Therefore, the system reported an error directly.

The general idea is to use the paging function of MySQL, that is, directly add the LIMIT clause after the original SQL statement. However, please note that although the feedback you see is only the number specified after the LIMIT, it is assumed that MySQL only retrieves the specified amount of data and returns it. In fact, the internal implementation of MySQL is to retrieve all records that meet the where condition and then return a specified number of records. From this point of view, adding the LIMIT clause directly after the original SQL statement can only be said to be a function implementation solution, but it may not be optimal.

In this example, we first take a look at the statistical information of the table with 150 fields:

A row occupies about 2 k, while the Innodb rows page size is 16 k, which means that up to 8 rows of data can be stored on one page. The possibility of random reading is greatly increased. This will undoubtedly put a lot of pressure on the IO of the database system.

Before Optimization

If the above scheme is adopted, that is, the LIMIT clause is directly added after the original SQL statement. Let's take a look at its execution.

First, the SQL statement after the LIMIT clause is directly added is as follows (150 fields in table a1 and one field in Table a2 have been omitted ):

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) WHERE STATUS<>'00' AND settle_date=20151230 limit 50000,10000;

The execution time is as follows:

It takes about 32 s to execute, and most of them are spent on Sending data. Sending data refers to the time when the server retrieves data, reads data, and returns the data to the client.

 

The preceding execution results must be described as follows:

1. This is the result of multiple SQL statements, so that the impact of result caching can be ruled out. In fact, the duration of each query is about 32 s.

2. Why do I choose limit, instead of? This is mainly because, for the LIMIT clause, the later the limit clause, the higher the paging cost. Based on this, the median value is selected as the paging result.

The statement execution plan is as follows:

 

After optimization:

Optimization ideas:

Only the primary keys of the table are paged, and then the returned primary keys are used as the results of the subquery to retrieve the values of other fields in the table.

The SQL statement after rewriting is as follows:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id from (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000) as t);

The execution time is as follows:

More than 3 s, which is about 10 times faster than the first solution, with remarkable results.

Next let's take a look at its execution plan (explain extended)

 

Summary:

1. The modified statement is originally as follows:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000);

However, MySQL reports the following error:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

You need to add a nested subquery,

For example, such statements cannot be correctly executed. Select * from table where id in (select id from table limit 12); however, you only need to add another layer. For example, select * from table where id in (select t. id from (select * from table limit 12) as t) can bypass the issue of the limit subquery. Solve the problem.

2. If you want to view the SQL statement after the MySQL query optimizer is equivalent to rewriting, You can first get the specific execution plan through explain extended, and then view it through show warnings.

In this example, the SQL statement after equivalent rewriting is as follows:

Consistent with the execution sequence in the assumption ~

3. For details about how to view the execution time of MySQL statements, see:

This article permanently updates the link address: