The connector

Note that the memory temporarily used during mysql execution is managed in the connection object and will be released only when the connection is disconnected.

Solution: 1. Disconnect the link periodically (usually done by proxy layer) 2. 5.7 Can be used latermysql_reset_connectionTo initialize the link resource

show processlist ; -- Check all connections.Copy the code

The cache

It is stored in the form of key value, where key is an SQL statement and value is a query result. Caching is not recommended,

Caching is suitable for scenarios where static tables, such as system configuration tables, are removed completely from the cache in mysql8.0


analyzer

In addition to this analysis, the presence of tables and fields is determined. It’s all in the parser layer.


The optimizer

After the profiler, mysql already knows what to do, and again it needs to be optimized. Take the following SQL for example

select * from t1 join t2 using(ID) where t1.a = 1 and t2.b = 1;Copy the code

1. Execute t1 and find the value of t2.b=1.

2. Execute t2 and find b=1, associate T1, and find t1.

It is up to the optimizer to decide which rule to use.


actuator

Mysql knows what it wants to do with profilers. Know what to do with the optimizer. So it goes into the actuator

First check whether there is permission, if there is an interface that will call the storage engine, such as the following SQL

select * from T where name = 't'; -- name No indexCopy the code

1. Innodb storage engine will fetch the first row of the table, determine whether name is equal to t, not skip, but save to the result set

2. Take the next line, repeat the same logic until all results are iterated, and then return the satisfied result set to the client.


The ROws_EXAMINED field in the slow query log tells you how many rows this statement scanned.