多表聯(lián)合查詢 + 子查詢 + 條件查詢 + 排序
SELECT
ac.*,
c.car_number,
c.car_name,
cs.car_source,
cb.brand_name,
cm.car_model,
ct.car_type,
c.number_plate,
(
SELECT
COUNT(ar.auction_record_id)
FROM
csp_auction_record AS ar
WHERE
ac.auction_car_id = ar.auction_car_id
) AS auction_count
FROM
csp_auction_car AS ac
INNER JOIN
csp_car AS c
ON
ac.car_id = c.car_id
INNER JOIN
csp_car_brand AS cb
ON
c.car_brand_id = cb.car_brand_id
INNER JOIN
csp_car_source AS cs
ON
c.car_source_id = cs.car_source_id
INNER JOIN
csp_car_type AS ct
ON
c.car_type_id = ct.car_type_id
INNER JOIN
csp_car_model AS cm
ON
c.car_model_id = cm.car_model_id
WHERE
ac.auction_s_time <= CURRENT_TIMESTAMP AND ac.auction_e_time >= CURRENT_TIMESTAMP
ORDER BY
ac.auction_car_id
DESC
LIMIT 0, 10相關(guān)數(shù)據(jù)表：
csp_car_brand，車輛品牌表：
csp_car_type，車輛類型表：
csp_car_source，車輛來源表：
csp_car_model，車輛型號表（所屬品牌的具體型號）：
csp_car，車輛表（這張表字段太多，只列出部分）
csp_auction_record，拍賣紀(jì)錄表：
請問這條 sql 語句有性能問題嗎？？該如何優(yōu)化？？是用 php 來拆成一條條簡單sql語句，然后將結(jié)果組合，還是怎樣？？
1
0
0
擁有18年軟件開發(fā)和IT教學(xué)經(jīng)驗(yàn)。曾任多家上市公司技術(shù)總監(jiān)、架構(gòu)師、項(xiàng)目經(jīng)理、高級軟件工程師等職務(wù)。 網(wǎng)絡(luò)人氣名人講師，...
既然你這么問了，就表示你知道自己的sql有問題。問題在于csp_auction_car表有多少條記錄，csp_auction_record表就被掃描了多少次。想想就覺得可怕，但是我確實(shí)看到很多人喜歡這么寫。還有，where條件盡量寫在子查詢里面，ac表的數(shù)據(jù)量查出來會減少很多，跟后面的關(guān)聯(lián)會更快。
可以改成下面這樣，csp_auction_record表只被掃描1次
SELECT ac.*,
c.car_number,
c.car_name,
cs.car_source,
cb.brand_name,
cm.car_model,
ct.car_type,
c.number_plate,
IFNULL(ar.count, 0) AS auction_count
FROM (select *
from csp_auction_car
WHERE auction_s_time <= CURRENT_TIMESTAMP
AND auction_e_time >= CURRENT_TIMESTAMP) AS ac
INNER JOIN csp_car AS c
ON ac.car_id = c.car_id
INNER JOIN csp_car_brand AS cb
ON c.car_brand_id = cb.car_brand_id
INNER JOIN csp_car_source AS cs
ON c.car_source_id = cs.car_source_id
INNER JOIN csp_car_type AS ct
ON c.car_type_id = ct.car_type_id
INNER JOIN csp_car_model AS cm
ON c.car_model_id = cm.car_model_id
left outer join (select auction_car_id, count(*) count
from csp_auction_record
group by auction_car_id) ar
on ac.auction_car_id = ar.auction_car_id
ORDER BY ac.auction_car_id DESC LIMIT 0, 10
