一(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í)間(j± iān)戳)為(wèi)後綴

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

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

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

沒有(yǒu)特殊要(yào)求(即Innodb無法滿足的(de)↓€←♣功能(néng)如(rú)：列存儲，存儲空(π$♣kōng)間(jiān)數(shù)據等)的(de)情況下(←≈♣xià)，所有(yǒu)表必須使用(yòngλσ)Innodb存儲引擎(mysql5.5之前默認使用>✔•✘(yòng)Myisam，5.6以後默認的(de)為ε♠♠★(wèi)Innodb)Innodb 支持事(shì)務，支持行(xín↔≈$§g)級鎖，更好(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)始就(jiù)進♥φ←行(xíng)數(shù)據字典的(de)維護£≤

4、盡量控制(zhì)單表數(shù)據量的(d★→e)大(dà)小(xiǎo)，建議(yì)控制(zhì)在5♣δΩ↓00萬以內(nèi)

500萬并不(bù)是(shì)MySQL數(shù)據庫的(de)σ​限制(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)表的(de)寬度

MySQL限制(zhì)每個(gè)表最多(duō)存儲4α ‌ε096列，并且每一(yī)行(xíng)數(shù)據的(∑≈de)大(dà)小(xiǎo)不(bù)能(néng)超過65535字節•π 減少(shǎo)磁盤IO,保證熱(rè)數(♣☆☆shù)據的(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)數(s© β‌hù)據 經常一(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)件(j→¥≠iàn)等大(dà)的(de)二進制(zh춶)數(shù)據

通(tōng)常文(wén)件(jiàn)很(hěn)大(dà)，會‍ 'δ(huì)短(duǎn)時(shí)間(jiān)內(nèi)造成數(shù) ®<←據量快(kuài)速增長(cháng)，數(shù)據庫進行(xíng)¶®→數(shù)據庫讀(dú)取時(shí)，通(tōn€ ✘g)常會(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ì)，數(s✔£✘ hù)據庫隻存儲文(wén)件(jiàn)地(dì)址信σ→息

9、禁止在線上(shàng)做(zuò)數(s≠γπhù)據庫壓力測試

10、禁止從(cóng)開(kāi)發環境，測試環境直接連接生(shē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)的(↕→→'de)IO次數(shù)也(yě)就(jiù)越多(duō)， 索引的(₹✘ de)性能(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φ∑≈_aton把ip地(dì)址轉為(wèi)整型，可(kě)↑δ以節省空(kōng)間(jiān)。顯示數(shù)據時(shí)，使‍↑用(yòng)inet_ntoa把整型的(de)ip地(dδ♥÷>ì)址轉為(wèi)地(dì)址顯示即可(kě)。

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

因為(wèi)：無符号相(xiàng)對(duì≥φ)于有(yǒu)符号可(kě)以多(duō)出一(yī)倍的(de)存儲空(k®‍βōng)間(jiān)

1. SIGNED INT -2147483648~21474≠•±83647 
2. UNSIGNED INT 0~4294967295&≥<₩σnbsp;

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數(shù)據類型，最常見(ji≈>àn)的(de)TEXT類型可(kě)以存儲64k的(de)數(shù)據"♦π•

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

Mysql內(nèi)存臨時(shí)表不(bù)支持TEXT、B♣✔•✘LOB這(zhè)樣的(de)大(dà)數(shù)據類"γ>≠型，如(rú)果查詢中包含這(zhè)樣的(™ <€de)數(shù)據，在排序等操作(zuò)€♦時(shí)，就(jiù)不(bù)能(néng)使用(y♦≠λòng)內(nèi)存臨時(shí)表，必須使用(yòng)磁© 盤臨時(shí)表進行(xíng)。

而且對(duì)于這(zhè)種數(shù)據，Myλε♦sql還(hái)是(shì)要(yào)進行(xíng♣↑≤)二次查詢，會(huì)使sql性能(néng)變得(de)很(hěn)差，但✔∑(dàn)是(shì)不(bù)是(shì)說(shuō)一(yī)定不("•≈∞bù)能(néng)使用(yòng)這(zhè)樣的(de)數®↕±(shù)據類型。

如(rú)果一(yī)定要(yào)使用(yòng)，建議(yì)把BLO©✔☆♣B或是(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)，所以TδαEXT類型隻能(néng)使用(yòng)前綴索引，并且TEXT列上(shànε₩γ‍g)是(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 NU✘  "LL

原因：

索引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)範圍 19>¶→≠70-01-01 00:00:01 ~ 2038-01-19-03:☆&14:07。

TIMESTAMP 占用(yòng)4字節和(hé)INT相(xi®∞☆àng)同，但(dàn)比INT可(kě)£<讀(dú)性高(gāo)

超出TIMESTAMP取值範圍的(de)使用(yòng)DATETIME↑•←Ω類型存儲。

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

• 缺點1：無法用(yòng)日(rì)期函數(shù)進行(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)decimal類型

• 非精準浮點：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)于存儲比bα÷igint更大(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∏​¥íng)計(jì)劃的(de)時(shí)間(jiān)，同樣會(hu★αβπì)降低(dī)查詢性能(néng)。

2、禁止給表中的(de)每一(yī)列都(dōu)≥β↑•建立單獨的(de)索引

5.6版本之前，一(yī)個(gè)sql隻能(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ī)種索引組織表：數(s♠‍hù)據的(de)存儲的(de)邏輯順序和(hé)索引 ≈♦♣的(de)順序是(shì)相(xiàng)同的(de♥Ω)。

每個(gè)表都(dōu)可(kě)以有(yǒu)多(duō)個(gè)索α‌π引，但(dàn)是(shì)表的(de)存π←儲順序隻能(néng)有(yǒu)一(yī)種 I$π♥nnodb是(shì)按照(zhào)主鍵索引的(d↓₽e)順序來(lái)組織表的(de)。

不(bù)要(yào)使用(yòng)更新頻(pín)繁的‍₹Ω(de)列作(zuò)為(wèi)主鍵，不(bù)适用(yòn©<$≠g)多(duō)列主鍵(相(xiàng)當于聯合索引) 不(bù)β§要(yào)使用(yòng)UUID、MD5、HASH、字符串列作(zuò♠↓←)為(wèi)主鍵(無法保證數(shù)據的(de)順序增長 ¥(cháng))。

主鍵建議(yì)使用(yòng)自(zì)增ID值。

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

• 出現(xiàn)在SELECT、UPDATE、DELE↕δ↕TE語句的(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)數(shù)據查找，減少(shǎo)随機(jī)IO¶♠，增加查詢性能(néng) ，索引能(néng)過濾出•$∏¥越少(shǎo)的(de)數(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)數(sφ↔§hù)據量越大(dà)，IO性能(néng> )也(yě)就(jiù)越好(hǎo));
• 使用(yòng)最頻(pín)繁的(de)列放(fàng)到(dào™₹)聯合索引的(de)左側(這(zhè)樣可(kě)以比較少(s∞>hǎo)的(de)建立一(yī)些(xiē)©‌索引)。

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

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

• 重複索引示例：primary key(id)、index(id)、uniq•∏§ue index(id)
• 冗餘索引示例：index(a,b,c)、index(a,b)、 ✔±index(a)

八、優先考慮覆蓋索引

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

覆蓋索引：就(jiù)是(shì)包含了(le)所有(yǒu)查詢字段(w÷λhere,select,ordery by,group by包含的(de)字​‌段)的(de)索引

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

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

Innodb是(shì)以聚集索引的(de)順序來(lái)存儲的(de)↑λ ​，對(duì)于Innodb來(lái)說(shuō)，二級索引在葉子(∏₩↕zǐ)節點中所保存的(de)是(shì)行(xíng)的(d✘₽πe)主鍵信息，

如(rú)果是(shì)用(yòng)二級索引查詢數(shù•≠☆)據的(de)話(huà)，在查找到(dàβ$'↔o)相(xiàng)應的(de)鍵值後，還(hái)要(yào)通(tōnφ£g)過主鍵進行(xíng)二次查詢才能(néng)獲取我們真實所需要(yà€♦λ o)的(de)數(shù)據。而在覆蓋索引中，二級索引的(de₹σ↓)鍵值中可(kě)以獲取所有(yǒu)的(de)數(shù)據，避免了(↓✔le)對(duì)主鍵的(de)二次查詢 ，減少(shǎo)了(le)IO≈↔ε操作(zuò)，提升了(le)查詢效率。

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

由于覆蓋索引是(shì)按鍵值的(de)順•§≥​序存儲的(de)，對(duì)于IO密集型的(de)範圍查找來↕↑(lái)說(shuō)，對(duì)比随機(jī)從(cón<₩♠g)磁盤讀(dú)取每一(yī)行(xíng)的(de)數(shù)據↓‍​αIO要(yào)少(shǎo)的(de)多 ¶£(duō)，因此利用(yòng)覆蓋索引在訪問(wèn)時(shβ‍í)也(yě)可(kě)以把磁盤的(de)随機(jī)讀(dú)取的(d >®e)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ón∑£✘g)而降低(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)SQL注∏₽入的(de)問(wèn)題 隻傳參數(shù)≈♦∞±，比傳遞SQL語句更高(gāo)效 相(xiànε∞g)同語句可(kě)以一(yī)次解析，多(du‌™Ω​ō)次使用(yòng)，提高(gāo)處理(lǐβ✔)效率。

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

隐式轉換會(huì)導緻索引失效。如(rú)：select na•™>‌me,phone from customer wh ≥ere id = '111';

3、充分(fēn)利用(yòng)表上(shàng)已經存在的(de)‌₩₩α索引

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

如(rú)a like '%123%& ₽Ω≥#39;，(如(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)的(d​λ✘₩e)索引将不(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)優化(h§πuà)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)的(d♥>₽✔e)安全風(fēng)險

6、禁止使用(yòng)SELECT * 必須使用(yòng)SE&Ω₽×LECT <字段列表> 查詢

原因：

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

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

如(rú)：insert into values ('a',&☆♠#39;b','c');

應使用(yòng)insert into t(c1,c2,c3) ↑♦values ('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_bu₽¥ffer_size參數(shù)進行(xíng)設置。Ω' 

在Mysql中，對(duì)于同一(yī)個(gè)SQL多(×←duō)關聯(join)一(yī)個(gè) ≠↕'表，就(jiù)會(huì)多(duō)分(fēnΩ$)配一(yī)個(gè)關聯緩存，如(rú)果在一>☆(yī)個(gè)SQL中關聯的(de)表越多(duō)，所占用(yòng)​$&α的(de)內(nèi)存也(yě)就(jiù)越大≠φγ±(dà)。

如(rú)果程序中大(dà)量的(de)使用(yòng)了(le)多(duō) ≠±♠表關聯的(de)操作(zuò)，同時(shí)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ǐng)響查詢效率Mys±♦≈δql最多(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én±×δ÷g)利用(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ēn≠Ω$g)成一(yī)個(gè)随機(jī)值₹♠γ ，如(rú)果滿足條件(jiàn)的(de)數(shù)據集非常大©₩∞(dà)，就(jiù)會(huì)消耗大(dà)量₩₹Ω★的(de)CPU和(hé)IO及內(nèi)存資源。

推薦在程序中獲取一(yī)個(gè)随機(jī)值，然後從(cónγ≈≤∑g)數(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_t¶★ime)='20190101' ↔γπ<;

推薦：

1. where create_time >=&nb₹←sp;'20190101' and c₽γ< reate_time < '&•;20190102' 

14、在明(míng)顯不(bù)會(huì)有(yǒu)重複值時(shí)✔φ​→使用(yòng)UNION ALL而不(bù)是(sh α₩ì)UNION

• UNION會(huì)把兩個(gè)結果β&π∑集的(de)所有(yǒu)數(shù)據​≥‍'放(fàng)到(dào)臨時(shí)表中後¶•☆↑再進行(xíng)去(qù)重操作(zuò)
• UNION ALL不(bù)會(huì)再對(duì)結€φ&果集進行(xíng)去(qù)重操作(zuò)‍β×✔

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)批量寫(UP≠₹©DATE、DELETE、INSERT)操作(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)時(shí)間(jiān)，而隻有(y ÷♥ǒu)當主庫上(shàng)執行(xíng)完成後，才會¶<(huì)在其他(tā)從(cóng)庫上(shàng)執行(xín' αg)，所以會(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ì)對(duì)于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án≠♠¥g)，這(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的(de)性能(néng)産生(↔₽πshēng)非常大(dà)的(de)影(y  ǐng)響。

特别是(shì)長(cháng)時(shí)間(÷>jiān)的(de)阻塞會(huì)占滿所有(y‌✔∞≠ǒu)數(shù)據庫的(de)可(kě)用(yòng)連接，這(<¥"σzhè)會(huì)使生(shēng)産環境中的(de)其>¶他(tā)應用(yòng)無法連接到(dào)數(shù)Ωφφ據庫，因此一(yī)定要(yào)注意大(dà)批量寫∞αΩ操作(zuò)要(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)新表，并且在新表上(shà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)¥γ®的(de)賬号賦予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éng)在一(yī)個(gè)π•DB下(xià)使用(yòng)，不(bù)準跨庫 程序使÷÷用(yòng)的(de)賬号原則上(shàng)不(bù)準∑♠λ>有(yǒu)drop權限。