MySQL笔记：select默认使用不当索引导致的巨大性能损失问题_MySQL

bitsCN.com

MySQL笔记：select默认使用不当索引导致的巨大性能损失问题

 

数据库使用菜鸟一枚，只会最基本的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：

 select sum(amount) 

from flow 

where start=1 

and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

问题2:

select sum(amount) 

from flow 

where end=1 

and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 

问题3：

select sum(amount) 

from flow 

where (start=1 or end=1) 

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就行了：

select sum(amount) 

from flow 

where (start=1 and end=1) 

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过滤导致效率底下的可能原因有（基本上是自己的想象，因为对数据库的底层实现机理实在是不了解）：

time的比较操作采用的是between范围比较，而start和end都是直接的等于比较

一张表中大概包含15天的数据，所以在按天查询的情况下，time第一遍过滤后，还会剩下大约1/15的数据需要进行后续过滤。相反，如果第一遍使用start或end进行过滤，因为一共有1000个左右的不同地点，所以只剩下约1/1000的数据还需要后续的条件过滤。

 

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

select sum(amount) 

from flow force index (idx_start_time, idx_end_time) 

where (start=1 or end=1) 

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展开：

select sum(amount) 

from flow 

where  

(start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') 

or  

(end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') 

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

接着，加上force index

select sum(amount) 

from flow force index (idx_start_time, idx_end_time) 

where  

(start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') 

or  

(end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') 

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

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

后记：

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

 

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

 

bitsCN.com