mysql强制使用索引
在公司后台某模块功能记录日志中有一个搜索功能,通过前段时间的产品使用时间区间进行搜索反馈有些卡顿,我发现这个搜索功能比较慢,要3秒左右才能出来,就决定对这里做一下优化。
通过分析代码和SQL发现最核心的问题在于一个区间查询耗时太长,耗时2秒多,所以我决定看看这里能不能优化,其中核心SQL为
SELECT * FROM XXX_log WHERE (`ctime` BETWEEN '2017-09-11 09:34:13' AND '2017-10-11 09:34:13') and id > 27851 AND column1 = 'xxx' AND (column2 = 'null' OR LENGTH(column2) > 91) ORDER BY id DESC LIMIT 0, 30
这个查询是一个简单查询,没有联表,就是单表的limit分页查询,外加一个时间区间和字段搜索,我通过SQL分析 explain 发现并未走索引,扫描区间也很大,由于该表拥有接近100万的记录,查询的扫描区间接近50万,我感觉这样肯定是效率不高的。
然后查询了一下数据,发现耗时确实在2.6秒左右,我起初的设想是,column2字段查询肯定是没法改了 ,毕竟那涉及到以前的业务,该字段这样搜索查询确实有违常理,但是既然之前的功能已经这样设计了,现在去做调整,反而有可能有问题,那么就只有在 id ctime column1下手了,id它写死了,定了某条记录开始进行查询,我猜测可能之前的数据是测试数据或者是废弃数据,那既然有个标尺,就不去动它,column1是固定的查询值,这样也没法改,这样的固定值在数据行中差异不明显,加索引效果也不大,然后是ctime了,唯一能动文章的也只有它了。
然后我好好分析了一下这个模块要实现的功能逻辑,就是查询这段时间内符合要求的数据,这段时间,很明显是一个月,能不能设定这个查询区间只能查一个星期呢?
我改为一周内的时间区间查询,肯定能降低查询的查询区间啊,然而令人遗憾的是,这个降低查询区间间隔,并没有多大效果,最后无奈,我给ctime添加了一个 normal index btree索引,加了索引后,我本以为一切都会好起来,谁知,explain后效果还是那样!
可能用到的索引keys 主键 ctime ,然而呢 查询区间还是接近45万,效果并不明显,查询时间也还是2秒多,这可让我犯难了,一时束手无策,思考许久,突然想到这感觉是索引没有真正生效导致的,那么能不能强制告诉mysql使用某个索引了,让他主动是走某个索引,然后我查询了资料 找到了force index(强制要走的那个索引) ,我马上就试了一下
SELECT * FROM XXX_log force index(ctime) WHERE (`ctime` BETWEEN '2017-09-11 09:34:13' AND '2017-10-11 09:34:13') and id > 27851 AND column1 = 'xxx' AND (column2 = 'null' OR LENGTH(column2) > 91) ORDER BY id DESC LIMIT 0, 30
果然 explain分析之后 使用了ctime索引
而且查询区间降低到3万多了,这个效果太明显了,耗时降低到0.2秒左右,然后这个功能马上从3秒才能打开降低到0.3秒就能打开了,这个优化效果令我非常满意。
在和运维讨论该表的数据时,运维提供方案说,看到该表数据量庞大 接近100万,并且时间很多是2015年 2016年的,他提出可以转移2015年 2016年的数据,我查看了一下,2015年和2016年的数据合计起来有70多万了,占据了绝大部分,如果转移的话,确实有利于我们查询啊,比对啊,更新啊之类的,因为这是一张日志表,在某个期间具有时效性,毕竟大部分时候今年不会再看去年前年的查询数据了,所以我觉得这种做法也有依据,也合理,恰逢同事请假休息,此事等他回来再做讨论。
本次想到强制使用索引,也是灵光一现,稍微有一些运气在里面的,不然还真不好解决这个问题,算是天公作美吧!遂感慨一首:
苍茫青天显神威,
拦路崔嵬面如灰。
忽而得来灵光现,
开山破土镇边陲!