MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)的36條原則(小結(jié))

前言

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

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

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

一、核心原則

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

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

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

• 盡量不在數(shù)據(jù)庫(kù)做運(yùn)算
• 復(fù)雜運(yùn)算移到程序端CPU
• 盡可能簡(jiǎn)單應(yīng)用MYSQL

舉例：

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

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

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

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

• 純INT不超過(guò)1000W
• 含CHAR不超過(guò)500W

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

• 通過(guò)USERID來(lái)分表（根據(jù)ID區(qū)間分表）：在金融行業(yè)應(yīng)用較多，用戶量大、用戶特征明顯
• 按DATE分表（按天、周、月分表）：在電信行業(yè)應(yīng)用非常多，如用戶上網(wǎng)記錄表、用戶短信表、話單表等
• 按AREA分表（省、市、區(qū)分表）
• 其他

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• IO高效
• 全表遍歷
• 表修復(fù)快
• 提高并發(fā)
• alter table更快

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

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

• 順序讀1G文件需N秒
• 單行不超過(guò)200Byte
• 單表不超50個(gè)純INT字段
• 單表不超20個(gè)CHAR(10)字段

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

4.平衡范式與冗余

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

第一范式：?jiǎn)蝹€(gè)字段不可再分。唯一性。

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

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

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

冗余是以存儲(chǔ)換取性能，

范式是以性能換取存儲(chǔ)。

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

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

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

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

5.拒絕3B

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

這就要求我們?cè)谧鰯?shù)據(jù)庫(kù)開(kāi)發(fā)的時(shí)候一定要注意高并發(fā)下的瓶頸，防止因高并發(fā)造成數(shù)據(jù)庫(kù)癱瘓。

這里的拒絕3B是指：

• 大SQL（BIG SQL）：要減少
• 大事務(wù)（BIG Transaction）
• 大批量（BIG Batch）

二、字段類原則

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

三類數(shù)值類型：

• 整型：TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)
• 浮點(diǎn)型：FLOAT(4B)、DOUBLE(8B)
• DECIMAL(M,D)

以幾個(gè)常見(jiàn)的例子來(lái)進(jìn)行說(shuō)明：

1）INT(1) VS INT(11)

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

2）BIGINT AUTO_INCREMENT

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

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

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

3）DECIMAL(N,0)

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

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

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

• 更高效
• 查詢更快
• 占用空間更小

舉例：用無(wú)符號(hào)INT存儲(chǔ)IP，而非CHAR(15)

INT UNSIGNED

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

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

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

1）字符串型

2）可能值已知且有限

存儲(chǔ)方面：

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

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

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

舉例：

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

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

4.避免使用NULL字段

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

很難進(jìn)行查詢優(yōu)化

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

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

舉例：

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

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

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

5.少用并拆分TEXT/BLOB

TEXT類型處理性能遠(yuǎn)低于VARCHAR

• 強(qiáng)制生成硬盤臨時(shí)表
• 浪費(fèi)更多空間
• VARCHAR(65535)==>64K(注意UTF-8)

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

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

舉例：

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

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

先上圖：

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

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

1. 對(duì)數(shù)據(jù)庫(kù)的讀/寫的速度永遠(yuǎn)都趕不上文件系統(tǒng)處理的速度
2. 數(shù)據(jù)庫(kù)備份變的巨大，越來(lái)越耗時(shí)間
3. 對(duì)文件的訪問(wèn)需要穿越你的應(yīng)用層和數(shù)據(jù)庫(kù)層

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

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

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

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

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

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

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

比如網(wǎng)站的并發(fā)訪問(wèn)量大，目錄的生成分得月細(xì)越好。比如精確到小時(shí)，一個(gè)小時(shí)都可以是一個(gè)文件夾。同時(shí)0.001秒有兩個(gè)用戶同時(shí)在上傳圖片(因?yàn)槟敲淳蜁?huì)往同一個(gè)小時(shí)文件夾里面存圖片)。因?yàn)闀r(shí)間戳是精確到秒的。為了做到圖片名稱唯一性而不至于覆蓋，生成可以在在時(shí)間戳后面繼續(xù)加毫秒微秒等?？偨Y(jié)的規(guī)律是，并發(fā)訪問(wèn)量越大。就越精確就好了。

題外話：

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

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

如果需要單獨(dú)的域名(比如做cdn加速的時(shí)候)域名，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"

這里其實(shí)涉及到CDN的知識(shí)，具體CDN的知識(shí)在此不多展開(kāi)，簡(jiǎn)而言之：

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

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

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

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

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

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

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

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

三、索引類原則

1.謹(jǐn)慎合理添加索引

• 添加索引是為了改善查詢
• 添加索引會(huì)減慢更新
• 索引不是越多越好
• 能不加的索引盡量不加（綜合評(píng)估數(shù)據(jù)密度和數(shù)據(jù)分布，最好不超過(guò)字段數(shù)20%）
• 結(jié)合核心SQL有限考慮覆蓋索引

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

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

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

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.不在索引列做運(yùn)算

原因有兩點(diǎn)：

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

2）會(huì)導(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主鍵

• 對(duì)主鍵建立聚簇索引
• 二級(jí)索引存儲(chǔ)主鍵值
• 主鍵不應(yīng)更新修改
• 按自增順序插入值
• 忌用字符串做主鍵
• 聚簇索引分裂
• 推薦用獨(dú)立于業(yè)務(wù)的AUTO_INCREMENT列或全局ID生成器做代理主鍵
• 若不指定主鍵，InnoDB會(huì)用唯一且非空值索引代替

5.盡量不用外鍵

1. 線上OLTP系統(tǒng)盡量不用外鍵：
2. 外鍵可節(jié)省開(kāi)發(fā)量
3. 有額外開(kāi)銷
4. 逐行操作
5. 可“到達(dá)”其他表，意味著鎖
6. 高并發(fā)時(shí)容易死鎖

建議由程序保證約束

比如我們?cè)瓉?lái)建表語(yǔ)句是這樣的：

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;

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

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;

實(shí)際開(kāi)發(fā)中，一般不會(huì)建立外鍵約束。

四、SQL類原則

1.SQL語(yǔ)句盡可能簡(jiǎn)單

在開(kāi)發(fā)過(guò)程中，我們盡量要保持SQL語(yǔ)句的簡(jiǎn)單性，我們對(duì)比一下大SQL和多個(gè)簡(jiǎn)單SQL

1. 傳統(tǒng)設(shè)計(jì)思想
2. BUG MySQL NOT
3. 一條SQL只能在一個(gè)CPU運(yùn)算
4. 5000+ QPS的高并發(fā)中，1秒大SQL意味著？
5. 可能一條大SQL就把整個(gè)數(shù)據(jù)庫(kù)堵死

拒絕大SQL，拆解成多條簡(jiǎn)單SQL

1. 簡(jiǎn)單SQL緩存命中率更高
2. 減少鎖表時(shí)間，特別是MyISAM
3. 用上多CPU

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

1. 事務(wù)/連接使用原則：即開(kāi)即用，用完即關(guān)
2. 與事務(wù)無(wú)關(guān)操作都放到事務(wù)外面，減少鎖資源的占用
3. 不破壞一致性前提下，使用多個(gè)短事務(wù)代替長(zhǎng)事務(wù)

舉例：

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

2）大量的sleep連接

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

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

• 盡可能少用存儲(chǔ)過(guò)程
• 盡可能少用觸發(fā)器
• 減少使用MySQL函數(shù)對(duì)結(jié)果進(jìn)行處理

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

4.盡量不用SELECT *

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

我們?cè)趯懖樵冋Z(yǔ)句時(shí)，應(yīng)當(dāng)盡量不用SELECT * ,只取需要的數(shù)據(jù)列：

• 更安全的設(shè)計(jì)：減少表變化帶來(lái)的影響
• 為使用covering index提供可能性
• Select/JOIN 減少硬盤臨時(shí)表生成，特別是有TEXT/BLOB時(shí)

舉例：

不推薦：

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很大時(shí)，OR會(huì)慢很多

注意控制IN的個(gè)數(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

1. 減少對(duì)不同字段進(jìn)行 "or" 查詢
2. Merge index往往很弱智
3. 如果有足夠信心：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ù)向查詢和%前綴模糊查詢

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

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

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

舉例：

8.減少COUNT(*)

在開(kāi)發(fā)中我們經(jīng)常會(huì)使用COUNT(*)，殊不知這種用法會(huì)造成大量的資源浪費(fèi)，因?yàn)镃OUNT(*)資源開(kāi)銷大，所以我們能不用盡量少用

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

1. 實(shí)時(shí)統(tǒng)計(jì)：用memcache，雙向更新，凌晨跑基準(zhǔn)
2. 非實(shí)時(shí)統(tǒng)計(jì)：盡量用單獨(dú)統(tǒng)計(jì)表，定期重算

來(lái)對(duì)比一下COUNT(*)和其他幾個(gè)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高效分頁(yè)

傳統(tǒng)分頁(yè)：

Select * from table limit 10000,10;

LIMIT原理：

1. Limit 10000,10
2. 偏移量越大則越慢

推薦分頁(yè)：

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

分頁(yè)方式二：

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

分頁(yè)方式三：

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

分頁(yè)方式四：

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

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

示例：

10.用UNION ALL 而非UNION

如果無(wú)需對(duì)結(jié)果進(jìn)行去重，僅僅是對(duì)多表進(jìn)行聯(lián)合查詢并展示，則用UNION ALL，因?yàn)閁NION有去重開(kāi)銷

舉例：

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不建議進(jìn)行兩個(gè)表以上的JOIN

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

• 可緩存大量早期數(shù)據(jù)
• 使用了多個(gè)MyISAM表
• 對(duì)大表的小ID IN()
• 聯(lián)接引用同一個(gè)表多次

舉例：

原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可以實(shí)現(xiàn)分組和自動(dòng)排序

無(wú)需排序：Order by NULL

特定排序：Group by DESC/ASC

舉例：

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

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

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

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

舉例：

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

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)入：

1. 成批裝載比單行裝載更快，不需要每次刷新緩存
2. 無(wú)索引時(shí)裝載比索引裝載更快
3. Insert values ,values，values 減少索引刷新
4. Load data比insert快約20倍

盡量不用INSERT ... SELECT，一個(gè)是有延遲，另外就是會(huì)同步出錯(cuò)

15.打散大批量更新

• 大批量更新盡量凌晨操作，避開(kāi)高峰
• 凌晨不限制
• 白天上線默認(rèn)為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ù)庫(kù)開(kāi)發(fā)人員，我們必須對(duì)數(shù)據(jù)庫(kù)的每條SQL都非常了解，常見(jiàn)的命令有：

• SHOW PROFILE
• MYSQLsla
• MySQLdumpslow
• explain
• Show Slow Log
• Show Processlist
• SHOW QUERY_RESPONSE_TIME(Percona）

五、約定類原則

1.隔離線上線下

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

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

1. 生產(chǎn)數(shù)據(jù)用pro庫(kù)
2. 預(yù)生產(chǎn)環(huán)境用pre庫(kù)
3. 測(cè)試用test庫(kù)
4. 開(kāi)發(fā)用dev庫(kù)

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

1. 大部分情況優(yōu)化較差
2. 特別WHERE中使用IN id的子查詢
3. 一般可用JOIN改寫

舉例：

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

3.永遠(yuǎn)不在程序端顯式加鎖

1. 外部鎖對(duì)數(shù)據(jù)庫(kù)丌可控
2. 高幵發(fā)時(shí)是災(zāi)難
3. 極難調(diào)試和排查

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

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

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

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

2）索引命名默認(rèn)為“idx_字段名"

3）庫(kù)名用縮寫，盡量在2~7個(gè)字母

DataSharing ==> ds

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

以上所有坑，建議數(shù)據(jù)庫(kù)開(kāi)發(fā)人員都要銘記于心。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。