MySQL Query Cache and Buffer Pool

1. Caches - Query Cache

The following figure is provided by the MySQL official website: MySQL architecture system diagram.

The query cache that people often refer to is the Cache part in the figure below.

If MySQL is divided into two parts: the server layer and the storage engine layer, then Caches are located in the server layer.

In addition, you should know:

When a SQL statement is sent to MySQL Server, MySQL Server will first check the query cache to see if the SQL statement has been executed before. If it has been executed before, the query results of the previous execution will be saved in the query cache in the form of Key-Value. The key is the SQL statement and the value is the query result. We call this process query caching!

If the data you are looking for is not in the query cache, MySQL will execute the subsequent logic and retrieve the data through the storage engine. And the query cache will be shared cache for sessions, yes, it will be shared by all sessions.

Disadvantages of query cache:

As long as there is a SQL update on the table, the query cache of the table will become invalid. So when the proportion of table CRUD in your business is similar, query cache may affect the throughput efficiency of the application.

You can disable query caching by setting the parameter query_chache_type=demand. And in the MySQL 8.0 version, the query cache module has been deleted.

Therefore, you can consider whether it is necessary to disable a function according to your own situation.

Buffer Pool

Again, if MySQL is divided into two parts: the server layer and the storage engine layer, then the Buffer Pool is located in the storage engine layer.

In fact, everyone knows that whether it is a connection pool or a cache pool, as long as it is an XXX pool, it is designed for acceleration. For example, in order to speed up data reading, the file system of the operating system designs a buffered write mechanism for inefficient random disk IO every time.

The Buffer Pool is a buffering mechanism designed by the MySQL storage engine to speed up data reading. The gray part in the picture below is the mind map of BufferPool. (The handwriting is authentic, very beautiful!)

The above is the details of MySQL query cache and Buffer Pool. For more information about MySQL query cache and Buffer Pool, please pay attention to other related articles on 123WORDPRESS.COM!