我正试图从我的表处理数百万条记录(大小约为30 GB),我目前正在使用分页(mysql 5.1.36)。我在for循环中使用的查询是
select blobCol from large_table
where name= 'someKey' and city= 'otherKey'
order by name
LIMIT <pageNumber*pageSize>, <pageSize>
对于大约500K条记录,这工作得非常好。我使用的页面大小为5000,在第100页之后,查询开始显着减慢。前80页可以在2-3秒内提取出来,但是在130页之后,每一页都需要30秒来检索,至少到200页。我的一个查询大约有900页,那将花费太长时间。
The table structure is (type is MyISAM)
name char(11)
id int // col1 & col2 is a composite key
city varchar(80) // indexed
blobCol longblob
我能做些什么来加快速度?查询的解释显示了这个
select_type: SIMPLE
possible_keys: city
key : city
type: ref
key_len: 242
ref: const
rows: 4293720
Extra: using where; using filesort
如果有帮助,我的服务器(24 GB ram, 2个四核进程)的my.cnf有这些条目
key_buffer_size = 6144M
max_connections = 20
max_allowed_packet = 32M
table_open_cache = 1024
sort_buffer_size = 256M
read_buffer_size = 128M
read_rnd_buffer_size = 512M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
tmp_table_size = 128M
max_heap_table_size = 64M
下面是我所做的,并将总执行时间减少了10倍。
我从原始查询的执行计划中意识到,它使用filesort对所有结果进行排序,并忽略索引。那有点浪费。
我的测试数据库:5 M记录,20 GB大小。表结构与题目
相同不是在第一个查询中直接获得blobCol,而是首先获得每个页面开头的'name'的值。无限期地运行这个查询,直到它返回0个结果。每次将结果添加到列表
SELECT name
FROM my_table
where id = <anyId> // I use the id column for partitioning so I need this here
order by name
limit <pageSize * pageNumber>, 1
正弦页码以前不知道,从值0开始,一直增加,直到查询返回null。您也可以执行select count(*),但这本身可能需要很长时间,并且无助于优化任何内容。当页数超过~60时,每个查询大约需要2秒才能运行。
对于我来说,页面大小是5000,所以我在位置0,5001,10001,15001等处得到了一个'name'字符串列表。结果显示页面数为1000,在内存中存储包含1000个结果的列表并不昂贵。
现在,遍历列表并运行这个查询
SELECT blobCol
FROM my_table
where name >= <pageHeader>
and name < <nextPageHeader>
and city="<any string>"
and id= 1
这将运行N次,其中N =先前获得的列表的大小。由于'name'是主键col, 'city'也被索引,EXPLAIN显示该计算是使用索引在内存中执行的。
现在,每个查询运行1秒,而不是原来的30-40秒。因此,结合每页2秒的预处理时间,每页的总时间为3-4秒,而不是30-40秒。
如果有人有更好的解决方案,或者如果这个有明显的错误,请告诉我
您可以使查询更精确,从而降低限制。
SELECT col1,col2, col4
FROM large_table
WHERE col1>"SomeKey" OR
(col1="SomeKey" AND col2>="OtherKey")
ORDER BY col1,col2
LIMIT PageSize
但是在每次数据库调用后更新"SomeKey"one_answers"OtherKey"
我在过去用Oracle 10g数据库尝试过同样的方法,得到了相同的结果(我的表有6000万行)。最初的页面检索速度很快,但随着页面数量的增加,查询变得太慢了。由于索引看起来是正确的,所以您不能对它们做太多事情,而且我不确定通过调优数据库配置可以实现什么。我想我有不同的需求,但我找到的唯一解决方案是将数据转储到文件中。如果col1的值集合有限,则可以去掉col1并生成n个表,每个表对应col1的已知值。如果col1是未知的,那么我不知道这个的解。您可以从非常大的表中检索小的数据集,但是检索大的数据集需要花费大量时间,而且分页对您没有任何帮助。您必须通过将数据转储到文件或生成其他表来对数据进行预处理。