in (select ... 在 MariaDB 上性能慢于 MySQL 的问题及解决

之前帮某人写了个非常简单的物品查询数据库，由于数据表的问题使用了三层 in (select ... 嵌套。在自己的测试环境 (Ubuntu 16.04 LTS / Windows 10, MySQL Community 5.7) 下加了索引之后没有任何性能问题，执行 SQL 语句 0.1s 内返回结果。
然而丢给某人之后跑不起来了，查询语句占用 CPU 100%，用时 20 秒才返回结果。

那边的环境是阿里云（比我的渣渣 Linode 好得多）Windows 环境，于是开始排查问题。

1. MySQL 版本问题。
   查询发现那边用的 5.0.51a 版本（貌似是 10 年前了），要求更新。更新之后还是不行，发现更新到了 MariaDB 10.1.25。搜索得知发现是 MySQL 的一个 GPL 分支。暂时失去线索。
2. 设置问题。
   查看设置后发现那边的配置甚至比我还好。于是把目光放到 InnoDB 引擎上，结果还是不行。
3. 索引问题。
   尝试重新导入数据表、从我的备份环境里导出数据表再导过去、手动建立索引，均无效。最后发现导出的数据表已经自带索引了……
4. 慢查询 log 及 explain
   只发现 explain 后我的测试环境和那边的环境结果不一样，剩下的就看不懂了（死）

由于某人不愿装 MySQL 5.7，只能从第四点实现方式有差异上入手。最后在 stackoverflow 上找到了这一条结果 ：

IN (SELECT …) optimized very poorly in 5.5 and before.

查询 wikipedia 发现 MariaDB 似乎并没有跟随 MySQL 走到 5.7 版本？于是尝试重写查询语句。
原查询语句：

select count(*) from mobs where mobNo in
(select monsterid from monsterdrops where itemid in
(select itemNo from items where itemLv >= 0 and itemName like '% 弓 %'));

其中 itemLv 和 itemName 条件是自定义输入。核心思想是从 item 表中读出查询物品的 id，在 monsterdrops 表中读出物品 id 对应的怪，最后从 mobs 表中读出怪物名称等内容。
既然无法使用 in (select 语句，搜索后打算使用 inner join 语句重写。
最后重写如下：

select mobName from mobs m
inner join (select distinct p.monsterid from monsterdrops p 
inner join (select itemNo from items where itemLv >= 0 and itemName like '% 弓 %') i
on p.itemid = i.itemNo) r
on m.mobNo = r.monsterid
group by Id;

要点：

1. 由于一个怪会有多个掉落，需要去重
2. 查找出来的结果不是正序，需要按照 Id 从小到大的顺序重新排序。

重写后在 MariaDB 上同样是不到 0.1s 返回结果，问题解决。
另外，由于分页需要使用 count(*) 查询数量，此时需要去掉 group by Id 语句，否则会返回无数行 1 结果，而不是 count(*)。

最后，由于发现国内未备案的话域名解析会被阿里云劫持到备案页面导致无法正常用域名访问，某人把数据库搬到 AWS 上去了并装了最新版的 MySQL。
无 fuck 说.jpg