没有ORDER BY子句的LIMIT子句是任意的。您显示的所有三个查询：

SELECT * FROM testtable LIMIT 0 , 2;
SELECT * FROM testtable LIMIT 2 , 2;
SELECT * FROM testtable LIMIT 4 , 2;

可以返回完全相同的两行。因此，您必须添加一个ORDER BY子句以使其可靠地工作：ORDER BY ifk1, ifk2 .
但是，是的，每次访问都要一次又一次地对数据进行排序，这会花费很多时间，这就是为什么我们尽量避免使用偏移量，而是使用键：

SELECT *
FROM testtable 
WHERE ifk1 > @last_ifk1 OR (ifk1 = @last_ifk1 AND ifk2 > @last_ifk2)
ORDER BY ifk1, ifk2
LIMIT 2;

分页几乎总是很慢。但是这种访问方法可以使用主键的唯一索引（ifk1，ifk2），并且可以非常快地访问接下来的两行。这取决于MySQL的实现和它的版本。

我不确定我是否理解了你的问题中的索引部分。但是一般来说，如果你想迭代一个更大的结果集，你可以使用游标，如下所述：
https://www.mysqltutorial.org/mysql-cursor/
这将用于存储过程，但其他语言的数据库驱动程序将公开类似的功能。

如果您的请求是使用索引：逻辑是一样的：

mysql> SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
+------+------+-------+
| ifk1 | ifk2 | data1 |
+------+------+-------+
|    2 |    4 | c     |
|    5 |    8 | d     |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | testtable | NULL       | index | PRIMARY       | keyName | 43      | NULL |    6 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 4,2;
+------+------+-------+
| ifk1 | ifk2 | data1 |
+------+------+-------+
|    2 |    2 | e     |
|    2 |    5 | f     |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 4,2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | testtable | NULL       | index | PRIMARY       | keyName | 43      | NULL |    6 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

以上使用KeyName索引：也许您应该停用或删除KeyName索引，并启用查询以使用PK复合索引：请按照以下步骤操作：
DROP keyName索引第一：

mysql> ALTER TABLE testtable
    -> DROP INDEX keyName;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后再次运行查询，查看查询中是否使用了PK组合键，我认为这会使查询更快：

mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testtable | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    6 |    33.33 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)