一(yī)、數(shù)據庫命←∞♦令規範

所有(yǒu)數(shù)據庫對(duì)象名稱必須使用(yòng)小(xiǎφ≠o)寫字母并用(yòng)下(xià)劃線分(fēn∑>)割

所有(yǒu)數(shù)據庫對(duì)象名稱禁止使用(yòng) ÷mysql保留關鍵字(如(rú)果表名中包含關鍵字查詢時(shí)，需要(ε©≠yào)将其用(yòng)單引号括起來(lái))

數(shù)據庫對(duì)象的(de)命名要(y"εào)能(néng)做(zuò)到(dào)見(jiàn)名♥×₽識意，并且最後不(bù)要(yào)超過32≤→✔φ個(gè)字符

臨時(shí)庫表必須以tmp_為(wèi)前綴并以日(rì)期為(wè'≤↓ i)後綴，備份表必須以bak_為(wèi)前綴并以日✘> $(rì)期(時(shí)間(jiān)戳)為(wèi)後綴

所有(yǒu)存儲相(xiàng)同數(shù)據的(de)列名和₽®(hé)列類型必須一(yī)緻(一(yī)般作(zuò)為(wèi)關聯≈♠‍‌列，如(rú)果查詢時(shí)關聯列類型不(bù)一(yī)緻會(huì)自↓§★(zì)動進行(xíng)數(shù)據類型隐式轉換，會(hu↓$ì)造成列上(shàng)的(de)索引失效，導緻查詢效率降低(γφdī))

二、數(shù)據庫基本設計(jì)規範

1、所有(yǒu)表必須使用(yòng)I☆$nnodb存儲引擎

沒有(yǒu)特殊要(yào)求(即Innodb無法滿足的↔≈(de)功能(néng)如(rú)：列存儲，存儲空(kōng)間(ji₹₽σ¶ān)數(shù)據等)的(de)情況下(xià)，≤↑©§所有(yǒu)表必須使用(yòng)In₩ nodb存儲引擎(mysql5.5之前默認使用(yòng)Myis✔↔♠am，5.6以後默認的(de)為(wèi)Innodb)Innodb 支持事₩§β(shì)務，支持行(xíng)級鎖，更好(hǎo)的(de)恢複↑₩π性，高(gāo)并發下(xià)性能(néng)更好(hǎo)

2、數(shù)據庫和(hé)表的(de)字符集統一(yī)使用(y₹"òng)UTF8

兼容性更好(hǎo)，統一(yī)字符集可(kě)以避免由于字符集轉換産¶$> 生(shēng)的(de)亂碼，不(bù)同的(φ'♠≥de)字符集進行(xíng)比較前需要(yà®×o)進行(xíng)轉換會(huì)造成索引失效

3、所有(yǒu)表和(hé)字段都(dōu)需要(yào)₩♥​®添加注釋

使用(yòng)comment從(cóng)句添加表和(hé)列的(™σde)備注 從(cóng)一(yī)開(kāi)始就(jφβ€iù)進行(xíng)數(shù)據字典∞α£★的(de)維護

4、盡量控制(zhì)單表數(shù)據量的(d$↑βe)大(dà)小(xiǎo)，建議(yì•↕ )控制(zhì)在500萬以內(nèi)

500萬并不(bù)是(shì)MySQL數(shù)據庫的(d↓ ≥e)限制(zhì)，過大(dà)會(huì)造成修改表結構，備份，恢∑β©₹複都(dōu)會(huì)有(yǒu)很(hěn)大(dà   )的(de)問(wèn)題

可(kě)以用(yòng)曆史數(shù)據歸檔(應用γ→↑(yòng)于日(rì)志(zhì)數(sh≈♣ù)據)，分(fēn)庫分(fēn)表(應用(yòng)于απ♠§業(yè)務數(shù)據)等手段來(lái)控 ¶↔π制(zhì)數(shù)據量大(dà)小(xiǎo)>π →

5、謹慎使用(yòng)MySQL分(fēn)區(qū)表

分(fēn)區(qū)表在物(wù)理(lǐ)上(shàng)表現(xiàn)₹≈φ₽為(wèi)多(duō)個(gè)文(wén)件(jiàn)，在邏輯上(≠©shàng)表現(xiàn)為(wèi)一(yī)個(gè)表 謹慎選擇分(f≈α∏ēn)區(qū)鍵，跨分(fēn)區(qū)查詢效Ω★§ 率可(kě)能(néng)更低(dī) 建議(yì<"≥‍)采用(yòng)物(wù)理(lǐ)分ασ±≠(fēn)表的(de)方式管理(lǐ)大(dà)數>'​(shù)據

6、盡量做(zuò)到(dào)冷(lěng)熱(rè<γ)數(shù)據分(fēn)離(lí)，減小(xiǎo)表的(dα•e)寬度

MySQL限制(zhì)每個(gè)表最多(duō)存儲4096列，™♣‌并且每一(yī)行(xíng)數(shù)據的(de)大(α ¥dà)小(xiǎo)不(bù)能(néng)超過6553§π5字節 減少(shǎo)磁盤IO,保證熱(rè)數(s‍∞♥ hù)據的(de)內(nèi)存緩存命中率(表•↓♦越寬，把表裝載進內(nèi)存緩沖池時(shí)所占用(yò ≥ng)的(de)內(nèi)存也(yě)就£¥•‍(jiù)越大(dà),也(yě)會(huì)✔§&消耗更多(duō)的(de)IO) 更有(yǒu)效的(de)利用πβσ∞(yòng)緩存，避免讀(dú)入無用(yòng)↑♣的(de)冷(lěng)數(shù)據 經常一(yīγ♣☆ )起使用(yòng)的(de)列放(fàng)到(dà♣λ±o)一(yī)個(gè)表中(避免更多(duō)的(de ∞)關聯操作(zuò))

7、禁止在表中建立預留字段

預留字段的(de)命名很(hěn)難做(zuò)到(dào)見(jiàn)名識• ₽義 預留字段無法确認存儲的(de)數(shù)據類型，所∞₹δ÷以無法選擇合适的(de)類型 對(duì)預留字段類型的(de)修改，會​β∑(huì)對(duì)表進行(xíng)鎖定

8、禁止在數(shù)據庫中存儲圖片，文(wén)件(jiàn)等大→₹(dà)的(de)二進制(zhì)數(shù)據φ©

通(tōng)常文(wén)件(jiàn)很(hěn)大(dà)，會(huì∑ ↑)短(duǎn)時(shí)間(jiān)內(nèi)造成數(sγ←hù)據量快(kuài)速增長(cháng)，數(÷δ♠‍shù)據庫進行(xíng)數(shù)據庫讀(dú)取時(shí)，∞±★通(tōng)常會(huì)進行(xíng)大(dà)量的(de≤βφ)随機(jī)IO操作(zuò)，文(wén)件(jiàn)很₽÷∑(hěn)大(dà)時(shí)，IO操作(zuò)很(hěn)耗時¥®≈(shí) 通(tōng)常存儲于文(w$γ§én)件(jiàn)服務器(qì)，數(shù)據φ♥庫隻存儲文(wén)件(jiàn)地(dì)址信息

9、禁止在線上(shàng)做(zuò)數(εΩσ♥shù)據庫壓力測試

10、禁止從(cóng)開(kāi)發環境，測試環境直接連接生(s×₹φΩhēng)成環境數(shù)據庫

三、數(shù)據庫字段設計(jì)規範

1、優先選擇符合存儲需要(yào)的(de)‍<∑₩最小(xiǎo)的(de)數(shù)據類型± ₩

• 原因

列的(de)字段越大(dà)，建立索引時(shí)所需要(yào)的(d↑∑e)空(kōng)間(jiān)也(yě)就(jiù)越大(dà)πγγ，這(zhè)樣一(yī)頁中所能(néng)存儲的(de)索引節點↑∏<的(de)數(shù)量也(yě)就(jiù)越少(shǎo)也(yě↔™ )越少(shǎo)，在遍曆時(shí)所需要(yào)的(d↔&≤πe)IO次數(shù)也(yě)就(jiù)越多(duō)， 索引的(d≥♥ e)性能(néng)也(yě)就(jiù)越差

• 方法

1)将字符串轉換成數(shù)字類型存儲，如(rú)：将✔¥‌✔IP地(dì)址轉換成整形數(shù)據。

mysql提供了(le)兩個(gè)方法來(lái↓$)處理(lǐ)ip地(dì)址：

1. inet_aton 把ip轉為(wèi)無符号整型(4≥↑α-8位)  
2. inet_ntoa 把整型的(de)ip轉Ω 為(wèi)地(dì)址 

插入數(shù)據前，先用(yòng)inet_aε✔ton把ip地(dì)址轉為(wèi)整型，δ≥可(kě)以節省空(kōng)間(jiān)。顯示數( β¶₩shù)據時(shí)，使用(yòng)in×✘≠​et_ntoa把整型的(de)ip地(dì)址轉為($¶₹wèi)地(dì)址顯示即可(kě)。

2)對(duì)于非負型的(de)數(shù)據(如(rú)自(zì)增I <D、整型IP)來(lái)說(shuō)，要(yào)優先使用↕β"(yòng)無符号整型來(lái)存儲

因為(wèi)：無符号相(xiàng)對(d§ε✘λuì)于有(yǒu)符号可(kě)以多(±βduō)出一(yī)倍的(de)存儲空(kōng)間(∏¶jiān)

1. SIGNED INT -21474παλ←83648~2147483647 
2. UNSIGNED INT 0~≠‌¥4294967295 

VARCHAR(N)中的(de)N代表的(de)是(shì)字符×¥數(shù)，而不(bù)是(shì)字節數(shù)

使用(yòng)UTF8存儲255個(gè)漢字 Varchar(δβ£255)=765個(gè)字節。過大(dà)的(de)長(ch>γ↔áng)度會(huì)消耗更多(duō)的(de)內(nè↕'•i)存

2、避免使用(yòng)TEXT、BLOB數(s★¥₩≤hù)據類型，最常見(jiàn)的(de)TEXT類₹₽≠↔型可(kě)以存儲64k的(de)數(shù)據

• 建議(yì)把BLOB或是(shì)TEXT列分(fēn)離(lí)到☆✔↓≠(dào)單獨的(de)擴展表中

Mysql內(nèi)存臨時(shí)表不(bù)支持∑ ™TEXT、BLOB這(zhè)樣的(de)大(dà)數(shù)據類φλβλ型，如(rú)果查詢中包含這(zhè)樣的(de☆±)數(shù)據，在排序等操作(zuò)時(shí♦≥¶)，就(jiù)不(bù)能(néng)使用(yòn€♣'★g)內(nèi)存臨時(shí)表，必須使用♥∑λ>(yòng)磁盤臨時(shí)表進行(xíng)。

而且對(duì)于這(zhè)種數(shù)據，Mysql還(hái)是(sh∞•ì)要(yào)進行(xíng)二次查詢，會(huì)使sql性能↔↓(néng)變得(de)很(hěn)差，但(dàn)是(s↔→hì)不(bù)是(shì)說(shuō)一(yī♦¥₩)定不(bù)能(néng)使用(yòng)這(zhè™$✘≤)樣的(de)數(shù)據類型。

如(rú)果一(yī)定要(yào)使用(yòng)，建議(yì ←)把BLOB或是(shì)TEXT列分(fēn)離(lí)到(dào)單獨的 ☆δ↑(de)擴展表中，查詢時(shí)一(yī)定不✘©✔ε(bù)要(yào)使用(yòng)select * 而隻需要(yào)取Ωπ↓出必要(yào)的(de)列，不(bù)需要(yào₩™←∏)TEXT列的(de)數(shù)據時(shí★​'≤)不(bù)要(yào)對(duì)該列進行(x≈&"íng)查詢。

• TEXT或BLOB類型隻能(néng)使用(yòng)前綴索引

因為(wèi)MySQL對(duì)索引字段長(cháng)度是(shì)Ω↑‍®有(yǒu)限制(zhì)的(de)，所以TEXT類型隻能≈™(néng)使用(yòng)前綴索引，并且TEXT<β↔‍列上(shàng)是(shì)不(bù)能(néng)÷$‍<有(yǒu)默認值的(de)。

3、避免使用(yòng)ENUM類型

• 修改ENUM值需要(yào)使用(yòng)ALTER£↔語句
• ENUM類型的(de)ORDER BY操作(zuò)效πε ☆率低(dī)，需要(yào)額外(wài)操作(zuò)
• 禁止使用(yòng)數(shù)值作(zuò)為(wèi)™<ENUM的(de)枚舉值

4、盡可(kě)能(néng)把所有(yǒu)列定λ↓÷"義為(wèi)NOT NULL

原因：

索引NULL列需要(yào)額外(wài)的(de•∏)空(kōng)間(jiān)來(lái)保存，所以要(φβ&yào)占用(yòng)更多(duō)的(de)空δπ(kōng)間(jiān);

進行(xíng)比較和(hé)計(jì)算(÷>εsuàn)時(shí)要(yào)對(duì)NULL值做(zuò)特π•α×别的(de)處理(lǐ)

5、使用(yòng)TIMESTAMP(  ★4個(gè)字節)或DATETIME類型(®​ 8個(gè)字節)存儲時(shí)間(jiān)

TIMESTAMP 存儲的(de)時(shí)間★© (jiān)範圍 1970-01-01 00:00:01 ♥​€≤~ 2038-01-19-03:14:07。

TIMESTAMP 占用(yòng)4字節和(hé)IN±‍T相(xiàng)同，但(dàn)比IN÷♥≠§T可(kě)讀(dú)性高(gāo)

超出TIMESTAMP取值範圍的(de)使用(yòn÷σ£g)DATETIME類型存儲。

經常會(huì)有(yǒu)人(rén)用(y≈★ε òng)字符串存儲日(rì)期型的(de)數(shù)據(不(bù)α∞¥​正确的(de)做(zuò)法)：

• 缺點1：無法用(yòng)日(rì)期函數(s≠λσ&hù)進行(xíng)計(jì)算(suàn)和(hé)比∑$π​較
• 缺點2：用(yòng)字符串存儲日(rì)∑∑期要(yào)占用(yòng)更多(duō)的(de)空(kōng)∞☆©間(jiān)

6、同财務相(xiàng)關的(de)金↓♠γ(jīn)額類數(shù)據必須使用(yòng)deci ®mal類型

• 非精準浮點：float,double
• 精準浮點：decimal

Decimal類型為(wèi)精準浮點數(shù)，在計γ↓σ★(jì)算(suàn)時(shí)不(bù)會(₹↕↑huì)丢失精度。占用(yòng)空(kōng)間(jiān)由定→‌義的(de)寬度決定，每4個(gè)字節可(αβλ©kě)以存儲9位數(shù)字，并且小(xiǎo™¶")數(shù)點要(yào)占用(yòng)一(yī)個(gè)字節 '↕。可(kě)用(yòng)于存儲比bigint更大(dà)的(®↓de)整型數(shù)據。

四、索引設計(jì)規範

1、限制(zhì)每張表上(shàng)的↓↔÷​(de)索引數(shù)量，建議(yì)單張表索引不♣δ(bù)超過5個(gè)

索引并不(bù)是(shì)越多(duō)越₽₽好(hǎo)!索引可(kě)以提高(gāo)效率同樣可(kě)以降低(dī)效Ω ₹←率。

索引可(kě)以增加查詢效率，但(dàn)同樣也(yě)會(huì)降φ£低(dī)插入和(hé)更新的(de)效率，甚至有(©£"♥yǒu)些(xiē)情況下(xià)會(huì)降低(dī)查δ✘詢效率。

因為(wèi)mysql優化(huà)器(q♠§®ì)在選擇如(rú)何優化(huà)查詢時(sh↑‌í)，會(huì)根據統一(yī)信息，對‍∑¥(duì)每一(yī)個(gè)可(kě)以用(y¥ ∏òng)到(dào)的(de)索引來(láφ©♣i)進行(xíng)評估，以生(shēng)成出一(∏↕§∞yī)個(gè)最好(hǎo)的(de)執行(xíng)計(jì)劃，如(≤☆Ωrú)果同時(shí)有(yǒu)很(hěn)多 ≠(duō)個(gè)索引都(dōu)可(kě)以用≥≥‌(yòng)于查詢，就(jiù)會(huì)增加mysql優化(≥<π≤huà)器(qì)生(shēng)成執行(xín•φg)計(jì)劃的(de)時(shí)間(jiān)，同樣會(≥♦huì)降低(dī)查詢性能(néng)。

2、禁止給表中的(de)每一(yī)列都(dō→≈δu)建立單獨的(de)索引

5.6版本之前，一(yī)個(gè)sq↑≠<•l隻能(néng)使用(yòng)到(dào‌←♣‌)一(yī)個(gè)表中的(de)一(yī)個(gè)索引，5.6以後→₩，雖然有(yǒu)了(le)合并索引的(de)優化(huà)方→€Ω≠式，但(dàn)是(shì)還(hái)是(shì)遠(yuǎn)∑α‍遠(yuǎn)沒有(yǒu)使用(yòng)一(yī)個↔φ(gè)聯合索引的(de)查詢方式好(h®→♣✘ǎo)

3、每個(gè)Innodb表必須有(yǒu)個(gè)主鍵

Innodb是(shì)一(yī)種索引組織表：數(‍✔shù)據的(de)存儲的(de)邏輯順序和(h♥β↔é)索引的(de)順序是(shì)相(xiàn>γ✘₽g)同的(de)。

每個(gè)表都(dōu)可(kě)以有(yǒu)多(duō‍☆)個(gè)索引，但(dàn)是(shì)表的(de↕÷↑δ)存儲順序隻能(néng)有(yǒu)一(yī)種 Innodb是(shì)α↕↓按照(zhào)主鍵索引的(de)順序來(lái)組織表的(©​de)。

不(bù)要(yào)使用(yòng)更新頻(pín)繁的(d±β←e)列作(zuò)為(wèi)主鍵，不(bù)适用(yòng)多(duō)列主 ≈→鍵(相(xiàng)當于聯合索引) 不(bù£λ)要(yào)使用(yòng)UUID、M←→××D5、HASH、字符串列作(zuò)為(wèi)主鍵(無 ≠α×法保證數(shù)據的(de)順序增長(∞‌&↑cháng))。

主鍵建議(yì)使用(yòng)自(zì)增I☆↓∑€D值。

五、常見(jiàn)索引列建議(yì)

• 出現(xiàn)在SELECT、UPDATE、DELETE&↓ε¶語句的(de)WHERE從(cóng)句中的(de)列
• 包含在ORDER BY、GROUP BY、DISTINCT中的✘₽↓σ(de)字段

并不(bù)要(yào)将符合1和(hé)2中的(de)字段的(de)♣&§列都(dōu)建立一(yī)個(gè)索引，通(tōng)常将1、2中的(de↕ →)字段建立聯合索引效果更好(hǎo)

• 多(duō)表join的(de)關聯列

六、如(rú)何選擇索引列的(de)順序

建立索引的(de)目的(de)是(shì >)：希望通(tōng)過索引進行(xíng)數(s¶♠hù)據查找，減少(shǎo)随機(jī)IO，增加查詢性能(néng)σ♠ ，索引能(néng)過濾出越少(shǎo)的(d₹δe)數(shù)據，則從(cóng)磁盤中讀(dú)入♥π¥的(de)數(shù)據也(yě)就(jiù)越少(shǎo)。

• 區(qū)分(fēn)度最高(gāo)的(de)放(fàng∞•₩↓)在聯合索引的(de)最左側(區(qū)分(fēn)度=列中不(bù)₹• ♦同值的(de)數(shù)量/列的(de)總行(xíng)數(shù));∞Ω™
• 盡量把字段長(cháng)度小(xiǎo)的(de)列放(fà✘☆§ng)在聯合索引的(de)最左側(因為(wèφφi)字段長(cháng)度越小(xiǎo)，一(yσ>≠γī)頁能(néng)存儲的(de)數(shù)據量越大(dà)，IO性能↑≥(néng)也(yě)就(jiù)越好(hǎo));
• 使用(yòng)最頻(pín)繁的(de)列≤¶©Ω放(fàng)到(dào)聯合索引的(de)左側(這(zhè)樣$ ♣ 可(kě)以比較少(shǎo)的(de)建立一(yī)些(x₽§×iē)索引)。

七、避免建立冗餘索引和(hé)重複索引

因為(wèi)這(zhè)樣會(huì)增加查詢優化(huà)¥∞₩器(qì)生(shēng)成執行(xíng)計(jì)劃的(de)時(sh¥↕≈¶í)間(jiān)。

• 重複索引示例：primary key(id)、index(id)、un£<ique index(id)
• 冗餘索引示例：index(a,b,c)、inσ‌≠₹dex(a,b)、index(a)

八、優先考慮覆蓋索引

對(duì)于頻(pín)繁的(de)查詢優先考慮‍∏"∏使用(yòng)覆蓋索引。

覆蓋索引：就(jiù)是(shì)包含了(le)所有(yǒu)查詢字段(whe♦<re,select,ordery by,group by包含的(de)™‌字段)的(de)索引

覆蓋索引的(de)好(hǎo)處：

• 避免Innodb表進行(xíng)索引的(de)二次查詢

Innodb是(shì)以聚集索引的(de)順₹•序來(lái)存儲的(de)，對(duì)于I±∞nnodb來(lái)說(shuō)，二 ®'©級索引在葉子(zǐ)節點中所保存的(de)是(shì)行(xíng)的(©§↓εde)主鍵信息，

如(rú)果是(shì)用(yòng)二級索引查詢數(shù)據的(de)話Ω∞<¥(huà)，在查找到(dào)相(xiàng)應的♦±↕λ(de)鍵值後，還(hái)要(yào)通(tōng)過主鍵₹ 進行(xíng)二次查詢才能(néng)獲取™§≤我們真實所需要(yào)的(de)數(shù)據。而在覆蓋索引γ®☆中，二級索引的(de)鍵值中可(kě)以獲取所有(yǒu)的(de)數(sh <¶≈ù)據，避免了(le)對(duì)主鍵的(de)二次查詢 ，減少(s‍¥hǎo)了(le)IO操作(zuò)，提升了(le)查詢效率¶π•。

• 可(kě)以把随機(jī)IO變成順序I∞€O加快(kuài)查詢效率

由于覆蓋索引是(shì)按鍵值的(de)順序存儲的(de)，對(duì)于I↕♦→O密集型的(de)範圍查找來(lái)說(shuō)，對(d®£¶uì)比随機(jī)從(cóng)磁盤讀(d®₽‌ú)取每一(yī)行(xíng)的(de)數(shù)據IO​•> 要(yào)少(shǎo)的(de)多(duō)，因此利用(yòng)覆蓋索β≠引在訪問(wèn)時(shí)也(yě)可(kě)以把♥α磁盤的(de)随機(jī)讀(dú)取的(de)IO轉變成索•ε₹引查找的(de)順序IO。

九、索引SET規範

盡量避免使用(yòng)外(wài)鍵約束

• 不(bù)建議(yì)使用(yòng)外♥™ (wài)鍵約束(foreign key)，但(dàn)一(yī)定要(yà₩•o)在表與表之間(jiān)的(de)關聯✔​"鍵上(shàng)建立索引;
• 外(wài)鍵可(kě)用(yòng)于保證數(shù¥&)據的(de)參照(zhào)完整性，但(dàn)建議≈‍ ↑(yì)在業(yè)務端實現(xiàn);
• 外(wài)鍵會(huì)影(yǐng)響父表和(hé)子(zǐ)表的(♠↔γ₩de)寫操作(zuò)從(cóng)而降低(dī)性能(néng)。

十、數(shù)據庫SQL開(kāi)發γ ☆♥規範

1、建議(yì)使用(yòng)預編譯語句進行(xíng)數(shù)據庫$‌☆操作(zuò)

預編譯語句可(kě)以重複使用(yòng)這(zhè)些€π≈(xiē)計(jì)劃，減少(shǎo)¶≥SQL編譯所需要(yào)的(de)時(shí)間(jiānδ≈)，還(hái)可(kě)以解決動态SQL所帶來(lái)的(de)SQ£×L注入的(de)問(wèn)題 隻傳參數(shù)，比傳遞♦©SQL語句更高(gāo)效 相(xiàng)同語‍¶句可(kě)以一(yī)次解析，多(duō)次使用(y&≤↑òng)，提高(gāo)處理(lǐ)效率。

2、避免數(shù)據類型的(de)隐式轉換

隐式轉換會(huì)導緻索引失效。如(rú)：se♥¥≠lect name,phone from cα£ustomer where id = '111'₹÷×;

3、充分(fēn)利用(yòng)表上(shà≠✘σ♥ng)已經存在的(de)索引

• 避免使用(yòng)雙%号的(de)查詢€>條件(jiàn)。

如(rú)a like '%123%&↑★£'，(如(rú)果無前置%,隻有(yǒu)♣φγ後置%，是(shì)可(kě)以用(yòng)到(dào)列上(shàng$→)的(de)索引的(de))

• 一(yī)個(gè)SQL隻能(néng)利用(yòng)δσα☆到(dào)複合索引中的(de)一(yī)列進<γ行(xíng)範圍查詢

如(rú)：有(yǒu) a,b,c列的≤δ<(de)聯合索引，在查詢條件(jiàn)中有(yǒu)a><£列的(de)範圍查詢，則在b,c列上(shàng)的(de)索引将不(bù)會&•♥(huì)被用(yòng)到(dào)，在定義聯合索引時(¶←shí)，如(rú)果a列要(yào)用(yòng)到(dào£$≤)範圍查找的(de)話(huà)，就(jiù)要(yào)把 ♣ a列放(fàng)到(dào)聯合索引的(de)右側。

• 使用(yòng)left join或 not exists來(lái)優化¶♦‍(huà)not in操作(zuò)

因為(wèi)not in 也(yě)通(tōng)常會β±(huì)使用(yòng)索引失效。

4、數(shù)據庫設計(jì)時(shí)，應該​≤®要(yào)對(duì)以後擴展進行(xíng)考慮

5、程序連接不(bù)同的(de)數(shù)據庫使用(yòng÷Ω♠₽)不(bù)同的(de)賬号，進制(zhì)跨↓<& 庫查詢

• 為(wèi)數(shù)據庫遷移和(hé)分(fΩ≥÷ēn)庫分(fēn)表留出餘地(dì)
• 降低(dī)業(yè)務耦合度
• 避免權限過大(dà)而産生(shēng)σ< ×的(de)安全風(fēng)險

6、禁止使用(yòng)SELECT * 必須使用(yòng)SELε"ECT <字段列表> 查詢

原因：

• 消耗更多(duō)的(de)CPU和(hé)IO以網絡帶寬資源
• 無法使用(yòng)覆蓋索引
• 可(kě)減少(shǎo)表結構變更帶來(lái→♣ ♦)的(de)影(yǐng)響

7、禁止使用(yòng)不(bù)含字段列表的(de)INSER±φT語句

如(rú)：insert into values₽" ('a','b','c'↔β;);

應使用(yòng)insert into t(c1,c2,c3) valu→≠​es ('a','b',✘♣&'c');

8、避免使用(yòng)子(zǐ)查詢，可(kě)以把子(zǐ)‌™★查詢優化(huà)為(wèi)join操作(zuò)

通(tōng)常子(zǐ)查詢在in子(zǐ)✘​α句中，且子(zǐ)查詢中為(wèi)簡單SQL(不(bù)包含•♠±union、group by、order by、limit從(cóng) σγ®句)時(shí)，才可(kě)以把子(zǐ)查詢轉化(huà)為(wèi)‍&關聯查詢進行(xíng)優化(huà)。

子(zǐ)查詢性能(néng)差的(de)原因：

• 子(zǐ)查詢的(de)結果集無法使用(yòng)索引，通(t䶱ōng)常子(zǐ)查詢的(de)結果集∏&‌§會(huì)被存儲到(dào)臨時(shí)表中，不 φ(bù)論是(shì)內(nèi)存臨時(shí)表還(hΩ ái)是(shì)磁盤臨時(shí)表都(dōu)不&σ(bù)會(huì)存在索引，所以查詢性能(n ®≈λéng)會(huì)受到(dào)一(yī)定的(de)影(yǐng)響;
• 特别是(shì)對(duì)于返回結果集比較大(dà)的(de)子(zǐ> ★₽)查詢，其對(duì)查詢性能(néng)的(de)影(yǐ¶₹≈ng)響也(yě)就(jiù)越大(dà);
• 由于子(zǐ)查詢會(huì)産生(shēng)大(dà∑♠π)量的(de)臨時(shí)表也(yě)沒有(yǒu)索引，₹™所以會(huì)消耗過多(duō)的(de)CPU和(h¶→∞↔é)IO資源，産生(shēng)大(dà)量的(de)慢(mà ✘•n)查詢。

9、避免使用(yòng)JOIN關聯太多 ​π(duō)的(de)表

對(duì)于Mysql來(lái)說(shuō)，是(↓αφπshì)存在關聯緩存的(de)，緩存的(de)大(dà)小(xiǎo)可(k‌←εě)以由join_buffer_size參數(shù) ₩φ進行(xíng)設置。

在Mysql中，對(duì)于同一(yī)個←¥γ☆(gè)SQL多(duō)關聯(join)≈€∑₽一(yī)個(gè)表，就(jiù)會(h↕& uì)多(duō)分(fēn)配一(yī)個(gè)關聯緩存，如(rú)果≠β☆在一(yī)個(gè)SQL中關聯的(de)表越多(d♥£♦↕uō)，所占用(yòng)的(de)內(nèi)存也(yě)就(jiù)越♥₩大(dà)。

如(rú)果程序中大(dà)量的(de)₹ 使用(yòng)了(le)多(duō)表關聯的(de)操作(zuò)，同時(s>∑λ¥hí)join_buffer_size設置的(de)也(y¶ ​ě)不(bù)合理(lǐ)的(de)情況下₩£♥(xià)，就(jiù)容易造成服務器(qì)內(nè ≤☆₹i)存溢出的(de)情況，就(jiù)會(huì)影(₩" ¥yǐng)響到(dào)服務器(qì)數(sh< ù)據庫性能(néng)的(de)穩定性。

同時(shí)對(duì)于關聯操作(zuò)來(lái)說(shuō•₹)，會(huì)産生(shēng)臨時(shí)表操作(zuò)，影(yǐn∏✔‌πg)響查詢效率Mysql最多(duō)允許關←÷✘→聯61個(gè)表，建議(yì)不(bù)超過5個(gè¥✔✔‌)。

10、減少(shǎo)同數(shù)據庫的(de)¶β交互次數(shù)

數(shù)據庫更适合處理(lǐ)批量操作(♠ zuò) 合并多(duō)個(gè)相(∏¶•←xiàng)同的(de)操作(zuò)到(dào)一(yī↑↓→∑)起，可(kě)以提高(gāo)處理(lǐ)效率

11、對(duì)應同一(yī)列進行(xíng∏€γγ)or判斷時(shí)，使用(yòng)in代替or

in的(de)值不(bù)要(yào)超過500個(gè)in操作₽≈↓(zuò)可(kě)以更有(yǒu)效的(de)利用(yòng)索引，orγ 大(dà)多(duō)數(shù)情況下(xià)很(h©✔★βěn)少(shǎo)能(néng)利用(yòng)到(dào)索引。

12、禁止使用(yòng)order by rand() 進行(xíng)±σ£∏随機(jī)排序

會(huì)把表中所有(yǒu)符合條件(jià&>★£n)的(de)數(shù)據裝載到(dào)₹÷≤∞內(nèi)存中，然後在內(nèi)存中對(duì)所有(yǒu©≤$)數(shù)據根據随機(jī)生(shēng)成的∞•★≥(de)值進行(xíng)排序，并且可(kě)能(néng)會(hu≠♦αì)對(duì)每一(yī)行(xíng)都(dōu)生(shē★"ng)成一(yī)個(gè)随機(jī)值 φ，如(rú)果滿足條件(jiàn)的(de)數(shù)據集非常£₹'↑大(dà)，就(jiù)會(huì)消耗大(dà)量的¥¥'(de)CPU和(hé)IO及內(nèi)存資源。

推薦在程序中獲取一(yī)個(gè)随機(jī)值，然後從(cóng)®≠數(shù)據庫中獲取數(shù)據的(de)方式

13、WHERE從(cóng)句中禁止對$™(duì)列進行(xíng)函數(shù)轉換和(h₽₹₽ é)計(jì)算(suàn)

對(duì)列進行(xíng)函數(shù)轉換或計(jì)算×≥¶(suàn)時(shí)會(huì)導緻無法使用(≠♥€÷yòng)索引。

不(bù)推薦：

1. where date(create_time₽∞)='20190101' 

推薦：

1. where create_time '>✘>= '20190101' ¶♠♦;and create_time <&n‍"✘bsp;'20190102' 

14、在明(míng)顯不(bù)會(huì)有(yǒu)重∞€複值時(shí)使用(yòng)UNION ALL而不(bù)是$∞≤(shì)UNION

• UNION會(huì)把兩個(gè)結果集的(d↔&σ≈e)所有(yǒu)數(shù)據放(fàng)到(dào)臨時(shí)™>₹≠表中後再進行(xíng)去(qù)重操作(zuò)≥$>
• UNION ALL不(bù)會(huì)再對(duì™​Ω×)結果集進行(xíng)去(qù)重操作(z‍£"∑uò)

15、拆分(fēn)複雜(zá)的(de)大≤™≠(dà)SQL為(wèi)多(duō)個(gè)小(x♠•¶iǎo)SQL

• 大(dà)SQL：邏輯上(shàng)比較複雜(zá)，Ω≈"&需要(yào)占用(yòng)大(dà)量CPU進行(xíng)計(jì)算($÷♥γsuàn)的(de)SQL
• MySQL：一(yī)個(gè)SQL隻能(néng)使用≈∞™(yòng)一(yī)個(gè)CPU進行(xíng)計(jδ$ì)算(suàn)
• SQL拆分(fēn)後可(kě)以通(tōng)過并行(xíng)執行(xí♣§"¶ng)來(lái)提高(gāo)處理(lǐ)效率

十一(yī)、數(shù)據庫操作(zuò)↓≤行(xíng)為(wèi)規範

1、超100萬行(xíng)的(de)批量寫(UPDATE、DELETE、I→π‍NSERT)操作(zuò)，要(yào)分(fēn)批 ÷多(duō)次進行(xíng)操作(zuò)®↓™✔

• 大(dà)批量操作(zuò)可(kě)能(néng)會(huì)造成嚴重的(→<Ω©de)主從(cóng)延遲

主從(cóng)環境中，大(dà)批量操作(zuò)可(kě)能(néng)®✔☆會(huì)造成嚴重的(de)主從(cóng)延遲，大(dà)批¥λ'量的(de)寫操作(zuò)一(yī)般都(dōu)需要(yào)執行(x±★♠íng)一(yī)定長(cháng)的(de)時(sδ÷hí)間(jiān)，而隻有(yǒu)當主λ©φ≈庫上(shàng)執行(xíng)完成後，才會(huì)在其他(tā)從(c✘‌óng)庫上(shàng)執行(xíng)，所以會(huì)↓ε 造成主庫與從(cóng)庫長(cháng)時(shí)間(jiānα₽×↓)的(de)延遲情況

• binlog日(rì)志(zhì)為(wèi↕ <)row格式時(shí)會(huì)産生(shēng)大(dà)量&≠£​的(de)日(rì)志(zhì)

大(dà)批量寫操作(zuò)會(huì§≥​₹)産生(shēng)大(dà)量日(rì)志(zhì)，特别是(shì)對(d∏> uì)于row格式二進制(zhì)數(shù)據而言，由于φ 在row格式中會(huì)記錄每一(yī)行(x→Ω‌σíng)數(shù)據的(de)修改，我們一(yī)±₩→次修改的(de)數(shù)據越多(duō)，産生(shē‌✘ng)的(de)日(rì)志(zhì)量也(yě)就(jiù)會(huì£ )越多(duō)，日(rì)志(zhì)的(de)傳輸和(hé)恢複所≤±需要(yào)的(de)時(shí)間(jiān)也(y÷‌ě)就(jiù)越長(cháng)，這(zhè© )也(yě)是(shì)造成主從(cóng)延遲的(de)一(yī)個(gè)≠γ原因。

• 避免産生(shēng)大(dà)事(shì)務操作(zuò)

大(dà)批量修改數(shù)據，一(yī)定是(shì)在∑α$α一(yī)個(gè)事(shì)務中進行(x★γ>íng)的(de)，這(zhè)就(jiù)會(huì)造成表中大("©dà)批量數(shù)據進行(xíng)鎖定，從(cóng)而導緻大(dà)量♣β的(de)阻塞，阻塞會(huì)對(duì)MySQL的(dσφe)性能(néng)産生(shēng)非常大(dà)的(de←€)影(yǐng)響。

特别是(shì)長(cháng)時(shí)間(jiān)的(dδ∑e)阻塞會(huì)占滿所有(yǒu)數(shù)據庫的(de)可(kě↕₩↔")用(yòng)連接，這(zhè)會(huì)使生(shēng)産環境中的(‌π™γde)其他(tā)應用(yòng)無法連接到(dàφ<§o)數(shù)據庫，因此一(yī)定要(yào)注意大(dà)批量寫操作(z≥<uò)要(yào)進行(xíng)分(fēn)批。

2、對(duì)于大(dà)表使用(yòng)pt-online-§"∞βschema-change修改表結構

• 避免大(dà)表修改産生(shēng)的(de)主從(cóng)延遲
• 避免在對(duì)表字段進行(xíng)修改時(shí)進行(xíng)鎖表

對(duì)大(dà)表數(shù)據結構的(de)修改一(yī≠Ω ₹)定要(yào)謹慎，會(huì)造成嚴重的(de)鎖表操作(zu&&ò)，尤其是(shì)生(shēng)産環境，是(shì)不(bù)能(né₹↕ng)容忍的(de)。

pt-online-schema-change它會ε$&↔(huì)首先建立一(yī)個(gè)與原表結≥$構相(xiàng)同的(de)新表，并且在新表上(s​→≤ hàng)進行(xíng)表結構的(de)修改，然後再把π≤原表中的(de)數(shù)據複制(zhì)到(dào)新表中，并在→∏↓×原表中增加一(yī)些(xiē)觸發器(qì)。

把原表中新增的(de)數(shù)據也(y®‌‍ě)複制(zhì)到(dào)新表中，在行(xíng)所有(yǒu)數(shùπ✔)據複制(zhì)完成之後，把新表命名成原表，并把原來(lái)的(de)表删♥$除掉。

把原來(lái)一(yī)個(gè)DDL操作(zuò)，分(fēn)≥☆₹±解成多(duō)個(gè)小(xiǎo)的(de)批次進行(xíngλ≠★)。

3、禁止為(wèi)程序使用(yòng)的(d≠'•e)賬号賦予super權限

當達到(dào)最大(dà)連接數(shù)限制(zhì)時®α(shí)，還(hái)運行(xíng)1個(gè)有(>♦yǒu)super權限的(de)用(yòng)戶↑‍£€連接super權限隻能(néng)留給DBA處理(lǐ)問♥σ(wèn)題的(de)賬号使用(yòng)​<♠。

4、對(duì)于程序連接數(shù)據庫賬号，遵循權限最小(xiǎ₹ασo)原則

程序使用(yòng)數(shù)據庫賬号隻能(nénε₽±‌g)在一(yī)個(gè)DB下(xià)使用(yò≤≤ng)，不(bù)準跨庫 程序使用(yòng)的(de)賬号原則上(shà ✘✔ng)不(bù)準有(yǒu)drop權限。