Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of Profile in MYSQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

MYSQL--Profile analysis

When analyzing a SQL statement, it is found that adding a space at the end or front of the SQL statement does not affect fetching the result directly from the QC.

This is what the reference manual says:

The query must be identical (byte by byte) to be considered the same. In addition, the same query string may be thought to be different for other reasons. Queries that use different protocol versions or different default character sets are considered different queries and are cached separately.

Since it is to be exactly the same, the following two SQL should be different

Select count (*) from T1

Select count (*) from T1

But in the actual run, it is exactly the same SQL to execute

> SHOW PROFILES

+-+

| | Query_ID | Duration | Query | |

+-+

| | 1 | 0.00006925 | select count (*) from T1 |

| | 2 | 0.08126275 | insert into T1 values (6) |

| | 3 | 0.00043675 | select count (*) from T1 |

| | 4 | 0.00006850 | select count (*) from T1 |

| | 5 | 0.00021075 | select count (*) from T1 |

| | 6 | 0.00007150 | select count (*) from T1 |

| | 7 | 0.00007300 | select count (*) from T1 |

| | 8 | 0.00020975 | select count (*) from T1 |

+-+

8 rows in set (0.00 sec) www.2cto.com

And just add a space before and after the SQL statement, directly from the QC to get the data, no longer optimize, perform and other operations.

Mysql > SHOW PROFILE FOR QUERY 7

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000022 | |

| | checking query cache for query | 0.000007 | |

| | checking privileges on cached | 0.000005 | |

| | sending cached result to clien | 0.000034 | |

| | logging slow query | 0.000003 | |

| | cleaning up | 0.000003 | |

+-+ +

6 rows in set (0.00 sec)

Insert a space in the middle of the SQL to run as a different SQL.

Mysql > SHOW PROFILE FOR QUERY 8

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000023 | www.2cto.com |

| | checking query cache for query | 0.000049 | |

| | Opening tables | 0.000013 | |

| | System lock | 0.000005 | |

| | Table lock | 0.000037 | |

| | init | 0.000012 | |

| | optimizing | 0.000006 | |

| | executing | 0.000012 | |

| | end | 0.000004 | |

| | query end | 0.000003 | |

| | freeing items | 0.000036 | |

| | storing result in query cache | 0.000006 | |

| | logging slow query | 0.000003 | |

| | cleaning up | 0.000002 | |

+-+ +

14 rows in set (0.00 sec)

As you can see from the above, when QC stores the SQL statement, it removes the space between the beginning and the end. And in the query, also automatically remove the beginning and tail of the space, and then go to the QC inside to compare.

The above is all the content of the article "sample Analysis of Profile in MYSQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.