MySQL datetime类型使用like导致全表扫描的问题总结

最近项目里有一段mysql语句导致了数据库服务器的load过高报警,语句如下:

SELECT
    content
FROM
    communication_log
WHERE
    gmt_create LIKE '2016-12-02%';

explain了这条sql之后可以看出执行计划是全表扫描,查询了882210条记录,字段的索引没有用上,查询的时间为1.23:2be95c40-3efa-4a59-be3c-4437c1744c0a

原因是gmt_create是datetime类型,条件中写了like,会有隐式转换,gmt_create字段需要在比较前转换成String类型,字段的索引将不会被用到,查询变为全表扫描。

解决方法是把原来的SQL改成:
SELECT
    content
FROM
    communication_log
WHERE
    gmt_create BETWEEN '2016-12-02 00:00:00' AND '2016-12-02 23:59:59';

explain的结果,用上了索引只检索给定范围的行,扫描的行数也减少到了108行,查询的时间为0.02:

1b89fe5c-7e16-4a86-8ec5-c6d9c9008167

执行MySQL使用事务的方法

最近我团队出现了一个挺让我头疼的问题,由于我们的开发没来得及抽出时间来开发功能,一些紧急的需求过来了,我们团队的同学只能使用SQL语句来暂时帮助业务方将问题解决。然而有些需求却需要执行很多的SQL语句来解决,这样存在很大的风险,一不小心一个SQL写错了,就会对业务方造成很大的影响。所以我提出了几个改进的建议:
 1. 尽量不使用SQL语句,团队需要从原来的工作中总结经验,将功能提炼出来,通过系统功能来解决问题。这个建议是需要提前埋伏一些功能,对未来做一些预判,而且要做到能够快速开发功能来应对业务方紧急的需求。
2. 如果一定要执行SQL语句,执行前需要有人check,结对编程,写的同学需要在check的同学面前把逻辑讲清楚。另外,为了保险起见,在执行SQL的时候必须要使用事务,执行完之后check,如果没问题就commit,否则就rollback:
set autocommit = 0;  # 先设置不允许自动提交 
# 执行你的SQL
commit;  # 如果没问题就commit
rollback;  # 如果有问题就rollback
set autocommit = 1;  # 设置回允许自动提交,因为MySQL是默认自动提交的
注:如果很不幸在执行之后没有做commit或者rollback的处理,你的MySQL客户端退出之后MySQL会自动将你执行的SQL rollback回去。

Java+MySQL报错Value ‘0000-00-00’ can not be represented as java.sql.Date的解决方法

今天在做mybatis的时候,运行主程序遇到如下报错:

Value '0000-00-00' can not be represented as java.sql.Date

解决的方法是在mybatis的连接数据库配置文件中将

<property name="url" value="jdbc:mysql://127.0.0.1:3306/tqmall?useUnicode=true&amp;characterEncoding=UTF-8" />

改成:

<property name="url" value="jdbc:mysql://127.0.0.1:3306/tqmall?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull" />

即添加了:

zeroDateTimeBehavior=convertToNull

这样就就解决问题了。

MySQL报错:1030 Got error 28 from storage engine

今天在工作的时候,MySQL一直报出代码为1030的错误:

1030 Got error 28 from storage engine

后来去查阅了下资料发现1030错误“28 from storage engine”的意思是没有足够的磁盘空间,去查看下系统的磁盘情况:

-> /root >df -h
文件系统              容量  已用 可用 已用% 挂载点
/dev/mapper/VolGroup00-LogVol00
                      141G  141G   0  100% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                1013M     0 1013M   0% /dev/shm
/dev/sdb2             459G  135G  301G  31% /data1

想办法把使用了100%的磁盘清理一下就可以了。

关于MySQL查询约束条件类型需要注意的一点

最近在工作中遇到了数据库服务器产生很多读写队列的问题,于是要求大家开始优化我们的SQL语句。

下面是查询quotedata_history表中的code字段的SQL语句,其中code字段的类型是varchar(6)。

20121217210533

以上查询语句是:

SELECT * FROM quotedata_history WHERE `code` = 600000

大家可以看到这个语句的code条件600000是没有加引号的,通过Navicate的截图大家可以看到这次查询花了14.169秒。

20121217210534

以上查询语句是:

SELECT * FROM quotedata_history WHERE `code` = '600000'

大家可以看到这个语句的code条件600000加了引号,通过Navicate的截图大家可以看到这次查询花了0.087秒。

这两个查询的效率相差实在是太大了!

所以大家要注意,以后查询数据库的时候,如果字段是string类型的,条件的值必须加上单引号,以提升查询的效率!