一起聊聊系統(tǒng)上線時(shí)SQL腳本的9大坑

前言

系統(tǒng)上線時(shí)，非常容易出問(wèn)題。

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

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

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

今天跟大家一起聊聊，系統(tǒng)上線時(shí)SQL腳本的9大坑，以便于大家吸取教訓(xùn)，能夠防微杜漸，希望對(duì)你會(huì)有所幫助。

1 漏腳本了

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

有時(shí)候少加了一個(gè)字段。

有時(shí)候字段的注釋沒(méi)有及時(shí)修改。

有時(shí)候有些新表沒(méi)創(chuàng)建。

有時(shí)候字段類(lèi)型忘了修改。

等等。

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

那么，如何解決這個(gè)問(wèn)題呢？

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

我們?cè)陂_(kāi)發(fā)環(huán)境任何對(duì)表的相關(guān)操作，比如：增加字段、修改字段類(lèi)型、修改注釋、增加索引、創(chuàng)建表等等，都需要通過(guò)SQL語(yǔ)句操作，然后把該SQL語(yǔ)句，整理到SQL腳本中。

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

通過(guò)該方式基本可以解決漏腳本的問(wèn)題。

2 腳本語(yǔ)法錯(cuò)誤

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

比如說(shuō)有這樣的場(chǎng)景：原本你的SQL腳本沒(méi)問(wèn)題的，但沒(méi)有按照規(guī)范，給一張表的添加多個(gè)字段，你寫(xiě)了多條ALTER語(yǔ)句。

例如：

altertablet_useraddcolumn`work`varchar(30)DEFAULTNULLCOMMENT'工作';
altertablet_useraddcolumn`provice`varchar(10)DEFAULTNULLCOMMENT'籍貫';

在上線時(shí)，你給DBA提SQL工單時(shí)，該工單被DBA審核拒絕打回來(lái)了。

然后為了趕時(shí)間，你急急忙忙把多條ALTER語(yǔ)句改成一條ALTER語(yǔ)句。

例如：

altertablet_useradd`work`varchar(30)DEFAULTNULLCOMMENT'工作',
add`provice`varchar(10)DEFAULTNULLCOMMENT'籍貫';

但在修改的過(guò)程中，有地方少了一個(gè)逗號(hào)，就可能會(huì)出現(xiàn)SQL語(yǔ)法錯(cuò)誤。

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

這樣基本可以避免SQL語(yǔ)法錯(cuò)誤的問(wèn)題。

3 腳本順序不對(duì)

有些時(shí)候，我們?cè)谏暇€系統(tǒng)時(shí)，DBA在執(zhí)行SQL腳本的時(shí)候，沒(méi)有報(bào)錯(cuò)，但最后的數(shù)據(jù)就是不對(duì)。

有可能是腳本順序不對(duì)導(dǎo)致的。

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

例如：

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

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

例如：

updatet_usersetage=25whereid=1;

你們提了兩條SQL腳本。

另外一個(gè)人先提的，你后提的。

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

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

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

那么這個(gè)問(wèn)題要如何解決呢？

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

例如：

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

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

4 執(zhí)行時(shí)機(jī)不對(duì)

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

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

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

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

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

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

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

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

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

我們?cè)谔酳QL工單時(shí)，千萬(wàn)不要一股腦就提了，一定要區(qū)分時(shí)機(jī)。

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

這樣可以解決SQL腳本執(zhí)行時(shí)機(jī)的問(wèn)題。

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

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

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

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

altertablesue.t_useradd`work`varchar(30)DEFAULTNULLCOMMENT'工作';

這里增加庫(kù)名：sue。

這樣基本可以避免選錯(cuò)數(shù)據(jù)庫(kù)的問(wèn)題。

6 腳本耗時(shí)太長(zhǎng)

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

例如：

updateusersetstatus=0wherestatus=1;

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

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

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

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

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

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

7 腳本無(wú)法回滾

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

但如果有些問(wèn)題，沒(méi)法再規(guī)定的時(shí)間內(nèi)解決，很有可能會(huì)導(dǎo)致上線失敗。

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

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

因此，我們?cè)跍?zhǔn)備SQL語(yǔ)句時(shí)，要留點(diǎn)心眼，順便想想該SQL語(yǔ)句能否回滾。

對(duì)于update語(yǔ)句可以加上修改時(shí)間：

updatet_usersetage=25,time=now(3)whereid=1;

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

有些時(shí)候我們要update的數(shù)據(jù)，是要通過(guò)多條sql語(yǔ)句查詢(xún)出來(lái)的，比如：需要使用的id。

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

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

8 忘了加索引

我們?cè)谠黾恿俗侄沃螅浅Ｈ菀缀雎缘囊患率牵杭铀饕?/p>

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

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

所以建議我們?cè)陂_(kāi)發(fā)的時(shí)候，如果要增加字段的話(huà)，要養(yǎng)成良好習(xí)慣，想一想這個(gè)字段需不需要建索引。

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

9 字段改名

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

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

但prod環(huán)境還是使用老的名稱(chēng)，所有使用該名稱(chēng)的sql語(yǔ)句，在代碼執(zhí)行過(guò)程中都會(huì)報(bào)錯(cuò)。

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

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

審核編輯：劉清