MySql拒绝使用索引 [英] MySql refuses to use index
问题描述
我是查询优化的新手，所以我接受我还不了解所有内容，但我不明白为什么即使这个简单的查询也没有按预期进行优化。
I'm new to query optimizations so I accept I don't understand everything yet but I do not understand why even this simple query isn't optimized as expected.
我的表：
+------------------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------+------+-----+-------------------+----------------+
| tasktransitionid | int(11) | NO | PRI | NULL | auto_increment |
| taskid | int(11) | NO | MUL | NULL | |
| transitiondate | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
+------------------+-----------+------+-----+-------------------+----------------+
我的指数：
+-----------------+------------+-------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tasktransitions | 0 | PRIMARY | 1 | tasktransitionid | A | 952 | NULL | NULL | | BTREE | | |
| tasktransitions | 1 | transitiondate_ix | 1 | transitiondate | A | 952 | NULL | NULL | | BTREE | | |
+-----------------+------------+-------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
我的查询：
SELECT taskid FROM tasktransitions WHERE transitiondate>'2013-09-31 00:00:00';
给出：
+----+-------------+-----------------+------+-------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+-------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tasktransitions | ALL | transitiondate_ix | NULL | NULL | NULL | 1082 | Using where |
+----+-------------+-----------------+------+-------------------+------+---------+------+------+-------------+
如果我理解正确的一切使用where 和 ALL 表示从存储引擎检索所有行并在服务器层进行过滤。这是次优的。为什么它拒绝使用索引并仅从存储引擎（innoDB）中检索请求的范围？
If I understand everything correctly Using where and ALL means that all rows are retrieved from the storage engine and filtered at server layer. This is sub-optimal. Why does it refuse to use the index and only retrieve the requested range from the storage engine (innoDB)?
干杯
推荐答案
如果估计MySQL不会使用索引它会选择表的很大一部分，它认为在这些情况下表扫描实际上更有效。
MySQL will not use the index if it estimates that it would select a significantly large portion of the table, and it thinks that a table-scan is actually more efficient in those cases.
通过类比，这就是原因一本书的索引不包含非常常见的单词，如the - 因为在索引中查找单词并查找页码列表是一个很长的列表，甚至每页都是浪费时间在书里。简单地阅读书籍封面会更有效率。
By analogy, this is the reason the index of a book doesn't contain very common words like "the" -- because it would be a waste of time to look up the word in the index and find the list of page numbers is a very long list, even every page in the book. It would be more efficient to simply read the book cover to cover.
我的经验是，如果查询的搜索条件匹配大于20％，那么这种情况会发生在MySQL中。表，这通常是正确的交叉点。可能会有一些基于数据类型，表大小等的变化。
My experience is that this happens in MySQL if a query's search criteria would match greater than 20% of the table, and this is usually the right crossover point. There could be some variation based on the data types, size of table, etc.
您可以给MySQL一个提示，以说服表扫描会令人望而却步昂贵，所以更有可能使用索引。这通常不是必需的，但您可以这样做：
You can give a hint to MySQL to convince it that a table-scan would be prohibitively expensive, so it would be much more likely to use the index. This is not usually necessary, but you can do it like this:
SELECT taskid FROM tasktransitions FORCE INDEX (transitiondate_ix)
WHERE transitiondate>'2013-09-31 00:00:00';
这篇关于MySql拒绝使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
