java - trade-off between sql performance and high concurrency

Question

SELECT

        S.carOwnerID ,
        S.name,
        S.mobile,
        S.coopName,
        S.VIN,
        s.policyNO,
        S.effectiveDate,
        S.expiryDate,
        s.plateNo,
        (
            CASE
            WHEN s.num > 1 THEN
                1
            WHEN s.num = 1 THEN
                0
            END
        ) AS carState
    FROM
        (
            SELECT
                c.carOwnerID,
                c.name,
                c.mobile,
                c.coopName,
                c.VIN,
                p.policyNO,
                p.effectiveDate,
                p.expiryDate,
                c.plateNo,
                count(p.PlateNo) AS num
            FROM
                customer C
            LEFT JOIN policy P ON C.carOwnerID = P.carOwnerID
            WHERE
                date_add(
                    P.createTime,
                    INTERVAL 11.5 HOUR
                ) > NOW()
    
    ) s
    WHERE
        s.num > 0;

It is possible to simply use multi-table association. If you encounter high concurrency, performance defects will appear immediately

Answer 1

Multi-table association does not mean high performance of SQL. The efficiency loss of too complex SQL is no lower than external locks, etc., and there is no conflict with high concurrency.

It is recommended to split the statement into simple statements and use lock and compensation mechanisms to ensure transactionality.