mysql - 我可以强制 MySQL 使用索引吗？

Question

我创建了一个包含近 800,000 条记录的表。

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 列的查询可以毫无问题地使用索引。

但是，针对 filename 列的简单 SELECT 查询会忽略索引

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 |       | 
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+

我怎样才能强制使用这个索引？？

Answer 1

由于没有 WHERE 条件的 SELECT 返回该列中的所有值，因此索引无事可做。

这有点像问为什么我想看整本书却不用教科书后面的索引。