由于损坏/过时的索引，MySQL 无限制地返回零行(空集)

Question

我在一张大约有 1500 个条目的大 table 上工作，

CREATE TABLE `crawler` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...
`provider_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `crawler_provider_id` (`provider_id`),
...
) ENGINE=MyISAM ...

provider_id 用于将此表链接到另一个表'providers，该表已被清除并用新数据重新填充。我试图重新创建从“爬虫”到“提供者”的连接(这在 MyISAM 的情况下并不重要)，但出于某种原因，如果我不提供限制，MySQL 会在我的脚本中返回零行。

mysql> SELECT `crawler`.`id` FROM `crawler` WHERE `crawler`.`provider_id` > 1371;
Empty set (0.40 sec)

但是

mysql> SELECT COUNT(*) FROM `crawler` WHERE `crawler`.`provider_id` > 1371;
|   346999 |

和

mysql> SELECT `crawler`.`id` FROM `crawler` WHERE `crawler`.`provider_id` > 1371 LIMIT 10;
10 rows in set (0.01 sec)

如果我从表中选择一些数据并自己检查它，我可以看到大于 1371 的值。

我可以通过删除索引来解决这个问题(稍后重新创建)，但我非常困惑。我从未见过索引与表数据不同步(而且我不知道它们会影响返回行的值)。不幸的是，我在删除索引之前没有执行“CHECK TABLE”，但它现在“status=ok”，我在日志中看不到任何错误，“REPAIR TABLE”显示没有问题。

那么，这是一个常见问题吗？可能是什么原因？这台服务器之前有一些内存不足的问题，是否也是这里的问题？

Answer 1

您的查询几乎可以肯定与 MyISAM 中的表损坏有关。

我做了

root@localhost [kris]> create table crawler (
  id integer not null auto_increment primary key, 
  provider_id int(11) DEFAULT NULL, 
  PRIMARY KEY (id), 
  KEY crawler_provider_id (provider_id)
) engine = myisam;
root@localhost [kris]> insert into crawler ( id, provider_id ) values ( NULL, 1 );</code>

然后重复

root@localhost [kris]> insert into crawler ( id, provider_id) 
  select NULL, rand() * 120000 from crawler;

直到我拥有

root@localhost [kris]> select count(*) from crawler;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (0.00 sec)

我现在有

root@localhost [kris]> SELECT COUNT(*) FROM `crawler` WHERE `crawler`.`provider_id` > 1371;
+----------+
| COUNT(*) |
+----------+
|   518389 |
+----------+
1 row in set (0.27 sec)

这在大小上与您在上面的示例中给出的大小相当。对于带有和不带有 LIMIT 子句的查询，我确实得到了两个不同的计划。

在没有 LIMIT 子句的情况下，我得到了不使用任何索引的全表扫描 (ALL):

root@localhost [kris]> explain SELECT `crawler`.`id` FROM `crawler` WHERE `crawler`.`provider_id` > 1371\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: crawler
         type: ALL
possible_keys: crawler_provider_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 524288
        Extra: Using where
1 row in set (0.00 sec)

使用 LIMIT 子句，INDEX 用于 RANGE 访问

root@localhost [kris]> explain SELECT `crawler`.`id` FROM `crawler` WHERE `crawler`.`provider_id` > 1371 LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: crawler
         type: range
possible_keys: crawler_provider_id
          key: crawler_provider_id
      key_len: 5
          ref: NULL
         rows: 518136
        Extra: Using where
1 row in set (0.00 sec)

在您的示例中，如果没有 LIMIT 子句(全表扫描)，您将得不到任何数据，但是使用 LIMIT 子句(使用索引进行范围访问)，您将获得数据。这指向损坏的 MYD 文件。

ALTER TABLE，作为REPAIR TABLE或OPTIMIZE TABLE，通常会将源表中的数据和保留的索引以新格式复制到隐藏的新版本表中。完成后，隐藏的新表将替换旧版本的表(将重命名为隐藏名称，然后删除)。

也就是说，通过删除索引，您有效地修复了表。