事情就是这样.等我一分钟...

That's the way things go. Bear with me a minute...

优化器希望使用 INDEX，在本例中为 ACTI_DATE_I.但它不想使用它，如果那样会更慢.

The Optimizer would like to use an INDEX, in this case ACTI_DATE_I. But it does not want to use it if that would be slower.

方案 A:使用索引.

1. 最后到达 BTree 结构的索引(因为 DESC)
2. 向后扫描
3. 对于索引中的每一行，在数据中查找对应的行.注意:索引有 (ACTIVITY_DATE, ACTIVITY_ID) 因为 PRIMARY KEY 隐式附加到任何辅助键.使用 PK (ACTIVITY_ID) 访问数据"是另一个 BTree 查找，可能是随机的.因此，它可能很慢.(但在你的情况下不是很慢.)
4. 这在 LIMIT 行之后停止.

计划 B:忽略表格

1. 扫描表，构建一个 tmp 表.(可能在内存中.)
2. 对tmp表进行排序
3. 剥离 LIMIT 行.

在您的情况下(96 - 10K 的 1%)，它选择了表扫描是令人惊讶的.通常，截止值大约是表中行数的 10%-30%.

In your case (96 -- 1% of 10K) it is surprising that it picked the table scan. Normally, the cutoff is somewhere around 10%-30% of the number of rows in the table.

ANALYZE TABLE 应该重新计算统计数据，这可以说服它与另一个计划一起使用.

ANALYZE TABLE should have caused a recalculation of the statistics, which could have convinced it to go with the other Plan.

您使用的是什么版本的 MySQL?(不，我不知道这方面有任何变化.)

What version of MySQL are you using? (No, I don't know of any changes in this area.)

您可以尝试一件事:OPTIMIZE TABLE ACTIVITIES;这将重建表，从而重新打包块并导致可能不同的统计数据.如果这有帮助，我想知道——因为我通常说优化表没用".

One thing you could try: OPTIMIZE TABLE ACTIVITIES; That will rebuild the table, thereby repacking the blocks and leading to potentially different statistics. If that helps, I would like to know it -- since I normally say "Optimize table is useless".