选择外键时在mysql SELECT中找不到索引

Question

如果我在我的表中选择了一个非外键的列，我会找到索引：

mysql> explain SELECT id FROM stats_image_optimization_hourly WHERE stats_image_optimization_hourly.record_status=1;

+----+-------------+---------------------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
| id | select_type | table                           | type  | possible_keys | key                       | key_len | ref  | rows    | Extra                    |
+----+-------------+---------------------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | stats_image_optimization_hourly | index | NULL          | image_time_record_status3 | 9       | NULL | 1824413 | Using where; Using index |
+----+-------------+---------------------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

另一方面，如果选择作为外键的列，则找不到索引：

mysql> explain SELECT server_id FROM stats_image_optimization_hourly WHERE stats_image_optimization_hourly.record_status=1;

+----+-------------+---------------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table                           | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | stats_image_optimization_hourly | ALL  | NULL          | NULL | NULL    | NULL | 1824413 | Using where |
+----+-------------+---------------------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

下面是表的定义：

stats_image_optimization_hourly | CREATE TABLE `stats_image_optimization_hourly` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `time_stamp` datetime NOT NULL,
  `site_id` int(11) NOT NULL,
  `server_id` int(11) NOT NULL,
  `device_class_id` int(11) NOT NULL,
  `congestion_level_id` int(11) NOT NULL,
  `network_type_id` int(11) NOT NULL,
  `image_format_id` int(11) NOT NULL,
  `hits` bigint(20) unsigned NOT NULL DEFAULT '1',
  `in_bytes` bigint(20) unsigned NOT NULL DEFAULT '0',
  `out_bytes` bigint(20) unsigned NOT NULL DEFAULT '0',
  `opt_pct` decimal(11,2) NOT NULL DEFAULT '0.00',
  `record_status` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `time_stamp` (`time_stamp`,`site_id`,`server_id`,`device_class_id`,`congestion_level_id`,`network_type_id`,`image_format_id`),
  KEY `fk_image_device_class_id3` (`device_class_id`),
  KEY `fk_image_congestion_level_id3` (`congestion_level_id`),
  KEY `fk_image_network_type_id3` (`network_type_id`),
  KEY `fk_image_site_id3` (`site_id`),
  KEY `fk_image_server_id3` (`server_id`),
  KEY `fk_image_format3` (`image_format_id`),
  KEY `image_time_record_status3` (`time_stamp`,`record_status`),
  CONSTRAINT `fk_image_congestion_level_id3` FOREIGN KEY (`congestion_level_id`) REFERENCES `congestion_level` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_device_class_id3` FOREIGN KEY (`device_class_id`) REFERENCES `device_class` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_format3` FOREIGN KEY (`image_format_id`) REFERENCES `image_format` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_network_type_id3` FOREIGN KEY (`network_type_id`) REFERENCES `network_type` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_server_id3` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_site_id3` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2479561 DEFAULT CHARSET=latin1 |

有谁能解释一下原因吗？

Answer 1

这似乎与索引选择性有关。您在tinyint列上有一个索引，与记录数量相比，该列的不同值很少。除非索引覆盖了一个查询(就像第一个查询一样-它是innodb表，并且主键列隐式地包含在辅助索引中)，优化器(基于统计数据)决定全表扫描比索引范围扫描+查找更便宜。

Answer 2

id是一个主键，因此是InnoDB表中每个索引的一部分。

当您运行第一个查询时，所需的所有数据(过滤和选定字段)都包含在索引本身中，因此只需扫描索引就足以运行查询。

从explain输出中的Using where; Using index可以看出：using index部分表示这是仅限索引的扫描。

在第二个查询中，server_id不是record_status上的索引的一部分。这意味着，如果MySQL选择了索引访问方法，则必须在嵌套循环中查找每个索引记录的相应表记录，以检索server_id的值。

这是一个相当昂贵的操作，而且您的大多数记录都有record_status = 1 (MySQL知道这一点，因为它保留索引键分布的直方图)。

因此，优化器选择仅执行表扫描，因为所有数据都在表中，而且大多数表记录无论如何都必须检索，但效率较低。