MySQL查詢緩存優(yōu)化的示例分析

小編給大家分享一下MySQL查詢緩存優(yōu)化的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

MySQL查詢緩存優(yōu)化

• 1 概述

• 2 操作流程

• 3 查詢緩存配置

• 4 開啟查詢緩存

• 5 查詢緩存SELECT選項

• 6 查詢緩存失效的情況

1、概述

開啟Mysql的查詢緩存，當(dāng)執(zhí)行完全相同的SQL語句的時候，服務(wù)器就會直接從緩存中讀取結(jié)果，當(dāng)數(shù)據(jù)被修改，之前的緩存會失效，修改比較頻繁的表不適合做查詢緩存。

2、操作流程

1. 客戶端發(fā)送一條查詢給服務(wù)器；
2. 服務(wù)器先會檢查查詢緩存，如果命中了緩存，則立即返回存儲在緩存中的結(jié)果。否則進(jìn)入下一階段；
3. 服務(wù)器端進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃；
4. MySQL根據(jù)優(yōu)化器生成的執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢；
5. 將結(jié)果返回給客戶端。

3、查詢緩存配置

1. 查看當(dāng)前的MySQL數(shù)據(jù)庫是否支持查詢緩存：

 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)

代表當(dāng)前數(shù)據(jù)庫支持查詢緩存

2. 查看當(dāng)前MySQL是否開啟了查詢緩存 ：

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

代表當(dāng)前沒有開啟查詢緩存

3. 查看查詢緩存的占用大小 ：

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

代表當(dāng)前查詢緩存占用16777216字節(jié)，大概占用1.5MB，如果緩存過小，可以改變query_cache_size的值來增加查詢緩存的大小。

4. 查看查詢緩存的狀態(tài)變量：

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        |+-------------------------+----------+

各個變量的含義如下：

4、開啟查詢緩存

MySQL的查詢緩存默認(rèn)是關(guān)閉的，需要手動配置參數(shù) query_cache_type ， 來開啟查詢緩存。query_cache_type 該參數(shù)的可取值有三個 ：

如何設(shè)置query_cache_type的值呢，這里我們需要修改MySQL的配置文件
博主的Ubuntu（Linux操作系統(tǒng)）版本為16.04，mysql版本為5.7。需要進(jìn)入到/etc/mysql/mysql.conf.d下修改配置文件mysqld.cnf

添加以下內(nèi)容

然后需要重啟MySQL服務(wù)

再登錄MySQL

此時可再查詢MySQL查詢緩存是否開啟

配置完畢之后，重啟服務(wù)既可生效 ；

然后就可以在命令行執(zhí)行SQL語句進(jìn)行驗(yàn)證 ，執(zhí)行一條比較耗時的SQL語句，然后再多執(zhí)行幾次，查看后面幾次的執(zhí)行時間；獲取通過查看查詢緩存的緩存命中數(shù)，來判定是否走查詢緩存。

我們可以進(jìn)行測試，我們曾經(jīng)建了一張表tb_item,里面有250萬條數(shù)據(jù)。

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)

可以看到，第一次執(zhí)行8s,第二次執(zhí)行相同的SQL語句，只需要0s
這樣我們就驗(yàn)證了查詢緩存確實(shí)開啟并且生效了。
我們可以看到緩存狀態(tài)，命中數(shù)有了1次，添加到緩存中的次數(shù)為1次（因?yàn)橄嗤腟QL語句只在第1次查詢的時候添加）

5、查詢緩存SELECT選項

可以在SELECT語句中指定兩個與查詢緩存相關(guān)的選項 ：
SQL_CACHE : 如果查詢結(jié)果是可緩存的，并且 query_cache_type 系統(tǒng)變量的值為ON或 DEMAND ，則緩存查詢結(jié)果 。
SQL_NO_CACHE : 服務(wù)器不使用查詢緩存。它既不檢查查詢緩存，也不檢查結(jié)果是否已緩存，也不緩存查詢結(jié)果。

注意：當(dāng) query_cache_type 系統(tǒng)變量的值為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)

我們測試查詢（注意，上一條語句已經(jīng)被放到緩存中，相應(yīng)的狀態(tài)值會發(fā)生改變）

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)

說明這一條語句也被加入到緩存中

當(dāng)我不想要做緩存的時候，我們需要在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，說明沒有緩存進(jìn)去。

6、查詢緩存失效的情況

我們前面已經(jīng)提高過，當(dāng)query_cache_type的值設(shè)置為1的時候，它會緩存符合條件的select語句的結(jié)果。原因是因?yàn)樵谀承┣闆r下，查詢緩存是會失效的。

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） 當(dāng)查詢語句中有一些不確定的時，則不會緩存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。這些函數(shù)每次獲取的結(jié)果都不同。

SQL1 : select * from tb_item where updatetime < now() limit 1;SQL2 : select user();SQL3 : select database();

3） 不使用任何表查詢語句。
比如select一個常量select ‘hello’;

select 'A';

4） 查詢 mysql， information_schema或 performance_schema 系統(tǒng)數(shù)據(jù)庫中的表時，不會走查詢緩存。
MySQL系統(tǒng)數(shù)據(jù)庫包括mysql， information_schema或 performance_schema

select * from information_schema.engines;

5） 在存儲的函數(shù)，觸發(fā)器或事件的主體內(nèi)執(zhí)行的查詢。

6） 如果表更改，則使用該表的所有高速緩存查詢都將變?yōu)闊o效并從高速緩存中刪除。這包括使用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查詢緩存優(yōu)化的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！