解决 NDVI 按年查找最大值问题

解决 NDVI 按年查找最大值问题

表：ID | 矿ID | 日期 | 年 | 值；总数据量 80 万，不算多，加上子查询不超过一秒

问题：直接使用 Group By 不能得到最大值对应的正确的日期，因为 Group By 输出的其他字段都是第一条，并不随 Max 函数字段变化。

尝试解决：

1. 首先查找资料发现可以先做一个按（年，值）排序的子查询，再进行 Group By 就可以得到最大值对应的所有字段。但是奇怪的是没有达到预期的效果，返回的日期和最大值还是没有对应上；
2. 继续查找资料发现是因为 MySQL 将会将子查询优化，并不会进行排序，Explain 发现并没有为子查询创建一个派生表；可以通过给子查询加上一个冗余的 limit 子句防止此优化，Explain 发现加上 limit 子句之后会为子查询建立一个派生表，并且查询达到预期效果；
3. 事情到此应该就结束了，但是有两个问题一个是 ①加上 limit 子句导致SQL语义不明，容易让人不知道这是啥意思 ②排序比较耗时（虽然这个表也才80万条数据），还可以使用索引进一步优化查询速度。
4. 然后就开始测试，先构建一个联合索引（矿区ID、年份、值 降序），通过 Explain 发现得到的 NDVI 值有 null，不是 null 的也都很小，这不就是最小值吗？我又使用 show create table 检查了一遍表里的的索引，发现新建的NDVI值的字段并不是降序，猜测是不是MySQL5.7 版本问题并在网上验证了自己的想法，降序索引在 MySQL 8 后才引入。
5. 后面在自己电脑上的 MySQL 8 上继续实验，实验过程创建索引的时候发现 MyISAM 是不支持降序索引的，直接报错，将表改为 InnoDB 引擎，建立（矿区ID升序、年份升序、NDVI值降序）的联合索引，然后直接使用 Group By 进行查询得到最大值及其对应的时间。
6. 最后故事尾声是上面查询使用索引是优化器选择的，语义还是不够明确，容易收到其他索引影响，于是在查询语句加上 force index 强制使用我们建立的为查询某矿区各年最大值的索引。后面在创建一个查询最小值的索引，验证 force index 是否成功，发现成功得到最小值。几乎完美。

遗留问题以及想法：

一个遗留问题，我们的 NDVI 值可能为 null ，查询最小值的时候就是把 null 当成了最小值，这样显然不正确，这个问题可以通过缺失数据处理使用插值算法将缺失值补上等等。
一些想法，如果我们一开始建表的时候同时加上一个触发器，在每次插入、删除、更新新数据的时候维护一个最大值表，这样也可以解决我们的问题，不过其实我们的表也才80万行，维护一个触发器并保证其正确运行也需要投入时间精力，这也是需要考虑到的问题。
子查询被优化，而不会进行排序，这个问题也可以通过 手动建立临时表 的方式得到一张排好序的表，然后使用 Group By 查询就可以查询到最大值。但是又会有临时表污染表命名空间、临时表需要删除的问题，临时表也没有解决查询速度的问题。