我可以强迫MySQL使用索引吗? [英] Can I force MySQL to use an index?
问题描述
我创建了一个包含近800,000条记录的表.
I created a table which has close to 800,000 records.
mysql> describe automation_search_test;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| filename | text | YES | MUL | NULL | |
| site | text | YES | MUL | NULL | |
| script | text | YES | MUL | NULL | |
| station | text | YES | MUL | NULL | |
| result | text | YES | MUL | NULL | |
| failcode | text | YES | MUL | NULL | |
| stbmodel | text | YES | MUL | NULL | |
| rid | text | YES | MUL | NULL | |
| testdate | text | YES | MUL | NULL | |
+----------+---------+------+-----+---------+----------------+
在filename列上具有索引
mysql> show index from automation_search_test;
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| automation_search_test | 0 | PRIMARY | 1 | id | A | 767825 | NULL | NULL | | BTREE | |
| automation_search_test | 1 | ast_fname_idx | 1 | filename | A | 767825 | 255 | NULL | YES | BTREE | |
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
在WHERE条件下，对filename列的查询使用索引不会出现问题.
With a WHERE condition, a query against the filename column uses the index without problem.
但是，针对filename列的简单SELECT查询将忽略索引
However, a simple SELECT query against the filename column ignores the index
mysql> explain select filename from automation_search_test;
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | automation_search_test | ALL | NULL | NULL | NULL | NULL | 767825 | |
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
如何强制使用此索引?
How can I force the use of this index??
推荐答案
听起来好像您正在寻找覆盖索引.覆盖索引(可以满足整个查询而不用到表的索引)只有在包含完整数据的情况下才起作用.在您的示例中，filename上的索引最多包含255个字符.如果实际文件名较长，则它将不包含全部数据，因此它不是该查询的覆盖索引.
It sounds as if you are looking for a covering index. A covering index (one that can satisfy the entire query without going to the table) only works if it contains the complete data. In your example, the index on filename includes up to 255 characters. If the actual file name were longer, it would not contain the entire data, so it is not a covering index for that query.
如果filename具有类似varchar(255)的类型，则它将在示例查询中使用索引.
If filename had a type such as varchar(255), then it would use the index for the example query.
这篇关于我可以强迫MySQL使用索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
