2019獨角獸企業(yè)重金招聘Python工程師標(biāo)準>>>

前言

這些原則都是經(jīng)歷過實戰(zhàn)總結(jié)而成

每一條原則背后都是血淋淋的教訓(xùn)

這些原則主要是針對數(shù)據(jù)庫開發(fā)人員，在開發(fā)過程中務(wù)必注意

總是在災(zāi)難發(fā)生后，才想起容災(zāi)的重要性；

總是在吃過虧以后，才記得曾有人提醒過。

?

?

一、核心原則

1.盡量不在數(shù)據(jù)庫做運算

俗話說：別讓腳趾頭想事情，那是腦瓜子的職責(zé)

作為數(shù)據(jù)庫開發(fā)人員，我們應(yīng)該讓數(shù)據(jù)庫多做她所擅長的事情：

• 盡量不在數(shù)據(jù)庫做運算

• 復(fù)雜運算移到程序端CPU

• 盡可能簡單應(yīng)用MYSQL

舉例：

在mysql中盡量不要使用如：md5()、Order by Rand()等這類運算函數(shù)

2.盡量控制單表數(shù)據(jù)量

大家都知道單表數(shù)據(jù)量過大后會影響數(shù)據(jù)查詢效率，嚴重情況下會導(dǎo)致整個庫都卡住

一般情況下，按照一年內(nèi)單表數(shù)據(jù)量預(yù)估：

• 純INT不超過1000W

• 含CHAR不超過500W

同時要盡量做好合理的分表，使單表數(shù)據(jù)量不超載，常見的分表策略有：

• 通過USERID來分表（根據(jù)ID區(qū)間分表）：在金融行業(yè)應(yīng)用較多，用戶量大、用戶特征明顯

• 按DATE分表（按天、周、月分表）：在電信行業(yè)應(yīng)用非常多，如用戶上網(wǎng)記錄表、用戶短信表、話單表等

• 按AREA分表（省、市、區(qū)分表）

• 其他

分區(qū)表的適用場景主要有：

① 表非常大，無法全部存在內(nèi)存，或者只在表的最后有熱點數(shù)據(jù)，其他都是歷史數(shù)據(jù)；

② 分區(qū)表的數(shù)據(jù)更易維護，可以對獨立的分區(qū)進行獨立的操作；

③ 分區(qū)表的數(shù)據(jù)可以分布在不同的機器上，從而高效使用資源；

④ 可以使用分區(qū)表來避免某些特殊的瓶頸；

⑤ 可以備份和恢復(fù)獨立的分區(qū)。

但是使用分區(qū)表同樣有一些限制，在使用的時候需要注意：

① 一個表最多只能有 1024 個分區(qū)；

② 5.1版本中，分區(qū)表表達式必須是整數(shù)， 5.5可以使用列分區(qū)；

③ 分區(qū)字段中如果有主鍵和唯一索引列，那么主鍵列和唯一列都必須包含進來；

④ 分區(qū)表中無法使用外鍵約束；

⑤ 需要對現(xiàn)有表的結(jié)構(gòu)進行修改；

⑥ 所有分區(qū)都必須使用相同的存儲引擎；

⑦ 分區(qū)函數(shù)中可以使用的函數(shù)和表達式會有一些限制；

⑧ 某些存儲引擎不支持分區(qū)；

⑨ 對于 MyISAM 的分區(qū)表，不能使用 load index into cache；

⑩ 對于 MyISAM 表，使用分區(qū)表時需要打開更多的文件描述符。

3.盡量控制表字段數(shù)量

單表的字段數(shù)量也不能太多，根據(jù)業(yè)務(wù)場景進行優(yōu)化調(diào)整，盡量調(diào)整表字段數(shù)少而精，這樣有以下好處：

• IO高效

• 全表遍歷

• 表修復(fù)快

• 提高并發(fā)

• alter table更快

那究竟單表多少字段合適呢？

按照單表1G體積，500W行數(shù)據(jù)量進行評估：

• 順序讀1G文件需N秒

• 單行不超過200Byte

• 單表不超50個純INT字段

• 單表不超20個CHAR(10)字段

==>建議單表字段數(shù)上限控制在20~50個

4.平衡范式與冗余

數(shù)據(jù)庫表結(jié)構(gòu)的設(shè)計也講究平衡，以往我們經(jīng)常說要嚴格遵循三大范式，所以先來說說什么是范式：

第一范式：單個字段不可再分。唯一性。?

第二范式：不存在非主屬性只依賴部分主鍵。消除不完全依賴。?

第三范式：消除傳遞依賴。

用一句話來總結(jié)范式和冗余：

冗余是以存儲換取性能，

范式是以性能換取存儲。

所以，一般在實際工作中冗余更受歡迎一些。

模型設(shè)計時，這兩方面的具體的權(quán)衡，首先要以企業(yè)提供的計算能力和存儲資源為基礎(chǔ)。

其次，一般互聯(lián)網(wǎng)行業(yè)中都根據(jù)Kimball模式實施數(shù)據(jù)倉庫，建模也是以任務(wù)驅(qū)動的，因此冗余和范式的權(quán)衡符合任務(wù)需要。

例如，一份指標(biāo)數(shù)據(jù)，必須在早上8點之前處理完成，但計算的時間窗口又很小，要盡可能減少指標(biāo)的計算耗時，這時在計算過程中要盡可能減少多表關(guān)聯(lián)，模型設(shè)計時需要做更多的冗余。

5.拒絕3B

數(shù)據(jù)庫的并發(fā)就像城市交通，呈非線性增長

這就要求我們在做數(shù)據(jù)庫開發(fā)的時候一定要注意高并發(fā)下的瓶頸，防止因高并發(fā)造成數(shù)據(jù)庫癱瘓。

這里的拒絕3B是指：

• 大SQL（BIG SQL）：要減少

• 大事務(wù)（BIG Transaction）

• 大批量（BIG Batch）

二、字段類原則

1.用好數(shù)值字段類型

三類數(shù)值類型：

• 整型：TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)

• 浮點型：FLOAT(4B)、DOUBLE(8B)

• DECIMAL(M,D)

以幾個常見的例子來進行說明：

1）INT(1) VS INT(11)

很多人都分不清INT(1)和INT(11)的區(qū)別，想必大家也很好奇吧，其實1和11其實只是顯示長度的卻別而已，也就是不管int（x）x的值是什么值，存儲數(shù)字的取值范圍還是int本身數(shù)據(jù)類型的取值范圍，x只是數(shù)據(jù)顯示的長度而已。

2）BIGINT AUTO_INCREMENT

大家都知道，有符號int最大可以支持到約22億，遠遠大于我們的需求和MySQL單表所能支持的性能上限。對于OLTP應(yīng)用來說，單表的規(guī)模一般要保持在千萬級別，不會達到22億上限。如果要加大預(yù)留量，可以把主鍵改為改為無符號int，上限為42億，這個預(yù)留量已經(jīng)是非常的充足了。

使用bigint，會占用更大的磁盤和內(nèi)存空間，內(nèi)存空間畢竟有限，無效的占用會導(dǎo)致更多的數(shù)據(jù)換入換出，額外增加了IO的壓力，對性能是不利的。

因此推薦自增主鍵使用int unsigned類型，但不建議使用bigint。

3）DECIMAL(N,0)

當(dāng)采用DECIMAL數(shù)據(jù)類型的時候，一般小數(shù)位數(shù)不會是0，如果小數(shù)位數(shù)設(shè)置為0，那建議使用INT類型

2.將字符轉(zhuǎn)化為數(shù)字

數(shù)字型VS字符串型索引有更多優(yōu)勢：

• 更高效

• 查詢更快

• 占用空間更小

舉例：用無符號INT存儲IP，而非CHAR(15)

INT UNSIGNED

可以用INET_ATON()和INET_NTOA()來實現(xiàn)IP字符串和數(shù)值之間的轉(zhuǎn)換

3.優(yōu)先使用ENUM或SET

對于一些枚舉型數(shù)據(jù)，我們推薦優(yōu)先使用ENUM或SET，這樣的場景適合：

1）字符串型

2）可能值已知且有限

存儲方面：

1）ENUM占用1字節(jié)，轉(zhuǎn)為數(shù)值運算

2）SET視節(jié)點定，最多占用8字節(jié)

3）比較時需要加‘單引號（即使是數(shù)值）

舉例：

`sex` enum('F','M')? COMMENT '性別'；

`c1` enum('0','1','2','3') COMMENT '審核'；

4.避免使用NULL字段

為什么在數(shù)據(jù)庫表字段設(shè)計的時候盡量都加上NOT NULL DEFAULT ''，這里面不得不說用NULL字段的弊端：

• 很難進行查詢優(yōu)化

• NULL列加索引，需要額外空間

• 含NULL復(fù)合索引無效

舉例：

1）`a`? char(32)? DEFAULT NULL??【不推薦】

2）`b`? int(10) NOT NULL??【不推薦】

3）`c`? int(10) NOT NULL DEFAULT 0??【推薦】

5.少用并拆分TEXT/BLOB

TEXT類型處理性能遠低于VARCHAR

• 強制生成硬盤臨時表

• 浪費更多空間

• VARCHAR(65535)==>64K(注意UTF-8)

盡量不用TEXT/BLOB數(shù)據(jù)類型

如果業(yè)務(wù)需要必須用，建議拆分到單獨的表

舉例：

CREATE?TABLE?t1?(id?INT?NOT?NULL?AUTO_INCREMENT,data?TEXT?NOT?NULL,PRIMARY?KEY(id) )?ENGINE=InnoDB;

6.不在數(shù)據(jù)庫里存圖片

先上圖：

可見，如果將圖片全部存在數(shù)據(jù)庫，將使得數(shù)據(jù)庫體積變大，會造成讀寫速度變慢。

圖片存數(shù)據(jù)庫的弊端：

• 對數(shù)據(jù)庫的讀/寫的速度永遠都趕不上文件系統(tǒng)處理的速度

• 數(shù)據(jù)庫備份變的巨大，越來越耗時間

• 對文件的訪問需要穿越你的應(yīng)用層和數(shù)據(jù)庫層

★推薦處理辦法：數(shù)據(jù)庫中保存圖片路徑

按照年月日生成路徑。具體是按照年月日還是按照年月去生成路徑，根據(jù)自己需要(不一定是按照日期去生成)。

理解為什么要分散到多個文件夾中去才是關(guān)鍵，涉及到一個原理就明白了：

操作系統(tǒng)對單個目錄的文件數(shù)量是有限制的。當(dāng)文件數(shù)量很多的時候。從目錄中獲取文件的速度就會越來越慢。所以為了保持速度，才要按照固定規(guī)則去分散到多個目錄中去。

圖片分散到磁盤路徑中去。數(shù)據(jù)庫字段中保存的是類似于這樣子的”images/2012/09/25/ 1343287394783.jpg”

原來上傳的圖片文件名稱會重新命名保存，比如按照時間戳來生成，1343287394783. jpg。這樣子是為了避免文件名重復(fù)，多個人往同一個目錄上傳圖片的時候會出現(xiàn)。

反正用什么樣的規(guī)則命名圖片，只要做到圖片名稱的唯一性即可。

比如網(wǎng)站的并發(fā)訪問量大，目錄的生成分得月細越好。比如精確到小時，一個小時都可以是一個文件夾。同時0.001秒有兩個用戶同時在上傳圖片(因為那么就會往同一個小時文件夾里面存圖片)。因為時間戳是精確到秒的。為了做到圖片名稱唯一性而不至于覆蓋，生成可以在在時間戳后面繼續(xù)加毫秒微秒等。總結(jié)的規(guī)律是，并發(fā)訪問量越大。就越精確就好了。

題外話：

1）為什么保存的磁盤路徑，是”images/2012/09/25/1343287394783.jpg”，而不是” /images/2012/09/25/ 1343287394783.jpg”(最前面帶有斜杠)

在頁面中需要取出圖片路徑展示圖片的時候，如果是相對路徑，則可以使用”./”+”images/2012/09/25/1343287394783.jpg”進行組裝。

如果需要單獨的域名(比如做cdn加速的時候)域名，img1.xxx.com,img2.xxx.com這樣的域名，

直接組裝 “http://img1.xxx.com/”+”images/2012/09/25/1343287394783.jpg”

2）為什么保存的磁盤路徑，是”images/2012/09/25/1343287394783.jpg”，而不是“http://www.xxx.com/images/2012/09/25/1343287394783.jpg"

這里其實涉及到CDN的知識，具體CDN的知識在此不多展開，簡而言之：

cdn服務(wù)：對于靜態(tài)內(nèi)容是非常適合的。所以像商品圖片，隨著訪問量大了后，租用cdn服務(wù)，只需要把圖片上傳到他們的服務(wù)器上去。

例子：北京訪問長沙服務(wù)器，距離太遠。我完全可以把商品圖片，放到北京的云服務(wù)（我覺得現(xiàn)在提供給網(wǎng)站使用的云存儲其實就是cdn，給網(wǎng)站提供分流和就近訪問）上去。這樣子北京用戶訪問的時候，實際上圖片就是就近獲取。不需要很長距離的傳輸。

自己用一個域名img.xxx.com來載入圖片。這個域名解析到北京的云服務(wù)上去。

做法:數(shù)據(jù)庫中保存的是” images/2012/09/25/1343287394783.jpg”,

這些圖片實際上不存儲在web服務(wù)器上。上傳到北京的cdn服務(wù)器上去。

我從數(shù)據(jù)庫取出來，直接”img.xxx.com/”+” images/2012/09/25/1343287394783.jpg”

比如如果還有多個，就命名img1.xx.com、img2.xx.com

反正可以隨便。所以如果把域名直接保存進去。就顯得很麻煩了。遷移麻煩。

三、索引類原則

1.謹慎合理添加索引

• 添加索引是為了改善查詢

• 添加索引會減慢更新

• 索引不是越多越好

• 能不加的索引盡量不加（綜合評估數(shù)據(jù)密度和數(shù)據(jù)分布，最好不超過字段數(shù)20%）

• 結(jié)合核心SQL有限考慮覆蓋索引

舉例：不要給“性別”列創(chuàng)建索引

理論文章會告訴你值重復(fù)率高的字段不適合建索引。不要說性別字段只有兩個值，網(wǎng)友親測，一個字段使用拼音首字母做值，共有26種可能，加上索引后，百萬加的數(shù)據(jù)量，使用索引的速度比不使用索引要慢！

為什么性別不適合建索引呢？因為你訪問索引需要付出額外的IO開銷，你從索引中拿到的只是地址，要想真正訪問到數(shù)據(jù)還是要對表進行一次IO。假如你要從表的100萬行數(shù)據(jù)中取幾個數(shù)據(jù)，那么利用索引迅速定位，訪問索引的這IO開銷就非常值了。但如果你是從100萬行數(shù)據(jù)中取50萬行數(shù)據(jù)，就比如性別字段，那你相對需要訪問50萬次索引，再訪問50萬次表，加起來的開銷并不會比直接對表進行一次完整掃描小。

2.字符字段必須建前綴索引

區(qū)分度：

• 單字母區(qū)分度：26

• 4字母區(qū)分度：26*26*26*26 = 456,976

• 5字母區(qū)分度：26*26*26*26*26 = 11,881,376

• 6字母區(qū)分度：26*26*26*26*26*26 = 308,915,776

字符字段必須建前綴索引，例如：

`pinyin`?varchar(100)?DEFAULT?NULL?COMMENT?'小區(qū)拼音',? KEY?`idx_pinyin`?(`pinyin`(8)),? )?ENGINE=InnoDB

3.不在索引列做運算

原因有兩點：

1）會導(dǎo)致無法使用索引

2）會導(dǎo)致全表掃描

舉例：

BAD SAMPLE:

select?*?from?table? WHERE?to_days(current_date)?–?to_days(date_col)?<=?10

GOOD SAMPLE:?

select?*?from?table? WHERE?date_col?>=?DATE_SUB('2011-10-22',INTERVAL?10?DAY);

4.自增列或全局ID做INNODB主鍵

• 對主鍵建立聚簇索引

• 二級索引存儲主鍵值

• 主鍵不應(yīng)更新修改

• 按自增順序插入值

• 忌用字符串做主鍵

• 聚簇索引分裂

• 推薦用獨立于業(yè)務(wù)的AUTO_INCREMENT列或全局ID生成器做代理主鍵

• 若不指定主鍵，InnoDB會用唯一且非空值索引代替

5.盡量不用外鍵

線上OLTP系統(tǒng)盡量不用外鍵：

• 外鍵可節(jié)省開發(fā)量

• 有額外開銷

• 逐行操作

• 可“到達”其他表，意味著鎖

• 高并發(fā)時容易死鎖

建議由程序保證約束

比如我們原來建表語句是這樣的：

CREATE?TABLE?`user`?(`user_id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵',`user_name`?varchar(50)?NOT?NULL?DEFAULT?''?COMMENT?'用戶名',PRIMARY?KEY?(`user_id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;CREATE?TABLE?`order`?(`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵',`total_price`?decimal(10,2)?NOT?NULL?DEFAULT?'0.00',??`user_id`?int(11)?NOT?NULL?DEFAULT?'0',PRIMARY?KEY?(`id`),??KEY?`for_indx_user_id`?(`user_id`),??CONSTRAINT?`for_indx_user_id`?FOREIGN?KEY?(`user_id`)?REFERENCES?`user`?(`user_id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

不使用外鍵約束后：

CREATE?TABLE?`user`?(`user_id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵',??`user_name`?varchar(50)?NOT?NULL?DEFAULT?''?COMMENT?'用戶名',PRIMARY?KEY?(`user_id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;CREATE?TABLE?`order`?(`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵',??`total_price`?decimal(10,2)?NOT?NULL?DEFAULT?'0.00',??`user_id`?int(11)?NOT?NULL?DEFAULT?'0',PRIMARY?KEY?(`id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

不適用外鍵約束后，為了加快查詢我們通常會給不建立外鍵約束的字段添加一個索引。

CREATE?TABLE?`order`?(`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵',??`total_price`?decimal(10,2)?NOT?NULL?DEFAULT?'0.00',??`user_id`?int(11)?NOT?NULL?DEFAULT?'0',PRIMARY?KEY?(`id`),??KEY?`idx_user_id`?(`user_id`), )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

實際開發(fā)中，一般不會建立外鍵約束。

四、SQL類原則

1.SQL語句盡可能簡單

在開發(fā)過程中，我們盡量要保持SQL語句的簡單性，我們對比一下大SQL和多個簡單SQL

• 傳統(tǒng)設(shè)計思想

• BUG MySQL NOT

• 一條SQL只能在一個CPU運算

• 5000+ QPS的高并發(fā)中，1秒大SQL意味著？

• 可能一條大SQL就把整個數(shù)據(jù)庫堵死

拒絕大SQL，拆解成多條簡單SQL

• 簡單SQL緩存命中率更高

• 減少鎖表時間，特別是MyISAM

• 用上多CPU

2.保持事務(wù)（連接）短小

• 事務(wù)/連接使用原則：即開即用，用完即關(guān)

• 與事務(wù)無關(guān)操作都放到事務(wù)外面，減少鎖資源的占用

• 不破壞一致性前提下，使用多個短事務(wù)代替長事務(wù)

舉例：

1）發(fā)帖時的圖片上傳等待

2）大量的sleep連接

3.盡可能避免使用SP/TRIG/FUNC

線上OLTP系統(tǒng)中，我們應(yīng)當(dāng)：

• 盡可能少用存儲過程

• 盡可能少用觸發(fā)器

• 減少使用MySQL函數(shù)對結(jié)果進行處理

將上述這些事情都交給客戶端程序負責(zé)

4.盡量不用SELECT *

用SELECT * 時，將會更多的消耗CPU、內(nèi)存、IO以及網(wǎng)絡(luò)帶寬

我們在寫查詢語句時，應(yīng)當(dāng)盡量不用SELECT * ,只取需要的數(shù)據(jù)列：

• 更安全的設(shè)計：減少表變化帶來的影響

• 為使用covering index提供可能性

• Select/JOIN 減少硬盤臨時表生成，特別是有TEXT/BLOB時

舉例：

不推薦：

SELECT?*?FROM?tag WHERE?id?=?999148

推薦：

SELECT?keyword?FROM?tag WHERE?id?=?999148

5.改寫OR為IN()

同一字段，將or改寫為in()

OR效率：O(n)

IN效率：O(Log n）

當(dāng)n很大時，OR會慢很多

注意控制IN的個數(shù)，建議n小于200

舉例：

不推薦：

Select?*?from?opp?WHERE?phone='12347856'?or?phone='42242233'

推薦：

Select?*?from?opp?WHERE?phone?in?('12347856'?,?'42242233')

6.改寫OR為UNION

不同字段，將or改為union

• 減少對不同字段進行 "or" 查詢

• Merge index往往很弱智

• 如果有足夠信心：set global optimizer_switch='index_merge=off';

舉例：

不推薦：

Select?*?from?opp? WHERE?phone='010-88886666'? or? cellPhone='13800138000';

?

推薦：

Select?*?from?opp? WHERE?phone='010-88886666'? union? Select?*?from?opp? WHERE?cellPhone='13800138000';

7.避免負向查詢和%前綴模糊查詢

在實際開發(fā)中，我們要盡量避免負向查詢，那什么是負向查詢呢，主要有以下：

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

同時，我們還要避免%前綴模糊查詢，因為這樣會使用B+ Tree，同時會造成使用不了索引，并且會導(dǎo)致全表掃描，性能和效率可想而知

舉例：

8.減少COUNT(*)

在開發(fā)中我們經(jīng)常會使用COUNT(*)，殊不知這種用法會造成大量的資源浪費，因為COUNT(*)資源開銷大，所以我們能不用盡量少用

對于計數(shù)類統(tǒng)計，我們推薦：

• 實時統(tǒng)計：用memcache，雙向更新，凌晨跑基準

• 非實時統(tǒng)計：盡量用單獨統(tǒng)計表，定期重算

來對比一下COUNT(*)和其他幾個COUNT吧：

`id`?int(10)?NOT?NULL?AUTO_INCREMENT?COMMENT?'公司的id', `sale_id`?int(10)?unsigned?DEFAULT?NULL,

結(jié)論：

COUNT(*)=COUNT(1)

COUNT(0)=COUNT(1)

COUNT(1)=COUNT(100)

COUNT(*)!=COUNT(col)

9.LIMIT高效分頁

傳統(tǒng)分頁：

Select?*?from?table?limit?10000,10;

LIMIT原理：

• Limit 10000,10

• 偏移量越大則越慢

推薦分頁：

Select?*?from?table?WHERE?id>=23423?limit?11;? #10+1?(每頁10條) select?*?from?table?WHERE?id>=23434?limit?11;

分頁方式二：

Select?*?from?table?WHERE?id?>=?(?select?id?from?table?limit?10000,1?)?limit?10;

分頁方式三：

SELECT?*?FROM?table?INNER?JOIN?(SELECT?id?FROM?table?LIMIT?10000,10)?USING?(id)?;

分頁方式四：

#先使用程序獲取ID： select?id?from?table?limit?10000,10; #再用in獲取ID對應(yīng)的記錄 Select?*?from?table?WHERE?id?in?(123,456…)?;

具體需要根據(jù)實際的場景分析并重組索引

示例：

10.用UNION ALL 而非UNION

如果無需對結(jié)果進行去重，僅僅是對多表進行聯(lián)合查詢并展示，則用UNION ALL，因為UNION有去重開銷

舉例：

MySQL>SELECT?*?FROM?detail20091128?UNION?ALL? SELECT?*?FROM?detail20110427?UNION?ALL? SELECT?*?FROM?detail20110426?UNION?ALL? SELECT?*?FROM?detail20110425?UNION?ALL? SELECT?*?FROM?detail20110424?UNION?ALL? SELECT?*?FROM?detail20110423;

11.分解聯(lián)接保證高并發(fā)

高并發(fā)DB不建議進行兩個表以上的JOIN

適當(dāng)分解聯(lián)接保證高并發(fā)：

• 可緩存大量早期數(shù)據(jù)

• 使用了多個MyISAM表

• 對大表的小ID IN()

• 聯(lián)接引用同一個表多次

舉例：

原SQL：

MySQL>?Select?*?from?tag? JOIN?tag_post? on?tag_post.tag_id=tag.id? JOIN?post? on?tag_post.post_id=post.id? WHERE?tag.tag=‘二手玩具’;

分解SQL：

MySQL>?Select?*?from?tag?WHERE?tag=‘二手玩具’;? MySQL>?Select?*?from?tag_post?WHERE?tag_id=1321;? MySQL>?Select?*?from?post?WHERE?post.id?in?(123,456,314,141)

12.GROUP BY 去除排序

使用GROUP BY可以實現(xiàn)分組和自動排序

無需排序：Order by NULL

特定排序：Group by DESC/ASC

舉例：

13.同數(shù)據(jù)類型的列值比較

原則：數(shù)字對數(shù)字，字符對字符

數(shù)值列與字符類型比較：同時轉(zhuǎn)換為雙精度進行比對

字符列與數(shù)值類型比較：字符列整列轉(zhuǎn)數(shù)值，不會使用索引查詢

舉例：

字段：`remark` varchar(50) NOT NULL COMMENT '備注,默認為空',

MySQL>SELECT?`id`,?`gift_code`?FROM?gift? WHERE?`deal_id`?=?640?AND?remark=115127;? 1?row?in?set?(0.14?sec)MySQL>SELECT?`id`,?`gift_code`?FROM?pool_gift? WHERE?`deal_id`?=?640?AND?remark='115127';? 1?row?in?set?(0.005?sec)

14.Load data 導(dǎo)數(shù)據(jù)

批量數(shù)據(jù)快導(dǎo)入：

• 成批裝載比單行裝載更快，不需要每次刷新緩存

• 無索引時裝載比索引裝載更快

• Insert values ,values，values 減少索引刷新

• Load data比insert快約20倍

盡量不用INSERT ... SELECT，一個是有延遲，另外就是會同步出錯

15.打散大批量更新

• 大批量更新盡量凌晨操作，避開高峰

• 凌晨不限制

• 白天上線默認為100條/秒（特殊再議）

舉例：

update?post?set?tag=1?WHERE?id?in?(1,2,3);? sleep?0.01;? update?post?set?tag=1?WHERE?id?in?(4,5,6);? sleep?0.01; ……

16.Know Every SQL

作為DBA乃至數(shù)據(jù)庫開發(fā)人員，我們必須對數(shù)據(jù)庫的每條SQL都非常了解，常見的命令有：

• SHOW PROFILE

• MYSQLsla

• MySQLdumpslow

• explain

• Show Slow Log

• Show Processlist

• SHOW QUERY_RESPONSE_TIME(Percona）

五、約定類原則

1.隔離線上線下

構(gòu)建數(shù)據(jù)庫的生態(tài)環(huán)境，確保開發(fā)無線上庫操作權(quán)限

原則：線上連線上，線下連線下

• 生產(chǎn)數(shù)據(jù)用pro庫

• 預(yù)生產(chǎn)環(huán)境用pre庫

• 測試用test庫

• 開發(fā)用dev庫

2.禁止未經(jīng)DBA確認的子查詢

• 大部分情況優(yōu)化較差

• 特別WHERE中使用IN id的子查詢

• 一般可用JOIN改寫

舉例：

MySQL>?select?*?from?table1?where?id?in?(select?id?from?table2);? MySQL>?insert?into?table1?(select?*?from?table2);?//可能導(dǎo)致復(fù)制異常

3.永遠不在程序端顯式加鎖

• 外部鎖對數(shù)據(jù)庫丌可控

• 高幵發(fā)時是災(zāi)難

• 極難調(diào)試和排查

對于類似并發(fā)扣款等一致性問題，我們采用事務(wù)來處理，Commit前進行二次校驗沖突

4.統(tǒng)一字符集為UTF8

5.統(tǒng)一命名規(guī)范

1）庫表等名稱統(tǒng)一用小寫

2）索引命名默認為“idx_字段名"

3）庫名用縮寫，盡量在2~7個字母

? ? ?DataSharing ==> ds

4）注意避免用保留字命名

? ?

以上所有坑，建議數(shù)據(jù)庫開發(fā)人員都要銘記于心。

購買華為云請點擊立即購買

轉(zhuǎn)載于:https://my.oschina.net/u/4145327/blog/3059471

總結(jié)

以上是生活随笔為你收集整理的Mysql数据库开发的36条原则的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。