sql性能和高并发的取舍问题

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; 简单的用多表关联是可以的，如果遇到高并发，性能缺陷立即就出来

Answer 1

多表关联并不意味着sql高性能，太复杂的SQL效率损耗并不比外部锁等低，跟高并发也没什么冲突。

建议把语句拆成简单语句，使用锁和补偿机制来保障事务性。