公司最近有個deployment，要run個SQL patch。係QA 同staging 都冇事，點知一去production run左30mins 都未行完，terminated & rollback. 原來係其中一個update statement 行subquery，where id = (select …) mysql 會run select on each row 🙁 DataSet 一大，慢到嘔。
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE alarm_backup SELECT * FROM alarm; UPDATE alarm a SET a.status = 'active' , ....... WHERE a.id = ( SELECT al.id FROM alarm_backup ab JOIN device d ON ab.device_id = d.id JOIN ...... WHERE ..... ); |
其實subquery 係唔需要⋯⋯
|
1 2 3 4 5 6 7 |
CREATE TABLE alarm_backup SELECT * FROM alarm; UPDATE alarm a JOIN device d ON a.device_id = d.id JOIN ...... SET a.status = 'active' , ....... WHERE ..... ； |
故事教訓我地： 1) 放假返黎之後小心點，你唔係到個時既Peer review 都要睇返。 2) 唔好咁容易信你上司，上左神枱技術係會回塘 😂