最近优化MySQL5.6的SQL语句,对视图的优化不能让人满意,谓词无法推进,下面有两个例子。以后用视图得小心了,一定要看执行计划。
样例1:
视图定义如下:
SHOW CREATE TABLE view_site_car ;
select m_site . SITE_CODE AS site_code,
ifnull(group_concat(gg_car_model . CAR_MODEL_CODE order by
gg_car_model . CAR_MODEL_CODE DESC separator ','),
_utf8 '') AS site_car_models
from ((gg_om_gg_site m_site left join gg_om_gg_charging_pile
gg_charging_pile
on((gg_charging_pile . SITE_CODE = m_site . SITE_CODE))) left join
gg_om_gg_car_model gg_car_model
on(((gg_car_model . RELEVANCE_CODE = gg_charging_pile .
CHARGING_PILE_CODE) and (gg_car_model . CAR_TYPE = 1))))
group by m_site . SITE_CODE;
使用视图的执行计划。
EXPLAIN SELECT * FROM view_site_car WHERE SITE_CODE='SITE-20160630173940-000001';
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------------- ------ --------------------------- --------------------------- ------- ----------------------------------- ------ -------------
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 7472 Using where
2 DERIVED m_site index (NULL) IDX_SITE_CODE 99 (NULL) 467 Using index
2 DERIVED gg_charging_pile ref IDX_CHARGING_PILE_SITE_CODE IDX_CHARGING_PILE_SITE_CODE 99 m_site.SITE_CODE 4 Using where
2 DERIVED gg_car_model ref INDEX_RELEVANCE_CODE INDEX_RELEVANCE_CODE 99 gg_charging_pile.CHARGING_PILE_CODE 4 Using where
拆掉视图,然后直接传入条件,用到索引。
EXPLAIN SELECT m_site.SITE_CODE AS site_code,
IFNULL(GROUP_CONCAT(gg_car_model . CAR_MODEL_CODE ORDER BY
gg_car_model . CAR_MODEL_CODE DESC SEPARATOR ','),
_utf8 '') AS site_car_models
FROM ((gg_om_gg_site m_site LEFT JOIN gg_om_gg_charging_pile
gg_charging_pile
ON((gg_charging_pile . SITE_CODE = m_site . SITE_CODE))) LEFT JOIN
gg_om_gg_car_model gg_car_model
ON(((gg_car_model . RELEVANCE_CODE = gg_charging_pile .
CHARGING_PILE_CODE) AND (gg_car_model . CAR_TYPE = 1))))
WHERE m_site.SITE_CODE='SITE-20160630173940-000001'
GROUP BY m_site . SITE_CODE;
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------------- ------ --------------------------- --------------------------- ------- ---------------------------------- ------ -------------
1 SIMPLE m_site const IDX_SITE_CODE IDX_SITE_CODE 99 const 1 Using index
1 SIMPLE gg_charging_pile ref IDX_CHARGING_PILE_SITE_CODE IDX_CHARGING_PILE_SITE_CODE 99 const 10 Using where
1 SIMPLE gg_car_model ref INDEX_RELEVANCE_CODE INDEX_RELEVANCE_CODE 99 gg_charging_pile.CHARGING_PILE_CODE 4 Using where
样例2:
视图定义如下:
SHOW CREATE TABLE v_bill_order;
select .....
from ((gg_pay_pay_result r left join gg_bm_o_order o
on((r.OUT_TRADE_NO = o.ORDER_CODE))) left join gg_bm_u_app_user u
on((o.USER_CODE = u.USER_CODE)))
where (r.TRADE_TYPE = 1)
union all
select .....
from ((gg_pay_elec_currency_pay r left join gg_bm_o_order o
on((r.TRADE_NO = o.ORDER_CODE))) left join gg_bm_u_app_user u
on((o.USER_CODE = u.USER_CODE)))
union all
select .....
from ((gg_pay_pay_result r left join gg_bm_u_charge_record o
on((r.OUT_TRADE_NO = o.CHARGE_RECORD_CODE))) left join
gg_bm_u_app_user u on((o.USER_CODE = u.USER_CODE)))
where (r.TRADE_TYPE = 2)
使用视图的执行计划,可以看到根本没有用到PAYMENT_TIME的索引。
EXPLAIN SELECT *
FROM v_bill_order r
WHERE r.`PAYMENT_TIME` >= STR_TO_DATE(CONCAT('2018-01-01',' 00:00:00'),'%Y-%m-%d %H:%i:%s')
AND r.`PAYMENT_TIME` <= STR_TO_DATE(CONCAT('2018-01-01',' 23:59:59'),'%Y-%m-%d %H:%i:%s')
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ---------- ------ ---------------------------------- ---------------------------------- ------- ------------------------------------ ------ -------------
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 140700 Using where
2 DERIVED r ALL (NULL) (NULL) (NULL) (NULL) 35887 Using where
2 DERIVED o ref IDX_ORDER_CODE IDX_ORDER_CODE 99 r.OUT_TRADE_NO 1 Using where
2 DERIVED u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
5 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
4 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
3 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
6 UNION r ALL (NULL) (NULL) (NULL) (NULL) 68926
6 UNION o ref IDX_ORDER_CODE IDX_ORDER_CODE 99 r.TRADE_NO 1 Using where
6 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
7 DEPENDENT SUBQUERY icd ref IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE 100 r.BILL_CODE,r.PAYMENT_TYPE 1
8 UNION r ALL (NULL) (NULL) (NULL) (NULL) 35887 Using where
8 UNION o ref AK_CHARGE_RECORD_CODE AK_CHARGE_RECORD_CODE 99 r.OUT_TRADE_NO 1 Using where
8 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
11 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
10 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
9 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
拆掉视图,然后直接传入条件,用到索引。
EXPLAIN SELECT .........
FROM ((gg_pay_pay_result r LEFT JOIN gg_bm_o_order o
ON((r . OUT_TRADE_NO = o . ORDER_CODE))) LEFT JOIN gg_bm_u_app_user u
ON((o . USER_CODE = u . USER_CODE)))
WHERE (r. TRADE_TYPE = 1 AND r.PAYMENT_TIME >=
STR_TO_DATE(CONCAT('2018-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
r.PAYMENT_TIME <=
STR_TO_DATE(CONCAT('2018-01-01', ' 23:59:59'), '%Y-%m-%d %H:%i:%s'))
UNION ALL
SELECT .........
FROM ((gg_pay_elec_currency_pay r LEFT JOIN gg_bm_o_order o
ON((r . TRADE_NO = o . ORDER_CODE))) LEFT JOIN gg_bm_u_app_user u
ON((o . USER_CODE = u . USER_CODE)))
WHERE o.ORDER_END_TIME >=
STR_TO_DATE(CONCAT('2018-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
o.ORDER_END_TIME <=
STR_TO_DATE(CONCAT('2018-01-01', ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
UNION ALL
SELECT .........
FROM ((gg_pay_pay_result r LEFT JOIN gg_bm_u_charge_record o
ON((r . OUT_TRADE_NO = o . CHARGE_RECORD_CODE))) LEFT JOIN
gg_bm_u_app_user u ON((o . USER_CODE = u . USER_CODE)))
WHERE (r . TRADE_TYPE = 2 AND r.PAYMENT_TIME >=
STR_TO_DATE(CONCAT('2018-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
r.PAYMENT_TIME <=
STR_TO_DATE(CONCAT('2018-01-01', ' 23:59:59'), '%Y-%m-%d %H:%i:%s'))
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ------ ------ --------------------------------------- ---------------------------------- ------- ------------------------------------ ------ ---------------------------------
1 PRIMARY r range IDX_PAY_RESULT_PAYMENT_TIME IDX_PAY_RESULT_PAYMENT_TIME 6 (NULL) 1 Using index condition; Using where
1 PRIMARY o ref IDX_ORDER_CODE IDX_ORDER_CODE 99 r.OUT_TRADE_NO 1 Using where
1 PRIMARY u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
4 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
3 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
2 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
5 UNION o range IDX_ORDER_CODE,IDX_ORDER_ORDER_END_TIME IDX_ORDER_ORDER_END_TIME 6 (NULL) 1 Using index condition; Using where
5 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
5 UNION r ref IDX_ELEC_CURRENCY_PAY_TRADE_NO IDX_ELEC_CURRENCY_PAY_TRADE_NO 99 o.ORDER_CODE 1
6 DEPENDENT SUBQUERY icd ref IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE 100 r.BILL_CODE,r.PAYMENT_TYPE 1
7 UNION r range IDX_PAY_RESULT_PAYMENT_TIME IDX_PAY_RESULT_PAYMENT_TIME 6 (NULL) 1 Using index condition; Using where
7 UNION o ref AK_CHARGE_RECORD_CODE AK_CHARGE_RECORD_CODE 99 r.OUT_TRADE_NO 1 Using where
7 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
10 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
9 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
8 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
样例1:
视图定义如下:
SHOW CREATE TABLE view_site_car ;
select m_site . SITE_CODE AS site_code,
ifnull(group_concat(gg_car_model . CAR_MODEL_CODE order by
gg_car_model . CAR_MODEL_CODE DESC separator ','),
_utf8 '') AS site_car_models
from ((gg_om_gg_site m_site left join gg_om_gg_charging_pile
gg_charging_pile
on((gg_charging_pile . SITE_CODE = m_site . SITE_CODE))) left join
gg_om_gg_car_model gg_car_model
on(((gg_car_model . RELEVANCE_CODE = gg_charging_pile .
CHARGING_PILE_CODE) and (gg_car_model . CAR_TYPE = 1))))
group by m_site . SITE_CODE;
使用视图的执行计划。
EXPLAIN SELECT * FROM view_site_car WHERE SITE_CODE='SITE-20160630173940-000001';
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------------- ------ --------------------------- --------------------------- ------- ----------------------------------- ------ -------------
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 7472 Using where
2 DERIVED m_site index (NULL) IDX_SITE_CODE 99 (NULL) 467 Using index
2 DERIVED gg_charging_pile ref IDX_CHARGING_PILE_SITE_CODE IDX_CHARGING_PILE_SITE_CODE 99 m_site.SITE_CODE 4 Using where
2 DERIVED gg_car_model ref INDEX_RELEVANCE_CODE INDEX_RELEVANCE_CODE 99 gg_charging_pile.CHARGING_PILE_CODE 4 Using where
拆掉视图,然后直接传入条件,用到索引。
EXPLAIN SELECT m_site.SITE_CODE AS site_code,
IFNULL(GROUP_CONCAT(gg_car_model . CAR_MODEL_CODE ORDER BY
gg_car_model . CAR_MODEL_CODE DESC SEPARATOR ','),
_utf8 '') AS site_car_models
FROM ((gg_om_gg_site m_site LEFT JOIN gg_om_gg_charging_pile
gg_charging_pile
ON((gg_charging_pile . SITE_CODE = m_site . SITE_CODE))) LEFT JOIN
gg_om_gg_car_model gg_car_model
ON(((gg_car_model . RELEVANCE_CODE = gg_charging_pile .
CHARGING_PILE_CODE) AND (gg_car_model . CAR_TYPE = 1))))
WHERE m_site.SITE_CODE='SITE-20160630173940-000001'
GROUP BY m_site . SITE_CODE;
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------------- ------ --------------------------- --------------------------- ------- ---------------------------------- ------ -------------
1 SIMPLE m_site const IDX_SITE_CODE IDX_SITE_CODE 99 const 1 Using index
1 SIMPLE gg_charging_pile ref IDX_CHARGING_PILE_SITE_CODE IDX_CHARGING_PILE_SITE_CODE 99 const 10 Using where
1 SIMPLE gg_car_model ref INDEX_RELEVANCE_CODE INDEX_RELEVANCE_CODE 99 gg_charging_pile.CHARGING_PILE_CODE 4 Using where
样例2:
视图定义如下:
SHOW CREATE TABLE v_bill_order;
select .....
from ((gg_pay_pay_result r left join gg_bm_o_order o
on((r.OUT_TRADE_NO = o.ORDER_CODE))) left join gg_bm_u_app_user u
on((o.USER_CODE = u.USER_CODE)))
where (r.TRADE_TYPE = 1)
union all
select .....
from ((gg_pay_elec_currency_pay r left join gg_bm_o_order o
on((r.TRADE_NO = o.ORDER_CODE))) left join gg_bm_u_app_user u
on((o.USER_CODE = u.USER_CODE)))
union all
select .....
from ((gg_pay_pay_result r left join gg_bm_u_charge_record o
on((r.OUT_TRADE_NO = o.CHARGE_RECORD_CODE))) left join
gg_bm_u_app_user u on((o.USER_CODE = u.USER_CODE)))
where (r.TRADE_TYPE = 2)
使用视图的执行计划,可以看到根本没有用到PAYMENT_TIME的索引。
EXPLAIN SELECT *
FROM v_bill_order r
WHERE r.`PAYMENT_TIME` >= STR_TO_DATE(CONCAT('2018-01-01',' 00:00:00'),'%Y-%m-%d %H:%i:%s')
AND r.`PAYMENT_TIME` <= STR_TO_DATE(CONCAT('2018-01-01',' 23:59:59'),'%Y-%m-%d %H:%i:%s')
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ---------- ------ ---------------------------------- ---------------------------------- ------- ------------------------------------ ------ -------------
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 140700 Using where
2 DERIVED r ALL (NULL) (NULL) (NULL) (NULL) 35887 Using where
2 DERIVED o ref IDX_ORDER_CODE IDX_ORDER_CODE 99 r.OUT_TRADE_NO 1 Using where
2 DERIVED u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
5 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
4 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
3 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
6 UNION r ALL (NULL) (NULL) (NULL) (NULL) 68926
6 UNION o ref IDX_ORDER_CODE IDX_ORDER_CODE 99 r.TRADE_NO 1 Using where
6 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
7 DEPENDENT SUBQUERY icd ref IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE 100 r.BILL_CODE,r.PAYMENT_TYPE 1
8 UNION r ALL (NULL) (NULL) (NULL) (NULL) 35887 Using where
8 UNION o ref AK_CHARGE_RECORD_CODE AK_CHARGE_RECORD_CODE 99 r.OUT_TRADE_NO 1 Using where
8 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
11 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
10 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
9 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
拆掉视图,然后直接传入条件,用到索引。
EXPLAIN SELECT .........
FROM ((gg_pay_pay_result r LEFT JOIN gg_bm_o_order o
ON((r . OUT_TRADE_NO = o . ORDER_CODE))) LEFT JOIN gg_bm_u_app_user u
ON((o . USER_CODE = u . USER_CODE)))
WHERE (r. TRADE_TYPE = 1 AND r.PAYMENT_TIME >=
STR_TO_DATE(CONCAT('2018-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
r.PAYMENT_TIME <=
STR_TO_DATE(CONCAT('2018-01-01', ' 23:59:59'), '%Y-%m-%d %H:%i:%s'))
UNION ALL
SELECT .........
FROM ((gg_pay_elec_currency_pay r LEFT JOIN gg_bm_o_order o
ON((r . TRADE_NO = o . ORDER_CODE))) LEFT JOIN gg_bm_u_app_user u
ON((o . USER_CODE = u . USER_CODE)))
WHERE o.ORDER_END_TIME >=
STR_TO_DATE(CONCAT('2018-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
o.ORDER_END_TIME <=
STR_TO_DATE(CONCAT('2018-01-01', ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
UNION ALL
SELECT .........
FROM ((gg_pay_pay_result r LEFT JOIN gg_bm_u_charge_record o
ON((r . OUT_TRADE_NO = o . CHARGE_RECORD_CODE))) LEFT JOIN
gg_bm_u_app_user u ON((o . USER_CODE = u . USER_CODE)))
WHERE (r . TRADE_TYPE = 2 AND r.PAYMENT_TIME >=
STR_TO_DATE(CONCAT('2018-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
r.PAYMENT_TIME <=
STR_TO_DATE(CONCAT('2018-01-01', ' 23:59:59'), '%Y-%m-%d %H:%i:%s'))
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ------ ------ --------------------------------------- ---------------------------------- ------- ------------------------------------ ------ ---------------------------------
1 PRIMARY r range IDX_PAY_RESULT_PAYMENT_TIME IDX_PAY_RESULT_PAYMENT_TIME 6 (NULL) 1 Using index condition; Using where
1 PRIMARY o ref IDX_ORDER_CODE IDX_ORDER_CODE 99 r.OUT_TRADE_NO 1 Using where
1 PRIMARY u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
4 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
3 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
2 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
5 UNION o range IDX_ORDER_CODE,IDX_ORDER_ORDER_END_TIME IDX_ORDER_ORDER_END_TIME 6 (NULL) 1 Using index condition; Using where
5 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
5 UNION r ref IDX_ELEC_CURRENCY_PAY_TRADE_NO IDX_ELEC_CURRENCY_PAY_TRADE_NO 99 o.ORDER_CODE 1
6 DEPENDENT SUBQUERY icd ref IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE IDX_ELEC_DETAIL_PAY_BILL_CODE_TYPE 100 r.BILL_CODE,r.PAYMENT_TYPE 1
7 UNION r range IDX_PAY_RESULT_PAYMENT_TIME IDX_PAY_RESULT_PAYMENT_TIME 6 (NULL) 1 Using index condition; Using where
7 UNION o ref AK_CHARGE_RECORD_CODE AK_CHARGE_RECORD_CODE 99 r.OUT_TRADE_NO 1 Using where
7 UNION u ref AK_USER_CODE AK_USER_CODE 99 o.USER_CODE 1 Using where
10 DEPENDENT SUBQUERY ba eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
9 DEPENDENT SUBQUERY al eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where
8 DEPENDENT SUBQUERY wx eq_ref PRIMARY PRIMARY 98 r.ASYN_RESULT_ID 1 Using where