与MySQL的注释、Query_cache有关的一个bug

今天有同学问了一个关于注释的问题，顺藤摸瓜发现一个bug，简要说明下。

有关注释的一些QA

Q： MySQL注释有哪些格式

A：MySQL的注释格式有三种，分别是 /**/ -- 和 #。具体参见手册

        

Q：在一些导出文件中见过/*! xxx*/，是不是注释？

A:  需要注意的是/*! */ 这种格式，对于MySQL来说不是注释，是能够直接执行的。同时这个格式还支持指定版本号，比如/*!50518 xxxx*/ 表示，若server端版本大于5518，则后续的xxxx作为语句的一部分执行，否则忽略。

 

Q：语句中的注释会不会发给server

A：默认是不会的。一种方法是在mysql连接中加-c (--comments)参数，则会强制将注释部分也发给Server端。 另一种方法与上一条答案有关，指定一个超过server端的版本号。如果比如指定 /*!121221 xxxx*/，也可以发给server。

 

Q: 慢查询日志会不会记录注释部分

A: 慢查询会记录语句原文，如果用上面说到的两种方法将注释发给server，若记录到慢查询日志中，就会显示。（也许可以用这种方法来加入语句来源，做业务慢查询分析）

   不只是慢查询，如果使用statement格式的binlog，注释部分也会写入到binlog中。

 

Q: 使用注释的语句能不能使用Query_cache(QC)

A：可以的，QC 会用原文作key，因此若有注释，则注释部分也要求一模一样才会命中QC

 

Query_cache小背景

QC是一个hash表，key是语句原文，value是查询结果。因此要求一模一样的语句才会命中。

 

Bug描述

若你的语句中是这么写select * from t /*!121221 just test*/. 按照上面说到的，这个语句是会被全文发到server端，不用怕语法错误，因为121221这个版本号大于现有的所有可用版本。因此实际执行的是select * from t;

 

如果query_cache正常打开，会看到第一次执行后show status like 'qcache_inserts';值确实+1.

然后再次执行，发现Qcache_hits并没有+1，反而是Qcache_not_cached +1.

说明这个语句虽然被插入QC中，但是却用不上

 

Bug分析

如果这个查询是慢查询，我们可以在慢查询日志中看到，它记录的是 select * from t /* 121221 just test*/。 ‘!’被换成了空格。

这个替换是在解析阶段发生的。

这样就有问题了：

1、  判断QC是否命中是在解析之前，也就是SQL的原文

2、  第一次执行不命中QC，需要执行，执行期间将!替换成空格

3、  结果在插入QC中，但是此时的key，已经是替换过的。

4、  下一次执行使用的仍然是带！的，因此仍然无法命中

 

    一种解决方法

Percona和MariaDB(5.5+)中有一个特性，刚好绕过这个问题。

参数query_cache_strip_comments用于控制QC中是否去掉SQL语句的注释部分。默认是OFF，若打开，插入QC中的是原语句，可以解决这个问题。

 

'!'为什么要替换

这个问题的是由解析器将/*! */替换成/*  */ 造成的。在5.1其实没有这个替换行为(感谢@飞哥最爱白菜 指出)。为什么MySQL要加上这个动作呢？

原因是我们上面提到的，binlog中会记录语句原文，包括/*!VERSION xxx*/的内容。

这样在主从同步时，若VERSION大于主库版本而小于从库版本（官方是承诺向下兼容的），则可能会出现主从执行不一致的问题。

主库发现xxx不会被执行，就做了替换，语句变成 /* VERSION xxx*/，这是个标准的注释，从库也不会执行xxx部分。

因此属于解决bug的时候引入的。