數(shù)據(jù)庫(kù)——MySQL高性能優(yōu)化規(guī)范

這篇具有很好參考價(jià)值的文章主要介紹了數(shù)據(jù)庫(kù)——MySQL高性能優(yōu)化規(guī)范。希望對(duì)大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請(qǐng)大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問(wèn)。

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

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

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

1. 所有表必須使用 Innodb 存儲(chǔ)引擎

沒(méi)有特殊要求（即 Innodb 無(wú)法滿(mǎn)足的功能如：列存儲(chǔ)，存儲(chǔ)空間數(shù)據(jù)等）的情況下，所有表必須使用 Innodb 存儲(chǔ)引擎（MySQL5.5 之前默認(rèn)使用 Myisam，5.6 以后默認(rèn)的為 Innodb）。

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

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

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

參考文章：MySQL 字符集不一致導(dǎo)致索引失效的一個(gè)真實(shí)案例

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

使用 comment 從句添加表和列的備注，從一開(kāi)始就進(jìn)行數(shù)據(jù)字典的維護(hù)

4. 盡量控制單表數(shù)據(jù)量的大小,建議控制在 500 萬(wàn)以?xún)?nèi)。

500 萬(wàn)并不是 MySQL 數(shù)據(jù)庫(kù)的限制，過(guò)大會(huì)造成修改表結(jié)構(gòu)，備份，恢復(fù)都會(huì)有很大的問(wèn)題。

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

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

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

謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢(xún)效率可能更低；

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

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

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

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

更有效的利用緩存，避免讀入無(wú)用的冷數(shù)據(jù)；

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

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

預(yù)留字段的命名很難做到見(jiàn)名識(shí)義。

預(yù)留字段無(wú)法確認(rèn)存儲(chǔ)的數(shù)據(jù)類(lèi)型，所以無(wú)法選擇合適的類(lèi)型。

對(duì)預(yù)留字段類(lèi)型的修改，會(huì)對(duì)表進(jìn)行鎖定。

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

通常文件很大，會(huì)短時(shí)間內(nèi)造成數(shù)據(jù)量快速增長(zhǎng)，數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)庫(kù)讀取時(shí)，通常會(huì)進(jìn)行大量的隨機(jī) IO 操作，文件很大時(shí)，IO 操作很耗時(shí)。

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

9. 禁止在線(xiàn)上做數(shù)據(jù)庫(kù)壓力測(cè)試

10. 禁止從開(kāi)發(fā)環(huán)境,測(cè)試環(huán)境直接連接生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)

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

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

原因：

列的字段越大，建立索引時(shí)所需要的空間也就越大，這樣一頁(yè)中所能存儲(chǔ)的索引節(jié)點(diǎn)的數(shù)量也就越少也越少，在遍歷時(shí)所需要的 IO 次數(shù)也就越多，索引的性能也就越差。

方法：

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

MySQL 提供了兩個(gè)方法來(lái)處理 ip 地址

inet_aton 把 ip 轉(zhuǎn)為無(wú)符號(hào)整型 (4-8 位)
inet_ntoa 把整型的 ip 轉(zhuǎn)為地址

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

b.對(duì)于非負(fù)型的數(shù)據(jù) (如自增 ID,整型 IP) 來(lái)說(shuō),要優(yōu)先使用無(wú)符號(hào)整型來(lái)存儲(chǔ)

原因：

無(wú)符號(hào)相對(duì)于有符號(hào)可以多出一倍的存儲(chǔ)空間

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295

VARCHAR(N) 中的 N 代表的是字符數(shù)，而不是字節(jié)數(shù)，使用 UTF8 存儲(chǔ) 255 個(gè)漢字 Varchar(255)=765 個(gè)字節(jié)。過(guò)大的長(zhǎng)度會(huì)消耗更多的內(nèi)存。

2. 避免使用 TEXT,BLOB 數(shù)據(jù)類(lèi)型，最常見(jiàn)的 TEXT 類(lèi)型可以存儲(chǔ) 64k 的數(shù)據(jù)

a. 建議把 BLOB 或是 TEXT 列分離到單獨(dú)的擴(kuò)展表中

MySQL 內(nèi)存臨時(shí)表不支持 TEXT、BLOB 這樣的大數(shù)據(jù)類(lèi)型，如果查詢(xún)中包含這樣的數(shù)據(jù)，在排序等操作時(shí)，就不能使用內(nèi)存臨時(shí)表，必須使用磁盤(pán)臨時(shí)表進(jìn)行。而且對(duì)于這種數(shù)據(jù)，MySQL 還是要進(jìn)行二次查詢(xún)，會(huì)使 sql 性能變得很差，但是不是說(shuō)一定不能使用這樣的數(shù)據(jù)類(lèi)型。

如果一定要使用，建議把 BLOB 或是 TEXT 列分離到單獨(dú)的擴(kuò)展表中，查詢(xún)時(shí)一定不要使用 select * 而只需要取出必要的列，不需要 TEXT 列的數(shù)據(jù)時(shí)不要對(duì)該列進(jìn)行查詢(xún)。

2、TEXT 或 BLOB 類(lèi)型只能使用前綴索引

因?yàn)镸ySQL 對(duì)索引字段長(zhǎng)度是有限制的，所以 TEXT 類(lèi)型只能使用前綴索引，并且 TEXT 列上是不能有默認(rèn)值的

3. 避免使用 ENUM 類(lèi)型

修改 ENUM 值需要使用 ALTER 語(yǔ)句

ENUM 類(lèi)型的 ORDER BY 操作效率低，需要額外操作

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

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

原因：

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

進(jìn)行比較和計(jì)算時(shí)要對(duì) NULL 值做特別的處理

5. 使用 TIMESTAMP(4 個(gè)字節(jié)) 或 DATETIME 類(lèi)型 (8 個(gè)字節(jié)) 存儲(chǔ)時(shí)間

TIMESTAMP 存儲(chǔ)的時(shí)間范圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

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

超出 TIMESTAMP 取值范圍的使用 DATETIME 類(lèi)型存儲(chǔ)

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

缺點(diǎn) 1：無(wú)法用日期函數(shù)進(jìn)行計(jì)算和比較
缺點(diǎn) 2：用字符串存儲(chǔ)日期要占用更多的空間

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

非精準(zhǔn)浮點(diǎn)：float,double
精準(zhǔn)浮點(diǎn)：decimal

Decimal 類(lèi)型為精準(zhǔn)浮點(diǎn)數(shù)，在計(jì)算時(shí)不會(huì)丟失精度

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

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

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

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

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

索引可以增加查詢(xún)效率，但同樣也會(huì)降低插入和更新的效率，甚至有些情況下會(huì)降低查詢(xún)效率。

因?yàn)?MySQL 優(yōu)化器在選擇如何優(yōu)化查詢(xún)時(shí)，會(huì)根據(jù)統(tǒng)一信息，對(duì)每一個(gè)可以用到的索引來(lái)進(jìn)行評(píng)估，以生成出一個(gè)最好的執(zhí)行計(jì)劃，如果同時(shí)有很多個(gè)索引都可以用于查詢(xún)，就會(huì)增加 MySQL 優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)間，同樣會(huì)降低查詢(xún)性能。

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

5.6 版本之前，一個(gè) sql 只能使用到一個(gè)表中的一個(gè)索引，5.6 以后，雖然有了合并索引的優(yōu)化方式，但是還是遠(yuǎn)遠(yuǎn)沒(méi)有使用一個(gè)聯(lián)合索引的查詢(xún)方式好。

3. 每個(gè) Innodb 表必須有個(gè)主鍵

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

Innodb 是按照主鍵索引的順序來(lái)組織表的

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

4. 常見(jiàn)索引列建議

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

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

建立索引的目的是：希望通過(guò)索引進(jìn)行數(shù)據(jù)查找，減少隨機(jī) IO，增加查詢(xún)性能，索引能過(guò)濾出越少的數(shù)據(jù)，則從磁盤(pán)中讀入的數(shù)據(jù)也就越少。

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

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

重復(fù)索引示例：primary key(id)、index(id)、unique index(id)
冗余索引示例：index(a,b,c)、index(a,b)、index(a)

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

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

覆蓋索引的好處：

避免 Innodb 表進(jìn)行索引的二次查詢(xún): Innodb 是以聚集索引的順序來(lái)存儲(chǔ)的，對(duì)于 Innodb 來(lái)說(shuō)，二級(jí)索引在葉子節(jié)點(diǎn)中所保存的是行的主鍵信息，如果是用二級(jí)索引查詢(xún)數(shù)據(jù)的話(huà)，在查找到相應(yīng)的鍵值后，還要通過(guò)主鍵進(jìn)行二次查詢(xún)才能獲取我們真實(shí)所需要的數(shù)據(jù)。而在覆蓋索引中，二級(jí)索引的鍵值中可以獲取所有的數(shù)據(jù)，避免了對(duì)主鍵的二次查詢(xún) ，減少了 IO 操作，提升了查詢(xún)效率。
可以把隨機(jī) IO 變成順序 IO 加快查詢(xún)效率: 由于覆蓋索引是按鍵值的順序存儲(chǔ)的，對(duì)于 IO 密集型的范圍查找來(lái)說(shuō)，對(duì)比隨機(jī)從磁盤(pán)讀取每一行的數(shù)據(jù) IO 要少的多，因此利用覆蓋索引在訪(fǎng)問(wèn)時(shí)也可以把磁盤(pán)的隨機(jī)讀取的 IO 轉(zhuǎn)變成索引查找的順序 IO。

8.索引 SET 規(guī)范

盡量避免使用外鍵約束

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

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

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

預(yù)編譯語(yǔ)句可以重復(fù)使用這些計(jì)劃，減少 SQL 編譯所需要的時(shí)間，還可以解決動(dòng)態(tài) SQL 所帶來(lái)的 SQL 注入的問(wèn)題。

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

相同語(yǔ)句可以一次解析，多次使用，提高處理效率。

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

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

select name,phone from customer where id = '111';

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

避免使用雙%號(hào)的查詢(xún)條件。如：a like '%123%'，（如果無(wú)前置%,只有后置%，是可以用到列上的索引的）

一個(gè) SQL 只能利用到復(fù)合索引中的一列進(jìn)行范圍查詢(xún)。如：有 a,b,c 列的聯(lián)合索引，在查詢(xún)條件中有 a 列的范圍查詢(xún)，則在 b,c 列上的索引將不會(huì)被用到。

在定義聯(lián)合索引時(shí)，如果 a 列要用到范圍查找的話(huà)，就要把 a 列放到聯(lián)合索引的右側(cè)，使用 left join 或 not exists 來(lái)優(yōu)化 not in 操作，因?yàn)?not in 也通常會(huì)使用索引失效。

4. 數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，應(yīng)該要對(duì)以后擴(kuò)展進(jìn)行考慮

5. 程序連接不同的數(shù)據(jù)庫(kù)使用不同的賬號(hào)，禁止跨庫(kù)查詢(xún)

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

6. 禁止使用 SELECT * 必須使用 SELECT <字段列表> 查詢(xún)

原因：

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

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

如：

insert into t1 values ('a','b','c');

應(yīng)使用：

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

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

通常子查詢(xún)?cè)?in 子句中，且子查詢(xún)中為簡(jiǎn)單 SQL(不包含 union、group by、order by、limit 從句) 時(shí),才可以把子查詢(xún)轉(zhuǎn)化為關(guān)聯(lián)查詢(xún)進(jìn)行優(yōu)化。

子查詢(xún)性能差的原因：

子查詢(xún)的結(jié)果集無(wú)法使用索引，通常子查詢(xún)的結(jié)果集會(huì)被存儲(chǔ)到臨時(shí)表中，不論是內(nèi)存臨時(shí)表還是磁盤(pán)臨時(shí)表都不會(huì)存在索引，所以查詢(xún)性能會(huì)受到一定的影響。特別是對(duì)于返回結(jié)果集比較大的子查詢(xún)，其對(duì)查詢(xún)性能的影響也就越大。

由于子查詢(xún)會(huì)產(chǎn)生大量的臨時(shí)表也沒(méi)有索引，所以會(huì)消耗過(guò)多的 CPU 和 IO 資源，產(chǎn)生大量的慢查詢(xún)。

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

對(duì)于 MySQL 來(lái)說(shuō)，是存在關(guān)聯(lián)緩存的，緩存的大小可以由 join_buffer_size 參數(shù)進(jìn)行設(shè)置。

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

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

同時(shí)對(duì)于關(guān)聯(lián)操作來(lái)說(shuō)，會(huì)產(chǎn)生臨時(shí)表操作，影響查詢(xún)效率，MySQL 最多允許關(guān)聯(lián) 61 個(gè)表，建議不超過(guò) 5 個(gè)。

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

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

11. 對(duì)應(yīng)同一列進(jìn)行 or 判斷時(shí)，使用 in 代替 or

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

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

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

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

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

對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換或計(jì)算時(shí)會(huì)導(dǎo)致無(wú)法使用索引

不推薦：

where date(create_time)='20190101'

推薦：

where create_time >= '20190101' and create_time < '20190102'

14. 在明顯不會(huì)有重復(fù)值時(shí)使用 UNION ALL 而不是 UNION

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

15. 拆分復(fù)雜的大 SQL 為多個(gè)小 SQL

大 SQL 邏輯上比較復(fù)雜，需要占用大量 CPU 進(jìn)行計(jì)算的 SQL
MySQL 中，一個(gè) SQL 只能使用一個(gè) CPU 進(jìn)行計(jì)算
SQL 拆分后可以通過(guò)并行執(zhí)行來(lái)提高處理效率

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

1. 超 100 萬(wàn)行的批量寫(xiě) (UPDATE,DELETE,INSERT) 操作,要分批多次進(jìn)行操作

大批量操作可能會(huì)造成嚴(yán)重的主從延遲

主從環(huán)境中,大批量操作可能會(huì)造成嚴(yán)重的主從延遲，大批量的寫(xiě)操作一般都需要執(zhí)行一定長(zhǎng)的時(shí)間，
而只有當(dāng)主庫(kù)上執(zhí)行完成后，才會(huì)在其他從庫(kù)上執(zhí)行，所以會(huì)造成主庫(kù)與從庫(kù)長(zhǎng)時(shí)間的延遲情況

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

大批量寫(xiě)操作會(huì)產(chǎn)生大量日志，特別是對(duì)于 row 格式二進(jìn)制數(shù)據(jù)而言，由于在 row 格式中會(huì)記錄每一行數(shù)據(jù)的修改，我們一次修改的數(shù)據(jù)越多，產(chǎn)生的日志量也就會(huì)越多，日志的傳輸和恢復(fù)所需要的時(shí)間也就越長(zhǎng)，這也是造成主從延遲的一個(gè)原因

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

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

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

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

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

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

pt-online-schema-change 它會(huì)首先建立一個(gè)與原表結(jié)構(gòu)相同的新表，并且在新表上進(jìn)行表結(jié)構(gòu)的修改，然后再把原表中的數(shù)據(jù)復(fù)制到新表中，并在原表中增加一些觸發(fā)器。把原表中新增的數(shù)據(jù)也復(fù)制到新表中，在行所有數(shù)據(jù)復(fù)制完成之后，把新表命名成原表，并把原來(lái)的表刪除掉。把原來(lái)一個(gè) DDL 操作，分解成多個(gè)小的批次進(jìn)行。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-668371.html

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

當(dāng)達(dá)到最大連接數(shù)限制時(shí)，還運(yùn)行 1 個(gè)有 super 權(quán)限的用戶(hù)連接
super 權(quán)限只能留給 DBA 處理問(wèn)題的賬號(hào)使用

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

程序使用數(shù)據(jù)庫(kù)賬號(hào)只能在一個(gè) DB 下使用，不準(zhǔn)跨庫(kù)
程序使用的賬號(hào)原則上不準(zhǔn)有 drop 權(quán)限

到了這里，關(guān)于數(shù)據(jù)庫(kù)——MySQL高性能優(yōu)化規(guī)范的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！