数据库使用菜鸟一枚,只会最基本的select。最近碰到一个mysql对某select语句使用索引不当而导致的性能问题,颇有意思,故记之

索引,是对数据库操作性能最息息相关的一个因素,我也不必多说。但是,你是否想过,就算建立了合适的索引,数据库也有可能没有足够的“智能”去选择针对某条select最合适的索引呢?这种事还真被我碰上了,于是第一次用上了force index这种神奇的东西~

先说一下背景情况:

系统环境:

  • os: windows 7 home edition 64 bit
  • db: MySQL 5.5.28 x64

 

涉及的数据库表,就一张,叫flow,用MyISAM引擎,有下面几列:

  • start: int
  • end: int
  • time: timestamp
  • amount: int
  • 含义:表中每行指从地点start到地点end在time时刻,共有amount的数据流动。 注意,在我们的应用场景下,start=end是可能的,即同一个地点发送和接收。
  • 数据量:1000多个可选择的地点(都可作为start或end),时间跨度约15天,共20,000,000条以上数据

 

在这个表上有下面这几个索引:

  • idx_start_time: 以start, time为key
  • idx_end_time: 以end, time为key
  • idx_time: 以time为key

 

要解决的问题有如下3个:

  • 一个时间段T内,以某个地点A为起点发出的数据总和
  • 一个时间段T内,以某个地点A为终点收到的数据总和
  • 一个时间段T内,以某个地点A为起点或终点产生的数据总和(如果起点和终点都是A,数据流动只计算一次)

怎么样,都是很简单的问题吧,于是三下五除二,就倒腾出了三个select语句。

设地点A为1,时间范围T是2012-01-01一整天

问题1:

  1. select sum(amount) 
  2. from flow 
  3. where start=1 
  4. and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

问题2:

  1. select sum(amount) 
  2. from flow 
  3. where end=1 
  4. and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

问题3:

  1. select sum(amount) 
  2. from flow 
  3. where (start=1 or end=1) 
  4. and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

再来测试一下:

  • 跑第一个,耗时0.01s。(不错。)
  • 再跑第二个,耗时0.01s。(很不错。这活儿太容易了~)
  • 再来第三个,耗时3s。(等等,这是砸回事儿?太不科学啦!怎么一合并一下多出了300倍的耗时??)

没办法,果然没那么简单轻松,又得苦逼地接着找办法啦。否则回头给1000多个地点统计半年里每一天的数据,不得算上1000 * 180 * 3 = 540,000s = 150h。150个小时啊,就做这么一个简单到爆的汇总,不扯淡吗!

好在,有前两个问题的帮忙,并利用在小学里打下的扎实的"集合论"基础,想到了一个回旋的方法:

问题3答案 = 问题1答案+问题2答案-(A同时为起点和终点的在时间段T内的数据流动)

而(A同时为起点和终点的在时间段T内的数据流动),这还不简单,直接把问题3里面的or改成and就行了:

  1. select sum(amount) 
  2. from flow 
  3. where (start=1 and end=1) 
  4. and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

再一跑这个,也不过耗时0.01s。把这三个查询合一块儿,也不到0.05s,比起那个坑爹的3s可是好多了。这下整个统计总能在几个小时里跑完,还成~

不过,如果到这儿就结束这问题,也就不会有这文章了。我没法就这么接受这种无比别扭的临时解决方案,况且在代码的注释上写个"I don't know why, but this method is faster"也有点太sb了~所以,本着从小养成的打破沙锅问到底的优良习惯,我就开始琢磨更'优雅'的解决方案。

终于,在脑子的一角想起了在那个我疯狂看书的年代,曾在一本sql的教程上看到过一个叫做explain的命令,可以用来分析select语句。好吧,操起这家伙开干吧。由于我贫乏的数据库知识,我也只能想到这是索引在捣蛋,于是我也就关注了explaint结果里的索引那一部分(说实话,其他的我也看不太懂= =)。

问题1~3的sql语句在explain命令分析下,得到的优先采用的索引如下:

  • 问题1:idx_start_time
  • 问题2:idx_end_time
  • 问题3:idx_time

这一看,果然索引不对劲。第1和第2个用的索引非常完美,但第3个就不对了。MySQL默认首先用了time作索引,也就是说它首先用time过滤一遍所有数据。在现在的问题下,先用time过滤导致效率底下的可能原因有(基本上是自己的想象,因为对数据库的底层实现机理实在是不了解):

  1. time的比较操作采用的是between范围比较,而start和end都是直接的等于比较
  2. 一张表中大概包含15天的数据,所以在按天查询的情况下,time第一遍过滤后,还会剩下大约1/15的数据需要进行后续过滤。相反,如果第一遍使用start或end进行过滤,因为一共有1000个左右的不同地点,所以只剩下约1/1000的数据还需要后续的条件过滤。

 

那么,我怎么样才能让MySQL修正这个索引判断错误呢。一搜,发现有个叫force index的东西,开始尝试:

  1. select sum(amount) 
  2. from flow force index (idx_start_time, idx_end_time) 
  3. where (start=1 or end=1) 
  4. and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

结果1.7s。快是快了一点,但也没多大改进啊,还是坑爹。

于是,接着想,这个式子到底怎么跑才能快呢?我得到的初步结论是:

  • 用start过滤一次原始数据,得到一个过滤结果r1
  • 用end再过滤一次原始数据,得到一个过滤结果r2
  • 合并r1和r2为r
  • 在r上,对time进行过滤

 

呃,是不是现在对问题3写的SQL语句让MySQL没办法找到这种解法呢?那么就改写法吧,搞不好就能让MySQL开窍了。于是,把or展开:

  1. select sum(amount) 
  2. from flow 
  3. where  
  4. (start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59'
  5. or  
  6. (end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59'

先不加force index,依然是坑爹的3s。

接着,加上force index

  1. select sum(amount) 
  2. from flow force index (idx_start_time, idx_end_time) 
  3. where  
  4. (start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59'
  5. or  
  6. (end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59'

见证奇迹的时刻到了,0.01s

这坑爹的MySQL在这个问题上终于被调教好了!

后记:

正如一开始提到的,我并没有很强的数据库知识和使用经验,所以上面提到的解法和观点很有可能是不精确甚至是错误的。虽然我最终看似得到了一些结论,但是产生这个问题的根本原因依然没有理解的十分透彻。进一步的分析可能需要对MySQL或其他类似关系型数据库的底层实现机制有一定的了解,对我而言这目前是一个彻底的空白。

我只能说, 对于MySQL,在有些情况下更改SQL语句的字面写法和强制指定索引真的是有可能起到奇效的。这并不只是理论上的可能性,而是实际工作学习中可能遇到的实实在在的问题。