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

超出 取值范圍的使用 類型存儲(chǔ)

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

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

Decimal 類型為精準(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ù)量,建議單張表索引不超過 5 個(gè)

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

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

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

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

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

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

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

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

4. 常見索引列建議 5.如何選擇索引列的順序

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

6. 避免建立冗余索引和重復(fù)索引（增加了查詢優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)間） 7. 對(duì)于頻繁的查詢優(yōu)先考慮使用覆蓋索引

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

覆蓋索引的好處：

8.索引 SET 規(guī)范

盡量避免使用外鍵約束

數(shù)據(jù)庫 SQL 開發(fā)規(guī)范 1. 建議使用預(yù)編譯語句進(jìn)行數(shù)據(jù)庫操作

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

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

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

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

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

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

詳細(xì)解讀可以看：MySQL中的隱式轉(zhuǎn)換造成的索引失效這篇文章。

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

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

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

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

4. 數(shù)據(jù)庫設(shè)計(jì)時(shí)，應(yīng)該要對(duì)以后擴(kuò)展進(jìn)行考慮 5. 程序連接不同的數(shù)據(jù)庫使用不同的賬號(hào)，禁止跨庫查詢 6. 禁止使用 SELECT * 必須使用 SELECT 查詢

原因：

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

如：

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

應(yīng)使用：

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

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

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

子查詢性能差的原因：

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

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

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

對(duì)于 MySQL 來說，是存在關(guān)聯(lián)緩存的，緩存的大小可以由 參數(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í) 設(shè)置的也不合理的情況下，就容易造成服務(wù)器內(nèi)存溢出的情況，就會(huì)影響到服務(wù)器數(shù)據(jù)庫性能的穩(wěn)定性。

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

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

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

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

in 的值不要超過 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ī)值，如果滿足條件的數(shù)據(jù)集非常大，就會(huì)消耗大量的 CPU 和 IO 及內(nèi)存資源。

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

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

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

不推薦：

where date(create_time)='20190101'

推薦：

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

14. 在明顯不會(huì)有重復(fù)值時(shí)使用 UNION ALL 而不是 UNION 15. 拆分復(fù)雜的大 SQL 為多個(gè)小 SQL 數(shù)據(jù)庫操作行為規(guī)范 1. 超 100 萬行的批量寫 (UPDATE,DELETE,INSERT) 操作,要分批多次進(jìn)行操作

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

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

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

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

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

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

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

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

對(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ù)制完成之后，把新表命名成原表，并把原來的表刪除掉。把原來一個(gè) DDL 操作，分解成多個(gè)小的批次進(jìn)行。

3. 禁止為程序使用的賬號(hào)賦予 super 權(quán)限 4. 對(duì)于程序連接數(shù)據(jù)庫賬號(hào),遵循權(quán)限最小原則

轉(zhuǎn)自：MySQL高性能優(yōu)化規(guī)范建議 |