我有一个简单的InnoDB表，有1M+行和一些简单的索引。我需要按first_public和id列对这个表进行排序，并得到其中的一些，这就是为什么我索引了first_public列。
first_public目前是唯一的，但在现实生活中可能不是。
mysql> desc table;
+--------------+-------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| id_category | int | NO | MUL | NULL | |
| active | smallint | NO | | NULL | |
| status | enum('public','hidden') | NO | | NULL | |
| first_public | datetime | YES | MUL | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+--------------+-------------------------+------+-----+---------+----------------+
8 rows in set (0.06 sec)
当我在130000+
之前的行工作时，它工作得很好mysql> explain select id from table where active = 1 and status = 'public' order by first_public desc, id desc limit 24 offset 130341;
+----+-------------+--------+------------+-------+---------------+---------------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | table | NULL | index | NULL | firstPublicDateIndx | 6 | NULL | 130365 | 5.00 | Using where; Backward index scan |
+----+-------------+--------+------------+-------+---------------+---------------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
但是当我试图得到一些下一行(偏移量140000+)，它看起来像MySQL不使用first_public列索引。
mysql> explain select id from table where active = 1 and status = 'public' order by first_public desc, id desc limit 24 offset 140341;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | table | NULL | ALL | NULL | NULL | NULL | NULL | 1133533 | 5.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
我试图将first_public列添加到select子句中，但没有任何改变。我做错了什么?
MySQL的优化器尝试估算执行查询的成本，以决定是否值得使用索引。有时，它会比较使用索引的成本与按顺序读取行并丢弃不属于结果的行的成本。
在本例中，它决定如果您使用的OFFSET大于140k，它就放弃使用索引。
记住OFFSET是如何工作的。没有办法通过索引来查找偏移量的位置。索引帮助按值查找行，而不是按位置。因此，要执行OFFSET查询，它必须检查从第一个匹配行开始的所有行。然后，它丢弃它检查的行，直到偏移量，然后计算出满足LIMIT的足够行并返回这些行。
这就像如果你想读一本书的第500-510页，但要做到这一点，你必须先读第1-499页。然后，当有人让你读第511-520页时，你必须再读一遍第1-510页。
最终，偏移量变得如此之大，以至于在表扫描中读取14000行比读取14000个索引项+ 14000行更便宜。
什么? ! ?OFFSET真的这么贵吗?是的，它是。按值查找行更为常见，因此MySQL针对这种用法进行了优化。
因此，如果您可以重新设想您的分页查询按值查找行，而不是使用LIMIT/OFFSET，您会更高兴。
例如，假设您读取"page"1000，您可以看到该页面上最高的id值是13999。当客户端请求下一页时，您可以执行以下查询:
SELECT ... FROM mytable WHERE id > 13999 LIMIT 24;
根据id的值进行查找，因为它利用了主键索引，所以进行了优化。然后它只读取24行并返回它们(MySQL至少足够聪明，在达到OFFSET + LIMIT行后停止读取)。
最佳索引
INDEX(active, status, first_public, id)
使用巨大的偏移量是非常低效的——它必须扫描超过140341 + 24行来执行查询。
如果你想"遍历"在表格中，使用"记住你离开的地方"的技巧。关于此的更多讨论:http://mysql.rjweb.org/doc.php/pagination
优化器放弃索引的原因:它认为在索引和表之间来回跳跃可能比简单地扫描整个表更糟糕。(临界值约为20%，但差异很大。)