Press "Enter" to skip to content

Mysql优化经验之一:奇怪的索引

自从学会了使用slow-query-log这一招以后,便经常关注一下这个日志里面的内容,执行的太慢的索引总要想办法清理掉不是?昨天晚上搞了三个多小时,才算是搞明白了一点东西。

以前总以为在where和order by所用到的字段上建个索引就OK了,有了索引Mysql就不会去物理数据里进行查询和计算,其实,远远不是这么回事。

在slow-query-log里面出现最多的一类语句是:select * from topics where category_id=1 and createdon>’2008-12-10′ order by createdon,id limit 6,很简单,不是吗?id是主键,索引当然不用我去关心,category_id和createdon分别建立了索引,可是这条语句执行的还是非常慢。(表里数据10万条,空间450M左右,文章content字段是text,比较大。)

执行explain以后,显示使用的索引是category_id,没有使用createdon,难道mysql执行的时候只能使用一个索引?于是想到建立联合索引,根据网上文章的说明,对于这条语句来说,给category_id和createdon建立联合索引是最有效的,查询和排序的时候都可以用到索引,而且对于where category_id=1 order by createdon这样的语句这个联合索引也是最有效的。于是删除了createdon的索引,给这两个字段建立联合索引,再执行,还是很慢,再explain,索引使用没有问题了,但是Extra里显示using filesort。为什么呢?一位朋友说可能是sort_buffer不够大,加大了这个配置以后无效,还是这样。后来在排序条件中把id删掉,只保留createdon,Extra里就没有using filesort了,测试查询运行时间从0.15秒降到了0.001秒。那好吧,放弃排序字段里的id字段,虽然可能会有搜索出来的内容有点影响,但是并不是什么至关重要的数据。

slow-query-log里出现最多的另一条语句是:select * from dts where (in_id=1 or out_id=1) and createdon>’2008-12-10′ order by createdon,id limit 1,三个字段分别建立了索引。使用explain查看结果,显示使用的索引是createdon,需要扫描的行数是9万多,(表内数据70万,空间150M左右),但是如果我删除createdon这个条件,只用前面两个or的话,explain显示最终使用的索引是in_id和out_id的union索引,也就是自动联合了两个索引,需要扫描的行数只要25行就能找到结果,执行速度当然不在同一个级别上了。见鬼了,为什么多了一个条件mysql反而会去使用更差的索引呢?

后来在完整语句的基础上加上use index关键字,select * from dts use index(in_id,out_id) where (in_id=1 or out_id=1) and createdon>’2008-12-10′ order by createdon,id limit 1显示最终使用的索引就是两个id了。但是这条语句是django的ORM自动生成的,我也改不了。后来想到再加一个限定条件上去试试:select * from dts where user_id=1 and (in_id=1 or out_id=1) and createdon>’2008-12-10′ order by createdon,id limit 1,结果显示最终使用的索引就变成了user_id,虽然不如使用两个id的union index效果更好,但是已经远远好于createdon的索引了,需要扫描300多行取得结果。整条语句的实际执行时间从1.8秒缩短到0.0015秒。

问题虽然已经解决,原理却不甚清晰,是否有更好的解决方案也不知道,还得再研究研究,测试测试。

3 Comments

  1. 老时 2008年12月20日

    换皮了啊呵呵

  2. 非鱼 Post author | 2008年12月20日

    不止换皮,连芯都换了。

  3. hehe 2008年12月24日

    非鱼 :不止换皮,连芯都换了。

Comments are closed.