标题
[mysql] 1700万条记录下的极速 mysql !
clq
浏览(0) +
2008-07-09 14:15:19 发表
编辑
关键字:
最近换用了 pgsql,因为 mysql 的速度实在是撑不住了,没想到在优化索引的过程中意外的发现了一个可以让 mysql 也极速起来的方法!
哈哈,哈哈,天纵奇才呀,哈哈... (站长持继得意中..)
这个方法同样也可以用在 pgsql 或者其他数据库中,只要它支持索引. 不过它需要访问程序的配合,所以有钱的仁兄还是去买 oracle 吧,不过这至少证明了并不是昂贵的 oracle 才能处理巨量的数据,大数据量时应该换用 pgsql 也同样是成立的. 只不过认为 mysql 不能处理海量数据的看法应该是不正确的.
clq
首先炫耀一下我们的速度 -- 在 1700万数据中查询 100 条记录执行时间为 0.02 秒!
办法其实也不稀奇,就是索引.不过因为我用了特别的方法使查询只在索引中一次完成,所以速度超快,我原来还担心 mysql 对大文件索引的性能不好,现在看来是多虑了,这个速度其实就是 mysql 访问索引的速度,mysql 还是值得信赖的,至少在处理超大索引文件上.
首先看一下我们的表结构及它增长的情况:
CREATE TABLE `kline_min` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mark` int(11) DEFAULT NULL,
`jys_code` varchar(50) DEFAULT NULL,
`pb_code` char(20) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`open` float DEFAULT NULL,
`high` float DEFAULT NULL,
`low` float DEFAULT NULL,
`close` float DEFAULT NULL,
`volume` float DEFAULT NULL,
`amount` float DEFAULT NULL,
`up_count` int(11) DEFAULT NULL,
`down_count` int(11) DEFAULT NULL,
`time` int(11) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
`month` int(11) DEFAULT NULL,
`day` int(11) DEFAULT NULL,
`f_index` char(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `kline_min_test_index_pb_code` (`pb_code`),
KEY `kline_min_test_index` (`f_index`)
) ENGINE=MyISAM AUTO_INCREMENT=17726866 DEFAULT CHARSET=gbk;
很普通的表.是一个证券类的分钟 K 线历史记录,一共 7000 多支证券,每个每分钟都要写一条记录,那就是一分钟 7000 条(实际情况要少一点).现在是要求查询某一天中的 K 线记录,实际上这用复合索引是可以很快做到的,索引字段为 (year,month,day,pb_code,mark) 即可,但这里有一个问题,无论是 mysql 还是 pgsql 在这些条件全部使用 = 号的时候速度都是非常的快,但一旦有一个是 ">,<,>=" 速度立马下降 10 倍以上,我没试过 oracle 的情况,也许 oracle 做得好一点. 使用 = 很快说明索引的访问速度是不用说的,问题是怎么让 mysql/pgsql 明白,只要在 索引中计算就行了呢,即便是强制使用 use index 也不行,我真绝望啊. pgsql 在这种情况下表现就好很多,不过很多时间也要 1到2 秒,这是不符合条件的,mysql 更差了,需要 10 秒! 但在 = 的情况下 mysql 居然比 pgsql 快至少 2 倍,这也是我最后没有使用 pgsql 的原因.
偶然的,我拿 id 字段做试验,使用 > 等符号时也很快! 那就不是对 > 的运算符号有什么速度差异了,肯定是对索引的访问没有优化好! 为我证明我的想法,我用了 pb_code 字段又做了试验,因为 id 是一直在增加的,我担心是因为它在硬盘上的位置比较接近在这么快的,没想到用 pb_code 字段一样的超级快! 这就好办了嘛,想办法让我要使用的 where 和 order by 中的字段能合并在一个字段中就 ok 了,办法是最后用一个字符串字段来专门索引其值合并方法如下:
CONCAT(
RIGHT(CONCAT('000000', pb_code), 8) , # 8 位
'+',
RIGHT(CONCAT('000000', mark ), 6), # 6 位
'+',
RIGHT(CONCAT('000000', year ), 4), # 4 位
RIGHT(CONCAT('000000', month ), 2), # 2 位
RIGHT(CONCAT('000000', day ), 2) # 2 位
)
完整的数据导入语句如下:
insert into kline_min_test
( mark, jys_code, pb_code, name, open, high, low, close,
volume, amount, up_count, down_count, time, year, month,
day, f_index)
( SELECT mark, jys_code, pb_code, name, open, high, low, close,
volume, amount, up_count, down_count, time, year, month,
day,
CONCAT(
RIGHT(CONCAT('000000', pb_code), 8) , # 8 λ
'+',
RIGHT(CONCAT('000000', mark ), 6), # 6 λ
'+',
RIGHT(CONCAT('000000', year ), 4), # 4 λ
RIGHT(CONCAT('000000', month ), 2), # 2 λ
RIGHT(CONCAT('000000', day ), 2) # 2 λ
)
FROM kline_min limit 20)
ok 了! 我超级快的数据库完成了! 不过有一点要注意的是,如果 where 上有两个条件,速度会马上降下来.比如
select * from kline_min
where f_index<'00600356+000001+20080709'
order by f_index desc
limit 300
是非常快的,但是
select * from kline_min
where f_index<'00600356+000001+20080709'
and f_index>'00600356+000001+00000000'
#and year=2008
order by f_index desc
limit 300
就大打折扣了,可能是 mysql 去猜测它的前界了.还有一点要注意的是这样找出来的数据 pb_code 有可能不是你需要的,要在返回值中进行过滤.
NEWBT官方QQ群1: 276678893
可求档连环画,漫画;询问文本处理大师等软件使用技巧;求档softhub软件下载及使用技巧.
但不可"开车",严禁国家敏感话题,不可求档涉及版权的文档软件.
验证问题说明申请入群原因即可.
