MySQL True 与 False 优化

Question

有人可以向我解释为什么我会看到以下行为吗:

mysql> show index from history_historyentry;
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| history_historyentry |          0 | PRIMARY                      |            1 | id          | A         |       48609 |     NULL | NULL   |      | BTREE      |         |  
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = False;
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | history_historyentry | ALL  | NULL          | NULL | NULL    | NULL | 48612 | Using where | 
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = True;
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | history_historyentry | ALL  | NULL          | NULL | NULL    | NULL | 48613 | Using where | 
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> create index deleted on history_historyentry (is_deleted) ;
Query OK, 48627 rows affected (0.38 sec)
Records: 48627  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = False;
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | history_historyentry | index | deleted       | deleted | 1       | NULL | 36471 | Using where; Using index | 
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = True;
+----+-------------+----------------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table                | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | history_historyentry | ref  | deleted       | deleted | 1       | const |  166 | Using index | 
+----+-------------+----------------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

为什么 True 与 False 的索引使用存在差异？具体来说，在false的情况下，ref列为NULL，extra列为Using where；使用索引。但在真实情况下，ref 列是 const，extra 列是 Using index。

Answer 1

大概是因为一个提供了良好的选择性而另一个没有，即只有一小部分行被删除。

基于成本的优化器只会使用索引，如果它提供良好的选择性(通常为 10%)或者如果它是覆盖索引(无需进一步的表或书签查找就可以满足查询)。