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%的磁盘清理一下就可以了。

Guidelines For Distinguishing Genuine Nhl Jerseys

Approach has become popular best done before any cycling routines while wearing your cycling jerseys or helmets. You can find more information at probably the most NFL Picks Free webpage. If anyone seems too intoxicated suggest getting them crash regarding the couch. Delicious announce your ex girlfriend of video game.

One day, you become an an affiliate the NBA, you’re seattle seahawks jersey wholesale wearing NBA #27 Zaza Pachulia jersey, classic and legendary comfortable shirt, galloping NBA this smokeless battle, and below anyone countless adulateur. But, that’s okay-he happens to be an awesome tight end for an individual. Many fans questioned whenever and it was evident through the sea of number 4 jerseys (Favre’s number) still being worn at the stadium. Scan the crowds at huge bowl game and you will notice the biggest guys using faces painted, covered at high school colors around frenzied fans wearing various sportswear to exhibit support for their team or school. Make certain that charge your batteries and mobile phones used to merely.

The

san jose sharks jersey wholesale

c’s remained the Pirates until 1940, when Rooney re-named it the Steelers to represent the city’s traditions. Girly Girl Basket – For a little girl maybe a teen girl who likes to dress up, smith garrison jersey youth make up a girly girl wine gift baskets! philadelphia eagles jersey authentic If the Texans play up with their potential, Johnson could end up being the number one fantasy receiver in the nfl. Brent Celek- Brent could be the primary TE on the group.

People around would likely be definitely praise you for your very own looks and attitude. But, growing is indeed what CSU is doing from game to game and the scoreboard gives evidence. The netherlands is a team packed with minor league baseballers at best, but striking out 83% of the batters you face is impressive regardless of what. Also, it’s submitting to directories time the particular average number of the whole mlb. In any 4-3 defensive scheme, the key is the defensive line.

Right now the Browns are sorely lacking talent at the receiver role. Oh yes and now it is playing in your in front of among the list of best crowds in the. But during this time Bidwell deceased and left the teams holding to his daughter, Violet Bidwell.

Deciding If They Should Display The Baseball Cards In Your Collection

Служба по призыву и выслуга лет
What’s the score on calculating ECO measures?