MYSQL - НЕ vs var = false

В последние дни я заметил что-то странное, оптимизирующее мой запрос. У меня есть простой запрос, который делает что-то вроде:

   SELECT id,name,amount FROM reservations WHERE NOT canceled ORDER BY name ASC

Я заметил, что mysql не использовал какой-либо индекс, поэтому я начал делать некоторые эксперименты. Случайно я заменил "NOT cancel" на "cancel = false", а затем Mysql начал использовать "отмененный" как индекс. После этого я попытался использовать противоположное:

   SELECT ... FROM reservations WHERE canceled ORDER BY ...

Тот же результат! Когда я меняю это на "cancel = true", индекс снова работает.

Мой вопрос: КАК ПРИХОДИТ?! Не использует "НЕ" "элегантный" способ? Во всяком случае, я не ожидал, что это изменит ситуацию.

Я использую InnoDB как движок, но получаю тот же результат с помощью MyISAM. Может ли кто-нибудь прояснить ситуацию? Спасибо.

Изменить: структура таблицы

CREATE TABLE `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trip_code` varchar(10) DEFAULT NULL,
  `departure_date` date DEFAULT NULL,
  `amount` float DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `canceled` tinyint(1) NOT NULL DEFAULT '0',
  `created_date` date NOT NULL,
  `creator_user` int(11) NOT NULL DEFAULT '1',
  `last_update_user` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `trip_code` (`trip_code`),
  KEY `departure_date` (`departure_date`),
  KEY `created_date` (`created_date`),
  KEY `canceled` (`canceled`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=123181 ;

Ответы

Ответ 1

Я не знаком с MYSQL, но, рассуждая логически, я понимаю это так:
Индекс похож на телефонную книгу, когда вы ищете "Коэн" , вы можете получить ее сразу.
Но если вы ищете НЕ "Коэн" , вам придется пропустить каждую запись и проверить, отличается ли она от "Коэн" .
Поэтому, когда вы ищете конкретное значение, он выглядит именно так. И когда вы используете НЕ, он ищет любое другое значение, которое может вписываться внутрь tinyint(1) (поскольку я понимаю это не только 1 или 0, не так ли?).

Ответ 2

Даже если он использует индекс, индекс (верьте или нет) может сделать ваш запрос медленнее. Это немного странно, но это связано с избирательностью индекса. Он обычно представлен в столбцах типа boolean.

Он описывается как:

"Как разные значения поля: это число от 0 до 1, хотя вы также можете думать об этом как о проценте. Значение 1 или 100%, означает, что каждое значение в поле уникально"

Важно учитывать, что:

"MySQL имеет оптимизатор с затратами. Это означает, что MySQL вычисляет затраты на различные способы выполнения запроса, а затем выбирает дешевый один. Ну, расчет затрат - это неточная наука. Таким образом, оценка берется, и иногда оценка ошибочна".

Обычный простой:

Если данные, которые вы ищете, имеют более или менее 20% от того же значения (например, отменено 40% вашей таблицы), то просто просто выполнить сканирование таблицы.

EDIT:

Что касается вашего вопроса, EXPLAIN сообщает вам, что MySQL использует индекс. Но это может быть не очень хорошо, единственный способ отметить, лучше ли ваша оптимизация - проверить производительность. Кроме того, рассмотрите стоимость операций INSERT, UPDATE и DELETE, чтобы сохранить этот индекс. Профилируйте с индексом и без него.

Взгляните на это:

Ответ 3

SELECT *
FROM 
(SELECT 1 AS C, 0 AS X UNION ALL
SELECT 2 AS C, 1 AS X UNION ALL
SELECT 3 AS C, 2 AS X ) T
WHERE X=true

Возвращает

'2', '1'

SELECT *
FROM 
(SELECT 1 AS C, 0 AS X UNION ALL
SELECT 2 AS C, 1 AS X UNION ALL
SELECT 3 AS C, 2 AS X ) T
WHERE X

Возвращает

'2', '1'
'3', '2'

Итак, кажется, что в первом случае true получает значение int, а затем используется в поисковом предикате, тогда как во втором случае значение столбца неявно выполняется. Неявные отливки обычно делают условие несоизмеримым.

Глядя на план объяснения вашего запроса с помощью WHERE canceled = true, вы получите

+----+-------------+--------------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type |    table     | type | possible_keys |   key    | key_len |  ref  | rows |            Extra            |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | reservations | ref  | canceled      | canceled |       1 | const |    1 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-----------------------------+

Если для WHERE canceled вы получаете

+----+-------------+--------------+------+---------------+-----+---------+-----+------+-----------------------------+
| id | select_type |    table     | type | possible_keys | key | key_len | ref | rows |            Extra            |
+----+-------------+--------------+------+---------------+-----+---------+-----+------+-----------------------------+
|  1 | SIMPLE      | reservations | ALL  |               |     |         |     |    2 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+-----+---------+-----+------+-----------------------------+

Таким образом, кажется, что он не может даже рассматривать индекс на canceled как возможный вариант в этом случае.