如果选择所有字段，则MySQL索引无法工作。

Question

我有张这样简单的桌子，

CREATE TABLE `domain` (
  `id` varchar(191) NOT NULL,
  `time` bigint(20) DEFAULT NULL,
  `task_id` bigint(20) DEFAULT NULL,
  `name` varchar(512) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `idx_domain_time` (`time`),
  KEY `idx_domain_task_id` (`task_id`),
  FULLTEXT KEY `idx_domain_name` (`name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

索引是这样的：

mysql> show index from domain;
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table  | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| domain |          0 | PRIMARY                |            1 | id          | A         |     2036092 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| domain |          1 | idx_domain_name        |            1 | name        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

仅选择id字段时使用索引：

mysql> explain SELECT id FROM `domain` WHERE task_id = '3';
+------+-------------+--------+------+--------------------+--------------------+---------+-------+---------+-------------+
| id   | select_type | table  | type | possible_keys      | key                | key_len | ref   | rows    | Extra       |
+------+-------------+--------+------+--------------------+--------------------+---------+-------+---------+-------------+
|    1 | SIMPLE      | domain | ref  | idx_domain_task_id | idx_domain_task_id | 9       | const | 1018046 | Using index |
+------+-------------+--------+------+--------------------+--------------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

当我选择所有字段时，它不起作用：

mysql> explain SELECT * FROM `domain` WHERE task_id = '3';
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
| id   | select_type | table  | type | possible_keys      | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | domain | ALL  | idx_domain_task_id | NULL | NULL    | NULL | 2036092 | Using where |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT id, name FROM `domain` WHERE task_id = '3';
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
| id   | select_type | table  | type | possible_keys      | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | domain | ALL  | idx_domain_task_id | NULL | NULL    | NULL | 2036092 | Using where |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

怎么了？

Answer 1

主键以外的索引是通过按索引顺序存储索引字段的数据以及主键来完成的。

因此，当您通过索引字段选择主键时，索引中有足够的信息来完全满足查询。添加其他字段时，索引中不再有足够的信息。这并不意味着数据库将不再使用该索引，但现在它不再是一个大扣篮，它更多地归结为表统计。

Answer 2

MySql优化器将尝试实现最佳性能，因此可能忽略索引。如果您确信优化器会提供更好的性能，则可以强制优化器使用所需的索引。您可以使用：

SELECT * FROM `domain` USE INDEX (idx_domain_task_id)  WHERE task_id = '3';

有关更多详细信息，请参见此页面索引提示。