不同的数值怎么会让相同的查询使用不同的索引？

Question

mysql> explain select * from thefts where user_id=191 and is_transfer_free =1;
+----+-------------+--------+------+---------------------------------------------------------------------------------+------------------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys                                                                   | key                                | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------------------------------------------------------------+------------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | thefts | ref  | index_thefts_on_user_id_and_status,index_thefts_on_user_id_and_is_transfer_free | index_thefts_on_user_id_and_status | 5       | const |    1 | Using where |
+----+-------------+--------+------+---------------------------------------------------------------------------------+------------------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)mysql> explain select * from thefts where user_id=190 and is_transfer_free =1;
+----+-------------+--------+------+---------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys                                                                   | key                                          | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+---------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | thefts | ref  | index_thefts_on_user_id_and_status,index_thefts_on_user_id_and_is_transfer_free | index_thefts_on_user_id_and_is_transfer_free | 10      | const,const |   10 | Using where |
+----+-------------+--------+------+---------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
用的是mysql innodb

Answer 1

如果在两个字段上建立的索引：
select * from (
select * from thefts where user_id=191) a
where is_transfer_free =1

Answer 2

这样会更快吗？
mysql> explain select * from (select * from thefts where user_id=191) a where is_transfer_free=1;
+----+-------------+------------+--------+---------------------------------------------------------------------------------+------------------------------------+---------+------+------+-------------+
| id | select_type | table      | type   | possible_keys                                                                   | key                                | key_len | ref  | rows | Extra       |
+----+-------------+------------+--------+---------------------------------------------------------------------------------+------------------------------------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL                                                                            | NULL                               | NULL    | NULL |    1 |             |
|  2 | DERIVED     | thefts     | ref    | index_thefts_on_user_id_and_status,index_thefts_on_user_id_and_is_transfer_free | index_thefts_on_user_id_and_status | 5       |      |    1 | Using where |
+----+-------------+------------+--------+---------------------------------------------------------------------------------+------------------------------------+---------+------+------+-------------+
2 rows in set (0.06 sec)

Answer 3

MySQL优化器自身选择的索引是非常快的，所以不建议强制索引使用！

Answer 4

但是这个查询应该是index_thefts_on_user_id_and_is_transfer_free这个索引比较快，为什么有时候它不用呢？

Answer 5

数据库的索引算法不一定就是没有bug的，我的那个表，相同的查询下，数据库如果用索引，是数据库不用索引的几百倍（保守的说，因为我从来都没有等到它不用索引，查询完的时候过），但是数据库依然会不定时的不用索引查询，这个我看了它的执行计划的