How Do I View Slow Queries in MySQL and Analyze the Efficiency of SQL Execution?

Issue. We have a sql that finds tables that don't have a primary key unique key, but it's running very slow on mysql, what should I do?

Experiment. Let's set up a MySQL environment, and the setup steps are omitted here.

Write a simple script to make a batch of tables with and without a primary key:

Execute the following script:

Now execute the following sql to see the effect:

Performed , it felt very slow.

Now let's use the DBA three-board axe and look at the execution plan:

It feels a bit miserable, because the information is a metadata table, and there is no necessary statistical information.

Let's show warnings and take a look at the sql after mysql rewrites

Let's format sql:

You can see that mysql will.

select from a where not in (select x from b) is a non-associated subquery.

I converted it. select from a where not exists (select 1 from b where = associated subquery.

If we are MySQL ourselves, we can use a very simple strategy when executing non-associative subqueries:

select from a where not in (select x from b where ..Non-Guanzhi Raid Lianzi Query: 1

Scan all the records in Table B, find the records that meet the conditions, store them in the temporary Table C, and build index 2Scan the records in table A, compare them with the records in temporary table C, and compare them directly in the index, while correlating subqueries requires circular iteration:

select from a where not exists (select 1 from b where = and ..The correlation subquery scans each record of Table A for RA: Scans Table B to find the first record that satisfies the RA criteria.

Obviously, the scan cost of an associated subquery will be higher than that of a non-associated subquery.

We hope MySQL will come first"Caching"The results of subqueries (caching this step is called materialization), but MySQL thinks that it is faster not to cache, so we need to give MySQL some guidance.

You can see that the execution time has become .

1.For metadata tables in the information schema, the execution plan does not provide valid information.

2.By looking at the SQL rewritten by MySQL, we guessed that the optimizer had a false positive.

3.We've added hints to guide MySQL to make correct optimization judgments.

But at present, our experiments are limited to guessing, and if we guess correctly, everything will be fine, and if we don't guess, we will not be able to make a good diagnosis.