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

In ORACLE, we often recommend using exists instead of in, which can also achieve better optimization results. In the process of moving ORACLE applications to MYSQL, we found that some of the sub-query statements of in were brought into MYSQL, and its execution efficiency became very low, which is very unthinkable. So, I analyzed a wave.

Make an associated query on two tables, one large and one small:

Mysql > select count (*) from users;+-+ | count (*) | +-+ | 19 | +-1 row in setmysql > select count (*) from orders;+-+ | count (*) | +-+ | 86310 | +-+ 1 row in setmysql >

Turn on profile and find that no matter whether the subquery is a large table or a small table, the statement of exists is always slower than that of in:

Mysql > show profiles +-+ | Query_ ID | Duration | Query | +-- -+ | 1 | 1.08661625 | select count (1) from orders o where o.user_id in (select u.id from users u) | | 2 | 1.56956275 | select count (1) from orders o where exists (select 1 from users u where u.id = o. User_id) | | 3 | 0.81266425 | select count (1) from users u where u.id in (select o.user_id from orders o) | | 4 | 8.4164905 | select count (1) from users u where exists (select 1 from orders o where u.id = o.user_id) | +- -+ 4 rows in set

Looking at the profile content of the exists statement, it is found that there are multiple executing and sending data procedures, which is the main time-consuming process of the entire sql execution:

Mysql > show profile for query 2 +-- +-+ | Status | Duration | +-+-+ | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.5E-5 | | executing | 2E-6 | | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | | | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 1E-6 | | Sending data | 1.3E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.7E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1. 2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.1E-5 | | executing | 2E-6 | Sending data | 1.2E-5 | | executing | 1E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.1E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.2E-5 | | executing | 2E-6 | | Sending data | 1.5E-5 | | end | 3E-6 | | query end | 3E-6 | | | waiting for handler commit | 1E-5 | | closing tables | 9E-6 | | freeing items | 0.000152 | | cleaning up | 1.7E-5 | +-+-+ 100 rows in set |

In the in subquery, the sending data process is performed only once, which is the main time-consuming part of the entire sql execution:

Mysql > show profile for query 1 +-- +-+ | Status | Duration | +-- +-+ | starting | 9. 3E-5 | | Executing hook on transaction | 6E-6 | | starting | 8E-6 | | checking permissions | 5E-6 | | checking permissions | 4E-6 | | Opening tables | 0.004849 | init | 1.8E-5 | | System lock | 1.4E-5 | | optimizing | | 1.4E-5 | | statistics | 3.1E-5 | | preparing | 2.2E-5 | | executing | 3E-6 | | Sending data | 1.081273 | "| end | | | 1.3E-5 | | query end | 3E-6 | | waiting for handler commit | 1E-5 | | closing tables | 5.2E-5 | | freeing items | 0.000171 | | cleaning up | 2.9E-5 | + -+

About sending data and executing parsing:

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Sending data is: the thread is reading and processing rows for a select statement and sending data to the client. Because operations tend to be heavily disk accessed (read) during this period, this is often the longest-running phase of the entire query cycle. (this is the interpretation of MySQL 5.5. the interpretation of 5.7is exactly the same, but 5.7has an extra Sending to client state.)

This makes it clear that Sending data does reading, processing (filtering, sorting, etc.). ) and send three things, and then look at the cpu and io information in this state to analyze whether the bottleneck of the statement is read or processed, and then make appropriate optimization adjustments.

Executing

The thread has begun executing a statement.

Constantly executing and Sending data in the exists statement should be constantly scanning the crawling data for matching, which should be related to the algorithm of MYSQL, which is actually checked in multiple subqueries join_execution through trace:

{"subselect_execution": {"select#": 2, "steps": [{"join_execution": {"select#": 2 "steps": [] / * steps * /} / * join_execution * /}] / * steps * /} / * subselect_execution * /}

Do you want to know if this is a chicken rib? I wonder if there is any relevant analysis.

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.