MySQL中缺少降序索引功能的解决方法 [英] Workaround for missing descending index feature in MySQL

问题描述

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

We have a problem with our production environment which uses a modified version of jBPM that has support for priorities. Indices present are:

| 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_的查询以升序排序：

The query with PRIORITY_ sorted ascending instead:

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的值，而是索引该值。在我看来，这似乎是一个非常丑陋的解决方案，所以我想问你们大家是否有更好的解决方案！

Googling around suggests that the solution to this is to add another column (REVERSEPRIORITY_) that contains the value of PRIORITY_ * -1 and index that one instead. This seems to me like a pretty ugly solution so I want to ask you folks if you have better ones!

推荐答案

-PRIORITY 是最佳解决方案。

但是，您可以使用 MySQL FORCE INDEX 和 STRAIGHT_JOIN 模拟 SKIP SCAN ：

However, you can use MySQL FORCE INDEX and STRAIGHT_JOIN to emulate 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

您需要在上创建索引（优先权，duedate）：

CREATE INDEX ix_JBPM_TIMER_priority_duedate ON JBPM_TIMER (priority, duedate)

请注意，与原始溶胶不同ution，这真是一个丑陋的黑客，其行为可以在 MySQL 的未来版本中发生变化。

Note that unlike your original solution, this really is an ugly hack whose behavior can change in the future releases of MySQL.

如果您无法更改架构，我只会将其作为解决方法在此处发布。

I'm posting it here only as a workaround if you cannot change your schema.

如果您的机会稍有可能，请不要使用它code> MySQL 正在升级。

Don't use it if there is a slightest chance of your MySQL being upgraded.

这篇关于MySQL中缺少降序索引功能的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！