Mysql Page-Optimized _mysql

Recently, I helped my colleagues rewrite a MySQL SQL statement, the SQL statement involves two tables, one of which is a dictionary (a field needs to be returned), the other table is a business table (there are 150 fields in itself, all are returned), and of course the number of fields is not evaluated here. Usually, the returned data about 5w, the system can still receive data. But December 31 that day, the amount of data about 20w, resulting in SQL execution time is too long, not in the specified time feedback results, so the system directly error.

The general idea is to use MySQL paging function, that is, directly after the original SQL statement added limit clause. Note, though, that the feedback you see is just the number specified after limit, so take it for granted that MySQL simply retrieves the specified amount of data and then gives it back. In fact, the principle of MySQL internal implementation is to retrieve all records that match the Where condition, and then return the specified number of records. From this point of view, directly after the original SQL statement added limit clause can only be said to be a function of the scheme, but not necessarily optimal.

In this case, first let's look at the statistics for the tables in the 150 fields:

A row is about 2k, and the InnoDB default page is 16k, which means that up to 8 rows of data can be stored in a page. The likelihood of random reading is greatly increased. This will undoubtedly cause great pressure on the IO of the database system.

Before optimization

If you use the above scenario, which is to add the limit clause directly behind the original SQL statement, let's look at its implementation.

First, the following SQL statement is added directly after the limit clause (150 fields of the A1 table and one field in A2) are omitted:

Copy Code code as follows:

From Upay_csys_scquery_txn_log_his A1 the left JOIN Upay_csys_trans_code the A2 on (a1.int_trans_code=a2.trans_code) WHERE Status<> ' and settle_date=20151230 limit 50000,10000;

Its execution time is as follows:

Probably executed 32s, most of it was spent on sending data. Sending data refers to the time that the server retrieves and reads data and returns the data to the client.

With regard to the above implementation results, the following points need to be explained:

1. This is the result of multiple execution of the SQL statement so that the effect of the result cache can be excluded, in fact, each time the query is 32s or so.

2. Why the choice is limit 50000,10000, not 0,10000, this is mainly considered for the limit clause, the more to the back, the higher the cost of paging. Based on this, the median value is selected as the result of the paging.

The execution plan for the statement is as follows:

After optimization:

The idea of optimization:

Only the primary key of the table is paginated, and the value of other fields in the table is retrieved using the returned primary key as the result of the subquery.

The rewritten SQL statements are as follows:

Copy Code code 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<> ' and settle_date=2 0151230 ORDER by 1 limit 50000,10000) as T);

Its execution time is as follows:

Probably more than 3s, faster than the first scheme almost 10 times times, the effect is significant.

Here's a look at its implementation plan (explain extended)

Summarize:

1. The rewritten statement was originally as follows:

Copy Code code 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<> ' and settle_date=20151230 order by 1 L Imit 50000,10000);

But MySQL reported the following error:

Copy Code code as follows:

ERROR 1235 (42000): This version of MySQL doesn ' t yet support ' LIMIT & in/all/any/some subquery '

You need to add one more nested subqueries,

Such statements, for example, cannot be executed correctly.

Copy Code code as follows:

SELECT * FROM table where ID in (select id from table limit 12);

But, as long as you add another layer on the line. Such as:

Copy Code code as follows:

SELECT * FROM table where ID in (select T.id to (SELECT * from table limit) as T)

This allows you to bypass the problem of limit subqueries.
Solve the problem.

2. If you want to see the MySQL query optimizer equivalent rewrite SQL statement, you can first get a specific execution plan through explain extended, and then through show warnings view.

In this case, the equivalent rewrite SQL statement is as follows:

Consistent with the intended execution order ~

3. How to view the execution time of each step of the MySQL statement.

The above is the entire content of this article, I hope that the MySQL page optimization for everyone to help.