MySQL数据join不走索引问题排查

【问题场景】

有个30多行的大SQL执行效率特别慢,问题集中在一个子查询上,开始没有建索引,可是发现索引都创建了,不走索引,大SQL简化一下,提出不走索引的部分,如下图所示

简单点,有三张表需要关联查询,关联关系如下

A表

B表 关联 A.col = B.id

C表 关联 B.col = C.id

问题出在 B表 关联 A.col = B.id,为啥?执行计划就是不走id主键,C表 关联 B.col = C.id都可以正常走

【解决思路】

1、尝试单表查询,验证索引是否正常 试了一下单表查询B是可以走主键索引,正常,排出索引问题

2、尝试优化SQL 修改了一下SQL,将left join 分别改为inner join,join和子查询,几种方式都不能走索引,排出优化可能

3、尝试在其他环境执行

发现在其他环境下可以正常,走索引,说明不是SQL的问题,排出SQL问题。

TMD !!! 到底哪出问题

既然有环境可以,肯定是哪里配置有问题,慢慢对比一下,于是从表结构,索引创建方式,逐一排查,还是没有发现问题。

没有思路了,困意来袭,下班回家,明日再战


新的一天开始,没办法,在baidu继续搜搜,“SQL 不走索引”

忽然间发现了一篇文章 https://www.cnblogs.com/jarjune/articles/7912722.html

启发了我,是不是两张表的编码方式不一样呢,有思路来,开搞,哈哈 ……

先看表的编码,哇塞,uft8mb4

再看字段的编码,神马,uft8mb4

我们默认都是uft8,谁干的,我的刀呢 …… 修改了表的编码和字段的编码方式,终于可以正常走索引了

【总结】

  • 对于大SQL,不要抱着大西瓜跑步,轻装上路。精确定位问题,简化问题,逐步缩小范围
  • 在一两个人开发的时候,大家都熟悉规范,使用默认的编码方式,一般会忽略编码方式不同的问题,所以团队开发要注意遵守规范
  • 在使用join连接查询的时候,如果编码不一致,就不走索引了

解决MySql数据库连接超时问题

在Mysql的默认设置中,如果一个数据库连接超过8小时没有使用(闲置8小时,即28800s),mysql server将主动断开这条连接。

在此输入图片描述

在运维监控系统中,程序会报如下异常:

<!-- lang: java -->
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on     Hibernate data access; nested exception is org.hibernate.exception.GenericJDBCException: could not inspect JDBC autocommit mode

底层错误日志:

<!-- lang: java -->
    [130925-102435-381][ERROR][ActionQueue:afterTransactionCompletion 179]could not release a cache lock
org.hibernate.cache.CacheException: java.lang.IllegalStateException: The org.hibernate.cache.UpdateTimestampsCache Cache is not alive.
	at org.hibernate.cache.EhCache.put(EhCache.java:125)
	at org.hibernate.cache.UpdateTimestampsCache.invalidate(UpdateTimestampsCache.java:69)
	at org.hibernate.engine.ActionQueue.afterTransactionCompletion(ActionQueue.java:174)
	at org.hibernate.impl.SessionImpl.afterTransactionCompletion(SessionImpl.java:424)
	at org.hibernate.jdbc.JDBCContext.afterTransactionCompletion(JDBCContext.java:225)
	at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:174)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:577)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:631)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:608)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:328)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:111)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:652)
	at com.sinosoft.sepmis.inspection.service.InspectionDataServiceImpl$$EnhancerByCGLIB$$d9f54f39.saveInspectionData(<generated>)
	at com.sinosoft.sepmis.util.ConnectionTelnet.execute(ConnectionTelnet.java:319)
	at com.sinosoft.sepmis.util.InspectionUtil.inspectExecute(InspectionUtil.java:331)
	at com.sinosoft.sepmis.util.InspectionUtil.partOfManual(InspectionUtil.java:259)
	at com.sinosoft.sepmis.util.ThreadManageUtil.startInspect(ThreadManageUtil.java:45)
	at com.sinosoft.sepmis.util.ThreadManageUtil.run(ThreadManageUtil.java:31)
	at java.lang.Thread.run(Thread.java:735)
Caused by: 
java.lang.IllegalStateException: The org.hibernate.cache.UpdateTimestampsCache Cache is not alive.
	at net.sf.ehcache.Cache.checkStatus(Cache.java:1204)
	at net.sf.ehcache.Cache.put(Cache.java:549)
	at net.sf.ehcache.Cache.put(Cache.java:522)
	at org.hibernate.cache.EhCache.put(EhCache.java:119)
	... 19 more

原因分析:

由于程序后台一直在运行定时程序,而且定时程序的运行为7*24小时。在这期间程序会出现八小时闲置的情况,即程序和数据库之间没有任何交换操作。这样Mysql数据库端会主动动断开连接。这样如果定时启动后,要想获取到Mysql的连接,自然就会报出异常。

方法一,Mysql数据库延长wait_timeout参数时间(不推荐):

通过修改my.ini(Linux系统为my.cnf)文件中wait_timeout=xxx,即便是这样系统在闲着超过此参数设置时间后依然会出现异常。(注意:此参数单位为秒) 也可以通过mysql命令设置。

<!-- lang: sql -->
Mysql > set global wait_timeout=xxx;

设置后重启mysql服务,命令如下: <!-- lang: sql --> service mysql restart 登录mysql查看是否生效: <!-- lang: sql --> Mysql > show global variables like 'wait_timeout'; 在此输入图片描述

方法二,修改hibernate 中配置属性(不推荐):

代码如下

<!-- lang: xml -->
<property name="connection.autoReconnect">true</ property>
<property name="connection.autoReconnectForPools">true</ property >
<property name="connection.is-connection-validation-required">true</ property>

个人通过试验发现,这种方法并没有起到效果。不知道网上的写文章的人是怎么解决的。

方法三,使用第三方数据库连接池(推荐): 现在第三方数据库连接池使用较多的为c3p0,proxool等,在性能上c3p0稍好一些,原因c3p0数据库连接池,底层有一个定时查看数据库连接是否有效的参数。而且Hibernate的api中也推荐使用第三方数据库连接池,因为Hibernate本身的数据库连接池过于简单、本身存在bug。c3p0参数配置如下:

<!-- lang: xml -->
<!-- 数据源 --> 
<bean id="dataSourceTarget" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name="user" value="root" />
<property name="password" value="root" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.61.208:3306/sinoomv1_0_0" />
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<!-- 系统初始化连接数 -->
<property name="initialPoolSize" value="10" />
<!-- 最大连接数 -->
<property name="maxPoolSize" value="30" />
<!-- 最小连接数 -->
<property name="minPoolSize" value="10" />
<!--最大空闲时间,600秒(10分钟)内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime" value="600" />
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->  
<property name="acquireIncrement" value="3" />
<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->  
<property name="idleConnectionTestPeriod" value="60" />
<!-- 每次从pool内checkout连接时测试有效性(同步操作)
    程序每次数据库调用都连接有效性,若无效关闭此连接并剔除出pool,
   从pool内取其他连接,慎用,会造成至少多一倍的数据库调用。Default:false -->
<property name="testConnectionOnCheckout" value="false" />
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts" value="30"/>
<!--两次连接中间隔时间,单位毫秒。Default: 1000 -->
<property name="acquireRetryDelay" value="1000"/>
</bean>

查看mysql数据库连接池情况,命令如下:

<!-- lang: sql -->
Mysql> show processlist;

在此输入图片描述

ps:最近在在停起程序的时候我发现了一个问题,就算是我停止了服务,但是程序和mysql数据库之间的连接数依然没有断开,后来经查资料和同事请教,我发现在上边的c3p0配置存在一个小bug。因为我将c3p0交给Spring进行管理,但是在上边注入的bean中没有加上destroy-method="close"这个属性。 也就是说Spring是不能正常销毁连接的,由此出现了我说的问题。

写在这里以供大家警示,真是马虎啊,竟然忘记了销毁,实在不应该。

<!-- lang: xml -->
<!-- 数据源 --> 
<bean id="dataSourceTarget" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
 ... ...
</bean>

参考文章:

Hibernate API 文档 http://blog.csdn.net/nethibernate/article/details/6658855 http://zhidao.baidu.com/question/96746075.html