where子句上的mysql布尔值

Question

我想知道哪个更快？

SELECT * FROM `table` WHERE `is_deleted` = false;

或

SELECT * FROM `table` WHERE NOT `is_deleted`

感谢

Answer 1

架构

create table t123
(
    id int auto_increment primary key,
    x boolean not null,
    key(x)
);
truncate table t123;
insert t123(x) values (false),(true),(false),(true),(false),(true),(false),(true),(false),(true),(false),(true);
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
select count(*) as rowCount from t123;
+----------+
| rowCount |
+----------+
|  3145728 |
+----------+

我们现在有310万行。

A

explain SELECT * FROM t123 WHERE x=false;
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
|  1 | SIMPLE      | t123  | ref  | x             | x    | 1       | const | 1570707 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

B

explain SELECT * FROM t123 WHERE NOT `x`;
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | t123  | index | NULL          | x    | 1       | NULL | 3141414 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+

因此，A速度更快，因为它能够使用本机数据类型（如索引中所示），并且由于B处理数据转换的方式（确实导致表扫描），因此不会强制进行表扫描

它的证明在explain输出中，需要rows的数量来确定答案，并且即使在两个查询的列上也没有使用索引（ref列）。

解释语法的Mysql手册页。

Answer 2

从table中选择*而非is_deleted

此查询将为您提供更快、更合适的结果。

因为在Mysql中，布尔数据类型最好使用Not运算符。