MYSQL - NOT vs var=false

Question

在过去的几天里，我注意到优化查询时出现了一些奇怪的情况。我有一个简单的查询，它的作用如下：

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

我注意到mysql没有使用任何索引，所以我开始做一些实验。不小心，我把"NOT cancelled"替换成了"cancelled=false"，然后，Mysql开始使用"cancelld"作为索引。之后，我尝试使用相反的方法：

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

同样的结果！当我将其更改为"cancelled=true"时，索引将再次工作。

我的问题是：怎么会这样？！使用"NOT"不是"优雅"的方式吗？无论如何，我没想到它会有什么不同。

我使用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 ;

Answer 1

即使使用索引，该索引(信不信由你(也可能会使查询速度变慢。这有点奇怪，但它与指数选择性有关。它通常以布尔类型的列表示。

描述如下：

"一个字段的值有多不同。它是一个从0-1开始的数字，尽管你也可以把它看作一个百分比。值为1，或100%，意味着字段中的每个值都是唯一的">

重要的是要考虑使用：

"MySQL有一个基于成本的优化器。这意味着MySQL计算执行查询的不同方式的成本，然后选择最便宜的。嗯，计算成本是一门不精确的科学。所以估计是有根据的，有时估计是错误的。">

简单明了：

如果你正在查找的数据或多或少有20%的相同值(例如，cancelled占表的40%(，那么只需扫描表就很简单了。

编辑：

关于您的问题，EXPLAIN告诉您MySQL正在使用索引。但是，这可能不太好，要注意优化是否更好，唯一的方法就是测试性能。此外，还要考虑INSERT、UPDATE和DELETE操作保持该索引的成本。使用和不使用索引进行一些分析。

看看这个：

• http://sheeri.com/archives/77
• http://www.mysqlperformanceblog.com/2006/06/02/indexes-in-mysql/

Answer 2

我不熟悉MYSQL，但从逻辑上讲，我是这样理解的：
索引就像一本电话簿，当你搜索"科恩"时，你可以马上得到它
但是，如果您要查找NOT"Cohen"，则必须仔细查看每个条目，并检查它是否与"Cohen"不同
因此，当你在寻找特定值时，它只会寻找它。当你使用NOT，它会寻找任何其他适合tinyint(1)的值(据我所知，它不仅仅是1或0，是吗？(。

Answer 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上的索引视为可能的选项。