MySQL sometimes erroneously returns 0 for count(*)

我最后接受了答案,因为它确实回答了我提出的问题(“为什么会发生这种情况”),即使它没有回答我真正想知道的问题(“为什么这会发生在我身上”).但是我已经设法在后一个问题上稍微缩小了一点,并且认为我可以用一种我不理解和以前从未见过的方式明确地说某些事情是错误的.

这个问题一直很难调试,因为由于我无法理解的原因,登录到数据库会自动修复它.但是,今天我设法在终端中打开MysqL会话时触发有问题的状态.以下是一些查询以及随后从该会话中获得的响应：

首先,这是我的表格布局：

MysqL> describe forum_posts;                                                    
+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| post_id   | int(11)    | NO   | PRI | NULL    | auto_increment |
| thread_id | int(11)    | YES  | MUL | NULL    |                |
| forum_id  | int(11)    | YES  | MUL | NULL    |                |
| user_id   | int(11)    | YES  | MUL | NULL    |                |
| moderator | tinyint(1) | NO   |     | 0       |                |
| message   | mediumtext | YES  | MUL | NULL    |                |
| date      | int(11)    | NO   | MUL | NULL    |                |
| edited    | int(11)    | YES  |     | NULL    |                |
| deleted   | tinyint(1) | YES  | MUL | 0       |                |
| bbcode    | tinyint(1) | NO   |     | 1       |                |
+-----------+------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

现在,让我们看看给定论坛帖子中有多少帖子：

MysqL> SELECT count(post_id) as num FROM `forum_posts` where thread_id=5243;
+-----+
| num |
+-----+
| 195 |
+-----+
1 row in set (0.00 sec)

好的,但我只想要没有设置已删除标志的论坛帖子：

MysqL> SELECT count(post_id) as num FROM `forum_posts` where thread_id=5243 and deleted=0;
+-----+
| num |
+-----+
|   0 |
+-----+
1 row in set (0.06 sec)

MysqL> select post_id,deleted from forum_posts where thread_id=5243 and deleted=0;
Empty set (0.06 sec)

好的,我们只需要双重确认它们实际上并未全部删除：

MysqL> select post_id,deleted from forum_posts where thread_id=5243;
+---------+---------+
| post_id | deleted |
+---------+---------+
|  104081 |       0 |
|  104082 |       0 |

[snip]

|  121162 |       0 |
|  121594 |       0 |
+---------+---------+
195 rows in set (0.00 sec)

该表中的每一行都将’deleted’设置为0,但在查询中添加和删除= 0不会产生任何结果.直到我通过从终端窗口再次登录MysqL打开一个新会话,之后我可以再次正确选择’deleted’为0的行.

到底怎么回事？

更新：

@ miken32在下面的评论中建议我尝试一下EXPLAIN SELECT …,所以：

MysqL> explain select post_id,deleted from forum_posts where thread_id='5243' and deleted=0;
+----+-------------+-------------+-------------+-------------------+-------------------+---------+------+------+--------------------------------------------------------------+
| id | select_type | table       | type        | possible_keys     | key               | key_len | ref  | rows | Extra                                                        |
+----+-------------+-------------+-------------+-------------------+-------------------+---------+------+------+--------------------------------------------------------------+
|  1 | SIMPLE      | forum_posts | index_merge | thread_id,deleted | thread_id,deleted | 5,2     | NULL |   97 | Using intersect(thread_id,deleted); Using where; Using index |
+----+-------------+-------------+-------------+-------------------+-------------------+---------+------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)