Interviewer: Have you confused the query cache with the Buffer Pool? Talk about?

Caches – Query cache

The following figure shows the MySQL architecture architecture.

The query Cache is often referred to as the Cache in the figure below.

Caches are in the Server tier if MySQL is split into the Server tier and the storage engine tier.

Another thing you should know:

When an SQL query is called to MySQL Server, the MySQL Server first checks whether the SQL query has been executed in the query cache. If yes, the query results are stored in the query cache as key-values. Key is the SQL statement, and value is the query result. We call this process query caching!

If the query cache does not contain the data you are looking for, MySQL will perform subsequent logic to retrieve the data through the storage engine. And the query cache will be shared by the cache for sessions, yes, it will be shared by all sessions.

Disadvantages of query caching:

The query cache for the table is invalidated whenever a SINGLE SQL update is made to the table. So when your business has a similar ratio of table CRUD, the query cache may be affecting the throughput efficiency of your application.

You can disable query caching with the query_chache_type=demand parameter. And in mysql8.0, the query cache module has been removed.

Therefore, you can consider whether it is necessary to disable a feature based on your situation

Second, the Buffer Pool

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

It is well known that any connection pool, cache pool, or XXX pool is designed for acceleration. For example, in order to speed up data reading, the file system of the operating system performs inefficient random DISK I/O every time and designs a buffer write mechanism.

The Buffer Pool is a Buffer mechanism designed by the MySQL storage engine to speed up data reading. The gray part of the image below is the brain map of BufferPool. (The words are authentic, very delicate!)

As shown in the figure above, the units in Buffer are cached pages. A cache page is essentially a page of data read into memory from disk. Data pages contain rows of records. The default size in the Buffer Pool is 128MB and the data page size is 16KB.

If you don’t know what a data page is, you can understand this: InnoDB organizes data in the smallest unit as a data page. Simply put, MySQL always reads data pages from disk at the default size of 16KB. There are many pieces of data in the data page.

The descriptions in the Buffer Pool are organized together in a bidirectional linked list (LRU). With the description of the data page, we can find the location of the cache page it describes.

The query cache belongs to MySQLServer and the Buffer Pool belongs to the storage engine. Another point to remember is that the Buffer Pool is controlled by InnoDB’s optimized LRU algorithm to drain the aged data from the cache, so that the data cached in the Buffer Pool will not be completely invalidated at one time.