php - Gibt es ein Leistungsproblem, wenn die SQL-Anweisung so geschrieben wird? ? Wie kann man sich verbessern? ?

Question

Mehrtabellen-Gemeinschaftsabfrage + Unterabfrage + bedingte Abfrage + Sortierung

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

Zugehöriges Datenblatt:

csp_car_brand, Fahrzeugmarkenliste:

csp_car_type, Fahrzeugtyptabelle:

csp_car_source, Fahrzeugquellentabelle:

csp_car_model, Fahrzeugmodelltabelle (spezifisches Modell der Marke):

csp_car, Fahrzeugtabelle (diese Tabelle hat zu viele Felder, nur einige sind aufgelistet)

csp_auction_record, Auktionsrekordliste:

Gibt es bei dieser SQL-Anweisung ein Leistungsproblem? ? Wie optimieren? ? Sollte ich PHP verwenden, um es in einfache SQL-Anweisungen aufzuteilen und dann die Ergebnisse zu kombinieren, oder was? ?

Answer 1

既然你这么问了，就表示你知道自己的sql有问题。问题在于csp_auction_car表有多少条记录，csp_auction_record表就被扫描了多少次。想想就觉得可怕，但是我确实看到很多人喜欢这么写。还有，where条件尽量写在子查询里面，ac表的数据量查出来会减少很多，跟后面的关联会更快。
可以改成下面这样，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