Explication avancée de l'optimisation du cache de requêtes MySQL-tutoriel mysql-php.cn

Explication avancée de l'optimisation du cache de requêtes MySQL

Optimisation du cache de requêtes MySQL

1 Présentation
2 Processus de fonctionnement
3 Requête Configuration du cache
4 Activer le cache de requêtes
5 Option SELECT du cache de requêtes
6 Échec du cache de requêtes

(apprentissage gratuit recommandé : Tutoriel vidéo MySQL)

1. Activez le cache de requêtes de MySQL. Lorsque exactement la même instruction SQL est exécutée, le serveur lira les résultats directement à partir du cache. Lorsque les données sont modifiées, le cache précédent sera invalide. Les tables fréquemment modifiées ne conviennent pas à la mise en cache des requêtes.

2. Processus d'opération

1. Le client envoie une requête au serveur

2. Serveur Le cache des requêtes sera vérifié en premier, et si le cache est atteint, les résultats stockés dans le cache seront renvoyés immédiatement. Sinon, passez à l'étape suivante : Explication avancée de loptimisation du cache de requêtes MySQL 3. Le serveur effectue une analyse et un prétraitement SQL, puis l'optimiseur génère le plan d'exécution correspondant
4. MySQL appelle l'API du moteur de stockage en fonction du plan d'exécution généré par ; l'optimiseur. Exécuter la requête ; 5. Renvoyez les résultats au client.

3. Configuration du cache de requêtes

Vérifiez si la base de données MySQL actuelle prend en charge le cache de requêtes :

 SHOW VARIABLES LIKE 'have_query_cache';

Copier après la connexion

rrree

Vérifiez si le cache des requêtes est actuellement activé dans MySQL :

mysql> SHOW VARIABLES LIKE 'have_query_cache';+------------------+-------+| Variable_name    | Value |+------------------+-------+| have_query_cache | YES   |+------------------+-------+1 row in set (0.26 sec)

Copier après la connexion

Afficher la taille d'occupation du cache de requête :

mysql>  SHOW VARIABLES LIKE 'query_cache_type';+------------------+-------+| Variable_name    | Value |+------------------+-------+| query_cache_type | OFF   |+------------------+-------+1 row in set (0.01 sec)

Copier après la connexion

Afficher les variables d'état du cache de requêtes :

mysql> SHOW VARIABLES LIKE 'query_cache_size';+------------------+----------+| Variable_name    | Value    |+------------------+----------+| query_cache_size | 16777216 |+------------------+----------+

Copier après la connexion

4 . Activez le cache de requêtes

参数	含义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
Qcache_hits	查询缓存命中数
Qcache_inserts	添加到查询缓存的查询数
Qcache_lowmen_prunes	由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached	非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

Le cache de requêtes MySQL est désactivé par défaut. Vous devez configurer manuellement le paramètre query_cache_type pour activer le cache de requêtes. query_cache_type Ce paramètre a trois valeurs possibles :

如何设置query_cache_type的值呢，这里我们需要修改MySQL的配置文件
博主的Ubuntu（Linux操作系统）版本为16.04，mysql版本为5.7。需要进入到/etc/mysql/mysql.conf.d下修改配置文件mysqld.cnf
Explication avancée de loptimisation du cache de requêtes MySQL
添加以下内容

然后需要重启MySQL服务

再登录MySQL
Explication avancée de loptimisation du cache de requêtes MySQL
此时可再查询MySQL查询缓存是否开启

配置完毕之后，重启服务既可生效；

然后就可以在命令行执行SQL语句进行验证，执行一条比较耗时的SQL语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存。

我们可以进行测试，我们曾经建了一张表tb_item,里面有250万条数据。

mysql> select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (8.57 sec)mysql> select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (0.00 sec)

Copier après la connexion

可以看到，第一次执行8s,第二次执行相同的SQL语句，只需要0s
这样我们就验证了查询缓存确实开启并且生效了。
我们可以看到缓存状态，命中数有了1次，添加到缓存中的次数为1次（因为相同的SQL语句只在第1次查询的时候添加）
Explication avancée de loptimisation du cache de requêtes MySQL

5、查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项：
SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果。
SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

注意：当 query_cache_type 系统变量的值为ON时，即使不加SQL_CACHE，也是会缓存的，而query_cache_type的变量的值为DEMAND，只有显示的指定了SQL_CACHE,才会做缓存。

tb_item表的前两行信息如下

mysql> select * from tb_item limit 2;+----+------------+----------+-------+------------+--------+------------+---------------------+---------------------+| id | title      | price    | num   | categoryid | status | sellerid   | createtime          | updatetime          |+----+------------+----------+-------+------------+--------+------------+---------------------+---------------------+|  1 | 货物1号    | 33494.85 |   365 |          0 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 ||  2 | 货物2号    |  5617.72 | 24060 |          0 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 |+----+------------+----------+-------+------------+--------+------------+---------------------+---------------------+2 rows in set (0.04 sec)

Copier après la connexion

我们测试查询（注意，上一条语句已经被放到缓存中，相应的状态值会发生改变）

mysql> select title,sellerid from tb_item where id=1;+------------+------------+| title      | sellerid   |+------------+------------+| 货物1号    | 5435343235 |+------------+------------+1 row in set (0.00 sec)mysql> show status like 'Qcache%';+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        || Qcache_free_memory      | 16764840 || Qcache_hits             | 1        || Qcache_inserts          | 3        || Qcache_lowmem_prunes    | 0        || Qcache_not_cached       | 3        || Qcache_queries_in_cache | 3        || Qcache_total_blocks     | 8        |+-------------------------+----------+8 rows in set (0.00 sec)

Copier après la connexion

说明这一条语句也被加入到缓存中

当我不想要做缓存的时候，我们需要在select后面加上SELECT_NO_CACHE

mysql> select SQL_NO_CACHE title,sellerid from tb_item where id=2;+------------+------------+| title      | sellerid   |+------------+------------+| 货物2号    | 5435343235 |+------------+------------+1 row in set, 1 warning (0.00 sec)mysql> show status like 'Qcache%';+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        || Qcache_free_memory      | 16764840 || Qcache_hits             | 1        || Qcache_inserts          | 3        || Qcache_lowmem_prunes    | 0        || Qcache_not_cached       | 4        || Qcache_queries_in_cache | 3        || Qcache_total_blocks     | 8        |+-------------------------+----------+8 rows in set (0.03 sec)

Copier après la connexion

Qcache_inserts 的值仍然为3，说明没有缓存进去。

6、查询缓存失效的情况

我们前面已经提高过，当query_cache_type的值设置为1的时候，它会缓存符合条件的select语句的结果。原因是因为在某些情况下，查询缓存是会失效的。

1） SQL 语句不一致的情况，要想命中查询缓存，查询的SQL语句必须完全一致。

SQL1 : select count(*) from tb_item;SQL2 : Select count(*) from tb_item;

Copier après la connexion

仅大小写不同。

mysql> select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (0.00 sec)mysql> Select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (2.02 sec)

Copier après la connexion

2）当查询语句中有一些不确定的时，则不会缓存。如： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。这些函数每次获取的结果都不同。

SQL1 : select * from tb_item where updatetime <p><strong>3） 不使用任何表查询语句。</strong><br> 比如select一个常量select ‘hello’;</p><pre class="brush:php;toolbar:false">select 'A';

Copier après la connexion

4）查询 mysql， information_schema或 performance_schema 系统数据库中的表时，不会走查询缓存。
MySQL系统数据库包括mysql， information_schema或 performance_schema

select * from information_schema.engines;

Copier après la connexion

5） 在存储的函数，触发器或事件的主体内执行的查询。

6）如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

mysql> Select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (0.00 sec)mysql> update tb_item set title='test1' where id=5;Query OK, 1 row affected (0.05 sec)Rows matched: 1  Changed: 1  Warnings: 0

mysql> Select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (1.23 sec)mysql> Select count(*) from tb_item;+----------+| count(*) |+----------+|  2499695 |+----------+1 row in set (0.00 sec)

Copier après la connexion