欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-02-07 08:34:58
...
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