关于为优先级jBPM优化mysql索引,我有another个问题.相关指数如下：

    | JBPM_TIMER |          1 | JBPM_TIMER_REVERSEPRIORITY__DUEDATE_ |            1 | REVERSEPRIORITY_ | A         |          17 |     NULL | NULL   | YES  | BTREE      |         | 
    | JBPM_TIMER |          1 | JBPM_TIMER_REVERSEPRIORITY__DUEDATE_ |            2 | DUEDATE_         | A         |      971894 |     NULL | NULL   | YES  | BTREE      |         | 
    | JBPM_TIMER |          1 | JBPM_TIMER_DUEDATE_                  |            1 | DUEDATE_         | A         |      971894 |     NULL | NULL   | YES  | BTREE      |         | 

检索计时器时,JBPM会提出两个问题.第一个取决于综合指数(反向优先权和duedate),第二个取决于单独的duedate指数.但是,在添加solo索引时,它在运行此查询时优先于正确的索引：

mysql> explain select timer0_.ID_ as col_0_0_ from JBPM_TIMER timer0_ where timer0_.ISSUSPENDED_<>1 and  timer0_.DUEDATE_<='2009-08-17 14:51:06' order  by timer0_.REVERSEPRIORITY_ asc, timer0_.DUEDATE_ asc limit 160;
+----+-------------+---------+-------+---------------------------+---------------------------+---------+------+-------+-----------------------------+
| id | select_type | table   | type  | possible_keys             | key                       | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+---------------------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | timer0_ | range | JBPM_TIMER_DUEDATE_       | JBPM_TIMER_DUEDATE_ERIK_T | 9       | NULL | 971894| Using where; Using filesort | 
+----+-------------+---------+-------+---------------------------+---------------------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

另一个查询需要此索引：

mysql> explain select timer0_.ID_ as col_0_0_ from JBPM_TIMER timer0_ where (timer0_.EXCEPTION_ is null) and timer0_.ISSUSPENDED_<>1 order by timer0_.DUEDATE_ asc limit 160;
+----+-------------+---------+-------+---------------+---------------------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys | key                 | key_len | ref  | rows  | Extra       |
+----+-------------+---------+-------+---------------+---------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | timer0_ | index | NULL          | JBPM_TIMER_DUEDATE_ | 9       | NULL | 24249 | Using where | 
+----+-------------+---------+-------+---------------+---------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

删除独奏索引时,查询号1正确执行,查询2需要文件排序.添加独奏索引查询号2正确执行,查询1需要文件排序.

可以通过向第一个查询添加索引提示来覆盖此不需要的行为：

explain select timer0_.ID_ as col_0_0_ 
from JBPM_TIMER timer0_ USE INDEX (JBPM_TIMER_REVERSEPRIORITY__DUEDATE_) 
where timer0_.ISSUSPENDED_<>1 and  
    timer0_.DUEDATE_<='2009-08-17 14:51:06' 
order  by timer0_.REVERSEPRIORITY_ asc, timer0_.DUEDATE_ asc 
limit 160;

提示是否是使MySQL正确优化两个查询的唯一方法？或者我们做错了什么？

解决方法:

尝试按顺序添加索引(DUEDATE_,REVERSEPRIORITY_).它仍将使用filesort(我认为)但更少的行.

还尝试OPTIMIZE TABLE table_name你的表和CHECK TABLE table_name你的表(所以mysql将重新计算索引值).

这只是一个有根据的猜测.