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)
+----+-------------+-------+------+---------------+--------------+---------+----------------+-------+--------------------------------+
| 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)
+----+-------------+-------+--------+---------------+------------+---------+----------------+-------+--------------------------------+
| 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 ;