mysql - 简单的 mySQL 查询不使用索引

Question

我对 mySQL 和简单索引的简单查询有非常奇怪的问题。我正在尝试使用此查询:

SELECT * FROM `counter_links` WHERE `link_id`=1544;

如您所见，还有名为 link_id 的索引:

mysql> show indexes from counter_links;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| Table         | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  | Index_comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| counter_links |          1 | link_id     |            1 | link_id     | A         |        NULL |     NULL | NULL   |      | BTREE      | disabled |               |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
1 row in set (0.12 sec)

但看看 EXPLAIN 返回了什么:

mysql> explain SELECT * FROM `counter_links` WHERE `link_id`=1544;
+----+-------------+---------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | counter_links | ALL  | NULL          | NULL | NULL    | NULL | 22103687 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.03 sec)

为什么mySQL不在那里使用索引？我看到在较小版本的表上同样有效，但我无法识别问题出在哪里。你知道吗？

这看起来更奇怪，因为较低的 ID 有时会与索引一起使用。

提前感谢您的所有评论!

干杯!

雅各布

Answer 1

the index 的注释字段显示该索引已禁用，不知道为什么。

ALTER TABLE ... ENABLE KEYS 可能有效