mysql 查询结果为空时 ID倒序索引导致性能极低

Question

首先这个表比较奇葩，字段很多，130个
查询的时候做了字段过滤，但是效果基本没什么差别查询条件做过索引，分别是shop_id和type这两个。另外因为大量删除旧数据 id已经用到4亿多了，但是数据量800w到1200w之间，定时删+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wifi_wa_data |          0 | PRIMARY  |            1 | ID          | A         |    10707454 | NULL     | NULL   |      | BTREE      |         |               |
| wifi_wa_data |          1 | add_time |            1 | add_time    | A         |      823650 | NULL     | NULL   | YES  | BTREE      |         |               |
| wifi_wa_data |          1 | type     |            1 | type        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| wifi_wa_data |          1 | shop_id  |            1 | shop_id     | A         |          25 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
type的类别很少，shop_id的值也不多查询基本上就是SELECT XXX FROM `wifi_wa_data` WHERE shop_id=5 and type = 4 ORDER BY `ID` DESC LIMIT 0, 10;
这么个简单的查询。查询字段十来个本地数据库测试的时候并没有发现问题，数据量200W，查询都是毫秒级
然而线上数据库发现查询结果为空时sending data卡住很长时间。
去掉ORDER则大幅好转，200毫秒左右。不去掉ORDER，只要有命中数据的也都很快顶多几十毫秒iD倒序，结果为空时则会卡住数十秒甚至几分钟。并且insert会Waiting for table level lock将查询字段全删掉，只查ID的现象也是一样……myisam引擎……

Answer 1

补充一点信息我后来explain了一下发现我本地实验的服务器和出问题的结果不一样
本地
explain select * from wifi_wa_data where shop_id=5 and type =4 order by ID desc limit 10;;
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table        | type | possible_keys | key     | key_len | ref   | rows | Extra                       |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | wifi_wa_data | ref  | type,shop_id  | shop_id | 5       | const |    1 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-----------------------------+
问题服务器
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | wifi_wa_data | index | type          | PRIMARY | 4       | NULL |   10 | Using where |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
问题服务器使用的索引扫描 没有使用type shop_id索引貌似两个库结构完全一样，区别只是后者的表存数据量更大

Answer 2

问题关键在 Using filesort, mysql 8.0 以前只支持顺序索引，order by   desc 会将索引从头扫到尾，等同于全表扫描。说到解决方案，不才有个大胆的想法。 使用一个大数，减去数据创建时候的时间戳，      以此字段建立索引，   用于排倒序的数据。 id排正序数据。