以前看别人的文章也没有太理解,这两天测试有个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/