Erweiterte Erläuterung der MySQL-Abfrage-Cache-Optimierung

MySQL-Abfrage-Cache-Optimierung

• 1 Übersicht
• 2 Betriebsablauf
• 3 Abfrage-Cache-Konfiguration
• 4 Abfrage-Cache aktivieren
• 5 Abfrage-Cache-SELECT-Option
• 6 Abfrage-Cache-Fehler

(Kostenlose Lernempfehlung: MySQL-Video-Tutorial)

1. Aktivieren Sie den MySQL-Abfrage-Cache, wenn genau dieselbe SQL-Anweisung ausgeführt wird Beim Lesen der Ergebnisse wird der vorherige Cache ungültig. Tabellen, die häufig geändert werden, sind nicht für das Abfrage-Caching geeignet.

2. Operationsprozess

1. Der Server überprüft zunächst den Abfragecache und gibt die gespeicherten Ergebnisse sofort zurück im Cache. Andernfalls gehen Sie zur nächsten Stufe über.

3. Der Server führt eine SQL-Analyse und Vorverarbeitung durch, und dann generiert der Optimierer den entsprechenden Ausführungsplan. 4. MySQL ruft die API der Speicher-Engine auf, um die Abfrage basierend auf dem von generierten Ausführungsplan auszuführen der Optimierer;
5. Geben Sie die Ergebnisse an den Client zurück.


3. Abfrage-Cache-Konfiguration

Überprüfen Sie, ob die aktuelle MySQL-Datenbank Abfrage-Caching unterstützt:

 SHOW VARIABLES LIKE 'have_query_cache';

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

bedeutet, dass die aktuelle Datenbank Abfrage-Caching unterstützt:

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

bedeutet, dass der Abfragecache derzeit nicht aktiviert ist. Überprüfen Sie die belegte Größe des Abfragecaches:

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

bedeutet, dass der aktuelle Abfragecache 16777216 Bytes, etwa 1,5 MB, belegt. Wenn der Cache zu klein ist, können Sie den Wert ändern von query_cache_size, um die Größe des Abfragecaches zu erhöhen.

2. Sehen Sie sich die Statusvariablen des Abfragecaches an:

mysql> SHOW STATUS LIKE 'Qcache%';+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        || Qcache_free_memory      | 16768680 || Qcache_hits             | 0        || Qcache_inserts          | 0        || Qcache_lowmem_prunes    | 0        || Qcache_not_cached       | 29       || Qcache_queries_in_cache | 0        || Qcache_total_blocks     | 1        |+-------------------------+----------+

Die Bedeutung jeder Variablen ist wie folgt:

Parameter

Bedeutung

Qcache_free_blocksDie Anzahl der verfügbaren Speicherblöcke in der Abfrage-Cache Qcache_free_memoryMenge an freiem Speicher für den Abfrage-CacheQcache_hitsAnzahl der Abfrage-Cache-TrefferQcache_insertsAnzahl der Abfragen, die dem Abfrage-Cache hinzugefügt wurden Qcache_lowmen_prunes Aus dem Abfrage-Cache entfernt, da nicht genügend Arbeitsspeicher vorhanden ist. Anzahl der Abfragen Qcache_not_cachedAnzahl der nicht zwischenspeicherbaren Abfragen (können aufgrund der Einstellung „query_cache_type“ nicht zwischengespeichert werden) Qcache_queries_in_cacheAnzahl der im Abfrage-Cache registrierten Abfragen Qcache_total _blocks Abfragecache Die Gesamtzahl der Blöcke 4. Aktivieren Sie den Abfragecache Der Abfragecache von MySQL ist standardmäßig deaktiviert. Sie müssen den Parameter query_cache_type manuell konfigurieren Aktivieren Sie den Abfragecache. query_cache_type Es gibt drei mögliche Werte für diesen Parameter:

Wert Bedeutung

OFF oder 0Abfrage-Cache-Funktion ist ausgeschaltet ON oder 1Abfrage-Cache Funktion ist aktiviert. Wenn das Ergebnis die Caching-Bedingungen erfüllt, wird es nicht zwischengespeichert. Wenn SQL_NO_CACHE explizit angegeben ist, wird es nicht zwischengespeichert Die Caching-Funktion wird bei Bedarf ausgeführt. Nur SELECT-Anweisungen, die explizit SQL_CACHE angeben, werden zwischengespeichert

如何设置query_cache_type的值呢，这里我们需要修改MySQL的配置文件
博主的Ubuntu（Linux操作系统）版本为16.04，mysql版本为5.7。需要进入到/etc/mysql/mysql.conf.d下修改配置文件mysqld.cnf

添加以下内容

然后需要重启MySQL服务

再登录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)

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

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)

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

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)

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

当我不想要做缓存的时候，我们需要在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)

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

6、查询缓存失效的情况

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

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

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

仅大小写不同。

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)

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';

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

select * from information_schema.engines;

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)

相关免费学习推荐：mysql数据库(视频)

Das obige ist der detaillierte Inhalt vonErweiterte Erläuterung der MySQL-Abfrage-Cache-Optimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!