律师做网站,福州软件网站开发培训班,wordpress定时函数,软件开发者选项一、mysql一个小问题引发的思考-mysql类型转换-查询缓存 最近在做的一个项目中有一个SQL语句发现点问题#xff0c;大概如下#xff1a; select * from table where cid0 or find_in_set(1, cid); 数据表中的字段cid是字符串类型#xff0c;原来的后端同学未提过此字段还能是…一、mysql一个小问题引发的思考-mysql类型转换-查询缓存 最近在做的一个项目中有一个SQL语句发现点问题大概如下 select * from table where cid0 or find_in_set(1, cid); 数据表中的字段cid是字符串类型原来的后端同学未提过此字段还能是空值(原认为只有0或者ID值的组合以逗号分开)测试期间也未有空值上线后发现这个字段还能是空值导致出现一些问题即数据库中字段为空的内容也被查询出来 原因和解决此字段是字符串类型不能使用数字查询。所以改起来到是很简单查询条件里的cid0加上引号即可解决。 但深入一下使用上面不带引号的SQL查询到底发生了什么呢实际上发生的东西和PHP也有点类似就是mysql类型转换typecasting/conversion不带引号查询时MYSQL会将表中的值转为数字后再进行比对。因此数据表中的空值字符串(当然这里不会出现)0三种结果都会出来。如下图测试示例 问题就到这里就结束了吗没有我在将两条SQL进行对比的时候发现差异还不只是这点。对比的两条SQL和查询用时如下
[SQL]
select * from has where ids2;
受影响的行: 0
时间: 0.173s
[SQL]
select * from has where ids2;
受影响的行: 0
时间: 0.165s 单从这一次执行来看并未有很大的差别我重复这样操作了多遍有时两个SQL的用时消耗是一样的即在类型转换上的开销并不大。当然也不能忽略高并发时这点性能提升也很关键。总之通过这个对比可以发现增加类型转换是会增加系统开销的。接下来执行完第一次后再执行两句SQL这时的用时如下:
[SQL]
select * from has where ids2;
受影响的行: 0
时间: 0.157s
[SQL]
select * from has where ids2;
受影响的行: 0
时间: 0.001s 此时会发现和上面的结果大相径庭加了引号的SQL执行用时快了很多基本是不消耗时间。为什么后面的这句SQL能这么快呢原因也很简单就是使用上了SQL的查询缓存而前面这句未使用上查询缓存如果我们把上面的SQL语句改成下面两句就会发现它们的性能相差较小(带上引号执行会更快)
select SQL_NO_CACHE * from has where ids2;
select SQL_NO_CACHE * from has where ids2; 所以从上面可以看出如果条件不带上引号性能损失远不止一点点因为不带引号每次都是重新查库不能使用查询缓存而带上了引号后面的查询都很快数据表没有变化的前提下。由此也可以说这不是一个小问题。对全局都很重要。而且写SQL时也一定要规避这样的问题。 那为什么不带引号就不能使用查询缓存呢我们在执行这句SQL时再接着执行一句show warnings;
select * from has where ids2;
show warnings;
#show warnings;的结果如下
Warning 1292 Truncated incorrect DOUBLE value: a
Warning 1292 Truncated incorrect DOUBLE value: 23,12 可见在执行不带引号的SQL时MYSQL发生了警告信息。像PHP一样只是因为这个不影响功能不会导致严重错误。但发生了警告对查询缓存是非常关键的。mysql对存在警告信息的查询语句无法缓存其记录集。即永远不能使用查询缓存。 所以一句话对mysql中的字符串字段的判断条件一定要带上引号
二、MYSQL查询缓存以及自动选择不使用查询缓存的情况 MYSQL查询缓存(5.1.17开始支持)顾名思义只是针对查询的缓存功能。在WEB开发甚至社会各种生活的方方面面都是缓存为王。MYSQL层也不例外 MYSQL查询缓存功能缓存的是SELECT操作或预处理查询的SQL语句和结果集新的SELECT语句或预处理查询语句先去查询缓存(根据整条SQL)判断是否存在可用的记录集注意SQL语句必须是完全一样SQL有改变大小写或者加上某个空格也会导致查询缓存不可用。即使完全相同的SQL如果使用不同的字符集、不同的协议等也会被认为是不同的查询而分别进行缓存。
1. MYSQL查询缓存的启用 启用mysql查询缓存涉及两个配置query_cache_type和query_cache_size任何一个参数设置为0都是查询缓存功能不可用。如果确实要关闭查询缓存请设置query_cache_type为0可减少检查query_cache_size的配置。 query_cache_type: 有0、1、2三个取值。0则不使用查询缓存。1表示始终使用查询缓存。2表示按需使用查询缓存。 如果query_cache_type为1而又不想利用查询缓存中的数据可以用下面的SQL SELECT SQL_NO_CACHE * FROM my_table WHERE condition; 如果值为2要使用缓存的话需要使用SQL_CACHE开关参数 SELECT SQL_CACHE * FROM my_table WHERE condition; 可以通过命令查看查询缓存命中次数 SHOW STATUS LIKE Qcache_hits; query_cache_size:允许设置query_cache_size的值最小为40K对于最大值则可以几乎认为无限制实际生产环境的应用经验告诉我们该值并不是越大查询缓存的命中率就越高也不是对服务器负载下降贡献大反而可能抵消其带来的好处甚至增加服务器的负载至于该如何设置下面的章节讲述推荐设置 为64M query_cache_limit:限制查询缓存区最大能缓存的查询记录集可以避免一个大的查询记录集占去大量的内存区域而且往往小查询记录集是最有效的缓存记录集默认设置为1M建议修改为16k~1024k之间的值域不过最重要的是根据自己应用的实际情况进行分析、预估来设置 query_cache_min_res_unit:设置查询缓存分配内存的最小单位要适当地设置此参数可以做到为减少内存块的申请和分配次数但是设置过大可能导致内存碎片数值上升。默认值为4K建议设置为1k~16K query_cache_wlock_invalidate:该参数主要涉及MyISAM引擎若一个客户端对某表加了写锁其他客户端发起的查询请求且查询语句有对应的查询缓存记录是否允许直接读取查询缓存的记录集信息还是等待写锁的释放。默认设置为0也即允许。在表的结构或数据发生改变时查询缓存中的数据不再有效。比如INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用不适合有大量数据更新的应用。对于一些并不是要经常查询的数据库可以使用query_cache_type2模式然后SQL语句加SQL_CACHE参数指定以减少查询缓存的开销。
2. MYSQL自动不使用查询缓存的情况
即便开启了MYSQL查询缓存但在以下条件下MYSQL会自动选择不使用查询缓存
1查询缓存对什么样的查询语句无法缓存其记录集大致有以下几类 2查询语句中加了SQL_NO_CACHE参数 3查询语句中含有获得值的函数包涵自定义函数如CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等 4对系统数据库的查询mysql、information_schema 5查询语句中使用SESSION级别变量或存储过程中的局部变量 6查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句 7查询语句中类似SELECT …INTO 导出数据的语句 8事务隔离级别为Serializable情况下所有查询语句都不能缓存 9对临时表的查询操作 10存在警告信息的查询语句例见:http://47.93.183.36/article/343.html 11不涉及任何表或视图的查询语句 12某用户只有列级别权限的查询语句
3. 查询缓存区的碎片整理 查询缓存使用一段时间之后都会出现内存碎片为此需要监控相关状态值并且定期进行内存碎片的整理和清理维护
1、FLUSH QUERY CACHE; //清理查询缓存内存碎片。 2、RESET QUERY CACHE; //从查询缓存中移出所有查询。 3、FLUSH TABLES; //关闭所有打开的表同时该操作将会清空查询缓存中的内容。
