Shulou(Shulou.com)06/01 Report--

The derived table in the initial MySQL is still in an accidental problem scenario.

The following statement throws an error when executed.

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (r.id)

FROM payment_data r

Where data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329'))

ERROR 1093 (HY000): You can't specify target table 'rr' for update in FROM clause will understand if you know enough about the MySQL query optimizer. In fact, this method is not supported by MySQL. Is there any WA, that is, through a special subquery, that is, derived table?

So the above statement can be cracked in the following way.

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (t.id)

FROM (select id,data_no from payment_data r) t

Where t.data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329'))

Let's go back to the Derived table just mentioned, which is what it says in the official documentation.

Derived tables is the internal name for subqueries in the FROM clause. To fully illustrate derived table, I'll give an example of the hapless t_fund_info table.

First look at two pieces of data as the basic data for our test, where id is the primary key column.

> select id from t_fund_info limit 1 and 2

+-+

| | id |

+-+

| | 138031 |

| | 1754906 |

+-+ if you query according to the id column, you will find it extremely efficient.

> select * from t_fund_info where id=138031

. . .

1 row in set (0. 01 sec) if we look at the execution plan, we will find that it is the scanning mode of primary key.

> explain select * from t_fund_info where id=138031

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | t_fund_info | const | PRIMARY | PRIMARY | 8 | const | 1 |

+-- +

1 row in set (0.01 sec) so let's move on to a different way of thinking, using two different derived table

The first kind:

> select * from (select id from t_fund_info) t where t.id=138031

1 row in set (1. 12 sec) look at the execution plan at this time and you will see the word derived table.

> explain select * from (select id from t_fund_info) t where t.id=138031

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 1998067 | Using where |

| | 2 | DERIVED | t_fund_info | index | NULL | account | 182 | NULL | 2127101 | Using index |

+-- +

2 rows in set (0.90 sec) seems to be the execution speed of 1 second, and the difference is not very big, so let's go to the second way.

> select * from (select * from t_fund_info) t where t.id=138031

ERROR 126 (HY000): Incorrect key file for table'/ tmp/#sql_3e34_0.MYI'; try to repair it

At this point, you will find that such a seemingly simple query has thrown an error.

Looking at the information in the error is a MYI file, obviously using a temporary table, a typical myisam table.

To verify this process, I collected the file usage in the / tmp directory as completely as possible. As you can see, it took up more than 2G of space, and finally found that there was not enough disk space to exit.

# df-h | grep\ / tmp

/ dev/shm 6.0G 4.1G 1.6G 73% / tmp

/ dev/shm 6.0G 4.5G 1.2G 79% / tmp

/ dev/shm 6.0G 4.8G 903M 85% / tmp

/ dev/shm 6.0G 4.9G 739M 88% / tmp

/ dev/shm 6.0G 5.0G 625m 90% / tmp

/ dev/shm 6.0G 5.2G 498M 92% / tmp

/ dev/shm 6.0G 5.3G 386M 94% / tmp

/ dev/shm 6.0G 5.4G 250m 96% / tmp

/ dev/shm 6.0G 5.5G 110m 99% / tmp

/ dev/shm 6.0G 5.7g 4.0K 100% / tmp

/ dev/shm 6.0G 3.7G 2.0G 66% / tmp

/ dev/shm 6.0G 3.7g 2.0G 66% / tmp there is another question here, that is, this table t_fund_info is an InnoDB table, occupying about 400m of space, but the derived table utilization rate has reached more than 2G. I don't know how to deal with it further inside MySQL.

-rw-rw---- 1 mysql mysql 9545 Oct 20 2016 t_fund_info.frm

-rw-rw---- 1 mysql mysql 482344960 Oct 20 2016 t_fund_info.ibd obviously shows that this approach still has potential performance problems. Does the myisam table occupy more space? obviously not. I tested the myisam table with the same amount of data, and the space is about 270m.

Is there any room for improvement in this way? Let's try the view to express the same meaning.

> create view test_view as select * from t_fund_info

Query OK, 0 rows affected (0.00 sec)

> select * from test_view where id=138031

. . .

1 row in set (0.01 sec)

The execution plan is exactly the same as the primary key execution plan.

So one way to improve derived table is to improve it through view, and the other is to avoid using it as much as possible.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.