Mysql - Explain键为空

Question

在我的表格电子邮件中

mysql> explain select * from Emails where email_id IN  (2029000006126024, 2029000004564319, 2029000000692079, 2029000004564202);
+----+-------------+---------------+------------+------+----------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys                          | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+------+----------------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | Emails        | NULL       | ALL  | Emails_FK1_IDX,Emails_IDX2             | NULL | NULL    | NULL | 785984 |    57.70 | Using where |
+----+-------------+---------------+------------+------+----------------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

并且已经为列"email_id“创建了索引，但正在为整个表运行查询。

和create table查询是

CREATE TABLE `Emails` (
`EMAIL_REPORT_ID` bigint(19) NOT NULL,
`CAMPAIGN_EMAIL_ID` bigint(19) NOT NULL,
`SENT_DATE` bigint(19) DEFAULT NULL,
`EMAIL_ID` bigint(19) NOT NULL,
`BOUNCE` int(10) DEFAULT '0',
`BOUNCED_DATE` bigint(19) DEFAULT NULL,
`SPAM` tinyint(1) DEFAULT '0',
`OPT_OUT` tinyint(1) DEFAULT '0',
`IGNORED` int(10) DEFAULT '0',
`CMP_CONTENT_ID` bigint(19) DEFAULT NULL,
`RESTRICT` int(10) DEFAULT '0',
`TIME_ZONE` varchar(100) DEFAULT NULL,
`GMT_DEVIATION` varchar(10) DEFAULT NULL,
`CMP_COMMENTS_COUNT` int(10) DEFAULT '0',
`IGNORED_BY_TRAP` int(10) DEFAULT '0',
PRIMARY KEY (`EMAIL_REPORT_ID`),
KEY `Emails_FK1_IDX` (`CAMPAIGN_EMAIL_ID`),
KEY `Emails_FK2_IDX` (`CONTACT_ID`),
KEY `Emails_FK3_IDX` (`RECIPIENT_SELECTION_ID`),
KEY `Emails_FK4_IDX` (`CMP_CONTENT_ID`),
KEY `Emails_IDX1` (`SENT_DATE`),
KEY `Emails_IDX2` (`CAMPAIGN_EMAIL_ID`),
KEY `Emails_IDX3` (`EMAIL_REPORT_ID`,`CAMPAIGN_EMAIL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

专家们，请告诉我如何解决这个问题。

Answer 1

如果MySQL估计您搜索的值可能与表的大部分内容相匹配，它将不使用该索引。

具体的阈值没有文档记录，但根据我的经验，当您的查询与表的大约20%匹配时，优化器可以从查询逻辑中推断出这一点，它认为进行表扫描并过滤掉它检查的行比使用索引更有效。

如果您不同意，可以使用FORCE INDEX hint告诉优化器表扫描的开销太大，如果有合适的索引，它应该更喜欢使用索引。