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秒。

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