PHP MySQL 分页很慢

Question

我的 table

Field   Type    Null    Key Default Extra
id      int(11)     NO  PRI NULL    auto_increment
userid  int(11)     NO  MUL NULL     
title   varchar(50) YES     NULL     
hosting varchar(10) YES     NULL     
zipcode varchar(5)  YES     NULL     
lat     varchar(20) YES     NULL     
long    varchar(20) YES     NULL     
msg    varchar(1000)YES MUL NULL     
time    datetime    NO      NULL     

那是 table 。我已经模拟了 500k 行数据并随机删除了 270k 行只留下 230k 自动增量为 500k。

这是我的索引

Keyname Type    Unique  Packed  Field   Cardinality Collation   Null
PRIMARY BTREE   Yes No  id            232377    A       
info    BTREE   No  No  userid          2003    A       
lat                                    25819    A   YES
long                                   25819    A   YES
title                                  25819    A   YES
time                                   25819    A   

考虑到这一点，这是我的查询:

SELECT * FROM posts WHERE long>-118.13902802886 AND long<-118.08130797114 AND lat>33.79987197114 AND lat<33.85759202886 ORDER BY id ASC LIMIT 0, 25

显示第 0 - 15 行(总共 16 行，查询耗时 1.5655 秒)[id: 32846 - 540342]

查询只给我带来了 1 页，但因为它必须搜索所有 230k 记录，所以仍然需要 1.5 秒。

这里是解释的查询:

id  select_type table   type    possible_keys   key     key_len ref rows    Extra
1   SIMPLE      posts   index   NULL            PRIMARY 4       NULL 25     Using where

所以即使我使用 where 子句只返回 16 个结果，我仍然得到一个缓慢的查询。

例如，如果我进行更广泛的搜索:

SELECT * FROM `posts` WHERE `long`>-118.2544681443 AND `long`<-117.9658678557 AND `lat`>33.6844318557 AND `lat`<33.9730321443 ORDER BY id ASC LIMIT 0, 25

显示第 0 - 24 行(总共 25 行，查询耗时 0.0849 秒)[id: 691 - 29818]

从 20 页中检索第一页要快得多，共找到 483 页，但我限制为 25 页。

但是如果我要最后一页

SELECT * FROM `posts` WHERE `long`>-118.2544681443 AND `long`<-117.9658678557 AND `lat`>33.6844318557 AND `lat`<33.9730321443 ORDER BY id ASC LIMIT 475, 25

显示第 0 - 7 行(总共 8 行，查询耗时 1.5874 秒)[id: 553198 - 559593]

我的查询很慢。

我的问题是如何实现良好的分页？当网站上线时，我预计当它起飞时，每天都会有数百个帖子被删除和发布。帖子应按 id 或时间戳排序，并且 Id 不是连续的，因为某些记录将被删除。我想要一个标准的分页

1 2 3 4 5 6 7 8 ... [Last Page]

Answer 1

使用 WHERE 子句从出现在较早页面上的结果记录中过滤:然后您不需要指定偏移量，只需指定行数。例如，跟踪最后看到的 ID 或时间戳，并仅过滤那些 ID 或时间戳大于该值的记录。