MySQL索引-索引中没有满表
我有一个简单的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子句中，但是没有什么改变。我做错什么了？
回答 2
Stack Overflow用户
发布于 2021-02-01 16:21:52
MySQL的优化器试图估计执行查询的成本，以确定是否值得使用索引。有时，它将使用索引的成本与按顺序读取行的成本进行比较，并丢弃不属于结果的行。
在本例中，它决定如果使用大于140 k的偏移量，则放弃使用索引。
记住胶印是如何工作的。没有办法通过索引查找偏移量的位置。索引有助于按值而不是按位置查找行。因此，要执行偏移量查询，它必须检查up上第一个匹配行中的所有行。然后，它将检查到偏移量的行丢弃，然后计算出满足限制的足够行并返回这些行。
就像你想在一本书里读500-510页，但要做到这一点，你必须先读1-499页。然后，当有人要求你阅读511-520页时，你必须再读一遍1-510页。
最终，偏移量变得如此之大，因此在表扫描中读取14000行要比读取14000个索引条目+ 14000行要便宜。
什么？！？补偿真的这么贵吗？是的，是这样的。按值查找行要常见得多，因此MySQL是针对这种使用进行优化的。
因此，如果您可以重新设想您的分页查询来按值查找行，而不是使用限制/偏移量，那么您会更高兴。
例如，假设您阅读" page“1000，您将看到该页上的最高id值为13999。当客户端请求下一页时，您可以执行以下查询：
SELECT ... FROM mytable WHERE id > 13999 LIMIT 24;这是通过id的值进行查找的，由于它使用了主键索引，所以对其进行了优化。然后，它只读取24行并返回它们(MySQL至少足够聪明，可以在达到偏移+限制行之后停止读取)。
Stack Overflow用户
发布于 2021-02-01 21:23:40
最好的指数是
INDEX(active, status, first_public, id)使用巨大的偏移量是非常低效率的-它必须扫描超过140341 + 24行来执行查询。
如果你想“走过”桌子，就用“记住你离开过的地方”的技巧。对此的更多讨论：http://mysql.rjweb.org/doc.php/pagination
优化器放弃索引的原因:它决定在索引和表之间来回跳可能比简单地扫描整个表更糟糕。(截止值约为20%，但差异很大。)
https://stackoverflow.com/questions/66001240
复制
腾讯云开发者
