关于mysql的query_cache

以前看别人的文章也没有太理解，这两天测试有个update速度问题，以为是query_cache导致，然后仔细测试了一下才明白其中的道理。

mysql的query_cache是缓冲的是select语句执行计划及其执行结果的(开头我还以为仅仅是缓冲执行计划，受对oracle的理解影响)

而update语句会将query_cache里边相关被update表的东西(计划与数据)清空。

下边两个老大算是研究比较清楚，不过只有自己仔细实验了似乎才理解了

http://www.anysql.net/mysql/mysql_bind_query_cache.html

http://www.freelamp.com/1103001969/index_html

另外Qcache_not_cached是可以记录DML语句的数量的

顺便还看了一下怎么去查看select,update,insert语句的计数器，

mysql> show status like '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 7 |
+---------------+-------+
1 row in set (0.01 sec)

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 '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 7 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 7 |
+---------------+-------+
1 row 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 '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 8 |
+---------------+-------+
1 row 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> show status like '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 8 |
+---------------+-------+
1 row in set (0.00 sec)

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

mysql> show status like '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 9 |
+---------------+-------+
1 row 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 '%hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 9 |
+---------------+-------+
1 row in set (0.01 sec)

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> show status like '%update%';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| Com_update | 4 |
| Com_update_multi | 0 |
| Handler_update | 0 |
| Innodb_rows_updated | 6930012 |
+---------------------+---------+
4 rows in set (0.00 sec)

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

mysql> show status like '%update%';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| Com_update | 5 |
| Com_update_multi | 0 |
| Handler_update | 0 |
| Innodb_rows_updated | 6930014 |
+---------------------+---------+
4 rows in set (0.01 sec)

mysql> show status like '%insert%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| Com_insert | 4 |
| Com_insert_select | 0 |
| Delayed_insert_threads | 0 |
| Innodb_rows_inserted | 3191778 |
| Qcache_inserts | 9 |
+------------------------+---------+
5 rows in set (0.01 sec)

mysql> insert into a (100,101);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '100,101)' at line 1
mysql> show status like '%insert%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| Com_insert | 4 |
| Com_insert_select | 0 |
| Delayed_insert_threads | 0 |
| Innodb_rows_inserted | 3191778 |
| Qcache_inserts | 9 |
+------------------------+---------+
5 rows in set (0.00 sec)

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

mysql> show status like '%insert%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| Com_insert | 5 |
| Com_insert_select | 0 |
| Delayed_insert_threads | 0 |
| Innodb_rows_inserted | 3191779 |
| Qcache_inserts | 9 |
+------------------------+---------+
5 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82392/viewspace-144699/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82392/viewspace-144699/