MySQL неправильно выбирает строки (иногда)
Это обновление к этому вопросу, в котором я размышлял, пытаясь понять, что на земле происходит:
MySQL иногда ошибочно возвращает 0 для подсчета(*)
В итоге я принял ответ там, потому что он ответил на вопрос, который я задал ("почему это может произойти"), хотя он не ответил на вопрос, о котором я действительно хотел знать ("почему это происходит со мной"). Но Мне удалось немного сузить круг вопросов по последний вопрос, и думаю, что я могу определенно сказать, что что-то не так, чего я не понимаю и никогда раньше не видел.
Проблему было действительно трудно отладить, потому что по причинам, выходящим за рамки моего понимания, вход в базу данных автоматически исправляет ее. Однако сегодня мне удалось вызвать проблемное состояние во время открытого сеанса 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)
Хорошо, но мне нужны только сообщения на форуме, для которых не установлен флаг deleted
:
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)
Для каждой строки в этой таблице значение "удалено" равно 0, и все же добавление and deleted=0
в запрос не дает результатов. Пока я не открою новый сеанс, снова войдя в MySQL из окна терминала, после чего я может еще раз правильно выбрать строки, где "удалено" равно 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)
2 answers
Основываясь на комментарии о том, что использование ПРИНУДИТЕЛЬНОГО КЛЮЧА изменяет результат запроса, весьма вероятно, что мы имеем дело с ошибкой оптимизатора слияния. ОБЪЯСНЕНИЕ исходного запроса показывает, что оптимизация выполняется путем выбора из удаленного ключа, затем из ключа post_id, а затем объединения результатов. Когда мы заставляем обойти этот код, проблема исчезает.
Шаги от точки:
- попробуйте использовать те же данные с самой последней версией MySQL 5.6
- если проблема воспроизводится, попробуйте изолировать ее до самого минимального тестового случая, посетите http://bugs.mysql.com / и сообщить об ошибке
Изгоните демонов и призраков! Добавьте этот индекс, чтобы избежать ошибки "слияния":
INDEX(deleted, thread_id) and DROP the key on just deleted
Индекс на флаге почти всегда бесполезен. На этот раз это было хуже, чем бесполезно.
Это будет дешевле, быстрее и безопаснее, чем ИНДЕКС СИЛЫ.