5.1手册上写明,在where条件后,如果有now(),sysdate() 等函数时,mysql将无法使用cache。那么在oracle中使用的绑定变量在mysql会使用到cache吗? 以下是测试
测试版本
mysql 5.1.6 64位 redhat 5 64位虚拟机
从测试结果上来看,无法使用cache。
MySQL中,如果cache对你很重要就不要用绑定变量。
mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 31 | 0.00007200 | set @s:=3 |
| 32 | 0.01539800 | select count(*) from io_01 where c1 = @s |
| 33 | 0.01528700 | select count(*) from io_01 where c1 = @s |
| 34 | 0.01539700 | select count(*) from io_01 where c1 = @s |
| 35 | 0.01587500 | select count(*) from io_01 where c1 = @s |
| 36 | 0.01508000 | select count(*) from io_01 where c1 = @s |
| 37 | 0.01512300 | select count(*) from io_01 where c1 = @s |
| 38 | 0.01506700 | select count(*) from io_01 where c1 = 3 |
| 39 | 0.00022400 | select count(*) from io_01 where c1 = 3 |
| 40 | 0.00021400 | select count(*) from io_01 where c1 = 3 |
| 41 | 0.00004000 | select count(*) from io_01 where c1 = 3 |
| 42 | 0.00022300 | select count(*) from io_01 where c1 = 3 |
| 43 | 0.00023800 | select count(*) from io_01 where c1 = 3 |
| 44 | 0.00022500 | select count(*) from io_01 where c1 = 3 |
| 45 | 0.00004000 | select count(*) from io_01 where c1 = 3 |
+----------+------------+-------------------------------------------+
15 rows in set (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12807983/viewspace-714576/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12807983/viewspace-714576/
本文通过实验对比了MySQL中使用绑定变量与直接指定数值时查询性能的变化,发现使用绑定变量会导致查询缓存失效,而直接指定数值则能有效利用查询缓存。
4万+

被折叠的 条评论
为什么被折叠?



