使用分页处理大量数据库条目会随着时间的推移而变慢

一尘不染

使用分页处理大量数据库条目会随着时间的推移而变慢

hibernate

我正在尝试处理表中的数百万条记录（大小约为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>

对于大约50万条记录，这完全可以正常工作。我正在使用的页面大小为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

如果有帮助，我服务器的my.cnf（24 GB内存，2个四核proc）具有以下条目

  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

阅读 209

2020-06-20

共1个答案

一尘不染

这是我所做的，并将总执行时间减少了10倍。

我从原始查询的执行计划中意识到的是，它使用文件排序对所有结果进行排序并忽略了索引。那有点浪费。

我的测试数据库：500万条记录，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。您也可以执行选择计数（*），但它本身可能会花费很长时间，并且无助于优化任何内容。页数超过〜60后，每个查询大约需要2秒钟才能运行。

对我而言，页面大小为5000，因此我在位置0、5001、10001、15001等处获得了“名称”字符串列表。原来的页面数为1000，并在内存中存储1000个结果列表并不昂贵。

现在，遍历列表并运行此查询

SELECT blobCol
FROM my_table
where name >= <pageHeader>
and name < <nextPageHeader>
and city="<any string>"
and id= 1

这将运行N次，其中N =先前获得的列表大小。由于“名称”是主键列，并且“城市”也已建立索引，因此EXPLAIN显示此计算是使用索引在内存中执行的。

现在，每个查询需要1秒才能运行，而不是原来的30-40。因此，结合每页2秒的预处理时间，每页总时间为3-4秒，而不是30-40。

如果有人有更好的解决方案，或者该解决方案存在明显错误，请告诉我

2020-06-20