MySQL 中缺少降序索引功能的解决方法

我们的生产环境存在问题，该环境使用支持优先级的 jBPM 修改版本。目前的指数有：

| JBPM_TIMER |          1 | JBPM_TIMER_DUEDATE__PRIORITY_ |            1 | PRIORITY_        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| JBPM_TIMER |          1 | JBPM_TIMER_DUEDATE__PRIORITY_ |            2 | DUEDATE_         | A         |          51 |     NULL | NULL   | YES  | BTREE      |         | 

有问题的查询：

mysql> explain select * from JBPM_TIMER where PRIORITY_ < 0 order by PRIORITY_ ASC, DUEDATE_ desc;
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------+
| id | select_type | table      | type  | possible_keys                 | key                           | key_len | ref  | rows | Extra                       |
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | JBPM_TIMER | range | JBPM_TIMER_DUEDATE__PRIORITY_ | JBPM_TIMER_DUEDATE__PRIORITY_ | 5       | NULL |   10 | Using where; Using filesort | 
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

使用 PRIORITY_ 升序排序的查询改为：

mysql> explain select * from JBPM_TIMER where PRIORITY_ < 0 order by PRIORITY_ ASC, DUEDATE_ asc;
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys                 | key                           | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | JBPM_TIMER | range | JBPM_TIMER_DUEDATE__PRIORITY_ | JBPM_TIMER_DUEDATE__PRIORITY_ | 5       | NULL |   10 | Using where | 
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

谷歌搜索表明，解决此问题的方法是添加另一列 (REVERSEPRIORITY_)，其中包含 PRIORITY_ * -1 的值并为其建立索引。在我看来，这是一个非常丑陋的解决方案，所以我想问你们是否有更好的解决方案！

-PRIORITY是最好的解决方案。

但是，您可以使用MySQL FORCE INDEX and STRAIGHT_JOIN效仿SKIP SCAN:

SELECT  jt *
FROM    (
        SELECT  DISTINCT priority
        FROM    JBPM_TIMER
        ORDER BY
                priority DESC
        ) jtd
STRAIGHT_JOIN
        JBPM_TIMER jt FORCE INDEX (ix_JBPM_TIMER_priority_duedate)
ON      jt.priority >= jtd.priority
        AND jt.priority <= jtd.priority

您需要创建一个索引(priority, duedate):

CREATE INDEX ix_JBPM_TIMER_priority_duedate ON JBPM_TIMER (priority, duedate)

请注意，与您原来的解决方案不同，这确实是一个丑陋的黑客行为其行为可能会在未来版本中改变MySQL.

如果您无法更改架构，我将其发布在这里只是作为一种解决方法。

如果有丝毫可能，请不要使用它MySQL正在升级。