ืืื ืฆื ืืืึทืงืกื 10 ืืื ืื ื ืืืขืจ ืคืื ืคึฟืจืืื ืฆื ืื ืืึทืืึทืืืืก ืึธื ืืึทื ืฆื ืึท ืืขืจ ืคึผืจืึธืืืงืืืื ืกืขืจืืืขืจ ืืื ืืึทืืื ืื ืคืึทื ืืงืฉืึทื ืึทืืืื ืคืื ืื ืกืืกืืขื? ืืื ืืืขื ืืึธืื ืืืจ ืืื ืืืจ ืืึธืื ืืขืื ืืื ืื ืึทืจืึธืคึผืืืื ืืื ืื ืคืึธืจืฉืืขืืื ื ืคืื ืืื ืืืขืจ ืืึทืืึทืืืืก, ืืื ืืืจ ืึธืคึผืืืืืืขื SQL ืงืืืืจืื ืฆื ืืื ืขื ืืื ืคืืืข ืืืืขืจื ืืื ืืขืืืขื ืืื ื ืืฉื ืคืึทืจืืจืขืกืขืจื ืื ืงืึธืก ืคืื ืงืึทืืคึผืืืืื ื ืจืขืกืืจืกื.
ืืื ืืึทืื ืึท ืืื ืกื ืคึฟืึทืจ ืึธื ืคืืจืื ื ืืขืฉืขืคื ืคึผืจืึทืกืขืกืึทื ืืื ืงืึทื ืกืืจืึทืงืฉืึทื ืงืึธืืคึผืึทื ืืขืก. ืืืขืื ืงืกื ืืืงืก ืืืืื ื ืงืึธืืคึผืึทื ืืขืก ืึทืจืืขื ืืื ืืื ืื. ืืขืจ ืืื 3 ืืืืื ื ืืขื ืืฉื ืึทืจืืขืื ืืื ืืื ืืืขืจ ืกืืกืืขื ืืขืืขืจ ืืึธื ืคึฟืึทืจ 10-4 ืฉืขื. ืขืก ืกืึทืืืื ืคืึทืจืฉืืื ืคึผืจืึธืืืขืืก ืคืื ืคึผืืึทื ืืจืื ื, ืึธื ืืึธื, ืืืืจืขื ืื ื, ืืืึทืืึทืืืืฉืึทื ... ืืืจ ื ืืฆื PostgreSQL 10. ืืืจ ืืึธืื ืืืขืื 9.6 ืืืฉื ืืื ืื ืืึทืืึทืืืืก ืืื ืึทืจืืืฃ ืฆื 300 ืืืืืึธื ืคึฟืจืืื (200 ืืืืื ื ืคืึทืจืฉืืืขื ืข ืึธื ืขืก) ืืขื ืขื ืืืงืืืขื ืืขืืขืจ ืืึธื. ืืื ืืืจืืฉื ืืืืขื ืืืจ ืืึธืื 10-3 ืืืืื ื ืจืืงืืืขืก ืคึผืขืจ ืกืขืงืื ืืข, ืืื ืื ืืขืจืกื ืึทืงืืืื ืืึธืืืึทื ืฅ ืืขืจ ืืื 4 ืืืืื ื ืจืืงืืืขืก ืคึผืขืจ ืกืขืงืื ืืข. ืจืืึฟ ืคืื ืื ืคึฟืจืืื ืืขื ืขื OLAP. ืขืก ืืขื ืขื ืคืื ืืืืื ืืงืขืจืข ืึทืืืฉืึทื ื, ืืึธืืืคืืงืึทืืืึธื ืก ืืื ืืืืืฉืึทื ื, ืืืึทืืฉ ืื OLTP ืืึทืกืข ืืื ืืขืคืืขืจืขื ืืืื. ืืื ืืึธื ืฆืืืขืฉืืขืื ืึทืืข ืื ื ืืืขืจื ืึทืืื ืึทื ืืืจ ืงืขื ืขื ืึทืกืกืขืกืก ืื ืืืึธื ืคืื ืืื ืืืขืจ ืคึผืจืืืขืงื ืืื ืคึฟืึทืจืฉืืืื ืืื ื ืืฆืืง ืืื ืืืขืจ ืืขืจืคืึทืจืื ื ืงืขื ืขื ืืืื ืคึฟืึทืจ ืืืจ.
ืืืื ืืืื ืขืจ. ืืืจืืงืึทื
ืืืขื ืืืจ ืกืืึทืจืืขื ืึทื ืืืืืงืืื ื, ืืืจ ืืึธื ื ืื ืืึทืงืข ืืจืึทืืื ืืืขืื ืืืึธืก ืกืึธืจื ืคืื ืืึทืกืข ืืืึธืื ืคืึทืื ืืืืฃ ืื ืืึทืืึทืืืืก ืืื ืืืึธืก ืืืจ ืืืึธืื ืืึธื ืืืื ืืขืจ ืกืขืจืืืขืจ ืืื ืืขืืืขื ืกืืึทืคึผื. ืืืขื ืืืืืื ืื ื ืื ืืึทืืึทืืืืก, ืืืจ ื ืืืืขืืื ืืขื ืึทืืืขืืืื ืข ืจืขืงืึทืืึทื ืืืืฉืึทื ื ืืื ืืขืคืจืืืื ื ืืฉื ืฆื ืืจืืืขื ืืื ืืื ืื ืคึฟืืก, ืึธืืขืจ ืืขืืื ืืขื ืืืืึทืืขืจ ืคืื ืึทืืืขืืืื ืขืฆื ืืื "ืืึธื ื ืื ื ืืฆื ืืขื ืืืกืืขืจ ืืืจ ืืขื ืขื ื ืืฉื ืึทืจืฒึทื. ืืืจ ืืืืืื ื ืืืืืจื ืืืืฃ ืื ืคึผืจืื ืกืึทืคึผืึทืื ืคืื ื ืึธืจืืึทืืืืืืฉืึทื, ืืืกืืืืื ืืึทืื ืืืขืจืืงืืึทื ืืื ืืึธืื ื ืื ืืึธืจืื ืืืขืื ืคืึทืจืืืืขืจื ืืืืขืจ ืคึฟืจืืื. ืืื ืืึทืื ืืื ืืขืจ ืขืจืฉืืขืจ ื ืืฆืขืจืก ืื ืืขืงืืืขื, ืืืจ ืืขืคึผืืึธื ืืขืจื ืึท ืคืึธืจืฉืืขืืื ื ืคึผืจืึธืืืขื. ืฐ ื ืืขืฐืฒื ืืข ื ืืขื ืข ื ืื ืจ ืืขืฐืข ื ืืื ืืื ืฆ ื ืืืืืขืืจืฒ ื ืฆ ื ืืข ื . ื ื ืขืจืฉื ืข ืคืจืืืืขืืข ื ืืื ื ืื ื ืืจืืืกืืขืฐืืื , ืฐ ื ืคึผืฉืื . ืืื ืึท ืืขืจืฉื, ืึทืืฅ ืืื ืกืึทืืืื ืืืจื ืึทืืื ื ืึท ื ืืึท ืืื ืืขืงืก. ืืืขืจ ืขืก ืืขืงืืืขื ืึท ืฆืืึทื ืืืขื ืคึผืฉืื ืคึผืึทืืฉืึทื ืคืืจืฉืืืคื ืืจืืขืื. ืืืจ ืืึธืื ืืืื ืืขืืขื ืึทื ืืืจ ืคืขืื ืืขืจืคืึทืจืื ื ืืื ืขืก ืืื ืื ืงืจืืกืื ืืื ืฉืืืขืจ ืคึฟืึทืจ ืืื ืื ืฆื ืคึฟืึทืจืฉืืืื ืืืึธืก ืืื ืืขืคึฟืืจื ืื ืคืจืืืืขืืขื, ืืืจ ืืืืขืจื ืกืคึผืขืฉืึทืืึทืกืฅ ืืืึธืก ืืขืืึธืืคึฟื ืืื ืื ืฉืืขืื ืื ืกืขืจืืืขืจ ืจืืืืืง, ืคืึทืจืืื ืื ืืึธื ืืืึธืจืื ื ืืื ืืขืืืืื ืืื ืฆื ืงืืงื ืฆื ืืึทืงืืืขื .
ืืืื ืฆืืืื. ืกืืึทืืืกืืืฉ
ืึทืืื ืืืจ ืืึธืื ืืืขืื 10 ืืืืื ื ืคืึทืจืฉืืืขื ืข ืคึฟืจืืื ืืืึธืก ืืขื ืขื ืขืงืกืึทืงืืืืึทื ืืืืฃ ืืื ืืืขืจ ืืึทืืึทืืืืก ืคึผืขืจ ืืึธื. ืคืื ืื 10 ืืืืื ื, ืขืก ืืขื ืขื ืืึธื ืกืืขืจืก ืืืึธืก ืืขื ืขื ืขืงืกืึทืงืืืืึทื 2-3 ืืืืืึธื ืืื ืืื ืึท ืืืจืืฉื ืืืืขื ืืืจืืคืืจืื ื ืฆืืื ืคืื 0.1-0.3 ืืื, ืืื ืขืก ืืขื ืขื ืคึฟืจืืื ืืื ืึท ืืืจืืฉื ืืืืขื ืืืจืืคืืจืื ื ืฆืืื ืคืื 30 ืกืขืงืื ืืขืก ืืืึธืก ืืขื ืขื ืืขืจืืคื 100 ืืื ืึท ืืึธื.
ืขืก ืืื ืืขืืืขื ื ืื ืืขืืืขื ืฆื ืึทืคึผืืึทืืืื ืึทืืข 10 ืืืืื ื ืงืืืืจืื, ืึทืืื ืืืจ ืืึทืฉืืึธืกื ืฆื ืืขืคึฟืื ืขื ืืืืก ืืื ืฆื ืคืืจื ืืื ืืืขืจ ืืฉืชืืืืช ืืื ืกืืจ ืฆื ืคึฟืึทืจืืขืกืขืจื ืื ืคืึธืจืฉืืขืืื ื ืคืื ืื ืืึทืืึทืืืืก ืจืืืืืง. ื ืึธื ืขืืืขืืข ืืืขืจืืืฉืึทื ื, ืืืจ ืื ืืขืืืืื ืฆื ืืืืื ืจืืงืืืขืก ืืื ืืืืคึผืก.
ืฉืคึผืืฅ ืจืืงืืืขืก
ืืึธืก ืืขื ืขื ืื ืืขืืืืึทืกื ืคึฟืจืืื ืืืึธืก ื ืขืืขื ืื ืืขืจืกื ืฆืืื (ืืึทื ืฅ ืฆืืื). ืืึธืก ืืขื ืขื ืคึฟืจืืื ืืืึธืก ืืขื ืขื ืืขืจืืคึฟื ืืืืขืจ ืึธืคื ืึธืืขืจ ืคึฟืจืืื ืืืึธืก ื ืขืืขื ืึท ืืืืขืจ ืืึทื ื ืฆืืื ืฆื ืืืกืคืืจื (ืืึทื ื ืืื ืึธืคื ืคึฟืจืืื ืืขื ืขื ืึธืคึผืืืืืืขื ืืื ืืขืจ ืขืจืฉืืขืจ ืืืขืจืืืฉืึทื ื ืคืื ืืขื ืงืึทืืฃ ืคึฟืึทืจ ืืืืงืืึทื). ืืื ืึท ืจืขืืืืืึทื, ืืขืจ ืกืขืจืืืขืจ ืกืคึผืขื ืื ืื ืืขืจืกื ืฆืืื ืืืืฃ ืืืืขืจ ืืืจืืคืืจืื ื. ืืขืจืฆื, ืขืก ืืื ืืืืืืืง ืฆื ืืึทืืื ืืขืจ ืื ืฉืคึผืืฅ ืจืืงืืืขืก ืืืจื ืืึทื ืฅ ืืืจืืคืืจืื ื ืฆืืื ืืื ืกืขืคึผืขืจืึทืืื ืืืจื IO ืฆืืื. ืื ืืขืืืึธืืก ืคึฟืึทืจ ืึธืคึผืืืืืืื ื ืึทืืึท ืคึฟืจืืื ืืขื ืขื ืึท ืืืกื ืึทื ืืขืจืฉ.
ืื ืืขืืืืื ืืืขื ืคืืจ ืคืื ืึทืืข ืงืึธืืคึผืึทื ืืขืก ืืื ืฆื ืึทืจืืขืื ืืื ืฉืคึผืืฅ ืจืืงืืืขืก. ืขืก ืืขื ืขื ืืืืื ืืง ืคืื ืืื; ืึธืคึผืืืืืืื ื ืืคืืื ืืืื ืึธื ืคึฟืจืขื ืงืขื ืขื ืคืจืื ืึทืจืืืฃ 5-10% ืคืื ืจืขืกืืจืกื. ืึธืืขืจ, ืืื ืื ืคึผืจืืืขืงื ืืึทืืืืจื, ืึธืคึผืืืืืืื ื ืฉืคึผืืฅ ืงืืืืจืื ืืืขืจื ืึท ืื ืงืจืืกืื ืืื ื ืื-ื ืืืืืืึทื ืึทืจืืขื. ืึทืืข ืคึผืฉืื ืืขืืืึธืืก ืืึธืื ืฉืืื ืืขืืจืืขื ืืืืก, ืืื ืื ืืขืจืกื "ืฉืืืขืจ" ืืขืื ื ืขืื "ืืืืื" 3-5% ืคืื ืจืขืกืืจืกื. ืืืื ืื ืฉืคึผืืฅ ืงืืืืจืื ืืื ืืึทื ืฅ ื ืขืืขื ืืืืื ืืงืขืจ ืืื 30-40% ืคืื ืื ืฆืืื, ืจืืึฟ ืืกืชึผืื ืืืจ ืืึธื ืฉืืื ืืขืืืื ืืฉืชืืืืช ืฆื ืืึทืื ืืื ืึทืจืืขื ืืขืฉืืืื ื ืืื ืขืก ืืื ืฆืืื ืฆื ืคืึธืจืืขืฆื ืฆื ืึธืคึผืืืืืืืจื ืคึฟืจืืื ืคืื ืืขืจ ืืืืึทืืขืจ ืืจืืคึผืข.
ืขืก ืืืืืื ืฆื ืขื ืืคึฟืขืจื ืื ืงืฉืื ืคืื ืืื ืคืืืข ืฉืคึผืืฅ ืงืืืืจืื ืืึธื ืืืื ืึทืจืืึทื ืืขืจืขืื ื ืืื ืืขื ืืจืืคึผืข. ืืื ืืืืฉืึทืืืึทืื ื ืขืืขื ืืืึท ืืื ืืกืืขืจ 10, ืึธืืขืจ ื ืื ืืขืจ ืืื 20. ืืื ืคึผืจืืืืจื ืฆื ืขื ืฉืืจ ืึทื ืื ืฆืืื ืคืื ืืขืจ ืขืจืฉืืขืจ ืืื ืืขืฆืืข ืืื ืื ืฉืคึผืืฅ ืืจืืคึผืข ืืื ื ืืฉื ืืขืจ ืืื 10 ืืื. ืึทื ืืื, ืืืื ืื ืึธื ืคึฟืจืขื ืืืจืืคืืจืื ื ืฆืืื ืืจืึทืคึผื ืฉืืจืฃ ืคืื 1 ืึธืจื ืฆื 10, ืืื ื ืขืืขื TOP-10, ืืืื ืื ืงืึทืคึผ ืืื ืืขืจ ืืจืึทืืืฉืืึทื, ืืื ืคืึทืจืืจืขืกืขืจื ืื ืืจืืคึผืข ืืจืืืก ืฆื 15 ืึธืืขืจ 20.
ืืืืขื ืข ืคืืืขืจืื
ืืึธืก ืืขื ืขื ืึทืืข ืจืืงืืืขืก ืืืึธืก ืงืืืขื ืืืืื ื ืึธื TOP, ืืื ืื ืืืกื ืขื ืคืื ืื ืืขืฆืืข 5-10%. ืืืืฉืึทืืืึทืื, ืืื ืึธืคึผืืืืืืื ื ืื ืงืืืืจืื ืืืื ืื ืืขืืขืื ืืืื ืฆื ืฉืืืจืง ืคืึทืจืืจืขืกืขืจื ืกืขืจืืืขืจ ืคืึธืจืฉืืขืืื ื. ืื ืจืืงืืืขืก ืงืขื ืขื ืืืขืื ืึทืจืืืฃ ืฆื 80%. ืึธืืขืจ ืืคืืื ืืืื ืืืืขืจ ืืืืื ืืื ืืงืกืื 50%, ืขืก ืืื ืฆืืื ืฆื ืงืืงื ืืืืฃ ืืื ืืขืจ ืงืขืจืคืึทืื.
ืขืง
ืืื ืืขืจืืื ื, ืื ืคึฟืจืืื ืงืืืขื ืืื ืื ืกืืฃ ืืื ื ืขืืขื 5-10% ืคืื ืื ืฆืืื. ืืืจ ืงืขื ืขื ืคืึทืจืืขืกื ืืืขืื ืืื ืืืืื ืืืื ืืืจ ืืึธื ื ืื ื ืืฆื ืึธืืึทืืึทืืืง ืึธื ืคึฟืจืขื ืึทื ืึทืืืกืืก ืืืฉืืจืื, ืืื ืึธืคึผืืืืืืื ื ืืื ืงืขื ืขื ืืืื ืืืื ืืืืืง.
ืืื ืฆื ืึธืคึผืฉืึทืฆื ืืขืืขืจ ืืจืืคึผืข?
ืืื ื ืืฆื ืึท SQL ืึธื ืคึฟืจืขื ืืืึธืก ืืขืืคึผืก ืืึทืื ืึทืืึท ืึทื ืึทืกืขืกืืึทื ื ืคึฟืึทืจ PostgreSQL (ืืื ืืื ืืืืขืจ ืึทื ืึท ืขื ืืขื ืึธื ืคึฟืจืขื ืงืขื ืขื ืืืื ืืขืฉืจืืื ืคึฟืึทืจ ืคืืืข ืื ืืขืจืข ืืืืก)
SQL ืึธื ืคึฟืจืขื ืฆื ืึธืคึผืฉืึทืฆื ืื ืืจืืืก ืคืื TOP-MEDIUM-TAIL ืืจืืคึผืขืก
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
ืืขืจ ืจืขืืืืืึทื ืคืื ืื ืึธื ืคึฟืจืขื ืืื ืืจืื ืฉืคืืืื, ืืขืืขืจ ืคืื ืืืึธืก ืึผืืื ืื ืคึผืจืึธืฆืขื ื ืคืื ืฆืืื ืขืก ื ืขืื ืฆื ืคึผืจืึทืกืขืกืื ื ืคึฟืจืืื ืคึฟืื ืืขืจ ืืจืืคึผืข. ืื ืืขืจ ืืงืฉื ืขืก ืืขื ืขื ืฆืืืื ื ืืืขืจื (ืืื ืืืื ืคืึทื ืขืก ืืื 20 ืืื 800) ืืืึธืก ืืึทืืื ืืขืจ ืจืืงืืืขืก ืคืื ืืืื ืืจืืคึผืข ืคืื โโืื ืืขืจื.
ืืึธืก ืืื ืืื ืื ืฉืึทืจืขืก ืคืื ืจืืงืืืขืก ืืขืขืจืขื ืคืึทืจืืืืึทืื ืืื ืืขืจ ืฆืืื ืืืึธืก ืึทืคึผืืึทืืึทืืืืฉืึทื ืึทืจืืขื ืื ืืขืืืืื ืืื ืืืฆื.
ืื ืืืึทืืจืึทืืข ืืืืืื ืึทื ืื ืืืืื ืคืื ืฉืคึผืืฅ ืจืืงืืืขืก ืืื ืฉืืจืฃ ืืืงืจืืกื, ืึธืืขืจ ืื "ืืืื ืคืืืขืจืื" ืืึธืื ืืขืืืืงืกื.
ืืื ืขืจืฉืืขืจ, ืื ืฉืคึผืืฅ ืจืืงืืืขืก ืึทืจืืึทื ืืขืจืขืื ื ืืืืืืึทื ื ืืืึธื ืืขืจืก. ืืื ืืขืจ ืฆืืื ืืขื ืขื ืงืื ืืฉืึทืคื ืงืจืขื ืง ืคืึทืจืฉืืืื ืื, ืื ืืืืื ืคืื TOP ืจืืงืืืขืก ืืื ืืืงืจืืกื, ืืื ืืขืจ ืืื ืืขืจ ืืฉืชืืืืช ืืึธืื ืฆื ืคืึทืจืืืืขืจื ืฉืืืขืจ ืจืืงืืืขืก.
ืฆื ืืึทืงืืืขื ืืขื ืืขืงืกื ืคืื ืจืืงืืืขืก ืืืจ ื ืืฆื ืื ืคืืืืขื ืืข ืืขืื
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
ืืึธ ืืื ืึท ืจืฉืืื ืคืื ืื ืืขืจืกื ืงืึทืืึทื ืื ืืขืืืืื ื ืืขืงื ืืงืก ืืืึธืก ืืขืืึธืืคึฟื ืืื ืื ืคืึทืจืืืืขืจื ืื ืฉืคึผืืฅ ืคึฟืจืืื:
- ืจืืืืืืื ืคืื ืื ืกืืกืืขื, ืืืฉื, ืจืืืืขืจืงืื ื ืื ืึธื ืืึธื ืืึธืืืง ืืื ืึท ืึธื ืืึธื ืืขืงืืขืจ ืึทื ืฉืืึธื ืคืื ืคึผืขืจืืึธืืืฉ ืคึฟืจืืื ืฆื ืื ืืึทืืึทืืืืก
- ืึทืืื ื ืึธืืขืจ ืืฉืึทื ืืื ื ืื ืืขืงืกืื
- ืจืืจืืืืื ื ORM ืคึฟืจืืื ืฆื ืจืืื SQL
- ืจืืจืืืืื ื ืคืืื ืืึทืื ืืึธืืืื ื ืืึธืืืง
- ืงืึทืืฉืื ื ืืืจื ืืึทืื ืืื ืึธืจืืึทืืืืืืฉืึทื. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืืจ ืืึธืื ืึท ืืืฉ ืงืฉืจ ืขืงืกืคึผืจืขืก -> ืื ืืืืืก -> ืืขืื -> ืึทืคึผืคึผืืืงืึทืืืึธื. ืึทื ืืื, ืืขืืขืจ ืขืงืกืคึผืจืขืก ืืื ืคืืจืืื ืื ืืื ืึท ืึทืคึผืืึทืงืืืฉืึทื ืืืจื ืื ืืขืจืข ืืืฉื. ืึผืื ื ืืฉื ืฆื ืคึฟืึทืจืืื ืื ืึทืืข ืืืฉื ืืื ืืขืืขืจ ืืงืฉื, ืืืจ ืืืคึผืืืงืืืืื ืื ืืื ืง ืฆื ืื ืืขืื ืืื ืื ืขืงืกืคึผืจืขืก ืืืฉ.
- ืงืึทืืฉืื ื ืกืืึทืืืง ืืืฉื ืืื ืจืขืคึฟืขืจืขื ืฅ ืืืืขืจ ืืื ืจืึทืจืขืื ืืฉืึทื ืืื ื ืืืฉื ืืื ืคึผืจืึธืืจืึทื ืืึผืจืื.
ืืื ืื ืขื ืืขืจืื ืืขื ืึทืืึทืื ืื ืฆื ืึท ืืืคึผืจืขืกืืื ืจืืืืืืื, ืึธืืขืจ ืืื ืฆืืืขืฉืืขืื 5-10% ืคืื ืื ืกืืกืืขื ืืึทืกืข ืืื ืืขื ืขื ืืขืจืขืืืคืืจืืืงื. ืืื ืืขืจ ืฆืืื ืืื ืืขืจ ืืืืกืืืก ืืขืืืืจื ืงืืขื ืขืจ ืืื ืงืืขื ืขืจ, ืืื ืืขื ืืื ืคืืจืืื ืื ืืขืจ ืืื ืืขืจ ืขืจื ืกืืข ืจืขืืืืืื.
ืื ื ืืื ื ืื ืจ ืื ื ืฆืืืขืงืขืจ ื ืืื ืฃ ืืข ืจ ืฆืฐืฒืืข ืจ ืืจืืค ืข ืืงืฉืืช - ื ื ืืจืืค ืข ืืืืขื ืข ืคืืืขืจืื . ืขืก ืืขื ืขื ืคืืืข ืืขืจ ืคึฟืจืืื ืืื ืขืก ืืื ืขืก ืืขืืืขื ืึทื ืขืก ืืืึธืื ื ืขืืขื ืึท ืคึผืืึทืฅ ืคืื ืฆืืื ืฆื ืึทื ืึทืืืื ืื ืืื ืฆืข ืืจืืคึผืข. ืึธืืขืจ, ืจืืึฟ ืคึฟืจืืื ืืขื ืขื ืืขืืืขื ืืืืขืจ ืคึผืฉืื ืฆื ืึทืคึผืืึทืืืื, ืืื ืคืืืข ืคึผืจืึธืืืขืืก ืืขื ืขื ืจืืคึผืืืื ืืึทืืึทื ื ืคืื ืืื ืืื ืคืึทืจืฉืืืขื ืข ืืืขืจืืืืฉืึทื ื. ืืึธ ืืขื ืขื ืืืืฉืคืืื ืคืื ืขืืืขืืข ืืืคึผืืฉ ืึธืคึผืืืืืืึทืืืึธื ืก ืืืึธืก ืืืจ ืืึธืื ืืขืืืขื ืื ืฆื ืืึทืืึทื ื ืคืื ืขื ืืขื ืคึฟืจืืื ืืื ืืขืืขืจ ืืจืืคึผืข ืคืื โโืึธืคึผืืืืืืขื ืงืืืืจืื ืึทื ืืึธืื ืื ืืึทืืึทืืืืก ืืื 3-5%.
- ืึทื ืฉืืึธื ืงืึธื ืืจืึธืืืจืื ื ืคึฟืึทืจ ืื ืืืึทืืืึทื ืคืื ืจืขืงืึธืจืืก ื ืืฆื COUNT ืืื ืึท ืคืื ืืืฉ ืืืขืจืงืืงื, EXISTS ืื ืืขืืืืื ืฆื ืืืื ืืขืืืืื ื
- ืืึทืงืืืขื ืืึทืคืจืืึทืขื ืคืื DISTINCT (ืขืก ืืื ืงืืื ืืขื ืขืจืึทื ืจืขืฆืขืคึผื, ืึธืืขืจ ืืื ืืืจ ืงืขื ืขื ืืืืื ืืึทืงืืืขื ืืึทืคืจืืึทืขื ืคืื ืขืก ืืืจื ืกืคึผืืืื ื ืื ืืงืฉื ืืื 10-100 ืืื).
ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืึทื ืฉืืึธื ืคืื ืึท ืึธื ืคึฟืจืขื ืฆื ืืืืกืงืืืึทืื ืึทืืข ืืจืืืืขืจืก ืคืื ืึท ืืจืืืก ืืืฉ ืคืื ืืืืืืืขืจืื (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDืืขืืืื ืึท ืึธื ืคึฟืจืขื ืืืืฃ ืึท ืืขืคืืขืจืขื ืงืืืื ืืืฉ ืืขื ืืฉ
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)ืขืก ืืืึธืื ืืืกืงืืืขื ืึทื ืืืจ ืืึธืื ืืขืืืืื ื ืึท ืงืึธืจืึทืืืืืึทื ืกืึทืืงืืืขืจื, ืึธืืขืจ ืขืก ืืื ืึท ืกืคึผืืืึทืคึผ ืคืื ืืขืจ ืืื 10 ืืื.
- ืืื ืคืืืข ืงืึทืกืขืก, COUNT ืืื ืืขืืืขื ืคืืจืืืื ืืขืกืึทืืึทืงื ืืื
- ืึทื ืฉืืึธื ืคืื
UPPER(s) LIKE JOHN%โื ืืฆื
s ILIKE โJohn%โ
ืืขืืขืจ ืกืคึผืขืฆืืคืืฉ ืืงืฉื ืืื ืืื ืกืคึผืืื ืึทืจืืืฃ ืืื 3-1000 ืืื. ืืจืึธืฅ ืื ืืืคึผืจืขืกืืื ืคืึธืจืฉืืขืืื ื, ืืื ืขืจืฉืืขืจ ืขืก ืืขืืืขื ืฆื ืืื ืื ืึทื ืขืก ืืื ืงืืื ืคืื ื ืืื ืึธืคึผืืืืืืื ื ืึท ืึธื ืคึฟืจืขื ืืืึธืก ื ืขืื 10 ืืื ืฆื ืคืึทืจืขื ืืืงื, ืืื ืืืื ืขืจ ืคืื ืื 3rd ืืื ืืขืจื ืืขืืืืึทืกื ืงืืืืจืื ืืื ื ืขืื ืึท ืืื ืืขืจืืกื ืคืื ืึท ืคึผืจืึธืฆืขื ื ืคืื ืื ืงืืืืขืืืืง ืืึทืืึทืืืืก ืืึทืกืข ืฆืืื. ืึธืืขืจ ืืืจื ืึทืคึผืืืืื ื ืื ืืขืืืข ืจืขืฆืขืคึผื ืฆื ืึท ืืจืืคึผืข ืคืื โโืคึฟืจืืื ืคืื ืืขืจ ืืขืืืืงืขืจ ืืืคึผ, ืืืจ ืืืึทื ืฆืืจืืง ืึท ืืืกื ืคึผืจืึธืฆืขื ื. ืึผืื ื ืืฉื ืฆื ืืืืกื ืฆืืื ืืึทื ืืืึทืื ืจืืืืืืื ื ืึทืืข ืืื ืืขืจืืขืจ ืคืื ืคึฟืจืืื, ืืืจ ืืขืฉืจืืื ืขืืืขืืข ืคึผืฉืื ืกืงืจืืคึผืก ืืืึธืก ืืขืืืืื ื ืจืขืืืืขืจ ืืืืกืืจืืงื ืฆื ืืขืคึฟืื ืขื ืคึฟืจืืื ืคืื ืืขืจ ืืขืืืืงืขืจ ืืืคึผ. ืืื ืึท ืจืขืืืืืึทื, ืืืืืึธืืึทืืืฉ ืืืื ืืจืืคึผืขืก ืคืื ืคึฟืจืืื ืขืจืืืืื ืืื ืื ืฆื ืคึฟืึทืจืืขืกืขืจื ืืื ืืืขืจ ืคืึธืจืฉืืขืืื ื ืืื ืืึทืฉืืืื ืื.
ืืื ืึท ืจืขืืืืืึทื, ืืืจ ืืึธืื ืฉืืื ืืจืืขืื ืืืืฃ ืืขืจ ืืขืืืืงืขืจ ืืึทืื ืืืึทืจื ืคึฟืึทืจ ืืจืื ืืึธืจ ืืืฆื. ืื ืืืจืืฉื ืืืืขื ืืขืืืขื ืืึทืกืข ืืื ืืืขืื 30%, ืืื ืคึผืืงืก ืขืก ืจืืืฉืึทื 70%. ืื ื ืืืขืจ ืคืื ืจืืงืืืขืก, ืืื ืืขืืื ื ืืื ืื ื ืืืขืจ ืคืื ื ืืฆืขืจืก, ืืื ืืขืืืืงืกื ืืขืขืจืขื 10 ืืื. ืืื ืึทืืข ืืขื ืืึทื ืง ืฆื ืื ืงืขืกืืืืขืจืืืง ืืึธื ืืืึธืจืื ื ืคืื ืื ืืืืขืจ ืืจืืคึผืขืก ืคืื ืฉืคึผืืฅ-ืืืื ืจืืงืืืขืก. ืืื ืืึทืื ืืื ืึท ื ืืึทืข ืืงืฉื ืืื ืืจืืืก ืืื ืื ืฉืคึผืืฅ ืืจืืคึผืข, ืืืจ ืืืืื ืึทื ืึทืืืื ืขืก ืืื ืคึผืจืืืืจื ืฆื ืคืึทืจืืืืขืจื ืขืก. ืืืจ ืึธืคึผืฉืึทืฆื ืื ืืขืืืื ืืจืืคึผืข ืึทืืึธื ืึท ืืืึธื ืืื ืึธื ืคึฟืจืขื ืึทื ืึทืืืกืืก ืกืงืจืืคึผืก. ืืืื ืืืจ ืืจืขืคื ื ืืึทืข ืคึฟืจืืื ืืืึธืก ืืืจ ืฉืืื ืืืืกื ืืื ืฆื ืึทืคึผืืึทืืืื, ืืืจ ืืขืฉืืืื ื ืืืืฉื ืืื. ืืื ืืืจ ืืขืคึฟืื ืขื ื ืืึทืข ืึทืคึผืืึทืืึทืืืืฉืึทื ืืขืืืึธืืก ืืืึธืก ืงืขื ืขื ืืืื ืืขืืืขื ืื ืฆื ืขืืืขืืข ืคึฟืจืืื ืืื ืึทืืึธื.
ืืืื ืืื ืืืขืจ ืคืึธืจืงืึทืกืฅ, ืื ืงืจืึทื ื ืกืขืจืืืขืจ ืืืขื ืืืืืกืืึทื ื ืึท ืคืึทืจืืจืขืกืขืจื ืืื ืื ื ืืืขืจ ืคืื ื ืืฆืขืจืก ืืื ื ืึธื 3-5 ืืื. ืืืช, ืืืจ ืืึธืื ื ืึธื ืืืื ืืืื ืืื ืืื ืืืขืจ ืึทืจืื - ืืืจ ื ืึธื ืืึธืื ื ืืฉื ืืจืึทื ืกืคืขืจื SELECT ืคึฟืจืืื ืฆื ืืขืจ ืฉืคึผืืื, ืืื ืจืขืงืึทืืขื ืืื. ืึธืืขืจ ืืืจ ืืึธื ืืึธืก ื ืืฉื ืงืึทื ืฉืึทืกืื, ืืืืึทื ืืืจ ืืืขืื ืขืจืฉืืขืจ ืืึธืจ ืืืกืืึทืืขืจื ืื ืคึผืึทืกืึทืืืืึทืืื ืคืื "ืงืืื" ืึธืคึผืืืืืืึทืืืึธื ืืืืืขืจ ืืืขื ืื ืืืืฃ ืื "ืฉืืืขืจ ืึทืจืืืืขืจืืข".
ื ืงืจืืืืฉ ืงืืง ืืื ืื ืึทืจืืขื ืืขืืื ืงืขื ืคึฟืึธืจืฉืืึธืื ื ืืฆื ืืืขืจืืืงืึทื ืกืงืืืืื ื. ืงืืืคื ืึท ืืขืจ ืฉืืึทืจืง ืกืขืจืืืขืจ ืึทื ืฉืืึธื ืคืื ืืืืกื ืื ืฆืืื ืคืื ืกืคึผืขืฉืึทืืึทืกืฅ. ืืขืจ ืกืขืจืืืขืจ ืงืขื ื ืืฉื ืงืึธืกืื ืึทืืื ืคืื, ืกืคึผืขืฆืืขื ืืื ื ืืืจ ืืึธืื ื ืืฉื ื ืึธื ืืืกืืขืืึทืืขืจื ืื ืืืืึทืฅ ืคืื ืืืขืจืืืงืึทื ืกืงืืืืื ื. ืึธืืขืจ, ืืืืื ืื ื ืืืขืจ ืคืื ืจืืงืืืขืก ืืขืืืืงืกื 10 ืืื. ืืื ืืืืฃ ืคืื ืขืืืขืืข ืืึธืจื, ืื ืคืึทื ืืงืฉืึทื ืึทืืืื ืคืื ืื ืกืืกืืขื ืืื ืืขืืืืงืกื ืืื ืืืฆื ืขืก ืืขื ืขื ืืขืจ ืืืืคึผืก ืคืื ืจืืงืืืขืก. ืืึทื ืง ืฆื ืงืึทืืฉืื ื, ืื ืคืึทื ืืงืฉืึทื ืึทืืืื ืืืึธืก ืืื ืืขืืืขื ืืืจืืืขืงืึธืื ืืื ืืืืื ืืงืขืจืข ืจืืงืืืขืก ืืื ืืขืจ ืขืคืขืงืืืื ืจืืงืืืขืก. ืืขื ืืืื ืืืจ ืงืขื ืขื ืืขืฉืึธืืขื ืืขืจื ืืื ื ืึธื 5 ืฆื ืืึทืงืืืขื ืื ืคืึทืงืืืฉ ืึทืงืกืขืืขืจืืืฉืึทื ืงืึธืืึทืคืืฉืึทื ื. ืึทืืื, ืืืื ืื ืืขืจืกื ืงืึธื ืกืขืจืืืึทืืืืืข ืขืกืืึทืืึทืฅ, ืืืจ ืงืขื ืขื ืืึธืื ืึทื ืื ืึทืงืกืขืืขืจืืืฉืึทื ืืื ืืขืืืขื 50 ืืื ืึธืืขืจ ืืขืจ. ืืืขืจืืืงืื ืกืืืื ืืื ื ืึท ืกืขืจืืืขืจ ืืืึธืื ืงืึธืกืื 50 ืืื ืืขืจ. ืกืคึผืขืฆืืขื ืงืึทื ืกืืืขืจืื ื ืึทื ืึทืืึธื ืึทืคึผืืึทืืึทืืืืฉืึทื ืืื ืืืจืืืขืงืึธืื ืขืก ืึทืจืืขื ืึทืืข ืื ืฆืืื, ืืื ืื ืจืขืืขื ืื ื ืคึฟืึทืจ ืื ืจืขื ืืึทื ืกืขืจืืืขืจ ืงืืื ืืขืืขืจ ืืืืฉ.
ืืงืืจ: www.habr.com