以前看別人的文章也沒有太理解，這兩天測試有個update速度問題，以為是query_cache導致，然後仔細測試了一下才明白其中的道理。

mysql的query_cache是緩衝的是select語句執行計劃及其執行結果的(開頭我還以為僅僅是緩衝執行計劃，受對oracle的理解影響)

而update語句會將query_cache裡邊相關被update表的東西(計劃與資料)清空。

下邊兩個老大算是研究比較清楚，不過只有自己仔細實驗了似乎才理解了

另外Qcache_not_cached是可以記錄DML語句的數量的

順便還看了一下怎麼去檢視select,update,insert語句的計數器，

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33543040 |
| Qcache_hits | 7 |
| Qcache_inserts | 5 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 72 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> update a set a = a+1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33545600 |
| Qcache_hits | 7 |
| Qcache_inserts | 5 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 73 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33545600 |
| Qcache_hits | 7 |
| Qcache_inserts | 5 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 76 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select a from a;
+------+
| a |
+------+
| 4 |
| 5 |
+------+
2 rows in set (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544064 |
| Qcache_hits | 7 |
| Qcache_inserts | 6 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 77 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> select a from a;
+------+
| a |
+------+
| 4 |
| 5 |
+------+
2 rows in set (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544064 |
| Qcache_hits | 8 |
| Qcache_inserts | 6 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 79 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> select a,b from a;
+------+------+
| a | b |
+------+------+
| 4 | 2 |
| 5 | 3 |
+------+------+
2 rows in set (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33543040 |
| Qcache_hits | 8 |
| Qcache_inserts | 7 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 80 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> create table b (a int,b int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table b (a int,b int);
ERROR 1050 (42S01): Table 'b' already exists
mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33543040 |
| Qcache_hits | 8 |
| Qcache_inserts | 7 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 81 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> insert into b values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33543040 |
| Qcache_hits | 8 |
| Qcache_inserts | 7 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 82 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> insert into b values (2,3);
Query OK, 1 row affected (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33543040 |
| Qcache_hits | 8 |
| Qcache_inserts | 7 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 83 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select a from b;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33541504 |
| Qcache_hits | 8 |
| Qcache_inserts | 8 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 84 |
| Qcache_queries_in_cache | 3 |
| Qcache_total_blocks | 9 |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> select a from b;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

mysql> update a set a = a+1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> show status like '%Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 33544064 |
| Qcache_hits | 9 |
| Qcache_inserts | 8 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 88 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+----------+
8 rows in set (0.01 sec)

select,update,insert語句的計數器

mysql> show status like '%select%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_insert_select | 0 |
| Com_replace_select | 0 |
| Com_select | 10 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 99 |
+------------------------+-------+
8 rows in set (0.01 sec)

mysql> update a set a = a+2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> insert into a values(100,101);
Query OK, 1 row affected (0.00 sec)

關於mysql的query_cache

相關文章