Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities.

When the MySQL cache function is enabled, when the same SQL query statement is executed, it directly retrieves the result set and returns the result set. When data is modified, deleted, or added, the cache is cleared.

1 MySQL statement execution process

1 The client initiates a query to the server and sends the QUERY SQL to the MySQL server.

2 The server checks whether the cache exists. If a match is found in the cache, the server returns a message.

3 the server parses SQL, preprocesses SQL, and generates the corresponding execution plan by the optimizer.

4 MySQL invokes the storage engine API to perform the query based on the execution plan.

5 Return the result to the client.

2 Common MySQL cache parameters

Take the local MySQL database as an example. Use the Navicat connection tool or the command line to connect to MySQL

Procedure 1 Check whether the database has the cache function

show variables like 'have_query_cache';
Copy the code

2 Check whether the cache function is enabled for the database

show variables like 'query_cache_type';
Copy the code

3 Check the cache size

show variables like 'query_cache_size';
Copy the code

4 View common cache variables

-- % indicates fuzzy query
show status like 'Qcache%';
Copy the code

parameter meaning
Qcache_free_blocks Number of memory blocks available for the cache
Qcache_free_memory The amount of memory available for the cache
Qcache_hits Cache hits
Qcache_inserts The number of cached queries
Qcache_lowmen_prunes The number of queries that were removed from the query cache because of insufficient memory
Qcache_not_cached The number of uncached queries
Qcache_queries_in_cache Number of queries registered in the cache
Qcache_total_blocks Total number of blocks in the cache

3 Test the cache query

Create a new user table object:

Create table user
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT 'primary key ID',
  `name` varchar(30) DEFAULT NULL COMMENT 'name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `email` varchar(50) DEFAULT NULL COMMENT 'email'.PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add data
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (1.'Jone'.1.'[email protected]');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (2.'Jack'.20.'5%[email protected]');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (3.'Tom'.28.'[email protected]');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (4.'Sandy'.21.'[email protected]');
Copy the code

When you look again, you see that it is fetched directly from the cache.