Before learning about MySQL caching, we must first understand an architecture of MySQL. After analyzing the architecture, we begin to have an in-depth understanding of caching.
1, MySQL architecture
Mysql database is a single process and multi-threaded architecture, which is similar to SQL Server, but different from Oracle. Oracle is a multi process architecture. Let me take a look at the specific architecture of MySQL:
MySQL architecture is divided into connection layer, service layer, storage engine layer and system file layer. Each layer has its own operation. Let's take a look at the specific operations in each layer.
1.1 connection layer
The connection layer is mainly responsible for connection management and identity authentication. After you enter mysql -uxxx -pxxx, the first thing you do when you come to MySQL is to verify your identity and permissions. Each client connection corresponds to a thread on the server. A thread pool is maintained on the server to avoid creating and destroying a thread for each connection. When the client connects to the MySQL server, the server authenticates it. It can be authenticated by user name and password, or by SSL certificate. After login authentication, the server will also verify whether the client has the operation permission to execute a query.
1.2 service layer
After successful authentication, we will come to the service layer. The service layer is the core of MySQL. The core service layer of MySQL is in this layer, including any SQL statements you execute. Ordinary DDL, DML, DQL, stored procedures, views, triggers, etc. need to go through some operations in this layer before they can be recognized by mysql. Mainly through SQL parser, SQL optimizer, etc., and finally through query cache. In short, all functions across storage engines are realized in this layer.
1.3 storage engine layer
We know that MySQL is different from Oracle and SQL Server in terms of storage engine. MySQL provides a pluggable storage engine, that is, "what storage engine is inserted, there is what function". The storage engine is really responsible for the storage and extraction of data in MySQL. The server communicates with the storage engine through API. Different storage engines have different functions. Different storage engines will directly determine how data is stored to disk.
1.4 system file layer
The data storage layer mainly stores the data on the file system running with the device, and completes the interaction with the storage engine.
1.5 execution process of SQL statement
1) The user sends an SQL statement to the MySQL server
2) First, query the cache. If there is data in the cache, respond directly. If there is no data in the cache, go to the next step
3) The parser performs a series of operations such as parsing SQL statements, preprocessing, optimizing SQL statements through the optimizer, and invokes the storage engine
4) The storage engine reads the data from the disk, and finally responds to the client in the form of MySQL (i.e. row and table), and stores a copy in the query cache.
2, MySQL caching mechanism
MySQL caching mechanism is to cache sql text and cache results, which are saved in KV form and then stored in the server memory. If the same sql is run, the server directly obtains the results from the cache without parsing, optimizing and executing the sql. If this table is modified, all caches in this table will no longer be valid, and the query cache value related entries will be cleared. Any change in the table is any data or structure change in the value table, including insert,update,delete,truncate,alter table,drop table or drop database, including those queries using merge table mapped to the changed table. Obviously, the query cache is not suitable for frequently updated tables. For some tables with unchanged data and a large number of the same sql queries, Query caching can save a lot of performance.
1.1 cache hit conditions
The cache has hit conditions, and not all SQL statements will enter the cache for search.
The cache is stored in a hash table. Query SQL, query database, client protocol, etc. as the key. Before judging the hit, MySQL will not parse the SQL, but use SQL to query the cache. Any difference in SQL characters, such as spaces and comments, will lead to cache miss. If the query has uncertain data, current_date(), then the results will not be cached after the query is completed, and those containing uncertain numbers will not be placed in the cache.
1.3 caching workflow
1) The server receives SQL and uses SQL and other conditions as key s to find the cache.
2) If the cache is found, the cache is returned directly.
3) If the cache is not found, execute SQL query, including original SQL parsing, optimization, etc.
4) After executing the SQL query results, put the SQL query results into the query cache.
1.3 cache description
1.3.1 enable query cache
MySQL closes the query cache by default. We need to open it in the configuration file.
Query whether the current database cache is enabled:
mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | OFF | +--------------------+ 1 row in set, 1 warning (0.00 sec)
Modify the configuration file: linux is / etc / my Conf for Windows is C: \ programdata \ MySQL \ MySQL server 5.7 \ my ini
Add under mysqld group:
query_cache_type=1
Restart MySQL service:
systemctl restart mysqld
View again:
mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | on | +--------------------+ 1 row in set, 1 warning (0.00 sec)
1.3.2 cache parameters
- Enter the following command to query cache related parameters
show variables like "%query_cache%";
have_query_cache: whether the current database version supports caching NO: not supported YES: supported
query_cache_limit: the maximum size of the query cache that can cache the result set generated by a single SQL statement. The result set of SQL statements exceeding this size will not be put into the cache.
query_cache_min_res_unit: the minimum unit size when allocating memory blocks
MySQL does not allocate query all at once_ cache_ Size memory is used as the cache, and the size of the whole query cache is divided into several memory blocks_ cache_ min_ res_ Unit determines the size of these blocks. It should be noted that even if the cached data does not reach a cache block size, it needs to occupy a cache block size space. If a single cache block is exceeded, multiple cache blocks need to be applied. When the query is completed and it is found that there is excess cache block memory, the excess cache block memory will be released, resulting in cache fragmentation.
query_cache_size: the total memory space used by the cache, in bytes. This value must be an integer multiple of 1024, otherwise the actual allocation of MySQL may be different from this value
query_cache_type: whether to open cache
OFF: turn OFF caching (default)
ON: enable cache
DEMAND: only SQL is explicitly written_ Only the query of cache will be written to the cache
select SQL_CACHE * from t_goods; -- Put the query results into the cache (if the cache is on)
select SQL_NO_CACHE * from t_goods; -- Do not cache query results
query_cache_wlock_invalidate: if a data table is locked, whether to still return data from the cache. The default is OFF, indicating that it can still be returned.
1.3.3 global cache status
Enter the following command to query the global cache status:
show global status like '%Qcache%';
- Qcache_free_blocks: the number of empty free blocks in the cache pool
- Qcache_free_memory: the amount of free memory in the cache
- Qcache_hits: Cache Hits
- Qcache_inserts: number of cache writes
- Qcache_lowmen_prunes: number of times to delete cache due to insufficient memory
- Qcache_not_cached: the number of times the query has not been cached. For example, the query result exceeds the size of the cache block, the query contains variable functions, or no data rows are queried, or SQL is used in the SQL statement_ NO_ Cache et al.
- Qcache_queries_in_cache: the number of SQL cached in the current cache
- Qcache_total_blocks: total number of cache block s
1.4 cache test
After understanding a series of cache parameters, we begin to manually test the hit and write of cache and the efficiency of cache under large amount of data.
1.4.1 cache hit test
- Create database:
create database test; use test;
- Create a test table:
CREATE TABLE `goods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `price` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
- Insert test data:
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (1, 'Huawei 4 G Full screen game phone', 'Huawei Mobile', 5299.00); INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (2, 'Shenzhou ares Game Book', 'Shenzhou notebook', 4599.00); INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (3, 'Xiaomi 5 G Full screen mobile phone', 'Mi phones', 2899.00); INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (4, 'Millet 4 G Game full screen camera phone', 'Mi phones', 1899.00);
- Query current cache usage:
-- Execute the query statement once first select * from goods; -- Check whether there are writes to the cache show global status like '%Qcache%';
Cache writes found + 1
We execute the SQL statement again to check the cache usage:
select*from goods; showglobalstatuslike'%Qcache%';
We execute multiple SQL statements to check the cache inserts and hits:
-- Write cache+1 select * from goods g; -- Look carefully SQL Statement (alias) -- Write cache+1 select * from goods where id=1; -- Write cache+1 select * from goods where id=2; -- Write cache+1 select * from goods g where id=1; -- Hit cache+1 select * from goods g; -- Hit cache+1 select * from goods where id=1; -- Hit cache+1 select * from goods where id=2;
Let's calculate it first:
The number of cache writes (Qcache_inserts) is 1 (the original one) + 4 = 5
The number of cache hits (Qcache_hits) is 1 (the original one) + 3 = 4
To view cache writes and hits:
show global status like '%Qcache%';
It should be noted here that even if some SQL queries fail to produce results, they will be written to the cache
- Let's do a test:
It is found that even if some SQL does not query the result set, it will be written to the cache, and the query will hit the cache again
1.4.2 cache emptying test
As mentioned above, any DML operation on a table will lead to caching, including insert,update,delete,truncate,alter table,drop table, etc.
To facilitate the test, I restart the MySQL server (all cache information is cleared):
systemctl restart mysqld
Execute SQL statement and observe the change of cache hit
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from goods where id=1; # Write cache
+----+-------------------------------+--------------+---------+
| id | title | name | price |
+----+-------------------------------+--------------+---------+
| 1 | Huawei 4 G Full screen game phone | Huawei Mobile | 5399.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)
mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1030296 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 | # An SQL is currently cached
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)
mysql> select * from goods where id=1; # Hit cache
+----+-------------------------------+--------------+---------+
| id | title | name | price |
+----+-------------------------------+--------------+---------+
| 1 | Huawei 4 G Full screen game phone | Huawei Mobile | 5399.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)
mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1030296 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 | # An SQL is currently cached
|