为什么MySQL使用错误的索引？ [英] Why does MySQL use the wrong index?
问题描述
我有关于优化mysql索引的另一个问题我们优先考虑jBPM。相关指数如下所示：
I have another question regarding optimizing mysql indices for our prioritizing jBPM. The relevant indices look like this:
| 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索引时，它在运行此查询时优先于正确的索引：
JBPM asks two questions when retrieving timers. The first one is dependent on the combined index (reversed priority and duedate) and the second one on the solo duedate index. However, when adding the solo index it takes precedence over the correct one when running this query:
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)
另一个需要此索引查询：
This index is needed for another query:
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需要文件排序。添加solo索引查询号2正确执行，查询1需要一个filesort。
When removing the solo index, query number 1 executes correctly and query 2 needs a filesort. Adding the solo index query number 2 executes correctly and query 1 needs a filesort.
可以通过向第一个查询添加索引提示来覆盖此不需要的行为：
This unwanted behavior can be overridden by adding an index hint to the first query:
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正确优化两个查询的唯一方法？或者我们做错了什么？
Is the hint the only way to go to make MySQL optimize both queries correctly? Or are we doing something wrong?
推荐答案
尝试将索引添加到（DUEDATE_，REVERSEPRIORITY _）按此顺序。它仍然会使用filesort（我认为），但更少的行。
Try adding an index to (DUEDATE_, REVERSEPRIORITY_) in that order. It will still use filesort (I think) but on much less rows.
还尝试 OPTIMIZE TABLE table_name your table和 CHECK TABLE table_name 你的表（所以mysql会重新计算索引值）。
Also try to OPTIMIZE TABLE table_name your table and CHECK TABLE table_name your table (so mysql will re-calculate index values).
这都是公正的一个有根据的猜测。
This is all just an educated guess.
这篇关于为什么MySQL使用错误的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
