MySQL查詢緩存優化的示例分析

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

MySQL查詢緩存優化

• 1 概述

• 2 操作流程

• 3 查詢緩存配置

• 4 開啟查詢緩存

• 5 查詢緩存SELECT選項

• 6 查詢緩存失效的情況

1、概述

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

2、操作流程

1. 客戶端發送一條查詢給服務器；
2. 服務器先會檢查查詢緩存，如果命中了緩存，則立即返回存儲在緩存中的結果。否則進入下一階段；
3. 服務器端進行SQL解析、預處理，再由優化器生成對應的執行計劃；
4. MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢；
5. 將結果返回給客戶端。

3、查詢緩存配置

1. 查看當前的MySQL數據庫是否支持查詢緩存：

 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)

代表當前數據庫支持查詢緩存

2. 查看當前MySQL是否開啟了查詢緩存 ：

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

代表當前沒有開啟查詢緩存

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

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

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

4. 查看查詢緩存的狀態變量：

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的查詢緩存默認是關閉的，需要手動配置參數 query_cache_type ， 來開啟查詢緩存。query_cache_type 該參數的可取值有三個 ：

如何設置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 < now() limit 1;SQL2 : select user();SQL3 : select database();

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

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查詢緩存優化的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！