系統(tǒng)上線時SQL腳本的9大坑

系統(tǒng)上線時，非常容易出問題。

即使之前在測試環(huán)境，已經(jīng)執(zhí)行過SQL腳本了。但是有時候，在系統(tǒng)上線時，在生產(chǎn)環(huán)境執(zhí)行相同的SQL腳本，還是有可能出現(xiàn)一些問題。

有些小公司，SQL腳本是開發(fā)自己執(zhí)行的，有很大的風險。

有些大廠，有專業(yè)的DBA把關，但DBA也不是萬能的，還是有可能會讓一些錯誤的SQL腳本被生產(chǎn)環(huán)境執(zhí)行了，比如：update語句的順序不對。

今天跟大家一起聊聊，系，以便于大家吸取教訓，能夠防微杜漸，希望對你會有所幫助

1 漏腳本了

我們上線時執(zhí)行的SQL腳本，出現(xiàn)次數(shù)最多的問題，應該是漏腳本了。

• 有時候少加了一個字段。
• 有時候字段的注釋沒有及時修改。
• 有時候有些新表沒創(chuàng)建。
• 有時候字段類型忘了修改。

等等。

我們的SQL腳本中漏腳本的情況有很多。

那么，如何解決這個問題呢？

答：將SQL腳本做成代碼的一部分。在項目的代碼中，創(chuàng)建一個專門的sql目錄，在該目錄下根據(jù)每個迭代創(chuàng)建一個子目錄，比如：mv3.2.1，將SQL腳本存放到mv3.2.1下。

我們在開發(fā)環(huán)境任何對表的相關操作，比如：增加字段、修改字段類型、修改注釋、增加索引、創(chuàng)建表等等，都需要通過SQL語句操作，然后把該SQL語句，整理到SQL腳本中。

最后提交到公司的GitLab上，我們在測試環(huán)境和生產(chǎn)環(huán)境發(fā)版時，去GitLab上找相關迭代版本的SQL腳本執(zhí)行。

通過該方式基本可以解決漏腳本的問題。

2 腳本語法錯誤

有些小伙伴看到這個標題可能有點懵，SQL腳本不是已經(jīng)在測試環(huán)境執(zhí)行過了嗎？為什么還會出現(xiàn)語法錯誤？

比如說有這樣的場景：原本你的SQL腳本沒問題的，但沒有按照規(guī)范，給一張表的添加多個字段，你寫了多條ALTER語句。

例如：

alter table t_user add column  `work` varchar(30) DEFAULT NULL COMMENT '工作';
alter table t_user add column  `provice` varchar(10) DEFAULT NULL  COMMENT '籍貫';

在上線時，你給DBA提SQL工單時，該工單被DBA審核拒絕打回來了。

然后為了趕時間，你急急忙忙把多條ALTER語句改成一條ALTER語句。

例如：

alter table t_user add `work` varchar(30) DEFAULT NULL COMMENT '工作',
add `provice` varchar(10) DEFAULT NULL  COMMENT '籍貫';

但在修改的過程中，有地方少了一個逗號，就可能會出現(xiàn)SQL語法錯誤。

因此，不管是什么SQL語句，要養(yǎng)成好習慣，只要修改了一定要記得到開發(fā)環(huán)境的數(shù)據(jù)庫中，先執(zhí)行測試一下，切勿直接提到生產(chǎn)環(huán)境，即使你有很大的把握，也需要再更慎重一些。

這樣基本可以避免SQL語法錯誤的問題。

3 腳本順序不對

有些時候，我們在上線系統(tǒng)時，DBA在執(zhí)行SQL腳本的時候，沒有報錯，但最后的數(shù)據(jù)就是不對。

有可能是腳本順序不對導致的。

比如有這樣一種場景：你往某張表通過insert初始化了一條數(shù)據(jù)。

例如：

INSERT INTO `sue`.`t_user`(`id`, `code`, `age`, `name`, `height`, `address`, `work`, `provice`) VALUES (1, '101', 21, '周星馳', 173, '香港', NULL, NULL);

另外一個人要基于你這條數(shù)據(jù)，通過update修改數(shù)據(jù)。

例如：

update t_user set age=25 where id=1;

你們提了兩條SQL腳本。

另外一個人先提的，你后提的。

DBA先把他的SQL工單審核通過了，先update數(shù)據(jù)，此時通過id是沒法找到那條數(shù)據(jù)的，影響行數(shù)為0。

然后DBA再審核你的SQL工單，審核通過了，插入了一條數(shù)據(jù)。

由于SQL腳本的順序不對，導致最終系統(tǒng)上線時的數(shù)據(jù)不對。

那么這個問題要如何解決呢？

雙方要事先溝通好，把另外一個同事的SQL腳本加到你的初始化腳本中，你的腳本在初始化時，直接去修改數(shù)據(jù)即可。

例如：

INSERT INTO `sue`.`t_user`(`id`, `code`, `age`, `name`, `height`, `address`, `work`, `provice`) VALUES (1, '101', 25, '周星馳', 173, '香港', NULL, NULL);

這樣可以避免執(zhí)行順序問題。

4 執(zhí)行時機不對

有些系統(tǒng)功能已經(jīng)上線了，在后面的迭代中，為了盡量避免少影響線上功能，可以增加一個pre（即預生產(chǎn)環(huán)境）。

該環(huán)境跟生產(chǎn)環(huán)境是差不多的，連接了相同的數(shù)據(jù)庫，使用了相同的apollo配置。

但唯一的區(qū)別是pre環(huán)境沒有實際的用戶流量，只能公司內部人員才能訪問。

一般在迭代版本上線之前，先要把系統(tǒng)功能發(fā)布到pre環(huán)境中，測試通過之后，才能發(fā)布到prod（即生產(chǎn)環(huán)境）。

但有些SQL腳本，卻沒法再pre環(huán)境中執(zhí)行，不然會影響生產(chǎn)環(huán)境。

比如：修改了字段類型，int改成varchar了，或者初始化數(shù)據(jù)時，初始化了一條新加的枚舉數(shù)據(jù)。

由于pre環(huán)境是運行的最新代碼，但prod環(huán)境還是運行的老代碼。

如果在發(fā)布pre環(huán)境時，直接執(zhí)行SQL腳本，可能會導致prod環(huán)境的功能異常。

因此要搞清楚SQL腳本的執(zhí)行時機，哪些是要在pre環(huán)境執(zhí)行的，哪些是要在prod環(huán)境執(zhí)行的。

我們在提SQL工單時，千萬不要一股腦就提了，一定要區(qū)分時機。

在發(fā)pre環(huán)境時，要么不提發(fā)prod環(huán)境的SQL腳本。要么，在工單的名稱上做區(qū)分，比如增加prod_開頭的標識。

這樣可以解決SQL腳本執(zhí)行時機的問題。

5 搞錯數(shù)據(jù)庫了

有時候，我們的數(shù)據(jù)庫做了分庫分表，或者增加備份庫。

在執(zhí)行SQL腳本的時候，由于我們自己的疏忽，提SQL工單時選錯數(shù)據(jù)庫了，或者DBA的疏忽，在執(zhí)行SQL工單時搞錯數(shù)據(jù)庫了，就會出現(xiàn)問題。

建議我們的SQL腳本增加庫名，比如：

alter table sue.t_user add `work` varchar(30) DEFAULT NULL COMMENT '工作';

這里增加庫名：sue。

這樣基本可以避免選錯數(shù)據(jù)庫的問題。

6 腳本耗時太長

有時候，我們的SQL腳本需要批量修改生產(chǎn)環(huán)境的一些數(shù)據(jù)，正常情況下一條update語句就能搞定。

例如：

update user set status=0 where status=1;

但由于user表的數(shù)據(jù)量非常大，我們在執(zhí)行該SQL腳本之前，沒有預先評估該SQL腳本的耗時情況，而選擇直接在生產(chǎn)環(huán)境的數(shù)據(jù)庫中執(zhí)行。

假如該SQL腳本耗時非常長，比如要10分鐘才能執(zhí)行完，可能會導致user表長期鎖表，影響正常的業(yè)務功能。

在該SQL腳本執(zhí)行的過程中，極有可能會出現(xiàn)業(yè)務功能操作，導致的死鎖問題。

因此，建議這種大批量的數(shù)據(jù)更新操作，要在用戶較少的凌晨，分批多次執(zhí)行。

我們要盡可能少的影響線上用戶的功能。

此外，在生產(chǎn)環(huán)境增加字段，增加索引等操作，也能會導致長期鎖表。也要避免在用戶訪問高峰期執(zhí)行相關的SQL腳本。

7 腳本無法回滾

絕大多數(shù)系統(tǒng)上線是能夠成功的，雖然過程中會遇到很多問題，但如果能夠及時解決，也能夠上線成功。

但如果有些問題，沒法再規(guī)定的時間內解決，很有可能會導致上線失敗。

如果上線失敗，意味著代碼和數(shù)據(jù)庫的SQL腳本要回滾。

如果只回滾了代碼，不回滾數(shù)據(jù)庫，可能會導致很多系統(tǒng)異常。

因此，我們在準備SQL語句時，要留點心眼，順便想想該SQL語句能否回滾。

對于update語句可以加上修改時間：

update t_user set age=25,time=now(3) where id=1;

這樣可以通過該時間追溯一次SQL操作修改的數(shù)據(jù)，方便后面做回滾。

有些時候我們要update的數(shù)據(jù)，是要通過多條sql語句查詢出來的，比如：需要使用的id。

為了方便回滾我們可以增加臨時表，保存這些id，后面就能追溯了。

當然有些開源的數(shù)據(jù)庫管理平臺，比如：Archery，是有自帶SQL審核和回滾的功能。

8 忘了加索引

我們在增加了字段之后，非常容易忽略的一件事是：加索引。

特別是當前表數(shù)據(jù)量很大，而且增加的字段是另外一張表的id時，這種情況強烈建議增加索引。

如果我們上線系統(tǒng)時，在SQL腳本中，忘了給該字段增加索引。如果該id字段被大批量訪問，全部走的全表掃描，可能會導致數(shù)據(jù)庫性能直線下降，出現(xiàn)大量的超時問題。

所以建議我們在開發(fā)的時候，如果要增加字段的話，要養(yǎng)成良好習慣，想一想這個字段需不需要建索引。

如果不確定數(shù)據(jù)量的話，可以先到生產(chǎn)環(huán)境查詢一下真實的用戶數(shù)據(jù)，不然后續(xù)可能會引起比較大的生產(chǎn)事故。

9 字段改名

對于生產(chǎn)環(huán)境的表字段，通常情況下，我們不允許修改名稱。

如果你在發(fā)布pre環(huán)境時，通過SQL腳本把某張表的某個字段名稱修改了，pre環(huán)境代碼使用了新的名稱，系統(tǒng)沒有問題。

但prod環(huán)境還是使用老的名稱，所有使用該名稱的sql語句，在代碼執(zhí)行過程中都會報錯。

因此，禁止在生產(chǎn)環(huán)境通過SQL腳本修改字段名稱。

當然系統(tǒng)上線時除了SQL腳本的這些坑之外，還有系統(tǒng)發(fā)版失敗，代碼合錯分支，mq消息被pre消費了，無法回滾等等，還有很多問題。