诡异的MySql执行计划的更改

SQL是在从库上执行

 

正常的执行计划： 

explain SELECT h.id hid,r.rate rate FROM ORDER_CUST_INFO o,  exchange_rate r, HOTEL_INFO h   where o.group_id = h.group_id and o.currency = r.type and r.type != 'CNY'  and h.type = 'OWNER'  and h.apply_status = 'CHECKED' and h.online = true and h.online_status = true and h.deleted = false ;
+----+-------------+-------+------+---------------+--------------+---------+----------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref            | rows  | Extra                          |
+----+-------------+-------+------+---------------+--------------+---------+----------------+-------+--------------------------------+
|  1 | SIMPLE      | r     | ALL  | NULL          | NULL         | NULL    | NULL           |     2 | Using where                    |
|  1 | SIMPLE      | o     | ALL  | FK_OCI_GID    | NULL         | NULL    | NULL           | 18476 | Using where; Using join buffer |
|  1 | SIMPLE      | h     | ref  | idx_group_id  | idx_group_id | 5       | hms.o.GROUP_ID |     1 | Using where                    |
+----+-------------+-------+------+---------------+--------------+---------+----------------+-------+--------------------------------+
3 rows in set (0.00 sec)

 

在定时备份时，从库的执行计划变更

mysql> explain SELECT h.id hid,r.rate rate FROM ORDER_CUST_INFO o,  exchange_rate r, HOTEL_INFO h   where o.group_id = h.group_id and o.currency = r.type and r.type != 'CNY'  and h.type = 'OWNER'  and h.apply_status = 'CHECKED' and h.online = true and h.online_status = true and h.deleted = false ;        
+----+-------------+-------+--------+---------------+------------+---------+----------------+-------+--------------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref            | rows  | Extra                          |
+----+-------------+-------+--------+---------------+------------+---------+----------------+-------+--------------------------------+
|  1 | SIMPLE      | r     | ALL    | NULL          | NULL       | NULL    | NULL           |     2 | Using where                    |
|  1 | SIMPLE      | h     | ALL    | idx_group_id  | NULL       | NULL    | NULL           | 68935 | Using where; Using join buffer |
|  1 | SIMPLE      | o     | eq_ref | FK_OCI_GID    | FK_OCI_GID | 4       | hms.h.group_id |     1 | Using where                    |
+----+-------------+-------+--------+---------------+------------+---------+----------------+-------+--------------------------------+
3 rows in set (0.00 sec)

 

是在想不到好办法，只能强制表的扫描顺序：

SELECT 

  h.id hid,

  r.rate rate 

FROM

  exchange_rate r 
  STRAIGHT_JOIN ORDER_CUST_INFO o   ON o.CURRENCY = r.TYPE  
  STRAIGHT_JOIN HOTEL_INFO h  ON h.group_id = o.GROUP_ID 

WHERE o.group_id = h.group_id 

  AND o.currency = r.TYPE 

  AND r.TYPE != 'CNY' 

  AND h.TYPE = 'OWNER' 

  AND h.apply_status = 'CHECKED' 

  AND h.online = TRUE 

  AND h.online_status = TRUE 

  AND h.deleted = FALSE ;