?

MySQL高性能優(yōu)化規(guī)范

作者: 聽風嗦随，原文地址:https://www.cnblogs.com/huchong/p/10219318.html。

數(shù)據(jù)庫命令規(guī)范

?所有數(shù)據(jù)庫對象名稱必須使用小寫字母并用下劃線分割?所有數(shù)據(jù)庫對象名稱禁止使用 MySQL 保留關(guān)鍵字（如果表名中包含關(guān)鍵字查詢時，需要將其用單引號括起來）?數(shù)據(jù)庫對象的命名要能做到見名識意，并且最后不要超過 32 個字符?臨時庫表必須以 tmp_為前綴并以日期為后綴，備份表必須以 bak_為前綴并以日期 (時間戳) 為后綴?所有存儲相同數(shù)據(jù)的列名和列類型必須一致（一般作為關(guān)聯(lián)列膊爪，如果查詢時關(guān)聯(lián)列類型不一致會自動進行數(shù)據(jù)類型隱式轉(zhuǎn)換，會造成列上的索引失效嚎莉，導致查詢效率降低）

數(shù)據(jù)庫基本設(shè)計規(guī)范

1. 所有表必須使用 Innodb 存儲引擎

沒有特殊要求（即Innodb 無法滿足的功能如：列存儲米酬，存儲空間數(shù)據(jù)等）的情況下，所有表必須使用 Innodb 存儲引擎（MySQL5.5 之前默認使用 Myisam趋箩，5.6 以后默認的為 Innodb）赃额。

Innodb 支持事務(wù)，支持行級鎖阁簸，更好的恢復性爬早，高并發(fā)下性能更好。

2. 數(shù)據(jù)庫和表的字符集統(tǒng)一使用 UTF8

兼容性更好启妹，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼，不同的字符集進行比較前需要進行轉(zhuǎn)換會造成索引失效醉旦，如果數(shù)據(jù)庫中有存儲emoji 表情的需要饶米，字符集需要采用 utf8mb4 字符集。

3. 所有表和字段都需要添加注釋

使用comment 從句添加表和列的備注车胡，從一開始就進行數(shù)據(jù)字典的維護

4. 盡量控制單表數(shù)據(jù)量的大小,建議控制在 500 萬以內(nèi)檬输。

500 萬并不是 MySQL 數(shù)據(jù)庫的限制，過大會造成修改表結(jié)構(gòu)匈棘，備份丧慈，恢復都會有很大的問題。

可以用歷史數(shù)據(jù)歸檔（應(yīng)用于日志數(shù)據(jù)），分庫分表（應(yīng)用于業(yè)務(wù)數(shù)據(jù)）等手段來控制數(shù)據(jù)量大小

5. 謹慎使用 MySQL 分區(qū)表

分區(qū)表在物理上表現(xiàn)為多個文件逃默，在邏輯上表現(xiàn)為一個表鹃愤；

謹慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低完域；

建議采用物理分表的方式管理大數(shù)據(jù)软吐。

6.盡量做到冷熱數(shù)據(jù)分離,減小表的寬度

MySQL 限制每個表最多存儲 4096 列，并且每一行數(shù)據(jù)的大小不能超過 65535 字節(jié)吟税。

減少磁盤IO,保證熱數(shù)據(jù)的內(nèi)存緩存命中率（表越寬凹耙，把表裝載進內(nèi)存緩沖池時所占用的內(nèi)存也就越大,也會消耗更多的 IO）；

更有效的利用緩存肠仪，避免讀入無用的冷數(shù)據(jù)肖抱；

經(jīng)常一起使用的列放到一個表中（避免更多的關(guān)聯(lián)操作）。

7. 禁止在表中建立預(yù)留字段

預(yù)留字段的命名很難做到見名識義异旧。

預(yù)留字段無法確認存儲的數(shù)據(jù)類型意述，所以無法選擇合適的類型。

對預(yù)留字段類型的修改泽艘，會對表進行鎖定欲险。

8. 禁止在數(shù)據(jù)庫中存儲圖片,文件等大的二進制數(shù)據(jù)

通常文件很大，會短時間內(nèi)造成數(shù)據(jù)量快速增長匹涮，數(shù)據(jù)庫進行數(shù)據(jù)庫讀取時天试，通常會進行大量的隨機IO 操作，文件很大時然低，IO 操作很耗時喜每。

通常存儲于文件服務(wù)器，數(shù)據(jù)庫只存儲文件地址信息

9. 禁止在線上做數(shù)據(jù)庫壓力測試

10. 禁止從開發(fā)環(huán)境,測試環(huán)境直接連接生成環(huán)境數(shù)據(jù)庫

數(shù)據(jù)庫字段設(shè)計規(guī)范

1. 優(yōu)先選擇符合存儲需要的最小的數(shù)據(jù)類型

原因：

列的字段越大雳攘，建立索引時所需要的空間也就越大带兜，這樣一頁中所能存儲的索引節(jié)點的數(shù)量也就越少也越少，在遍歷時所需要的IO 次數(shù)也就越多吨灭，索引的性能也就越差刚照。

方法：

a.將字符串轉(zhuǎn)換成數(shù)字類型存儲,如:將 IP 地址轉(zhuǎn)換成整形數(shù)據(jù)

MySQL 提供了兩個方法來處理 ip 地址

?inet_aton 把 ip 轉(zhuǎn)為無符號整型 (4-8 位)?inet_ntoa 把整型的 ip 轉(zhuǎn)為地址

插入數(shù)據(jù)前，先用inet_aton 把 ip 地址轉(zhuǎn)為整型喧兄，可以節(jié)省空間无畔，顯示數(shù)據(jù)時，使用 inet_ntoa 把整型的 ip 地址轉(zhuǎn)為地址顯示即可吠冤。

b.對于非負型的數(shù)據(jù) (如自增 ID,整型 IP) 來說,要優(yōu)先使用無符號整型來存儲

原因：

無符號相對于有符號可以多出一倍的存儲空間

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

SIGNED INT -2147483648~2147483647UNSIGNED INT 0~4294967295

VARCHAR(N) 中的 N 代表的是字符數(shù)浑彰，而不是字節(jié)數(shù)，使用 UTF8 存儲 255 個漢字 Varchar(255)=765 個字節(jié)拯辙。過大的長度會消耗更多的內(nèi)存郭变。

2. 避免使用 TEXT,BLOB 數(shù)據(jù)類型，最常見的 TEXT 類型可以存儲 64k 的數(shù)據(jù)

a. 建議把 BLOB 或是 TEXT 列分離到單獨的擴展表中

MySQL 內(nèi)存臨時表不支持 TEXT、BLOB 這樣的大數(shù)據(jù)類型诉濒，如果查詢中包含這樣的數(shù)據(jù)周伦，在排序等操作時，就不能使用內(nèi)存臨時表循诉，必須使用磁盤臨時表進行横辆。而且對于這種數(shù)據(jù)，MySQL 還是要進行二次查詢茄猫，會使 sql 性能變得很差狈蚤，但是不是說一定不能使用這樣的數(shù)據(jù)類型。

如果一定要使用划纽，建議把BLOB 或是 TEXT 列分離到單獨的擴展表中脆侮，查詢時一定不要使用 select * 而只需要取出必要的列，不需要 TEXT 列的數(shù)據(jù)時不要對該列進行查詢勇劣。

2靖避、TEXT 或 BLOB 類型只能使用前綴索引

因為MySQL[1]?對索引字段長度是有限制的，所以 TEXT 類型只能使用前綴索引比默，并且 TEXT 列上是不能有默認值的

3. 避免使用 ENUM 類型

修改ENUM 值需要使用 ALTER 語句

ENUM 類型的 ORDER BY 操作效率低幻捏，需要額外操作

禁止使用數(shù)值作為ENUM 的枚舉值

4. 盡可能把所有列定義為 NOT NULL

原因：

索引NULL 列需要額外的空間來保存，所以要占用更多的空間

進行比較和計算時要對NULL 值做特別的處理

5. 使用 TIMESTAMP(4 個字節(jié)) 或 DATETIME 類型 (8 個字節(jié)) 存儲時間

TIMESTAMP 存儲的時間范圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP 占用 4 字節(jié)和 INT 相同命咐，但比 INT 可讀性高

超出TIMESTAMP 取值范圍的使用 DATETIME 類型存儲

經(jīng)常會有人用字符串存儲日期型的數(shù)據(jù)（不正確的做法）

?缺點 1：無法用日期函數(shù)進行計算和比較?缺點 2：用字符串存儲日期要占用更多的空間

6. 同財務(wù)相關(guān)的金額類數(shù)據(jù)必須使用 decimal 類型

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

Decimal 類型為精準浮點數(shù)篡九，在計算時不會丟失精度

占用空間由定義的寬度決定，每4 個字節(jié)可以存儲 9 位數(shù)字醋奠，并且小數(shù)點要占用一個字節(jié)

可用于存儲比bigint 更大的整型數(shù)據(jù)

索引設(shè)計規(guī)范

1. 限制每張表上的索引數(shù)量,建議單張表索引不超過 5 個

索引并不是越多越好榛臼！索引可以提高效率同樣可以降低效率。

索引可以增加查詢效率窜司，但同樣也會降低插入和更新的效率沛善，甚至有些情況下會降低查詢效率。

因為MySQL 優(yōu)化器在選擇如何優(yōu)化查詢時塞祈，會根據(jù)統(tǒng)一信息金刁，對每一個可以用到的索引來進行評估，以生成出一個最好的執(zhí)行計劃议薪，如果同時有很多個索引都可以用于查詢胀葱，就會增加 MySQL 優(yōu)化器生成執(zhí)行計劃的時間，同樣會降低查詢性能笙蒙。

2. 禁止給表中的每一列都建立單獨的索引

5.6 版本之前，一個 sql 只能使用到一個表中的一個索引庆锦，5.6 以后捅位，雖然有了合并索引的優(yōu)化方式，但是還是遠遠沒有使用一個聯(lián)合索引的查詢方式好。

3. 每個 Innodb 表必須有個主鍵

Innodb 是一種索引組織表：數(shù)據(jù)的存儲的邏輯順序和索引的順序是相同的艇搀。每個表都可以有多個索引尿扯，但是表的存儲順序只能有一種。

Innodb 是按照主鍵索引的順序來組織表的

?不要使用更新頻繁的列作為主鍵焰雕，不適用多列主鍵（相當于聯(lián)合索引）?不要使用 UUID,MD5,HASH,字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）?主鍵建議使用自增 ID 值

4. 常見索引列建議

?出現(xiàn)在 SELECT衷笋、UPDATE、DELETE 語句的 WHERE 從句中的列?包含在 ORDER BY矩屁、GROUP BY辟宗、DISTINCT 中的字段?并不要將符合 1 和 2 中的字段的列都建立一個索引， 通常將 1吝秕、2 中的字段建立聯(lián)合索引效果更好?多表 join 的關(guān)聯(lián)列

5.如何選擇索引列的順序

建立索引的目的是：希望通過索引進行數(shù)據(jù)查找泊脐，減少隨機IO，增加查詢性能 烁峭，索引能過濾出越少的數(shù)據(jù)容客，則從磁盤中讀入的數(shù)據(jù)也就越少。

?區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）?盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)（因為字段長度越小约郁，一頁能存儲的數(shù)據(jù)量越大缩挑，IO 性能也就越好）?使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）

6. 避免建立冗余索引和重復索引（增加了查詢優(yōu)化器生成執(zhí)行計劃的時間）

?重復索引示例：primary key(id)、index(id)鬓梅、unique index(id)?冗余索引示例：index(a,b,c)供置、index(a,b)、index(a)

7. 對于頻繁的查詢優(yōu)先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢字段(where,select,ordery by,group by 包含的字段) 的索引

覆蓋索引的好處：

?避免Innodb 表進行索引的二次查詢:?Innodb 是以聚集索引的順序來存儲的己肮，對于 Innodb 來說士袄，二級索引在葉子節(jié)點中所保存的是行的主鍵信息，如果是用二級索引查詢數(shù)據(jù)的話谎僻，在查找到相應(yīng)的鍵值后娄柳，還要通過主鍵進行二次查詢才能獲取我們真實所需要的數(shù)據(jù)。而在覆蓋索引中艘绍，二級索引的鍵值中可以獲取所有的數(shù)據(jù)赤拒，避免了對主鍵的二次查詢 ，減少了 IO 操作诱鞠，提升了查詢效率挎挖。?可以把隨機IO 變成順序 IO 加快查詢效率:?由于覆蓋索引是按鍵值的順序存儲的寞焙，對于 IO 密集型的范圍查找來說肄梨，對比隨機從磁盤讀取每一行的數(shù)據(jù) IO 要少的多，因此利用覆蓋索引在訪問時也可以把磁盤的隨機讀取的 IO 轉(zhuǎn)變成索引查找的順序 IO关顷。

8.索引 SET 規(guī)范

盡量避免使用外鍵約束

?不建議使用外鍵約束（foreign key）阳掐，但一定要在表與表之間的關(guān)聯(lián)鍵上建立索引?外鍵可用于保證數(shù)據(jù)的參照完整性始衅，但建議在業(yè)務(wù)端實現(xiàn)?外鍵會影響父表和子表的寫操作從而降低性能

數(shù)據(jù)庫SQL 開發(fā)規(guī)范

1. 建議使用預(yù)編譯語句進行數(shù)據(jù)庫操作

預(yù)編譯語句可以重復使用這些計劃冷蚂，減少SQL 編譯所需要的時間，還可以解決動態(tài) SQL 所帶來的 SQL 注入的問題汛闸。

只傳參數(shù)蝙茶，比傳遞SQL 語句更高效。

相同語句可以一次解析诸老，多次使用隆夯，提高處理效率。

2. 避免數(shù)據(jù)類型的隱式轉(zhuǎn)換

隱式轉(zhuǎn)換會導致索引失效如:

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

select name,phone from customer where id = 111;

3. 充分利用表上已經(jīng)存在的索引

避免使用雙%號的查詢條件别伏。如：a like %123%蹄衷，（如果無前置%,只有后置%，是可以用到列上的索引的）

一個SQL 只能利用到復合索引中的一列進行范圍查詢畸肆。如：有 a,b,c 列的聯(lián)合索引宦芦，在查詢條件中有 a 列的范圍查詢，則在 b,c 列上的索引將不會被用到轴脐。

在定義聯(lián)合索引時调卑，如果a 列要用到范圍查找的話，就要把 a 列放到聯(lián)合索引的右側(cè)大咱，使用 left join 或 not exists 來優(yōu)化 not in 操作恬涧，因為 not in 也通常會使用索引失效。

4. 數(shù)據(jù)庫設(shè)計時碴巾，應(yīng)該要對以后擴展進行考慮

5. 程序連接不同的數(shù)據(jù)庫使用不同的賬號溯捆，進制跨庫查詢

?為數(shù)據(jù)庫遷移和分庫分表留出余地?降低業(yè)務(wù)耦合度?避免權(quán)限過大而產(chǎn)生的安全風險

6. 禁止使用 SELECT * 必須使用 SELECT <字段列表> 查詢

原因：

?消耗更多的 CPU 和 IO 以網(wǎng)絡(luò)帶寬資源?無法使用覆蓋索引?可減少表結(jié)構(gòu)變更帶來的影響

7. 禁止使用不含字段列表的 INSERT 語句

如：

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

insert into values (a,b,c);

應(yīng)使用：

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

insert into t(c1,c2,c3) values (a,b,c);

8. 避免使用子查詢，可以把子查詢優(yōu)化為 join 操作

通常子查詢在in 子句中厦瓢，且子查詢中為簡單 SQL(不包含 union提揍、group by、order by煮仇、limit 從句) 時,才可以把子查詢轉(zhuǎn)化為關(guān)聯(lián)查詢進行優(yōu)化劳跃。

子查詢性能差的原因：

子查詢的結(jié)果集無法使用索引，通常子查詢的結(jié)果集會被存儲到臨時表中浙垫，不論是內(nèi)存臨時表還是磁盤臨時表都不會存在索引刨仑，所以查詢性能會受到一定的影響。特別是對于返回結(jié)果集比較大的子查詢夹姥，其對查詢性能的影響也就越大杉武。

由于子查詢會產(chǎn)生大量的臨時表也沒有索引，所以會消耗過多的CPU 和 IO 資源辙售，產(chǎn)生大量的慢查詢轻抱。

9. 避免使用 JOIN 關(guān)聯(lián)太多的表

對于MySQL 來說，是存在關(guān)聯(lián)緩存的旦部，緩存的大小可以由 join_buffer_size 參數(shù)進行設(shè)置十拣。

在MySQL 中封拧，對于同一個 SQL 多關(guān)聯(lián)（join）一個表，就會多分配一個關(guān)聯(lián)緩存夭问，如果在一個 SQL 中關(guān)聯(lián)的表越多，所占用的內(nèi)存也就越大曹铃。

如果程序中大量的使用了多表關(guān)聯(lián)的操作缰趋，同時join_buffer_size 設(shè)置的也不合理的情況下，就容易造成服務(wù)器內(nèi)存溢出的情況陕见，就會影響到服務(wù)器數(shù)據(jù)庫性能的穩(wěn)定性秘血。

同時對于關(guān)聯(lián)操作來說，會產(chǎn)生臨時表操作评甜，影響查詢效率灰粮，MySQL 最多允許關(guān)聯(lián) 61 個表，建議不超過 5 個忍坷。

10. 減少同數(shù)據(jù)庫的交互次數(shù)

數(shù)據(jù)庫更適合處理批量操作粘舟，合并多個相同的操作到一起，可以提高處理效率佩研。

11. 對應(yīng)同一列進行 or 判斷時柑肴，使用 in 代替 or

in 的值不要超過 500 個，in 操作可以更有效的利用索引旬薯，or 大多數(shù)情況下很少能利用到索引晰骑。

12. 禁止使用 order by rand() 進行隨機排序

order by rand() 會把表中所有符合條件的數(shù)據(jù)裝載到內(nèi)存中，然后在內(nèi)存中對所有數(shù)據(jù)根據(jù)隨機生成的值進行排序绊序，并且可能會對每一行都生成一個隨機值硕舆，如果滿足條件的數(shù)據(jù)集非常大，就會消耗大量的 CPU 和 IO 及內(nèi)存資源骤公。

推薦在程序中獲取一個隨機值抚官，然后從數(shù)據(jù)庫中獲取數(shù)據(jù)的方式。

13. WHERE 從句中禁止對列進行函數(shù)轉(zhuǎn)換和計算

對列進行函數(shù)轉(zhuǎn)換或計算時會導致無法使用索引

不推薦：

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

where date(create_time)=20190101

推薦：

[if !supportLists]·?[endif]

[if !supportLists]·?[endif]

where create_time >= 20190101 and create_time < 20190102

14. 在明顯不會有重復值時使用 UNION ALL 而不是 UNION

?UNION 會把兩個結(jié)果集的所有數(shù)據(jù)放到臨時表中后再進行去重操作?UNION ALL 不會再對結(jié)果集進行去重操作

15. 拆分復雜的大 SQL 為多個小 SQL

?大 SQL 邏輯上比較復雜淋样，需要占用大量 CPU 進行計算的 SQL?MySQL 中耗式，一個 SQL 只能使用一個 CPU 進行計算?SQL 拆分后可以通過并行執(zhí)行來提高處理效率

數(shù)據(jù)庫操作行為規(guī)范

1. 超 100 萬行的批量寫 (UPDATE,DELETE,INSERT) 操作,要分批多次進行操作

大批量操作可能會造成嚴重的主從延遲

主從環(huán)境中,大批量操作可能會造成嚴重的主從延遲，大批量的寫操作一般都需要執(zhí)行一定長的時間趁猴， 而只有當主庫上執(zhí)行完成后刊咳，才會在其他從庫上執(zhí)行，所以會造成主庫與從庫長時間的延遲情況

binlog 日志為 row 格式時會產(chǎn)生大量的日志

大批量寫操作會產(chǎn)生大量日志儡司，特別是對于row 格式二進制數(shù)據(jù)而言娱挨，由于在 row 格式中會記錄每一行數(shù)據(jù)的修改，我們一次修改的數(shù)據(jù)越多捕犬，產(chǎn)生的日志量也就會越多跷坝，日志的傳輸和恢復所需要的時間也就越長酵镜，這也是造成主從延遲的一個原因

避免產(chǎn)生大事務(wù)操作

大批量修改數(shù)據(jù)，一定是在一個事務(wù)中進行的柴钻，這就會造成表中大批量數(shù)據(jù)進行鎖定淮韭，從而導致大量的阻塞，阻塞會對MySQL 的性能產(chǎn)生非常大的影響贴届。

特別是長時間的阻塞會占滿所有數(shù)據(jù)庫的可用連接靠粪，這會使生產(chǎn)環(huán)境中的其他應(yīng)用無法連接到數(shù)據(jù)庫，因此一定要注意大批量寫操作要進行分批

2. 對于大表使用 pt-online-schema-change 修改表結(jié)構(gòu)

?避免大表修改產(chǎn)生的主從延遲?避免在對表字段進行修改時進行鎖表

對大表數(shù)據(jù)結(jié)構(gòu)的修改一定要謹慎毫蚓，會造成嚴重的鎖表操作占键，尤其是生產(chǎn)環(huán)境，是不能容忍的元潘。

pt-online-schema-change 它會首先建立一個與原表結(jié)構(gòu)相同的新表畔乙，并且在新表上進行表結(jié)構(gòu)的修改，然后再把原表中的數(shù)據(jù)復制到新表中翩概，并在原表中增加一些觸發(fā)器牲距。把原表中新增的數(shù)據(jù)也復制到新表中，在行所有數(shù)據(jù)復制完成之后氮帐，把新表命名成原表嗅虏，并把原來的表刪除掉。把原來一個 DDL 操作上沐，分解成多個小的批次進行皮服。

3. 禁止為程序使用的賬號賦予 super 權(quán)限

?當達到最大連接數(shù)限制時，還運行 1 個有 super 權(quán)限的用戶連接?super 權(quán)限只能留給 DBA 處理問題的賬號使用

4. 對于程序連接數(shù)據(jù)庫賬號,遵循權(quán)限最小原則

?程序使用數(shù)據(jù)庫賬號只能在一個 DB 下使用参咙，不準跨庫?程序使用的賬號原則上不準有 drop 權(quán)限

1(?;LM&3