这是我的表格：
CREATE TABLE `idx_weight` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`SECURITY_ID` bigint(20) NOT NULL COMMENT,
`CONS_ID` bigint(20) NOT NULL,
`EFF_DATE` date NOT NULL,
`WEIGHT` decimal(9,6) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `BPK_AK` (`SECURITY_ID`,`CONS_ID`,`EFF_DATE`),
KEY `idx_weight_ix` (`SECURITY_ID`,`EFF_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=75334536 DEFAULT CHARSET=utf8
对于查询 1：
explain select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight where security_id = 1782：
+----+-------------+------------+------+----------------------+---------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+----------------------+---------------+---------+-------+--------+-------------+
| 1 | SIMPLE | idx_weight | ref | BPK_AK,idx_weight_ix | idx_weight_ix | 8 | const | 887856 | Using index |
+----+-------------+------------+------+----------------------+---------------+---------+-------+--------+-------------+
此查询运行正常。
现在查询 2(唯一更改的是security_id参数(：
explain select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight where security_id = 26622：
+----+-------------+------------+------+----------------------+--------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+----------------------+--------+---------+-------+----------+-------------+
| 1 | SIMPLE | idx_weight | ref | BPK_AK,idx_weight_ix | BPK_AK | 8 | const | 10700002 | Using index |
+----+-------------+------------+------+----------------------+--------+---------+-------+----------+-------------+
请注意，它选取索引BPK_AK，并且实际查询运行超过 1 分钟。
这是不正确的。第二次花了10秒多。我猜索引第一次不在缓冲池中。
我可以通过附加group by security_id来获得解决方法：
explain select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight where security_id = 26622 group by security_id：
+----+-------------+------------+-------+----------------------+---------------+---------+------+-------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+----------------------+---------------+---------+------+-------+---------------------------------------+
| 1 | SIMPLE | idx_weight | range | BPK_AK,idx_weight_ix | idx_weight_ix | 8 | NULL | 10314 | Using where; Using index for group-by |
+----+-------------+------------+-------+----------------------+---------------+---------+------+-------+---------------------------------------+
但我仍然不明白为什么 mysql 不会为某些security_id选择idx_weight_ix，这是此查询的覆盖索引(而且便宜得多(。知道吗？
====
==================================================================================更新： @oysteing 学会了新花样，很酷！:)
下面是优化程序跟踪：
查询 1：https://gist.github.com/aping/c4388d49d666c43172a856d77001f4ce
查询 2：https://gist.github.com/aping/1af5504b428ca136a8b1c41c40d763e4
还有一些可能有用的额外信息：
从INFORMATION_SCHEMA.STATISTICS：
+------------+---------------+--------------+-------------+-------------+
| NON_UNIQUE | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | CARDINALITY |
+------------+---------------+--------------+-------------+-------------+
| 0 | BPK_AK | 1 | SECURITY_ID | 74134 |
| 0 | BPK_AK | 2 | CONS_ID | 638381 |
| 0 | BPK_AK | 3 | EFF_DATE | 68945218 |
| 1 | idx_weight_ix | 1 | SECURITY_ID | 61393 |
| 1 | idx_weight_ix | 2 | EFF_DATE | 238564 |
+------------+---------------+--------------+-------------+-------------+
CARDINALITYSECURITY_ID是不同的，但从技术上讲，它们应该是完全相同的，对吗？
由此： https://dba.stackexchange.com/questions/49656/find-the-size-of-each-index-in-a-mysql-table?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
+---------------+-------------------+
| index_name | indexentry_length |
+---------------+-------------------+
| BPK_AK | 1376940279 |
| idx_weight_ix | 797175951 |
+---------------+-------------------+
索引大小约为 800MB 与 1.3GB。
运行select count(*) from idx_weight where security_id = 1782返回509994
和select count(*) from idx_weight where security_id = 26622回报5828054
然后强制对查询 1 使用BPK_AK：
select SQL_NO_CACHE SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight use index (BPK_AK) where security_id = 1782花了0.2秒。
所以基本上，26622的行数是1782的 10 倍，但使用相同的索引，花费的时间是 50 倍。
PS：缓冲池大小为25GB。
优化器跟踪显示，索引选择差异的原因是由于从 InnoDB 收到的估计值。 对于每个潜在索引，优化程序会要求存储引擎估计该范围内的记录数。 对于第一个查询，它获得以下估计值：
BPK_AK: 1031808
idx_weight_ix: 887856
因此，对于idx_weight_ix，估计的读取成本最低，因此选择此索引。对于第二个查询，估计值为：
BPK_AK: 11092112
idx_weight_ix: 12003098
并且由于行数较少，BPK_AK的估计读取成本最低。 你可以说MySQL应该知道在这两种情况下，范围内的实际行数是相同的，但该逻辑尚未实现。
我不知道InnoDB如何计算此估计值的细节，但它基本上进行了两次"索引潜水"以找到范围内的第一行和最后一行，然后以某种方式计算两者之间的"距离"。 可能是估计值受到索引页中未使用空间的影响，OPTIMIZE TABLE 可以解决此问题，但在如此大的表上运行 OPTIMIZE TABLE 可能需要很长时间。
解决此问题的最快方法是添加一个 GROUP BY 子句，正如这里其他一些人提到的。 然后 MySQL 只需要每组读取 2 行;对于 security_id 的每个值，第一个和最后一个 SINCE 索引按 EFF_DATE 排序。 或者，您可以使用 FORCE INDEX 来强制使用特定索引。
也可能是MySQL 8.0将更好地处理此查询。 成本模型发生了一些变化，它将对未缓存在缓冲池中的"冷"索引施加更高的成本。
当你混合普通列(SECURITY_ID(和聚合函数(在你的例子中是min和max(时，你应该使用GROUP BY。如果你不这样做，MySQL是免费的，给出任何它喜欢的结果。使用GROUP BY，您将获得正确的结果。默认情况下，较新的 MySQL 数据库强制使用此行为。
省略 GROUP BY 时未选择第二个索引的原因很可能是因为聚合函数不限于同一组 (=security_id( abd 因此不能用作限制器。
我可以通过按security_id附加组来获得解决方法
是的。我不会以任何其他方式这样做，因为当您使用聚合函数时，您需要按某些内容进行分组。我甚至不知道MySQL允许你解决它。
我认为@slaakso是对的。给他点赞。