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将主动断开这条连接。

在此输入图片描述

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

底层错误日志:

原因分析:

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

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

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

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

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

代码如下

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

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

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

在此输入图片描述

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

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

参考文章:

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