mysql query cache

最新推荐文章于 2021-03-18 09:20:00 发布

最新推荐文章于 2021-03-18 09:20:00 发布 · 127 阅读

文章标签：

#Cache #MySQL

mysql 专栏收录该内容

2 篇文章

订阅专栏

本文探讨了MySQL中查询缓存在事务内的工作原理及其与空白和注释的关系。实验结果显示，一旦事务内修改了表，则直到该事务提交前，涉及该表的所有查询都无法使用查询缓存。此外，文章还讨论了空白字符和注释如何影响查询缓存的命中率。

1. works with Transactions

http://www.mysqlperformanceblog.com/2008/01/29/how-mysql-query-cache-works-with-transactions/ 写道

The result set can be retrieved from query cache (for statements both inside and outside of transactions) until there is a statement inside transactions which modifies the table. As soon as table is modified in transaction it becomes uncachable by query cache until that transaction is committed. Not only query cache can’t be used inside the same transaction which modified data but also in other concurrent transactions which do not even see the changes done yet.

With current approach Innodb can probably do something as simple as marking table “uncachable ” if it has any uncommitted changed which would take care about all complicated aspects of change visibility in different transaction modes.

transaction 1 :
start transaction;
select count(*) from parent;  //  result: 1   QCache_hits : 0
select count(*) from parent;  //  result: 1   QCache_hits : 1

transaction 2 :
start transaction;
select count(*) from parent;  //  result: 1   QCache_hits : 2
insert into parent(name) values('new');
select count(*) from parent;  //  result: 2   QCache_hits : 2

transaction 1 :
select count(*) from parent;  //  result: 1   QCache_hits : 2




transaction 2 :
commit;

transaction 1 :
select count(*) from parent;  //  result: 1   QCache_hits : 2

commit;
select count(*) from parent;  //  result: 2   QCache_hits : 2



select count(*) from parent;  //  result: 2   QCache_hits : 3

transaction 2 :
select count(*) from parent;  //  result: 2   QCache_hits : 4

the experiment result perfectly explains the mechanism of mysql cache query with transactions.

2. WhiteSpace and comments

http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-cache-whitespace-and-comments/ 写道

You can have whitespace in the start and you can have leading comment and it all works. However the comment which is inside the query works interesting way - the queries with different comments are both treated as same query if only comment is different. However if you change whitespace a bit (see the last query has space after comment deleted) it causes query cache miss.

Whitespace at the start of query does not block query from being cached . Moreover query with 2 spaces in front is considered same as query with 3 spaces in front

Comment at the start of the query does not block query from being cached. However queries with different comments are considered different queries (it is not stripped before hashing) - so you should not put things like current time in such a comment.

Comments inside the query also matter. Meaning if you place comments inside the query or in the end. Though this was always the case

select count(*) from parent;  //  QCache_hits : 0
select count(*) from parent;  //  QCache_hits : 1
/*comment*/ select count(*) from parent;  //  QCache_hits : 2             //  whitespace and comment at the start of query 
/*newcomment*/ select count(*) from parent;  //  QCache_hits : 3      //   does not block query from
/*comment*/select count(*) from parent;  //  QCache_hits : 4              //   being cached.

select /*comment*/ count(*) from parent;  //  QCache_hits : 4             // comments inside
select /*new comment*/ count(*) from parent;  //  QCache_hits : 5

select /*comment*/count(*) from parent;  //  QCache_hits : 5         // no whitespace between count(*) and comments.
select /*newcomment*/count(*) from parent;  //  QCache_hits : 6

select /*comment*/   count(*) from parent;  //  QCache_hits : 6      // one more whitespace before count(*)