在高并发下面,下面的SQL有严重的性能问题。
# User@Host: gg[gg] @ [xx.xx.xx.xx] Id: 28927
# Query_time: 224.879036 Lock_time: 0.000542 Rows_sent: 1 Rows_examined: 93633
EXPLAIN SELECT ...
CASE
WHEN o_order_comment.comment_count > 0 THEN
2
ELSE
1
END isComment
FROM gg_bm_o_order o_order
LEFT JOIN (SELECT order_code,SUM(1) AS comment_count FROM gg_bm_u_app_comment GROUP BY order_code) o_order_comment
ON o_order_comment.order_code = o_order.order_code
LEFT JOIN gg_om_dm_site m_site ON o_order.SITE_CODE = m_site.SITE_CODE
LEFT JOIN gg_om_dm_charging_pile charging_pile ON o_order.charging_pile_code =
charging_pile.charging_pile_code
LEFT JOIN gg_om_dm_site_img m_site_img ON m_site_img.SITE_CODE =
m_site.SITE_CODE
AND m_site_img.SORT_NO = 1
LEFT JOIN gg_bm_u_app_user app_user ON app_user.user_code =
o_order.user_code
LEFT JOIN gg_bm_o_preengage o_preengage ON o_order.PREENGAGE_CODE =
o_preengage.PREENGAGE_CODE
LEFT JOIN gg_om_dm_car_model_base m_car_model_base ON m_car_model_base.CAR_MODEL_CODE =
o_preengage.CAR_MODEL_CODE
LEFT JOIN gg_om_dm_car_brand_base m_car_brand_base ON m_car_model_base.CAR_BRAND_CODE =
m_car_brand_base.CAR_BRAND_CODE
LEFT JOIN gg_pay_invoice_order_relation invoice_order ON invoice_order.ORDER_CODE = o_order.order_code
WHERE o_order.ORDER_CODE = 'gg1801100049240171' AND o_order.IS_DELETE <> 2
LIMIT 0, 1;
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------------------- ------ ----------------------------------------- ------------------------- ------- --------------------------- ------ -------------
1 PRIMARY o_order const IDX_ORDER_CODE IDX_ORDER_CODE 99 const 1 (NULL)
1 PRIMARY o_preengage const IDX_RESERVATION_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 PRIMARY m_car_model_base const INDEX_CAR_MODEL_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 PRIMARY m_car_brand_base const INDEX_CAR_BRAND_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 99 const 10 (NULL)
1 PRIMARY m_site ref IDX_SITE_CODE IDX_SITE_CODE 99 const 1 (NULL)
1 PRIMARY charging_pile ref IDX_CHARGING_PILE_CODE IDX_CHARGING_PILE_CODE 99 const 1 (NULL)
1 PRIMARY m_site_img ref AK_KEY_SITE_CODE_SORT_NO,FK_IMG_SITE_CODE AK_KEY_SITE_CODE_SORT_NO 104 evic.m_site.SITE_CODE,const 1 (NULL)
1 PRIMARY app_user ref AK_USER_CODE AK_USER_CODE 99 const 1 (NULL)
1 PRIMARY invoice_order ref IX_INVOICEORDER_ORDERCODE IX_INVOICEORDER_ORDERCODE 99 const 1 (NULL)
2 DERIVED gg_bm_u_app_comment INDEX IX_APPCOMMENT_ORDERCODE IX_APPCOMMENT_ORDERCODE 99 (NULL) 94163 USING INDEX
一眼看到是这一步有问题,2 DERIVED gg_bm_u_app_comment 94163 有问题,分析业务,是查看评论,检查了数据,99.99%的单没有评论,直接把这个表的关联删除就可以了。
如果还想要这个功能,可以采用其他的方式,如查出一条数据就返回。
EXPLAIN SELECT tt.*, (SELECT IF(COUNT(1)=0,1,2) AS comment_count FROM gg_bm_u_app_comment WHERE order_code=orderCode LIMIT 1) isComment
FROM (
o_order.order_code AS orderCode,
...........
FROM gg_bm_o_order o_order
LEFT JOIN gg_om_dm_site m_site ON o_order.SITE_CODE = m_site.SITE_CODE
LEFT JOIN gg_om_dm_charging_pile charging_pile ON o_order.charging_pile_code =
charging_pile.charging_pile_code
LEFT JOIN gg_om_dm_site_img m_site_img ON m_site_img.SITE_CODE =
m_site.SITE_CODE
AND m_site_img.SORT_NO = 1
LEFT JOIN gg_bm_u_app_user app_user ON app_user.user_code =
o_order.user_code
LEFT JOIN gg_bm_o_preengage o_preengage ON o_order.PREENGAGE_CODE =
o_preengage.PREENGAGE_CODE
LEFT JOIN gg_om_dm_car_model_base m_car_model_base ON m_car_model_base.CAR_MODEL_CODE =
o_preengage.CAR_MODEL_CODE
LEFT JOIN gg_om_dm_car_brand_base m_car_brand_base ON m_car_model_base.CAR_BRAND_CODE =
m_car_brand_base.CAR_BRAND_CODE
LEFT JOIN gg_pay_invoice_order_relation invoice_order ON invoice_order.ORDER_CODE = o_order.order_code
WHERE o_order.ORDER_CODE = 'gg1801100049240171' AND o_order.IS_DELETE <> 2
LIMIT 0, 1) tt;
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ------------------- ------ ----------------------------------------- ------------------------- ------- ----------- ------ -------------
1 PRIMARY <derived3> system (NULL) (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED o_order const IDX_ORDER_CODE IDX_ORDER_CODE 99 const 1 (NULL)
3 DERIVED m_site const IDX_SITE_CODE IDX_SITE_CODE 99 const 1 (NULL)
3 DERIVED charging_pile const IDX_CHARGING_PILE_CODE IDX_CHARGING_PILE_CODE 99 const 1 (NULL)
3 DERIVED m_site_img const AK_KEY_SITE_CODE_SORT_NO,FK_IMG_SITE_CODE AK_KEY_SITE_CODE_SORT_NO 104 const,const 1 (NULL)
3 DERIVED app_user const AK_USER_CODE AK_USER_CODE 99 const 1 (NULL)
3 DERIVED o_preengage const IDX_RESERVATION_CODE (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED m_car_model_base const INDEX_CAR_MODEL_CODE (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED m_car_brand_base const INDEX_CAR_BRAND_CODE (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED invoice_order ref IX_INVOICEORDER_ORDERCODE IX_INVOICEORDER_ORDERCODE 99 const 1 (NULL)
FROM gg_bm_o_order o_order
LEFT JOIN gg_om_dm_site m_site ON o_order.SITE_CODE = m_site.SITE_CODE
LEFT JOIN gg_om_dm_charging_pile charging_pile ON o_order.charging_pile_code =
charging_pile.charging_pile_code
LEFT JOIN gg_om_dm_site_img m_site_img ON m_site_img.SITE_CODE =
m_site.SITE_CODE
AND m_site_img.SORT_NO = 1
LEFT JOIN gg_bm_u_app_user app_user ON app_user.user_code =
o_order.user_code
LEFT JOIN gg_bm_o_preengage o_preengage ON o_order.PREENGAGE_CODE =
o_preengage.PREENGAGE_CODE
LEFT JOIN gg_om_dm_car_model_base m_car_model_base ON m_car_model_base.CAR_MODEL_CODE =
o_preengage.CAR_MODEL_CODE
LEFT JOIN gg_om_dm_car_brand_base m_car_brand_base ON m_car_model_base.CAR_BRAND_CODE =
m_car_brand_base.CAR_BRAND_CODE
LEFT JOIN gg_pay_invoice_order_relation invoice_order ON invoice_order.ORDER_CODE = o_order.order_code
WHERE o_order.ORDER_CODE = 'gg1801100049240171' AND o_order.IS_DELETE <> 2
LIMIT 0, 1;
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ---------------- ------ ----------------------------------------- ------------------------- ------- ----------- ------ --------
1 SIMPLE o_order const IDX_ORDER_CODE IDX_ORDER_CODE 99 const 1 (NULL)
1 SIMPLE m_site const IDX_SITE_CODE IDX_SITE_CODE 99 const 1 (NULL)
1 SIMPLE charging_pile const IDX_CHARGING_PILE_CODE IDX_CHARGING_PILE_CODE 99 const 1 (NULL)
1 SIMPLE m_site_img const AK_KEY_SITE_CODE_SORT_NO,FK_IMG_SITE_CODE AK_KEY_SITE_CODE_SORT_NO 104 const,const 1 (NULL)
1 SIMPLE app_user const AK_USER_CODE AK_USER_CODE 99 const 1 (NULL)
1 SIMPLE o_preengage const IDX_RESERVATION_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 SIMPLE m_car_model_base const INDEX_CAR_MODEL_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 SIMPLE m_car_brand_base const INDEX_CAR_BRAND_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 SIMPLE invoice_order ref IX_INVOICEORDER_ORDERCODE IX_INVOICEORDER_ORDERCODE 99 const 1 (NULL)
# User@Host: gg[gg] @ [xx.xx.xx.xx] Id: 28927
# Query_time: 224.879036 Lock_time: 0.000542 Rows_sent: 1 Rows_examined: 93633
EXPLAIN SELECT ...
CASE
WHEN o_order_comment.comment_count > 0 THEN
2
ELSE
1
END isComment
FROM gg_bm_o_order o_order
LEFT JOIN (SELECT order_code,SUM(1) AS comment_count FROM gg_bm_u_app_comment GROUP BY order_code) o_order_comment
ON o_order_comment.order_code = o_order.order_code
LEFT JOIN gg_om_dm_site m_site ON o_order.SITE_CODE = m_site.SITE_CODE
LEFT JOIN gg_om_dm_charging_pile charging_pile ON o_order.charging_pile_code =
charging_pile.charging_pile_code
LEFT JOIN gg_om_dm_site_img m_site_img ON m_site_img.SITE_CODE =
m_site.SITE_CODE
AND m_site_img.SORT_NO = 1
LEFT JOIN gg_bm_u_app_user app_user ON app_user.user_code =
o_order.user_code
LEFT JOIN gg_bm_o_preengage o_preengage ON o_order.PREENGAGE_CODE =
o_preengage.PREENGAGE_CODE
LEFT JOIN gg_om_dm_car_model_base m_car_model_base ON m_car_model_base.CAR_MODEL_CODE =
o_preengage.CAR_MODEL_CODE
LEFT JOIN gg_om_dm_car_brand_base m_car_brand_base ON m_car_model_base.CAR_BRAND_CODE =
m_car_brand_base.CAR_BRAND_CODE
LEFT JOIN gg_pay_invoice_order_relation invoice_order ON invoice_order.ORDER_CODE = o_order.order_code
WHERE o_order.ORDER_CODE = 'gg1801100049240171' AND o_order.IS_DELETE <> 2
LIMIT 0, 1;
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------------------- ------ ----------------------------------------- ------------------------- ------- --------------------------- ------ -------------
1 PRIMARY o_order const IDX_ORDER_CODE IDX_ORDER_CODE 99 const 1 (NULL)
1 PRIMARY o_preengage const IDX_RESERVATION_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 PRIMARY m_car_model_base const INDEX_CAR_MODEL_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 PRIMARY m_car_brand_base const INDEX_CAR_BRAND_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 99 const 10 (NULL)
1 PRIMARY m_site ref IDX_SITE_CODE IDX_SITE_CODE 99 const 1 (NULL)
1 PRIMARY charging_pile ref IDX_CHARGING_PILE_CODE IDX_CHARGING_PILE_CODE 99 const 1 (NULL)
1 PRIMARY m_site_img ref AK_KEY_SITE_CODE_SORT_NO,FK_IMG_SITE_CODE AK_KEY_SITE_CODE_SORT_NO 104 evic.m_site.SITE_CODE,const 1 (NULL)
1 PRIMARY app_user ref AK_USER_CODE AK_USER_CODE 99 const 1 (NULL)
1 PRIMARY invoice_order ref IX_INVOICEORDER_ORDERCODE IX_INVOICEORDER_ORDERCODE 99 const 1 (NULL)
2 DERIVED gg_bm_u_app_comment INDEX IX_APPCOMMENT_ORDERCODE IX_APPCOMMENT_ORDERCODE 99 (NULL) 94163 USING INDEX
一眼看到是这一步有问题,2 DERIVED gg_bm_u_app_comment 94163 有问题,分析业务,是查看评论,检查了数据,99.99%的单没有评论,直接把这个表的关联删除就可以了。
如果还想要这个功能,可以采用其他的方式,如查出一条数据就返回。
EXPLAIN SELECT tt.*, (SELECT IF(COUNT(1)=0,1,2) AS comment_count FROM gg_bm_u_app_comment WHERE order_code=orderCode LIMIT 1) isComment
FROM (
o_order.order_code AS orderCode,
...........
FROM gg_bm_o_order o_order
LEFT JOIN gg_om_dm_site m_site ON o_order.SITE_CODE = m_site.SITE_CODE
LEFT JOIN gg_om_dm_charging_pile charging_pile ON o_order.charging_pile_code =
charging_pile.charging_pile_code
LEFT JOIN gg_om_dm_site_img m_site_img ON m_site_img.SITE_CODE =
m_site.SITE_CODE
AND m_site_img.SORT_NO = 1
LEFT JOIN gg_bm_u_app_user app_user ON app_user.user_code =
o_order.user_code
LEFT JOIN gg_bm_o_preengage o_preengage ON o_order.PREENGAGE_CODE =
o_preengage.PREENGAGE_CODE
LEFT JOIN gg_om_dm_car_model_base m_car_model_base ON m_car_model_base.CAR_MODEL_CODE =
o_preengage.CAR_MODEL_CODE
LEFT JOIN gg_om_dm_car_brand_base m_car_brand_base ON m_car_model_base.CAR_BRAND_CODE =
m_car_brand_base.CAR_BRAND_CODE
LEFT JOIN gg_pay_invoice_order_relation invoice_order ON invoice_order.ORDER_CODE = o_order.order_code
WHERE o_order.ORDER_CODE = 'gg1801100049240171' AND o_order.IS_DELETE <> 2
LIMIT 0, 1) tt;
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ------------------- ------ ----------------------------------------- ------------------------- ------- ----------- ------ -------------
1 PRIMARY <derived3> system (NULL) (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED o_order const IDX_ORDER_CODE IDX_ORDER_CODE 99 const 1 (NULL)
3 DERIVED m_site const IDX_SITE_CODE IDX_SITE_CODE 99 const 1 (NULL)
3 DERIVED charging_pile const IDX_CHARGING_PILE_CODE IDX_CHARGING_PILE_CODE 99 const 1 (NULL)
3 DERIVED m_site_img const AK_KEY_SITE_CODE_SORT_NO,FK_IMG_SITE_CODE AK_KEY_SITE_CODE_SORT_NO 104 const,const 1 (NULL)
3 DERIVED app_user const AK_USER_CODE AK_USER_CODE 99 const 1 (NULL)
3 DERIVED o_preengage const IDX_RESERVATION_CODE (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED m_car_model_base const INDEX_CAR_MODEL_CODE (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED m_car_brand_base const INDEX_CAR_BRAND_CODE (NULL) (NULL) (NULL) 1 (NULL)
3 DERIVED invoice_order ref IX_INVOICEORDER_ORDERCODE IX_INVOICEORDER_ORDERCODE 99 const 1 (NULL)
2 DEPENDENT SUBQUERY gg_bm_u_app_comment ref IX_APPCOMMENT_ORDERCODE IX_APPCOMMENT_ORDERCODE 99 const 1 Using index
EXPLAIN SELECT .....FROM gg_bm_o_order o_order
LEFT JOIN gg_om_dm_site m_site ON o_order.SITE_CODE = m_site.SITE_CODE
LEFT JOIN gg_om_dm_charging_pile charging_pile ON o_order.charging_pile_code =
charging_pile.charging_pile_code
LEFT JOIN gg_om_dm_site_img m_site_img ON m_site_img.SITE_CODE =
m_site.SITE_CODE
AND m_site_img.SORT_NO = 1
LEFT JOIN gg_bm_u_app_user app_user ON app_user.user_code =
o_order.user_code
LEFT JOIN gg_bm_o_preengage o_preengage ON o_order.PREENGAGE_CODE =
o_preengage.PREENGAGE_CODE
LEFT JOIN gg_om_dm_car_model_base m_car_model_base ON m_car_model_base.CAR_MODEL_CODE =
o_preengage.CAR_MODEL_CODE
LEFT JOIN gg_om_dm_car_brand_base m_car_brand_base ON m_car_model_base.CAR_BRAND_CODE =
m_car_brand_base.CAR_BRAND_CODE
LEFT JOIN gg_pay_invoice_order_relation invoice_order ON invoice_order.ORDER_CODE = o_order.order_code
WHERE o_order.ORDER_CODE = 'gg1801100049240171' AND o_order.IS_DELETE <> 2
LIMIT 0, 1;
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ---------------- ------ ----------------------------------------- ------------------------- ------- ----------- ------ --------
1 SIMPLE o_order const IDX_ORDER_CODE IDX_ORDER_CODE 99 const 1 (NULL)
1 SIMPLE m_site const IDX_SITE_CODE IDX_SITE_CODE 99 const 1 (NULL)
1 SIMPLE charging_pile const IDX_CHARGING_PILE_CODE IDX_CHARGING_PILE_CODE 99 const 1 (NULL)
1 SIMPLE m_site_img const AK_KEY_SITE_CODE_SORT_NO,FK_IMG_SITE_CODE AK_KEY_SITE_CODE_SORT_NO 104 const,const 1 (NULL)
1 SIMPLE app_user const AK_USER_CODE AK_USER_CODE 99 const 1 (NULL)
1 SIMPLE o_preengage const IDX_RESERVATION_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 SIMPLE m_car_model_base const INDEX_CAR_MODEL_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 SIMPLE m_car_brand_base const INDEX_CAR_BRAND_CODE (NULL) (NULL) (NULL) 1 (NULL)
1 SIMPLE invoice_order ref IX_INVOICEORDER_ORDERCODE IX_INVOICEORDER_ORDERCODE 99 const 1 (NULL)