目前在處理一條SQL耗時查詢的優化，查詢的資料量目前就只有3w但是耗時將近4.5s。查看SQL執行計劃都有走索引，關鍵欄位也建立索引查詢仍舊需要那么長的耗時。目前懷疑耗時的地方在于兩個：一個是SQL里的子查詢二就是if處理。但是本人想不到啥好的辦法去優化它，子查詢我嘗試用關聯查詢去替換，結果資料不對（可能是我寫的SQL問題）
，求助哪位大佬指點下執行SQL如下：
SELECT
a.company_id,
a.company_name,
c.personal_wx,
a.create_time,
d.end_time,
a.agent_company_id,
d.available_num,
d.current_num,
a.is_agent,
a.company_type,
a.agent_money,
( SELECT count( * ) FROM business_card WHERE company_id = a.company_id AND state = 1 AND is_del = 0 ) allBusinessCardCount,
( SELECT count( * ) FROM customer WHERE company_id = a.company_id AND is_del = 0 ) allCustomerCount,
IF
( c.NAME IS NULL, from_base64 ( b.nicke_name ), c.NAME ) NAME,
IF
( c.phone IS NULL, b.phone, c.phone ) phone
FROM
company a
LEFT JOIN user b ON a.user_id = b.user_id
LEFT JOIN business_card c ON a.user_id = c.user_id
AND c.is_del = 0
AND c.state = 1
LEFT JOIN company_service d ON a.company_id = d.company_id
AND d.type = 1
WHERE
a.company_id > 0
and a.is_del = 0
order by a.create_time desc
執行計劃如下所示：
uj5u.com熱心網友回復：
試試下面呢
SELECT
a.company_id,
a.company_name,
c.personal_wx,
a.create_time,
d.end_time,
a.agent_company_id,
d.available_num,
d.current_num,
a.is_agent,
a.company_type,
a.agent_money,
allBusinessCardCount,
allCustomerCount,
IF
( c.NAME IS NULL, from_base64 ( b.nicke_name ), c.NAME ) NAME,
IF
( c.phone IS NULL, b.phone, c.phone ) phone
FROM
company a
LEFT JOIN user b ON a.user_id = b.user_id
LEFT JOIN business_card c ON a.user_id = c.user_id
AND c.is_del = 0
AND c.state = 1
LEFT JOIN company_service d ON a.company_id = d.company_id
AND d.type = 1
LEFT JOIN (SELECT COUNT(*) AS allBusinessCardCount,COMPANY_ID FROM business_card WHERE state = 1 AND is_del = 0 GROUP BY COMPANY_ID) e ON A.COMPANY_ID=E.COMPANY_ID
LEFT JOIN (SELECT count( * ) AS allCustomerCount,COMPANY_ID FROM customer WHERE is_del = 0 GROUP BY COMPANY_ID) f ON A.COMPANY_ID=F.COMPANY_ID
WHERE
a.company_id > 0
and a.is_del = 0
order by a.create_time desc
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/186715.html
標籤：疑難問題
上一篇：用戶在輸入框里輸入性別男，存到資料庫sex欄位的資料變成1，性別女自動變成2，SQL陳述句要怎么寫啊
下一篇：Java監聽器的四種實作方法