唐福林 博客雨

本文按署名·非商业用途·保持一致授权
作者: surfchen@gmail.com 

原理

QueryCache(下面简称QC)是根据SQL语句来cache的。一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用 QC。每个Cache都是以SQL文本作为key来存的。在应用QC之前，SQL文本不会被作任何处理。也就是说，两个SQL语句，只要相差哪怕是一个字 符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。不过SQL文本有可能会被客户端做一些处理。例如在官方的命令行客户端里，在发送SQL给服务器之前，会做如下处理：

• 过滤所有注释
• 去掉SQL文本前后的空格,TAB等字符。注意，是文本前面和后面的。中间的不会被去掉。

下面的三条SQL里，因为SELECT大小写的关系，最后一条和其他两条在QC里肯定是用的不一样的存储位置。而第一条和第二条，区别在于后者有个 注释，在不同客户端，会有不一样的结果。所以，保险起见，请尽量不要使用动态的注释。在PHP的mysql扩展里，SQL的注释是不会被去掉的。也就是三 条SQL会被存储在三个不同的缓存里，虽然它们的结果都是一样的。

select * FROM people where name='surfchen';
select * FROM people where /*hey~*/name='surfchen';
SELECT * FROM people where name='surfchen';

目前只有select语句会被cache，其他类似show,use的语句则不会被cache。

因为QC是如此前端，如此简单的一个缓存系统，所以如果一个表被更新，那么和这个表相关的SQL的所有QC都会被失效。假设一个联合查询里涉及到了表A和表B，如果表A或者表B的其中一个被更新（update或者delete），这个查询的QC将会失效。

也就是说，如果一个表被频繁更新，那么就要考虑清楚究竟是否应该对相关的一些SQL进行QC了。一个被频繁更新的表如果被应用了QC，可能会加重数 据库的负担，而不是减轻负担。我一般的做法是默认打开QC，而对一些涉及频繁更新的表的SQL语句加上SQL_NO_CACHE关键词来对其禁用 CACHE。这样可以尽可能避免不必要的内存操作，尽可能保持内存的连续性。

那些查询很分散的SQL语句，也不应该使用QC。例如用来查询用户和密码的语句——“select pass from user where name=’surfchen’”。这样的语句，在一个系统里，很有可能只在一个用户登陆的时候被使用。每个用户的登陆所用到的查询，都是不一样的SQL 文本，QC在这里就几乎不起作用了，因为缓存的数据几乎是不会被用到的，它们只会在内存里占地方。

存储块

在本节里“存储块”和“block”是同一个意思QC缓存一个查询结果的时 候，一般情况下不是一次性地分配足够多的内存来缓存结果的。而是在查询结果获得的过程中，逐块存储。当一个存储块被填满之后，一个新的存储块将会被创建， 并分配内存（allocate）。单个存储块的内存分配大小通过query_cache_min_res_unit参数控制，默认为4KB。最后一个存储 块，如果不能被全部利用，那么没使用的内存将会被释放。如果被缓存的结果很大，那么会可能会导致分配内存操作太频繁，系统系能也随之下降；而如果被缓存的 结果都很小，那么可能会导致内存碎片过多，这些碎片如果太小，就很有可能不能再被分配使用。

除了查询结果需要存储块之外，每个SQL文本也需要一个存储块，而涉及到的表也需要一个存储块（表的存储块是所有线程共享的，每个表只需要一个存储 块）。存储块总数量=查询结果数量*2+涉及的数据库表数量。也就是说，第一个缓存生成的时候，至少需要三个存储块：表信息存储块，SQL文本存储块，查 询结果存储块。而第二个查询如果用的是同一个表，那么最少只需要两个存储块：SQL文本存储块，查询结果存储块。

通过观察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每个缓存结果占用的存储块。它们的 比例如果接近1:2，则说明当前的query_cache_min_res_unit参数已经足够大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多，则需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit（sql文本和表信息所在的block占用的 内存很小，可以忽略）如果远远大于query_cache_size-Qcache_free_memory，那么可以尝试减小 query_cache_min_res_unit的值。

调整大小

如果Qcache_lowmem_prunes增长迅速，意味着很多缓存因为内存不够而被释放，而不是因为相关表被更新。尝试加大query_cache_size，尽量使Qcache_lowmem_prunes零增长。

启动参数

show variables like ‘query_cache%’可以看到这些信息。

query_cache_limit:如果单个查询结果大于这个值，则不Cache

query_cache_size: 分配给QC的内存。如果设为0，则相当于禁用QC。要注意QC必须使用大约40KB来存储它的结构，如果设定小于40KB,则相当于禁用QC。QC存储的 最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。

query_cache_type:0 完全禁止QC，不受SQL语句控制（另外可能要注意的是，即使这里禁用，上面一个参数所设定的内存大小还是会被分配）；1启用QC，可以在SQL语句使用 SQL_NO_CACHE禁用；2可以在SQL语句使用SQL_CACHE启用。

query_cache_min_res_unit:每次给QC结果分配内存的大小

状态

show status like ‘Qcache%’可以看到这些信息。

Qcache_free_blocks: 当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH QUERY CACHE语句来清空free blocks。

Qcache_free_memory:可用内存，如果很小，考虑增加query_cache_size

Qcache_hits:自mysql进程启动起，cache的命中数量

Qcache_inserts:自mysql进程启动起，被增加进QC的数量

Qcache_lowmem_prunes:由于内存过少而导致QC被删除的条数。加大query_cache_size，尽可能保持这个值0增长。

Qcache_not_cached:自mysql进程启动起，没有被cache的只读查询数量（包括select,show,use,desc等）

Qcache_queries_in_cache:当前被cache的SQL数量

Qcache_total_blocks: 在QC中的blocks数。一个query可能被多个blocks存储，而这几个blocks中的最后一个，未用满的内存将会被释放掉。例如一个QC结果 要占6KB内存，如果query_cache_min_res_unit是4KB，则最后将会生成3个blocks，第一个block用来存储sql语句 文本，这个不会被统计到query+cache_size里，第二个block为4KB，第三个block为2KB（先allocate4KB，然后释放 多余的2KB）。每个表，当第一个和它有关的SQL查询被CACHE的时候，会使用一个block来存储表信息。也就是说，block会被用在三处地方： 表信息，SQL文本，查询结果。

一．             控制流程函数

1．CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END

该语句用于在不同情况时，执行不同的语句，实例如下：

Eg1. SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;

输出：true

Eg2. SELECT CASE BINARY ‘B’ WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;

输出：NULL

2．IF(expr1,expr2,expr3)

如果expr1返回true，则该表达式返回expr2，否则返回expr3。

Eg1. SELECT IF(1<2,’yes ‘,’no’);

输出：‘yes ‘

Eg2. SELECT IF(STRCMP(‘test’,'test1′),’no’,'yes’);

上例中STRCMP(expr1, expr2)这个函数在expr1小于expr2时，返回-1，相等时返回0，其余的情况返回1，在上例中返回-1，因此上例的输出为：‘yes’

3．IFNULL(expr1,expr2)

该函数在expr1为NULL时，返回expr2，否则，返回expr1。

Eg1. SELECT IFNULL(1,0);

输出：1

Eg2. SELECT IFNULL(NULL,10);

输出：10

4．NULLIF(expr1,expr2)

如果expr1=expr2，则返回NULL，否则，返回expr1。

Eg1. SELECT NULLIF(1,1);

输出：NULL

Eg2. SELECT NULLIF(3,4);

输出：3

二．             字符串函数

1.       ASCII(str)

返回值为字符串str 的最左字符的数值。假如str为空字符串，则返回值为 0 。假如str 为NULL，则返回值为 NULL。 ASCII()用于带有从 0到255的数值的字符。

Eg1. SELECT ASCII(’2′);

输出：50

Eg2. SELECT ASCII(‘de’);

输出：100

2.       CONCAT(str1,str2,…) 

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。

Eg1. SELECT CONCAT(‘Hello’, ‘,’, ‘阿蜜果‘);

输出：‘Hello, 阿蜜果‘

Eg2. SELECT CONCAT(‘Hello’, NULL, ‘阿蜜果‘);

输出：NULL

3.       CONCAT_WS(separator,str1,str2,…)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。   第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的NULL值。

Eg1. SELECT CONCAT_WS(‘,’, ‘Hello’, ‘阿蜜果‘);

输出：‘Hello, 阿蜜果‘

Eg. SELECT CONCAT_WS(‘,’, ‘Hello’, NULL, ‘阿蜜果‘);

输出：‘Hello, 阿蜜果‘

4.       INSERT(str,pos,len,newstr)

返回字符串 str, 其子字符串起始于 pos位置和长期被字符串 newstr取代的len字符。 如果pos超过字符串长度，则返回值为原始字符串。 假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

Eg1. SELECT INSERT(‘hello,Amigo’, 7, 5, ‘Locus’);

输出：hello,Locus

5.       INTER(str, substr)

返回字符串 str中子字符串的第一个出现位置。

Eg1. SELECT INSTR(‘hello,Amigo’, ‘Amigo’);

输出：7

6.       LENGTH(str)

返回值为字符串str的长度，单位为字节。一个多字节字符算作多字节。

Eg1. SELECT LENGTH(‘Amigo’);

输出：5

7.       LOWER(str)

返回字符串 str以及所有根据最新的字符集映射表变为小写字母的字符。

Eg1. SELECT LOWER(‘AMIGO’);

UPPER(str)函数的用法与此类似，在此不再赘述。不过它是将字符串转换为大写的。

输出：amigo

8.       LPAD(str, len, padstr)

返回字符串 str, 其左边由字符串padstr 填补到len字符长度。假如str 的长度大于len, 则返回值被缩短至len字符。

Eg1. SELECT LPAD(‘hi’,4,’!!’);

输出：‘!!hi’

Eg2. SELECT LPAD(‘hi’,1,’!!’);

输出：‘h’

RPAD(…)函数用法与此类似，在此不再赘述。

9.       REPEAT(str,count)

返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count。 若 count <= 0,则返回一个空字符串。若str或count 为 NULL，则返回NULL。

Eg1. SELECT REPEAT(‘Amigo’, 2);

输出：‘AmigoAmigo’

10.   SUBSTRING(str,pos), SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

       不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

Eg1. SELECT SUBSTRING(‘hello,Amigo’,6);

输出：Amigo

Eg2. SELECT SUBSTRING(‘hello,Amigo’, 6, 4);

输出：Amig

三．             数值函数

1.       ABS(X)

返回X的绝对值。

Eg1. SELECT ABS(-5);

输出：5

2.       FLOOR(X)

返回不大于X的最大整数值 。

Eg1. SELECT FLOOR(2.56);

输出：2

Eg2. SELECT FLOOR(-2.56)

输出：-3

3.       MOD(N,M) , N % M N MOD M

模操作。返回N 被 M除后的余数。

Eg1. SELECT MOD(237, 10);

输出：7

Eg2. SELECT 237 % 10;

输出：7

Eg3. SELECT 237 MOD 10;

输出：7

4.       POW(X, Y)，POWER(X, Y)

返回X的Y乘方的结果值。

Eg1. SELECT POW(2, 3);

输出：8

Eg2. SELECT POWER(2, 4)

输出：16

5.       RAND()，RAND(N)

返回一个随机浮点值 v，范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N，则它被用作种子值，用来产生重复序列。

Eg1.  SELECT RAND(20);

输出：0.15888261251047

Eg2. SELECT RAND();

输出：0.9233482386203

若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如， 若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：

SELECT FLOOR(7 + (RAND() * 6));

6.       ROUND(X)，ROUND(X,D)

返回参数X, 其值接近于最近似的整数。在有两个参数的情况下，返回 X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位，可将 D 设为负值。

Eg1. SELECT ROUND(-1.23);

输出：-1

Eg2. SELECT ROUND(2.56);

输出：3

7.       SQRT(X)

      返回非负数X的二次方根，如X为NULL时，返回NULL。

Eg1. SELECT SQRT(4);

输出：2

Eg2. SELECT SQRT(-4);

输出：NULL

四．             日期和时间函数

1. ADDDATE(date,INTERVAL expr type)，ADDDATE(expr,days)

       当被第二个参数的INTERVAL格式激活后， ADDDATE()就是DATE_ADD()的同义词。相关函数SUBDATE()则是DATE_SUB()的同义词。

Eg1. SELECT DATE_ADD(’1998-01-02′, INTERVAL 31 DAY);

输出：’1998-02-02′

Eg2. SELECT ADDDATE(’1998-01-02′, INTERVAL 31 DAY);

输出：’1998-02-02′

2. CURDATE()

将当前日期按照‘YYYY-MM-DD’ 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。

Eg1. SELECT CURDATE();

输出：’2008-01-01′

Eg2. SELECT CURDATE() + 0;

输出：20080101

3. CURTIME()

将当前时间以‘HH:MM:SS’或 HHMMSS 的格式返回， 具体格式根据函数用在字符串或是数字语境中而定。

Eg1. SELECT CURTIME();

输出：’15:15:26′

4. DATE(expr)

提取日期或时间日期表达式expr中的日期部分。

Eg1. SELECT DATE(’2007-12-31 23:59:59′);

输出：’2007-12-31′

5. DATEDIFF(expr, expr2)

DATEDIFF() 返回起始时间 expr和结束时间expr2之间的天数。Expr和expr2为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。

Eg1. SELECT DATEDIFF(’1997-12-31 23:59:59′,’1997-12-30′);

输出：1

Eg2. SELECT DATEDIFF(’1997-11-30 23:59:59′,’1997-12-31′);

输出：-31

6. DAYOFMONTH(date)

返回date 对应的该月日期，范围是从 1到31。

Eg1. SELECT DAYOFMONTH(’2008-01-05′);

输出：5

7. MONTH (time)

返回date对应的月份，范围时从 1 到 12。

Eg1. SELECT MONTH(’2007-01-05′);

输出：1

8. HOUR(time)

       返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23 。

Eg1. SELECT HOUR(’11:47:1′);

9. MINUTE(time)

返回 time 对应的分钟数,范围是从 0 到 59。

Eg1. SELECT MINUTE(’2007-01-05 11:51:23);

输出：51

10. SECOND(time)

返回time对应的秒数, 范围是从 0到59。

Eg1. SELECT SECOND(’11:56:43);

输出：43

11. LAST_DAY(date)

获取一个日期或日期时间值，返回该月最后一天对应的值。若参数无效，则返回NULL。

Eg1. SELECT LAST_DAY(’2008-02-01′);

输出：29

12. NOW()

返回当前日期和时间值，其格式为 ‘YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS ， 具体格式取决于该函数是否用在字符串中或数字语境中。

Eg1. SELECT NOW();

输出：’2007-01-05 11:54:30′

13. SUBDATE(date,INTERVAL expr type) ，SUBDATE(expr,days)

       当被第二个参数的 INTERVAL型式调用时, SUBDATE()和DATE_SUB()的意义相同。对于有关INTERVAL参数的信息， 见有关 DATE_ADD()的讨论。

Eg1. SELECT SUBDATE(’2008-01-05′, INTERVAL 31 DAY);

输出：2007-12-05

14. SYSDATE()

返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS， 具体格式根据函数是否用在字符串或数字语境而定。

在一个存储程序或触发器中, SYSDATE()返回其执行的时间, 而非存储成都或触发语句开始执行的时间。这个NOW()的运作有所不同。

Eg1. SELECT SYSDATE();

输出：2008-01-05 12:01:41